Skip to Main Content
  • Questions
  • Can Oracle automatically gather session specific stats for a GTT?

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Achal.

Asked: February 16, 2018 - 3:01 pm UTC

Last updated: February 22, 2018 - 1:43 am UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

Hi Team,

First of all, Thanks a lot for all the great work you are doing! It would be great if you can help me with a query.

While reviewing AWRSQL report of a SQL I noticed that session specific stats were used for that execution. This SQL is an update statement and involves only a single GTT.

We don't explicitly gather stats in our code. It makes wonder if Oracle itself gathered session specific stats before execution? If so, then is there is a way to make Oracle do so more often? Except for this instance, for us Oracle automatically goes for dynamic sampling. It is first time I have seen that Oracle has automatically used Session Specific Stats.

The GTT is created to preserve rows on commit and GLOBAL_TEMP_TABLE_STATS is left with default value. Flow of program on a high-level is:

1. Insert data into a GTT by selecting from other regular and GTT tables. Number of rows inserted in this step varies from 6000 rows to 3 Million rows.
2. Update the inserted data using an Update statement that involves only the GTT. This update also uses some aggregation functions.

I tried but was not able to replicate this in a test environment. I think a good option would be to modify process to gather session specific stats after Step# 1. However, we don't have authority to edit the code.

Thanks for help.

and Connor said...

If the data is being loaded with direct load, then the standard collection of stats on load is performed like a normal table, eg

SQL> create global temporary table T ( x int )
  2  on commit preserve rows;

Table created.

SQL> insert /*+ append */ into t
  2  select rownum
  3  from dual
  4  connect by level <= 1000;

1000 rows created.

SQL>
SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
SQL_ID  1bshr3t6jtv86, child number 0
-------------------------------------
insert /*+ append */ into t select rownum from dual connect by level <=
1000

Plan hash value: 1600317434

---------------------------------------------------------------------------------
| Id  | Operation                        | Name | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                 |      |       |     2 (100)|          |
|   1 |  LOAD AS SELECT                  | T    |       |            |          |
|   2 |   OPTIMIZER STATISTICS GATHERING |      |     1 |     2   (0)| 00:00:01 |  <<======
|   3 |    COUNT                         |      |       |            |          |
|   4 |     CONNECT BY WITHOUT FILTERING |      |       |            |          |
|   5 |      FAST DUAL                   |      |     1 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------



whereas a conventional load of rows will not

SQL> create global temporary table T ( x int )
  2  on commit preserve rows;

Table created.

SQL>
SQL> insert into t
  2  select rownum
  3  from dual
  4  connect by level <= 1000;

1000 rows created.

SQL>
SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
SQL_ID  2th1j5m01vax7, child number 0
-------------------------------------
insert into t select rownum from dual connect by level <= 1000

Plan hash value: 1731520519

-------------------------------------------------------------------------------
| Id  | Operation                      | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | INSERT STATEMENT               |      |       |     2 (100)|          |
|   1 |  LOAD TABLE CONVENTIONAL       | T    |       |            |          |
|   2 |   COUNT                        |      |       |            |          |
|   3 |    CONNECT BY WITHOUT FILTERING|      |       |            |          |
|   4 |     FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------


which could explain why you are getting statistics even without an explicit dbms_stats call.

Unfortunately, you'll need to intercept the code with your own dbms_stats calls if you want to perform more frequent statistics gathering. The alternative would be to disable session stats and rely exclusively on dynamic sampling.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Achal Bansal, February 20, 2018 - 7:24 am UTC

Thank you so much for the reply! The Update statement is not a direct path operation. Execution plan also don't has any step to gather stats.

Only thing that suggests stats gathering is used is following note at bottom of the plan:

"Global temporary table session private statistics used"

Any other thing that could have triggered Session private stats?

Also regarding following:

If the data is being loaded with direct load, then the standard collection of stats on load is performed like a normal table, eg

It means that regular stats (not session specific stats) would be gathered and value of GLOBAL_TEMP_TABLE_STATS parameter will not matter?

Connor McDonald
February 22, 2018 - 1:43 am UTC

In 12c, the *default* for global temp tables is session-specific stats.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.