Oracle 23c New Features for DBA'sOften, when the global temporary tables are in use in batch processing, we have lot of problems regarding plan stability.
For example
A session inserting 1 row in a global temporary table based on some other table joins Another session which were apparently doing the same insert but will try to insert 1000 rows will use same plan which can be a disaster To overcome this, - Use stored outlines, profiles, fixed statistics, lock statistics - gather statistics after the load i.e insert - use optimizer samplings like dynamic sampling,cardinality or cursor_sharing hints - use different schemas for different workloads to avoid the sharing of sql statements
But from 12c onwards this has been enhanced to use the session level statistics for global temporary tables,
so that they can leverage the session statistics only rather the one that has been done in another processing.
So lets have a look with test case.
Tables for Test case
CREATE TABLE test_obj AS SELECT * FROM dba_objects, (SELECT * FROM dual CONNECT BY rownum < =10); A table test_obj_types that contains all distinct data types derived from ALL_OBJECTS CREATE TABLE test_obj_types AS SELECT DISTINCT object_type FROM all_objects; A table T that has the same structure as test_obj CREATE TABLE t AS SELECT * FROM test_obj WHERE 1=2; And a global temporary table temp_gtt that has only one column which will accept OBJECT_IDs CREATE GLOBAL TEMPORARY TABLE temp_gtt (object_id NUMBER NOT NULL) ON COMMIT PRESERVE ROWS; EXEC dbms_stats.gather_table_stats(ownname=>'SH',tabname=>'TEST_OBJ',cascade=>true); EXEC dbms_stats.gather_table_stats(ownname=>'SH',tabname=>'TEST_OBJ_TYPES',cascade=>true);
In Session 1
SQL> INSERT INTO temp_gtt (SELECT object_id FROM all_objects WHERE rownum< =1);
1 row created
SQL> INSERT INTO t
SELECT /*+ gather_plan_statistics */ o.*
FROM test_obj o
, test_obj_types t
, temp_gtt tt
WHERE o.object_type=t.object_type
AND o.object_id=tt.object_id;
10 rows created.
SQL> select * from table(dbms_xplan.display)cursor());
SQL> select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------
SQL_ID 21rvq7cdg8gjv, child number 0
-------------------------------------
INSERT INTO t SELECT /*+ gather_plan_statistics */ o.* FROM test_obj
o , test_obj_types t , temp_gtt tt WHERE
o.object_type=t.object_type AND o.object_id=tt.object_id
Plan hash value: 1735148311
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 4299 (100)| |
| 1 | LOAD TABLE CONVENTIONAL | | | | | |
|* 2 | HASH JOIN | | 9 | 1260 | 4299 (1)| 00:00:01 |
| 3 | MERGE JOIN CARTESIAN | | 41 | 943 | 5 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | TEMP_GTT | 1 | 13 | 2 (0)| 00:00:01 |
| 5 | BUFFER SORT | | 41 | 410 | 3 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | TEST_OBJ_TYPES | 41 | 410 | 3 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | TEST_OBJ | 915K| 102M| 4292 (1)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("O"."OBJECT_TYPE"="T"."OBJECT_TYPE" AND
"O"."OBJECT_ID"="TT"."OBJECT_ID")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
Session 2
SQL> INSERT INTO temp_gtt (SELECT object_id FROM all_objects WHERE rownum < =1000);
1000 rows created.
SQL> exec dbms_stats.gather_table_stats(ownname=>'SH', tabname=>'TEMP_GTT');
PL/SQL procedure successfully completed.
SQL> INSERT INTO t
SELECT /*+ gather_plan_statistics */ o.*
FROM test_obj o
, test_obj_types t
, temp_gtt tt
WHERE o.object_type=t.object_type
AND o.object_id=tt.object_id;
10000 rows created.
SQL> select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------
SQL_ID 21rvq7cdg8gjv, child number 0
-------------------------------------
INSERT INTO t SELECT /*+ gather_plan_statistics */ o.* FROM test_obj
o , test_obj_types t , temp_gtt tt WHERE
o.object_type=t.object_type AND o.object_id=tt.object_id
Plan hash value: 1735148311
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 4299 (100)| |
| 1 | LOAD TABLE CONVENTIONAL | | | | | |
|* 2 | HASH JOIN | | 9 | 1260 | 4299 (1)| 00:00:01 |
| 3 | MERGE JOIN CARTESIAN | | 41 | 943 | 5 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | TEMP_GTT | 1 | 13 | 2 (0)| 00:00:01 |
| 5 | BUFFER SORT | | 41 | 410 | 3 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | TEST_OBJ_TYPES | 41 | 410 | 3 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | TEST_OBJ | 915K| 102M| 4292 (1)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("O"."OBJECT_TYPE"="T"."OBJECT_TYPE" AND
"O"."OBJECT_ID"="TT"."OBJECT_ID")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
See above, the same plan has been used to insert both 10 rows and 10000 rows,
and merge join cartesian will have drastic performance degradation
Check the V$sql to see whether cursor is invalidated
SQL> select plan_hash_value, child_number, CHILD_ADDRESS, executions, invalidations, is_shareable, is_obsolete from v$sql where sql_id='21rvq7cdg8gjv'; PLAN_HASH_VALUE CHILD_NUMBER CHILD_ADDRESS EXECUTIONS INVALIDATIONS I I --------------- ------------ ---------------- ---------- ------------- - - 1735148311 0 00000000A14AD990 2 0 Y N
This is due to the cursor that has been shared and the statistics of the temporary table been used by both session.
Now from 12c, onwards each session can be use its own statistics (session level statistics) for Temporary tables.
Get the table preferences using get_prefs
SQL> select dbms_stats.get_prefs(ownname=>'SH', tabname=>'TEMP_GTT',pname=>'GLOBAL_TEMP_TABLE_STATS') from dual; DBMS_STATS.GET_PREFS(OWNNAME=>'SH',TABNAME=>'TEMP_GTT',PNAME=>'GLOBAL_TEMP_TABLE_STATS') -------------------------------------------------------------- SHARED
Set this value to "Session"
SQL> exec dbms_stats.set_table_prefs(ownname=>'SH', tabname=>'TEMP_GTT',pname=>'GLOBAL_TEMP_TABLE_STATS',pvalue=>'SESSION'); SQL> select dbms_stats.get_prefs(ownname=>'SH', tabname=>'TEMP_GTT',pname=>'GLOBAL_TEMP_TABLE_STATS') from dual; DBMS_STATS.GET_PREFS(OWNNAME=>'SH',TABNAME=>'TEMP_GTT',PNAME=>'GLOBAL_TEMP_TABLE_STATS') -------------------------------------------------------------- SESSION
Initiate the insert operation again
SQL> truncate table temp_gtt;
Table truncated.
SQL> INSERT INTO temp_gtt (SELECT object_id FROM all_objects WHERE rownum < =1000);
1000 rows created.
SQL> exec dbms_stats.gather_table_stats(ownname=>'SH', tabname=>'TEMP_GTT');
PL/SQL procedure successfully completed.
SQL> INSERT INTO t
SELECT /*+ gather_plan_statistics */ o.*
FROM test_obj o
, test_obj_types t
, temp_gtt tt
WHERE o.object_type=t.object_type
AND o.object_id=tt.object_id; 2 3 4 5 6 7
10000 rows created.
SQL> select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------
SQL_ID 21rvq7cdg8gjv, child number 1
-------------------------------------
INSERT INTO t SELECT /*+ gather_plan_statistics */ o.* FROM test_obj
o , test_obj_types t , temp_gtt tt WHERE
o.object_type=t.object_type AND o.object_id=tt.object_id
Plan hash value: 908924331
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 4299 (100)| |
| 1 | LOAD TABLE CONVENTIONAL | | | | | |
|* 2 | HASH JOIN | | 8804 | 1126K| 4299 (1)| 00:00:01 |
| 3 | TABLE ACCESS FULL | TEST_OBJ_TYPES | 41 | 410 | 3 (0)| 00:00:01 |
|* 4 | HASH JOIN | | 9877 | 1167K| 4296 (1)| 00:00:01 |
| 5 | TABLE ACCESS FULL | TEMP_GTT | 1000 | 4000 | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | TEST_OBJ | 915K| 102M| 4292 (1)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("O"."OBJECT_TYPE"="T"."OBJECT_TYPE")
4 - access("O"."OBJECT_ID"="TT"."OBJECT_ID")
Note
-----
- Global temporary table session private statistics used
30 rows selected.
The plan has been changed now and used correct statistics for TEMP_GTT 1000 rows rather 1 row earlier.
Take a look at the V$SQL about cursor invalidation.
SQL> select plan_hash_value, child_number, CHILD_ADDRESS, executions, invalidations, is_shareable, is_obsolete from v$sql where sql_id='21rvq7cdg8gjv'; PLAN_HASH_VALUE CHILD_NUMBER CHILD_ADDRESS EXECUTIONS INVALIDATIONS I I --------------- ------------ ---------------- ---------- ------------- - - 1735148311 0 00000000A14AD990 2 2 Y N 908924331 1 000000009FACFE20 1 0 Y N
This feature will help and make subtle difference in post processing of batch insertion and no more statistics
gotchas for temporary tables. Its that you have to collect statistics and do not close that session
and continue further processing.
-Thanks
Geek DBA
Follow Me!!!