[案例] 字元型欄位統計資訊只對前32位進行統計

westzq1984發表於2012-10-01
系統一個SQL緩慢,從執行計劃來看,SQL走了全表掃描
但是SQL很簡單,就是FLOW_INSTANCE_ID等於1個值,就取一條資料出來。並且該列上是存在索引的
統計資訊也是很新的,從統計資訊看,SQL不可能去選擇全表掃描

****************************************************************************************                                                 
CURSOR                                                                                                                                   
****************************************************************************************                                                 
SQL_ID  93vkutmkygmwg, child number 4                                                                                                    
-------------------------------------                                                                                                    
select flowinstan0_.FLOW_ID as FLOW_ID, flowinstan0_.FLOW_INSTANCE_ID as                                                                 
FLOW_INS2_, flowinstan0_.STS as STS, flowinstan0_.STS_DATE as STS_DATE,                                                                  
flowinstan0_.TEMPLATE_FLAG as TEMPLATE5_, flowinstan0_.APPL_DATE as                                                                      
APPL_DATE, flowinstan0_.COMPL_DATE as COMPL_DATE, flowinstan0_.USE_FLAG as                                                               
USE_FLAG, flowinstan0_.ORDER_ID as ORDER_ID from FLOW_INSTANCE                                                                           
flowinstan0_ where (flowinstan0_.FLOW_INSTANCE_ID=:1 )                                                                                   
                                                                                                                                         
Plan hash value: 705113994                                                                                                              
                                                                                                                                        
-----------------------------------------------------------------------------------                                                     
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |                                                     
-----------------------------------------------------------------------------------                                                     
|   0 | SELECT STATEMENT  |               |       |       | 16505 (100)|          |                                                     
|*  1 |  TABLE ACCESS FULL| FLOW_INSTANCE |   621K|    52M| 16505   (1)| 00:03:19 |                                                     
-----------------------------------------------------------------------------------                                                     
                                                                                                                                        
Predicate Information (identified by operation id):                                                                                     
---------------------------------------------------                                                                                     
                                                                                                                                        
   1 - filter("FLOWINSTAN0_"."FLOW_INSTANCE_ID"=:1)                                                                                     
                                                                                                                                        
                                                                                                                                        
23 rows selected.                                                                                                                       
                                                                                                                                        
                                                                                                                                        
****************************************************************************************                                                
SQL STATS                                                                                                                               
****************************************************************************************                                                
                                                                                                                                        
         CPU      ELAPSED         DISK       BUFFER         ROWS                 PARSE                                                  
        TIME         TIME        READS         GETS    PROCESSED      FETCHES    CALLS    SORTS         EXEC                            
------------ ------------ ------------ ------------ ------------ ------------ -------- -------- ------------                            
############ ############  323,771,818 ############      819,410      825,145  825,637        0      825,639                            
                                                                                                                                        
                                                                                                                                        
         CPU          ELA         DISK          GET         ROWS         ROWS                                                           
    PRE EXEC     PRE EXEC     PRE EXEC     PRE EXEC     PRE EXEC  PRE FETCHES         EXEC                                              
------------ ------------ ------------ ------------ ------------ ------------ ------------                                              
     503,471    2,112,582          392        1,802            1            1      825,639                                              
                                                                                                                                        
                                                                                                                                        
         CPU          ELA         DISK          GET         ROWS         ROWS                       PLAN   CHILD USER                   
    PRE EXEC     PRE EXEC     PRE EXEC     PRE EXEC     PRE EXEC  PRE FETCHES         EXEC    HASH VALUE  NUMBER NAME                   
------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------- ------- ----------             
     457,500    3,912,946          995       75,292            1            1          120     705113994       4 SP                     
                                                                                                                                        
                                                                                                                                        
****************************************************************************************                                                
TABLES                                                                                                                                  
****************************************************************************************                                                
                                                                                                                                        
           TABLE               BUFFER                       NUM                     EMPTY          AVG          AVG LAST                
OWNER      NAME            LOG POOL    DEGREE PART         ROWS       BLOCKS       BLOCKS        SPACE      ROW_LEN ANALYZED            
---------- --------------- --- ------- ------ ---- ------------ ------------ ------------ ------------ ------------ --------            
SP         FLOW_INSTANCE   YES DEFAULT 1      NO      2,869,680       85,322           22        1,623           97 20120927            
                                                                                                                                        
                                                                                                                                        
****************************************************************************************                                                
INDEX INFO                                                                                                                              
****************************************************************************************                                                
                                                                                                                                        
TABLE      TABLE           Index                                    COLUMN           Col                                                
OWNER      NAME            Name                           Unique    NAME             Pos DESC                                           
---------- --------------- ------------------------------ --------- --------------- ---- ----                                           
SP         FLOW_INSTANCE   IDX_FLOW_INSTANCE_1            NONUNIQUE ORDER_ID           1 ASC                                            
                           IDX_FLOW_INSTANCE_FII          NONUNIQUE FLOW_INSTANCE_I    1 ASC                                            
                                                                    D                                                                   
                                                                                                                                        
                           PK_FLOW_INSTANCE               UNIQUE    FLOW_ID            1 ASC                                            
                                                                    FLOW_INSTANCE_I    2 ASC                                            
                                                                    D                                                                   
                                                                                                                                        
                                                                                                                                        
                                                                                                                                        
****************************************************************************************                                                
LITERAL SQL                                                                                                                             
****************************************************************************************                                                
DECLARE                                                                                                                                 
*                                                                                                                                       
ERROR at line 1:                                                                                                                        
ORA-01403: no data found                                                                                                                
ORA-06512: at line 33                                                                                                                   
                                                                                                                                        
                                                                                                                                        
                                                                                                                                        
****************************************************************************************                                                
SQL WAIT HIST                                                                                                                           
****************************************************************************************                                                
                                                                                                                                        
                                                                                 WAIT                                                   
PROGRAM                        EVENT                                       TOTAL CLASS                                                  
------------------------------ ---------------------------------------- -------- ---------------                                        
                               CPU                                        55,969 CPU                                                    
                               read by other session                      19,353 User_IO                                                
                               latch: cache buffers chains                 4,280 Concurrency                                            
                               db file sequential read                     3,865 User_IO                                                
                               db file scattered read                      2,412 User_IO                                                
                               latch free                                  1,034 Other                                                  
                               wait list latch free                            9 Other                                                  
                               latch: shared pool                              1 Concurrency                                            
                               library cache lock                              1 Concurrency                                            
                               latch: In memory undo latch                     1 Concurrency                                            
                                                                                                                                        
10 rows selected.                                                                                                                       
                                                                                                                                        
                                                                                                                                        
****************************************************************************************                                                
TABLE COLUMNS                                                                                                                           
****************************************************************************************                                                
                                                                                                                                        
      TABLE           COLUMN          DATA                                           NUM          NUM          AVG LAST                 
OWNER NAME            NAME            TYPE            NL HIST       DENSITY        NULLS      BUCKETS      COL LEN ANALYZED             
----- --------------- --------------- --------------- -- ----- ------------ ------------ ------------ ------------ --------             
SP    FLOW_INSTANCE   FLOW_ID         VARCHAR2        N  FREQU            0            0           24            5 20120927             
                      FLOW_INSTANCE_I VARCHAR2        N  HEIGH            0            0          254           39 20120927             
                      D                                                                                                                 
                                                                                                                                        
                      ORDER_ID        VARCHAR2        N  NONE             0            0            1           16 20120927             
                      STS             CHAR            Y  FREQU            0            0            4            2 20120927             
                      STS_DATE        DATE            Y  HEIGH            0          120          254            8 20120927             
                      TEMPLATE_FLAG   CHAR            Y  NONE             0    2,869,680            0            1 20120927             
                      APPL_DATE       DATE            Y  HEIGH            0          130          254            8 20120927             
                      COMPL_DATE      DATE            Y  NONE             0      768,830            1            7 20120927             
                      USE_FLAG        CHAR            Y  FREQU            0            0            2            2 20120927             
                      INSERT_MOMENT   TIMESTAMP(6)    N  NONE             0            0            1           11 20120927             
                                                                                                                                        
10 rows selected.                                                                                                                       
                                                                                                                                        
                                                                                                                                        
****************************************************************************************                                                
INDEX STATS                                                                                                                             
****************************************************************************************                                                
                                                                                                                                        
                                          B                                        Average     Average                                  
           Index                       Tree     Leaf     Distinct          NUM Leaf Blocks Data Blocks      Cluster LAST                
OWNER      Name            LOG STATUS Level     Blks         Keys         ROWS     Per Key     Per Key       Factor ANALYZED            
---------- --------------- --- ------ ----- -------- ------------ ------------ ----------- ----------- ------------ --------            
SP         IDX_FLOW_INSTAN YES VALID      3   59,560       91,141    2,745,090           1          28    2,597,090 20120927            
           CE_FII                                                                                                                       
                                                                                                                                        
           IDX_FLOW_INSTAN YES VALID      2   30,800    1,819,234    2,872,140           1           1    2,698,230 20120927            
           CE_1                                                                                                                         
                                                                                                                                        
           PK_FLOW_INSTANC YES VALID      3   61,340    2,895,090    2,895,090           1           1    2,738,820 20120927            
           E                                                                                                                            

先嚐試下sql advisor,結果無任何建議

DBMS_SQLTUNE.REPORT_TUNING_TASK(:MY_TASK_NAME)                                                         
----------------------------------------------------------------------------------------------------   
GENERAL INFORMATION SECTION                                                                            
-------------------------------------------------------------------------------                        
Tuning Task Name   : sqltuning_zq_20120927171516                                                       
Tuning Task Owner  : SYS                                                                               
Scope              : COMPREHENSIVE                                                                     
Time Limit(seconds): 60                                                                                
Completion Status  : COMPLETED                                                                         
Started at         : 09/27/2012 17:15:18                                                               
Completed at       : 09/27/2012 17:15:21                                                               
                                                                                                       
