oracle10g中部分檢視查詢非常‘慢“問題解決

      在檢視部分檢視中，有時要等很長時間才能出來結果，例如：

              案例1：
                       SQL>select distinct owner from v$access;
                        
                                   Elapsed: 00:02:43.51
            
                案例2：
                         SQL>select *  from dba_extents
                                  where file_id = 6 and 3348 between block_id and
                                  block_id + blocks - 1
                
                            Elapsed: 00:00:24.45

          造成該原因的是fixed table沒有進行統計分析，具體步驟如下：

             1、通過以下指令碼進行確認：

                      SQL> select VIEW_DEFINITION from
                                  v$fixed_view_definition
                                  where view_name='GV$ACCESS';
                   
                             使用x$ksuse ,x$kglob , x$kgldp ,x$kgllk
       
                        SQL>select dbms_metadata.get_ddl('VIEW', 'DBA_EXTENTS')
                                   from dual;
 
                              其中使用了 x$ktfbue       

             2、檢視是否fixed table是否進行統計分析
                   
                          SQL>select count(*) from sys.tab_stats$;
                                      COUNT(*)
                                      ----------
                                           0
                        說明：未進行統計分析

             3、執行統計分析
                           SQL>connect /as sysdba
                           SQL> exec dbms_stats.gather_fixed_objects_stats(null);
                            SQL>select count(*) from sys.tab_stats$;
                                        COUNT(*)
                                         ----------
                                              579
              4、檢視以上的fixed table是否都已經被統計分析
                         
                           SQL>select num_rows, last_analyzed from user_tab_statistics
                                      where table_name = 'X$KTFBUE';

                                     NUM_ROWS LAST_ANAL
                                          ---------- ---------
                
                             SQL>select num_rows, last_analyzed from user_tab_statistics
                                          where table_name = 'X$KGLDP';

                                         NUM_ROWS LAST_ANAL
-                                        --------- ---------
                                                868 14-JUN-09
                        
                             說明： 發覺X$KTFBUE並未進行統計分析，造成該問題的主要
                                          原因是oracle10g BUG 5259025

             5、需要單獨對這張fixed_table進行統計分析
                             
                             SQL>exec DBMS_STATS.GATHER_TABLE_STATS
                                         ('SYS', 'X$KTFBUE');
                                  
                              SQL>select num_rows, last_analyzed
                                         from user_tab_statistics
                                         where table_name = 'X$KTFBUE';

                                          NUM_ROWS LAST_ANAL
                                          ---------- ---------
                                            4186        14-JUN-09 

          重新測試：
       
           SQL>select /*+ gather_plan_statistics */ distinct owner from v$access;

                        Elapsed: 00:00:00.05
                        從2分多提高到不到1秒
 
           SQL>select *  from dba_extents
                        where file_id = 6 and 3348 between block_id and
                        block_id + blocks - 1

                        Elapsed: 00:00:02.59
                        從24秒提高到3秒

備註：
       1、 檢視具體執行計劃：
              SQL>select sid, sql_id from v$session where xxxxxxx
              SQL>set serveroutput on size 1000000
              SQL>select * from
                        dbms_xplan.display_cursor('SQL_ID', '0', 'ALL ALLSTATS'))
         2、Bug   5259025請參看metalink.oracle.com

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/354732/viewspace-606217/，如需轉載，請註明出處，否則將追究法律責任。