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 ---------- ------- ----- ----- ----- ------ ----- ------ --------- ---------- ------- ------- ----- ------------- --- -------------------- 491627931 4.296 4.048 0.001 0.000 1940 32 0 2 1 2 1 1 0 MEM GV$SQLAREA_PLAN_HASH 3648575135 STA DBA_SQLTUNE_PLANS
# | Source | Plan Hash Value | SQL Handle | Plan Name | Inst ID | Child Number | Executions | Format |
---|---|---|---|---|---|---|---|---|
1 | GV$SQL_PLAN | 491627931 | 1 | 0 | 1 | ADVANCED ALLSTATS LAST | ||
2 | PLAN_TABLE | 491627931 | ADVANCED |
Plan hash value: 491627931 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 118 (100)| | | | 2 |00:00:03.84 | 229 | | | | | 1 | HASH GROUP BY | | 1 | 4 | 200 | 118 (2)| 00:00:02 | | | 2 |00:00:03.84 | 229 | 801K| 801K| 560K (0)| |* 2 | HASH JOIN | | 1 | 130 | 6500 | 117 (1)| 00:00:02 | | | 165 |00:00:03.84 | 229 | 855K| 855K| 1068K (0)| | 3 | JOIN FILTER CREATE | :BF0000 | 1 | 130 | 3770 | 58 (0)| 00:00:01 | | | 165 |00:00:00.01 | 156 | | | | |* 4 | HASH JOIN | | 1 | 130 | 3770 | 58 (0)| 00:00:01 | | | 165 |00:00:00.01 | 156 | 951K| 951K| 1195K (0)| | 5 | TABLE ACCESS FULL | PRODUCTS | 1 | 72 | 648 | 3 (0)| 00:00:01 | | | 72 |00:00:00.01 | 3 | | | | | 6 | PARTITION RANGE ALL | | 1 | 130 | 2600 | 55 (0)| 00:00:01 | 1 | 28 | 165 |00:00:00.01 | 153 | | | | | 7 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 28 | 130 | 2600 | 55 (0)| 00:00:01 | 1 | 28 | 165 |00:00:00.01 | 153 | | | | | 8 | BITMAP CONVERSION TO ROWIDS | | 16 | | | | | | | 165 |00:00:00.01 | 32 | | | | |* 9 | BITMAP INDEX SINGLE VALUE | SALES_CUST_BIX | 16 | | | | | 1 | 28 | 4 |00:00:00.01 | 32 | | | | | 10 | VIEW | VW_GBF_9 | 1 | 1460 | 30660 | 58 (0)| 00:00:01 | | | 28 |00:00:03.83 | 73 | | | | | 11 | HASH GROUP BY | | 1 | 1460 | 11680 | 58 (0)| 00:00:01 | | | 28 |00:00:03.83 | 73 | 848K| 848K| 2539K (0)| | 12 | JOIN FILTER USE | :BF0000 | 1 | 918K| 7178K| 58 (0)| 00:00:01 | | | 16548 |00:00:03.81 | 73 | | | | | 13 | PARTITION RANGE ALL | | 1 | 918K| 7178K| 58 (0)| 00:00:01 | 1 | 28 | 918K|00:00:02.81 | 73 | | | | | 14 | BITMAP CONVERSION TO ROWIDS | | 28 | 918K| 7178K| 58 (0)| 00:00:01 | | | 918K|00:00:00.93 | 73 | | | | | 15 | BITMAP INDEX FULL SCAN | SALES_TIME_BIX | 28 | | | | | 1 | 28 | 1460 |00:00:00.01 | 73 | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$15B92CC1 5 - SEL$15B92CC1 / P@SEL$1 7 - SEL$15B92CC1 / S1@SEL$1 10 - SEL$AEFB4ED9 / VW_GBF_9@SEL$CEB13333 11 - SEL$AEFB4ED9 14 - SEL$AEFB4ED9 / S2@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.3') DB_VERSION('11.2.0.3') ALL_ROWS OUTLINE_LEAF(@"SEL$AEFB4ED9") OUTLINE_LEAF(@"SEL$15B92CC1") PLACE_GROUP_BY(@"SEL$1" ( "S2"@"SEL$1" ) 9) OUTLINE(@"SEL$CEB13333") OUTLINE(@"SEL$1") FULL(@"SEL$15B92CC1" "P"@"SEL$1") BITMAP_TREE(@"SEL$15B92CC1" "S1"@"SEL$1" AND(("SALES"."CUST_ID"))) NO_ACCESS(@"SEL$15B92CC1" "VW_GBF_9"@"SEL$CEB13333") LEADING(@"SEL$15B92CC1" "P"@"SEL$1" "S1"@"SEL$1" "VW_GBF_9"@"SEL$CEB13333") USE_HASH(@"SEL$15B92CC1" "S1"@"SEL$1") USE_HASH(@"SEL$15B92CC1" "VW_GBF_9"@"SEL$CEB13333") PX_JOIN_FILTER(@"SEL$15B92CC1" "VW_GBF_9"@"SEL$CEB13333") USE_HASH_AGGREGATION(@"SEL$15B92CC1") BITMAP_TREE(@"SEL$AEFB4ED9" "S2"@"SEL$1" AND(("SALES"."TIME_ID"))) USE_HASH_AGGREGATION(@"SEL$AEFB4ED9") END_OUTLINE_DATA */ Peeked Binds (identified by position): -------------------------------------- 1 - (NUMBER): 10 Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("S1"."TIME_ID"="ITEM_1") 4 - access("S1"."PROD_ID"="P"."PROD_ID") 9 - access("S1"."CUST_ID"=:B1) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "S1"."CHANNEL_ID"[NUMBER,22], SUM("P"."PROD_LIST_PRICE"*"ITEM_2")[22] 2 - (#keys=1) "P"."PROD_LIST_PRICE"[NUMBER,22], "S1"."CHANNEL_ID"[NUMBER,22], "ITEM_2"[NUMBER,22] 3 - "P"."PROD_LIST_PRICE"[NUMBER,22], "S1"."CHANNEL_ID"[NUMBER,22], "S1"."TIME_ID"[DATE,7] 4 - (#keys=1) "P"."PROD_LIST_PRICE"[NUMBER,22], "S1"."CHANNEL_ID"[NUMBER,22], "S1"."TIME_ID"[DATE,7] 5 - "P"."PROD_ID"[NUMBER,22], "P"."PROD_LIST_PRICE"[NUMBER,22] 6 - "S1"."PROD_ID"[NUMBER,22], "S1"."TIME_ID"[DATE,7], "S1"."CHANNEL_ID"[NUMBER,22] 7 - "S1"."PROD_ID"[NUMBER,22], "S1"."TIME_ID"[DATE,7], "S1"."CHANNEL_ID"[NUMBER,22] 8 - "S1".ROWID[ROWID,10], "S1"."CUST_ID"[NUMBER,22] 9 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920], "S1"."CUST_ID"[NUMBER,22] 10 - "ITEM_1"[DATE,7], "ITEM_2"[NUMBER,22] 11 - "S2"."TIME_ID"[DATE,7], COUNT(*)[22] 12 - "S2"."TIME_ID"[DATE,7] 13 - "S2"."TIME_ID"[DATE,7] 14 - "S2".ROWID[ROWID,10], "S2"."TIME_ID"[DATE,7] 15 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920], "S2"."TIME_ID"[DATE,7]
SELECT /*+ gather_plan_statistics monitor bind_aware */ /* sqlt_s53237 */ s1.channel_id, SUM(p.prod_list_price) price FROM products p, sales s1, sales s2 WHERE s1.cust_id = :b1 AND s1.prod_id = p.prod_id AND s1.time_id = s2.time_id GROUP BY s1.channel_id Plan hash value: 491627931 ------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 200 | 118 (2)| 00:00:02 | | | | 1 | HASH GROUP BY | | 4 | 200 | 118 (2)| 00:00:02 | | | |* 2 | HASH JOIN | | 130 | 6500 | 117 (1)| 00:00:02 | | | | 3 | JOIN FILTER CREATE | :BF0000 | 130 | 3770 | 58 (0)| 00:00:01 | | | |* 4 | HASH JOIN | | 130 | 3770 | 58 (0)| 00:00:01 | | | | 5 | TABLE ACCESS FULL | PRODUCTS | 72 | 648 | 3 (0)| 00:00:01 | | | | 6 | PARTITION RANGE ALL | | 130 | 2600 | 55 (0)| 00:00:01 | 1 | 28 | | 7 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 130 | 2600 | 55 (0)| 00:00:01 | 1 | 28 | | 8 | BITMAP CONVERSION TO ROWIDS | | | | | | | | |* 9 | BITMAP INDEX SINGLE VALUE | SALES_CUST_BIX | | | | | 1 | 28 | | 10 | VIEW | VW_GBF_9 | 1460 | 30660 | 58 (0)| 00:00:01 | | | | 11 | HASH GROUP BY | | 1460 | 11680 | 58 (0)| 00:00:01 | | | | 12 | JOIN FILTER USE | :BF0000 | 918K| 7178K| 58 (0)| 00:00:01 | | | | 13 | PARTITION RANGE ALL | | 918K| 7178K| 58 (0)| 00:00:01 | 1 | 28 | | 14 | BITMAP CONVERSION TO ROWIDS | | 918K| 7178K| 58 (0)| 00:00:01 | | | | 15 | BITMAP INDEX FULL SCAN | SALES_TIME_BIX | | | | | 1 | 28 | ------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$15B92CC1 5 - SEL$15B92CC1 / P@SEL$1 7 - SEL$15B92CC1 / S1@SEL$1 10 - SEL$AEFB4ED9 / VW_GBF_9@SEL$CEB13333 11 - SEL$AEFB4ED9 14 - SEL$AEFB4ED9 / S2@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA USE_HASH_AGGREGATION(@"SEL$AEFB4ED9") BITMAP_TREE(@"SEL$AEFB4ED9" "S2"@"SEL$1" AND(("SALES"."TIME_ID"))) USE_HASH_AGGREGATION(@"SEL$15B92CC1") PX_JOIN_FILTER(@"SEL$15B92CC1" "VW_GBF_9"@"SEL$CEB13333") USE_HASH(@"SEL$15B92CC1" "VW_GBF_9"@"SEL$CEB13333") USE_HASH(@"SEL$15B92CC1" "S1"@"SEL$1") LEADING(@"SEL$15B92CC1" "P"@"SEL$1" "S1"@"SEL$1" "VW_GBF_9"@"SEL$CEB13333") NO_ACCESS(@"SEL$15B92CC1" "VW_GBF_9"@"SEL$CEB13333") BITMAP_TREE(@"SEL$15B92CC1" "S1"@"SEL$1" AND(("SALES"."CUST_ID"))) FULL(@"SEL$15B92CC1" "P"@"SEL$1") OUTLINE(@"SEL$1") OUTLINE(@"SEL$CEB13333") PLACE_GROUP_BY(@"SEL$1" ( "S2"@"SEL$1" ) 9) OUTLINE_LEAF(@"SEL$15B92CC1") OUTLINE_LEAF(@"SEL$AEFB4ED9") 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 - access("S1"."TIME_ID"="ITEM_1") 4 - access("S1"."PROD_ID"="P"."PROD_ID") 9 - access("S1"."CUST_ID"=TO_NUMBER(:B1)) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=1) "S1"."CHANNEL_ID"[NUMBER,22], SUM("P"."PROD_LIST_PRICE"*"ITEM_2")[22] 2 - (#keys=1) "P"."PROD_LIST_PRICE"[NUMBER,22], "S1"."CHANNEL_ID"[NUMBER,22], "ITEM_2"[NUMBER,22] 3 - "P"."PROD_LIST_PRICE"[NUMBER,22], "S1"."CHANNEL_ID"[NUMBER,22], "S1"."TIME_ID"[DATE,7] 4 - (#keys=1) "P"."PROD_LIST_PRICE"[NUMBER,22], "S1"."CHANNEL_ID"[NUMBER,22], "S1"."TIME_ID"[DATE,7] 5 - "P"."PROD_ID"[NUMBER,22], "P"."PROD_LIST_PRICE"[NUMBER,22] 6 - "S1"."PROD_ID"[NUMBER,22], "S1"."TIME_ID"[DATE,7], "S1"."CHANNEL_ID"[NUMBER,22] 7 - "S1"."PROD_ID"[NUMBER,22], "S1"."TIME_ID"[DATE,7], "S1"."CHANNEL_ID"[NUMBER,22] 8 - "S1".ROWID[ROWID,10], "S1"."CUST_ID"[NUMBER,22] 9 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920], "S1"."CUST_ID"[NUMBER,22] 10 - "ITEM_1"[DATE,7], "ITEM_2"[NUMBER,22] 11 - (#keys=1) "S2"."TIME_ID"[DATE,7], COUNT(*)[22] 12 - "S2"."TIME_ID"[DATE,7] 13 - "S2"."TIME_ID"[DATE,7] 14 - "S2".ROWID[ROWID,10], "S2"."TIME_ID"[DATE,7] 15 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7920], "S2"."TIME_ID"[DATE,7]
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 ---------- ----- ---- ---- ------ ------ ------ ----- ------------------- ------- ------- ------ ------ ----- --- ----- ------ ----- ------ ----- PRODUCTS SH NO N 72 72 72 100.0 2013-01-15/09:48:39 1 8 5 0 0 173 0 YES NO NO SALES SH YES N 928300 918843 918843 100.0 2013-01-15/09:48:57 1907 0 0 29 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 ---------- ---------------- --- ------ ----- ------ ----- -------- ---------- ---------------------- ---------------------- ------------------- --- -------------- ------- --------- ---------- ------ ----- PRODUCTS PROD_CATEGORY 7 72 0 72 100.0 5 FALSE "Electronics" "Software/Other" 2013-01-15/09:48:39 17 2.000000e-01 1 NONE FALSE YES NO PRODUCTS PROD_ID 1 72 0 72 100.0 72 FALSE "13" "148" 2013-01-15/09:48:39 4 1.388889e-02 1 NONE FALSE YES NO PRODUCTS PROD_LIST_PRICE 15 72 0 72 100.0 42 FALSE "6.99" "1299.99" 2013-01-15/09:48:39 5 2.380952e-02 1 NONE FALSE YES NO PRODUCTS PROD_STATUS 14 72 0 72 100.0 1 FALSE "STATUS" "STATUS" 2013-01-15/09:48:39 7 1.000000e+00 1 NONE FALSE YES NO PRODUCTS PROD_SUBCATEGORY 4 72 0 72 100.0 21 FALSE "Accessories" "Y Box Games" 2013-01-15/09:48:39 14 4.761905e-02 1 NONE FALSE YES NO SALES CHANNEL_ID 4 918843 0 918843 100.0 4 FALSE "2" "9" 2013-01-15/09:48:57 3 2.500000e-01 1 NONE FALSE YES NO SALES CUST_ID 2 918843 0 918843 100.0 7059 FALSE "2" "101000" 2013-01-15/09:48:57 5 1.416631e-04 1 NONE FALSE YES NO SALES PROD_ID 1 918843 0 918843 100.0 72 FALSE "13" "148" 2013-01-15/09:48:57 4 1.388889e-02 1 NONE FALSE YES NO SALES PROMO_ID 5 918843 0 918843 100.0 4 FALSE "33" "999" 2013-01-15/09:48:57 4 2.500000e-01 1 NONE FALSE YES NO SALES TIME_ID 3 918843 0 918843 100.0 1460 FALSE " 1998/01/01 00:00:00" " 2001/12/31 00:00:00" 2013-01-15/09:48:57 8 6.849315e-04 1 NONE 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 ---------- ------------------------ ----- ------ ---- ---- ----- ------ ----- ------------------- -------- ------ ------- ------- ------ ------ ------ ---------- ------ ----- ------ ----- PRODUCTS PRODUCTS_PK SH NORMAL NO N 72 72 100.0 2013-01-15/09:48:39 72 0 1 8 1 1 1 2 YES NO NO PRODUCTS PRODUCTS_PROD_CAT_IX SH NORMAL NO N 72 72 100.0 2013-01-15/09:48:39 5 0 1 8 1 1 1 8 YES NO NO PRODUCTS PRODUCTS_PROD_STATUS_BIX SH BITMAP NO N 1 1 100.0 2013-01-15/09:48:39 1 0 1 8 1 1 1 1 YES NO NO PRODUCTS PRODUCTS_PROD_SUBCAT_IX SH NORMAL NO N 72 72 100.0 2013-01-15/09:48:39 21 0 1 8 1 1 1 8 YES NO NO SALES SALES_CHANNEL_BIX SH BITMAP YES N 92 92 100.0 2013-01-15/09:49:25 4 1 47 11 23 92 YES NO NO SALES SALES_CUST_BIX SH BITMAP YES N 35808 35808 100.0 2013-01-15/09:49:07 7059 1 452 1 5 35808 YES NO NO SALES SALES_PROD_BIX SH BITMAP YES N 1074 1074 100.0 2013-01-15/09:49:16 72 1 32 1 14 1074 YES NO NO SALES SALES_PROMO_BIX SH BITMAP YES N 54 54 100.0 2013-01-15/09:49:42 4 1 30 7 13 54 YES NO NO SALES SALES_TIME_BIX SH BITMAP YES N 1460 1460 100.0 2013-01-15/09:49:34 1460 1 57 1 1 1460 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 ---------- ------------------------ --- ---------------- --- ------ ----- ------ ----- -------- ---------- ---------------------- ---------------------- ------------------- --- -------------- ------- --------- ---------- ------ ----- PRODUCTS PRODUCTS_PK 1 PROD_ID 1 72 0 72 100.0 72 FALSE "13" "148" 2013-01-15/09:48:39 4 1.388889e-02 1 NONE FALSE YES NO PRODUCTS PRODUCTS_PROD_CAT_IX 1 PROD_CATEGORY 7 72 0 72 100.0 5 FALSE "Electronics" "Software/Other" 2013-01-15/09:48:39 17 2.000000e-01 1 NONE FALSE YES NO PRODUCTS PRODUCTS_PROD_STATUS_BIX 1 PROD_STATUS 14 72 0 72 100.0 1 FALSE "STATUS" "STATUS" 2013-01-15/09:48:39 7 1.000000e+00 1 NONE FALSE YES NO PRODUCTS PRODUCTS_PROD_SUBCAT_IX 1 PROD_SUBCATEGORY 4 72 0 72 100.0 21 FALSE "Accessories" "Y Box Games" 2013-01-15/09:48:39 14 4.761905e-02 1 NONE FALSE YES NO SALES SALES_CHANNEL_BIX 1 CHANNEL_ID 4 918843 0 918843 100.0 4 FALSE "2" "9" 2013-01-15/09:48:57 3 2.500000e-01 1 NONE FALSE YES NO SALES SALES_CUST_BIX 1 CUST_ID 2 918843 0 918843 100.0 7059 FALSE "2" "101000" 2013-01-15/09:48:57 5 1.416631e-04 1 NONE FALSE YES NO SALES SALES_PROD_BIX 1 PROD_ID 1 918843 0 918843 100.0 72 FALSE "13" "148" 2013-01-15/09:48:57 4 1.388889e-02 1 NONE FALSE YES NO SALES SALES_PROMO_BIX 1 PROMO_ID 5 918843 0 918843 100.0 4 FALSE "33" "999" 2013-01-15/09:48:57 4 2.500000e-01 1 NONE FALSE YES NO SALES SALES_TIME_BIX 1 TIME_ID 3 918843 0 918843 100.0 1460 FALSE " 1998/01/01 00:00:00" " 2001/12/31 00:00:00" 2013-01-15/09:48:57 8 6.849315e-04 1 NONE FALSE YES NO