-------------------------------------------------------------------------------                        
Schema Name: SP                                                                                        
SQL ID     : 93vkutmkygmwg                                                                             
SQL Text   : select flowinstan0_.FLOW_ID as FLOW_ID,                                                   
             flowinstan0_.FLOW_INSTANCE_ID as FLOW_INS2_, flowinstan0_.STS as                          
             STS, flowinstan0_.STS_DATE as STS_DATE,                                                   
             flowinstan0_.TEMPLATE_FLAG as TEMPLATE5_, flowinstan0_.APPL_DATE                          
             as APPL_DATE, flowinstan0_.COMPL_DATE as COMPL_DATE,                                      
             flowinstan0_.USE_FLAG as USE_FLAG, flowinstan0_.ORDER_ID as                               
             ORDER_ID from FLOW_INSTANCE flowinstan0_ where                                            
             (flowinstan0_.FLOW_INSTANCE_ID=:1 )                                                       
                                                                                                       
-------------------------------------------------------------------------------                        
There are no recommendations to improve the statement.                                                 
                                                                                                       
-------------------------------------------------------------------------------                        

注意到在FLOW_INSTANCE_ID上,是收集了HEIGH的直方圖的。
由於該SQL在已經沒有變數的值存放,於是找開發人員獲得FLOW_INSTANCE_ID的值Direct_CDMA_PR_iSPP_IVPN_SICHUAN-201209229512416

select flowinstan0_.FLOW_ID as FLOW_ID, flowinstan0_.FLOW_INSTANCE_ID as FLOW_INS2_, flowinstan0_.STS as STS,
flowinstan0_.STS_DATE as STS_DATE, flowinstan0_.TEMPLATE_FLAG as TEMPLATE5_, flowinstan0_.APPL_DATE as APPL_DATE,
flowinstan0_.COMPL_DATE as COMPL_DATE, flowinstan0_.USE_FLAG as USE_FLAG, flowinstan0_.ORDER_ID as ORDER_ID from FLOW_INSTANCE
flowinstan0_ where (flowinstan0_.FLOW_INSTANCE_ID='Direct_CDMA_PR_iSPP_IVPN_SICHUAN-201209229512416' )

這個引數太長,一般對於字元型,ORACLE只在欄位統計資訊中保留前32位。這個值前32位幾乎一模一樣。
也就是說,直方圖是造成該故障的主要原因。

SQL> exec dbms_stats.gather_table_stats('SP','FLOW_INSTANCE',NO_INVALIDATE=>false,DEGREE=>2,ESTIMATE_PERCENT=>10,method_opt=>'for all indexed columns size 1')

PL/SQL procedure successfully completed.

將直方圖刪除後,SQL正常.

為安全起見,為SQL繫結profile

SQL> @staexchange.sql                                                      
Enter value for sql_id1(used to generate sql_text): 93vkutmkygmwg  
Enter value for child_no1 (used to generate sql_text) (0): 3       
Enter value for sql_id2(used to generate sql_hints): 93vkutmkygmwg 
Enter value for child_no2(used to generate sql_hints) (0): 3       
Enter value for profile_name (PROF_sqlid_planhash):                
Enter value for category (DEFAULT):                                
Enter value for force_matching (FALSE): true                       
SQL Profile PROF_93vkutmkygmwg_4040583430 created.         

檢視正常的執行計劃
              
SQL_ID  93vkutmkygmwg, child number 0                                                                   
-------------------------------------                                                                   
select flowinstan0_.FLOW_ID as FLOW_ID, flowinstan0_.FLOW_INSTANCE_ID as FLOW_INS2_,                    
flowinstan0_.STS as STS, flowinstan0_.STS_DATE as STS_DATE, flowinstan0_.TEMPLATE_FLAG as               
TEMPLATE5_, flowinstan0_.APPL_DATE as APPL_DATE, flowinstan0_.COMPL_DATE as COMPL_DATE,                 
flowinstan0_.USE_FLAG as USE_FLAG, flowinstan0_.ORDER_ID as ORDER_ID from FLOW_INSTANCE                 
flowinstan0_ where (flowinstan0_.FLOW_INSTANCE_ID=:1 )                                                  
                                                                                                        
Plan hash value: 4040583430                                                                             
                                                                                                        
-----------------------------------------------------------------------------------------------------   
| Id  | Operation                   | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |   
-----------------------------------------------------------------------------------------------------   
|   0 | SELECT STATEMENT            |                       |       |       |     5 (100)|          |   
|   1 |  TABLE ACCESS BY INDEX ROWID| FLOW_INSTANCE         |     1 |    88 |     5   (0)| 00:00:01 |   
|*  2 |   INDEX RANGE SCAN          | IDX_FLOW_INSTANCE_FII |     1 |       |     4   (0)| 00:00:01 |   
-----------------------------------------------------------------------------------------------------   
                                                                                                        
Predicate Information (identified by operation id):                                                     
---------------------------------------------------                                                     
                                                                                                        
   2 - access("FLOWINSTAN0_"."FLOW_INSTANCE_ID"=:1)                                                     
                                                                                                        
Note                                                                                                    
-----                                                                                                   
   - SQL profile "PROF_93vkutmkygmwg_4040583430" used for this statement                                
                                                                                                        
                                                                                                        
27 rows selected.                                                                                       
        

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

相關文章