By Franck Pachot
.
In my UKOUG OracleScene article on 12c online statistics and GTT I mentioned the following:
A final note about those 12c changes in statistics gathering on GTT. In 11g the dbms_stats did a commit at the start. So if you did gather stats after the load, you had to set the GTT as ON COMMIT PRESERVE ROWS. Or you just vacuum what you’ve loaded. That has changed in 12c. If you now choose to do a conventional insert followed by dbms_stats (having set private stats of course) then you don’t need to set on commit preserve rows anymore.
Today, I realized that I’ve never explained exactly when dbms_stats.gather_table_stats commits the transaction or not. Because, of course, it depends. In summary: 12c non-SYS owner GTT with private statistics.
Here is an example. I connect as non-SYS user:
SQL> connect demo/demo@//localhost/pdb1
Connected.
SQL> show user
USER is "DEMO"
I create a permanent table and a global temporary table:
SQL> create table DEMO(text varchar2(20));
Table created.
SQL> create global temporary table DEMOGTT(text varchar2(20));
Table created.
In the permanent table, I insert my row. The goal is to be sure that this insert is not commited and can be rolled back at the end:
SQL> insert into DEMO values('Forget me, please!');
1 row created.
In the global temporary table I insert one row. The goal is to be sure that the row remains until the end of my transaction (on commit delete rows):
SQL> insert into DEMOGTT values('Preserve me, please!');
1 row created.
Here it is:
SQL> select * from DEMO;
TEXT
--------------------
Forget me, please!
SQL> select * from DEMOGTT;
TEXT
--------------------
Preserve me, please!
Then, I gather statistics on the GTT:
SQL> exec dbms_stats.gather_table_stats(user,'DEMOGTT');
PL/SQL procedure successfully completed.
I check that my rows in the GTT are still there, which is a proof that no commit happened:
SQL> select * from DEMOGTT;
TEXT
--------------------
Preserve me, please!
And I check that, as no commit happened, I can rollback my previous insert on the permanent table:
SQL> rollback;
Rollback complete.
SQL> select * from DEMO;
no rows selected
This is the new behavior in 12c. The same in 11g would have committed my transaction before and after the call to dbms_stats.
GTT only
Here is the same example when gathering the stats on the permanent table:
SQL> show user
USER is "DEMO"
SQL> exec dbms_stats.gather_table_stats(user,'DEMO');
PL/SQL procedure successfully completed.
 :
SQL> select * from DEMOGTT;
no rows selected
 :
SQL> rollback;
Rollback complete.
 :
SQL> select * from DEMO;
 :
TEXT
--------------------
Forget me, please!
The transaction was committed by dbms_stats here: no rows from GTT (on commit delete rows), and the insert in permanent table was commited before my rollback.
Not for SYS
When connected as SYS:
SQL> show user
USER is "SYS"
SQL> exec dbms_stats.gather_table_stats(user,'DEMOGTT');
PL/SQL procedure successfully completed.
SQL> select * from DEMOGTT;
no rows selected
SQL> rollback;
Rollback complete.
SQL> select * from DEMO;
TEXT
--------------------
Forget me, please!
The transaction was committed by dbms_stats here: when the table is owned by SYS, dbms_stats commits.
I mean, not for SYS owner
If I’m connected by SYS but gather stats on a non-SYS table, dbms_stats do not commit:
SQL> show user
USER is "SYS"
SQL> exec dbms_stats.gather_table_stats('DEMO','DEMOGTT');
PL/SQL procedure successfully completed.
SQL> select * from DEMOGTT;
TEXT
--------------------
Preserve me, please!
SQL> rollback;
Rollback complete.
SQL> select * from DEMO;
no rows selected
The behaviour is not related to the user who runs dbms_stats, but the owner of the GTT.
Private statistics only
The default in 12c for GTT is private statistics, visible to session only. Trying the same with shared statistics (as in 11g):
SQL> show user
USER is "DEMO"
SQL> select dbms_stats.get_prefs(ownname=>user,tabname=>'DEMO_GTT',pname=>'GLOBAL_TEMP_TABLE_STATS') from dual;
DBMS_STATS.GET_PREFS(OWNNAME=>USER,TABNAME=>'DEMO_GTT',PNAME=>'GLOBAL_TEMP_TABLE
--------------------------------------------------------------------------------
SESSION
SQL> exec dbms_stats.set_table_prefs(user,'DEMO_GTT','GLOBAL_TEMP_TABLE_STATS','SHARED' );
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(user,'DEMOGTT');
PL/SQL procedure successfully completed.
 :
SQL> select * from DEMOGTT;
no rows selected
 :
SQL> rollback;
Rollback complete.
 :
SQL> select * from DEMO;
 :
TEXT
--------------------
Forget me, please!
SQL> exec dbms_stats.set_table_prefs(user,'DEMO_GTT', 'GLOBAL_TEMP_TABLE_STATS',null);
PL/SQL procedure successfully completed.
The dbms_stats did commit my transaction here.
So what?
Private session statistics for GTT is a great feature. Use it: gather statistics after filling the GTT. And don’t worry about on commit delete rows GTT (the default) because this statistic gathering do not commit the transation.
milleniumaire
16.11.2023Hi, having recently encountered an issue with gathering stats on GTT's I came across your blog, but it doesn't provide the full picture, so didn't really help me!
What you don't mention is the difference between Transaction-specific and Session-specific GTT's. What are these? The former occurs when you create the GTT using ON COMMIT DELETE ROWS (the default clause) and the latter when using ON COMMIT PRESERVE ROWS.
When gathering stats on a GTT a commit will NOT happen only when the GTT is defined as Transaction-specific (ON COMMIT DELETE ROWS). Gathering stats on a Session-specific (ON COMMIT PRESERVE ROWS) GTT WILL ALWAYS perform an implicit commit.
This is a very important distinction between types of GTT's and is what recently caught me out as GTT's had been defined as Session-specific and so commits were occurring, DESPITE the stats being generated for the SESSION and not SHARED.
Clemens Bleile
16.11.2023Hi,
thanks for your feedback.
Before 12c there was no possibility to gather statistics on Transaction-specific GTTs (except with dynamic sampling or if the table was empty already). That's why the implicit commit of dbms_stats had to be removed to get private statistics for my session. That was not necessary with Session-specific GTTs, because the commit does not change the table data in my session.
Regards
Clemens