事情的发生就是这么偶然，一步步的深入才能汲取到更深入的知识~~
-------------------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里面没这个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本身消耗的CPU或IO并没有超过5秒（由于锁、网络？）
- 并行执行的SQL将全部被监控到，不需要等待CPU或IO时间超过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设置的系统隐藏参数：
看了这些，根据这些查看了下数据库的环境都是OK的，并且觉得还是有点无法理解，因为这条sql在v$sql_monitor里面有了，但是就是在OEM看不到，另外在sqlplus也生成不了报告，于是只好作罢~~
当然最后在top activity里面看到了这条sql,但是sql monitor按钮是灰色的，如下图：
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:
- The index must contain all the columns referenced in the query. （在本例中应该算不符合把，毕竟order_line_key这个列还在query里面呢，但并不在索引里）
- Index FFS is only available with Cost Based Optimizer (CBO) (Index hint forces CBO).
- 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明明语法正确，看上去可以用，但是数据库优化器就是没选择呢，
这里面一定有值得去探究的原因！也告诫自己在以后的思考过程中，不要想当然的去钻牛角尖~~思维要开拓~