Prior to Oracle 12c the database don’t maintain separate statistics
for global temporary table (GTT), the database maintains one version of
statistics shared by all session, even though the data across sessions could differ.
Starting with 12c we can set the table-level preference
GLOBAL_TEMP_TABLE_STATS to either shared or session-specific (global preference
default GLOBAL_TEMP_TABLE_STATS to SESSION). Users can gather statistics on GTT
and can have own version of session statistics. During optimization the
optimizer first check if session statistics exists if yes, then make use of
them. If not optimizer uses Shared statistics if they exist.
rajesh@ORA12C> select
dbms_stats.get_prefs('GLOBAL_TEMP_TABLE_STATS')
2 from dual ;
DBMS_STATS.GET_PREFS('GLOBAL_TEMP_TABLE_STATS')
------------------------------------------------------------------------
SESSION
1 row selected.
rajesh@ORA12C> create global temporary table t(x int);
Table created.
rajesh@ORA12C> insert into t(x) values(0);
1 row created.
rajesh@ORA12C> select num_rows,blocks,last_analyzed,scope
2 from user_tab_statistics
3 where table_name ='T';
NUM_ROWS BLOCKS LAST_ANALYZ SCOPE
---------- ---------- ----------- -------
SHARED
1 row selected.
rajesh@ORA12C> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.
rajesh@ORA12C> select num_rows,blocks,last_analyzed,scope
2 from user_tab_statistics
3 where table_name ='T';
NUM_ROWS BLOCKS LAST_ANALYZ SCOPE
---------- ---------- ----------- -------
SHARED
1 1 11-FEB-2016 SESSION
2 rows selected.
We
have session level statistics in place, the optimizer now uses this session
statistics for optimal execution.
rajesh@ORA12C> select count(data_object_id)
2 from big_table
3 where id in (select x from t) ;
COUNT(DATA_OBJECT_ID)
---------------------
0
1 row selected.
rajesh@ORA12C> @xplan_cursor
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 3zwrtp37uvz2b,
child number 0
-------------------------------------
select count(data_object_id) from big_table where id in (select
x from
t)
Plan hash value: 3252046534
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | | |
| 5 (100)|
| 1 | SORT AGGREGATE | | 1 |
9 | |
| 2 | NESTED LOOPS | | 1 |
9 | 5 (20)|
| 3 | NESTED LOOPS | | 1 |
9 | 5 (20)|
| 4 | SORT UNIQUE | | 1 |
2 | 2 (0)|
| 5 | TABLE ACCESS FULL | T |
1 | 2 | 2
(0)|
|* 6 | INDEX RANGE SCAN | BIG_IDX_01 | 1 |
| 2 (0)|
| 7 | TABLE ACCESS BY INDEX ROWID| BIG_TABLE | 1
| 7 | 2
(0)|
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 -
access("ID"="X")
Note
-----
- this is an adaptive
plan
- Global temporary table session private statistics used
30 rows selected.
The
notes section from the plan, shows that it used session statistics for
optimization. When loaded with few more
data and did stats gathering, the existing cursor for this session from shared
pool got re-loaded with new set of session statistics.
rajesh@ORA12C>
rajesh@ORA12C> insert into t(x)
2 select rownum
3 from all_objects
4 where rownum <=1000;
1000 rows created.
rajesh@ORA12C> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.
rajesh@ORA12C> select num_rows,blocks,last_analyzed,scope
2 from user_tab_statistics
3 where table_name ='T';
NUM_ROWS BLOCKS LAST_ANAL SCOPE
---------- ---------- --------- -------
SHARED
1001 2
11-feb-16 SESSION
2 rows selected.
rajesh@ORA12C> set autotrace traceonly explain
rajesh@ORA12C> select *
2 from big_table
3 where id in (select x from t) ;
Execution Plan
----------------------------------------------------------
Plan hash value: 2358921057
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
1001 | 108K| 1506
(1)|
| 1 | NESTED LOOPS | |
1001 | 108K| 1506
(1)|
| 2 | NESTED LOOPS | |
1001 | 108K| 1506
(1)|
| 3 | SORT UNIQUE | |
1001 | 4004 | 2
(0)|
| 4 | TABLE ACCESS
FULL | T |
1001 | 4004 | 2
(0)|
|* 5 | INDEX RANGE SCAN | BIG_IDX_01 | 1 |
| 2 (0)|
| 6 | TABLE ACCESS BY INDEX ROWID| BIG_TABLE | 1
| 107 | 3
(0)|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 -
access("ID"="X")
Note
-----
- this is an adaptive
plan
- Global temporary
table session private statistics used
rajesh@ORA12C> set autotrace off
rajesh@ORA12C>
Now
from a different session tried this.
rajesh@ORA12C> select num_rows,blocks,last_analyzed,scope
2 from user_tab_statistics
3 where table_name ='T';
NUM_ROWS BLOCKS LAST_ANALYZ SCOPE
---------- ---------- ----------- -------
SHARED
1 row selected.
rajesh@ORA12C> set serveroutput off
rajesh@ORA12C> select count(data_object_id)
2 from big_table
3 where id in (select x from t) ;
COUNT(DATA_OBJECT_ID)
---------------------
0
1 row selected.
rajesh@ORA12C> @xplan_cursor
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
SQL_ID 3zwrtp37uvz2b,
child number 0
-------------------------------------
select count(data_object_id) from big_table where id in (select
x from
t)
Plan hash value: 3252046534
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT |
| | |
6 (100)|
| 1 | SORT AGGREGATE | | 1 |
20 | |
| 2 | NESTED LOOPS | | 1 |
20 | 6 (17)|
| 3 | NESTED LOOPS | | 1 |
20 | 6 (17)|
| 4 | SORT UNIQUE | | 1 |
13 | 2 (0)|
| 5 | TABLE ACCESS FULL | T |
1 | 13 | 2
(0)|
|* 6 | INDEX RANGE SCAN | BIG_IDX_01 | 1 |
| 2 (0)|
| 7 | TABLE ACCESS BY INDEX ROWID| BIG_TABLE | 1
| 7 | 3
(0)|
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 -
access("ID"="X")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- this is an adaptive
plan
30 rows selected.
Since
no session-specific and shared statistics Dynamic sampling kicked in, with shared
statistics in place, optimizer make use of shared statistics.
rajesh@ORA12C>
rajesh@ORA12C>
rajesh@ORA12C> begin
2 dbms_stats.set_table_prefs(user,'T',
3 'GLOBAL_TEMP_TABLE_STATS','SHARED');
4 dbms_stats.set_table_stats(user,'T',
5 numrows=>5000,
6 numblks=>500);
7 end;
8 /
PL/SQL procedure successfully completed.
rajesh@ORA12C> select num_rows,blocks,last_analyzed,scope
2 from user_tab_statistics
3 where table_name ='T';
NUM_ROWS BLOCKS LAST_ANALYZ SCOPE
---------- ---------- ----------- -------
5000 500 11-FEB-2016 SHARED
1 row selected.
rajesh@ORA12C> begin
2 dbms_stats.set_table_prefs(user,'T',
3
'GLOBAL_TEMP_TABLE_STATS','SESSION');
4 end;
5 /
PL/SQL procedure successfully completed.
rajesh@ORA12C> select num_rows,blocks,last_analyzed,scope
2 from user_tab_statistics
3 where table_name ='T';
NUM_ROWS BLOCKS LAST_ANALYZ SCOPE
---------- ---------- ----------- -------
5000 500 11-FEB-2016 SHARED
1 row selected.
rajesh@ORA12C> select count(data_object_id)
2 from big_table
3 where id in (select x from t) ;
COUNT(DATA_OBJECT_ID)
---------------------
0
1 row selected.
rajesh@ORA12C>
rajesh@ORA12C> @xplan_cursor
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
SQL_ID 3zwrtp37uvz2b, child
number 0
-------------------------------------
select count(data_object_id) from big_table where id in (select
x from
t)
Plan hash value: 3252046534
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | | |
| 606 (100)|
| 1 | SORT AGGREGATE | | 1 |
20 | |
| 2 | NESTED LOOPS | |
156 | 3120 | 606
(1)|
| 3 | NESTED LOOPS | |
156 | 3120 | 606
(1)|
| 4 | SORT UNIQUE | |
5000 | 65000 | 137 (0)|
| 5 | TABLE ACCESS FULL | T |
5000 | 65000 | 137 (0)|
|* 6 | INDEX RANGE SCAN | BIG_IDX_01 | 1 |
| 2 (0)|
| 7 | TABLE ACCESS BY INDEX ROWID| BIG_TABLE | 1
| 7 | 3
(0)|
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 -
access("ID"="X")
Note
-----
- this is an
adaptive plan
29 rows selected.
See,
no indication of “session private statistics” in notes section. With Session-specific and shared statistics in
place, session statistics take more preference for optimization.
rajesh@ORA12C> insert into t(x)
2 select rownum
3 from all_objects;
89984 rows created.
rajesh@ORA12C>
rajesh@ORA12C> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.
rajesh@ORA12C> select num_rows,blocks,last_analyzed,scope
2 from user_tab_statistics
3 where table_name ='T';
NUM_ROWS BLOCKS LAST_ANALYZ SCOPE
---------- ---------- ----------- -------
5000 500 11-FEB-2016 SHARED
89984 137 11-FEB-2016 SESSION
2 rows selected.
rajesh@ORA12C> select count(data_object_id)
2 from big_table
3 where id in (select x from t) ;
COUNT(DATA_OBJECT_ID)
---------------------
6534
1 row selected.
rajesh@ORA12C>
rajesh@ORA12C> @xplan_cursor
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 3zwrtp37uvz2b,
child number 0
-------------------------------------
select count(data_object_id) from big_table where id in (select
x from
t)
Plan hash value: 2375446597
-----------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)|
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| | |
4675 (100)|
| 1 | SORT AGGREGATE | |
1 | 12 | |
|* 2 | HASH JOIN | | 89984 | 1054K|
4675 (1)|
| 3 | SORT UNIQUE | | 89984 | 439K|
39 (0)|
| 4 | TABLE ACCESS FULL|
T | 89984 | 439K|
39 (0)|
| 5 | TABLE ACCESS FULL | BIG_TABLE | 1000K|
6835K| 4355 (1)|
-----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -
access("ID"="X")
Note
-----
- this is an adaptive
plan
- Global temporary table session private statistics used
28 rows selected.
rajesh@ORA12C>
It 's an amazing article and useful for developers
ReplyDeleteOracle SOA Online Training