Should we gather stats on Global Temporary Table within any Stats Job?

Global Temporary table(GTT) are meant to store data specific to a session and it only persist’s till session lifetime.

Most of Stats gathering job, also gather underlying stats on global temp table.
Pre Oracle 12c, Underlying gathered statistics on Global temp table is “SHARED”.
Stats gathered are stored and shared across all session for GTT.

Sharing Stats has it’s own impact as each session would have different data but underlying stats won’t take any advantage of same.

Suppose we gather statistics on underlying GTT as part of Schedule stats gathering job, we might end up with below situations.

Whenever we gather stats with no data in GTT, we have below metrics.

Create Global Temporary Table Gtt (A Int) on commit PRESERVE rows;

exec dbms_stats.GATHER_TABLE_STATS (ownname => user, tabname => 'GTT') ;

TABLE_NAME NUM_ROWS AVG_ROW_LEN
---------- --------- -----------
GTT 0 0

Stats gathering mark Num_Rows as “0”, as while stats gathering no data will be present on GTT.

Now let walk through some example to understand impact of same.

Session 1 :
Insert Into Gtt (Select Rownum From Dual Connect By Rownum < 1001);
commit;

explain plan for
select count(*) from Gtt;

Select * From Table(dbms_xplan.display);

Plan hash value: 3344941513

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| GTT | 1 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------

Check at ID :: 2 , Though we have inserted 1000 rows, but still Cardinality estimate is close to “0”.
Optimiser won’t estimate cardnality as Zero, it will always display it as 1.

If we use GTT with some other transactional table, GTT estimate cardinality might impact join method used and ultimately might result in Bad Execution plan.

For each session, optimizer will estimate GTT cardinality as Zero.

Very important point is why “Dynamic Sampling” didn’t come into picture.

NAME VALUE
---------------------------- ------
optimizer_dynamic_sampling 2

On most of Database system, we have default setup for dynamic sampling.
It will only trigger when we won’t have any stats associated with any Table/GTT.

Whenever we have any Stats Associated with Table, though it might be NUM_ROWS := 0.
Dynamic sampling will not get triggered.

For Dynamic sampling, we should not gather underlying stats for GTT pre Oracle 12c.
Now lets delete underlying stats for our GTT and lets Dynamic sampling plays it role.

Session 1
BEGIN
dbms_stats.delete_table_stats(user, tabname=>'GTT');
End;
/

Select Table_Name , Num_Rows, Avg_Row_Len From Dba_Tables Where Table_Name Like 'GTT';

TABLE_NAME NUM_ROWS AVG_ROW_LEN
---------- ---------- -----------
GTT NULL NULL

Insert Into Gtt (Select Rownum From Dual Connect By Rownum < 1001);
Commit;

explain plan for
select count(*) from Gtt;

Select * From Table(dbms_xplan.display);

Plan hash value: 3344941513

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| GTT | 1000 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)

After Deleting stats for GTT, Dynamic sampling got triggered and estimate cardinality(1000) is near to actual rows.
For Each Session and for varying data within GTT, Dynamic sampling will get triggered and help to get estimated cardinality to real number.

Session 2 :
Insert Into Gtt (Select Rownum From Dual Connect By Rownum < 50);

explain plan for
select count(*) from Gtt;

Select * From Table(dbms_xplan.display);

Plan hash value: 3344941513

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| GTT | 49 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)

Pre Oracle 12c, it is always wise to not gather stats on GTT and let Dynamic sampling helps us to get estimated cardinality with in each session.
With Oracle 12c , we have Session specific stats for GTT.

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Oracle Internal and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s