Avg User Avg AVG I/O Other Avg CPU Wait Wait Elapsed Time Time Time Avg Avg Avg Total Plan Hash Time in in in in Buffer Disk Direct Avg Rows Total Total Version Total Total Value secs secs secs secs Gets Reads Writes Processed Executions Fetches Count Loads Invalidations Src Source --------- ------- ----- ----- ----- ------ ----- ------ --------- ---------- ------- ------- ----- ------------- --- -------------------- 657302870 0.288 0.280 0.000 0.000 102 2 0 0 1 0 1 1 0 MEM GV$SQLAREA_PLAN_HASH
# | Source | Plan Hash Value | SQL Handle | Plan Name | Inst ID | Child Number | Executions | Format |
---|---|---|---|---|---|---|---|---|
1 | GV$SQL_PLAN | 657302870 | 1 | 0 | 1 | ADVANCED ALLSTATS LAST | ||
2 | PLAN_TABLE | 657302870 | ADVANCED |
Error: cannot fetch specified explain plan from GV$SQL_PLAN_STATISTICS_ALL
EXPLAIN PLAN SET statement_id = '53242' INTO SQLTXPLAIN.sqlt$_sql_plan_table FOR SELECT v.customer_name, v.orders_total, v.credit_limit, (orders_total - credit_limit) over_limit FROM customer_v v WHERE orders_total > credit_limit AND customer_type = :b1 ORDER BY over_limit DESC Plan hash value: 657302870 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2102 | 203K| | 21392 (2)| 00:04:17 | | 1 | SORT ORDER BY | | 2102 | 203K| 5240K| 21392 (2)| 00:04:17 | |* 2 | FILTER | | | | | | | | 3 | HASH GROUP BY | | 2102 | 203K| 5240K| 21392 (2)| 00:04:17 | | 4 | NESTED LOOPS | | 49026 | 4739K| | 20548 (2)| 00:04:07 | |* 5 | HASH JOIN | | 49026 | 4500K| 4216K| 20544 (2)| 00:04:07 | | 6 | JOIN FILTER CREATE | :BF0000 | 49026 | 3638K| | 1070 (1)| 00:00:13 | |* 7 | HASH JOIN | | 49026 | 3638K| 1240K| 1070 (1)| 00:00:13 | |* 8 | TABLE ACCESS FULL | CUSTOMER | 16667 | 1041K| | 220 (1)| 00:00:03 | |* 9 | TABLE ACCESS FULL | SALES_ORDER | 282K| 3307K| | 465 (2)| 00:00:06 | | 10 | VIEW | | 302K| 5325K| | 18836 (2)| 00:03:47 | | 11 | HASH GROUP BY | | 302K| 7396K| 130M| 18836 (2)| 00:03:47 | | 12 | JOIN FILTER USE | :BF0000 | 3416K| 81M| | 8648 (1)| 00:01:44 | |* 13 | HASH JOIN | | 3416K| 81M| 4304K| 8648 (1)| 00:01:44 | | 14 | TABLE ACCESS FULL| PART | 200K| 1953K| | 392 (1)| 00:00:05 | | 15 | TABLE ACCESS FULL| ORDER_LINE | 3450K| 49M| | 3609 (2)| 00:00:44 | |* 16 | INDEX UNIQUE SCAN | CUSTOMER_PK | 1 | 5 | | 0 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$FD6D9502 8 - SEL$FD6D9502 / C@SEL$2 9 - SEL$FD6D9502 / O@SEL$4 10 - LINES_ROLLUP_QB / LINES_TOTAL@SEL$4 11 - LINES_ROLLUP_QB 14 - LINES_ROLLUP_QB / P@LINES_ROLLUP_QB 15 - LINES_ROLLUP_QB / L@LINES_ROLLUP_QB 16 - SEL$FD6D9502 / C@SEL$4 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA USE_HASH_AGGREGATION(@"LINES_ROLLUP_QB") USE_HASH(@"LINES_ROLLUP_QB" "L"@"LINES_ROLLUP_QB") LEADING(@"LINES_ROLLUP_QB" "P"@"LINES_ROLLUP_QB" "L"@"LINES_ROLLUP_QB") FULL(@"LINES_ROLLUP_QB" "L"@"LINES_ROLLUP_QB") FULL(@"LINES_ROLLUP_QB" "P"@"LINES_ROLLUP_QB") USE_HASH_AGGREGATION(@"SEL$FD6D9502") PX_JOIN_FILTER(@"SEL$FD6D9502" "LINES_TOTAL"@"SEL$4") USE_NL(@"SEL$FD6D9502" "C"@"SEL$4") USE_HASH(@"SEL$FD6D9502" "LINES_TOTAL"@"SEL$4") USE_HASH(@"SEL$FD6D9502" "O"@"SEL$4") LEADING(@"SEL$FD6D9502" "C"@"SEL$2" "O"@"SEL$4" "LINES_TOTAL"@"SEL$4" "C"@"SEL$4") INDEX(@"SEL$FD6D9502" "C"@"SEL$4" ("CUSTOMER"."CUSTOMER_ID")) NO_ACCESS(@"SEL$FD6D9502" "LINES_TOTAL"@"SEL$4") FULL(@"SEL$FD6D9502" "O"@"SEL$4") FULL(@"SEL$FD6D9502" "C"@"SEL$2") OUTLINE(@"SEL$4") OUTLINE(@"SEL$3") OUTLINE(@"SEL$2") OUTLINE(@"SEL$1") MERGE(@"SEL$4") OUTLINE(@"SEL$07BDC5B4") MERGE(@"SEL$2") OUTLINE(@"SEL$F5BB74E1") OUTLINE(@"LINES_ROLLUP_QB") MERGE(@"SEL$07BDC5B4") OUTLINE_LEAF(@"SEL$FD6D9502") OUTLINE_LEAF(@"LINES_ROLLUP_QB") ALL_ROWS DB_VERSION('11.2.0.3') OPTIMIZER_FEATURES_ENABLE('11.2.0.3') IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("C"."CREDIT_LIMIT"<SUM("LINES_TOTAL"."ORDER_TOTAL")) 5 - access("O"."ORDER_ID"="LINES_TOTAL"."ORDER_ID") 7 - access("C"."CUSTOMER_ID"="O"."CUSTOMER_ID") 8 - filter("C"."CUSTOMER_TYPE"=:B1) 9 - filter("O"."STATUS"<>'S' AND "O"."STATUS"<>'C') 13 - access("L"."PART_ID"="P"."PART_ID") 16 - access("O"."CUSTOMER_ID"="C"."CUSTOMER_ID") Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=1) SUM("LINES_TOTAL"."ORDER_TOTAL")-"C"."CREDIT_LIMIT"[22], "C"."CUSTOMER_NAME"[VARCHAR2,4000], SUM("LINES_TOTAL"."ORDER_TOTAL")[22], "C"."CREDIT_LIMIT"[NUMBER,22] 2 - "C"."CREDIT_LIMIT"[NUMBER,22], "C"."CUSTOMER_NAME"[VARCHAR2,4000], SUM("LINES_TOTAL"."ORDER_TOTAL")[22] 3 - (#keys=4) "O"."CUSTOMER_ID"[NUMBER,22], ROWID[ROWID,10], "C"."CREDIT_LIMIT"[NUMBER,22], "C"."CUSTOMER_NAME"[VARCHAR2,4000], SUM("LINES_TOTAL"."ORDER_TOTAL")[22] 4 - (#keys=0) "O"."CUSTOMER_ID"[NUMBER,22], ROWID[ROWID,10], "C"."CREDIT_LIMIT"[NUMBER,22], "C"."CUSTOMER_NAME"[VARCHAR2,4000], "LINES_TOTAL"."ORDER_TOTAL"[NUMBER,22] 5 - (#keys=1) "O"."CUSTOMER_ID"[NUMBER,22], ROWID[ROWID,10], "C"."CREDIT_LIMIT"[NUMBER,22], "C"."CUSTOMER_NAME"[VARCHAR2,4000], "LINES_TOTAL"."ORDER_TOTAL"[NUMBER,22] 6 - "O"."CUSTOMER_ID"[NUMBER,22], ROWID[ROWID,10], "C"."CREDIT_LIMIT"[NUMBER,22], "C"."CUSTOMER_NAME"[VARCHAR2,4000], "O"."ORDER_ID"[NUMBER,22] 7 - (#keys=1) "O"."CUSTOMER_ID"[NUMBER,22], ROWID[ROWID,10], "C"."CREDIT_LIMIT"[NUMBER,22], "C"."CUSTOMER_NAME"[VARCHAR2,4000], "O"."ORDER_ID"[NUMBER,22] 8 - ROWID[ROWID,10], "C"."CUSTOMER_ID"[NUMBER,22], "C"."CUSTOMER_NAME"[VARCHAR2,4000], "C"."CREDIT_LIMIT"[NUMBER,22] 9 - "O"."ORDER_ID"[NUMBER,22], "O"."CUSTOMER_ID"[NUMBER,22] 10 - "LINES_TOTAL"."ORDER_ID"[NUMBER,22], "LINES_TOTAL"."ORDER_TOTAL"[NUMBER,22] 11 - (#keys=1) "L"."ORDER_ID"[NUMBER,22], COUNT(*)[22], SUM(ROUND("L"."QUANTITY"*"P"."PART_PRICE"*(100-NVL("L"."DISCOUNT_PERC",0))/100,2))[22], SUM("L"."QUANTITY")[22] 12 - "P"."PART_PRICE"[NUMBER,22], "L"."ORDER_ID"[NUMBER,22], "L"."DISCOUNT_PERC"[NUMBER,22], "L"."QUANTITY"[NUMBER,22] 13 - (#keys=1) "P"."PART_PRICE"[NUMBER,22], "L"."ORDER_ID"[NUMBER,22], "L"."DISCOUNT_PERC"[NUMBER,22], "L"."QUANTITY"[NUMBER,22] 14 - "P"."PART_ID"[NUMBER,22], "P"."PART_PRICE"[NUMBER,22] 15 - "L"."ORDER_ID"[NUMBER,22], "L"."PART_ID"[NUMBER,22], "L"."QUANTITY"[NUMBER,22], "L"."DISCOUNT_PERC"[NUMBER,22]
Avg Stat Num Sample Segment Segment Empty Avg Row Chain Global User Type Stale Table Name Owner Part Temp Count Rows Size Perc Last Analyzed Extents Blocks Blocks Blocks Space Len Cnt Stats Stats Locked Stats ----------- ----- ---- ---- ------- ------- ------- ----- ------------------- ------- ------- ------ ------ ----- --- ----- ------ ----- ------ ----- CUSTOMER QTUNE NO N 97200 100000 100000 100.0 2012-11-28/09:55:33 22 896 802 0 0 92 0 YES NO NO ORDER_LINE QTUNE NO N 3487000 3450201 3450201 100.0 2012-11-28/09:56:22 84 13312 13157 0 0 23 0 YES NO NO PART QTUNE NO N 195900 200000 200000 100.0 2012-11-28/09:55:44 27 1536 1430 0 0 58 0 YES NO NO SALES_ORDER QTUNE NO N 293900 300000 300000 100.0 2012-11-28/09:55:54 29 1792 1693 0 0 38 0 YES NO NO
Fluctuatin Avg Fluctuatin Col Num Num Sample Num NDV Col Num Endpoint Global User Table Name Column Name ID Rows Nulls Size Perc Distinct Count Low Value High Value Last Analyzed Len Density Buckets Histogram Count Stats Stats ----------- ------------- --- ------- ------- ------- ----- -------- ---------- ---------------------------------- ---------------------------------- ------------------- --- -------------- ------- --------- ---------- ------ ----- CUSTOMER CREDIT_LIMIT 4 100000 14285 5430 6.3 101 FALSE "1000" "101000" 2012-11-28/09:55:31 4 5.908484e-06 101 FREQUENCY FALSE YES NO CUSTOMER CUSTOMER_ID 1 100000 0 100000 100.0 100000 FALSE "1" "100000" 2012-11-28/09:55:31 5 1.000000e-05 1 NONE FALSE YES NO CUSTOMER CUSTOMER_NAME 2 100000 0 100000 100.0 99624 FALSE "AAAEGJQPRNJLYOSWRLICMZRKJVANPMQA" "ZZZVQZYRQVBCEZLJJJRAIXVRNKAMEXAI" 2012-11-28/09:55:31 41 1.003774e-05 1 NONE FALSE YES NO CUSTOMER CUSTOMER_TYPE 3 100000 0 6315 6.3 6 FALSE "1" "6" 2012-11-28/09:55:31 2 5.080454e-06 6 FREQUENCY FALSE YES NO CUSTOMER SYS_NC00005$ 100000 0 100000 100.0 99728 FALSE "aaaegjqprnjlyoswrlicmzrkjvanpmqa" "zzzvqzyrqvbcezljjjraixvrnkamexai" 2012-11-28/09:55:31 41 1.002727e-05 1 NONE FALSE YES NO ORDER_LINE DISCOUNT_PERC 6 3450201 2760161 690040 100.0 11 FALSE "5" "55" 2012-11-28/09:56:06 2 9.090909e-02 1 NONE FALSE YES NO ORDER_LINE LINE_ID 1 3450201 0 3450201 100.0 3450201 FALSE "1" "3450201" 2012-11-28/09:56:06 6 2.898382e-07 1 NONE FALSE YES NO ORDER_LINE ORDER_ID 2 3450201 0 3450201 100.0 302976 FALSE "1" "300000" 2012-11-28/09:56:06 5 3.300591e-06 1 NONE FALSE YES NO ORDER_LINE PART_ID 4 3450201 0 3450201 100.0 201968 FALSE "1" "200000" 2012-11-28/09:56:06 5 4.951279e-06 1 NONE FALSE YES NO ORDER_LINE QUANTITY 5 3450201 0 3450201 100.0 52 FALSE "0" "51" 2012-11-28/09:56:06 3 1.923077e-02 1 NONE FALSE YES NO PART PART_ID 1 200000 0 200000 100.0 200000 FALSE "1" "200000" 2012-11-28/09:55:41 5 5.000000e-06 1 NONE FALSE YES NO PART PART_NAME 2 200000 0 200000 100.0 200000 FALSE "0004S6EOFFEEVRMBSW3NQ791PVPZ3R" "ZZZUCN4U8VWW9FLMIGPB99E5QH4745" 2012-11-28/09:55:41 31 5.000000e-06 1 NONE FALSE YES NO PART PART_PRICE 4 200000 0 200000 100.0 124536 FALSE "0" "4875.71" 2012-11-28/09:55:41 5 8.029807e-06 1 NONE FALSE YES NO SALES_ORDER CUSTOMER_ID 4 300000 0 300000 100.0 95952 FALSE "1" "99999" 2012-11-28/09:55:51 5 1.042188e-05 1 NONE FALSE YES NO SALES_ORDER ORDER_DATE 3 300000 0 300000 100.0 1002 FALSE " 2010/02/23 00:00:00" " 2012/11/20 00:00:00" 2012-11-28/09:55:51 8 9.980040e-04 1 NONE FALSE YES NO SALES_ORDER ORDER_ID 1 300000 0 300000 100.0 300000 FALSE "1" "300000" 2012-11-28/09:55:51 5 3.333333e-06 1 NONE FALSE YES NO SALES_ORDER ORDER_NUM 2 300000 0 300000 100.0 299840 FALSE "AA003667020574" "ZZ999041716303" 2012-11-28/09:55:51 15 3.335112e-06 1 NONE FALSE YES NO SALES_ORDER STATUS 5 300000 0 5464 1.8 10 FALSE "Q" "Z" 2012-11-28/09:55:51 2 1.677648e-06 10 FREQUENCY FALSE YES NO
Avg Avg Leaf Data Blocks Blocks Stat Index Num Sample Distinct Segment Segment Leaf per per Clustering Global User Type Stale Table Name Index Name Owner Type Part Temp Rows Size Perc Last Analyzed Keys BLevel Extents Blocks Blocks Key Key Factor Stats Stats Locked Stats ----------- -------------- ----- --------------------- ---- ---- ------- ------ ----- ------------------- -------- ------ ------- ------- ------ ------ ------ ---------- ------ ----- ------ ----- CUSTOMER CUSTOMER_F1 QTUNE FUNCTION-BASED NORMAL NO N 100000 100000 100.0 2012-11-28/09:55:41 99728 2 21 768 725 1 1 99881 YES NO NO CUSTOMER CUSTOMER_N1 QTUNE NORMAL NO N 100000 100000 100.0 2012-11-28/09:55:37 99624 2 21 768 725 1 1 99881 YES NO NO CUSTOMER CUSTOMER_N2 QTUNE NORMAL NO N 100000 100000 100.0 2012-11-28/09:55:37 446 1 17 256 229 1 162 72410 YES NO NO CUSTOMER CUSTOMER_PK QTUNE NORMAL NO N 100000 100000 100.0 2012-11-28/09:55:34 100000 1 17 256 208 1 1 780 YES NO NO ORDER_LINE ORDER_LINE_N1 QTUNE NORMAL NO N 3402652 499399 14.7 2012-11-28/09:57:05 302976 2 77 7936 7570 1 11 3401234 YES NO NO ORDER_LINE ORDER_LINE_N2 QTUNE NORMAL NO N 3438118 505056 14.7 2012-11-28/09:57:28 201968 2 77 7936 7645 1 17 3435899 YES NO NO ORDER_LINE ORDER_LINE_PK QTUNE NORMAL NO N 3387371 506145 14.9 2012-11-28/09:56:44 3387371 2 76 7808 7395 1 1 18873 YES NO NO PART PART_N1 QTUNE NORMAL NO N 200000 200000 100.0 2012-11-28/09:55:51 200000 2 25 1280 1177 1 1 199846 YES NO NO PART PART_PK QTUNE NORMAL NO N 200000 200000 100.0 2012-11-28/09:55:46 200000 1 19 512 417 1 1 1398 YES NO NO SALES_ORDER SALES_ORDER_N1 QTUNE NORMAL NO N 300000 300000 100.0 2012-11-28/09:55:58 299840 2 24 1152 1087 1 1 299837 YES NO NO SALES_ORDER SALES_ORDER_N2 QTUNE NORMAL NO N 300000 300000 100.0 2012-11-28/09:56:03 299546 2 24 1152 1000 1 1 299799 YES NO NO SALES_ORDER SALES_ORDER_PK QTUNE NORMAL NO N 300000 300000 100.0 2012-11-28/09:56:06 300000 1 21 768 626 1 1 1657 YES NO NO
Fluctuatin Avg Fluctuatin Col Col Num Num Sample Num NDV Col Num Endpoint Global User Table Name Index Name Pos Column Name ID Rows Nulls Size Perc Distinct Count Low Value High Value Last Analyzed Len Density Buckets Histogram Count Stats Stats ----------- -------------- --- ------------- --- ------- ----- ------- ----- -------- ---------- ---------------------------------- ---------------------------------- ------------------- --- -------------- ------- --------- ---------- ------ ----- CUSTOMER CUSTOMER_F1 1 SYS_NC00005$ 100000 0 100000 100.0 99728 FALSE "aaaegjqprnjlyoswrlicmzrkjvanpmqa" "zzzvqzyrqvbcezljjjraixvrnkamexai" 2012-11-28/09:55:31 41 1.002727e-05 1 NONE FALSE YES NO CUSTOMER CUSTOMER_N1 1 CUSTOMER_NAME 2 100000 0 100000 100.0 99624 FALSE "AAAEGJQPRNJLYOSWRLICMZRKJVANPMQA" "ZZZVQZYRQVBCEZLJJJRAIXVRNKAMEXAI" 2012-11-28/09:55:31 41 1.003774e-05 1 NONE FALSE YES NO CUSTOMER CUSTOMER_N2 1 CUSTOMER_TYPE 3 100000 0 6315 6.3 6 FALSE "1" "6" 2012-11-28/09:55:31 2 5.080454e-06 6 FREQUENCY FALSE YES NO CUSTOMER CUSTOMER_N2 2 CREDIT_LIMIT 4 100000 14285 5430 6.3 101 FALSE "1000" "101000" 2012-11-28/09:55:31 4 5.908484e-06 101 FREQUENCY FALSE YES NO CUSTOMER CUSTOMER_PK 1 CUSTOMER_ID 1 100000 0 100000 100.0 100000 FALSE "1" "100000" 2012-11-28/09:55:31 5 1.000000e-05 1 NONE FALSE YES NO ORDER_LINE ORDER_LINE_N1 1 ORDER_ID 2 3450201 0 3450201 100.0 302976 FALSE "1" "300000" 2012-11-28/09:56:06 5 3.300591e-06 1 NONE FALSE YES NO ORDER_LINE ORDER_LINE_N2 1 PART_ID 4 3450201 0 3450201 100.0 201968 FALSE "1" "200000" 2012-11-28/09:56:06 5 4.951279e-06 1 NONE FALSE YES NO ORDER_LINE ORDER_LINE_PK 1 LINE_ID 1 3450201 0 3450201 100.0 3450201 FALSE "1" "3450201" 2012-11-28/09:56:06 6 2.898382e-07 1 NONE FALSE YES NO PART PART_N1 1 PART_NAME 2 200000 0 200000 100.0 200000 FALSE "0004S6EOFFEEVRMBSW3NQ791PVPZ3R" "ZZZUCN4U8VWW9FLMIGPB99E5QH4745" 2012-11-28/09:55:41 31 5.000000e-06 1 NONE FALSE YES NO PART PART_PK 1 PART_ID 1 200000 0 200000 100.0 200000 FALSE "1" "200000" 2012-11-28/09:55:41 5 5.000000e-06 1 NONE FALSE YES NO SALES_ORDER SALES_ORDER_N1 1 ORDER_NUM 2 300000 0 300000 100.0 299840 FALSE "AA003667020574" "ZZ999041716303" 2012-11-28/09:55:51 15 3.335112e-06 1 NONE FALSE YES NO SALES_ORDER SALES_ORDER_N2 1 CUSTOMER_ID 4 300000 0 300000 100.0 95952 FALSE "1" "99999" 2012-11-28/09:55:51 5 1.042188e-05 1 NONE FALSE YES NO SALES_ORDER SALES_ORDER_N2 2 ORDER_DATE 3 300000 0 300000 100.0 1002 FALSE " 2010/02/23 00:00:00" " 2012/11/20 00:00:00" 2012-11-28/09:55:51 8 9.980040e-04 1 NONE FALSE YES NO SALES_ORDER SALES_ORDER_PK 1 ORDER_ID 1 300000 0 300000 100.0 300000 FALSE "1" "300000" 2012-11-28/09:55:51 5 3.333333e-06 1 NONE FALSE YES NO