-- Demo case: Researching DBMS_XPLAN column projection information (rowset and count) -- Date: 20-01-2015 -- References: Data was generated with Swingbench ( http://dominicgiles.com/index.html ) -- Test case based on mail from Martin Bach - i used hints to get the same execution plan -- Parameter "_rowsets_enabled" mentioned in MOS ID #17016479.8 -- Bug 17016479 - Wrong results from nested loops join to CDB view ( https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=17016479.8 ) -- Demo run on Oracle version: 12.1.0.1 desc SH.SALES Name Null? Type --------------------- -------- -------------- PROD_ID NOT NULL NUMBER ... select /*+ gather_plan_statistics no_place_group_by use_hash(s) */ count(s.prod_id), p.prod_name, c.channel_desc, c.channel_class from sh.sales s, sh.channels c, sh.products p where s.time_id = DATE '2013-01-13' and s.CHANNEL_ID = c.channel_id and s.prod_id = p.prod_id group by p.prod_name, c.channel_desc, c.channel_class; Plan hash value: 1304540706 --------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | --------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 5711 (100)| | 0 |00:00:00.15 | 20767 | 20717 | | | | | 1 | HASH GROUP BY | | 1 | 341 | 22506 | 5711 (1)| 00:00:01 | 0 |00:00:00.15 | 20767 | 20717 | 898K| 898K| | |* 2 | HASH JOIN | | 1 | 341 | 22506 | 5711 (1)| 00:00:01 | 0 |00:00:00.15 | 20767 | 20717 | 1321K| 1321K| 1093K (0)| | 3 | TABLE ACCESS FULL | CHANNELS | 1 | 5 | 105 | 18 (0)| 00:00:01 | 5 |00:00:00.01 | 22 | 0 | | | | |* 4 | HASH JOIN | | 1 | 341 | 15345 | 5693 (1)| 00:00:01 | 0 |00:00:00.15 | 20745 | 20717 | 1185K| 1185K| 1281K (0)| | 5 | TABLE ACCESS FULL| PRODUCTS | 1 | 72 | 2160 | 18 (0)| 00:00:01 | 72 |00:00:00.01 | 18 | 0 | | | | |* 6 | TABLE ACCESS FULL| SALES | 1 | 635 | 9525 | 5675 (1)| 00:00:01 | 0 |00:00:00.15 | 20727 | 20717 | | | | --------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 3 - SEL$1 / C@SEL$1 5 - SEL$1 / P@SEL$1 6 - SEL$1 / S@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('12.1.0.1') DB_VERSION('12.1.0.1') ALL_ROWS OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "P"@"SEL$1") FULL(@"SEL$1" "S"@"SEL$1") FULL(@"SEL$1" "C"@"SEL$1") LEADING(@"SEL$1" "P"@"SEL$1" "S"@"SEL$1" "C"@"SEL$1") USE_HASH(@"SEL$1" "S"@"SEL$1") USE_HASH(@"SEL$1" "C"@"SEL$1") SWAP_JOIN_INPUTS(@"SEL$1" "C"@"SEL$1") USE_HASH_AGGREGATION(@"SEL$1") END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("S"."CHANNEL_ID"="C"."CHANNEL_ID") 4 - access("S"."PROD_ID"="P"."PROD_ID") 6 - filter("S"."TIME_ID"=TO_DATE(' 2013-01-13 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "P"."PROD_NAME"[VARCHAR2,50], "C"."CHANNEL_DESC"[VARCHAR2,20], "C"."CHANNEL_CLASS"[VARCHAR2,20], COUNT(*)[22] 2 - (#keys=1; rowset=200) "C"."CHANNEL_CLASS"[VARCHAR2,20], "C"."CHANNEL_DESC"[VARCHAR2,20], "P"."PROD_NAME"[VARCHAR2,50] 3 - (rowset=200) "C"."CHANNEL_ID"[NUMBER,22], "C"."CHANNEL_DESC"[VARCHAR2,20], "C"."CHANNEL_CLASS"[VARCHAR2,20] 4 - (#keys=1; rowset=200) "P"."PROD_NAME"[VARCHAR2,50], "S"."CHANNEL_ID"[NUMBER,22] 5 - (rowset=200) "P"."PROD_ID"[NUMBER,22], "P"."PROD_NAME"[VARCHAR2,50] 6 - (rowset=200) "S"."PROD_ID"[NUMBER,22], "S"."CHANNEL_ID"[NUMBER,22] CBO trace (alter session set events '10053 trace name context forever, level 1') ------------------ CNT: Considering count(col) to count(*) on query block SEL$1 (#0) ************************* Count(col) to Count(*) (CNT) ************************* CNT: Converting COUNT(PROD_ID) to COUNT(*). CNT: COUNT() to COUNT(*) done. … Final query after transformations:******* UNPARSED QUERY IS ******* SELECT /*+ NO_PLACE_GROUP_BY () USE_HASH ("S") */ COUNT(*) "COUNT(S.PROD_ID)","P"."PROD_NAME" "PROD_NAME","C"."CHANNEL_DESC" "CHANNEL_DESC","C"."CHANNEL_CLASS" "CHANNEL_CLASS" FROM "SH"."SALES" "S","SH"."CHANNELS" "C","SH"."PRODUCTS" "P" WHERE "S"."TIME_ID"=TO_DATE(' 2013-01-13 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "S"."CHANNEL_ID"="C"."CHANNEL_ID" AND "S"."PROD_ID"="P"."PROD_ID" GROUP BY "P"."PROD_NAME","C"."CHANNEL_DESC","C"."CHANNEL_CLASS" … Hidden parameters (>= Oracle 12c as not available in my latest 11g R2 release - 11.2.0.3.6) ------------------ NAME VALUE ISSYS_MOD DESCRIPTION ------------------------------------------------------- -------------------------------------------------- --------- ---------------------------------------------------------------------------------------------------- _rowsets_cdb_view_enabled TRUE IMMEDIATE rowsets enabled for CDB views _rowsets_enabled TRUE IMMEDIATE enable/disable rowsets _rowsets_max_rows 200 IMMEDIATE maximum number of rows in a roust _rowsets_target_maxsize 524288 IMMEDIATE target size in bytes for space reserved in the frame for a rowset shell$ oerr ora 10055 10055, 00000, "Rowsets: turn off rowsets for various operations" // *Document: NO // *Cause: N/A // *Action: Turns off rowsets for various operations // Level: // 0x00000001 - turn off for table scan // 0x00000002 - turn off for hash join consume // 0x00000004 - turn off for hash join produce // 0x00000008 - turn off for group by // 0x00000010 - turn off for sort // 0x00000020 - turn off for table-queue out // 0x00000040 - turn off for table-queue in // 0x00000080 - turn off for identity // 0x00000100 - turn off for granule iterator // 0x00000200 - turn off for EVA functions // 0x00000400 - turn off for PL/SQL // 0x00000800 - turn off for upgrade // 0x00001000 - turn off for database startup // 0x00002000 - turn off for blobs and clobs // 0x00004000 - turn off for tracing row source // 0x00008000 - turn off rowset information in explain plan // 0x00010000 - disable hash join rowsets fast path // 0x00020000 - turn off for bloom create // 0x00040000 - turn off for bloom use // 0x00080000 - disable prefetch for hash join // 0x00100000 - disable prefetch for bloom // 0x00200000 - disable semi blocking hash join // 0x00400000 - turn off rowset for fixed table alter session set events '10055 trace name context forever, level 1'; select /*+ gather_plan_statistics no_place_group_by use_hash(s) */ count(s.prod_id), p.prod_name, c.channel_desc, c.channel_class from sh.sales s, sh.channels c, sh.products p where s.time_id = DATE '2013-01-13' and s.CHANNEL_ID = c.channel_id and s.prod_id = p.prod_id group by p.prod_name, c.channel_desc, c.channel_class; Plan hash value: 1304540706 --------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | --------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 5711 (100)| | 0 |00:00:00.16 | 20767 | 20717 | | | | | 1 | HASH GROUP BY | | 1 | 341 | 22506 | 5711 (1)| 00:00:01 | 0 |00:00:00.16 | 20767 | 20717 | 898K| 898K| | |* 2 | HASH JOIN | | 1 | 341 | 22506 | 5711 (1)| 00:00:01 | 0 |00:00:00.16 | 20767 | 20717 | 1321K| 1321K| 1034K (0)| | 3 | TABLE ACCESS FULL | CHANNELS | 1 | 5 | 105 | 18 (0)| 00:00:01 | 5 |00:00:00.01 | 22 | 0 | | | | |* 4 | HASH JOIN | | 1 | 341 | 15345 | 5693 (1)| 00:00:01 | 0 |00:00:00.16 | 20745 | 20717 | 1185K| 1185K| 1286K (0)| | 5 | TABLE ACCESS FULL| PRODUCTS | 1 | 72 | 2160 | 18 (0)| 00:00:01 | 72 |00:00:00.01 | 18 | 0 | | | | |* 6 | TABLE ACCESS FULL| SALES | 1 | 635 | 9525 | 5675 (1)| 00:00:01 | 0 |00:00:00.16 | 20727 | 20717 | | | | --------------------------------------------------------------------------------------------------------------------------------------------------------------- Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - "P"."PROD_NAME"[VARCHAR2,50], "C"."CHANNEL_DESC"[VARCHAR2,20], "C"."CHANNEL_CLASS"[VARCHAR2,20], COUNT(*)[22] 2 - (#keys=1; rowset=200) "C"."CHANNEL_CLASS"[VARCHAR2,20], "C"."CHANNEL_DESC"[VARCHAR2,20], "P"."PROD_NAME"[VARCHAR2,50] 3 - "C"."CHANNEL_ID"[NUMBER,22], "C"."CHANNEL_DESC"[VARCHAR2,20], "C"."CHANNEL_CLASS"[VARCHAR2,20] 4 - (#keys=1; rowset=200) "P"."PROD_NAME"[VARCHAR2,50], "S"."CHANNEL_ID"[NUMBER,22] 5 - "P"."PROD_ID"[NUMBER,22], "P"."PROD_NAME"[VARCHAR2,50] 6 - "S"."PROD_ID"[NUMBER,22], "S"."CHANNEL_ID"[NUMBER,22]