sql monitor生成不了报告& FFS hint不生效两个问题思考

时间:2023-12-28 14:37:14

事情的发生就是这么偶然,一步步的深入才能汲取到更深入的知识~~

-------------------START-------------------------------------------

 

来了一个query running longer than 4hours的邮件,来看看里面有哪些sql:

SID    SERIAL#    INST_ID SQL_ID        Run_in_sec OS_user     MACHINE       SQL_TEXT          MODULE              EVENT

--------------------------------------------------------------------------------------------------------------------------------------------------------------

195        915          6 7wftzk9rvj3kn      35079 snema1               NIKEW7P4V196

select * from ntcom.nc_order_item_fact_v where transaction_date_key in '20160529' and shipping_count

ry='-1'                                          SQL Developer                                          PX Deq: Execution Msg

1710         19          3 cygs51q4a5tm3      22600 apan11

NKE-WIN-CTX-P86

SELECT /*+PARALLEL(8)*/ HDR1.ORDER_NO, TO_CHAR(hdr1.order_date,'dd-Mon-yy')

SQL Developer

PX Deq: Execution Msg

好的,先来看看第一个sql为什么跑了这么久:

先看看sql的状态:

select status,sid,serial#,event,username from gv$session where sql_id='7wftzk9rvj3kn' and status='ACTIVE';

STATUS          SID    SERIAL# EVENT                                                            USERNAME

-------- ---------- ---------- ---------------------------------------------------------------- ------------------------------

ACTIVE            9        799 PX Deq: Execution Msg                                            DTC_REPORTING

ACTIVE           74        549 PX Deq Credit: send blkd                                         DTC_REPORTING

ACTIVE          132        769 PX Deq: Execution Msg                                            DTC_REPORTING

ACTIVE          195        915 PX Deq: Execution Msg                                            DTC_REPORTING

咦,怎么只有4个session,好像没看到QC进程相关的信息,因为如上几个等待事件一般都是发生在parallel进程上的。

再继续看看QC进程是哪个:

SELECT QCSID, SID, INST_ID "Inst", SERVER_GROUP "Group", SERVER_SET "Set",

DEGREE "Degree", REQ_DEGREE "Req Degree"

FROM GV$PX_SESSION where inst_id=6 and sid=9 ORDER BY QCSID, QCINST_ID, SERVER_GROUP, SERVER_SET;

QCSID        SID       Inst      Group        Set     Degree Req Degree

---------- ---------- ---------- ---------- ---------- ---------- ----------

139          9          6          1          1          4         48

看看QC以及parallel processes信息:

select * from gv$PX_SESSION where QCSID=139;

INST_ID SADDR                   SID    SERIAL#      QCSID  QCSERIAL#  QCINST_ID SERVER_GROUP SERVER_SET    SERVER#     DEGREE REQ_DEGREE

---------- ---------------- ---------- ---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ----------

6 00000017D13A1B70          9        799        139        267          6            1          1          1          4         48

6 00000017913BB868         74        549        139        267          6            1          1          2          4         48

6 00000017B13FACE8        132        769        139        267          6            1          1          3          4         48

6 00000017B14164C8        195        915        139        267          6            1          1          4          4         48

6 00000017B13F7C08        139        267        139

看下QC以及parallel processes的等待事件以及更多信息:

SELECT px.QCSID,px.SID "SID", p.PID, p.SPID "SPID", px.INST_ID "Inst",

px.SERVER_GROUP "Group", px.SERVER_SET "Set",s.last_call_et,

px.DEGREE "Degree", px.REQ_DEGREE "Req Degree", w.event "Wait Event"

FROM GV$SESSION s, GV$PX_SESSION px, GV$PROCESS p, GV$SESSION_WAIT w

WHERE s.sid (+) = px.sid AND s.inst_id (+) = px.inst_id AND

s.sid = w.sid (+) AND s.inst_id = w.inst_id (+) AND

s.paddr = p.addr (+) AND s.inst_id = p.inst_id (+) and px.qcsid=139

ORDER BY DECODE(px.QCINST_ID,  NULL, px.INST_ID,  px.QCINST_ID), px.QCSID,

DECODE(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP), px.SERVER_SET, px.INST_ID;

QCSID        SID        PID SPID                           Inst      Group        Set LAST_CALL_ET     Degree Req Degree Wait Event

---------- ---------- ---------- ------------------------ ---------- ---------- ---------- ------------ ---------- ---------- ----------------------------------------------------------------

139        139        146 3036                              6                              29160                       SQL*Net message from client

139         74         49 5263                              6          1          1        29171          4         48 PX Deq Credit: send blkd

139        195         51 5267                              6          1          1        29171          4         48 PX Deq: Execution Msg

139          9         48 5261                              6          1          1        29171          4         48 PX Deq: Execution Msg

139        132         50 5265                              6          1          1        29171          4         48 PX Deq: Execution Msg

看看QC进程的状态:

select sid,serial#,status,event,username from v$session where sid=139;

SID    SERIAL# STATUS   EVENT                                                            USERNAME

---------- ---------- -------- ---------------------------------------------------------------- ------------------------------

139        267 INACTIVE SQL*Net message from client                                      DTC_REPORTING

已经inactive了。所以原因差不多就是用户在客户端kill掉了这个session,但是并行子进程还active,所以要去kill掉这些子进程,因为它们还在占用这数据库资源,如下可以看到,这些并行子进程还是in use状态,所以及时清理掉这些~

SQL> select * from v$px_process where sid in (select sid from gv$PX_SESSION where QCSID=139);

SERV STATUS           PID SPID                            SID    SERIAL#

---- --------- ---------- ------------------------ ---------- ----------

P000 IN USE            48 5261                              9        799

P001 IN USE            49 5263                             74        549

P002 IN USE            50 5265                            132        769

P003 IN USE            51 5267                            195        915

接下来看第二个long running sql:

也先按照上面的思路来分析:

SQL> select sid,serial#,username,event from gv$session where sql_id='cygs51q4a5tm3' and status='ACTIVE';

SID    SERIAL# USERNAME                       EVENT

---------- ---------- ------------------------------ ----------------------------------------------------------------

952        443 APAN11                         PX Deq: Execute Reply

1710         19 APAN11                         PX Deq: Execution Msg

1770         81 APAN11                         gc buffer busy acquire

1897       1051 APAN11                         db file sequential read

1956        491 APAN11                         gc buffer busy acquire

2083       8917 APAN11                         PX Deq: Table Q Normal

2145        701 APAN11                         PX Deq: Table Q Normal

2336        435 APAN11                         PX Deq: Table Q Normal

2400       1645 APAN11                         PX Deq: Table Q Normal

这里可以看到QC进程等待事件PX Deq: Execute Reply并且子进程还在读数据块,说明这个sql还是执行当中,那就来深入分析下。

SELECT QCSID, SID, INST_ID "Inst", SERVER_GROUP "Group", SERVER_SET "Set",

DEGREE "Degree", REQ_DEGREE "Req Degree"

FROM GV$PX_SESSION where inst_id=3 and sid=952 ORDER BY QCSID, QCINST_ID, SERVER_GROUP, SERVER_SET;

QCSID        SID       Inst      Group        Set     Degree Req Degree

---------- ---------- ---------- ---------- ---------- ---------- ----------

952        952          3

select * from gv$PX_SESSION where QCSID=952;

INST_ID SADDR                   SID    SERIAL#      QCSID  QCSERIAL#  QCINST_ID SERVER_GROUP SERVER_SET    SERVER#     DEGREE REQ_DEGREE

---------- ---------------- ---------- ---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ----------

3 000000139172E548       2083       8917        952        443          3            1          1          1          4          8

3 000000138175A0F8       2145        701        952        443          3            1          1          2          4          8

3 00000013A17AF0E8       2336        435        952        443          3            1          1          3          4          8

3 00000013B17D8868       2400       1645        952        443          3            1          1          4          4          8

3 00000013D1688010       1710         19        952        443          3            1          2          1          4          8

3 00000013B16C59A8       1770         81        952        443          3            1          2          2          4          8

3 00000013C16F8748       1897       1051        952        443          3            1          2          3          4          8

3 0000001381707958       1956        491        952        443          3            1          2          4          4          8

3 00000013C155C128        952        443        952

SELECT px.QCSID,px.SID "SID", p.PID, p.SPID "SPID", px.INST_ID "Inst",

px.SERVER_GROUP "Group", px.SERVER_SET "Set",s.last_call_et,

px.DEGREE "Degree", px.REQ_DEGREE "Req Degree", w.event "Wait Event"

FROM GV$SESSION s, GV$PX_SESSION px, GV$PROCESS p, GV$SESSION_WAIT w

WHERE s.sid (+) = px.sid AND s.inst_id (+) = px.inst_id AND

s.sid = w.sid (+) AND s.inst_id = w.inst_id (+) AND

s.paddr = p.addr (+) AND s.inst_id = p.inst_id (+) and px.qcsid=952

ORDER BY DECODE(px.QCINST_ID,  NULL, px.INST_ID,  px.QCINST_ID), px.QCSID,

DECODE(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP), px.SERVER_SET, px.INST_ID;

QCSID        SID        PID SPID                           Inst      Group        Set LAST_CALL_ET     Degree Req Degree Wait Event

---------- ---------- ---------- ------------------------ ---------- ---------- ---------- ------------ ---------- ---------- ----------------------------------------------------------------

952        952         63 23610                             3                              18362                       PX Deq: Execute Reply

952       2145        178 34466                             3          1          1        18197          4          8 PX Deq: Table Q Normal

952       2336        181 34468                             3          1          1        18197          4          8 PX Deq: Table Q Normal

952       2083        177 34464                             3          1          1        18197          4          8 PX Deq: Table Q Normal

952       2400        182 34470                             3          1          1        18197          4          8 PX Deq: Table Q Normal

952       1710        219 35556                             3          1          2        18197          4          8 PX Deq: Execution Msg

952       1770        220 35558                             3          1          2        18197          4          8 gc buffer busy acquire

952       1897        222 35560                             3          1          2        18197          4          8 gc buffer busy acquire

952       1956        223 35562                             3          1          2        18197          4          8 db file sequential read

做到这里跟上面的第一个sql都还是一样的分析,但这个sql还是在执行中,所以想着用sql monitor来看看执行的一些情况。

先进OEM—>sql monitor来看看:

sql monitor生成不了报告& FFS hint不生效两个问题思考

很奇怪呀,怎么sql monitor里面没这个sql,这个没弄明白,只好先去sqlplus里面试试sql monitor report功能~

SQL> SELECT status, KEY, SID, sql_id, elapsed_time, cpu_time, fetches, buffer_gets,disk_reads FROM v$sql_monitor where status='EXECUTING' and sql_id='cygs51q4a5tm3';

STATUS                     KEY        SID SQL_ID        ELAPSED_TIME   CPU_TIME    FETCHES BUFFER_GETS DISK_READS

------------------- ---------- ---------- ------------- ------------ ---------- ---------- ----------- ----------

EXECUTING           1.9327E+11       1770 cygs51q4a5tm3   1.9926E+10  532833000          0    11080906    1305247

EXECUTING           1.0952E+12       1897 cygs51q4a5tm3   1.9926E+10  515328000          0    11075922    1086451

EXECUTING           1.8254E+12       1956 cygs51q4a5tm3   1.9926E+10  466325000          0    11046250     626473

可以从v$sql_monitor看到相关的信息,于是想生成一个sql monitor report:

SQL> SELECT dbms_sqltune.report_sql_monitor(

sql_id => 'cygs51q4a5tm3',

report_level => 'ALL',

type=>'TEXT')  from dual;

DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_ID=>'CYGS51Q4A5TM3',REPORT_LEVEL=>'ALL',TYPE=>'TEXT')

-----------------------------------------------------------------------------------------------------------

SQL Monitoring Report

生成的是空的,试了好几次,还把text格式改成html格式,也是空的。

于是又看了一下v$sql_monitor:

SQL> SELECT status, KEY, SID, sql_id, elapsed_time, cpu_time, fetches, buffer_gets,disk_reads FROM v$sql_monitor where status='EXECUTING' and sql_id='cygs51q4a5tm3';

STATUS                     KEY        SID SQL_ID        ELAPSED_TIME   CPU_TIME    FETCHES BUFFER_GETS DISK_READS

------------------- ---------- ---------- ------------- ------------ ---------- ---------- ----------- ----------

EXECUTING           1.9327E+11       1770 cygs51q4a5tm3   1.9926E+10  532833000          0    11080906    1305247

EXECUTING           1.0952E+12       1897 cygs51q4a5tm3   1.9926E+10  515328000          0    11075922    1086451

EXECUTING           1.8254E+12       1956 cygs51q4a5tm3   1.9926E+10  466325000          0    11046250     626473

SQL> /

STATUS                     KEY        SID SQL_ID        ELAPSED_TIME   CPU_TIME    FETCHES BUFFER_GETS DISK_READS

------------------- ---------- ---------- ------------- ------------ ---------- ---------- ----------- ----------

EXECUTING           1.9327E+11       1770 cygs51q4a5tm3   2.0228E+10  540060000          0    11210059    1317837

EXECUTING           1.0952E+12       1897 cygs51q4a5tm3   2.0228E+10  522385000          0    11205153    1099241

EXECUTING           1.8254E+12       1956 cygs51q4a5tm3   2.0228E+10  472476000          0    11175484     634022

/

STATUS                     KEY        SID SQL_ID        ELAPSED_TIME   CPU_TIME    FETCHES BUFFER_GETS DISK_READS

------------------- ---------- ---------- ------------- ------------ ---------- ---------- ----------- ----------

EXECUTING           1.9327E+11       1770 cygs51q4a5tm3   2.0402E+10  544447000          0    11270578    1325204

EXECUTING           1.0952E+12       1897 cygs51q4a5tm3   2.0403E+10  526656000          0    11265952    1106658

EXECUTING           1.8254E+12       1956 cygs51q4a5tm3   2.0402E+10  476219000          0    11236001     638965

SELECT to_char(key), status, SID, sql_id, elapsed_time, cpu_time, fetches, buffer_gets,disk_reads FROM v$sql_monitor where status='EXECUTING' and sql_id='cygs51q4a5tm3';

TO_CHAR(KEY)                             STATUS                     SID SQL_ID        ELAPSED_TIME   CPU_TIME    FETCHES BUFFER_GETS DISK_READS

---------------------------------------- ------------------- ---------- ------------- ------------ ---------- ---------- ----------- ----------

193273569471                             EXECUTING                 1770 cygs51q4a5tm3   2.0955E+10  570281000          0    11718231    1387668

1095216701633                            EXECUTING                 1897 cygs51q4a5tm3   2.0956E+10  550922000          0    11713329    1160543

