12c dbms_stats.gather_table_stats on GTT do not commit

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.
&nbsp:
SQL> select * from DEMOGTT;
no rows selected
&nbsp:
SQL> rollback;
Rollback complete.
&nbsp:
SQL> select * from DEMO;
&nbsp:
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.
&nbsp:
SQL> select * from DEMOGTT;
no rows selected
&nbsp:
SQL> rollback;
Rollback complete.
&nbsp:
SQL> select * from DEMO;
&nbsp:
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.

 

Cet article 12c dbms_stats.gather_table_stats on GTT do not commit est apparu en premier sur Blog dbi services.