优化器统计信息

botang

1. select  
   
2.    dbms_stats.get_prefs(pname => 'STALE_PERCENT')
   
3.    
   
4. from dual;
   
5. 
   
6. begin
   
7.     dbms_stats.set_global_prefs(pname =>  'STALE_PERCENT',
   
8.     pvalue => 15);
   
9.    end;
   
10. 
    
11. 
    
12. 
    
13. begin
    
14.    dbms_stats.set_table_prefs(ownname => 'HR',
    
15.    tabname => 'EMPLOYEES',
    
16.    pname => 'STALE_PERCENT',
    
17.    pvalue => 13);
    
18. end;
    
19. 
    
20. 
    
21. select  * from dba_tab_stat_prefs;
    
22.    

复制代码

1. select  * from dba_tab_stat_prefs;
   
2. 
   
3. select  * from dba_tab_pending_stats;
   
4. 
   
5. begin
   
6.    dbms_stats.set_table_prefs('STATS',tabname => 'TABJFV',pname => 'PUBLISH',
   
7.    pvalue => 'FALSE');
   
8. end;
   
9. 
   
10. select  * from dba_tab_stat_prefs;
    
11. 
    
12. select t.num_rows,t.last_analyzed  from dba_tables t where t.table_name='TBJFV';
    
13. 
    
14. select i.last_analyzed from dba_indexes i where i.table_name='TABJFV';
    
15. 
    
16.   
    
17. select * from dba_tab_col_statistics tcs
    
18.   where tcs.table_name='TABJFV';
    
19. 
    
20. begin
    
21.    dbms_stats.gather_table_stats('STATS','TABJFV',estimate_percent => 100,
    
22.    method_opt => 'for all columns size 1 for columns c2 size 254 for columns c3 size 254');
    
23.   end;
    
24. 
    
25. -------
    
26. 
    
27. select  * from dba_tab_pending_stats;
    
28. 
    
29. select  * from dba_col_pending_stats;
    
30. 
    
31. 
    
32. -----------------
    
33. 
    
34. select * from dba_stat_extensions;
    
35. 
    
36. select dbms_stats.create_extended_stats('STATS','TABJFV','(c1,c2)' )
    
37.   from dual;
    
38.    
    
39. begin
    
40.     dbms_stats.gather_table_stats ('STATS','TABJFV',
    
41.      method_opt =>'for columns  (c1,c2)  size 254');
    
42.   end;
    
43. 
    
44. 
    
45.   select * from dba_tab_col_statistics tcs
    
46.   where tcs.table_name='TABJFV';
    
47. 
    
48. ------------
    
49.   select  * from dba_col_pending_stats;
    
50.   -----------
    
51.    begin
    
52.    dbms_stats.gather_table_stats('STATS','TABJFV',estimate_percent => 100,
    
53.    method_opt => 'for all columns size 1 for columns c2 size 254 for columns c3 size 254');
    
54.   end;
    
55.   ----
    
56.   
    
57.   begin
    
58.     dbms_Stats.publish_pending_stats(ownname => 'STATS',tabname => 'TABJFV');
    
59.    end;
    
60.    
    
61.    ----
    
62.    begin
    
63.    dbms_stats.set_table_prefs('STATS',tabname => 'TABJFV',pname => 'PUBLISH',
    
64.    pvalue => 'TRUE');
    
65. end;
    
66.   

复制代码