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

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Achal.

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

Last updated: October 10, 2024 - 12:54 pm UTC

Version: 12.1.0.2

Viewed 10K+ times! This question is

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

  (3 ratings)

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.

Automate - Global temporary table session private statistics

A reader, October 07, 2024 - 4:05 am UTC

Thanks for your great work!

Ny Database version - 19.18

I have a read-only application (external client) which loads data into GTT table (on-commit delete rows based GTT) for the search queries. Their input data load into GTT could vary from 100, 1000 to 280000. So, want to test with Session private stats.

1) If i understand right, It's the application responsibility to gather stats on the GTT after the load into GTT is done, correct?

2) Since its read-only account, We have granted SELECT/INSERT/UPDATE/DELETE on the GTT table only permission to the DB user account. Now, How should we go about granting permission for them to gather stats on GTT. table ?

Just would like to know, is there any suggested automated API calls /procedure we can build
and grant them to call the API for GTT session private statistics?


Thank you.



Chris Saxon
October 09, 2024 - 2:35 pm UTC

If you're loading data, how is it a read only app? :)

If you:

- Define the GTT as ON COMMIT PRESERVE ROWS
- Use direct path loads (append hint)
- Commit after loading data (to avoid ORA-12838)

The optimizer will gather basic stats as part of the load. See below.

Needing to commit has implications for your transactions. But as this is "read only", presumably that's not a problem.

If this method is infeasible for you, to gather stats on a table in another schema you need the ANALYZE ANY privilege. Or you can create a a wrapper procedure in the table owner's schema that calls gather stats with definer's rights. Then grant the other user access to this wrapper, e.g.:

create or replace procedure gather as 
begin 
  dbms_stats.gather_table_stats ( ownname => null, tabname => 'gtt' );
end;
/

grant execute on gather to app_user;


Online gather example:

create global temporary table gtt ( c1 int )
on commit preserve rows;

insert /*+ append */into gtt 
  select level from dual connect by level <= 10;

commit;

set serveroutput off
alter session set statistics_level = all;
select count(*) from gtt;
select * from dbms_xplan.display_cursor ( format => 'ALLSTATS LAST');

----------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |       3 |      1 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |       3 |      1 |
|   2 |   TABLE ACCESS FULL| GTT  |      1 |     10 |     10 |00:00:00.01 |       3 |      1 |
----------------------------------------------------------------------------------------------
 
Note
-----
   - Global temporary table session private statistics used

truncate table gtt;

insert /*+ append */into gtt 
  select level from dual connect by level <= 1000;

commit;

set serveroutput off
alter session set statistics_level = all;
select count(*) from gtt;
select * from dbms_xplan.display_cursor ( format => 'ALLSTATS LAST');

----------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |       4 |      2 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |       4 |      2 |
|   2 |   TABLE ACCESS FULL| GTT  |      1 |   1000 |   1000 |00:00:00.01 |       4 |      2 |
----------------------------------------------------------------------------------------------
 
Note
-----
   - Global temporary table session private statistics used


Global temporary table session private statistics

A reader, October 10, 2024 - 3:14 am UTC

Hi Chris,

Thanks for the suggestion.

We are using on-commit based GTT table. I will create wrapper stored procedure and grant access to other account for stats collection.

Thanks!


Chris Saxon
October 10, 2024 - 12:54 pm UTC

You're welcome

More to Explore

Design

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