Speed Oracle SQL with Temporary Tables (GTT)

The effective use of temporary tables while doing huge batch process can really boost performance. The idea is to store intermediate data while performing huge processing in temporary tables.The data in temporary table is stored in-memory and improve the speed of SQL queries for complex summarization activities.

The other advantage is that you can create index and also check for integrity (primary key, not null etc..) of data in temp tables. GTT’s are the closest you can get to materialized view with data in memory.

Now, the same can be achieved by using PL/SQL tables, but for huge data volume the use of GTT is the right choice. Currently, there is a bug in Oracle 11g because of which the optimizer is not able to establish the cardinality of data in PL/SQL tables. With GTT, the cardinality can be achieved using dynamic sampling.

select /*+ dynamic_sampling (gtt 4) */ column1 from transaction_gtt gtt

Oracle 12c Note: Session-specific statistics for global temporary tables

Oracle will allows private optimizer statistics for global temporary tables, allowing you to invoke session-level dbms_stats to gather statistics specific to your own global temporary table.  Prior to 12c, statistics were shared from a master copy of the CBO statistics.

Example (Code credit : Don Burleson)                         

Here is an actual performance comparison of equivalent queries:

SQL> --*********************************************
SQL> -- Using subqueries
SQL> --*********************************************
SQL>
SQL> select
2 store_name,
3 sum(quantity) store_sales,
4 (select sum(quantity) from sales)/(select count(*) from store) avg_sales
5 from
6 store s,
7 sales sl
8 where
9 s.store_key = sl.store_key
10 having
11 sum(quantity) > (select sum(quantity) from sales)/(select count(*) from store)
12 group by
13 store_name
14 ;
| Id  | Operation                      | Name        | Rows  | Bytes | Cost (%CPU)| Time     |                         
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 31 | 4 (25)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
| 2 | TABLE ACCESS FULL | SALES | 100 | 400 | 2 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | | | |
| 4 | INDEX FULL SCAN | SYS_C003999 | 10 | | 1 (0)| 00:00:01 |
|* 5 | FILTER | | | | | |
| 6 | HASH GROUP BY | | 1 | 31 | 4 (25)| 00:00:01 |
| 7 | NESTED LOOPS | | 100 | 3100 | 3 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | SALES | 100 | 900 | 2 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID| STORE | 1 | 22 | 1 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | SYS_C003999 | 1 | | 0 (0)| 00:00:01 |
| 11 | SORT AGGREGATE | | 1 | 4 | | |
| 12 | TABLE ACCESS FULL | SALES | 100 | 400 | 2 (0)| 00:00:01 |
| 13 | SORT AGGREGATE | | 1 | | | |
| 14 | INDEX FULL SCAN | SYS_C003999 | 10 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

113  consistent gets - What is Consistent gets(explanation below) ?    

SQL> --*********************************************
SQL> -- Using GTT or CTAS
SQL> --*********************************************

SQL> create global temporary table t1 as select sum(quantity) all_sales from sales;

Table created.

SQL> create global temporary table t2 as select count(*) nbr_stores from store;

Table created.

SQL> create table t3 as select store_name, sum(quantity) store_sales from store natural join sales group by store_name;

Table created.

SQL>
SQL> select
2 store_name
3 from
4 t1,
5 t2,
6 t3
7 where
8 store_sales > (all_sales / nbr_stores);


------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 61 | 6 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 61 | 6 (0)| 00:00:01 |
| 2 | MERGE JOIN CARTESIAN| | 1 | 26 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T1 | 1 | 13 | 2 (0)| 00:00:01 |
| 4 | BUFFER SORT | | 1 | 13 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T2 | 1 | 13 | 2 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | T3 | 1 | 35 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------

  30  consistent gets  

Caution

The key point to remember about GTT is that it’s session specific. If we stretch the role of GTT’s beyond that boundary, it will lead to potential issues.

Few explain plan terminologies explained below -

Consistent Gets – a normal reading of a block from the buffer cache. A check will be made if the data needs reconstructing from rollback info to give you a consistent view but most of the time it won’t.

To put it in a different way, a Consistent Get is where oracle returns a block from the block buffer cache but has to take into account checking to make sure it is the block current at the time the query started.

Now, with consistent gets Oracle wants to make sure you are getting only those data that has been committed at the very point in time your query started.

DB Block Gets – Internal processing. Don’t worry about them unless you are interested in Oracle Internals and have a lot of time to spend on it.

Physical Reads – Where Oracle has to get a block from the IO subsystem

From a performance perspective:-

Reducing Physical IO {if there is any} is very often a very good way to make a statement run a lot faster.

Reducing Consistent Gets is often a good way to make a statement run faster.

Reducing DB Block Gets is probably beyond what you can do (except as a side effect of changes to the execution plan). It won’t make a lot of difference.

References

What are consistent gets  (June 2009). Retrieved Oct 01, 2013, from http://mwidlake.wordpress.com/

 
Comment