Manage Oracle statistics

Under the CBO (cost-based optimizer mode) condition, the execution plan of SQL statements is determined by the statistical information. If no statistical information is available, the execution plan is determined by dynamic sampling! It can be said that the statistical information is related to whether the SQL Execution Plan is correct. It is a guiding ideology for SQL Execution. The oracle initialization parameter statistics_level controls the level of collected statistics. There are three parameter values:

BASIC: collect BASIC statistical information TYPICAL: collect most statistical information (default settings of the database) ALL: Collect ALL statistical information. Statistical information includes: Row statistical information (user_tables ): number of rows (NUM_ROWS), number of BLOCKS (BLOCKS), average length of rows (AVG_ROW_LEN), and column statistics (user_tab_columns): number of unique values in the column NUM_DISTINCT ), number of NULL values (NUM_NULLS), data distribution (HISTOGRAM), index statistics (user_index): -- number of leaf blocks (LEAF_BLOCKS), level (BLEVEL), clustering factor (CLUSTERING_FACTOR ); the collection time of statistical information is scheduled in the time window!

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/1T240F50-0.jpg "border =" 0 "alt =" "/>

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/1T2406254-1.jpg "border =" 0 "alt =" "/>

I. 10 Gb disable automatic statistics collection tasks

 
 

  
  
begin 
  
  
 DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB'); 
  
  
end; 
 
 

Ii. Disable Automatic statistics collection tasks at 11 GB.

 
 

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

Query

 
 

  
  
SQL> select client_name ,status from dba_autotask_client; 
  
  
 
  
  
CLIENT_NAME                                                      STATUS 
  
  
 
  
  
---------------------------------------------------------------- -------- 
  
  
 
  
  
auto optimizer stats collection                                  DISABLED 
  
  
 
  
  
auto space advisor                                               ENABLED 
  
  
 
  
  
sql tuning advisor                                               ENABLED 
 
 

3. Collect statistical information manually. The sampling speed is 10% and the degree of parallelism is 8. The METHOD_OPT option indicates the index column distribution and generates a histogram.

 
 

  
  
BEGIN 
  
  
dbms_stats.gather_schema_stats( 
  
  
ownname=>'HR', 
  
  
METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE SKEWONLY', 
  
  
CASCADE=>TRUE, 
  
  
ESTIMATE_PERCENT=>10, 
  
  
DEGREE=>8); 
  
  
END; 
 
 

4. view the statistical information of a table or index

 
 

  
  
select table_name,  
  
  
       last_analyzed,  
  
  
       num_rows,  
  
  
       avg_row_len,  
  
  
       row_movement， 
  
  
       monitoring 
  
  
from dba_tables 
  
  
where owner = 'HR'; 
  
  
 
  
  
select index_name, 
  
  
       table_name, 
  
  
       blevel, 
  
  
       status, 
  
  
       clustering_factor, 
  
  
       last_analyzed, 
  
  
       num_rows, 
  
  
       leaf_blocks, 
  
  
       distinct_keys, 
  
  
       avg_leaf_blocks_per_key 
  
  
from dba_indexes 
  
  
where owner = 'HR'; 
 
 

V. Export Statistics

 
 

  
  
begin 
  
  
  dbms_stats.create_stat_table(ownname => 'HR', 
  
  
  stattab => 'HR_STAT_BAK'); 
  
  
end; 
  
  
 
  
  
begin  
  
  
  dbms_stats.export_schema_stats(ownname => 'HR', 
  
  
  stattab => 'HR_STAT_BAK', 
  
  
  statid => 'N1', 
  
  
  statown => 'HR'); 
  
  
end; 
  
  
 
  
  
select * from HR_STAT_BAK； 
 
 

6. Delete statistics

 
 

  
  
begin 
  
  
  dbms_stats.delete_schema_stats(ownname => 'HR'); 
  
  
end; 
 
 

7. import statistics

 
 

  
  
begin 
  
  
  dbms_stats.import_schema_stats(ownname => 'HR', 
  
  
  stattab => 'HR_STAT_BAK', 
  
  
  statid => 'N1', 
  
  
  statown => 'HR'); 
  
  
end; 
 
 

8. Import the HR_STAT_BAK table to other oracle servers through expdp/impdp.

 
 

  
  
SQL> exec dbms_stats.upgrade_stat_table ('hr', 'hr _ STAT_BAK ');
  
  
The PL/SQL process is successfully completed.
  
  
 
  
  
SQL> EXEC dbms_stats.import_schema_stats (ownname => 'hr', stattab => 'hr _ STAT_BAK ', statid => 'n1', statown => 'hr ');
  
  
The PL/SQL process is successfully completed.
 
 

9. Lock and unlock statistics

 
 

  
  
1: Locked statistics
  
  
Begin
  
  
Dbms_stats.lock_schema_stats (ownname => 'hr ');
  
  
End;
  
  
 
  
  
2: query which tables or indexes are locked.
  
  
Select * from user_tab_statistics where stattype_locked = 'all ';
  
  
Select * from user_ind_statistics where stattype_locked = 'all ';
  
  
 
  
  
3: If statistics are collected under the locking condition, the following error occurs:
  
  
ERROR at line 1:
  
  
ORA-20005: object statistics are locked (stattype = ALL)
  
  
ORA-06512: at "SYS. DBMS_STATS", line 20337
  
  
ORA-06512: at "SYS. DBMS_STATS", line 20360
  
  
ORA-06512: at line 1
  
  
 
  
  
4: Unlock statistics
  
  
Begin
  
  
Dbms_stats.lock_schema_stats (ownname => 'hr ');
  
  
End;
 
 

 

 

This article is from the "Bo Yue" blog and will not be reproduced!