=================START================================
BI数据仓库环境里面跑着一个crontab job,一旦sql运行超过4hours，就会接到mail alert.
现在要分析的正是收到alert里面的一个语句：
SID SERIAL# INST_ID SQL_ID Run_in_sec OS_user MACHINE
--------------------
SQL_TEXT MODULE EVENT
----------------------------------------------------------------
1707 485 3 g4b83zcm0b9b3 KCHIMA
LN0FW2FV6416FF 跑了15000S左右，即大概5小时还没跑完
SELECT /*+PARALLEL(8)*/ DISTINCT ORH.ORDER_NO, ORH.ORDER_DATE, ORH.DIVISION, ORH.BUYER_USER_ID as PR
OFILE_ID ,ORH.PAYMENT_STATUS, ORL.RESERVATION_ID, ORL.ORDER_LINE_KEY, ORH.ORDER_HEADER_KEY
,ORL.UPC_CODE, ORL.Item_id, ORL.EXTN_STYLE_NUMBER||'-'||ORL.EXTN_COLOR_NUMBER||'-'||ORL.EXT
N_DISPLAY_SIZE as ProductCD, ORL.EXTN_METRIC_ID, ORL.PRIME_LINE_NO ,ORL.EXTN_PRODUCT_ID as PID, ORL
.ITEM_DESCRIPTION AS PROD_DESC, ORL.LINE_TYPE,ST.STATUS_NAME, ORS.STATUS, ORS.STATUS_DATE
,ORS.CREATEUSERID,ORS.CREATETS, ORS.MODIFYUSERID, ORS.MODIFYTS , 'SalesOrderNo="'||ORH.ORD
ER_NO||'"%PrimeLineNo="'||ORL.PRIME_LINE_NO||'" Quantity="0"%' ,ORL.CARRIER_SERVICE_CODE,
ORL.PROMISED_APPT_START_DATE, ORL.PROMISED_APPT_END_DATE FROM DOM.YFS_ORDER_HEADER ORH, DOM.YFS_ORDER_RELEASE_STATUS ORS, DOM.YFS_STATUS ST, DOM.YFS_ORDER_LINE ORL
where ORS.ORDER_LINE_KEY = ORL.ORDER_LINE_KEY AND ORH.ORDER_HEADER_KEY = ORS.ORDER_HEADER_KEY AND ST.STATUS = ORS.STATUS A
SQL Developer
PX Deq: Execute Reply
现在就来分析下这个sql的状态以及是否能做些改动让它跑得更快些
先看下现在这个sql在数据库里的状态，还在执行中，
select sid,serial#,inst_id,event,osuser,username,machine,sql_id,status,to_char(logon_time,'yyyymmdd hh24:mi:ss') from gv$session where sql_id='g4b83zcm0b9b3' and status='ACTIVE';
SID SERIAL# INST_ID EVENT OSUSER USERNAME MACHINE SQL_ID STATUS TO_CHAR(LOGON_TIM
---------- ---------- ---------- --------------------------------- ------------- ----------------- ----------------------------------------------------- -------------
68 299 3 db file sequential read KCHIMA KCHIMA LN0FW2FV6416FF g4b83zcm0b9b3 ACTIVE 20161216 00:47:20
699 469 3 db file sequential read KCHIMA KCHIMA LN0FW2FV6416FF g4b83zcm0b9b3 ACTIVE 20161216 00:47:20
1707 485 3 PX Deq: Execute Reply KCHIMA KCHIMA LN0FW2FV6416FF g4b83zcm0b9b3 ACTIVE 20161215 23:10:55
2209 465 3 PX Deq: Table Q Normal KCHIMA KCHIMA LN0FW2FV6416FF g4b83zcm0b9b3 ACTIVE 20161216 00:47:20
2270 317 3 PX Deq: Table Q Normal KCHIMA KCHIMA LN0FW2FV6416FF g4b83zcm0b9b3 ACTIVE 20161216 00:47:20
2333 183 3 PX Deq: Table Q Normal KCHIMA KCHIMA LN0FW2FV6416FF g4b83zcm0b9b3 ACTIVE 20161216 00:47:20
2399 667 3 PX Deq: Table Q Normal KCHIMA KCHIMA LN0FW2FV6416FF g4b83zcm0b9b3 ACTIVE 20161216 00:47:20
2401 151 3 db file sequential read KCHIMA KCHIMA LN0FW2FV6416FF g4b83zcm0b9b3 ACTIVE 20161216 00:47:20
2966 149 3 db file sequential read KCHIMA KCHIMA LN0FW2FV6416FF g4b83zcm0b9b3 ACTIVE 20161216 00:47:20
---------------------------------------------------------------
SQL格式化如下：
SELECT
/*+PARALLEL(8)*/
DISTINCT ORH.ORDER_NO ,
ORH.ORDER_DATE ,
ORH.DIVISION ,
ORH.BUYER_USER_ID AS PROFILE_ID ,
ORH.PAYMENT_STATUS ,
ORL.RESERVATION_ID ,
ORL.ORDER_LINE_KEY ,
ORH.ORDER_HEADER_KEY ,
ORL.UPC_CODE ,
ORL.Item_id ,
ORL.EXTN_STYLE_NUMBER
||
'-'
||
ORL.EXTN_COLOR_NUMBER
||
'-'
||
ORL.EXTN_DISPLAY_SIZE AS ProductCD,
ORL.EXTN_METRIC_ID ,
ORL.PRIME_LINE_NO ,
ORL.EXTN_PRODUCT_ID AS PID ,
ORL.ITEM_DESCRIPTION AS PROD_DESC ,
ORL.LINE_TYPE ,
ST.STATUS_NAME ,
ORS.STATUS ,
ORS.STATUS_DATE ,
ORS.CREATEUSERID ,
ORS.CREATETS ,
ORS.MODIFYUSERID ,
ORS.MODIFYTS ,
'SalesOrderNo="'
||
ORH.ORDER_NO
||
'"%PrimeLineNo="'
||
ORL.PRIME_LINE_NO
||
'" Quantity="0"%' ,
ORL.CARRIER_SERVICE_CODE ,
ORL.PROMISED_APPT_START_DATE,
ORL.PROMISED_APPT_END_DATE
FROM DOM.YFS_ORDER_HEADER ORH ,
DOM.YFS_ORDER_RELEASE_STATUS ORS,
DOM.YFS_STATUS ST ,
DOM.YFS_ORDER_LINE ORL
WHERE ORS.ORDER_LINE_KEY = ORL.ORDER_LINE_KEY
AND ORH.ORDER_HEADER_KEY = ORS.ORDER_HEADER_KEY
AND ST.STATUS = ORS.STATUS
AND ORH.ORDER_HEADER_KEY = ORL.ORDER_HEADER_KEY
AND ORS.STATUS_QUANTITY > 0
AND ST.PROCESS_TYPE_KEY = 'ORDER_FULFILLMENT'
AND ORH.DOCUMENT_TYPE = '0001'
AND ORH.ENTERPRISE_KEY = 'NIKEUS'
AND ORS.STATUS != '1400'
AND ORS.STATUS < '3700'
AND ORL.LINE_TYPE = 'INLINE'
AND ORL.UOM = 'EACH'
AND ORL.PACKLIST_TYPE = 'DIGITALPID'
AND ORL.FULFILLMENT_TYPE = 'DIGITALPID'
AND ORH.ORDER_HEADER_KEY >'20161201'
ORDER BY ORH.ORDER_NO ,
ORL.ORDER_LINE_KEY,
ORS.STATUS_DATE;
==================
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('g4b83zcm0b9b3', format=>'ALL'));先来看一下执行计划
可以看到如下的执行计划有很多bitmap的操作，并且重点在于note里面说是启用了星型转换这一个特性，所以执行计划里面有temp table,bitmap就不奇怪了。
关于星型转换在数据仓库的应用在后面会讲到。
Plan hash value: 364873834
------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 559K(100)| | | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 2 | 512 | 248K (1)| 01:35:07 | Q1,00 | P->S | QC (RAND) |
| 4 | LOAD AS SELECT | | | | | | Q1,00 | PCWP | |
| 5 | NESTED LOOPS | | 2 | 512 | 248K (1)| 01:35:07 | Q1,00 | PCWP | |
| 6 | NESTED LOOPS | | 2 | 512 | 248K (1)| 01:35:07 | Q1,00 | PCWP | |
| 7 | PX BLOCK ITERATOR | | | | | | Q1,00 | PCWC | |
|* 8 | TABLE ACCESS FULL | YFS_ORDER_LINE | 2 | 350 | 248K (1)| 01:35:07 | Q1,00 | PCWP | |
|* 9 | INDEX UNIQUE SCAN | YFS_ORDER_HEADER_PK | 1 | | 0 (0)| | Q1,00 | PCWP | |
|* 10 | TABLE ACCESS BY INDEX ROWID | YFS_ORDER_HEADER | 1 | 81 | 0 (0)| | Q1,00 | PCWP | |
| 11 | PX COORDINATOR | | | | | | | | |
| 12 | PX SEND QC (ORDER) | :TQ20002 | 1 | 401 | 311K (1)| 01:59:32 | Q2,02 | P->S | QC (ORDER) |
| 13 | SORT UNIQUE | | 1 | 401 | 311K (1)| 01:59:32 | Q2,02 | PCWP | |
| 14 | PX RECEIVE | | 1 | 401 | 311K (1)| 01:59:32 | Q2,02 | PCWP | |
| 15 | PX SEND RANGE | :TQ20001 | 1 | 401 | 311K (1)| 01:59:32 | Q2,01 | P->P | RANGE |
|* 16 | HASH JOIN | | 1 | 401 | 311K (1)| 01:59:32 | Q2,01 | PCWP | |
| 17 | PX RECEIVE | | 1 | 355 | 311K (1)| 01:59:32 | Q2,01 | PCWP | |
| 18 | PX SEND BROADCAST | :TQ20000 | 1 | 355 | 311K (1)| 01:59:32 | Q2,00 | P->P | BROADCAST |
| 19 | NESTED LOOPS | | 1 | 355 | 311K (1)| 01:59:32 | Q2,00 | PCWP | |
| 20 | NESTED LOOPS | | 1 | 355 | 311K (1)| 01:59:32 | Q2,00 | PCWP | |
| 21 | PX BLOCK ITERATOR | | | | | | Q2,00 | PCWC | |
|* 22 | TABLE ACCESS FULL | SYS_TEMP_0FDA22E46_502EA344 | 1 | 225 | 2 (0)| 00:00:01 | Q2,00 | PCWP | |
| 23 | BITMAP CONVERSION TO ROWIDS | | | | | | Q2,00 | PCWP | |
| 24 | BITMAP AND | | | | | | Q2,00 | PCWP | |
| 25 | BITMAP MERGE | | | | | | Q2,00 | PCWP | |
| 26 | BITMAP KEY ITERATION | | | | | | Q2,00 | PCWP | |
| 27 | TABLE ACCESS FULL | SYS_TEMP_0FDA22E46_502EA344 | 2 | 50 | 2 (0)| 00:00:01 | Q2,00 | PCWP | |
| 28 | BITMAP CONVERSION FROM ROWIDS| | | | | | Q2,00 | PCWP | |
|* 29 | INDEX RANGE SCAN | YFS_ORDER_RELEASE_STATUS_I2 | | | 3 (0)| 00:00:01 | Q2,00 | PCWP | |
| 30 | BITMAP CONVERSION FROM ROWIDS | | | | | | Q2,00 | PCWP | |
| 31 | SORT ORDER BY | | | | | | Q2,00 | PCWP | |
|* 32 | INDEX RANGE SCAN | YFS_ORDER_RELEASE_STATUS_I7 | | | 4 (0)| 00:00:01 | Q2,00 | PCWP | |
| 33 | BITMAP MERGE | | | | | | Q2,00 | PCWP | |
| 34 | BITMAP KEY ITERATION | | | | | | Q2,00 | PCWP | |
|* 35 | INDEX FAST FULL SCAN | YFS_STATUS_I1 | 80 | 2240 | 2 (0)| 00:00:01 | Q2,00 | PCWP | |
| 36 | BITMAP CONVERSION FROM ROWIDS| | | | | | Q2,00 | PCWP | |
|* 37 | INDEX RANGE SCAN | DNC_YFS_ORDER_RELSE_STATUS_I10 | | | 26809 (1)| 00:10:17 | Q2,00 | PCWP | |
| 38 | TABLE ACCESS BY INDEX ROWID | YFS_ORDER_RELEASE_STATUS | 1 | 130 | 311K (1)| 01:59:32 | Q2,00 | PCWP | |
| 39 | PX BLOCK ITERATOR | | 80 | 3680 | 2 (0)| 00:00:01 | Q2,01 | PCWC | |
|* 40 | TABLE ACCESS FULL | YFS_STATUS | 80 | 3680 | 2 (0)| 00:00:01 | Q2,01 | PCWP | |
Note
-----
- dynamic sampling used for this statement (level=7)
- Degree of Parallelism is 8 because of hint
- star transformation used for this statement
=========================
自己看了下这个sql语句，无非就是四张表的join，
先来看看这四张表的信息：
TABLE_NAME OWNER NUM_ROWS BLOCKS LAST_ANAL PAR
------------------------------ ------------------------------ ---------- ---------- --------- ---
YFS_STATUS DOM 795 64 10-DEC-16 NO
YFS_ORDER_HEADER DOM 20628980 3443494 10-DEC-16 NO
YFS_ORDER_RELEASE_STATUS DOM 217432467 16004052 10-DEC-16 NO
YFS_ORDER_LINE DOM 35327340 8868339 10-DEC-16 NO
有个800条记录的小表，200,300百万数据量的两张表，最后一张大彪2KW数据量
按照自己的思维，先挑挑小表开始做个测试，即因为源sql是四张表的连接，所以我想从小表先入手，让小表和它相关联的表去join，看看这个速度，数据量怎么样
select /*+ parallel(8) */ count(*) from DOM.YFS_ORDER_RELEASE_STATUS ORS,
DOM.YFS_STATUS ST where ST.PROCESS_TYPE_KEY = 'ORDER_FULFILLMENT'
AND ORS.STATUS != '1400'
AND ORS.STATUS < '3700'
AND ST.STATUS = ORS.STATUS
AND ORS.STATUS_QUANTITY > 0 ;
结果：
COUNT(*)
----------
8931586
Elapsed: 00:01:40.35
花了1秒多点的时间，这个时候，自己想直接让这四张表去按照小表到大表的顺序依次连接，即主要是想以小表来一步步驱动大表
改成如下sql在测试环境测了一下：
alter session set statistics_level=all;
SELECT
/*+ PARALLEL(8) ORDERED */
DISTINCT ORH.ORDER_NO ,
ORH.ORDER_DATE ,
ORH.DIVISION ,
ORH.BUYER_USER_ID AS PROFILE_ID ,
ORH.PAYMENT_STATUS ,
ORL.RESERVATION_ID ,
ORL.ORDER_LINE_KEY ,
ORH.ORDER_HEADER_KEY ,
ORL.UPC_CODE ,
ORL.Item_id ,
ORL.EXTN_STYLE_NUMBER
||
'-'
||
ORL.EXTN_COLOR_NUMBER
||
'-'
||
ORL.EXTN_DISPLAY_SIZE AS ProductCD,
ORL.EXTN_METRIC_ID ,
ORL.PRIME_LINE_NO ,
ORL.EXTN_PRODUCT_ID AS PID ,
ORL.ITEM_DESCRIPTION AS PROD_DESC ,
ORL.LINE_TYPE ,
ST.STATUS_NAME ,
ORS.STATUS ,
ORS.STATUS_DATE ,
ORS.CREATEUSERID ,
ORS.CREATETS ,
ORS.MODIFYUSERID ,
ORS.MODIFYTS ,
'SalesOrderNo="'
||
ORH.ORDER_NO
||
'"%PrimeLineNo="'
||
ORL.PRIME_LINE_NO
||
'" Quantity="0"%' ,
ORL.CARRIER_SERVICE_CODE ,
ORL.PROMISED_APPT_START_DATE,
ORL.PROMISED_APPT_END_DATE
FROM DOM.YFS_STATUS ST ,
DOM.YFS_ORDER_RELEASE_STATUS ORS,
DOM.YFS_ORDER_HEADER ORH ,
DOM.YFS_ORDER_LINE ORL
WHERE ORS.ORDER_LINE_KEY = ORL.ORDER_LINE_KEY
AND ORH.ORDER_HEADER_KEY = ORS.ORDER_HEADER_KEY
AND ST.STATUS = ORS.STATUS
AND ORH.ORDER_HEADER_KEY = ORL.ORDER_HEADER_KEY
AND ORS.STATUS_QUANTITY > 0
AND ST.PROCESS_TYPE_KEY = 'ORDER_FULFILLMENT'
AND ORH.DOCUMENT_TYPE = '0001'
AND ORH.ENTERPRISE_KEY = 'NIKEUS'
AND ORS.STATUS != '1400'
AND ORS.STATUS < '3700'
AND ORL.LINE_TYPE = 'INLINE'
AND ORL.UOM = 'EACH'
AND ORL.PACKLIST_TYPE = 'DIGITALPID'
AND ORL.FULFILLMENT_TYPE = 'DIGITALPID'
AND ORH.ORDER_HEADER_KEY >'20161201'
ORDER BY ORH.ORDER_NO ,
ORL.ORDER_LINE_KEY,
ORS.STATUS_DATE;
跑了十分钟左右跑出来了
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8zm612scbtn7t, child number 0
Plan hash value: 3516664327
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2556 |00:13:43.16 | 6699K| 2823K| | | |
| 1 | PX COORDINATOR | | 1 | | 2556 |00:13:43.16 | 6699K| 2823K| | | |
| 2 | PX SEND QC (ORDER) | :TQ10006 | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
| 3 | SORT ORDER BY | | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | 1575K| 619K| 158K (0)|
| 4 | PX RECEIVE | | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
| 5 | PX SEND RANGE | :TQ10005 | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
| 6 | HASH UNIQUE | | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | 1906K| 953K| 1356K (0)|
|* 7 | HASH JOIN | | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | 17M| 4087K| 2475K (0)|
| 8 | BUFFER SORT | | 0 | | 0 |00:00:00.01 | 0 | 0 | 2037K| 674K| 1810K (0)|
| 9 | PX RECEIVE | | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
| 10 | PX SEND BROADCAST | :TQ10001 | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 11 | TABLE ACCESS BY INDEX ROWID | YFS_ORDER_LINE | 1 | 1 | 8005 |00:03:41.62 | 3167K| 810K| | | |
|* 12 | INDEX RANGE SCAN | YFS_ORDER_LINE_I1 | 1 | 3 | 4338K|00:00:46.22 | 61428 | 178K| | | |
|* 13 | HASH JOIN | | 0 | 1252K| 0 |00:00:00.01 | 0 | 0 | 106M| 9842K| 14M (0)|
| 14 | BUFFER SORT | | 0 | | 0 |00:00:00.01 | 0 | 0 | 12M| 1365K| 11M (0)|
| 15 | PX RECEIVE | | 0 | 922K| 0 |00:00:00.01 | 0 | 0 | | | |
| 16 | PX SEND HASH | :TQ10002 | 0 | 922K| 0 |00:00:00.01 | 0 | 0 | | | |
|* 17 | TABLE ACCESS BY INDEX ROWID | YFS_ORDER_HEADER | 1 | 922K| 978K|00:04:19.23 | 2601K| 741K| | | |
|* 18 | INDEX RANGE SCAN | YFS_ORDER_HEADER_PK | 1 | 1 | 2835K|00:00:24.56 | 39187 | 89334 | | | |
| 19 | PX RECEIVE | | 0 | 1252K| 0 |00:00:00.01 | 0 | 0 | | | |
| 20 | PX SEND HASH | :TQ10004 | 0 | 1252K| 0 |00:00:00.01 | 0 | 0 | | | |
|* 21 | HASH JOIN | | 0 | 1252K| 0 |00:00:00.01 | 0 | 0 | 1172K| 1172K| 930K (0)|
| 22 | PX RECEIVE | | 0 | 80 | 0 |00:00:00.01 | 0 | 0 | | | |
| 23 | PX SEND HASH | :TQ10003 | 0 | 80 | 0 |00:00:00.01 | 0 | 0 | | | |
| 24 | PX BLOCK ITERATOR | | 0 | 80 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 25 | TABLE ACCESS FULL | YFS_STATUS | 0 | 80 | 0 |00:00:00.01 | 0 | 0 | | | |
| 26 | BUFFER SORT | | 0 | | 0 |00:00:00.01 | 0 | 0 | 152M| 4169K| 15M (0)|
| 27 | PX RECEIVE | | 0 | 952K| 0 |00:00:00.01 | 0 | 0 | | | |
| 28 | PX SEND HASH | :TQ10000 | 0 | 952K| 0 |00:00:00.01 | 0 | 0 | | | |
| 29 | TABLE ACCESS BY INDEX ROWID| YFS_ORDER_RELEASE_STATUS | 1 | 952K| 549K|00:05:39.03 | 930K| 1271K| | | |
|* 30 | INDEX RANGE SCAN | YFS_ORDER_RELEASE_STATUS_I7 | 1 | 3 | 549K|00:03:42.55 | 457K| 1082K| | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
-----------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("ORS"."ORDER_LINE_KEY"="ORL"."ORDER_LINE_KEY" AND "ORH"."ORDER_HEADER_KEY"="ORL"."ORDER_HEADER_KEY")
11 - filter(("ORL"."FULFILLMENT_TYPE"='DIGITALPID' AND "ORL"."PACKLIST_TYPE"='DIGITALPID' AND "ORL"."LINE_TYPE"='INLINE' AND "ORL"."UOM"='EACH'))
12 - access("ORL"."ORDER_HEADER_KEY">'20161201')
13 - access("ORH"."ORDER_HEADER_KEY"="ORS"."ORDER_HEADER_KEY")
17 - filter(("ORH"."DOCUMENT_TYPE"='0001' AND "ORH"."ENTERPRISE_KEY"='NIKEUS'))
18 - access("ORH"."ORDER_HEADER_KEY">'20161201')
21 - access("ST"."STATUS"="ORS"."STATUS")
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
25 - access(:Z>=:Z AND :Z<=:Z)
filter(("ST"."PROCESS_TYPE_KEY"='ORDER_FULFILLMENT' AND "ST"."STATUS"<'3700' AND "ST"."STATUS"<>'1400'))
30 - access("ORS"."ORDER_HEADER_KEY">'20161201' AND "ORS"."STATUS_QUANTITY">0 AND "ORS"."STATUS"<'3700')
filter(("ORS"."STATUS_QUANTITY">0 AND "ORS"."STATUS"<'3700' AND "ORS"."STATUS"<>'1400'))
Note
-----
- dynamic sampling used for this statement (level=7)
- Degree of Parallelism is 8 because of hint
Sql改动主要是加了ordered hint，然后from后面的四张表的顺序改了一下，让ST这张小表先作为驱动表，
显然跑出来的结果并不是ST作为驱动表，不过跑了十分钟出来了也不错，来看下上面的执行计划，除了ST这张小表走的是全表扫描，其余三张表都是index range scan扫出记录，然后这四张表的四次hash join
（这里先说个后续，其实在这里的并行并没有用上，唯一用到的估计就只有全表扫描ST这张表用到了并行，但意义不大，虽然看执行计划里面有并行的operation，但是实际上并没有，这点可以在后面生产库执行以及结合并行的实际使用原理来进行讲解）
在上面的sql跑完了，自己考虑的点是要是让ST作为驱动表能不能更快一点，
于是改成了SELECT /*+ PARALLEL(8) leading(ST) use_hash(ST,ORS,ORH,ORL) */
跑了一下，执行计划还是没变，于是就先放下这个问题，然后想了下，生产上这个sql是用到了星型转换的特性，那么万一不用星型转换呢，执行计划能不能就跟在测试环境走的一样，几张表直接hash join就行了，想看看没有星型转换的cost
于是下面是在生产操作：
Alter Session Set star_transformation_enabled = FALSE; 在生产上disable 星型转换
SELECT
/*+ PARALLEL(8) use_hash(ST,ORS,ORH,ORL) */。。。。语句省略了
2562 rows selected.
Elapsed: 00:04:31.55可见花了4分钟跑出来了
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2562 |00:03:27.32 | 180 | | | |
| 1 | PX COORDINATOR | | 1 | | 2562 |00:03:27.32 | 180 | | | |
| 2 | PX SEND QC (ORDER) | :TQ10004 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 3 | SORT ORDER BY | | 0 | 1 | 0 |00:00:00.01 | 0 | 2979K| 768K| 330K (0)|
| 4 | HASH UNIQUE | | 0 | 1 | 0 |00:00:00.01 | 0 | 3159K| 954K| 1355K (0)|
| 5 | PX RECEIVE | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 6 | PX SEND RANGE | :TQ10003 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 7 | HASH JOIN | | 0 | 1 | 0 |00:00:00.01 | 0 | 8867K| 1944K| 1471K (0)|
| 8 | PX RECEIVE | | 0 | 2 | 0 |00:00:00.01 | 0 | | | |
| 9 | PX SEND BROADCAST | :TQ10002 | 0 | 2 | 0 |00:00:00.01 | 0 | | | |
|* 10 | HASH JOIN | | 0 | 2 | 0 |00:00:00.01 | 0 | 8366K| 1955K| 1872K (0)|
| 11 | PX RECEIVE | | 0 | 2 | 0 |00:00:00.01 | 0 | | | |
| 12 | PX SEND BROADCAST | :TQ10001 | 0 | 2 | 0 |00:00:00.01 | 0 | | | |
|* 13 | HASH JOIN | | 0 | 2 | 0 |00:00:00.01 | 0 | 17M| 4087K| 2339K (0)|
| 14 | PX RECEIVE | | 0 | 2 | 0 |00:00:00.01 | 0 | | | |
| 15 | PX SEND BROADCAST | :TQ10000 | 0 | 2 | 0 |00:00:00.01 | 0 | | | |
| 16 | PX BLOCK ITERATOR | | 0 | 2 | 0 |00:00:00.01 | 0 | | | |
|* 17 | TABLE ACCESS FULL| YFS_ORDER_LINE | 0 | 2 | 0 |00:00:00.01 | 0 | | | |
| 18 | PX BLOCK ITERATOR | | 0 | 356K| 0 |00:00:00.01 | 0 | | | |
|* 19 | TABLE ACCESS FULL | YFS_ORDER_RELEASE_STATUS | 0 | 356K| 0 |00:00:00.01 | 0 | | | |
| 20 | PX BLOCK ITERATOR | | 0 | 80 | 0 |00:00:00.01 | 0 | | | |
|* 21 | TABLE ACCESS FULL | YFS_STATUS | 0 | 80 | 0 |00:00:00.01 | 0 | | | |
| 22 | PX BLOCK ITERATOR | | 0 | 1020K| 0 |00:00:00.01 | 0 | | | |
|* 23 | TABLE ACCESS FULL | YFS_ORDER_HEADER | 0 | 1020K| 0 |00:00:00.01 | 0 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------
执行计划里面走的是全表扫描，然后再sql执行的时候因为生产库有OEM，顺便拿OEM的sql monitor监控了一下，如下是一些信息，从这些信息可以看到：这几张表走的是全表扫描+并行，然后也是四次的hash join
可以看到并行的使用贯穿着sql的执行过程，那么回想之前说的，在测试环境走index range scan虽然有parallel hint，虽然执行计划里面显示了并行operation，但实际上是没有并行的，这一点接下来马上测试。
测试下走索引时候并行有没有用到了，如下几张OEM的截图可以说明，active session一直是1，即单线程工作。
最后取消了sql的执行，
ERROR at line 46:
ORA-01013: user requested cancel of current operation
Elapsed: 00:08:22.90
并行进程用不上 所以很慢。当然执行计划里面看到的是有并行操作的，所以不要被执行计划蒙蔽。当然后面会概括一下并行的使用场景，以及啥时候才能真正的用到并行。
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.79 | 3 | 0 | | | |
| 1 | PX COORDINATOR | | 1 | | 0 |00:00:00.79 | 3 | 0 | | | |
| 2 | PX SEND QC (ORDER) | :TQ10006 | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
| 3 | SORT ORDER BY | | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | 73728 | 73728 | |
| 4 | PX RECEIVE | | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
| 5 | PX SEND RANGE | :TQ10005 | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
| 6 | HASH UNIQUE | | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | 713K| 713K| |
|* 7 | HASH JOIN | | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | 721K| 721K| |
| 8 | PX RECEIVE | | 0 | 2 | 0 |00:00:00.01 | 0 | 0 | | | |
| 9 | PX SEND HASH | :TQ10004 | 0 | 2 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 10 | HASH JOIN | | 0 | 2 | 0 |00:00:00.01 | 0 | 0 | 763K| 763K| |
| 11 | BUFFER SORT | | 0 | | 0 |00:00:00.01 | 0 | 0 | 73728 | 73728 | |
| 12 | PX RECEIVE | | 0 | 2 | 0 |00:00:00.01 | 0 | 0 | | | |
| 13 | PX SEND BROADCAST | :TQ10000 | 0 | 2 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 14 | TABLE ACCESS BY INDEX ROWID | YFS_ORDER_LINE | 1 | 2 | 4718 |00:08:19.28 | 1954K| 390K| | | |
|* 15 | INDEX RANGE SCAN | YFS_ORDER_LINE_I1 | 1 | 2642K| 2789K|00:02:27.71 | 90761 | 38979 | | | |
|* 16 | HASH JOIN | | 0 | 462K| 0 |00:00:00.01 | 0 | 0 | 987K| 987K| |
| 17 | PX RECEIVE | | 0 | 80 | 0 |00:00:00.01 | 0 | 0 | | | |
| 18 | PX SEND HASH | :TQ10003 | 0 | 80 | 0 |00:00:00.01 | 0 | 0 | | | |
| 19 | PX BLOCK ITERATOR | | 0 | 80 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 20 | TABLE ACCESS FULL | YFS_STATUS | 0 | 80 | 0 |00:00:00.01 | 0 | 0 | | | |
| 21 | BUFFER SORT | | 0 | | 0 |00:00:00.01 | 0 | 0 | 73728 | 73728 | |
| 22 | PX RECEIVE | | 0 | 356K| 0 |00:00:00.01 | 0 | 0 | | | |
| 23 | PX SEND HASH | :TQ10001 | 0 | 356K| 0 |00:00:00.01 | 0 | 0 | | | |
| 24 | TABLE ACCESS BY INDEX ROWID| YFS_ORDER_RELEASE_STATUS | 0 | 356K| 0 |00:00:00.01 | 0 | 0 | | | |
|* 25 | INDEX RANGE SCAN | YFS_ORDER_RELEASE_STATUS_I7 | 0 | 2077K| 0 |00:00:00.01 | 0 | 0 | | | |
| 26 | BUFFER SORT | | 0 | | 0 |00:00:00.01 | 0 | 0 | 73728 | 73728 | |
| 27 | PX RECEIVE | | 0 | 1020K| 0 |00:00:00.01 | 0 | 0 | | | |
| 28 | PX SEND HASH | :TQ10002 | 0 | 1020K| 0 |00:00:00.01 | 0 | 0 | | | |
|* 29 | TABLE ACCESS BY INDEX ROWID | YFS_ORDER_HEADER | 0 | 1020K| 0 |00:00:00.01 | 0 | 0 | | | |
|* 30 | INDEX RANGE SCAN | YFS_ORDER_HEADER_PK | 0 | 1786K| 0 |00:00:00.01 | 0 | 0 | | | |
Note
-----
- dynamic sampling used for this statement (level=7)
- Degree of Parallelism is 8 because of hint
所以最后采用了
SELECT
/*+ PARALLEL(8) use_hash(ST,ORS,ORH,ORL) full(ORH) full(ORS) full(ORL) */
这个sql经过测试，即使不在session层面disable星型转换，它是会走parallel full table scan+hash join的执行计划。
小结以及后续动作：
1． 这次的sql执行慢初步分析是走了星型转换，但是这个start transformation在数据仓库还是用的很广的，这个参数也在我们的BI环境enable了，所以后续会对这个功能进一步的探寻，一个功能参数的启用并不意味着只会提升sql查询速度，也有慢的例子。
所以这次通过hint的方式不让优化器选择start transformation的方式，从而5分钟以内完成sql
2.并行何时才能真正的用到。
基本上三种情况下能用到parallel
即全表扫描 fast full index scan , index range scan based on partitioned index!
3.hint的使用，显然在测试的时候一些hint生效了，一些hint没生效,有些自己可以理解，有些还需要深入研究。