1825361141950                            EXECUTING                 1956 cygs51q4a5tm3   2.0955E+10  495977000          0    11683605     664742

可以看到buffer_gets和disk_reads在变动,说明sql还在跑

GV$SQL_MONITOR视图包含了语句执行时的监控数据。当有多个会话在运行相同的语句时,这个视图会有多个记录与之对应,所以请确保使用了正确的搜索过滤条件来获得你所关注的SQL执行情况。例如,应该注意SID和INST_ID是不是你正在寻找的会话,对于并行执行则是PX_QCSID和PX_QCINST_ID,如果在诊断一条当前正在运行的查询,则要检查列status是否显示为executing状态。

GV$SQL_PLAN_MONITOR 视图包含了执行计划每一行的性能指标,这些指标会被实时监控和更新。

来看看v$sql_plan_monitor这个sql的执行情况,可以根据outpu_rows大致判断执行的进度

select  plan_line_id, plan_operation, plan_options starts, output_rows

from v$sql_plan_monitor

where key=193273569471;

PLAN_LINE_ID PLAN_OPERATION                 STARTS                         OUTPUT_ROWS

------------ ------------------------------ ------------------------------ -----------

0 SELECT STATEMENT                                                        0

1 TEMP TABLE TRANSFORMATION                                               0

2 PX COORDINATOR                                                          0

3 PX SEND                        QC (RANDOM)                              0

4 LOAD AS SELECT                                                          0

5 FILTER                                                                  0

6 NESTED LOOPS                                                            0

7 NESTED LOOPS                                                            0

8 BUFFER                         SORT                                     0

9 PX RECEIVE                                                              0

10 PX SEND                        ROUND-ROBIN                              0

11 TABLE ACCESS                   BY INDEX ROWID                           0

12 INDEX                          RANGE SCAN                               0

13 INDEX                          RANGE SCAN                               0

14 TABLE ACCESS                   BY INDEX ROWID                           0

15 PX COORDINATOR                                                          0

16 PX SEND                        QC (ORDER)                               0

17 SORT                           ORDER BY                                 0

18 PX RECEIVE                                                              0

19 PX SEND                        RANGE                                    0

20 FILTER                                                                  0

21 HASH JOIN                      BUFFERED                                 0

22 PX RECEIVE                                                              0

23 PX SEND                        HASH                                     0

24 HASH JOIN                      OUTER BUFFERED                           0

25 PX RECEIVE                                                              0

26 PX SEND                        HASH                                     0

27 HASH JOIN                                                               0

28 PX RECEIVE                                                              0

29 PX SEND                        BROADCAST                                0

30 HASH JOIN                      BUFFERED                                 0

31 PX RECEIVE                                                              0

32 PX SEND                        BROADCAST                               64

33 PX BLOCK                       ITERATOR                                16

34 TABLE ACCESS                   FULL                                    16

35 HASH JOIN                                                               0

36 JOIN FILTER                    CREATE                                   0

37 PX RECEIVE                                                              0

38 PX SEND                        HASH                                     0

39 PX BLOCK                       ITERATOR                                 0

40 TABLE ACCESS                   BY INDEX ROWID                           0

41 BITMAP CONVERSION              TO ROWIDS                             5994

42 BITMAP AND                                                              1

43 BITMAP MERGE                                                           24

44 BITMAP KEY ITERATION                                                  124

45 INDEX                          FAST FULL SCAN                          88

46 BITMAP CONVERSION              FROM ROWIDS                            124

47 INDEX                          RANGE SCAN                       259599512

48 BITMAP MERGE                                                            1

49 BITMAP KEY ITERATION                                                 1597

50 TABLE ACCESS                   FULL                                230084

51 BITMAP CONVERSION              FROM ROWIDS                           1597

52 INDEX                          RANGE SCAN                         1565112

53 PX RECEIVE                                                              0

54 PX SEND                        HASH                                     0

55 JOIN FILTER                    USE                                      0

56 PX BLOCK                       ITERATOR                                 0

57 TABLE ACCESS                   FULL                                     0

58 PX BLOCK                       ITERATOR                                 0

59 TABLE ACCESS                   FULL                                     0

60 PX RECEIVE                                                              0

61 PX SEND                        HASH                                     0

62 PX BLOCK                       ITERATOR                                 0

63 TABLE ACCESS                   FULL                                     0

64 PX RECEIVE                                                              0

65 PX SEND                        HASH                                     0

66 PX BLOCK                       ITERATOR                                 0

67 TABLE ACCESS                   FULL                                     0

68 rows selected.

看的也差不多了,首先先没想去分析这个sql为何这么慢,后面再去分析,只是当时在想,为什么OEM sql monitor没有这条sql,然后sqlplus

也生成不了sql monitor report,因为一般自己去分析正在long running的sql都是这么分析的,怎么这条sql就不行。

于是自己找了一些资料也温习了sql monitor的概念,最后也没啥发现,只能安慰自己没有也得习惯,毕竟主要是分析sql的执行计划。

顺带先说下自己去分析上面疑问的过程:

什么SQL会被SQL MONITORING监控到

对于绝大多数OLTP系统来说,SQL相对比较简单,每次的运行时间都非常快,绝大部分SQL的响应时间都应该在10MS以下,优化的复杂度也比较低,SQL MONITORING功能的出现并不是为了帮助DBA发现、诊断OLTP SQL的性能问题,而是为了加快DBA优化数据仓库类SQL的效率,这些SQL是偏OLAP系统的,特点是并发量低、运行时间久、SQL复杂度高。满足以下条件的任意SQL都会被SQL MONITORING监控到:

  • 如果串行执行的SQL,消耗的CPU时间或IO时间超过5秒,那么这些SQL 将会被监控到,通过修改隐含参数_sqlmon_threshold可以控制这一行为,默认为5秒,如果设置为0将关闭SQL MONITORING功能。注意我这里提到的是SQL消耗的CPU时间或IO时间,而不是SQL的执行时间,之所以需要限制CPU时间或IO时间是为了防止数据库某一时刻如果有大量lock/latch的话,那么将有大量的SQL满足5秒执行时间的条件,而SQL监控本身比较消耗资源,需要拷贝运行时的性能统计信息到SGA,每一个受监控的SQL都有一个单独的内存结构,在11G可能会导致大量的latch竞争,CPU飙高,12C对这个问题做了优化不存在该问题了。如果你发现你的SQL运行时间明显超过了5秒但是却没被SQL MONITORING监控到,那么你该仔细检查是否是由于SQL本身消耗的CPUIO并没有超过5秒(由于锁、网络?)
  • 并行执行的SQL将全部被监控到,不需要等待CPUIO时间超过5秒。对于这一点也比较好理解,一般并行查询的SQL都是报表类或比较重的任务类的SQL,因此会自动打开SQL MONITORING的功能。
  • 增加HINT /+ monitor /SQL会立即开启SQL MONITORING功能。

除了以上条件外,你还需要检查一些系统参数是否设置正确:

  • statistics_level需要为TYPICAL(默认)或者ALL.
  • control_management_pack_access需要为DIAGNOSTIC+TUNING(默认)

以及看到了超过300行的执行计划不会被monitor到,如下mos doc:

How to Monitor SQL Statements with Large Plans Using Real-Time SQL Monitoring? (Doc ID 1613163.1)

The threshold for plan monitoring is controlled by a hidden parameter _sqlmon_max_planlines (which has a default of 300). This can be changed to a higher value, for example 500 using a command like:

SQL> alter system set "_sqlmon_max_planlines"=500 scope=both;

This would mean that explain plans with up 500 lines in them would now be monitored.

以及关于sql monitor设置的系统隐藏参数:

http://www.askmaclean.com/archives/11g%E6%96%B0%E5%8A%A8%E6%80%81%E6%80%A7%E8%83%BD%E8%A7%86%E5%9B%BEvsql_monitorvsql_plan_monitor.html

看了这些,根据这些查看了下数据库的环境都是OK的,并且觉得还是有点无法理解,因为这条sql在v$sql_monitor里面有了,但是就是在OEM看不到,另外在sqlplus也生成不了报告,于是只好作罢~~

当然最后在top activity里面看到了这条sql,但是sql monitor按钮是灰色的,如下图:

sql monitor生成不了报告& FFS hint不生效两个问题思考

sql monitor生成不了报告& FFS hint不生效两个问题思考

sql monitor生成不了报告& FFS hint不生效两个问题思考

Note:后面再某篇文章看到了相关的原因,我觉得也是因为这个,而导致的这个

以下出自精通oracle sql的第六章关于sql monitor的说法:

Sql监控报告也有一些局限性。尽管sql语句的监控室默认进行的,但通过监控得到的数据量也是有限制的。隐藏参数_sqlmon_max_plan控制分配给sql监控信息的内存区域大小,这个参数的默认值为20*cpu数,因此如果在数据库中有很多不同的sql语句执行,当想要查看某条sql语句的监控数据时候,它很有可能已经被清除了内存,因为新的sql监控数据需要存储空间。当然,如果想要将sql监控数据保留更长时间的话,可以增加这个参数的值。

只能继续去分析sql为何执行这么慢了,当然先来看看sql以及执行计划把,

Sql:

SELECT

/*+PARALLEL(8) */

hdr1.order_no,

To_char(hdr1.order_date,'dd-Mon-yy') ,

line.order_line_key,

hdr.order_no,

hdr.extn_sap_order_no,

substr(line.shipnode_ke y,0,4) AS plant,

dt.expected_date ,

st.status,

st.status_date,

des.status_name,

line.shipnode_key,

line.carrier_serv ice_code,

line.upc_code,

line.extn_style_number

||'-'

||line.extn_color_number ,

line.extn_size_description,

line.extn_metric_id

FROM            dom.yfs_order_header hdr,

dom.yfs_order_header hdr1,

dom.yfs_order_line line,

dom.yfs_order_release_status st,

dom.yfs_status des,

dom.yfs_order_line line1

left outer join dom.yfs_order_date dt

ON              line1.order_line_key =dt.order_line_key

AND             dt.date_type_id ='EDD'

WHERE           hdr.order_header_key = line.order_header_key

AND             des.status = st.status

AND             des.process_type_key = 'PO_FULFILLMENT'

AND             line.chained_from_order_line_key = line1.order_line_key

AND             line1.order_header_key =hdr1.order_header_key

AND             st.order_line_key = line.o rder_line_key

AND             st.status_quantity > 0

AND             st.status >='1100'

AND             st.status < '3700'

AND             line.line_type != 'NIKEID'

AND             hdr.document_type = '0005'

AND             hdr.enterprise_key = 'NIKEJP'

AND             hdr.order_header_key > '20161001'

AND             hdr.order_header_key < to_char(SYSDATE, 'yyyymmdd')

ORDER BY        st.order_release_status_key;

Sql plan:

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('cygs51q4a5tm3', format=>'ALL'));

Plan hash value: 3329794945

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                                                | Name                           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                                         |                                |       |       |       |  1694K(100)|          |        |      |            |

|   1 |  TEMP TABLE TRANSFORMATION                               |                                |       |       |       |            |          |        |      |            |

|*  2 |   PX COORDINATOR                                         |                                |       |       |       |            |          |        |      |            |

|   3 |    PX SEND QC (RANDOM)                                   | :TQ10001                       |   312K|    51M|       |   150K  (1)| 00:57:47 |  Q1,01 | P->S | QC (RAND)  |

|   4 |     LOAD AS SELECT                                       |                                |       |       |       |            |          |  Q1,01 | PCWP |            |

|*  5 |      FILTER                                              |                                |       |       |       |            |          |  Q1,01 | PCWC |            |

|   6 |       NESTED LOOPS                                       |                                |   312K|    51M|       |   150K  (1)| 00:57:47 |  Q1,01 | PCWP |            |

|   7 |        NESTED LOOPS                                      |                                |   312K|    51M|       |   150K  (1)| 00:57:47 |  Q1,01 | PCWP |            |

|   8 |         BUFFER SORT                                      |                                |       |       |       |            |          |  Q1,01 | PCWC |            |

|   9 |          PX RECEIVE                                      |                                |       |       |       |            |          |  Q1,01 | PCWP |            |

|  10 |           PX SEND ROUND-ROBIN                            | :TQ10000                       |       |       |       |            |          |        | S->P | RND-ROBIN  |

|* 11 |            TABLE ACCESS BY INDEX ROWID                   | YFS_ORDER_HEADER               |   224K|    12M|       | 40445   (1)| 00:15:31 |        |      |            |

|* 12 |             INDEX RANGE SCAN                             | YFS_ORDER_HEADER_I7            | 66271 |       |       | 11245   (1)| 00:04:19 |        |      |            |

|* 13 |         INDEX RANGE SCAN                                 | DNC_YFS_ORDER_LINE_I12         |     1 |       |       |     0   (0)|          |  Q1,01 | PCWP |            |

|* 14 |        TABLE ACCESS BY INDEX ROWID                       | YFS_ORDER_LINE                 |     1 |   117 |       |     1   (0)| 00:00:01 |  Q1,01 | PCWP |            |

|* 15 |   PX COORDINATOR                                         |                                |       |       |       |            |          |        |      |            |

|  16 |    PX SEND QC (ORDER)                                    | :TQ20009                       |   759K|   260M|       |  1543K  (4)| 09:51:40 |  Q2,09 | P->S | QC (ORDER) |

|  17 |     SORT ORDER BY                                        |                                |   759K|   260M|   282M|  1543K  (4)| 09:51:40 |  Q2,09 | PCWP |            |

|  18 |      PX RECEIVE                                          |                                |   759K|   260M|       |  1543K  (4)| 09:51:37 |  Q2,09 | PCWP |            |

|  19 |       PX SEND RANGE                                      | :TQ20008                       |   759K|   260M|       |  1543K  (4)| 09:51:37 |  Q2,08 | P->P | RANGE      |

|* 20 |        FILTER                                            |                                |       |       |       |            |          |  Q2,08 | PCWC |            |

|* 21 |         HASH JOIN BUFFERED                               |                                |   759K|   260M|       |  1543K  (4)| 09:51:37 |  Q2,08 | PCWP |            |

|  22 |          PX RECEIVE                                      |                                |   759K|   227M|       |  1447K  (4)| 09:14:44 |  Q2,08 | PCWP |            |

|  23 |           PX SEND HASH                                   | :TQ20006                       |   759K|   227M|       |  1447K  (4)| 09:14:44 |  Q2,06 | P->P | HASH       |

|* 24 |            HASH JOIN OUTER BUFFERED                      |                                |   759K|   227M|       |  1447K  (4)| 09:14:44 |  Q2,06 | PCWP |            |

|  25 |             PX RECEIVE                                   |                                |   759K|   192M|       |  1369K  (4)| 08:44:54 |  Q2,06 | PCWP |            |

|  26 |              PX SEND HASH                                | :TQ20004                       |   759K|   192M|       |  1369K  (4)| 08:44:54 |  Q2,04 | P->P | HASH       |

|* 27 |               HASH JOIN                                  |                                |   759K|   192M|       |  1369K  (4)| 08:44:54 |  Q2,04 | PCWP |            |

|  28 |                PX RECEIVE                                |                                | 54024 |    11M|       |  1121K  (5)| 07:09:57 |  Q2,04 | PCWP |            |

|  29 |                 PX SEND BROADCAST                        | :TQ20003                       | 54024 |    11M|       |  1121K  (5)| 07:09:57 |  Q2,03 | P->P | BROADCAST  |

|* 30 |                  HASH JOIN BUFFERED                      |                                | 54024 |    11M|       |  1121K  (5)| 07:09:57 |  Q2,03 | PCWP |            |

|  31 |                   PX RECEIVE                             |                                |    40 |  1840 |       |     2   (0)| 00:00:01 |  Q2,03 | PCWP |            |

|  32 |                    PX SEND BROADCAST                     | :TQ20000                       |    40 |  1840 |       |     2   (0)| 00:00:01 |  Q2,00 | P->P | BROADCAST  |

|  33 |                     PX BLOCK ITERATOR                    |                                |    40 |  1840 |       |     2   (0)| 00:00:01 |  Q2,00 | PCWC |            |

|* 34 |                      TABLE ACCESS FULL                   | YFS_STATUS                     |    40 |  1840 |       |     2   (0)| 00:00:01 |  Q2,00 | PCWP |            |

|* 35 |                   HASH JOIN                              |                                | 51323 |  8520K|       |  1121K  (5)| 07:09:56 |  Q2,03 | PCWP |            |

|  36 |                    JOIN FILTER CREATE                    | :BF0000                        | 51323 |  3458K|       |  1121K  (5)| 07:09:53 |  Q2,03 | PCWP |            |

|  37 |                     PX RECEIVE                           |                                | 51323 |  3458K|       |  1121K  (5)| 07:09:53 |  Q2,03 | PCWP |            |

|  38 |                      PX SEND HASH                        | :TQ20001                       | 51323 |  3458K|       |  1121K  (5)| 07:09:53 |  Q2,01 | P->P | HASH       |

|  39 |                       PX BLOCK ITERATOR                  |                                | 51323 |  3458K|       |  1121K  (5)| 07:09:53 |  Q2,01 | PCWC |            |

|* 40 |                        TABLE ACCESS BY INDEX ROWID       | YFS_ORDER_RELEASE_STATUS       | 51323 |  3458K|       |  1121K  (5)| 07:09:53 |  Q2,01 | PCWP |            |

|  41 |                         BITMAP CONVERSION TO ROWIDS      |                                |       |       |       |            |          |  Q2,01 | PCWP |            |

|  42 |                          BITMAP AND                      |                                |       |       |       |            |          |  Q2,01 | PCWP |            |

|  43 |                           BITMAP MERGE                   |                                |       |       |       |            |          |  Q2,01 | PCWP |            |

|  44 |                            BITMAP KEY ITERATION          |                                |       |       |       |            |          |  Q2,01 | PCWP |            |

|* 45 |                             INDEX FAST FULL SCAN         | YFS_STATUS_I1                  |    40 |  1120 |       |     2   (0)| 00:00:01 |  Q2,01 | PCWP |            |

|  46 |                             BITMAP CONVERSION FROM ROWIDS|                                |       |       |       |            |          |  Q2,01 | PCWP |            |

|* 47 |                              INDEX RANGE SCAN            | DNC_YFS_ORDER_RELSE_STATUS_I10 |       |       |       | 29371   (1)| 00:11:16 |  Q2,01 | PCWP |            |

|  48 |                           BITMAP MERGE                   |                                |       |       |       |            |          |  Q2,01 | PCWP |            |

|  49 |                            BITMAP KEY ITERATION          |                                |       |       |       |            |          |  Q2,01 | PCWP |            |

|  50 |                             TABLE ACCESS FULL            | SYS_TEMP_0FDA23106_982D9B77    |   312K|  7636K|       |   139   (0)| 00:00:04 |  Q2,01 | PCWP |            |

|  51 |                             BITMAP CONVERSION FROM ROWIDS|                                |       |       |       |            |          |  Q2,01 | PCWP |            |

|* 52 |                              INDEX RANGE SCAN            | YFS_ORDER_RELEASE_STATUS_I2    |       |       |       |     4   (0)| 00:00:01 |  Q2,01 | PCWP |            |

|  53 |                    PX RECEIVE                            |                                |   312K|    30M|       |   139   (0)| 00:00:04 |  Q2,03 | PCWP |            |

|  54 |                     PX SEND HASH                         | :TQ20002                       |   312K|    30M|       |   139   (0)| 00:00:04 |  Q2,02 | P->P | HASH       |

|  55 |                      JOIN FILTER USE                     | :BF0000                        |   312K|    30M|       |   139   (0)| 00:00:04 |  Q2,02 | PCWP |            |

|  56 |                       PX BLOCK ITERATOR                  |                                |   312K|    30M|       |   139   (0)| 00:00:04 |  Q2,02 | PCWC |            |

|* 57 |                        TABLE ACCESS FULL                 | SYS_TEMP_0FDA23106_982D9B77    |   312K|    30M|       |   139   (0)| 00:00:04 |  Q2,02 | PCWP |            |

|  58 |                PX BLOCK ITERATOR                         |                                |    36M|  1726M|       |   247K  (1)| 01:34:58 |  Q2,04 | PCWC |            |

|* 59 |                 TABLE ACCESS FULL                        | YFS_ORDER_LINE                 |    36M|  1726M|       |   247K  (1)| 01:34:58 |  Q2,04 | PCWP |            |

|  60 |             PX RECEIVE                                   |                                |    14M|   651M|       | 77815   (1)| 00:29:50 |  Q2,06 | PCWP |            |

|  61 |              PX SEND HASH                                | :TQ20005                       |    14M|   651M|       | 77815   (1)| 00:29:50 |  Q2,05 | P->P | HASH       |

|  62 |               PX BLOCK ITERATOR                          |                                |    14M|   651M|       | 77815   (1)| 00:29:50 |  Q2,05 | PCWC |            |

|* 63 |                TABLE ACCESS FULL                         | YFS_ORDER_DATE                 |    14M|   651M|       | 77815   (1)| 00:29:50 |  Q2,05 | PCWP |            |

|  64 |          PX RECEIVE                                      |                                |    21M|   908M|       | 96216   (1)| 00:36:53 |  Q2,08 | PCWP |            |

|  65 |           PX SEND HASH                                   | :TQ20007                       |    21M|   908M|       | 96216   (1)| 00:36:53 |  Q2,07 | P->P | HASH       |

|  66 |            PX BLOCK ITERATOR                             |                                |    21M|   908M|       | 96216   (1)| 00:36:53 |  Q2,07 | PCWC |            |

|* 67 |             TABLE ACCESS FULL                            | YFS_ORDER_HEADER               |    21M|   908M|       | 96216   (1)| 00:36:53 |  Q2,07 | PCWP |            |

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):

-------------------------------------------------------------

1 - SEL$F4B9A2CF

2 - SEL$A2542105

11 - SEL$A2542105 / YFS_ORDER_HEADER@SEL$A2542105

12 - SEL$A2542105 / YFS_ORDER_HEADER@SEL$A2542105

13 - SEL$A2542105 / YFS_ORDER_LINE@SEL$A2542105

14 - SEL$A2542105 / YFS_ORDER_LINE@SEL$A2542105

34 - SEL$F4B9A2CF / DES@SEL$4

40 - SEL$F4B9A2CF / ST@SEL$4

45 - SEL$698B22AD / DES@SEL$698B22AD

50 - SEL$5AAE24F6 / T1@SEL$5AAE24F6

57 - SEL$F4B9A2CF / T1@SEL$237ED24E

59 - SEL$F4B9A2CF / YFS_ORDER_LINE@SEL$3

63 - SEL$F4B9A2CF / DT@SEL$1

67 - SEL$F4B9A2CF / YFS_ORDER_HEADER@SEL$6

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter('20161001'<TO_CHAR(SYSDATE@!,'yyyymmdd'))

5 - filter('20161001'<TO_CHAR(SYSDATE@!,'yyyymmdd'))

11 - filter(("ORDER_HEADER_KEY">'20161001' AND "ORDER_HEADER_KEY"<TO_CHAR(SYSDATE@!,'yyyymmdd')))

12 - access("ENTERPRISE_KEY"='NIKEJP' AND "DOCUMENT_TYPE"='0005')

filter("DOCUMENT_TYPE"='0005')

13 - access("ORDER_HEADER_KEY"="ORDER_HEADER_KEY")

filter(("ORDER_HEADER_KEY">'20161001' AND "ORDER_HEADER_KEY"<TO_CHAR(SYSDATE@!,'yyyymmdd')))

14 - filter("LINE_TYPE"<>'NIKEID')

15 - filter(TO_CHAR(SYSDATE@!,'yyyymmdd')>'20161001')

20 - filter(TO_CHAR(SYSDATE@!,'yyyymmdd')>'20161001')

21 - access("ORDER_HEADER_KEY"="ORDER_HEADER_KEY")

24 - access("ORDER_LINE_KEY"="DT"."ORDER_LINE_KEY")

27 - access("C1"="ORDER_LINE_KEY")

30 - access("DES"."STATUS"="ST"."STATUS")

34 - access(:Z>=:Z AND :Z<=:Z)

filter(("DES"."PROCESS_TYPE_KEY"='PO_FULFILLMENT' AND "DES"."STATUS"<'3700' AND "DES"."STATUS">='1100'))

35 - access("ST"."ORDER_LINE_KEY"="C0")

40 - filter("ST"."STATUS_QUANTITY">0)

45 - filter(("DES"."PROCESS_TYPE_KEY"='PO_FULFILLMENT' AND "DES"."STATUS"<'3700' AND "DES"."STATUS">='1100'))

47 - access("ST"."STATUS"="DES"."STATUS")

filter(("ST"."STATUS"<'3700' AND "ST"."STATUS">='1100'))

52 - access("ST"."ORDER_LINE_KEY"="C0")

57 - access(:Z>=:Z AND :Z<=:Z)

filter(SYS_OP_BLOOM_FILTER(:BF0000,"C0"))

59 - access(:Z>=:Z AND :Z<=:Z)

63 - access(:Z>=:Z AND :Z<=:Z)

filter("DT"."DATE_TYPE_ID"='EDD')

67 - access(:Z>=:Z AND :Z<=:Z)

Note

-----

- dynamic sampling used for this statement (level=7)

- Degree of Parallelism is 8 because of hint

- star transformation used for this statement

看到了数据库优化器对这条sql进行了星型转换,执行计划里面也看的出来有bitmap的相关操作~

自己之前对这条sql优化过,就是采用并行扫全表+hash join的hint来去让它走经典的以资源换时间的并行扫全表操作,10mins左右能跑出来。

但是自己这次还想着能不能再进一步的降低点资源使用或者再减少点时间,于是有了如下漫长的一个分析过程,

开始分析了:

首先从st这张2亿来条数据大表入手,想着能不能跟个小表join一下,降低点数据量。。。

从大表st开始着手,看看st与表des的连接结果集

select /*+ parallel(8) */count(*) from dom.yfs_order_release_status st,

dom.yfs_status des where des.status = st.status and             st.status_quantity > 0

AND             st.status >='1100'

AND             st.status < '3700'  AND             des.process_type_key = 'PO_FULFILLMENT' ;

COUNT(*)

----------

6243946

Elapsed: 00:01:12.67

走的都是index fast full scan

select /*+ parallel(8) full(ST) full(DES) */count(*) from dom.yfs_order_release_status st,

dom.yfs_status des where des.status = st.status and             st.status_quantity > 0

AND             st.status >='1100'

AND             st.status < '3700'  AND             des.process_type_key = 'PO_FULFILLMENT' ;

COUNT(*)

----------

6243932

Elapsed: 00:02:40.61

自己做到这里,突发奇想,之前调优是走的全表扫ST这张大表,这次能不能也走个index fast full scan配上并行,速度岂不是很快,并且逻辑读什么的也能降低,因为上面的图中全表扫描的2KW逻辑读,ffs的只有800W逻辑读,于是往走偏的路上越来越远,一门心思想看看到底能不能走fast full index scan。

加了如下的hint:

explain plan for

SELECT  /*+PARALLEL(8) use_hash(ST) LEADING(DES,ST) INDEX_FFS(DES YFS_STATUS_I1) INDEX_FFS(ST YFS_ORDER_RELEASE_STATUS_I7)*/

|* 29 |                     HASH JOIN                  |                          |  7707K|   845M|       |   447K  (1)| 00:14:55 |  Q1,03 | PCWP |            |

|  30 |                      PX RECEIVE                |                          |    40 |  1840 |       |     2   (0)| 00:00:01 |  Q1,03 | PCWP |            |

|  31 |                       PX SEND BROADCAST        | :TQ10001                 |    40 |  1840 |       |     2   (0)| 00:00:01 |  Q1,01 | P->P | BROADCAST  |

|  32 |                        PX BLOCK ITERATOR       |                          |    40 |  1840 |       |     2   (0)| 00:00:01 |  Q1,01 | PCWC |            |

|* 33 |                         TABLE ACCESS FULL      | YFS_STATUS               |    40 |  1840 |       |     2   (0)| 00:00:01 |  Q1,01 | PCWP |            |

|  34 |                      PX BLOCK ITERATOR         |                          |    11M|   764M|       |   447K  (1)| 00:14:55 |  Q1,03 | PCWC |            |

|* 35 |                       TABLE ACCESS FULL        | YFS_ORDER_RELEASE_STATUS |    11M|   764M|       |   447K  (1)| 00:14:55 |  Q1,03 | PCWP |            |

调了很多次,hint也改过很多次,怎么都走不出FFS,为什么ST就是不走FFS呢。。。

这里也先贴下部分ST的索引信息:(index_type都是normal)

TABLE_NAME                     INDEX_NAME                     COLUMN_NAME                              COLUMN_POSITION

------------------------------ ------------------------------ ---------------------------------------- ---------------

YFS_ORDER_RELEASE_STATUS       DNC_YFS_ORDER_RELSE_STATUS_I10 STATUS                                                 1

YFS_ORDER_RELEASE_STATUS       YFS_ORDER_RELEASE_STATUS_I7    ORDER_HEADER_KEY                                       1

YFS_ORDER_RELEASE_STATUS       YFS_ORDER_RELEASE_STATUS_I7    STATUS                                                 2

YFS_ORDER_RELEASE_STATUS       YFS_ORDER_RELEASE_STATUS_I7    STATUS_QUANTITY                                        3

YFS_ORDER_RELEASE_STATUS       YFS_ORDER_RELEASE_STATUS_I7    STATUS_DATE                                            4

表信息:

TABLE_NAME                     OWNER                            NUM_ROWS     BLOCKS LAST_ANAL PAR

------------------------------ ------------------------------ ---------- ---------- --------- ---

YFS_ORDER_RELEASE_STATUS       DOM                             225019900   16004052 17-DEC-16 NO

DES索引信息:

TABLE_NAME                     INDEX_NAME                     COLUMN_NAME                              COLUMN_POSITION

------------------------------ ------------------------------ ---------------------------------------- ---------------

YFS_STATUS                     YFS_STATUS_I1                  STATUS                                                 1

YFS_STATUS                     YFS_STATUS_I1                  PROCESS_TYPE_KEY                                       2

YFS_STATUS                     YFS_STATUS_PK                  STATUS_KEY                                             1

SELECT  /*+PARALLEL(8) use_hash(ST) LEADING(DES,ST) INDEX_FFS(DES YFS_STATUS_I1) INDEX_FFS(ST YFS_ORDER_RELEASE_STATUS_I7)*/

为什么这个hint走不到对ST的快速索引全扫描呢,于是想到做个10053把,先看看:

SQL> SELECT s.sid,s.serial#,pa.value || '/' || LOWER(SYS_CONTEXT('userenv','instance_name')) || '_ora_' || p.spid || '.trc' AS trace_file

FROM   v$session s,  v$process p,v$parameter pa

WHERE  pa.name = 'user_dump_dest'   AND    s.paddr = p.addr  AND    s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');

SID    SERIAL#  TRACE_FILE

---------- ----------

574        149   /u01/app/oracle/diag/rdbms/dcbiprd/DCBIPRD3/trace/dcbiprd3_ora_37778.trc(这里注意下在Linux下的大小写)

SQL> alter session set events '10053 trace name context forever,level 1';

Session altered.

执行explain plan for SELECT  /*+PARALLEL(8) use_hash(ST) LEADING(DES,ST) INDEX_FFS(DES YFS_STATUS_I1) INDEX_FFS(ST YFS_ORDER_RELEASE_STATUS_I7)*/…………….(后面一大串sql省略)

SQL> alter session set events '10053 trace name context off';

Session altered.

10053生成完了,来看看这个文件吧,当然关于10053的很多东西怎么看,自己也一知半解的,也希望以后能把这一块彻底给搞懂。

先贴个10053的一些信息:

先来找下关键字:yfs_order_release_status

先看看table stats, Index: YFS_ORDER_RELEASE_STATUS_I7 下面标注了   User hint to use this index,至少说明了这个hint没啥语法错误,那咋

没用上呢。

Table Stats::

Table: YFS_ORDER_RELEASE_STATUS  Alias: ST

#Rows: 225019900  #Blks:  16004052  AvgRowLen:  267.00  ChainCnt:  0.00

Column (#5): STATUS(

AvgLen: 8 NDV: 110 Nulls: 0 Density: 0.000000

Histogram: Freq  #Bkts: 110  UncompBkts: 6750597  EndPtVals: 110

Column (#3): ORDER_LINE_KEY(

AvgLen: 25 NDV: 25344885 Nulls: 0 Density: 0.000000

Histogram: HtBal  #Bkts: 254  UncompBkts: 254  EndPtVals: 255

Index Stats::

Index: DNC_YFS_ORDER_RELSE_STATUS_I10  Col#: 5

LVLS: 3  #LB: 3228667  #DK: 110  LB/K: 29351.00  DB/K: 706241.00  CLUF: 77686533.00

Index: DNC_YFS_ORDER_RELSE_STATUS_I9  Col#: 13

LVLS: 3  #LB: 1146067  #DK: 3909646  LB/K: 1.00  DB/K: 11.00  CLUF: 43642867.00

Index: YFS_ORDER_RELEASE_STATUS_BI1  Col#: 14 4

LVLS: 4  #LB: 11188000  #DK: 10786722  LB/K: 1.00  DB/K: 17.00  CLUF: 124038967.00

Index: YFS_ORDER_RELEASE_STATUS_I1  Col#: 2

LVLS: 3  #LB: 474200  #DK: 1935484  LB/K: 1.00  DB/K: 10.00  CLUF: 20958333.00

Index: YFS_ORDER_RELEASE_STATUS_I2  Col#: 3

LVLS: 3  #LB: 4539400  #DK: 25344885  LB/K: 1.00  DB/K: 7.00  CLUF: 191888033.00

Index: YFS_ORDER_RELEASE_STATUS_I4  Col#: 5 4

LVLS: 4  #LB: 7840667  #DK: 10255916  LB/K: 1.00  DB/K: 24.00  CLUF: 145409767.00

Index: YFS_ORDER_RELEASE_STATUS_I5  Col#: 5 2 3

LVLS: 4  #LB: 8165000  #DK: 27522373  LB/K: 1.00  DB/K: 5.00  CLUF: 153048367.00

Index: YFS_ORDER_RELEASE_STATUS_I6  Col#: 8 7

LVLS: 4  #LB: 5294700  #DK: 56  LB/K: 94548.00  DB/K: 1022166.00  CLUF: 57241333.00

Index: YFS_ORDER_RELEASE_STATUS_I7  Col#: 4 5 7 6

LVLS: 4  #LB: 5529033  #DK: 10791788  LB/K: 1.00  DB/K: 16.00  CLUF: 150753867.00

User hint to use this index

Index: YFS_ORDER_RELEASE_STATUS_I8  Col#: 9

LVLS: 3  #LB: 8661767  #DK: 59271636  LB/K: 1.00  DB/K: 3.00  CLUF: 198543600.00

Index: YFS_ORDER_RELEASE_STATUS_MD  Col#: 15

LVLS: 3  #LB: 10598533  #DK: 10081092  LB/K: 1.00  DB/K: 12.00  CLUF: 126044600.00

Index: YFS_ORDER_RELEASE_STATUS_PK  Col#: 1

LVLS: 3  #LB: 14141233  #DK: 220980467  LB/K: 1.00  DB/K: 1.00  CLUF: 204740000.00

继续看看YFS_ORDER_RELEASE_STATUS的access path分析:

可以看出最后选择了cost最小的table full scan,不过发现了一点,为什么对这个索引I7的fast full index scan分析都没有,

难道优化器都没考虑这一个路径吗?

Access path analysis for YFS_ORDER_RELEASE_STATUS

***************************************

SINGLE TABLE ACCESS PATH

Single Table Cardinality Estimation for YFS_ORDER_RELEASE_STATUS[ST]

*** 2016-12-21 17:33:33.665

** Performing dynamic sampling initial checks. **

Column (#7):

NewDensity:0.000000, OldDensity:0.000000 BktCnt:6750597, PopBktCnt:6750585, PopValCnt:22, NDV:34

Column (#7): STATUS_QUANTITY(

AvgLen: 3 NDV: 34 Nulls: 0 Density: 0.000000 Min: 0 Max: 106

Histogram: Freq  #Bkts: 34  UncompBkts: 6750597  EndPtVals: 34

** Dynamic sampling initial checks returning TRUE (level = 7).

*** 2016-12-21 17:33:33.666

** Generated dynamic sampling query:

query text :

SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB)

opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */

NVL(SUM(C1),0), NVL(SUM(C2),0)

FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("ST") FULL("ST") NO_PARALLEL_INDEX("ST") */ 1 AS C1,

CASE WHEN "ST"."STATUS_QUANTITY">0 AND "ST"."STATUS">='1100' AND "ST"."STATUS"<'3700' THEN 1 ELSE 0 END AS C2

FROM "DOM"."YFS_ORDER_RELEASE_STATUS" SAMPLE BLOCK (0.001593 , 1) SEED (1) "ST") SAMPLESUB

*** 2016-12-21 17:33:34.088

** Executed dynamic sampling query:

level : 7

sample pct. : 0.001593

actual sample size : 3687

filtered sample card. : 191

orig. card. : 225019900

block cnt. table stat. : 16004052

block cnt. for sampling: 16004052

max. sample block cnt. : 256

sample block cnt. : 255

min. sel. est. : 0.11171934

** Using single table dynamic sel. est. : 0.05180363

Table: YFS_ORDER_RELEASE_STATUS  Alias: ST

Card: Original: 225019900.000000  Rounded: 11656849  Computed: 11656848.63  Non Adjusted: 11656848.63

Access Path: TableScan

Cost:  3220077.07  Resp: 447232.93  Degree: 0

Cost_io: 3218208.00  Cost_cpu: 75101168005

Resp_io: 446973.33  Resp_cpu: 10430717778

kkofmx: index filter:"ST"."STATUS_QUANTITY">0

kkofmx: index filter:"ST"."STATUS_QUANTITY">0

kkofmx: index filter:"ST"."STATUS">='1100'

kkofmx: index filter:"ST"."STATUS"<'3700'

Access Path: index (RangeScan)

Index: DNC_YFS_ORDER_RELSE_STATUS_I10

resc_io: 55614935.00  resc_cpu: 481755867217

ix_sel: 0.687324  ix_sel_with_filters: 0.687324

Cost: 55626924.64  Resp: 55626924.64  Degree: 1

Access Path: index (RangeScan)

Index: YFS_ORDER_RELEASE_STATUS_I4

resc_io: 105332653.00  resc_cpu: 826064023866

ix_sel: 0.687324  ix_sel_with_filters: 0.687324

Cost: 105353211.57  Resp: 105353211.57  Degree: 1

Access Path: index (RangeScan)

Index: YFS_ORDER_RELEASE_STATUS_I5

resc_io: 118766178.00  resc_cpu: 926210190656

ix_sel: 0.736702  ix_sel_with_filters: 0.736702

Cost: 118789228.95  Resp: 118789228.95  Degree: 1

Access Path: index (skip-scan)

SS scan sel: 0.161293  SS filter sel: 0.161293  ANDV (#skips): 21.000000

SS io: 1985529.000000 vs. table scan io: 3218208.000000

Skip Scan chosen

Access Path: index (SkipScan)

Index: YFS_ORDER_RELEASE_STATUS_I6

resc_io: 11218143.00  resc_cpu: 99788662463

ix_sel: 0.161293  ix_sel_with_filters: 0.161293

Cost: 11220626.48  Resp: 11220626.48  Degree: 1

Access Path: index (skip-scan)

SS scan sel: 0.110860  SS filter sel: 0.110860  ANDV (#skips): 10255916.000000

SS io: 5529033.000000 vs. table scan io: 3218208.000000

Skip Scan rejected

Access Path: index (FullScan)

Index: YFS_ORDER_RELEASE_STATUS_I7   对它的full fast index scan这个access path都没有!!!!

resc_io: 22241654.00  resc_cpu: 209221395452

ix_sel: 1.000000  ix_sel_with_filters: 0.110860

***** Logdef predicate Adjustment ******

Final IO cst 0.00 , CPU cst 50.00

***** End Logdef Adjustment ******

***** Logdef predicate Adjustment ******

Final IO cst 0.00 , CPU cst 58.06

***** End Logdef Adjustment ******

***** Logdef predicate Adjustment ******

Final IO cst 0.00 , CPU cst 63.75

***** End Logdef Adjustment ******

Cost: 22247218.00  Resp: 22247218.00  Degree: 1

****** trying bitmap/domain indexes ******

****** finished trying bitmap/domain indexes ******

Best:: AccessPath: TableScan

Cost: 447232.93  Degree: 8  Resp: 447232.93  Card: 11656848.63  Bytes: 0

于是只能去考虑这个hint在什么情况下会被优化器给忽略掉!!!!!!!!!!!!

查看了一些资料大致都是如下的观点:

1. 这种类型的索引扫描的是用来在查询列表中所有字段都包含在索引中并且索引中至少有一列具有非空约束是替代全表扫描的,

从而这种情况下,数据通过索引访问即可,不必再回到数据块的访问上。

2.这种扫描并不能用来避免排序!

在这个例子中,走I7这个索引因为有条件限制"ST"."STATUS">='1100' AND "ST"."STATUS"<'3700'可以说吗status not null,并且select列上都是I7索引所包含的列,所以第一个条件满足。

但是第二个就不满足了,sql是有排序的。

另外ST这张表的连结列不仅只是status这一列,可以看到ST这张表还有这个连接条件st.order_line_key = line.o rder_line_key ,

从10053的table stats也看到,优化器考虑了这两列status.order_line_key的相关统计信息,

所以这个hint没生效的原因也显而易见。

通俗点来讲就是要用Index fast full scan就是不要回表读,在满足这个条件下,可以用到FFS,但是如果不能保证不回表读,很可能FFS hint将没用,

自己完全可以测一下如果在没有 st.order_line_key = line.o rder_line_key和order by的情况下,FFS hint是完全可以走的,但是多了一个表连接的情况,更加多了一个排序,FFS hint无法使用似乎也在情理之中。

参考这篇文章Index Fast Full Scan Usage To Avoid Full Table Scans (Doc ID 70135.1)

When Will Index FFS be used in preference to FTS?

From the Oracle8 Server Concepts manual:

  1. The index must contain all the columns referenced in the query.  (在本例中应该算不符合把,毕竟order_line_key这个列还在query里面呢,但并不在索引里)
  2. Index FFS is only available with Cost Based Optimizer (CBO) (Index hint forces CBO).
  3. Index FFS can be hinted with /*+ INDEX_FFS() */ .

Index FFS was introduced in 7.3. 

In Oracle7 it requires initialization parameter V733_PLANS_ENABLED to be set to
TRUE .



An Index FFS will scan all blocks in the index. The returned data is not
sorted.(在本例是需要排序的,故也不符合)

Index FFS can use multiblock I/O and can be parallelized just like a Full Table
Scan.

总结:

从一个普通的mail邮件告警来逐步分析,一步步的温习了一些并行的知识,也学习到了一些东西,最主要的就是对sql
monitor的认识又加深了一步,

而且从下面FFS hint不生效的案例中也是不仅仅的涉及了10053 trace,更是学习了FFS
hint在某些情况不生效的原因。往大处思考,其实在很多时候的hint明明语法正确,看上去可以用,但是数据库优化器就是没选择呢,

这里面一定有值得去探究的原因!也告诫自己在以后的思考过程中,不要想当然的去钻牛角尖~~思维要开拓~