How to disable and enable automatic statistics collection for oracle 10 GB

How to disable and enable automatic statistics collection for oracle 10g 1. Oracle 10g automatic statistics collection-Automatic analyze starts from oracle Database 10g, after creating a database, Oracle creates a scheduled task named GATHER_STATS_JOB by default, which is used to automatically collect statistics of CBO. This automatic task is enabled by default between-on the workday and all day on the weekend. Call DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC to collect statistics. This process first detects the missing and obsolete objects of statistics. Then, determine the priority and start the statistics. Www.2cto.com can query the running status of this JOB through the following: select * from Dba_Scheduler_Jobs where JOB_NAME = 'gather _ STATS_JOB 'there is also an AUTO_SPACE_ADVISOR_JOB: SQL> select JOB_NAME, LAST_START_DATE from dba_scheduler_jobs; JOB_NAME LAST_START_DATE when using AUTO_SPACE_ADVISOR_JOB 04-DEC-07 when PM + 08:00 GATHER_STATS_JOB 04-DEC-07 10.00.00.701152 PM + 08:00 FGR $ AUTOPURGE_JOB www.2cto.com PURGE_LOG 05-DEC-07 03.00.00.169059 am prc. However, this automation function has affected the normal operation of many systems and is not idle for most production systems at PM. Automatic analysis may lead to extremely serious competition for latches, which may lead to database Hang or Crash. Therefore, it is recommended that you disable the automatic statistical information collection function: exec DBMS_SCHEDULER.DISABLE ('gather _ STATS_JOB '); automation is always accompanied by serious risks! To disable and enable the automatic collection function, you can use either of the following methods: Method 1: exec dbms_scheduler.disable ('sys. GATHER_STATS_JOB '); www.2cto.com exec dbms_scheduler.enable ('sys. GATHER_STATS_JOB '); Method 2: alter system set "_ optimizer_autostats_job" = false scope = spfile; alter system set "_ optimizer_autostats_job" = true scope = spfile; pfile can directly modify the initialization parameter file and restart the database. Ii. By default, AWR runs automatically every small part of the MMON and MMNL processes. To save space, the collected data is automatically cleared seven days later. You can modify the snapshot frequency and retention time. To view the current settings, you can use the following statement: www.2cto.com select snap_interval, retention from dba_hist_wr_control; SNAP_INTERVAL RETENTION ----------------- + 00000 01:00:00. 0 + 00007 00:00:00. 0 these SQL statements show that snapshots are collected every hour and the collected data is retained for 7 days. To modify the settings-for example, the snapshot interval is 20 minutes and the retention time is two days-you can issue the following command. The parameter is in minutes. Begin dbms_workload_repository.modify_snapshot_settings (interval => 20, retention => 2*24*60 www.2cto.com); end; AWR uses several tables to store collected statistics, all tables are stored in the SYS mode in the specified tablespace named SYSAUX and named in the WRM $ _ * And WRH $ _ * formats. The previous type stores metadata information (such as checked databases and collected snapshots), and the latter type stores actual collected statistics. (As you may have guessed, H represents "historical", and M represents "metadata )".) Several views with DBA_HIST _ prefix are built on these tables. These views can be used to compile your own performance diagnostic tools. The view name is directly related to the table. For example, the view DBA_HIST_SYSMETRIC_SUMMARY is built on the WRH $ _ SYSMETRIC_SUMMARY table. Www.2cto.com your processing plan is generally regular and generally based on your understanding of various events and your experience in handling them. Now imagine that the same thing is done by an engine that collects metrics and launches possible plans based on predefined logic. Isn't your work easier? This engine, which is now available in Oracle Database 10 Gb, is called the automatic Database diagnosis and monitoring program (ADDM ). To make a decision, ADDM uses data collected by AWR. After each snapshot collection by AWR, ADDM is called to check the measurement and generate suggestions. Therefore, you actually have an automatic database administrator working 24 hours a day. It actively analyzes data and generates suggestions to free you from the situation, this allows you to focus on more strategic issues. Snapshots of www.2cto.com are automatically collected by default, but you can also collect them as needed. All AWR functions are implemented in the package DBMS_WORKLOAD_REPOSITORY. To collect a SNAPSHOT, run the following command: execute dbms_workload_repository.create_snapshot, which immediately collects the SNAPSHOT. the SNAPSHOT is recorded in the WRM $ _ SNAPSHOT table. The collected measurement is for the TYPICAL level. If you want to collect more detailed statistics, you can set the FLUSH_LEVEL parameter to ALL during the above process. Statistics are automatically deleted, but you can also manually delete them by calling drop_snapshot_range.