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

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

How to Enable or Disable Automated Statistics Collection in Oracle Database

by admin

What kind of statistics do the Automated tasks collect?

The 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. The Automatic Statistics Gathering 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. The statistics collected are generic one and not customized; however since they use “AUTO” by default, a number of decisions are automatically tailored to the data.

For many databases these automatic statistics are adequate, however, since these provide basic and general statistics it may be possible to collect better statistics by customizing the statistics collection. The flexibility is there to allow you to gathers stats daily for some tables, collect hourly statistics for others, and just once for static data. You can decide to collect more accurate statistics depending on the data volatility and the performance of the current statistics.

How to enable auto stats collection?

If for some reason automatic optimizer statistics collection is disabled, you can enable it using the ENABLE procedure in the DBMS_AUTO_TASK_ADMIN package:

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

How to disable the auto stats collection?

In situations when you want to disable automatic optimizer statistics collection, you can disable it using the DISABLE procedure in the DBMS_AUTO_TASK_ADMIN package:

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

Filed Under: oracle

Some more articles you might also be interested in …

  1. Interview Questions : Oracle Flex ASM 12c
  2. What are Lexical Units in a PL/SQL Block
  3. How to check Oracle Database uptime
  4. How do stubs work in a WebLogic Server cluster?
  5. Oracle RAC Interview Questions – Coherence and Split-Brain
  6. ASM background processes in 11gR2
  7. Oracle Grid 12c: Read Only Instances on Leaf Nodes
  8. How to Create and Manage Snapshot Standby Database
  9. How to Enable a Database Trigger
  10. How to Perform Manual Archiving in Oracle Database

You May Also Like

Primary Sidebar

Recent Posts

  • aws ec2: CLI for AWS EC2 (Command Examples)
  • aws cur – Create, query, and delete AWS usage report definitions (Command Examples)
  • aws configure – Manage configuration for the AWS CLI (Command Examples)
  • aws cognito-idp: Manage Amazon Cognito user pool and its users and groups using the CLI

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright