Subscribe to Posts by Email

Subscriber Count

    696

Disclaimer

All information is offered in good faith and in the hope that it may be of use for educational purpose and for Database community purpose, but is not guaranteed to be correct, up to date or suitable for any particular purpose. db.geeksinsight.com accepts no liability in respect of this information or its use. This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content and if notify any such I am happy to remove. Product and company names mentioned in this website may be the trademarks of their respective owners and published here for informational purpose only. This is my personal blog. The views expressed on these pages are mine and learnt from other blogs and bloggers and to enhance and support the DBA community and this web blog does not represent the thoughts, intentions, plans or strategies of my current employer nor the Oracle and its affiliates or any other companies. And this website does not offer or take profit for providing these content and this is purely non-profit and for educational purpose only. If you see any issues with Content and copy write issues, I am happy to remove if you notify me. Contact Geek DBA Team, via geeksinsights@gmail.com

Pages

12c Database : Session level (private)statistics for global temporary tables

Often, 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

Comments are closed.