Viewed 10K+ times! This question is You Asked Hi tom,We have reporting system which DB is migrated from Sybase to Oracle. There are lots temp tables like #xxx in stored procedure(SP). Now we encounter the performance issues when migrate these temp tables to Oracle global temp tables specially when exist in Sub SP.If different parent SPs invoke the same Sub SP, sometimes the number of rows in those temp tables will be huge different. And we tried to do followings:1. Add hint on the query, but the result is one parent SP performance being well while others are bad.2. We found the same SP sometimes was executing quickly while sometimes was bad. I queried in the v$sql view and found several SQL statements in the SP have more than one plan_hash_value, obviously the bad plan has more buffer_gets and time cost, I guess this was caused by the temp tables statistics which used a wrong plan.And I found the doc somewhere said using the dynamic sampling on the temp tables. so I did this like dbms_stats.delete_table_stats('&&SCHEMA_ID', 'TT_XXX'); dbms_stats.lock_table_stats('&&SCHEMA_ID', 'TT_XXX');however it is still not affect.Since original reporting SPs' logic is complicated, and there are lots of temp tables everywhere in parent SP and Sub SP, we still want to keep them. Do you have any suggestion or best practice when try to tuning this kind of performance issue? Thanks. and Connor said...One option could be to move to 12c where there are session specific stats for global temporary tables. But even that doesn't necessarily solve the entire issue. For example, look at this in a single session:SQL>SQL> create global temporary table gtt on commit preserve rows 2 as select * from dba_objects 3 where 1=0;Table created.SQL>SQL> insert into gtt 2 select * from dba_objects 3 where rownum commit;Commit complete.SQL>SQL> variable rc refcursorSQL> exec open :rc for 'select count(*) from gtt';PL/SQL procedure successfully completed.SQL> print rc COUNT(*)---------- 1001 row selected.SQL> select * from table(dbms_xplan.display_cursor());PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------------------------------------SQL_ID gmxmjw679mxx5, child number 0-------------------------------------select count(*) from gttPlan hash value: 3344941513------------------------------------------------------------------- Id Operation Name Rows Cost (%CPU) Time ------------------------------------------------------------------- 0 SELECT STATEMENT 2 (100) 1 SORT AGGREGATE 1 2 TABLE ACCESS FULL GTT 100 2 (0) 00:00:01 -------------------------------------------------------------------Note----- - dynamic statistics used: dynamic sampling (level=2)18 rows selected.SQL>SQL> insert into gtt 2 select * from dba_objects 3 where rownum SQL> variable rc refcursorSQL> exec open :rc for 'select count(*) from gtt';PL/SQL procedure successfully completed.SQL> print rc COUNT(*)---------- 101001 row selected.SQL> select * from table(dbms_xplan.display_cursor());PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------------------------------------SQL_ID gmxmjw679mxx5, child number 0-------------------------------------select count(*) from gttPlan hash value: 3344941513------------------------------------------------------------------- Id Operation Name Rows Cost (%CPU) Time ------------------------------------------------------------------- 0 SELECT STATEMENT 2 (100) 1 SORT AGGREGATE 1 2 TABLE ACCESS FULL GTT 100 2 (0) 00:00:01 -------------------------------------------------------------------Note----- - dynamic statistics used: dynamic sampling (level=2)18 rows selected.SQL>So I created 100 rows, optimized for that, and then when I put another 10,000 rows in, I still optimized for just 100.What you could do is to have some simple permutations of the same SQL to cater (say) 4 orders of magnitude. A table alias is sufficient to form a new SQL, egSQL>SQL> create global temporary table gtt on commit preserve rows 2 as select * from dba_objects 3 where 1=0;Table created.SQL>SQL> insert into gtt 2 select * from dba_objects 3 where rownum commit;Commit complete.SQL>SQL> variable rc refcursorSQL> exec open :rc for 'select count(*) from gtt g_100_or_less';PL/SQL procedure successfully completed.SQL> print rc COUNT(*)---------- 1001 row selected.SQL> select * from table(dbms_xplan.display_cursor());PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------------------------------------SQL_ID grk7zx21v16c9, child number 0-------------------------------------select count(*) from gtt g_100_or_lessPlan hash value: 3344941513------------------------------------------------------------------- Id Operation Name Rows Cost (%CPU) Time ------------------------------------------------------------------- 0 SELECT STATEMENT 2 (100) 1 SORT AGGREGATE 1 2 TABLE ACCESS FULL GTT 100 2 (0) 00:00:01 -------------------------------------------------------------------Note----- - dynamic statistics used: dynamic sampling (level=2)18 rows selected.SQL>SQL> insert into gtt 2 select * from dba_objects 3 where rownum SQL> variable rc refcursorSQL> exec open :rc for 'select count(*) from gtt g_1000_or_less';PL/SQL procedure successfully completed.SQL> print rc COUNT(*)---------- 9001 row selected.SQL> select * from table(dbms_xplan.display_cursor());PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------------------------------------SQL_ID c4yxah6ngtjf9, child number 0-------------------------------------select count(*) from gtt g_1000_or_lessPlan hash value: 3344941513------------------------------------------------------------------- Id Operation Name Rows Cost (%CPU) Time ------------------------------------------------------------------- 0 SELECT STATEMENT 4 (100) 1 SORT AGGREGATE 1 2 TABLE ACCESS FULL GTT 900 4 (0) 00:00:01 -------------------------------------------------------------------Note----- - dynamic statistics used: dynamic sampling (level=2)18 rows selected.SQL>SQL> insert into gtt 2 select * from dba_objects 3 where rownum SQL> variable rc refcursorSQL> exec open :rc for 'select count(*) from gtt g_10k_or_less';PL/SQL procedure successfully completed.SQL> print rc COUNT(*)---------- 89001 row selected.SQL> select * from table(dbms_xplan.display_cursor());PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------------------------------------SQL_ID 1p4u746xy4kta, child number 0-------------------------------------select count(*) from gtt g_10k_or_lessPlan hash value: 3344941513------------------------------------------------------------------- Id Operation Name Rows Cost (%CPU) Time ------------------------------------------------------------------- 0 SELECT STATEMENT 25 (100) 1 SORT AGGREGATE 1 2 TABLE ACCESS FULL GTT 8242 25 (0) 00:00:01 -------------------------------------------------------------------Note----- - dynamic statistics used: dynamic sampling (level=2)18 rows selected.SQL>SQL>SQL>So assuming you have control over the *insertion* code, the logic would look along the lines of:insert ...if sql%rowcount In that way, you have some reasonably accurate stats data without literal SQL polluting your cache. Depending on volume of executions you could adjust the number of permutations to best suit your needs. Rating (2 ratings)Is this answer out of date? If it is, please let us know via a Comment Comments Comment 12c: Session private statistics for global temporary tablesDuke Ganote, January 18, 2017 - 5:55 pm UTC
Disadvantages Of Global Temporary Table In Oracle
There are two types of temporary tables: local and global. Local temporary tables are visible only to their creators during the same connection to an instance of SQL Server as when the tables were first created or referenced. Local temporary tables are deleted after the user disconnects from the instance of SQL Server. Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.
Local temp tables are only available to the SQL Server session or connection (means single user) that created the tables. These are automatically deleted when the session that created the tables has been closed. Local temporary table name is stared with single hash ("#") sign.
2.) A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection is closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.
Global temp tables are available to all SQL Server sessions or connections (means all the user). These can be created by any SQL Server connection user and these are automatically deleted when all the SQL Server connections have been closed. Global temporary table name is stared with double hash ("##") sign.
Allows setting the auto-drop functionality for global temporary tables. The default is ON, which means that the global temporary tables are automatically dropped when not in use by any session. When set to OFF, global temporary tables need to be explicitly dropped using a DROP TABLE statement or will be automatically dropped on server restart. 2ff7e9595c
Comments