系统一个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博客

[案例] 字符型字段统计信息只对前32位进行统计

最新文章