================START==============================
来了一个mail说是job跑得很慢，调查下原因
先来看下sql:
SELECT h.order_no AS SO_no,
h.order_type,
h1.order_no AS po_no,
l1.order_line_key AS PO_LKEY,
l1.shipnode_key,
L1.extn_is_preorder,
st.description AS current_status,
h.order_date ATG_Creation_time,
H.createts AS DOMS_CREATION_TIME,
/*HT.CREATETS FRAUD_INITITATE_TIME,*/
CASE
WHEN HT.createts IS NULL THEN NOTES.contact_time
ELSE HT.createts
END AS FRAUD_INITITATE_TIME,
NOTES.contact_time FRAUD_RESPONSE_TIME,
h1.createts PO_CREATION_TIME,
S2.status_date PO_RELEASED_TIME,
p.payment_type,
PER1.country AS SHIP_TO_COUNTRY,
l.carrier_service_code,
PER2.country AS BILL_TO_COUNTRY,
Count(l.order_line_key) AS ORDER_LINE_KEY,
SUM(l.ordered_qty) AS ORDERED_QTY,
notes.notes_key,
H.order_header_key,
YOD.expected_date AS CONSUMER_COMMUNICATED_EDD,
CASE
WHEN notes.reason_code = 'review' THEN 'Y'
ELSE 'N'
END AS MANUAL_FRAUD_RESPONSE
FROM dom.yfs_order_header h,
dom.yfs_order_header h1,
dom.yfs_order_line l,
dom.yfs_order_line l1,
dom.yfs_order_release_status s1,
dom.yfs_order_release_status s2,
dom.yfs_status st,
dom.yfs_order_hold_type ht,
dom.yfs_payment p,
dom.yfs_person_info PER1,
dom.yfs_person_info PER2,
dom.yfs_notes notes,
dom.yfs_order_date YOD
WHERE h.order_header_key = notes.table_key
AND h.order_header_key = l.order_header_key
AND h.order_header_key = ht.order_header_key(+)
/*ADDED Left outer operator by Jorn*/
AND l1.chained_from_order_line_key = l.order_line_key
AND l1.order_header_key = h1.order_header_key
AND l1.order_line_key = s1.order_line_key
AND l1.order_line_key = s2.order_line_key
AND h.order_header_key = p.order_header_key
AND per1.person_info_key = h.ship_to_key
AND per2.person_info_key = h.bill_to_key
AND YOD.order_line_key(+) = l.order_line_key
AND s1.status = st.status
AND st.process_type_key = 'PO_FULFILLMENT'
AND h.order_type <> 'OFBT'
AND s1.status_quantity > 0
AND s2.status = '1100.20'
AND L.line_type = 'INLINE'
AND h.enterprise_key = 'NIKEEUROPE'
AND ht.hold_type(+) IN ( 'FCI_CHO', 'FCI_CRO', 'FRAUD' )
AND H.document_type = '0001'
AND notes.table_name = 'YFS_ORDER_HEADER'
AND NOTES.reason_code IN ( 'approve', 'review' )
AND notes.note_text IN ( 'FeedzaiAuthResponse', 'FeedzaiResponse',
'FeedzaiReviewResponse' )
AND YOD.date_type_id(+) = 'EDD'
AND h.order_date > SYSDATE - 30
AND notes.notes_key > '20161119'
AND h.order_header_key > '20161119'
/*and h.order_header_key = '201610101300457126084958'*/
GROUP BY h.order_no,
h.order_type,
h1.order_no,
l1.order_line_key,
l1.shipnode_key,
st.description,
h.order_date,
H.createts,/*HT.CREATETS,*/
CASE
WHEN HT.createts IS NULL THEN NOTES.contact_time
ELSE HT.createts
END,
NOTES.contact_time,
h1.createts,
S2.status_date,
p.payment_type,
PER1.country,
l.carrier_service_code,
PER2.country,
notes.notes_key,
H.order_header_key,
notes.createts,
l1.extn_is_preorder,
YOD.expected_date,
CASE
WHEN notes.reason_code = 'review' THEN 'Y'
ELSE 'N'
END
看的头大，没什么思绪。From后面有13张表连接，一步步来吧，先来看看job执行情况，
select
sid,serial#,inst_id,status,event,machine，to_char(logon_time,'yyyymmdd hh24:mi:ss') from gv$session where username='EURO_BI_DEVTEAM';
SID SERIAL#
INST_ID STATUS EVENT MACHINE
TO_CHAR(LOGON_TIM SQL_ID
----------
---------- ---------- -------- ------------------------- ----------------------------------------------------------------
----------------- -------------
2647
2303 3 ACTIVE gc cr request NIKE\NKE-WIN-SQL-PC7 20161219
07:12:12 44wswfpx6086s
还在单线程执行，当时就在想，这么多表连接，单线程执行跑得完吗？？？！！！
先看下表的相关信息吧，
TABLE_NAME OWNER NUM_ROWS BLOCKS LAST_ANAL PAR
------------------------------
------------------------------ ---------- ---------- --------- ---
YFS_STATUS
DOM 795 64 18-NOV-16 NO
YFS_ORDER_DATE DOM 41787940 2683198 18-NOV-16 NO
YFS_ORDER_HOLD_TYPE DOM 13602010 1793017 18-NOV-16 NO
YFS_PAYMENT DOM 8525800 1002470 18-NOV-16 NO
YFS_ORDER_HEADER DOM 19285380 3166206 18-NOV-16 NO
YFS_NOTES DOM 175666640 11298453 18-NOV-16 NO
YFS_ORDER_RELEASE_STATUS DOM 180823567 15019008 18-NOV-16 NO
当时还没注意到统计信息的时间是11月份的，同事来了封邮件说是他正在收集统计信息，好吧，反正应用那边已经kill掉这个job了，那就等等收集统计信息吧。（自己觉得收集不收集在这里影响不是很大，主要是十来张表join，绝大部分都是千万级别，百万级别的大表） 因为sql语句列里面有AND notes.notes_key > '20161119' AND h.order_header_key > '20161119' ，那么就更新下统计信息吧。
期间，看看这个sql的执行计划，如下：
SQL_ID 44wswfpx6086s,
child number 0
Plan hash value: 2927843514
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | | |
| 64 (100)| |
| 1 | HASH GROUP BY | | 1 |
746 | 64 (2)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 |
746 | 63 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 |
746 | 63 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 |
700 | 62 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 |
664 | 50 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 |
619 | 47 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 |
591 | 44 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | 1 |
563 | 41 (0)| 00:00:01 |
| 9 | NESTED LOOPS OUTER | | 1 |
522 | 29 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | 1 |
474 | 25 (0)| 00:00:01 |
| 11 | NESTED LOOPS OUTER | | 1 |
403 | 19 (0)| 00:00:01 |
| 12 | NESTED LOOPS | | 1 |
361 | 15 (0)| 00:00:01 |
| 13 | NESTED LOOPS | | 1 |
291 | 11 (0)| 00:00:01 |
| 14 | NESTED LOOPS | | 1 |
162 | 7 (0)| 00:00:01 |
| 15 | TABLE ACCESS BY INDEX ROWID|
YFS_PAYMENT | 1 |
36 | 5 (0)| 00:00:01 |
|* 16 |
INDEX RANGE SCAN | YFS_PAYMENT_I2 | 1 |
| 4 (0)| 00:00:01 |
|* 17 |
TABLE ACCESS BY INDEX ROWID| YFS_ORDER_HEADER | 1 |
126 | 3 (0)| 00:00:01 |
|* 18 |
INDEX UNIQUE SCAN |
YFS_ORDER_HEADER_PK | 1 |
| 2 (0)| 00:00:01 |
|* 19 |
TABLE ACCESS BY INDEX ROWID | YFS_NOTES | 1 |
129 | 4 (0)| 00:00:01 |
|* 20 |
INDEX RANGE SCAN |
YFS_NOTES_PK |
1 | | 3
(0)| 00:00:01 |
|* 21 |
TABLE ACCESS BY INDEX ROWID |
YFS_ORDER_LINE | 1 |
70 | 4 (0)| 00:00:01 |
|* 22 |
INDEX RANGE SCAN |
YFS_ORDER_LINE_I1 | 1
| | 3
(0)| 00:00:01 |
| 23 | TABLE ACCESS BY INDEX ROWID | YFS_ORDER_HOLD_TYPE |
1 | 42 | 4
(0)| 00:00:01 |
|* 24 |
INDEX RANGE SCAN |
YFS_ORDER_HOLD_TYPE_I1 | 1 |
| 3 (0)| 00:00:01 |
| 25 | TABLE ACCESS BY INDEX ROWID | YFS_ORDER_LINE | 1 |
71 | 6 (0)| 00:00:01 |
|* 26 |
INDEX RANGE SCAN |
YFS_ORDER_LINE_I6 | 2 |
| 3 (0)| 00:00:01 |
| 27 | TABLE ACCESS BY INDEX ROWID | YFS_ORDER_DATE | 1 |
48 | 4 (0)| 00:00:01 |
|* 28 |
INDEX RANGE SCAN |
EXTN_YFS_ORDER_DATE_I1 | 1 |
| 3 (0)| 00:00:01 |
|* 29 | TABLE
ACCESS BY INDEX ROWID |
YFS_ORDER_RELEASE_STATUS | 1 |
41 | 12 (0)| 00:00:01 |
|* 30 |
INDEX RANGE SCAN |
YFS_ORDER_RELEASE_STATUS_I2 | 9
| | 3
(0)| 00:00:01 |
| 31 | TABLE ACCESS BY INDEX ROWID | YFS_PERSON_INFO | 1 |
28 | 3 (0)| 00:00:01 |
|* 32 | INDEX
UNIQUE SCAN |
YFS_PERSON_INFO_PK | 1 |
| 2 (0)| 00:00:01 |
| 33 | TABLE ACCESS BY INDEX ROWID | YFS_PERSON_INFO | 1 |
28 | 3 (0)| 00:00:01 |
|* 34 | INDEX
UNIQUE SCAN |
YFS_PERSON_INFO_PK | 1 |
| 2 (0)| 00:00:01 |
| 35 | TABLE ACCESS BY INDEX ROWID | YFS_ORDER_HEADER | 1 |
45 | 3 (0)| 00:00:01 |
|* 36 | INDEX
UNIQUE SCAN |
YFS_ORDER_HEADER_PK | 1 |
| 2 (0)| 00:00:01 |
|* 37 | TABLE
ACCESS BY INDEX ROWID |
YFS_ORDER_RELEASE_STATUS | 2 |
72 | 12 (0)| 00:00:01 |
|* 38 | INDEX
RANGE SCAN |
YFS_ORDER_RELEASE_STATUS_I2 | 9
| | 3
(0)| 00:00:01 |
|* 39 | INDEX
UNIQUE SCAN | YFS_STATUS_I1 | 1 |
| 0 (0)|
|
| 40 | TABLE ACCESS BY INDEX ROWID | YFS_STATUS | 1 |
46 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------------
都是走索引，然后nested loop，这里我怀疑的一点就是走nested loop，虽然说是有索引走，但是数据量那么大，能行吗？
数据量大不大，当然还是得看看过滤条件filter之后的数据量多少了。
这里自己没去检查各个谓词条件过滤之后的数据量，大致感觉了下，虽说执行计划看上去可以，但总感觉单线程，然后过滤之后数据量还是很多走nested
loop---》很慢！！！
(当然以后不能凭感觉。。。。)
拿showplan.sql看了下大致的信息，
------------- SQL Plan (Plan Hash Value:2927843514; Parsed
by schema:EURO_BI_DEVTEAM) --------------
0 ( )SELECT STATEMENT Optimizer=ALL_ROWS
1 (0) HASH (GROUP BY) (Cost=64 Card=0 rows
Bytes=0/746)
2 (1)
NESTED LOOPS (Cost=63 Card=0 rows Bytes=0/746)
3 (2)
NESTED LOOPS (Cost=63 Card=0 rows Bytes=0/746)
4 (3)
NESTED LOOPS (Cost=62 Card=0 rows Bytes=0/700)
5 (4)
NESTED LOOPS (Cost=50 Card=0 rows Bytes=0/664)
6 (5)
NESTED LOOPS (Cost=47 Card=0 rows Bytes=0/619)
7 (6)
NESTED LOOPS (Cost=44 Card=0 rows Bytes=0/591)
8 (7)
NESTED LOOPS (Cost=41 Card=0 rows Bytes=0/563)
9 (8)
NESTED LOOPS (OUTER) (Cost=29 Card=0 rows Bytes=0/522)
10 (9)
NESTED LOOPS (Cost=25 Card=0 rows Bytes=0/474)
11 (10)
NESTED LOOPS (OUTER) (Cost=19 Card=0 rows Bytes=0/403)
12 (11) NESTED LOOPS (Cost=15 Card=0 rows
Bytes=0/361)
13 (12) NESTED LOOPS (Cost=11 Card=1 rows
Bytes=0/291)
14 (13) NESTED LOOPS (Cost=7 Card=1 rows
Bytes=0/162)
15 (14) TABLE ACCESS (BY INDEX ROWID) OF
'YFS_PAYMENT' (TABLE) (Cost=5 Card=20157 rows Bytes=13025280/36)
*16 (15) INDEX (RANGE SCAN) OF
'YFS_PAYMENT_I2' (INDEX) (Cost=4 Card=20157 rows Bytes=1220608/)
#17 (14)
TABLE ACCESS (BY INDEX
ROWID) OF 'YFS_ORDER_HEADER' (TABLE) (Cost=3 Card=1 rows Bytes=9945088/126)
*#18 (17) INDEX (UNIQUE SCAN) OF
'YFS_ORDER_HEADER_PK' (INDEX (UNIQUE)) (Cost=2 Card=20157 rows Bytes=2015232/)
#19 (13)
TABLE ACCESS (BY INDEX ROWID)
OF 'YFS_NOTES' (TABLE) (Cost=4 Card=1 rows Bytes=16228147200/129)
*20 (19) INDEX (RANGE SCAN) OF
'YFS_NOTES_PK' (INDEX (UNIQUE)) (Cost=3 Card=25202618 rows Bytes=2723184640/)
#21 (12) TABLE ACCESS (BY INDEX ROWID) OF
'YFS_ORDER_LINE' (TABLE) (Cost=4 Card=0 rows Bytes=0/70)
*#22 (21) INDEX (RANGE SCAN) OF
'YFS_ORDER_LINE_I1' (INDEX (UNIQUE)) (Cost=3 Card=1 rows Bytes=16384/)
23 (11) TABLE ACCESS (BY INDEX ROWID) OF
'YFS_ORDER_HOLD_TYPE' (TABLE) (Cost=4 Card=0 rows Bytes=0/42)
*#24 (23) INDEX (RANGE SCAN) OF
'YFS_ORDER_HOLD_TYPE_I1' (INDEX (UNIQUE)) (Cost=3 Card=0 rows Bytes=0/)
25 (10)
TABLE ACCESS (BY INDEX ROWID) OF 'YFS_ORDER_LINE' (TABLE) (Cost=6 Card=0
rows Bytes=0/71)
*#26 (25) INDEX (RANGE SCAN) OF
'YFS_ORDER_LINE_I6' (INDEX) (Cost=3 Card=0 rows Bytes=0/)
27 (9)
TABLE ACCESS (BY INDEX ROWID) OF 'YFS_ORDER_DATE' (TABLE) (Cost=4 Card=0
rows Bytes=0/48)
*28 (27)
INDEX (RANGE SCAN) OF 'EXTN_YFS_ORDER_DATE_I1' (INDEX) (Cost=3 Card=0
rows Bytes=0/)
#29 (8)
TABLE ACCESS (BY INDEX ROWID) OF 'YFS_ORDER_RELEASE_STATUS' (TABLE)
(Cost=12 Card=0 rows Bytes=0/41)
*30 (29)
INDEX (RANGE SCAN) OF
'YFS_ORDER_RELEASE_STATUS_I2' (INDEX) (Cost=3 Card=0 rows Bytes=0/)
31 (7)
TABLE ACCESS (BY INDEX ROWID) OF 'YFS_PERSON_INFO' (TABLE) (Cost=3
Card=0 rows Bytes=0/28)
*32 (31)
INDEX (UNIQUE SCAN) OF 'YFS_PERSON_INFO_PK' (INDEX (UNIQUE)) (Cost=2
Card=0 rows Bytes=0/)
33 (6)
TABLE ACCESS (BY INDEX ROWID) OF 'YFS_PERSON_INFO' (TABLE) (Cost=3
Card=0 rows Bytes=0/28)
*34 (33)
INDEX (UNIQUE SCAN) OF 'YFS_PERSON_INFO_PK' (INDEX (UNIQUE)) (Cost=2
Card=0 rows Bytes=0/)
35 (5)
TABLE ACCESS (BY INDEX ROWID) OF 'YFS_ORDER_HEADER' (TABLE) (Cost=3
Card=0 rows Bytes=0/45)
*36 (35)
INDEX (UNIQUE SCAN) OF 'YFS_ORDER_HEADER_PK' (INDEX (UNIQUE)) (Cost=2
Card=0 rows Bytes=0/)
#37 (4)
TABLE ACCESS (BY INDEX ROWID) OF 'YFS_ORDER_RELEASE_STATUS' (TABLE)
(Cost=12 Card=0 rows Bytes=0/72)
*38 (37)
INDEX (RANGE SCAN) OF 'YFS_ORDER_RELEASE_STATUS_I2' (INDEX) (Cost=3
Card=0 rows Bytes=0/)
*39 (3)
INDEX (UNIQUE SCAN) OF 'YFS_STATUS_I1' (INDEX (UNIQUE))
40 (2)
TABLE ACCESS (BY INDEX ROWID) OF 'YFS_STATUS' (TABLE) (Cost=1 Card=0
rows Bytes=0/46)
------------- Predicate Information (Plan Hash
Value:2927843514) --------------
16 Access:
"P"."ORDER_HEADER_KEY">'20161119'
17 Filter:
("H"."ENTERPRISE_KEY"='NIKEEUROPE' AND
"H"."DOCUMENT_TYPE"='0001' AND
"H"."ORDER_DATE">SYSDATE@!-30 AND
"H"."ORDER_TYPE"<>'OFBT')
18 Access:
"H"."ORDER_HEADER_KEY"="P"."ORDER_HEADER_KEY"
18 Filter:
"H"."ORDER_HEADER_KEY">'20161119'
19 Filter:
("NOTES"."TABLE_KEY">'20161119' AND
"NOTES"."TABLE_NAME"='YFS_ORDER_HEADER' AND
INTERNAL_FUNCTION("NOTES"."REASON_CODE") AND
INTERNAL_FUNCTION("NOTES"."NOTE_TEXT") AND
"H"."ORDER_HEADER_KEY"="NOTES"."TABLE_KEY")
20 Access:
"NOTES"."NOTES_KEY">'20161119'
21 Filter:
"L"."LINE_TYPE"='INLINE'
22 Access:
"H"."ORDER_HEADER_KEY"="L"."ORDER_HEADER_KEY"
22 Filter:
"L"."ORDER_HEADER_KEY">'20161119'
24 Access:
"H"."ORDER_HEADER_KEY"="HT"."ORDER_HEADER_KEY"
24 Filter:
("HT"."ORDER_HEADER_KEY">'20161119' AND
INTERNAL_FUNCTION("HT"."HOLD_TYPE"))
26 Access:
"L1"."CHAINED_FROM_ORDER_LINE_KEY"="L"."ORDER_LINE_KEY"
26 Filter:
"L1"."CHAINED_FROM_ORDER_LINE_KEY" IS NOT NULL
28 Access:
"YOD"."ORDER_LINE_KEY"="L"."ORDER_LINE_KEY"
AND "YOD"."DATE_TYPE_ID"='EDD'
29 Filter:
"S2"."STATUS"='1100.20'
30 Access:
"L1"."ORDER_LINE_KEY"="S2"."ORDER_LINE_KEY"
32 Access:
"PER1"."PERSON_INFO_KEY"="H"."SHIP_TO_KEY"
34 Access:
"PER2"."PERSON_INFO_KEY"="H"."BILL_TO_KEY"
36 Access:
"L1"."ORDER_HEADER_KEY"="H1"."ORDER_HEADER_KEY"
37 Filter:
"S1"."STATUS_QUANTITY">0
38 Access:
"L1"."ORDER_LINE_KEY"="S1"."ORDER_LINE_KEY"
39 Access:
"S1"."STATUS"="ST"."STATUS" AND
"ST"."PROCESS_TYPE_KEY"='PO_FULFILLMENT'
------------- Plan Loading (Plan Hash Value:2927843514)
--------------
19: TABLE ACCESS BY
INDEX ROWID
############################################(87.13%)
20: INDEX RANGE
SCAN
######(12.83%)
17: TABLE ACCESS BY
INDEX ROWID (.02%)
15: TABLE ACCESS BY
INDEX ROWID (.01%)
18: INDEX UNIQUE
SCAN (0%)
------------- Waits Events (Plan Hash Value:2927843514)
--------------
db file sequential read on DOM.YFS_NOTES(TABLE)
#################(34.28%)
gc cr grant 2-way on DOM.YFS_NOTES(TABLE) ###########(22.4%)
ON CPU on DOM.YFS_NOTES(TABLE)
###########(21.25%)
db file sequential read on DOM.YFS_NOTES_PK(INDEX) ###(5.7%)
ON CPU on DOM.YFS_NOTES_PK(INDEX) ##(4.22%)
gc cr grant 2-way on DOM.YFS_NOTES_PK(INDEX) ##(3.52%)
gc current grant 2-way on DOM.YFS_NOTES(TABLE) (.34%)
gc cr request on DOM.YFS_NOTES(TABLE) (.33%)
gc current grant 2-way on DOM.YFS_NOTES_PK(INDEX) (.07%)
gc cr request on DOM.YFS_NOTES_PK(INDEX) (.04%)
gc cr block 3-way on DOM.YFS_NOTES(TABLE) (.04%)
db file sequential read on DOM.YFS_PAYMENT(TABLE) (.01%)
gc cr block busy on DOM.YFS_NOTES(TABLE) (.01%)
gc cr grant congested on DOM.YFS_NOTES(TABLE) (.01%)
db file sequential read on DOM.YFS_ORDER_HEADER(TABLE) (0%)
db file sequential read on
DOM.YFS_ORDER_HEADER_PK(INDEX)
(0%)
gc cr grant 2-way on DOM.YFS_ORDER_HEADER(TABLE) (0%)
gc cr grant congested on DOM.YFS_NOTES_PK(INDEX) (0%)
gc current block 2-way on DOM.YFS_ORDER_HEADER(TABLE) (0%)
gc current block 3-way on DOM.YFS_NOTES(TABLE) (0%)
gc current block 3-way on DOM.YFS_ORDER_HEADER(TABLE) (0%)
gc current grant 2-way on DOM.YFS_PAYMENT(TABLE) (0%)
------------- Statistics Data (Plan Hash
Value:2927843514)--------------
Loads: 1
Load Versions: 1
First Load Time: 2016-12-19/01:18:19
Last Load Time: 2016-12-19/01:18:19
User Openings: 1
Parse Calls: 4
Executions: 2
Sorts(Average): 0
Fetches(Average): .5
Disk Reads(Average): 19062032
Buffer Gets(Average): 127496730
Elapsed Time(Average): 10030.967 seconds
CPU Time(Average): 4224.743 seconds
Run Time Memory(Average): .041M
PGA Size(Maximum): .006G
Temp Space(Maximum): 0G
等收集完统计信息：
TABLE_NAME OWNER NUM_ROWS BLOCKS TO_CHAR(LAST_ANAL PAR
------------------------------
------------------------------ ---------- ---------- ----------------- ---
YFS_STATUS DOM 795 64 20161219 07:08:31 NO
YFS_ORDER_DATE DOM 51787940 2683198 20161219 07:25:09 NO
YFS_ORDER_HOLD_TYPE
DOM 16602010 1793017 20161219 07:16:22 NO
YFS_PAYMENT DOM 9725800 1002470 20161219 07:12:52 NO
YFS_ORDER_HEADER DOM 21285380 3166206 20161219 07:27:58 NO
YFS_NOTES DOM 195666640 11298453 20161219 07:54:21 NO
YFS_ORDER_RELEASE_STATUS DOM 225517433 15019008 20161219 08:00:35 NO
应用重新跑，
接近7小时过去了，还没跑完。
select sid,serial#,inst_id,status,event,machine,to_char(logon_time,'yyyymmdd
hh24:mi:ss'),last_call_et,sql_id from gv$session where
username='EURO_BI_DEVTEAM';
SID SERIAL#
INST_ID STATUS EVENT MACHINE TO_CHAR(LOGON_TIM
LAST_CALL_ET SQL_ID
---------- ---------- ---------- --------
----------------------------
----------------------------------------------------------------
----------------- ------------ -------------
2647 2303 3 ACTIVE gc cr request NIKE\NKE-WIN-SQL-PC7 20161219
07:12:12 22145 44wswfpx6086s
执行计划没变。
自己的分析思路是这样的，能不能先找个经个谓词条件过滤之后数据量小的表来驱动下：
select /*+ parallel(8) */ count(*) from dom.yfs_notes notes
where notes.table_name = 'YFS_ORDER_HEADER' AND NOTES.reason_code IN (
'approve', 'review' )
AND
notes.note_text IN ( 'FeedzaiAuthResponse', 'FeedzaiResponse',
'FeedzaiReviewResponse' ) AND
notes.notes_key > '20161119' ;
COUNT(*)
----------
2859803
Elapsed: 00:00:56.64
这个数据量好像还是有点多呀，300W.。。于是放弃拿小表先去驱动的这一个想法了。
另外的想法，能不能走hash join，配合并行全表扫描走hash join会怎么样，
那么怎么才能弄成这样的方式呢？自己试下下面几个hint，那么一开始的想法是看到原先的执行计划，全是index
range scan和index unique scan的执行计划，然后nested loop，自己想的是这个index
range scan和index unique scan走不了并行呀，那我得先弄成hash join，再用上并行。实际上在下面的测试中，
- 先添加use_hash的hint，走了全表扫描了，当然有几步是INDEX
RANGE SCAN 和INDEX
UNIQUE SCAN，并且有nested loop - 在测试下用full hint，也不是很想要
- 加上parallel和use_hash
hint，看了下好象不错，走的并行全表扫描和hash
join - 单独放个parallel hint，奇迹出现了，就是走的并行全表扫描和hash
join(当然也有nested loop操作)，这一点倒是出乎自己的意料，以及是自己的理解不够，自己理解成没加并行走的index
range scan和index unique scan，加了并行的hint的话，并行也起不了作用，还不是照样的index unique/range scan，其实不是这样思考的，得这样想，加了parallel的hint，优化器会再去选择更好的方式去执行这条sql，所以自己的逻辑一开始出现了点问题。Index
range/unique scan用不到并行，但并不意味着加了hint之后优化器还会继续选择index range/unique scan，所以一切要以优化器选的执行计划出发来考量。
如下是测试的时候4个执行计划：
Use_hash hint
SELECT /*+ use_hash(h
notes h1 l l1 s1 s2 st ht p YOD PER1 PER2 ) */
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem |
1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | | |
| | |
| 1 | HASH GROUP BY | | 1780 |
2036K| 931K| |
|* 2 | HASH JOIN OUTER | | 1780 |
1515K| 933K| |
|* 3 | HASH JOIN | | 1252 |
1461K| 935K| |
|* 4 | HASH JOIN | | 1252 |
1640K| 937K| |
|* 5 | HASH JOIN | | 1581 |
1245K| 1245K| 1254K (0)|
|* 6 | TABLE ACCESS FULL | YFS_STATUS | 70 |
| | |
|* 7 | HASH JOIN | | 2172 |
1296K| 943K| |
|* 8 | HASH JOIN | | 1199 |
1128K| 950K| |
|* 9 | HASH JOIN | | 1042 |
959K| 959K| |
|* 10 | HASH
JOIN OUTER | | 906 |
906K| 906K| |
| 11 | NESTED LOOPS | |
854 | | | |
| 12 | NESTED LOOPS | | 854 |
| | |
| 13 | NESTED LOOPS | | 853 | |
| |
| 14 | NESTED LOOPS | | 853 |
| | |
|* 15 |
HASH JOIN | | 856 |
803K| 803K| |
|* 16 |
TABLE ACCESS FULL |
YFS_NOTES | 856 |
| | |
|* 17 |
TABLE ACCESS BY INDEX ROWID| YFS_ORDER_HEADER |
169K| | | |
|* 18 | INDEX RANGE SCAN | YFS_ORDER_HEADER_I2 |
644K| | | |
| 19 | TABLE ACCESS BY INDEX ROWID |
YFS_PERSON_INFO | 1 |
| | |
|* 20 |
INDEX UNIQUE SCAN |
YFS_PERSON_INFO_PK | 1 |
| | |
| 21 | TABLE ACCESS BY INDEX ROWID | YFS_PERSON_INFO | 1 |
| | |
|* 22 |
INDEX UNIQUE SCAN | YFS_PERSON_INFO_PK |
1 | | | |
|* 23 |
INDEX RANGE SCAN |
YFS_PAYMENT_I2 | 1 |
| | |
| 24 | TABLE ACCESS BY INDEX ROWID | YFS_PAYMENT | 1 |
| | |
|* 25 |
TABLE ACCESS FULL |
YFS_ORDER_HOLD_TYPE | 467K|
| | |
|* 26 | TABLE
ACCESS FULL |
YFS_ORDER_LINE | 4626K|
| | |
|* 27 | TABLE
ACCESS FULL |
YFS_ORDER_LINE | 5322K|
| | |
|* 28 | TABLE
ACCESS FULL | YFS_ORDER_RELEASE_STATUS |
36M| | | |
| 29 | TABLE ACCESS BY INDEX ROWID | YFS_ORDER_RELEASE_STATUS |
4214K| | | |
|* 30 | INDEX
RANGE SCAN | YFS_ORDER_RELEASE_STATUS_I4
| 4214K| |
| |
| 31 | TABLE ACCESS FULL | YFS_ORDER_HEADER | 21M|
| | |
|* 32 | TABLE
ACCESS FULL |
YFS_ORDER_DATE | 14M|
| | |
---------------------------------------------------------------------------------------------------------------------
Full hint
SELECT /*+ full(h)
full(notes) full(h1) full(l) full(s1) full(s2) full(st) full(ht) full(p)
full(YOD) full(PER1) full(PER2) */
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | | |
| | 22M(100)| |
| 1 | HASH GROUP BY | | 1780 |
1296K| 1432K| 22M
(1)| 75:19:56 |
|* 2 | HASH JOIN OUTER | | 1780 |
1296K| | 22M
(1)| 75:19:52 |
|* 3 | HASH JOIN | | 1678 |
1153K| | 22M
(1)| 73:42:39 |
|* 4 | HASH JOIN | | 1675 |
1091K| | 21M
(1)| 72:48:18 |
|* 5 | HASH JOIN | | 1675 |
1045K| | 18M
(1)| 62:44:05 |
|* 6 | HASH JOIN OUTER | | 1682 |
1003K| | 15M
(1)| 52:39:51 |
|* 7 | HASH JOIN | | 1183 |
650K| | 15M
(1)| 50:14:19 |
|* 8 | HASH JOIN | | 1182 |
597K| | 14M
(1)| 47:22:25 |
|* 9 | HASH JOIN | | 1493 |
695K| | 10M
(1)| 33:47:58 |
|* 10 |
TABLE ACCESS FULL |
YFS_STATUS | 70 |
3220 | | 19
(0)| 00:00:01 |
|* 11 | HASH
JOIN | | 2051 |
863K| | 10M
(1)| 33:47:58 |
| 12 | NESTED LOOPS | | 1133 |
437K| | 6067K
(1)| 20:13:27 |
| 13 | NESTED LOOPS | | 1968 |
437K| | 6067K
(1)| 20:13:27 |
|* 14 |
HASH JOIN | | 984 |
311K| | 6064K
(1)| 20:12:50 |
|* 15 |
HASH JOIN | | 856 |
212K| | 3922K
(1)| 13:04:34 |
|* 16 |
TABLE ACCESS FULL |
YFS_NOTES | 856 |
107K| | 3064K
(1)| 10:12:52 |
|* 17 |
TABLE ACCESS FULL |
YFS_ORDER_HEADER | 169K|
20M| | 858K
(1)| 02:51:43 |
|* 18 |
TABLE ACCESS FULL |
YFS_ORDER_LINE | 4626K|
308M| | 2141K
(1)| 07:08:16 |
|* 19 | INDEX RANGE SCAN | YFS_ORDER_LINE_I6 |
2 | | |
3 (0)| 00:00:01 |
| 20 | TABLE ACCESS BY INDEX ROWID|
YFS_ORDER_LINE | 1 |
71 | | 5
(0)| 00:00:01 |
|* 21 | TABLE ACCESS FULL | YFS_ORDER_RELEASE_STATUS | 36M|
1249M| | 4072K
(1)| 13:34:31 |
|* 22 | TABLE
ACCESS FULL |
YFS_ORDER_RELEASE_STATUS | 4214K| 164M|
| 4072K (1)| 13:34:28 |
| 23 | TABLE ACCESS FULL | YFS_ORDER_HEADER |
21M| 913M| |
859K (1)| 02:51:53 |
|* 24 | TABLE
ACCESS FULL |
YFS_ORDER_DATE | 14M|
656M| | 727K
(1)| 02:25:32 |
| 25 | TABLE ACCESS FULL | YFS_PERSON_INFO |
156M| 4188M| |
3020K (1)| 10:04:09 |
| 26 | TABLE ACCESS FULL | YFS_PERSON_INFO |
156M| 4188M| |
3020K (1)| 10:04:09 |
|* 27 | TABLE
ACCESS FULL | YFS_PAYMENT |
3297K| 116M| |
271K (1)| 00:54:22 |
|* 28 | TABLE
ACCESS FULL |
YFS_ORDER_HOLD_TYPE | 467K|
18M| | 486K
(1)| 01:37:14 |
---------------------------------------------------------------------------------------------------------------------------
Use_hash and parallel
SELECT /*+ parallel(16) use_hash(h notes h1 l l1 s1 s2 st ht
p YOD PER1 PER2 ) */
Plan hash value: 3826480503
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation |
Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT
| |
| | |
1725K(100)| | |
| |
| 1 | PX COORDINATOR | | |
| | | | |
| |
| 2 | PX
SEND QC (RANDOM)
| :TQ10019 | 900K|
640M| | 1725K
(1)| 05:45:07 | Q1,19 | P->S |
QC (RAND) |
| 3 | HASH GROUP BY | | 900K|
640M| 703M| 1725K
(1)| 05:45:07 | Q1,19 | PCWP | |
| 4 | PX RECEIVE | | 900K|
640M| | 1716K
(1)| 05:43:21 | Q1,19 | PCWP
| |
| 5 | PX SEND HASH |
:TQ10018 | 900K|
640M| | 1716K
(1)| 05:43:21 | Q1,18 | P->P |
HASH |
|* 6 | HASH JOIN OUTER BUFFERED | | 900K|
640M| | 1716K
(1)| 05:43:21 | Q1,18 | PCWP
| |
| 7 | PX RECEIVE | | 633K|
421M| | 1666K
(1)| 05:33:15 | Q1,18 | PCWP
| |
| 8 | PX SEND HASH |
:TQ10016 | 633K|
421M| | 1666K
(1)| 05:33:15 | Q1,16 | P->P |
HASH |
|* 9 | HASH JOIN BUFFERED | | 633K|
421M| | 1666K
(1)| 05:33:15 | Q1,16 | PCWP
| |
| 10 | PX RECEIVE | | 70 |
3220 | | 2
(0)| 00:00:01 | Q1,16 | PCWP
| |
| 11 | PX SEND BROADCAST | :TQ10013 | 70 |
3220 | | 2
(0)| 00:00:01 | Q1,13 | P->P |
BROADCAST |
| 12 | PX BLOCK ITERATOR | | 70 |
3220 | | 2
(0)| 00:00:01 | Q1,13 | PCWC
| |
|* 13 |
TABLE ACCESS FULL
| YFS_STATUS | 70 |
3220 | | 2
(0)| 00:00:01 | Q1,13 | PCWP | |
|* 14 | HASH JOIN | | 870K|
541M| | 1666K
(1)| 05:33:15 | Q1,16 | PCWP | |
| 15 | PX RECEIVE | | 480K|
282M| | 1383K
(1)| 04:36:44 | Q1,16 | PCWP
| |
| 16 | PX SEND HASH | :TQ10014 | 480K|
282M| | 1383K
(1)| 04:36:44 | Q1,14 | P->P |
HASH |
|* 17 | HASH
JOIN BUFFERED | | 480K|
282M| | 1383K
(1)| 04:36:44 | Q1,14 | PCWP
| |
| 18 | PX RECEIVE | | 480K|
261M| |
1323K (1)| 04:24:48 | Q1,14 | PCWP | |
| 19 | PX SEND HASH | :TQ10011 | 480K|
261M| | 1323K
(1)| 04:24:48 | Q1,11 | P->P |
HASH |
|* 20 |
HASH JOIN BUFFERED
| | 480K|
261M| | 1323K
(1)| 04:24:48 | Q1,11 | PCWP
| |
| 21 | PX RECEIVE | | 4214K|
164M| | 282K (1)|
00:56:32 | Q1,11 | PCWP | |
| 22 | PX SEND HASH | :TQ10009 | 4214K|
164M| | 282K
(1)| 00:56:32 | Q1,09 | P->P |
HASH |
| 23 | PX BLOCK ITERATOR | | 4214K|
164M| | 282K
(1)| 00:56:32 | Q1,09 | PCWC
| |
|* 24 |
TABLE ACCESS FULL |
YFS_ORDER_RELEASE_STATUS | 4214K| 164M|
| 282K (1)| 00:56:32 | Q1,09 | PCWP | |
| 25 | PX RECEIVE | | 606K|
306M| | 1041K
(1)| 03:28:17 | Q1,11 | PCWP
| |
| 26 | PX SEND HASH | :TQ10010 | 606K|
306M| | 1041K
(1)| 03:28:17 | Q1,10 | P->P |
HASH |
|* 27 |
HASH JOIN BUFFERED
| | 606K|
306M| | 1041K
(1)| 03:28:17 | Q1,10 | PCWP
| |
| 28 | JOIN FILTER CREATE | :BF0000 | 737K|
322M| | 892K
(1)| 02:58:33 | Q1,10 | PCWP
| |
| 29 | PX RECEIVE | | 737K|
322M| | 892K
(1)| 02:58:33 | Q1,10 | PCWP
| |
| 30 | PX SEND HASH | :TQ10007 | 737K|
322M| | 892K
(1)| 02:58:33 | Q1,07 | P->P |
HASH |
|* 31 | HASH JOIN BUFFERED | | 737K|
322M| | 892K
(1)| 02:58:33 | Q1,07 | PCWP | |
|* 32 | HASH JOIN RIGHT
OUTER | |
605K| 224M| |
744K (1)| 02:28:50 | Q1,07 | PCWP | |
| 33 | PX RECEIVE | | 142K|
5850K| | 33732 (1)| 00:06:45 | Q1,07 | PCWP | |
| 34 | PX SEND HASH | :TQ10002 | 142K|
5850K| | 33732 (1)| 00:06:45 | Q1,02 | P->P | HASH |
| 35 | PX BLOCK
ITERATOR | | 142K|
5850K| | 33732 (1)| 00:06:45 | Q1,02 | PCWC | |
|* 36 | TABLE ACCESS
FULL | YFS_ORDER_HOLD_TYPE |
142K| 5850K| | 33732
(1)| 00:06:45 | Q1,02 | PCWP
| |
|* 37 | HASH JOIN | | 594K|
196M| | 710K
(1)| 02:22:05 | Q1,07 | PCWP | |
|* 38 | HASH JOIN | | 575K|
120M| |
497K (1)| 01:39:33 | Q1,07 | PCWP | |
| 39 | PX RECEIVE | | 574K|
99M| | 478K
(1)| 01:35:47 | Q1,07 | PCWP
| |
| 40 | PX SEND HASH | :TQ10003 | 574K|
99M| | 478K
(1)| 01:35:47 | Q1,03 | P->P |
HASH |
|* 41 | HASH JOIN | | 574K|
99M| | 478K
(1)| 01:35:47 | Q1,03 | PCWP
| |
| 42 | PX RECEIVE | | 577K|
84M| | 269K
(1)| 00:53:51 | Q1,03 | PCWP
| |
| 43 | PX SEND
BROADCAST | :TQ10001 | 577K|
84M| | 269K
(1)| 00:53:51 | Q1,01 | P->P |
BROADCAST |
|* 44 | HASH JOIN | | 577K|
84M| |
269K (1)| 00:53:51 | Q1,01 | PCWP | |
| 45 | PX
RECEIVE | | 577K|
69M| | 59579 (1)| 00:11:55 | Q1,01 | PCWP | |
| 46 | PX SEND BROADCAST | :TQ10000 | 577K|
69M| | 59579 (1)| 00:11:55 | Q1,00 | P->P | BROADCAST |
| 47 | PX BLOCK
ITERATOR |
| 577K| 69M|
| 59579 (1)| 00:11:55 | Q1,00 | PCWC | |
|* 48 | TABLE
ACCESS FULL| YFS_ORDER_HEADER
| 577K| 69M|
| 59579 (1)| 00:11:55 | Q1,00 | PCWP | |
| 49 | PX BLOCK ITERATOR | | 156M|
4188M| | 209K
(1)| 00:41:56 | Q1,01 | PCWC
| |
|* 50 | TABLE ACCESS
FULL | YFS_PERSON_INFO |
156M| 4188M| |
209K (1)| 00:41:56 | Q1,01 | PCWP | |
| 51 | PX BLOCK
ITERATOR | | 156M|
4188M| | 209K
(1)| 00:41:56 | Q1,03 | PCWC
| |
|* 52 | TABLE ACCESS
FULL | YFS_PERSON_INFO |
156M| 4188M| |
209K (1)| 00:41:56 | Q1,03 | PCWP | |
| 53 | PX RECEIVE | | 3297K|
116M| | 18859 (1)| 00:03:47 | Q1,07 | PCWP | |
| 54 | PX SEND HASH | :TQ10004 | 3297K|
116M| | 18859 (1)| 00:03:47 | Q1,04 | P->P | HASH |
| 55 | PX BLOCK ITERATOR
| | 3297K|
116M| | 18859 (1)| 00:03:47 | Q1,04 | PCWC | |
|* 56 | TABLE ACCESS
FULL | YFS_PAYMENT |
3297K| 116M| | 18859
(1)| 00:03:47 | Q1,04 | PCWP
| |
| 57 | PX RECEIVE | | 3039K|
371M| | 212K
(1)| 00:42:32 | Q1,07 | PCWP
| |
| 58 | PX SEND HASH | :TQ10005 | 3039K|
371M| | 212K
(1)| 00:42:32 | Q1,05 | P->P |
HASH |
| 59 | PX BLOCK
ITERATOR | | 3039K|
371M| | 212K
(1)| 00:42:32 | Q1,05 | PCWC
| |
|* 60 | TABLE ACCESS
FULL | YFS_NOTES | 3039K|
371M| | 212K
(1)| 00:42:32 | Q1,05 | PCWP | |
| 61 | PX RECEIVE | |
6469K| 431M| |
148K (1)| 00:29:44 | Q1,07 | PCWP | |
| 62 | PX SEND HASH | :TQ10006 | 6469K|
431M| | 148K
(1)| 00:29:44 | Q1,06 | P->P |
HASH |
| 63 | PX BLOCK
ITERATOR | | 6469K|
431M| | 148K
(1)| 00:29:44 | Q1,06 | PCWC
| |
|* 64 | TABLE ACCESS
FULL | YFS_ORDER_LINE |
6469K| 431M| |
148K (1)| 00:29:44 | Q1,06 | PCWP | |
| 65 | PX RECEIVE | | 5322K|
360M| | 148K
(1)| 00:29:44 | Q1,10 | PCWP
| |
| 66 | PX SEND HASH | :TQ10008 | 5322K|
360M| | 148K
(1)| 00:29:44 | Q1,08 | P->P |
HASH |
| 67 | JOIN FILTER USE | :BF0000 | 5322K|
360M| |
148K (1)| 00:29:44 | Q1,08 | PCWP | |
| 68 | PX BLOCK ITERATOR | | 5322K|
360M| | 148K
(1)| 00:29:44 | Q1,08 | PCWC
| |
|* 69 | TABLE ACCESS FULL | YFS_ORDER_LINE |
5322K| 360M| |
148K (1)| 00:29:44 | Q1,08 | PCWP | |
| 70 | PX RECEIVE | | 21M|
913M| | 59639 (1)| 00:11:56 | Q1,14 | PCWP | |
| 71 | PX SEND HASH | :TQ10012 | 21M|
913M| | 59639 (1)| 00:11:56 | Q1,12 | P->P | HASH |
| 72 | PX BLOCK ITERATOR | | 21M|
913M| | 59639 (1)| 00:11:56 | Q1,12 | PCWC | |
|* 73 |
TABLE ACCESS FULL
| YFS_ORDER_HEADER | 21M|
913M| | 59639 (1)| 00:11:56 | Q1,12 | PCWP | |
| 74 | PX RECEIVE | | 36M|
1249M| | 282K
(1)| 00:56:32 | Q1,16 | PCWP
| |
| 75 | PX SEND HASH | :TQ10015 | 36M|
1249M| | 282K
(1)| 00:56:32 | Q1,15 | P->P |
HASH |
| 76 | PX BLOCK ITERATOR | | 36M|
1249M| |
282K (1)| 00:56:32 | Q1,15 | PCWC | |
|* 77 |
TABLE ACCESS FULL
| YFS_ORDER_RELEASE_STATUS |
36M| 1249M| |
282K (1)| 00:56:32 | Q1,15 | PCWP | |
| 78 | PX RECEIVE | | 14M|
656M| | 50494 (1)| 00:10:06 | Q1,18 | PCWP | |
| 79 | PX SEND HASH |
:TQ10017 | 14M|
656M| | 50494 (1)| 00:10:06 | Q1,17 | P->P | HASH |
| 80 | PX BLOCK ITERATOR | | 14M|
656M| | 50494 (1)| 00:10:06 | Q1,17 | PCWC | |
|* 81 |
TABLE ACCESS FULL | YFS_ORDER_DATE |
14M| 656M| | 50494
(1)| 00:10:06 | Q1,17 | PCWP
| |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
单独加上parallel hint
SELECT /*+
parallel(32) */ h.order_no AS SO_no,
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation
| Name |
Rows | Bytes |TempSpc| Cost (%CPU)|
Time | TQ
|IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | | |
| | 721K(100)| | |
| |
| 1 | PX COORDINATOR
| | |
| | | | |
| |
| 2 | PX SEND QC (RANDOM) | :TQ10018 | 899K|
639M| | 721K
(1)| 02:24:17 | Q1,18 | P->S |
QC (RAND) |
| 3 | HASH GROUP BY | | 899K|
639M| 702M| 721K
(1)| 02:24:17 | Q1,18 | PCWP | |
| 4 | PX RECEIVE
| | 899K|
639M| | 716K
(1)| 02:23:24 | Q1,18 | PCWP
| |
| 5 | PX SEND HASH | :TQ10017 | 899K|
639M| | 716K
(1)| 02:23:24 | Q1,17 | P->P |
HASH |
|* 6 | HASH JOIN OUTER BUFFERED | | 899K|
639M| | 716K
(1)| 02:23:24 | Q1,17 | PCWP | |
| 7 | PX RECEIVE | | 632K|
421M| | 691K
(1)| 02:18:21 | Q1,17 | PCWP
| |
| 8 | PX SEND HASH | :TQ10015 | 632K|
421M| | 691K
(1)| 02:18:21 | Q1,15 | P->P |
HASH |
|* 9 | HASH JOIN BUFFERED | | 632K|
421M| | 691K
(1)| 02:18:21 | Q1,15 | PCWP
| |
| 10 | PX RECEIVE | | 70 |
3220 | | 2
(0)| 00:00:01 | Q1,15 | PCWP
| |
| 11 | PX SEND BROADCAST | :TQ10012 | 70 |
3220 | | 2
(0)| 00:00:01 | Q1,12 | P->P |
BROADCAST |
| 12 | PX BLOCK ITERATOR | |
70 | 3220 | |
2 (0)| 00:00:01 | Q1,12 | PCWC | |
|* 13 |
TABLE ACCESS FULL | YFS_STATUS | 70 |
3220 | | 2
(0)| 00:00:01 | Q1,12 | PCWP | |
|* 14 | HASH
JOIN
| | 869K|
540M| | 691K
(1)| 02:18:21 | Q1,15 | PCWP | |
| 15 | PX RECEIVE | |
479K| 281M| |
550K (1)| 01:50:05 | Q1,15 | PCWP | |
| 16 | PX SEND HASH |
:TQ10013 | 479K|
281M| | 550K
(1)| 01:50:05 | Q1,13 | P->P |
HASH |
|* 17 |
HASH JOIN BUFFERED | | 479K|
281M| | 550K
(1)| 01:50:05 | Q1,13 | PCWP | |
| 18 | PX RECEIVE | |
479K| 261M| |
520K (1)| 01:44:07 | Q1,13 | PCWP | |
| 19 | PX SEND HASH | :TQ10010 | 479K|
261M| | 520K
(1)| 01:44:07 | Q1,10 | P->P |
HASH |
|* 20 |
HASH JOIN RIGHT OUTER BUFFERED
| | 479K|
261M| | 520K
(1)| 01:44:07 | Q1,10 | PCWP | |
| 21 | PX RECEIVE | |
142K| 5850K| | 16866
(1)| 00:03:23 | Q1,10 | PCWP
| |
| 22 | PX SEND HASH | :TQ10008 | 142K|
5850K| | 16866 (1)| 00:03:23 | Q1,08 | P->P | HASH |
| 23 | PX BLOCK ITERATOR | | 142K|
5850K| | 16866 (1)| 00:03:23 | Q1,08 | PCWC | |
|* 24 |
TABLE ACCESS FULL
| YFS_ORDER_HOLD_TYPE | 142K|
5850K| | 16866 (1)| 00:03:23 | Q1,08 | PCWP | |
| 25 | PX RECEIVE | | 471K|
237M| | 503K
(1)| 01:40:45 | Q1,10 | PCWP
| |
| 26 | PX SEND HASH | :TQ10009 | 471K|
237M| | 503K
(1)| 01:40:45 | Q1,09 | P->P |
HASH |
| 27 | BUFFER SORT | |
899K| 639M| |
721K (1)| 02:24:17 | Q1,09 | PCWP | |
| 28 | NESTED LOOPS | | 471K|
237M| | 503K
(1)| 01:40:45 | Q1,09 | PCWP
| |
| 29 | NESTED LOOPS | | 471K|
237M| | 503K
(1)| 01:40:45 | Q1,09 | PCWP
| |
|* 30 |
HASH JOIN
| |
456K| 174M| |
424K (1)| 01:24:54 | Q1,09 | PCWP | |
| 31 | PX RECEIVE | | 3297K|
116M| | 9429
(1)| 00:01:54 | Q1,09 | PCWP
| |
| 32 | PX SEND HASH | :TQ10006 | 3297K|
116M| | 9429
(1)| 00:01:54 | Q1,06 | P->P |
HASH |
| 33 | PX BLOCK
ITERATOR | |
3297K| 116M| |
9429 (1)| 00:01:54 | Q1,06 | PCWC | |
|* 34 | TABLE ACCESS
FULL | YFS_PAYMENT |
3297K| 116M| |
9429 (1)| 00:01:54 | Q1,06 | PCWP | |
| 35 | PX RECEIVE | | 455K|
158M| | 414K
(1)| 01:23:00 | Q1,09 | PCWP
| |
| 36 | PX SEND HASH | :TQ10007 | 455K|
158M| | 414K
(1)| 01:23:00 | Q1,07 | P->P |
HASH |
| 37 | BUFFER SORT | | 899K|
639M| | 721K
(1)| 02:24:17 | Q1,07 | PCWP
| |
| 38 | NESTED LOOPS | | 455K|
158M| | 414K
(1)| 01:23:00 | Q1,07 | PCWP
| |
| 39 | NESTED LOOPS | | 457K|
158M| | 414K
(1)| 01:23:00 | Q1,07 | PCWP
| |
| 40 | NESTED LOOPS | | 457K|
146M| | 367K
(1)| 01:13:29 | Q1,07 | PCWP
| |
|* 41 | HASH JOIN | |
457K| 134M| |
319K (1)| 01:03:57 | Q1,07 | PCWP | |
| 42 | JOIN FILTER
CREATE | :BF0000 | 576K|
69M| | 29790 (1)| 00:05:58 | Q1,07 | PCWP | |
| 43 | PX RECEIVE | | 576K|
69M| | 29790 (1)| 00:05:58 | Q1,07 | PCWP | |
| 44 | PX SEND
HASH | :TQ10004 |
576K| 69M| | 29790
(1)| 00:05:58 | Q1,04 | P->P |
HASH |
| 45 | PX BLOCK
ITERATOR | | 576K|
69M| | 29790 (1)| 00:05:58 | Q1,04 | PCWC | |
|* 46 | TABLE ACCESS
FULL | YFS_ORDER_HEADER |
576K| 69M| | 29790
(1)| 00:05:58 | Q1,04 | PCWP
| |
| 47 | PX RECEIVE | |
4214K| 731M| |
289K (1)| 00:58:00 | Q1,07 | PCWP | |
| 48 | PX SEND
HASH | :TQ10005 | 4214K|
731M| | 289K
(1)| 00:58:00 | Q1,05 | P->P |
HASH |
| 49 | JOIN FILTER
USE | :BF0000 | 4214K|
731M| | 289K
(1)| 00:58:00 | Q1,05 | PCWP
| |
|* 50 | HASH JOIN
BUFFERED | |
4214K| 731M| |
289K (1)| 00:58:00 | Q1,05 | PCWP | |
| 51 | PX
RECEIVE | | 4214K|
450M| | 215K
(1)| 00:43:08 | Q1,05 | PCWP
| |
| 52 | PX SEND
HASH | :TQ10002 | 4214K|
450M| | 215K
(1)| 00:43:08 | Q1,02 | P->P |
HASH |
|* 53 | HASH JOIN
BUFFERED | |
4214K| 450M| |
215K (1)| 00:43:08 | Q1,02 | PCWP | |
| 54 | PX
RECEIVE | | 4214K|
164M| | 141K
(1)| 00:28:16 | Q1,02 | PCWP
| |
| 55 | PX SEND
HASH | :TQ10000 | 4214K|
164M| | 141K
(1)| 00:28:16 | Q1,00 | P->P |
HASH |
| 56 | PX
BLOCK ITERATOR | |
4214K| 164M| |
141K (1)| 00:28:16 | Q1,00 | PCWC | |
|* 57 | TABLE
ACCESS FULL| YFS_ORDER_RELEASE_STATUS |
4214K| 164M| |
141K (1)| 00:28:16 | Q1,00 | PCWP | |
| 58 | PX
RECEIVE | | 5322K|
360M| | 74322 (1)| 00:14:52 | Q1,02 | PCWP | |
| 59 | PX SEND
HASH | :TQ10001 |
5322K| 360M| | 74322
(1)| 00:14:52 | Q1,01 | P->P |
HASH |
| 60 | PX
BLOCK ITERATOR |
| 5322K| 360M|
| 74322 (1)| 00:14:52 | Q1,01 | PCWC | |
|* 61 | TABLE
ACCESS FULL| YFS_ORDER_LINE
| 5322K| 360M|
| 74322 (1)| 00:14:52 | Q1,01 | PCWP | |
| 62 | PX
RECEIVE | |
6469K| 431M| | 74298
(1)| 00:14:52 | Q1,05 | PCWP
| |
| 63 | PX SEND
HASH | :TQ10003 | 6469K|
431M| | 74298 (1)| 00:14:52 | Q1,03 | P->P | HASH |
| 64 | PX BLOCK
ITERATOR | | 6469K|
431M| | 74298 (1)| 00:14:52 | Q1,03 | PCWC | |
|* 65 | TABLE
ACCESS FULL | YFS_ORDER_LINE |
6469K| 431M| | 74298
(1)| 00:14:52 | Q1,03 | PCWP
| |
| 66 | TABLE ACCESS BY
INDEX ROWID | YFS_PERSON_INFO
| 1 | 28 |
| 0 (0)|
| Q1,07 | PCWP | |
|* 67 | INDEX UNIQUE
SCAN | YFS_PERSON_INFO_PK |
1 | | |
0 (0)| |
Q1,07 | PCWP | |
|* 68 | INDEX UNIQUE
SCAN | YFS_PERSON_INFO_PK |
1 | | |
0 (0)| |
Q1,07 | PCWP | |
| 69 | TABLE ACCESS BY
INDEX ROWID | YFS_PERSON_INFO |
1 | 28 | |
0 (0)| |
Q1,07 | PCWP | |
|* 70 |
INDEX RANGE SCAN
| YFS_NOTES_I1 |
1 | | |
0 (0)| |
Q1,09 | PCWP | |
|* 71 |
TABLE ACCESS BY INDEX ROWID
| YFS_NOTES | 1 |
128 | | 0
(0)| | Q1,09 | PCWP | |
| 72 | PX RECEIVE | | 21M|
913M| | 29820 (1)| 00:05:58 | Q1,13 | PCWP | |
| 73 | PX SEND HASH | :TQ10011 |
21M| 913M| | 29820
(1)| 00:05:58 | Q1,11 | P->P |
HASH |
| 74 | PX BLOCK ITERATOR | | 21M|
913M| | 29820 (1)| 00:05:58 | Q1,11 | PCWC | |
|* 75 |
TABLE ACCESS FULL
| YFS_ORDER_HEADER | 21M|
913M| | 29820 (1)| 00:05:58 | Q1,11 | PCWP | |
| 76 | PX RECEIVE | |
36M| 1249M| |
141K (1)| 00:28:16 | Q1,15 | PCWP | |
| 77 | PX SEND HASH |
:TQ10014 | 36M|
1249M| | 141K
(1)| 00:28:16 | Q1,14 | P->P |
HASH |
| 78 | PX BLOCK ITERATOR | | 36M|
1249M| | 141K
(1)| 00:28:16 | Q1,14 | PCWC
| |
|* 79 |
TABLE ACCESS FULL | YFS_ORDER_RELEASE_STATUS
| 36M|
1249M| | 141K
(1)| 00:28:16 | Q1,14 | PCWP
| |
| 80 | PX RECEIVE | | 14M|
656M| | 25247 (1)| 00:05:03 | Q1,17 | PCWP | |
| 81 | PX SEND HASH |
:TQ10016 | 14M|
656M| | 25247 (1)| 00:05:03 | Q1,16 | P->P | HASH |
| 82 | PX BLOCK ITERATOR | |
14M| 656M| | 25247
(1)| 00:05:03 | Q1,16 | PCWC
| |
|* 83 |
TABLE ACCESS FULL |
YFS_ORDER_DATE | 14M|
656M| | 25247 (1)| 00:05:03 | Q1,16 | PCWP | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
好的，经过上面的一番测试与分析，那么拿第四个sql来跑一跑，其实自己心里没什么底，因为毕竟太耗资源了，不知道不确定加了parallel即使全表扫描和HJ能带来多大的提升，但是想了下，先跑着测一下，然后在sqlplus里面看看sql
monitor的数据，
好的，开始跑了：SELECT /*+
parallel(32) */
看下并行资源，
没跑之前
select * from V$PX_PROCESS_SYSSTAT;
STATISTIC VALUE
------------------------------------------------------------------------------------------------------------------------
----------
Servers In Use 8
Servers Available
5
Servers Started
6769
Servers Shutdown
6756
Servers Highwater 80
Servers Cleaned Up
0
Server Sessions
51533
Memory Chunks Allocated
6
Memory Chunks Freed
0
Memory Chunks Current
6
Memory Chunks HWM
6
Buffers Allocated
516600
Buffers Freed
516515
Buffers Current
85
Buffers HWM
3568
SELECT * FROM V$PX_PROCESS;
SERV STATUS
PID SPID
SID SERIAL#
---- --------- ---------- ------------------------
---------- ----------
P004 IN USE
52 37222
254 3165
P005 IN USE
53 37224
320 1113
P006 IN USE
54 37226
380 3233
P007 IN USE
55 37228
444 943
P008 IN USE
71 31652
1451 5133
P009 IN USE
73 31654
1578 1485
P010 IN USE
77 31656
1830 1587
P011 IN USE
79 31658
1956 2027
PZ99 AVAILABLE
43 5450
P003 AVAILABLE
51 37220
P001 AVAILABLE
49 37216
P002 AVAILABLE
50 37218
P000 AVAILABLE
48 37214
跑了以后：
SELECT * FROM V$PX_PROCESS;
SERV STATUS
PID SPID
SID SERIAL#
---- --------- ---------- ------------------------
---------- ----------
P020 IN USE
96 38863
1 2349
P066 IN USE
144 38957
2 3565
P000 IN USE
48 37214
3 2683
P001 IN USE
49 37216
66 3161
P067 IN USE
145 38959
67 21
P021 IN USE
98 38866
128 4031
P002 IN USE
50 37218
129 551
P068 IN USE
146 38961
131 181
P003 IN USE
51 37220 192 1157
P069 IN USE
147 38963
193 1967
P004 IN USE
52 37222
254 3165
P070 IN USE
148 38965
255 2031
P022 IN USE
100 38869
257 893
P023 IN USE
101 38871
318 2315
P005 IN USE
53 37224
320 1113
P071 IN USE
149 38967 321 2221
P006 IN USE
54 37226
380 3233
P024 IN USE
102 38873
383 1229
P007 IN USE
55 37228
444 943
P025 IN USE
103 38875
445 2347
P026 IN USE
104 38877
508 5471
P027 IN USE
105 38879
571 627
P028 IN USE
106 38881 634 523
P029 IN USE
107 38883
697 435
P030 IN USE
108 38885
760 411
P031 IN USE
109 38887
823 4479
P032 IN USE
110 38889
886 3437
P033 IN USE
111 38891
949 5063
P034 IN USE
112 38893
1012 227
P035 IN USE
113 38895 1075 173
P036 IN USE
114 38897
1138 143
P037 IN USE
115 38899
1202 119
P012 IN USE
68 38605
1263 4409
P038 IN USE
116 38901
1264 4447
P039 IN USE
117 38903
1327 97
P040 IN USE
118 38905
1390 101
P008 IN USE
71 31652
1451 5133
P041 IN USE
119 38907
1452 2757
P042 IN USE
120 38909
1516 75
P043 IN USE
121 38911 1577 3847
P009 IN USE
73 31654
1578 1485
P044 IN USE
122 38913
1641 55
P045 IN USE
123 38915
1705 43
P046 IN USE
124 38917
1768 675
P047 IN USE
125 38919
1829 2125
P010 IN USE
77 31656
1830 1587
P048 IN USE
126 38921 1893 1609
P049 IN USE
127 38923
1954 4333
P011 IN USE
79 31658
1956 2027
P050 IN USE
128 38925
2019 1597
P051 IN USE
129 38927
2083 1377
P052 IN USE
130 38929
2145 5581
P053 IN USE
131 38931
2208 1185
P054 IN USE
132 38933 2271 383
P055 IN USE
133 38935
2333 915
P013 IN USE
85 38607
2334 357
P056 IN USE
134 38937
2397 821
P014 IN USE
86 38609
2398 347
P057 IN USE
135 38939
2461 431
P015 IN USE
88 38611
2521 451
P058 IN USE
136 38941 2522 385
P059 IN USE
137 38943
2587 955
P016 IN USE
90 38855
2647 2319
P060 IN USE
138 38945
2648 841
P017 IN USE
91 38857
2710 7887
P061 IN USE
139 38947
2711 8585
P062 IN USE
140 38949
2775 687
P018 IN USE
93 38859 2837 2039
P063 IN USE
141 38951
2838 745
P064 IN USE
142 38953
2900 1765
P019 IN USE
94 38861
2901 657
P065 IN USE
143 38955
2965 101
PZ99 AVAILABLE
43 5450
SQL> select * from
V$PX_PROCESS_SYSSTAT;
STATISTIC
VALUE
------------------------------------------------------------------------------------------------------------------------
----------
Servers In Use
72
Servers Available
1
Servers Started
6829
Servers Shutdown
6756
Servers Highwater
80
Servers Cleaned Up
0
Server Sessions 51609
Memory Chunks Allocated
6
Memory Chunks Freed 0
Memory Chunks Current
6
Memory Chunks HWM 6
Buffers Allocated
520086
Buffers Freed 516677
Buffers Current
3409
Buffers HWM
3750
SQL> select status,sid,serial#,event,sql_id from
gv$session where username='CDTDBA' and status='ACTIVE';
STATUS
SID SERIAL# EVENT
SQL_ID
-------- ---------- ----------
---------------------------------------------------------------- -------------
ACTIVE
1 2349 PX Deq: Table Q
Normal
2hjsqj9rwqd16
ACTIVE
2 3565 direct path read
2hjsqj9rwqd16
ACTIVE
3 2683 PX Deq: Table Q
Normal
2hjsqj9rwqd16
ACTIVE 66
3161 PX Deq: Table Q Normal
2hjsqj9rwqd16
ACTIVE
67 21 direct path
read
2hjsqj9rwqd16
ACTIVE
128 4031 PX Deq: Table Q
Normal
2hjsqj9rwqd16
ACTIVE
129 551 PX Deq: Table Q
Normal
2hjsqj9rwqd16
ACTIVE
131 181 direct path
read
2hjsqj9rwqd16
ACTIVE
192 1157 PX Deq: Table Q
Normal
2hjsqj9rwqd16
ACTIVE
193 1967 direct path
read
2hjsqj9rwqd16
ACTIVE 255
2031 direct path read
2hjsqj9rwqd16
ACTIVE
257 893 PX Deq: Table Q
Normal
2hjsqj9rwqd16
ACTIVE
318 2315 PX Deq: Table Q Normal
2hjsqj9rwqd16
ACTIVE
321 2221 direct path
read
2hjsqj9rwqd16
ACTIVE
383 1229 PX Deq: Table Q
Normal 2hjsqj9rwqd16
ACTIVE
445 2347 PX Deq: Table Q
Normal
2hjsqj9rwqd16
ACTIVE
508 5471 PX Deq: Table Q
Normal
2hjsqj9rwqd16
ACTIVE
571 627 PX Deq: Table Q
Normal
2hjsqj9rwqd16
ACTIVE
634 523 PX Deq: Table Q
Normal
2hjsqj9rwqd16
ACTIVE
697 435 PX Deq: Table Q
Normal
2hjsqj9rwqd16
ACTIVE
760 411 PX Deq: Table Q
Normal
2hjsqj9rwqd16
ACTIVE
823 4479 PX Deq: Table Q
Normal 2hjsqj9rwqd16
ACTIVE
824 875 PX Deq: Execute
Reply
2hjsqj9rwqd16
ACTIVE
886 3437 PX Deq: Table Q
Normal
2hjsqj9rwqd16
ACTIVE
948 3445 PX Deq: Execute
Reply
5vkh54u6c76gp
ACTIVE
949 5063 PX Deq: Table Q
Normal
2hjsqj9rwqd16
ACTIVE
1012 227 PX Deq: Table Q
Normal
2hjsqj9rwqd16
ACTIVE
1075 173 PX Deq: Table Q
Normal
2hjsqj9rwqd16
ACTIVE
1138 143 PX Deq: Table Q
Normal 2hjsqj9rwqd16
ACTIVE
1202 119 PX Deq: Table Q
Normal
2hjsqj9rwqd16
ACTIVE
1263 4409 PX Deq: Table Q
Normal
2hjsqj9rwqd16
ACTIVE
1264 4447 PX Deq: Table Q
Normal
2hjsqj9rwqd16
ACTIVE
1327 97 PX Deq: Table Q
Normal
2hjsqj9rwqd16
ACTIVE
1390 101 direct path
read
2hjsqj9rwqd16
ACTIVE
1452 2757 direct path
read
2hjsqj9rwqd16
ACTIVE
1516 75 direct path
read 2hjsqj9rwqd16
ACTIVE
1577 3847 direct path
read
2hjsqj9rwqd16
ACTIVE
1641 55 direct path
read
2hjsqj9rwqd16
ACTIVE
1705 43 direct path
read
2hjsqj9rwqd16
ACTIVE
1768 675 direct path
read
2hjsqj9rwqd16
ACTIVE
1829 2125 direct path
read
2hjsqj9rwqd16
ACTIVE
1893 1609 direct path
read
2hjsqj9rwqd16
ACTIVE
1954 4333 direct path
read 2hjsqj9rwqd16
ACTIVE
2019 1597 direct path
read
2hjsqj9rwqd16
ACTIVE
2083 1377 direct path
read
2hjsqj9rwqd16
ACTIVE
2145 5581 direct path
read
2hjsqj9rwqd16
ACTIVE
2208 1185 direct path
read
2hjsqj9rwqd16
ACTIVE
2271 383 direct path
read
2hjsqj9rwqd16
ACTIVE
2333 915 direct path
read
2hjsqj9rwqd16
ACTIVE
2334 357 PX Deq: Table Q
Normal
2hjsqj9rwqd16
ACTIVE
2397 821 direct path
read
2hjsqj9rwqd16
ACTIVE
2398 347 PX Deq: Table Q
Normal 2hjsqj9rwqd16
ACTIVE
2461 431 direct path
read
2hjsqj9rwqd16
ACTIVE
2521 451 PX Deq: Table Q
Normal
2hjsqj9rwqd16
ACTIVE
2522 385 direct path
read
2hjsqj9rwqd16
ACTIVE
2587 955 direct path
read
2hjsqj9rwqd16
ACTIVE
2647 2319 PX Deq: Table Q
Normal
2hjsqj9rwqd16
ACTIVE
2648 841 direct path
read
2hjsqj9rwqd16
ACTIVE
2710 7887 PX Deq: Table Q
Normal 2hjsqj9rwqd16
ACTIVE
2711 8585 direct path
read
2hjsqj9rwqd16
ACTIVE
2775 687 direct path
read
2hjsqj9rwqd16
ACTIVE
2837 2039 PX Deq: Table Q
Normal
2hjsqj9rwqd16
ACTIVE
2838 745 direct path
read
2hjsqj9rwqd16
ACTIVE
2900 1765 direct path
read
2hjsqj9rwqd16
ACTIVE
2901 657 PX Deq: Table Q
Normal 2hjsqj9rwqd16
ACTIVE
2965 101 direct path
read
2hjsqj9rwqd16
SQL> SELECT
sql_id,
process_name,status FROM v$sql_monitor
WHERE sql_id ='2hjsqj9rwqd16';
SQL_ID PROCE
STATUS
------------- ----- -------------------
2hjsqj9rwqd16 p057
DONE (ALL ROWS)
2hjsqj9rwqd16 p025
DONE (ALL ROWS)
2hjsqj9rwqd16 p042
DONE (ALL ROWS)
2hjsqj9rwqd16 p010
DONE (ALL ROWS)
2hjsqj9rwqd16 p042
EXECUTING
2hjsqj9rwqd16 p002
EXECUTING
2hjsqj9rwqd16 p055
EXECUTING
2hjsqj9rwqd16 p023
EXECUTING
2hjsqj9rwqd16 p071
EXECUTING
2hjsqj9rwqd16 p039
EXECUTING
2hjsqj9rwqd16 p047
EXECUTING
2hjsqj9rwqd16 p015
EXECUTING
2hjsqj9rwqd16 p066
DONE (ALL ROWS)
2hjsqj9rwqd16 p034
DONE (ALL ROWS)
2hjsqj9rwqd16 p062
DONE (ALL ROWS)
2hjsqj9rwqd16 p030
DONE (ALL ROWS)
2hjsqj9rwqd16 p064
EXECUTING
2hjsqj9rwqd16 p032
EXECUTING
2hjsqj9rwqd16 p050
EXECUTING
2hjsqj9rwqd16 p018
EXECUTING
2hjsqj9rwqd16 p069
EXECUTING
2hjsqj9rwqd16 p037
EXECUTING
2hjsqj9rwqd16 p043
EXECUTING
2hjsqj9rwqd16 p003
EXECUTING
2hjsqj9rwqd16 p068
EXECUTING
2hjsqj9rwqd16 p036
EXECUTING
2hjsqj9rwqd16 p057
EXECUTING
2hjsqj9rwqd16 p025
EXECUTING
2hjsqj9rwqd16 p040
EXECUTING
2hjsqj9rwqd16 p000
EXECUTING
2hjsqj9rwqd16 p054
EXECUTING
2hjsqj9rwqd16 p022
EXECUTING
2hjsqj9rwqd16 p056
DONE (ALL ROWS)
2hjsqj9rwqd16 p024
DONE (ALL ROWS)
2hjsqj9rwqd16 p041
EXECUTING
2hjsqj9rwqd16 p001
EXECUTING
2hjsqj9rwqd16 p026
EXECUTING
2hjsqj9rwqd16 p058
EXECUTING
2hjsqj9rwqd16 ora
DONE (ERROR)
2hjsqj9rwqd16 p050
DONE (ALL ROWS)
2hjsqj9rwqd16 p018
DONE (ALL ROWS)
2hjsqj9rwqd16 p040
DONE (ALL ROWS)
2hjsqj9rwqd16 p008
DONE (ALL ROWS)
2hjsqj9rwqd16 p059
EXECUTING
2hjsqj9rwqd16 p027 EXECUTING
2hjsqj9rwqd16 p045
EXECUTING
2hjsqj9rwqd16 p013
EXECUTING
2hjsqj9rwqd16 p046
DONE (ALL ROWS)
2hjsqj9rwqd16 p014
DONE (ALL ROWS)
2hjsqj9rwqd16 p070
EXECUTING
2hjsqj9rwqd16 p038
EXECUTING
2hjsqj9rwqd16 p031
DONE (ALL ROWS)
2hjsqj9rwqd16 p063
DONE (ALL ROWS)
2hjsqj9rwqd16 p049
EXECUTING
2hjsqj9rwqd16 p017
EXECUTING
2hjsqj9rwqd16 p039
DONE (ALL ROWS)
2hjsqj9rwqd16 p007
DONE (ALL ROWS)
2hjsqj9rwqd16 p048
DONE (ALL ROWS)
2hjsqj9rwqd16 p016
DONE (ALL ROWS)
2hjsqj9rwqd16 p043
DONE (ALL ROWS)
2hjsqj9rwqd16 p011
DONE (ALL ROWS)
2hjsqj9rwqd16 p036
DONE (ALL ROWS)
2hjsqj9rwqd16 p004
DONE (ALL ROWS)
2hjsqj9rwqd16 p047
DONE (ALL ROWS)
2hjsqj9rwqd16 p015
DONE (ALL ROWS)
2hjsqj9rwqd16 p055
DONE (ALL ROWS)
2hjsqj9rwqd16 p023
DONE (ALL ROWS)
2hjsqj9rwqd16 p038
DONE (ALL ROWS)
2hjsqj9rwqd16 p006
DONE (ALL ROWS)
2hjsqj9rwqd16 p049
DONE (ALL ROWS)
2hjsqj9rwqd16 p017
DONE (ALL ROWS)
2hjsqj9rwqd16 p067
DONE (ALL ROWS)
2hjsqj9rwqd16 p035
DONE (ALL ROWS)
2hjsqj9rwqd16 p046
EXECUTING
2hjsqj9rwqd16 p014
EXECUTING
2hjsqj9rwqd16 p061
DONE (ALL ROWS)
2hjsqj9rwqd16 p029
DONE (ALL ROWS)
2hjsqj9rwqd16 p053
EXECUTING
2hjsqj9rwqd16 p021
EXECUTING
2hjsqj9rwqd16 p056
EXECUTING
2hjsqj9rwqd16 p024
EXECUTING
2hjsqj9rwqd16 p052
EXECUTING
2hjsqj9rwqd16 p020
EXECUTING
2hjsqj9rwqd16 p060
EXECUTING
2hjsqj9rwqd16 p028
EXECUTING
2hjsqj9rwqd16 p044
DONE (ALL ROWS)
2hjsqj9rwqd16 p012
DONE (ALL ROWS)
2hjsqj9rwqd16 p065
EXECUTING
2hjsqj9rwqd16 p033
EXECUTING
2hjsqj9rwqd16 p037
DONE (ALL ROWS)
2hjsqj9rwqd16 p005
DONE (ALL ROWS)
2hjsqj9rwqd16 p048
EXECUTING
2hjsqj9rwqd16 p016
EXECUTING
2hjsqj9rwqd16 p053
DONE (ALL ROWS)
2hjsqj9rwqd16 p021
DONE (ALL ROWS)
2hjsqj9rwqd16 p061
EXECUTING
2hjsqj9rwqd16 p029
EXECUTING
2hjsqj9rwqd16 ora
EXECUTING
2hjsqj9rwqd16 p064
DONE (ALL ROWS)
2hjsqj9rwqd16 p032
DONE (ALL ROWS)
2hjsqj9rwqd16 p067
EXECUTING
2hjsqj9rwqd16 p035
EXECUTING
2hjsqj9rwqd16 p063
EXECUTING
2hjsqj9rwqd16 p031
EXECUTING
2hjsqj9rwqd16 p054
DONE (ALL ROWS)
2hjsqj9rwqd16 p022
DONE (ALL ROWS)
2hjsqj9rwqd16 p062
EXECUTING
2hjsqj9rwqd16 p030
EXECUTING
2hjsqj9rwqd16 p044
EXECUTING
2hjsqj9rwqd16 p012
EXECUTING
2hjsqj9rwqd16 p060
DONE (ALL ROWS)
2hjsqj9rwqd16 p028
DONE (ALL ROWS)
2hjsqj9rwqd16 p065
DONE (ALL ROWS)
2hjsqj9rwqd16 p033 DONE
(ALL ROWS)
2hjsqj9rwqd16 p051
DONE (ALL ROWS)
2hjsqj9rwqd16 p019
DONE (ALL ROWS)
2hjsqj9rwqd16 p052
DONE (ALL ROWS)
2hjsqj9rwqd16 p020
DONE (ALL ROWS)
2hjsqj9rwqd16 p041
DONE (ALL ROWS)
2hjsqj9rwqd16 p009
DONE (ALL ROWS)
2hjsqj9rwqd16 p059
DONE (ALL ROWS)
2hjsqj9rwqd16 p027
DONE (ALL ROWS)
2hjsqj9rwqd16 p045
DONE (ALL ROWS)
2hjsqj9rwqd16 p013
DONE (ALL ROWS)
2hjsqj9rwqd16 p066
EXECUTING
2hjsqj9rwqd16 p034
EXECUTING
2hjsqj9rwqd16 p051
EXECUTING
2hjsqj9rwqd16 p019 EXECUTING
2hjsqj9rwqd16 p058
DONE (ALL ROWS)
2hjsqj9rwqd16 p026
DONE (ALL ROWS)
因为SJ环境没有配OEM，所以OEM SQL monitor这么棒的图形界面用不了了，自己就尝试着用一下命令行界面的吧
自己还正在研究命令行界面的sql monitor，惊喜的发现sql跑出结果了！！！！！！！
好吧，如下贴了sql monitor text格式的内容和执行计划，这次sql也算是从7小时—》5mins成功调成！！！
但是还是需要考虑以及反思几个点的：
- 之前提到的因为index range/unique scan不走parallel而误认为加了parallel也没用的那个错误观点
- 在没有OEM sql monitor的情况下，如何在sqlplus里面完成sql
monitor的功能 - 学习下这个showplan.sql这个脚本，还是挺有帮助的，能够给出一些值得一看的信息
脚本摘自于http://www.hellodba.com/reader.php?ID=218&lang=CN
4.并行全表扫描居然把十几张表的join给五分钟解决了，还是难以置信啊~~！！！当然执行计划里面有nested
loop/hash join两个都有。主要是并行扫全表的威力！！
说明在数据仓库这种环境，并行这个功能还是很有用很有震慑力的！！！还是可以在没什么思路的时候可以去果断尝试并行的！！
5.用explain plan分析的时候给出的执行计划预估两小时多，都不敢去执行了，看来还是得胆大点，勇敢的尝试！
6.对于这些大表的join,表的设计：分区表可以提高很多性能，当然这里都不是分区表。所以对于大表join的优化思路也应该考虑在设计阶段！！
SQL> set trimspool on trim on
SQL> set pages 0 linesize 1000
SQL> set long 1000000 longchunksize 1000000
SQL> SELECT dbms_sqltune.report_sql_monitor(
2 sql_id => '2hjsqj9rwqd16',
3 report_level => 'ALL',
type=>'TEXT')
FROM dual; 4 5
SQL Monitoring Report
SQL Text
------------------------------
SELECT /*+ parallel(32) */ h.order_no AS SO_no, h.order_type……………………..
Global Information
------------------------------
Status :
EXECUTING
Instance ID :
3
Session :
CDTDBA (824:875)
SQL ID :
2hjsqj9rwqd16
SQL Execution ID :
50331649
Execution
Started : 12/19/2016 14:09:17
First Refresh
Time :
12/19/2016 14:09:23
Last Refresh
Time :
12/19/2016 14:17:13
Duration :
479s
Module/Action :
SQL*Plus/-
Service :
SYS$USERS
Program :
sqlplus@ora-bi-p-3.sj.b2c.nike.com (TNS V1-V3)
Fetch Calls :
458
Global Stats
=======================================================================================================================
| Elapsed | Cpu |
IO | Application | Concurrency
| Cluster | Fetch | Buffer | Read |
Read | Write | Write |
| Time(s) | Time(s) | Waits(s) | Waits(s)
| Waits(s) | Waits(s) | Calls | Gets |
Reqs | Bytes | Reqs | Bytes |
=======================================================================================================================
| 10277 | 608 |
9461 | 0.32 | 5.90 | 201 |
458 | 84M | 2M | 453GB |
4911 | 1GB |
=======================================================================================================================
Parallel Execution Details (DOP=32 , Servers Allocated=64)
==================================================================================================================================================================================
| Name | Type
| Server# | Elapsed | Cpu |
IO | Application | Concurrency
| Cluster | Buffer | Read | Read
| Write | Write | Wait
Events |
|
| | | Time(s) | Time(s) | Waits(s) | Waits(s)
| Waits(s) | Waits(s) |
Gets | Reqs | Bytes | Reqs | Bytes | (sample #) |
==================================================================================================================================================================================
| PX Coordinator | QC
| | 6.78 |
0.69 | | 0.32 | 5.78 | 0.00 |
394 | | . |
| . | os thread startup
(6) |
|
| | | | | | | | | |
| | |
| |
| p000 | Set
1 | 1 | 87 |
6.91 | 77 | | 0.00 | 2.26 |
798K | 24279 | 4GB | 124 |
30MB | db file sequential read (10) |
|
| | | | | | | | | |
| | |
| direct path read (69) |
|
| | | | | | |
| | |
| | |
| direct path write temp (1) |
| p001 | Set
1 | 2 | 88 |
7.17 | 79 | | 0.00 | 2.22 |
791K | 24233 | 4GB | 125 |
30MB | gc cr grant 2-way (1)
|
|
| | | | | | | | | |
| | |
| db file sequential read (9) |
|
| | | |
| | | | | |
| | |
| direct path read (65) |
|
| | | | | | | | | |
| |
| | direct path read temp
(1) |
|
| | | | | | | | | |
| | |
| direct path write temp (1) |
| p002 | Set
1 | 3 | 85 |
6.87 | 76 | | 0.00 | 2.22 |
767K | 23973 | 4GB | 126 |
31MB | gc cr grant 2-way (2)
|
|
| | | | | | | | |
| | |
| | db file sequential read
(7) |
|
| | | | | | | | | |
| | |
| direct path read (69) |
| | |
| | | | | | | |
| | |
| direct path read temp (1) |
| p003 | Set
1 | 4 | 86 |
6.76 | 77 | |
0.00 | 2.22 | 756K | 24044 | 4GB |
125 | 30MB | gc cr grant 2-way
(4) |
|
| | | | | | | | | |
| | |
| db file sequential read (6) |
|
| | | | | | | | | |
| | |
| direct path read (68) |
|
| | | | |
| | | | |
| | |
| direct path write temp (1) |
| p012 | Set
1 | 5 | 86 |
6.89 | 77 | | 0.00 | 2.22 |
767K | 24239 | 4GB | 125 |
30MB | gc cr grant 2-way (3)
|
|
| | | | | | | | | |
| | |
| db file sequential read (8) |
|
| | |
| | | | | | |
| | |
| direct path read (66) |
| p013 | Set
1 | 6 | 87 |
7.05 | 78 | | 0.00 | 2.21 |
780K | 24178 | 4GB | 126 |
31MB | gc cr grant 2-way (5)
|
|
| | | | | | | | | |
| | |
| db file sequential read (8) |
| | |
| | | | | | | |
| | |
| direct path read (67) |
|
| | | | | | | | | |
| | |
| direct path read temp (1) |
|
| | | | | | | | | |
| | |
| direct path write temp (1) |
| p014 | Set
1 | 7 | 88 |
6.98 | 78 | | 0.00 | 2.21 |
792K | 24195 | 4GB | 126 |
31MB | gc cr grant 2-way (2)
|
|
| | | | | | | | | |
| | |
| db file sequential read (11) |
|
| | | | | | | | | |
| | |
| direct path read (67) |
|
| | | | | | | | | |
| | |
| direct path read temp (1) |
| p015 | Set
1 | 8 |
87 | 7.01 | 77 | | 0.00 | 2.22 |
784K | 24184 | 4GB | 125 |
30MB | gc cr grant 2-way (4)
|
|
| | | | | | | | | |
| | |
| db file sequential read (8) |
|
| | | | | | |
| | |
| | |
| direct path read (69) |
|
| | | | | | | | | |
| | |
| direct path read temp (1) |
| p016 | Set
1 | 9 | 87 |
7.15 | 77 | | 0.00 | 2.23 |
769K | 24289 | 4GB | 125 |
30MB | gc cr grant 2-way (4)
|
|
| | | |
| | | | | |
| | |
| db file sequential read (8) |
|
| | | | | | | | | |
| |
| | direct path read
(66) |
| p017 | Set
1 | 10 | 87 |
6.90 | 78 | | 0.00 | 2.25 |
777K | 24268 | 4GB | 126 |
31MB | gc cr grant 2-way (5)
|
|
| | | | | | | | | |
| | |
| db file sequential read (5) |
|
| | | | | | | | |
| | |
| | direct path read
(67) |
| p018 | Set
1 | 11 | 85 |
6.79 | 76 | | 0.00 | 2.20 |
744K | 23860 | 3GB | 123 |
30MB | gc cr grant 2-way (3)
|
| | |
| | | | | | | |
| | |
| db file sequential read (7) |
|
| | | | | | |
| | |
| | |
| direct path read (65) |
|
| | | | | | | | | |
| | |
| direct path write temp (1) |
| p019 | Set
1 | 12 | 86 |
6.98 | 77 | | 0.00 | 2.22 |
771K | 24163 | 4GB | 126 |
31MB | gc cr grant 2-way (2)
|
|
| | | | |
| | | | |
| | |
| db file sequential read (7) |
|
| | | | | | | | | |
| | |
| direct path read (68) |
|
| | | | | | | | | |
| | |
| direct path read temp (1) |
| p020 | Set
1 | 13 |
86 | 6.78 | 77 | | 0.00 | 2.23 |
767K | 24257 | 4GB | 126 |
31MB | db file sequential read (10) |
|
| | | | | | | | | |
| | |
| direct path read (69) |
| p021 | Set
1 | 14 | 87 |
7.05 | 78 | | 0.00 | 2.22 |
817K | 24321 | 4GB | 126 |
31MB | gc cr grant 2-way (2)
|
| | |
| | | | | | | |
| | |
| db file sequential read (9) |
|
| | | | | | | | | |
| | |
| direct path read (68) |
|
| | | | | | | | | |
| | |
| direct path read temp (1) |
|
| | | | | | | | | |
| | |
| direct path write temp (1) |
| p022 | Set
1 | 15 | 86 |
6.86 | 77 | | 0.00 | 2.22 |
772K | 24100 | 4GB | 126 |
31MB | gc cr grant 2-way (3)
|
|
| | | | | | | | | |
| | |
| db file sequential read (8) |
|
| | | | | | | | | |
| | |
| direct path read (67) |
|
| | |
| | | | | | |
| | |
| direct path read temp (1) |
|
| | | | | | | | |
| |
| | | direct path write temp (1) |
| p023 | Set
1 | 16 | 86 |
7.00 | 77 | | 0.00 | 2.25 |
764K | 24302 | 4GB | 126 |
31MB | gc cr grant 2-way (2)
|
|
| | | | | | | | | |
| | |
| db file sequential read (10) |
|
| | | | | | |
| | |
| | |
| direct path read (68) |
|
| | | | | | | | | |
| | |
| direct path read temp (1) |
| p024 | Set
1 | 17 | 86 |
6.80 | 77 | | 0.00 | 2.22 |
783K | 24142 | 4GB | 125 |
30MB | gc cr grant 2-way (3)
|
|
| | | |
| | | | | |
| | |
| db file sequential read (7) |
|
| | | | | | | | | |
| |
| | direct path read
(66) |
|
| | | | | | | | | |
| | |
| direct path read temp (1) |
| p025 | Set
1 | 18 | 87 |
6.78 | 78 | | 0.00 | 2.22 |
780K | 24232 | 4GB | 125 |
30MB | gc cr grant 2-way (1)
|
|
| | | | | | | | |
| | |
| | db file sequential read
(9) |
|
| | | | | | | | | |
| | |
| direct path read (67) |
| | |
| | | | | | | |
| | |
| direct path read temp (1) |
|
| | | | | | |
| | |
| | |
| direct path write temp (1) |
| p026 | Set
1 | 19 | 86 |
7.01 | 77 | | 0.00 | 2.23 |
765K | 24339 | 4GB | 126 |
31MB | gc cr grant 2-way (3)
|
|
| | | | | | | | | |
| | |
| db file sequential read (9) |
|
| | | | |
| | | | |
| | |
| direct path read (65) |
|
| | | | | | | | | |
| | |
| direct path read temp (1) |
|
| | | | | | | | | |
| | |
| direct path write temp (1) |
| p027 | Set
1 | 20 |
87 | 6.97 | 77 | | 0.00 | 2.24 |
795K | 24328 | 4GB | 126 |
31MB | gc cr grant 2-way (4)
|
|
| | | | | | | | | |
| | |
| db file sequential read (8) |
|
| | | | | | | | | |
| | |
| direct path read (68) |
| p028 | Set
1 | 21 | 88 |
7.12 | 79 | | 0.00 | 2.22 |
824K | 24371 | 4GB | 123 |
30MB | gc cr grant 2-way (3)
|
|
| | | | | | | | | |
| | |
| db file sequential read (10) |
|
| | | | | | | | | |
| | |
| direct path read (66) |
| p029 | Set
1 | 22 | 87 |
7.02 | 78 | | 0.00 | 2.22 |
772K | 24238 | 4GB | 126 |
31MB | gc cr grant 2-way (2)
|
|
| | | | | | | | | |
| | |
| db file sequential read (11) |
|
| | | | | | | | | |
| | |
| direct path read (68) |
| p030 | Set
1 | 23 | 87 |
6.71 | 78 | | 0.00 | 2.23 |
751K | 24236 | 4GB | 125 |
30MB | gc cr grant 2-way (5)
|
|
| | |
| | | | | | |
| | |
| db file sequential read (7) |
|
| | | | | | | | | |
| | |
| direct path read (67) |
| p031 | Set
1 | 24 | 87 |
6.83 | 78 | |
0.00 | 2.22 | 779K | 24279 | 4GB |
126 | 31MB | gc cr grant 2-way
(4) |
|
| | | | | | | | | |
| | |
| db file sequential read (8) |
|
| | | | | | | | | |
| | |
| direct path read (69) |
|
| | | |
| | | | | |
| | |
| direct path read temp (1) |
|
| | | | | | | | | |
| |
| | direct path write temp
(1) |
| p032 | Set
1 | 25 | 87 |
6.95 | 78 | | 0.00 | 2.24 |
794K | 24265 | 4GB | 126 |
31MB | gc cr grant 2-way (1)
|
|
| | | | | | | | | |
| | |
| db file sequential read (11) |
|
| | | | | | | | |
| | |
| | direct path read
(66) |
|
| | | | | | | | | |
| | |
| direct path read temp (1) |
| p033 | Set
1 | 26 | 87 |
6.66 | 78 | | 0.00 | 2.24 |
770K | 24131 | 4GB | 126 |
31MB | db file sequential read (12) |
|
| | | | | | |
| | |
| | |
| direct path read (66) |
|
| | | | | | | | | |
| | |
| direct path read temp (1) |
| p034 | Set
1 | 27 | 88 |
7.16 | 79 | | 0.00 | 2.23 |
835K | 24585 | 4GB | 125 |
30MB | gc cr grant 2-way (1)
|
|
| | | | |
| | | | |
| | |
| db file sequential read (10) |
|
| | | | | | | | | |
| | |
| direct path read (68) |
|
| | | | | | | | | |
| | |
| direct path read temp (1) |
|
| | |
| | | | | | |
| | |
| direct path write temp (1) |
| p035 | Set
1 | 28 | 88 |
7.01 | 79 | | 0.00 | 2.20 |
841K | 24468 | 4GB | 126 |
31MB | gc cr grant 2-way (2)
|
|
| | | | | | | | | |
| | |
| db file sequential read (9) |
| | |
| | | | | | | |
| | |
| direct path read (70) |
|
| | | | | | | | | |
| | |
| direct path write temp (1) |
| p036 | Set
1 | 29 | 87 |
6.88 | 78 | | 0.00 | 2.24 |
774K | 24341 | 4GB | 126 |
31MB | gc cr grant 2-way (3)
|
|
| | | | | | | | | |
| | |
| db file sequential read (7) |
|
| | | | | | | | | |
| | |
| direct path read (67) |
|
| | | | | | | | | |
| | |
| direct path write temp (1) |
| p037 | Set
1 | 30 | 86 |
6.85 | 77 | | 0.00 | 2.24 |
775K | 24258 | 4GB | 126 |
31MB | gc cr grant 2-way (2)
|
|
| | |
| | | | | | |
| | |
| db file sequential read (10) |
|
| | | | | | | | |
| |
| | | direct path read (67) |
|
| | | | | | | | | |
| | |
| direct path write temp (1) |
| p038 | Set
1 | 31 | 84 |
6.85 | 75 | | 0.00 | 2.23 |
736K | 24078 | 3GB | 126 |
31MB | gc cr grant 2-way (2)
|
|
| | | | | | |
| | |
| | |
| db file sequential read (8) |
|
| | | | | | | | | |
| | |
| direct path read (65) |
| p039 | Set
1 | 32 | 86 |
6.78 | 77 | | 0.00 | 2.24 |
773K | 24269 | 4GB | 126 |
31MB | gc cr grant 2-way (3)
|
|
| | | |
| | | | | |
| | |
| db file sequential read (10) |
|
| | | | | | | | | |
| |
| | direct path read
(68) |
|
| | | | | | | | | |
| | |
| direct path read temp (1) |
|
| | | | | | | | | |
| | |
| direct path write temp (1) |
| p040 | Set
2 | 1 | 236 |
12 | 220 | | 0.00 | 4.06 |
2M | 47656 | 11GB | 28 |
7MB | gc cr grant 2-way (3)
|
|
| | | | | | | | | |
| | |
| db file sequential read (16) |
| | |
| | | | | | | |
| | |
| direct path read (205) |
| p041 | Set
2 | 2 | 234 |
12 | 218 | |
0.00 | 4.06 | 2M | 47076 | 10GB |
28 | 7MB | gc cr grant 2-way
(6) |
|
| | | | | | | | | |
| | |
| db file sequential read (13) |
|
| | | | | | | | | |
| | |
| direct path read (196) |
| p042 | Set
2 | 3 | 236 |
12 | 220 | | 0.01 | 4.06 |
2M | 47900 | 11GB | 28 |
7MB | gc cr grant 2-way (3)
|
|
| | | | | | | | | |
| | |
| db file sequential read (18) |
|
| | | | | | | | | |
| | |
| direct path read (201) |
| p043 | Set
2 | 4 |
237 | 12 | 221 | | 0.00 | 4.03 |
2M | 47525 | 11GB | 28 |
7MB | gc cr grant 2-way (5)
|
|
| | | | | | | | | |
| | |
| db file sequential read (16) |
|
| | | | | | | | | |
| | |
| direct path read (203) |
| p044 | Set
2 | 5 | 234 |
12 | 218 | | 0.00 | 4.04 |
2M | 47212 | 10GB | 28 |
7MB | gc cr grant 2-way (3)
|
|
| | | | | | | | | |
| | |
| db file sequential read (17) |
|
| | | | | | | | | |
| | |
| direct path read (201) |
| p045 | Set
2 | 6 | 235 |
12 | 218 | | 0.00 | 4.04 |
2M | 47417 | 11GB | 28 |
7MB | gc cr grant 2-way (4)
|
|
| | | | | | | | | |
| | |
| db file sequential read (18) |
|
| | | | | | | | | |
| | |
| direct path read (204) |
| p046 | Set
2 | 7 | 235 |
12 | 219 | | 0.00 | 4.04 |
2M | 47123 | 10GB | 28 |
7MB | gc cr grant 2-way (3)
|
|
| | |
| | | | | | |
| | |
| db file sequential read (15) |
|
| | | | | | | | | |
| | |
| direct path read (203) |
| p047 | Set
2 | 8 | 233 |
12 | 217 | |
0.00 | 4.03 | 2M | 47045 | 10GB |
28 | 7MB | gc cr grant 2-way
(6) |
|
| | | | | | | | | |
| | |
| db file sequential read (15) |
|
| | | | | | | | | |
| | |
| direct path read (198) |
| p048 | Set
2 | 9 | 234 |
12 | 218 | | 0.00 | 4.07 |
2M | 47272 | 11GB | 28 |
7MB | gc cr grant 2-way (5)
|
|
| | | | | | | | | |
| |
| | db file sequential read
(14) |
|
| | | | | | | | | |
| | |
| direct path read (203) |
| p049 | Set
2 | 10 | 233 |
12 | 216 | | 0.00 | 4.05 |
2M | 47005 | 10GB | 28 |
7MB | gc cr grant 2-way (3)
|
|
| | | | | | | | |
| | |
| | db file sequential read
(16) |
|
| | | | | | | | | |
| | |
| direct path read (198) |
| p050 | Set
2 | 11 | 236 |
12 | 220 | | 0.00 | 4.06 |
2M | 47515 | 11GB | 28 |
7MB | gc cr grant 2-way (3)
|
|
| | | | | | |
| | |
| | |
| db file sequential read (16) |
|
| | | | | | | | | |
| | |
| direct path read (202) |
| p051 | Set
2 | 12 | 233 |
12 | 217 | | 0.00 | 4.04 |
2M | 47280 | 10GB | 28 |
7MB | gc cr grant 2-way (5)
|
|
| | | | |
| | | | |
| | |
| db file sequential read (13) |
|
| | | | | | | | | |
| | |
| direct path read (200) |
| p052 | Set
2 | 13 | 236 |
12 | 220 | | 0.00 | 4.07 |
2M | 47351 | 10GB | 28 |
7MB | gc cr grant 2-way (3)
|
|
| | |
| | | | | | |
| | |
| db file sequential read (18) |
|
| | | | | | | | | |
| | |
| direct path read (202) |
| p053 | Set
2 | 14 | 235 |
12 | 219 | | 0.00 | 4.04 |
2M | 47409 | 11GB | 28 |
7MB | gc cr grant 2-way (2)
|
| | |
| | | | | | | |
| | |
| db file sequential read (18) |
|
| | | | | | | | | |
| | |
| direct path read (207) |
| p054 | Set
2 | 15 | 234 |
12 | 218 | | 0.00 | 4.06 |
2M | 47264 | 10GB | 28 |
7MB | gc cr grant 2-way (5)
|
|
| | | | | | | | | |
| | |
| db file sequential read (12) |
|
| | | | | | | | | |
| | |
| direct path read (203) |
| p055 | Set
2 | 16 | 235 |
12 | 219 | | 0.00 | 4.06 |
2M | 47412 | 10GB | 28
| 7MB | gc cr grant 2-way (1) |
|
| | | | | | | | | |
| | |
| db file sequential read (20) |
|
| | |
| | | | | | |
| | |
| direct path read (201) |
| p056 | Set
2 | 17 | 232 |
12 | 216 | | 0.00 | 4.04 |
2M | 47183 | 10GB |
28 | 7MB | gc cr grant 2-way
(1) |
|
| | | | | | | | | |
| | |
| db file sequential read (18) |
|
| | | | | | | | | |
| | |
| direct path read (201) |
| p057 | Set
2 | 18 | 233 |
12 | 217 | |
0.00 | 4.05 | 2M | 47174 | 10GB |
28 | 7MB | gc cr grant 2-way
(5) |
|
| | | | | | | | | |
| | |
| db file sequential read (14) |
|
| | | | | | | | | |
| | |
| direct path read (196) |
| p058 | Set
2 | 19 | 232 |
12 | 216 | | 0.00 | 4.08 |
2M | 46961 | 10GB | 28 |
7MB | gc cr grant 2-way (2)
|
|
| | | | | | | | | |
| |
| | db file sequential read
(17) |
|
| | | | | | | | | |
| | |
| direct path read (199) |
| p059 | Set
2 | 20 | 236 |
12 | 220 | | 0.00 | 4.08 |
2M | 47474 | 11GB | 28 |
7MB | gc cr grant 2-way (2)
|
|
| | | | | | | | |
| | |
| | gc cr request (1) |
|
| | | | | | | | | |
| | |
| db file sequential read (13) |
| | |
| | | | | | | |
| | |
| direct path read (207) |
| p060 | Set
2 | 21 | 232 |
12 | 216 | |
0.00 | 4.03 | 2M | 47001 | 10GB |
28 | 7MB | gc cr grant 2-way
(3) |
|
| | | | | | | | | |
| | |
| db file sequential read (16) |
|
| | | | | | | | | |
| | |
| direct path read (201) |
| p061 | Set
2 | 22 | 233 |
12 | 217 | | 0.00 | 4.06 |
2M | 47117 | 10GB | 28 |
7MB | gc cr grant 2-way (4)
|
|
| | | | | | | | | |
| | |
| db file sequential read (16) |
|
| | | | | | | | | |
| | |
| direct path read (198) |
| p062 | Set
2 | 23 |
233 | 12 | 217 | | 0.00 | 4.07 |
2M | 47231 | 11GB | 28 |
7MB | gc cr grant 2-way (4)
|
|
| | | | | | | | | |
| | |
| db file sequential read (16) |
|
| | | | | | | | | |
| | |
| direct path read (203) |
| p063 | Set
2 | 24 | 234 |
12 | 218 | | 0.00 | 4.04 |
2M | 47065 | 10GB | 28 |
7MB | gc cr grant 2-way (6)
|
|
| | | | | | | | | |
| | |
| db file sequential read (13) |
|
| | | | | | | | | |
| | |
| direct path read (200) |
| p064 | Set
2 | 25 | 235 |
12 | 219 | | 0.00 | 4.04 |
2M | 47366 | 11GB | 28 |
7MB | gc cr grant 2-way (5)
|
|
| | | | | | | | | |
| | |
| db file sequential read (15) |
|
| | | | | | | | | |
| | |
| direct path read (199) |
| p065 | Set
2 | 26 | 234 |
12 | 218 | | 0.00 | 4.06 |
2M | 47167 | 10GB | 28 |
7MB | gc cr grant 2-way (2)
|
|
| | |
| | | | | | |
| | |
| db file sequential read (19) |
|
| | | | | | | | | |
| | |
| direct path read (202) |
| p066 | Set
2 | 27 | 235 |
12 | 219 | |
0.00 | 4.01 | 2M | 47166 | 10GB |
28 | 7MB | gc cr grant 2-way
(2) |
|
| | | | | | | | | |
| | |
| db file sequential read (15) |
|
| | | | | | | | | |
| | |
| direct path read (204) |
| p067 | Set
2 | 28 | 234 |
12 | 218 | | 0.00 | 4.03 |
2M | 47149 | 10GB | 28 |
7MB | gc cr grant 2-way (3)
|
|
| | | | | | | | | |
| |
| | db file sequential read
(17) |
|
| | | | | | | | | |
| | |
| direct path read (206) |
| p068 | Set
2 | 29 | 235 |
12 | 219 | | 0.00 | 4.09 |
2M | 47266 | 10GB | 28 |
7MB | gc cr grant 2-way (2)
|
|
| | | | | | | | |
| | |
| | db file sequential read
(19) |
|
| | | | | | | | | |
| | |
| direct path read (205) |
| p069 | Set
2 | 30 | 237 |
12 | 221 | | 0.00 | 4.04 |
2M | 47547 | 11GB | 28 |
7MB | gc cr grant 2-way (5)
|
|
| | | | | | |
| | |
| | |
| db file sequential read (13) |
|
| | | | | | | | | |
| | |
| direct path read (207) |
| p070 | Set
2 | 31 | 233 |
12 | 217 | | 0.00 | 4.06 |
2M | 47225 | 10GB | 28 |
7MB | gc cr grant 2-way (1)
|
|
| | | | |
| | | | |
| | |
| db file sequential read (14) |
|
| | | | | | | | | |
| | |
| direct path read (198) |
| p071 | Set
2 | 32 | 236 |
12 | 220 | | 0.00 | 4.09 |
2M | 47213 | 11GB | 28 |
7MB | gc cr grant 2-way (3)
|
|
| | |
| | | | | | |
| | |
| db file sequential read (14) |
|
| | | | | | | | | |
| | |
| direct path read (208) |
==================================================================================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=4132303453)
=============================================================================================================================================================================================================================================================
| Id | Operation | Name |
Rows | Cost |
Time | Start | Execs |
Rows | Read | Read
| Write | Write | Mem | Temp | Activity | Activity Detail | Progress |
| |
| |
(Estim) | | Active(s) | Active
| | (Actual) | Reqs | Bytes | Reqs | Bytes |
| | (%)
| (# samples) | |
=============================================================================================================================================================================================================================================================
| -> 0 | SELECT STATEMENT
| | |
| 147 | +330 |
65 | 6856 | |
| | |
| | |
| |
| -> 1 | PX
COORDINATOR
| | |
| 475 | +2 |
65 | 6856 | |
| | |
| | 0.07 | os thread startup (6) |
|
| |
| | |
| | |
| | |
| | |
| | | Cpu (1) | |
| -> 2 | PX SEND
QC (RANDOM)
| :TQ10018 | 900K |
721K | 143 | +334 |
2 | 7257 | |
| | |
| | | | |
| -> 3 | HASH
GROUP BY
| | 900K |
721K | 147 | +330 |
2 | 7257 | |
| | | 27M | |
0.19 | Cpu (19)
| |
| 4 | PX
RECEIVE
| | 900K |
717K | 5 | +330 |
2 | 60773 | |
| | |
| | 0.03 | Cpu (3) | |
| 5 | PX SEND HASH |
:TQ10017 | 900K |
717K | 1 | +330 |
32 | 973K | |
| | |
| | 0.17 | Cpu (17) | |
| 6 | HASH JOIN OUTER BUFFERED | | 900K |
717K | 13 | +318 |
32 | 973K | 128 |
31MB | 128 | 31MB |
| | 0.21 | Cpu (21) | |
| 7 | PX RECEIVE | | 633K |
692K | 2 | +318 |
32 | 973K | |
| | |
| | | | |
| 8 | PX SEND HASH | :TQ10015 | 633K |
692K | 2 | +318 |
32 | 973K | |
| | |
| | | | |
| 9 | HASH JOIN BUFFERED | | 633K |
692K | 77 | +243 |
32 | 973K | 1084 | 263MB | 1084 | 263MB | |
| 0.03 | Cpu (3) | |
| 10 | PX RECEIVE | | 70 |
2 | 2 | +243 |
32 | 2240 | |
| | |
| | | | |
| 11 | PX SEND BROADCAST | :TQ10012 | 70 |
2 | 1 | +243 |
32 | 2240 | |
| | |
| | | | |
| 12 | PX BLOCK ITERATOR | | 70 |
2 | 1 | +243 |
32 | 70 | |
| | |
| | | | |
| 13 | TABLE ACCESS FULL | YFS_STATUS | 70 |
2 | 1 | +243 |
46 | 70 | |
| | |
| | | | |
| 14 | HASH JOIN | | 869K |
692K | 78 | +242 |
32 | 973K | |
| | |
| | 0.32 | Cpu (33) | |
| 15 | PX RECEIVE | | 480K |
550K | 3 | +242 |
32 | 973K | |
| | |
| | 0.05 | Cpu (5) | |
| 16 | PX SEND HASH |
:TQ10013 |
480K | 550K | 2 |
+242 | 32 | 973K | |
| | |
| | 0.12 | Cpu (12) | |
| 17 | HASH JOIN BUFFERED | |
480K | 550K | 17 |
+227 | 32 | 973K |
128 | 31MB | 128 |
31MB | | |
0.16 | Cpu (16) | |
| 18 | PX RECEIVE | | 480K |
521K | 1 | +227 |
32 | 973K | |
| | |
| | | | |
| 19 | PX SEND HASH | :TQ10010 | 480K |
521K | 2 | +227 |
32 | 973K | |
| | |
| | | | |
| 20 | HASH JOIN RIGHT OUTER
BUFFERED | | 480K |
521K | 3 | +226 |
32 | 973K | 1051 | 255MB | 1051 | 255MB | |
| 0.16 | Cpu (1) | |
| |
| | |
| | |
| | |
| | |
| | | direct path write temp (15) |
|
| 21 | PX RECEIVE | | 143K | 16866 | 2 |
+227 | 32 | 222K | |
| | |
| | | | |
| 22 | PX SEND HASH | :TQ10008 | 143K | 16866 | 9 |
+219 | 32 | 222K | |
| | |
| | | | |
| 23 | PX BLOCK ITERATOR | | 143K | 16866 | 9 |
+219 | 32 | 222K | |
| | |
| | 0.01 | Cpu (1) | |
| 24 | TABLE ACCESS FULL | YFS_ORDER_HOLD_TYPE |
143K | 16866 | 11 | +217 |
416 | 222K | 14418 | 14GB |
| | |
| 2.03 | Cpu (5) | |
| |
| | |
| | |
| | |
| | |
| | | direct path read (202) | |
| 25 | PX RECEIVE | | 471K |
504K | 3 | +226 |
32 | 973K | |
| | |
| | 0.01 | Cpu (1) | |
| 26 | PX SEND HASH | :TQ10009 | 471K |
504K | 11 | +217 |
32 | 973K | |
| | |
| | 0.01 | Cpu (1) | |
| 27 | BUFFER SORT | | 900K |
721K | 34 | +194 |
32 | 973K | |
| | |
| | 0.07 | Cpu (7) | |
| 28 | NESTED LOOPS | | 471K |
504K | 26 | +194 |
32 | 973K | |
| |
| | |
0.03 | Cpu (3)
| |
| 29 | NESTED LOOPS | | 471K |
504K | 26 | +194 |
32 | 7M | |
| | |
| | | | |
| 30 | HASH JOIN | | 456K |
424K | 29 | +191 |
32 | 993K | |
| | |
| | 0.02 | Cpu (2) | |
| 31 | PX RECEIVE | | 3M |
9429 | 5 | +191 |
32 | 3M | |
| | |
| | | | |
| 32 | PX SEND HASH | :TQ10006 | 3M |
9429 | 7 | +189 |
32 | 3M | |
| | |
| | | | |
| 33 | PX BLOCK ITERATOR | | 3M |
9429 | 7 | +189 |
32 | 3M | |
| | |
| | | | |
| 34 | TABLE ACCESS FULL | YFS_PAYMENT | 3M |
9429 | 7 | +189 |
416 | 3M | 8243 |
8GB | | |
| | 1.27 | Cpu (8) | |
| | | | |
| | |
| | |
| | |
| | | direct path read (122) | |
| 35 | PX RECEIVE | | 456K |
415K | 26 | +194 |
32 | 984K | |
| | |
| | 0.01 | Cpu (1) | |
| 36 | PX SEND HASH | :TQ10007 | 456K |
415K | 31 | +189 |
32 | 984K | |
| | |
| | 0.01 | Cpu (1) | |
| 37 | BUFFER SORT | | 900K |
721K | 44 | +176 |
32 | 984K | |
| | |
| | 0.02 | Cpu (2) | |
| 38 | NESTED LOOPS | | 456K |
415K | 15 | +176 |
32 | 984K | |
| | |
| | | | |
| 39 | NESTED LOOPS | | 457K |
415K | 15 | +176 |
32 | 984K | |
| | |
| | 0.01 | Cpu (1) | |
| 40 | NESTED LOOPS | | 457K |
367K | 15 | +176 |
32 | 984K | |
| | |
| | | | |
| 41 | HASH JOIN | | 457K |
320K | 28 | +163 |
32 | 984K | |
| | |
| | 0.05 | Cpu (5) | |
| 42 | JOIN FILTER
CREATE | :BF0000 |
576K | 29790 | 15 | +163 |
32 | 818K | |
| | |
| | | | |
| 43 | PX
RECEIVE | |
576K | 29790 | 15 | +163 |
32 | 818K | |
| | |
| | 0.01 | Cpu (1) | |
| 44 | PX SEND
HASH | :TQ10004 | 576K | 29790 | 18 |
+161 | 32 | 818K | |
| | |
| | | | |
| 45 | PX BLOCK
ITERATOR | | 576K | 29790 | 18 |
+161 | 32 | 818K | |
| | |
| | | | |
| 46 | TABLE
ACCESS FULL | YFS_ORDER_HEADER |
576K | 29790 | 19 | +160 |
416 | 818K | 25146 | 24GB |
| | |
| 4.65 | Cpu (13) | |
| |
| | |
| | |
| | |
| | |
| | | direct path read (462) | |
| 47 | PX RECEIVE | | 4M |
290K | 15 |
+176 | 32 | 1M | |
| | |
| | 0.02 | Cpu (2) | |
| 48 | PX SEND
HASH | :TQ10005 | 4M |
290K | 28 | +160 |
32 | 1M | |
| | |
| | | | |
| 49 | JOIN FILTER
USE | :BF0000 | 4M |
290K | 28 | +160 |
32 | 2M | |
| | |
| | | | |
| 50 | HASH JOIN
BUFFERED | | 4M |
290K | 67 | +121 |
32 | 2M |
640 | 155MB | 640 | 155MB | |
| 0.14 | Cpu (14) | 100% |
| 51 | PX
RECEIVE | | 4M |
216K | 2 | +121 |
32 | 4M | |
| | |
| | 0.03 | Cpu (3) | |
| 52 | PX SEND
HASH | :TQ10002 | 4M |
216K | 2 | +121 |
32 | 4M | |
| | |
| | 0.11 | Cpu (11) | |
| 53 | HASH JOIN
BUFFERED | | 4M |
216K | 107 | +16 |
32 | 4M | 1880 | 455MB | 1880 | 455MB | |
| 0.32 | Cpu (16) | |
| |
| | |
| | |
| | |
| | |
| | | direct path read temp (17) |
|
| 54 | PX
RECEIVE | | 4M |
141K | 68 | +16 |
32 | 4M | |
| |
| | |
|
| |
| 55 | PX SEND
HASH | :TQ10000 | 4M |
141K | 75 | +8 |
32 | 4M | |
| | |
| | 0.01 | Cpu (1) | |
| 56 | PX
BLOCK ITERATOR | | 4M |
141K | 75 | +8 |
32 | 4M | |
| | |
| | 0.01 | Cpu (1) | |
| 57 | TABLE
ACCESS FULL | YFS_ORDER_RELEASE_STATUS |
4M | 141K | 75 |
+8 | 416 | 4M |
118K | 114GB | | |
| | 23.04 | Cpu (34) | 100% |
| | | | |
| | |
| | |
| | |
| | | direct path read (2319) | |
| 58 | PX RECEIVE | | 5M | 74322 | 41 |
+82 | 32 | 5M | |
| | |
| | 0.03 | Cpu (3) | |
| 59 | PX SEND HASH | :TQ10001 | 5M | 74322 | 41 |
+82 | 32 | 5M | |
| | |
| | 0.04 | Cpu (4) | |
| 60 | PX BLOCK ITERATOR | | 5M | 74322 | 41 |
+82 | 32 | 5M | |
| | |
| | 0.01 | Cpu (1) | |
| 61 | TABLE ACCESS FULL | YFS_ORDER_LINE | 5M | 74322 | 41 |
+82 | 416 | 5M | 62124 | 60GB |
| | |
| 12.15 | Cpu (25) | |
| | | | |
| | |
| | |
| | |
| | | direct path read (1216) | |
| 62 | PX RECEIVE | | 6M | 74298 | 38 |
+124 | 32 | 7M | |
| | |
| | 0.01 | Cpu (1) | |
| 63 | PX SEND
HASH | :TQ10003 | 6M | 74298 | 40 |
+122 | 32 | 7M | |
| | |
| | 0.04 | Cpu (4) | |
| 64 | PX BLOCK
ITERATOR | | 6M | 74298 | 40 |
+122 | 32 | 7M | |
| | |
| | | | |
| 65 | TABLE
ACCESS FULL | YFS_ORDER_LINE | 6M | 74298 | 41 |
+121 | 416 | 7M | 62124 | 60GB |
| | |
| 11.77 | Cpu (25) | |
| |
| | |
| | |
| | |
| | |
| | | direct path read (1177) | |
| 66 | TABLE ACCESS BY
INDEX ROWID | YFS_PERSON_INFO | 1 |
| 15 | +176 |
984K | 984K | 498K |
4GB | | |
| | 3.09 | gc cr grant 2-way (62) |
|
| |
| |
| | | |
| | |
| | |
| | | Cpu (30) | |
| |
| | |
| | |
| | |
| | |
| | | db file sequential read (224) | |
| 67 | INDEX UNIQUE
SCAN | YFS_PERSON_INFO_PK |
1 | |
15 | +176 | 984K |
984K | 144K | 1GB |
| | |
| 0.75 | gc cr grant 2-way
(18) | |
| |
| | |
| | |
| | |
| | |
| | | Cpu (14) | |
| |
| | |
| | |
| | |
| | |
| | | db file sequential read (45) |
|
| 68 | INDEX UNIQUE
SCAN |
YFS_PERSON_INFO_PK | 1 |
| 41 |
+176 | 984K | 984K |
2533 | 20MB | |
| | |
0.04 | Cpu (2)
| |
| |
| | |
| | |
| | |
| | |
| | | db file sequential read (2) |
|
| 69 | TABLE ACCESS BY
INDEX ROWID | YFS_PERSON_INFO | 1 |
| 15 | +176 |
984K | 984K | 11911 | 93MB |
| | |
| 0.09 | gc cr grant 2-way
(1) | |
| |
| | |
| | |
| | |
| | |
| | | Cpu (2) | |
| |
| | |
| | |
| | |
| | |
| | | db file sequential read (6) |
|
| 70 | INDEX RANGE SCAN | YFS_NOTES_I1 | 1 |
| 26 | +194 |
993K | 7M | 206K |
2GB | | |
| | 1.31 | gc cr grant 2-way (22) |
|
| |
| | |
| | |
| | |
| | |
| | | Cpu (22) | |
| |
| | |
| | |
| | |
| | |
| | | db file sequential read (90) |
|
| 71 | TABLE ACCESS BY INDEX
ROWID | YFS_NOTES | 1 |
| 26 | +194 |
7M | 973K | 969K |
7GB | | |
| | 5.48 | gc cr grant 2-way (88) |
|
| |
| | |
| | |
| | |
| | |
| | | gc cr request (1) | |
| |
| | |
| | |
| | |
| | |
| | | Cpu (57) | |
| | | | |
| | |
| | |
| | |
| | | db file sequential read (414) | |
| 72 | PX RECEIVE | | 21M | 29820 | 15 |
+229 | 32 | 21M | |
| | |
| | 0.01 | Cpu (1) | |
| 73 | PX SEND HASH | :TQ10011 | 21M | 29820 | 18 |
+227 | 32 | 21M | |
| | |
| | 0.06 | Cpu (6) | |
| 74 | PX BLOCK ITERATOR | | 21M | 29820 | 18 |
+227 | 32 | 21M | |
| | |
| | 0.05 | Cpu (5) | |
| 75 | TABLE ACCESS FULL | YFS_ORDER_HEADER |
21M | 29820 | 18 | +227 |
416 | 21M | 25146 | 24GB |
| | |
| 4.59 | Cpu (9) | |
| | | | |
| | |
| | |
| | |
| | | direct path read (460) | |
| 76 | PX RECEIVE | | 36M |
141K | 76 | +244 |
32 | 37M | |
| | |
| | 0.04 | Cpu (4) | |
| 77 | PX SEND HASH | :TQ10014 | 36M |
141K | 77 | +243 |
32 | 37M | |
| | |
| | 0.19 | Cpu (19) | |
| 78 | PX BLOCK ITERATOR | | 36M |
141K | 77 | +243 |
32 | 37M | |
| | |
| | 0.05 | Cpu (5) | |
| 79 | TABLE ACCESS FULL | YFS_ORDER_RELEASE_STATUS | 36M |
141K | 77 | +243 |
416 | 37M | 118K | 114GB | |
| | |
22.72 | Cpu (58)
| 100% |
| |
| | |
| | |
| | |
| | |
| | | direct path read (2262) | |
| 80 | PX RECEIVE | | 14M | 25247 | 13 |
+318 | 32 | 14M | |
| | |
| | 0.02 | Cpu (2) | |
| 81 | PX SEND HASH |
:TQ10016 |
14M | 25247 | 17 | +318 |
32 | 13M | |
| | |
| | 0.04 | Cpu (4) | |
| 82 | PX BLOCK ITERATOR | |
14M | 25247 | 17 | +318 |
32 | 13M | |
| | |
| | 0.01 | Cpu (1) | |
| 83 | TABLE ACCESS FULL |
YFS_ORDER_DATE | 14M | 25247 | 18 |
+317 | 399 | 13M | 19754 | 19GB |
| | |
| 4.03 | Cpu (19) | |
| |
| | |
| | |
| | |
| | |
| | | direct path read (392) | |
=============================================================================================================================================================================================================================================================
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2hjsqj9rwqd16,
child number 0
-------------------------------------
SELECT /*+
parallel(32) */ h.order_no AS SO_no,
Plan hash value: 4132303453
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation
| Name | Starts
| E-Rows | A-Rows | A-Time | Buffers | Reads | Writes |
OMem | 1Mem | O/1/M
| Max-Tmp |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT
| | 2 |
| 14611 |00:05:34.74 | 788 |
0 | 0 | |
| | |
| 1 | PX COORDINATOR
| | 2 |
| 14611 |00:05:34.74 | 788 |
0 | 0 | |
| | |
| 2 | PX SEND QC (RANDOM) |
:TQ10018 | 0 |
899K| 0 |00:00:00.01 | 0 |
0 | 0 | |
| | |
| 3 | HASH GROUP BY | | 31 |
899K| 19027 |00:00:05.06 | 0 |
0 | 0 | 735M|
14M| | |
| 4 | PX RECEIVE | | 31 |
899K| 942K|00:00:02.61 | 0 |
0 | 0 | |
| | |
| 5 | PX SEND HASH |
:TQ10017 |
0 | 899K| 0 |00:00:00.01 | 0 |
0 | 0 | |
| | |
|* 6 | HASH JOIN OUTER BUFFERED | | 32 |
899K| 973K|00:07:13.41 | 0 |
3968 | 3968 | 310M|
15M| 32/0/0| 8192 |
| 7 | PX RECEIVE | | 32 |
632K| 973K|00:00:04.12 | 0 |
0 | 0 | |
| | |
| 8 | PX SEND HASH |
:TQ10015 | 0 |
632K| 0 |00:00:00.01 | 0 |
0 | 0 | |
| | |
|* 9 | HASH JOIN BUFFERED | | 30 |
632K| 911K|00:39:52.50 | 0 |
33604 | 33604 | 1556K|
1556K| 32/0/0| 16384 |
| 10 | PX RECEIVE | | 32 |
70 | 2170 |00:00:00.08 | 0 |
0 | 0 | |
| | |
| 11 | PX SEND BROADCAST | :TQ10012 | 0 |
70 | 0 |00:00:00.01 | 0 |
0 | 0 | |
| | |
| 12 | PX BLOCK ITERATOR | | 32 |
70 | 70 |00:00:00.04 | 92 |
0 | 0 | |
| | |
|* 13 |
TABLE ACCESS FULL | YFS_STATUS | 46 |
70 | 70 |00:00:00.02 | 92 |
0 | 0 | |
| | |
|* 14 | HASH
JOIN
| |
32 | 869K| 973K|00:39:46.80 | 0 |
0 | 0 | 297M|
15M| 32/0/0| |
| 15 | PX RECEIVE | | 32 |
479K| 973K|00:00:02.29 | 0 | 0 |
0 | | | | |
| 16 | PX SEND HASH |
:TQ10013 | 0 |
479K| 0 |00:00:00.01 | 0 |
0 | 0 | |
| | |
|* 17 |
HASH JOIN BUFFERED | | 32 |
479K| 973K|00:08:29.35 | 0 |
3968 | 3968 | 303M|
15M| 32/0/0| 8192 |
| 18 | PX RECEIVE | | 31 |
479K| 973K|00:00:04.11 | 0 |
0 | 0 | |
| | |
| 19 | PX SEND HASH | :TQ10010 | 0 |
479K| 0 |00:00:00.01 | 0 |
0 | 0 | |
| | |
|* 20 |
HASH JOIN RIGHT OUTER BUFFERED
| | 31 |
479K| 942K|00:03:09.63 | 0 |
31558 | 31558 | 17M|
3376K| 32/0/0| 16384 |
| 21 | PX RECEIVE | | 32 |
142K| 221K|00:03:06.52 | 0 |
0 | 0 | |
| | |
| 22 | PX SEND HASH | :TQ10008 | 0 |
142K| 0 |00:00:00.01 | 0 |
0 | 0 | |
| | |
| 23 | PX BLOCK ITERATOR | |
32 | 142K| 221K|00:03:43.14 | 1987K|
1790K| 0 | |
| | |
|* 24 |
TABLE ACCESS FULL
| YFS_ORDER_HOLD_TYPE | 416 |
142K| 221K|00:03:43.05 | 1987K|
1790K| 0 |
| | | |
| 25 | PX RECEIVE | | 29 |
471K| 911K|00:00:01.65 | 0 |
0 | 0 | |
| | |
| 26 | PX SEND HASH | :TQ10009 | 0 |
471K| 0 |00:00:00.01 | 0 |
0 | 0 | |
| | |
| 27 | BUFFER SORT | | 31 |
899K| 942K|00:13:42.35 | 8514K|
1137K| 0 | 311M|
5861K| 32/0/0| |
| 28 | NESTED LOOPS | | 31 |
471K| 942K|00:13:40.39 | 8514K|
1137K| 0 | |
| | |
| 29 | NESTED LOOPS | | 32 |
471K| 7186K|00:04:33.69 | 4114K|
205K| 0 | |
| | |
|* 30 |
HASH JOIN
| | 32 |
456K| 993K|00:02:17.63 | 0 |
0 | 0 | 247M|
11M| 32/0/0| |
| 31 | PX RECEIVE | | 32 |
3297K| 3337K|00:02:11.36 | 0 |
0 | 0 | |
| | |
| 32 | PX SEND HASH | :TQ10006 | 0 |
3297K| 0 |00:00:00.01 | 0 |
0 | 0 | |
| | |
| 33 | PX BLOCK ITERATOR | | 32 |
3297K| 3192K|00:02:21.70 | 1466K|
1001K| 0 | |
| | |
|* 34 | TABLE ACCESS
FULL | YFS_PAYMENT |
416 | 3297K| 3337K|00:02:21.21 | 1466K|
1001K| 0 | |
| | |
| 35 | PX RECEIVE | | 31 |
455K| 952K|00:00:02.77 | 0 |
0 | 0 | |
| | |
| 36 | PX SEND HASH | :TQ10007 | 0 |
455K| 0 |00:00:00.01 | 0 |
0 | 0 | |
| | |
| 37 | BUFFER SORT | | 32 |
899K| 890K|00:14:15.88 | 7849K|
635K| 0 | 249M|
5269K| 31/0/0| |
| 38 | NESTED LOOPS | | 32 |
455K| 952K|00:14:14.32 | 7849K|
656K| 0 | |
| | |
| 39 | NESTED LOOPS | | 32 |
457K| 860K|00:14:33.38 | 6865K|
644K| 0 | |
| | |
| 40 | NESTED LOOPS | | 32 |
457K| 953K|00:14:27.60 | 3924K|
641K| 0 | |
| | |
|* 41 | HASH JOIN | | 32 |
457K| 952K|00:07:33.29 | 0 |
0 | 0 | 128M|
8830K| 32/0/0| |
| 42 | JOIN FILTER CREATE | :BF0000 | 31 |
576K| 792K|00:07:29.77 | 0 |
0 | 0 | |
| | |
| 43 | PX RECEIVE | |
32 | 576K| 767K|00:07:29.39 | 0 |
0 | 0 | |
| | |
| 44 | PX SEND
HASH | :TQ10004 | 0 |
576K| 0 |00:00:00.01 | 0 | 0 |
0 | | | | |
| 45 | PX BLOCK
ITERATOR | | 32 |
576K| 818K|00:07:52.88 | 3529K|
3162K| 0 | |
| | |
|* 46 | TABLE ACCESS
FULL | YFS_ORDER_HEADER |
416 | 576K| 818K|00:07:52.68 | 3529K|
3162K| 0 | |
| | |
| 47 | PX RECEIVE | | 29 |
4214K| 950K|00:00:01.37 | 0 |
0 | 0 | |
| | |
| 48 | PX SEND
HASH | :TQ10005 | 0 |
4214K| 0 |00:00:00.01 | 0 |
0 | 0 | |
| | |
| 49 | JOIN FILTER
USE | :BF0000 | 32 |
4214K| 1013K|00:21:08.01 | 0 |
19840 | 19840 | |
| | |
|* 50 | HASH JOIN
BUFFERED | | 32 |
4214K| 2153K|00:21:07.43 | 0 |
19840 | 19840 | 636M|
17M| 32/0/0| 8192 |
| 51 | PX RECEIVE | | 32 |
4214K| 4240K|00:00:06.62 | 0 |
0 | 0 | |
| | |
| 52 | PX SEND
HASH | :TQ10002 |
0 | 4214K| 0 |00:00:00.01 | 0 |
0 | 0 | |
| | |
|* 53 | HASH JOIN
BUFFERED | | 62 |
4214K| 4240K|00:56:54.85 | 0 |
56482 | 56482 | 297M|
12M| 32/0/0| 16384 |
| 54 | PX
RECEIVE | | 64 |
4214K| 4107K|00:39:39.66 | 0 |
0 | 0 | |
| | |
| 55 | PX SEND
HASH | :TQ10000 | 0 |
4214K| 0 |00:00:00.01 | 0 |
0 | 0 | |
| | |
| 56 | PX
BLOCK ITERATOR |
| 64 | 4214K|
4420K|00:40:10.55 | 18M| 15M|
0 | | | | |
|* 57 | TABLE
ACCESS FULL| YFS_ORDER_RELEASE_STATUS |
448 | 4214K| 4420K|00:40:09.89 | 18M|
15M| 0 | |
| | |
| 58 | PX
RECEIVE | | 32 |
5322K| 5386K|00:20:47.25 | 0 |
0 | 0 | |
| | |
| 59 | PX SEND
HASH | :TQ10001 | 0 |
5322K| 0 |00:00:00.01 | 0 |
0 | 0 | |
| | |
| 60 | PX
BLOCK ITERATOR |
| 31 | 5322K|
5386K|00:20:40.41 | 8764K| 7643K|
0 | | |
| |
|* 61 | TABLE
ACCESS FULL| YFS_ORDER_LINE
| 416 | 5322K|
5386K|00:20:39.70 | 9049K| 7889K|
0 | | | | |
| 62 | PX RECEIVE | | 32 |
6469K| 6947K|00:20:49.14 | 0 |
0 | 0 | |
| | |
| 63 | PX SEND
HASH | :TQ10003 |
0 | 6469K| 0 |00:00:00.01 | 0 |
0 | 0 | |
| | |
| 64 | PX BLOCK
ITERATOR | | 31 |
6469K| 6789K|00:19:34.94 | 8758K|
7643K| 0 | |
| | |
|* 65 | TABLE
ACCESS FULL | YFS_ORDER_LINE |
416 | 6469K| 6947K|00:20:11.70 | 9049K|
7889K| 0 | |
| | |
| 66 | TABLE ACCESS BY
INDEX ROWID | YFS_PERSON_INFO
| 983K| 1 |
952K|00:06:39.18 | 3924K| 641K|
0 | | | | |
|* 67 | INDEX UNIQUE
SCAN | YFS_PERSON_INFO_PK |
983K| 1 | 952K|00:01:33.51 | 2940K|
143K| 0 | |
| | |
|* 68 | INDEX UNIQUE
SCAN | YFS_PERSON_INFO_PK |
953K| 1 | 953K|00:00:05.29 | 2940K|
2533 | 0 | |
| | |
| 69 | TABLE ACCESS BY
INDEX ROWID | YFS_PERSON_INFO |
983K| 1 | 983K|00:00:07.87 | 983K|
11911 | 0 | |
| | |
|* 70 |
INDEX RANGE SCAN
| YFS_NOTES_I1 | 993K|
1 | 7186K|00:02:14.70 | 4114K|
205K| 0 | |
| | |
|* 71 |
TABLE ACCESS BY INDEX ROWID
| YFS_NOTES | 7186K|
1 | 973K|00:09:31.09 | 4676K|
969K| 0 | |
| | |
| 72 | PX RECEIVE | |
32 | 21M| 21M|00:08:13.82 | 0 |
0 | 0 | |
| | |
| 73 | PX SEND HASH | :TQ10011 | 0 |
21M| 0 |00:00:00.01 | 0 | 0 |
0 | | | | |
| 74 | PX BLOCK ITERATOR | | 32 |
21M| 21M|00:08:08.80 | 3529K|
3162K| 0 | |
| | |
|* 75 |
TABLE ACCESS FULL
| YFS_ORDER_HEADER | 404 |
21M| 20M|00:07:36.31 | 3423K|
2972K| 0 | |
| | |
| 76 | PX RECEIVE | | 31 |
36M| 34M|00:38:10.94 | 0 |
0 | 0 | |
| | |
| 77 | PX SEND HASH |
:TQ10014 | 0 |
36M| 0 |00:00:00.01 | 0 |
0 | 0 | |
| | |
| 78 | PX BLOCK ITERATOR | | 32 |
36M| 36M|00:39:07.67 | 17M|
15M| 0 | |
| | |
|* 79 |
TABLE ACCESS FULL |
YFS_ORDER_RELEASE_STATUS | 403 | 36M|
35M|00:37:49.85 | 17M| 14M|
0 | | | | |
| 80 | PX RECEIVE | | 32 |
14M| 14M|00:06:58.40 | 0 |
0 | 0 | |
| | |
| 81 | PX SEND HASH |
:TQ10016 |
0 | 14M| 0 |00:00:00.01 | 0 |
0 | 0 | |
| | |
| 82 | PX BLOCK ITERATOR | | 32 |
14M| 13M|00:06:55.91 | 3228K|
2680K| 0 |
| | | |
|* 83 |
TABLE ACCESS FULL |
YFS_ORDER_DATE | 376 |
14M| 13M|00:06:15.73 | 3029K|
2428K| 0 | |
| | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 -
access("YOD"."ORDER_LINE_KEY"="L"."ORDER_LINE_KEY")
9 -
access("S1"."STATUS"="ST"."STATUS")
13 -
access(:Z>=:Z AND :Z<=:Z)
filter("ST"."PROCESS_TYPE_KEY"='PO_FULFILLMENT')
14 - access("L1"."ORDER_LINE_KEY"="S1"."ORDER_LINE_KEY")
17 -
access("L1"."ORDER_HEADER_KEY"="H1"."ORDER_HEADER_KEY")
20 -
access("H"."ORDER_HEADER_KEY"="HT"."ORDER_HEADER_KEY")
24 -
access(:Z>=:Z AND :Z<=:Z)
filter(("HT"."ORDER_HEADER_KEY">'20161119' AND
INTERNAL_FUNCTION("HT"."HOLD_TYPE")))
30 -
access("H"."ORDER_HEADER_KEY"="P"."ORDER_HEADER_KEY")
34 -
access(:Z>=:Z AND :Z<=:Z)
filter("P"."ORDER_HEADER_KEY">'20161119')
41 -
access("H"."ORDER_HEADER_KEY"="L"."ORDER_HEADER_KEY")
46 - access(:Z>=:Z
AND :Z<=:Z)
filter(("H"."ENTERPRISE_KEY"='NIKEEUROPE' AND
"H"."ORDER_HEADER_KEY">'20161119' AND
"H"."DOCUMENT_TYPE"='0001' AND
"H"."ORDER_DATE">SYSDATE@!-30 AND
"H"."ORDER_TYPE"<>'OFBT'))
50 -
access("L1"."CHAINED_FROM_ORDER_LINE_KEY"="L"."ORDER_LINE_KEY")
53 -
access("L1"."ORDER_LINE_KEY"="S2"."ORDER_LINE_KEY")
57 -
access(:Z>=:Z AND :Z<=:Z)
filter("S2"."STATUS"='1100.20')
61 -
access(:Z>=:Z AND :Z<=:Z)
filter("L1"."CHAINED_FROM_ORDER_LINE_KEY" IS NOT
NULL)
65 - access(:Z>=:Z
AND :Z<=:Z)
filter(("L"."ORDER_HEADER_KEY">'20161119' AND
"L"."LINE_TYPE"='INLINE'))
67 -
access("PER2"."PERSON_INFO_KEY"="H"."BILL_TO_KEY")
68 -
access("PER1"."PERSON_INFO_KEY"="H"."SHIP_TO_KEY")
70 -
access("NOTES"."TABLE_NAME"='YFS_ORDER_HEADER' AND
"H"."ORDER_HEADER_KEY"="NOTES"."TABLE_KEY")
filter("NOTES"."TABLE_KEY">'20161119')
71 -
filter((INTERNAL_FUNCTION("NOTES"."REASON_CODE") AND
INTERNAL_FUNCTION("NOTES"."NOTE_TEXT") AND
"NOTES"."NOTES_KEY">'20161119'))
75 -
access(:Z>=:Z AND :Z<=:Z)
79 -
access(:Z>=:Z AND :Z<=:Z)
filter("S1"."STATUS_QUANTITY">0)
83 -
access(:Z>=:Z AND :Z<=:Z)
filter("YOD"."DATE_TYPE_ID"='EDD')
Note
-----
- dynamic sampling
used for this statement (level=7)
- Degree of
Parallelism is 32 because of hint