• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle database
  • Oracle Ebusiness Suite
  • Oracle weblogic
  • Oracle Performance Tuning
  • Oracle Hyperion
  • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
Home » Oracle » Oracle Database » How to check automatic statistics collection in Oracle in 11g

How to check automatic statistics collection in Oracle in 11g

August 6, 2023 by techgoeasy Leave a Comment

Automatic statistics collection is a new feature starting 10g. Oracle has done a lot of changes to it starting with 11g. In this article, we will see How to check automatic statistics collection in Oracle, How to enable automatic statistics collection in Oracle

Table of Contents

  • What is automatic statistics collection?
  • How to check automatic statistics collection in Oracle
  • How to enable it
  • How to disable it
  • How do to check the values of parameters ( estimate percent, type of histograms etc) used by the job?

What is automatic statistics collection?

  • Automatic statistics jobs automatically gather Missing and Stale statistics on tables, indexes, and partitions for use by the Cost Based Optimizer (CBO) in determining the most optimal access paths for queries.
  • This job was built to assist with the collection of statistics from the start with newly created databases so that there are accurate statistics to use rather than relying on defaults.

How to check automatic statistics collection in Oracle

SELECT CLIENT_NAME,
       STATUS
FROM   DBA_AUTOTASK_CLIENT
WHERE  CLIENT_NAME = 'auto optimizer stats collection';

How to enable it

exec DBMS_AUTO_TASK_ADMIN.ENABLE(
 client_name => 'auto optimizer stats collection',
 operation => 'auto optimizer stats job',
 window_name => NULL);

How to disable it

exec DBMS_AUTO_TASK_ADMIN.DISABLE(
 client_name => 'auto optimizer stats collection', 
 operation => NULL, 
 window_name => NULL);

How do to check the values of parameters ( estimate percent, type of histograms etc) used by the job?

For 19c

set pages 100 lines 80
select dbms_stats.get_prefs('ESTIMATE_PERCENT'),
dbms_stats.get_prefs('METHOD_OPT'),
dbms_stats.get_prefs('GRANULARITY'),
dbms_stats.get_prefs('CASCADE'),
dbms_stats.get_prefs('DEGREE'),
dbms_stats.get_prefs('NO_INVALIDATE'),
dbms_stats.get_prefs('AUTOSTATS_TARGET'),
dbms_stats.get_prefs('STALE_PERCENT'),
dbms_stats.get_prefs('INCREMENTAL'),
dbms_stats.get_prefs('PUBLISH'),
dbms_stats.get_prefs('CONCURRENT'),
dbms_stats.get_prefs('INCREMENTAL_STALENESS'),
dbms_stats.get_prefs('INCREMENTAL_LEVEL'),
dbms_stats.get_prefs('STAT_CATEGORY'),
dbms_stats.get_prefs('AUTO_STAT_EXTENSIONS'),
dbms_stats.get_prefs('APPROXIMATE_NDV_ALGORITHM'),
dbms_stats.get_prefs('AUTO_TASK_STATUS')
from dual;

I hope you like this article on automatic statistics collection in Oracle

Related Articles

How to delete Optimizer preference: check out How to delete Optimizer preference at table level, schema level, database level and global level with exact commands
How to gather Statistics with DBMS_STATS :check out How to gather Statistics with DBMS_STATS for table, schema, database and index level using oracle preferred way
Optimizer statistics preferences in Oracle: check out what is Optimizer statistics preferences in Oracle, various preferences available in Oracle 19c, How to set the Optimizer statistics preferences
How to check optimizer statistics preferences at the table level 
PREFERENCE_OVERRIDES_PARAMETER in Oracle
How to set Schema level preference

Filed Under: Oracle, Oracle Database

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • How to use Coalesce Function in Oracle
  • Oracle Database Cloud Service Overview
  • How to drop the sql baseline in Oracle
  • how to find file based on size in linux
  • How to check automatic statistics collection in Oracle in 11g

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us