Skip to content

Instantly share code, notes, and snippets.

@oracle9999

oracle9999/gist:909b1477a321cd2b4e49eb81327687eb Secret

Last active June 30, 2023 11:07
Show Gist options
  • Save oracle9999/909b1477a321cd2b4e49eb81327687eb to your computer and use it in GitHub Desktop.
Save oracle9999/909b1477a321cd2b4e49eb81327687eb to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.txt
******************************* Global temporary table *****************************
SQL> create global temporary table t ( id number , c1 varchar2(130) ) on commit preserve rows ;
Table created.
Elapsed: 00:00:00.01
SQL>
SQL> create global temporary table x as select object_id , object_name from dba_objects ;
Table created.
Elapsed: 00:00:01.23
SQL>
SQL> create index indx11 on x(object_id) ;
Index created.
Elapsed: 00:00:00.01
SQL>
SQL>
SQL> explain plan for insert /*+APPEND enable_parallel_dml */ into t select /*+parallel(10) */ * from x where object_id=123 ;
Explained.
Elapsed: 00:00:00.11
SQL>
SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'ADVANCED'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3443877637
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 79 | 1 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 79 | 1 (0)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT (TEMP SEGMENT MERGE) | T | | | | | Q1,01 | PCWP | |
| 4 | OPTIMIZER STATISTICS GATHERING | | 1 | 79 | 1 (0)| 00:00:01 | Q1,01 | PCWP | |
| 5 | BUFFER SORT | | | | | | Q1,01 | PCWC | |
| 6 | PX RECEIVE | | 1 | 79 | 1 (0)| 00:00:01 | Q1,01 | PCWP | |
| 7 | PX SEND ROUND-ROBIN | :TQ10000 | 1 | 79 | 1 (0)| 00:00:01 | | S->P | RND-ROBIN |
| 8 | TABLE ACCESS BY INDEX ROWID BATCHED| X | 1 | 79 | 1 (0)| 00:00:01 | | | |
|* 9 | INDEX RANGE SCAN | INDX11 | 1 | | 1 (0)| 00:00:01 | | | |
------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
8 - SEL$1 / X@SEL$1
9 - SEL$1 / X@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "X"@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "X"@"SEL$1" ("X"."OBJECT_ID"))
FULL(@"INS$1" "T"@"INS$1")
PQ_DISTRIBUTE(@"INS$1" "T"@"INS$1" RANDOM)
OUTLINE_LEAF(@"INS$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
OPT_PARAM('_px_adaptive_dist_method' 'off')
DB_VERSION('19.1.0')
OPTIMIZER_FEATURES_ENABLE('19.1.0')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
9 - access("OBJECT_ID"=123)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - SYSDEF[4], SYSDEF[32720], SYSDEF[1], SYSDEF[120], SYSDEF[32720]
2 - (#keys=0) SYSDEF[4], SYSDEF[32720], SYSDEF[1], SYSDEF[120], SYSDEF[32720]
3 - SYSDEF[4], SYSDEF[32720], SYSDEF[1], SYSDEF[120], SYSDEF[32720]
4 - (#keys=6) ["X"."OBJECT_ID", "X"."OBJECT_NAME", MIN("X"."OBJECT_ID"), MAX("X"."OBJECT_ID"), MIN("X"."OBJECT_NAME"),
MAX("X"."OBJECT_NAME")] "X"."OBJECT_ID"[NUMBER,22], "X"."OBJECT_NAME"[VARCHAR2,128]
5 - (#keys=0) "X"."OBJECT_ID"[NUMBER,22], "X"."OBJECT_NAME"[VARCHAR2,128]
6 - "X"."OBJECT_ID"[NUMBER,22], "X"."OBJECT_NAME"[VARCHAR2,128]
7 - (#keys=0) "X"."OBJECT_ID"[NUMBER,22], "X"."OBJECT_NAME"[VARCHAR2,128]
8 - "X".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22], "X"."OBJECT_NAME"[VARCHAR2,128]
9 - "X".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
0 - STATEMENT
- parallel(10)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- Degree of Parallelism is 10 because of hint
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[INS$1]]></n><f><h><t><![CDATA[T]]></t><s><![CDATA[INS$1]]></s></h></f></q>
<q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[X]]></t><s><![CDATA[SEL$1]]></s></h></f></q>
82 rows selected.
Elapsed: 00:00:00.12
SQL>
SQL> explain plan for select /*+parallel(10)*/ * from x where object_id=123 ;
Explained.
Elapsed: 00:00:00.00
SQL>
SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'ADVANCED'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 520946394
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| X | 1 | 79 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDX11 | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / X@SEL$1
2 - SEL$1 / X@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "X"@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "X"@"SEL$1" ("X"."OBJECT_ID"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
OPT_PARAM('_px_adaptive_dist_method' 'off')
DB_VERSION('19.1.0')
OPTIMIZER_FEATURES_ENABLE('19.1.0')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=123)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "OBJECT_ID"[NUMBER,22], "X"."OBJECT_NAME"[VARCHAR2,128]
2 - "X".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
0 - STATEMENT
U - parallel(10)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[X]]></t><s><![CDATA[SEL$1]]><
/s></h></f></q>
62 rows selected.
Elapsed: 00:00:00.09
SQL>
************************** Normal Heap table *******************
SQL> create table t ( id number , c1 varchar2(130) ) ;
Table created.
Elapsed: 00:00:00.01
SQL>
SQL> create table x as select object_id , object_name from dba_objects ;
Table created.
Elapsed: 00:00:01.90
SQL>
SQL> create index indx11 on x(object_id) ;
Index created.
Elapsed: 00:00:00.75
SQL>
SQL>
SQL> explain plan for insert /*+APPEND enable_parallel_dml */ into t select /*+parallel(10) */ * from x where object_id=123 ;
Explained.
Elapsed: 00:00:00.01
SQL>
SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'ADVANCED'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3058381582
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 30 | 4 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 30 | 4 (0)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT (HYBRID TSM/HWMB) | T | | | | | Q1,01 | PCWP | |
| 4 | OPTIMIZER STATISTICS GATHERING | | 1 | 30 | 4 (0)| 00:00:01 | Q1,01 | PCWP | |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| X | 1 | 30 | 4 (0)| 00:00:01 | Q1,01 | PCWP | |
| 6 | PX RECEIVE | | 1 | | 3 (0)| 00:00:01 | Q1,01 | PCWP | |
| 7 | PX SEND HASH (BLOCK ADDRESS) | :TQ10000 | 1 | | 3 (0)| 00:00:01 | Q1,00 | S->P | HASH (BLOCK|
| 8 | PX SELECTOR | | | | | | Q1,00 | SCWC | |
|* 9 | INDEX RANGE SCAN | INDX11 | 1 | | 3 (0)| 00:00:01 | Q1,00 | SCWP | |
---------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
5 - SEL$1 / X@SEL$1
9 - SEL$1 / X@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "X"@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "X"@"SEL$1" ("X"."OBJECT_ID"))
FULL(@"INS$1" "T"@"INS$1")
OUTLINE_LEAF(@"INS$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
OPT_PARAM('_px_adaptive_dist_method' 'off')
DB_VERSION('19.1.0')
OPTIMIZER_FEATURES_ENABLE('19.1.0')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
9 - access("OBJECT_ID"=123)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - SYSDEF[4], SYSDEF[32720], SYSDEF[1], SYSDEF[120], SYSDEF[32720]
2 - (#keys=0) SYSDEF[4], SYSDEF[32720], SYSDEF[1], SYSDEF[120], SYSDEF[32720]
3 - SYSDEF[4], SYSDEF[32720], SYSDEF[1], SYSDEF[120], SYSDEF[32720]
4 - (#keys=6) ["X"."OBJECT_ID", "X"."OBJECT_NAME", MIN("X"."OBJECT_ID"), MAX("X"."OBJECT_ID"),
MIN("X"."OBJECT_NAME"), MAX("X"."OBJECT_NAME")] "X"."OBJECT_ID"[NUMBER,22], "X"."OBJECT_NAME"[VARCHAR2,128]
5 - "X".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22], "X"."OBJECT_NAME"[VARCHAR2,128]
6 - "X".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]
7 - (#keys=0) "X".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]
8 - "X".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]
9 - "X".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
0 - STATEMENT
- parallel(10)
Note
-----
- Degree of Parallelism is 10 because of hint
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[INS$1]]></n><f><h><t><![CDATA[T]]></t><s><![CDATA[INS$1]]></s></h></f></q>
<q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[X]]></t><s><![CDATA[SEL$1]]></s></h></f></q>
80 rows selected.
Elapsed: 00:00:00.17
SQL>
SQL> explain plan for select /*+parallel(10)*/ * from x where object_id=123 ;
Explained.
Elapsed: 00:00:00.00
SQL>
SQL> select plan_table_output from table(dbms_xplan.display('plan_table',null,'ADVANCED'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 153735942
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 4 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 30 | 4 (0)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| X | 1 | 30 | 4 (0)| 00:00:01 | Q1,01 | PCWP | |
| 4 | BUFFER SORT | | | | | | Q1,01 | PCWC | |
| 5 | PX RECEIVE | | 1 | | 3 (0)| 00:00:01 | Q1,01 | PCWP | |
| 6 | PX SEND HASH (BLOCK ADDRESS) | :TQ10000 | 1 | | 3 (0)| 00:00:01 | Q1,00 | S->P | HASH (BLOCK|
| 7 | PX SELECTOR | | | | | | Q1,00 | SCWC | |
|* 8 | INDEX RANGE SCAN | INDX11 | 1 | | 3 (0)| 00:00:01 | Q1,00 | SCWP | |
-------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / X@SEL$1
8 - SEL$1 / X@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "X"@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "X"@"SEL$1" ("X"."OBJECT_ID"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
OPT_PARAM('_px_adaptive_dist_method' 'off')
DB_VERSION('19.1.0')
OPTIMIZER_FEATURES_ENABLE('19.1.0')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access("OBJECT_ID"=123)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "OBJECT_ID"[NUMBER,22], "X"."OBJECT_NAME"[VARCHAR2,128]
2 - (#keys=0) "OBJECT_ID"[NUMBER,22], "X"."OBJECT_NAME"[VARCHAR2,128]
3 - "OBJECT_ID"[NUMBER,22], "X"."OBJECT_NAME"[VARCHAR2,128]
4 - (#keys=0) "X".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]
5 - (rowset=256) "X".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]
6 - (#keys=0) "X".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]
7 - "X".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]
8 - "X".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
0 - STATEMENT
- parallel(10)
Note
-----
- Degree of Parallelism is 10 because of hint
Query Block Registry:
---------------------
<q o="2" f="y"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[X]]></t><s><![CDATA[SEL$1]]></s></h></f></q>
74 rows selected.
Elapsed: 00:00:00.12
SQL>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment