I was excited to learn that since 12c we can have session level statistics on global temporary tables. Excellent. However when trying this out, I found there was an implicit commit happening - which wasn't so excellent. So much so - that I raised an idea here (
https://community.oracle.com/ideas/24889 ) to not do that.
However - I have since found that this implicit commit
only happens when the GTT is using
on commit preserve rows. Unfortunately I need this to happen for other reasons.
create global temporary table JKTST(text varchar2(20));
insert into JKTST values('A row');
exec dbms_stats.gather_table_stats(user,'JKTST');
select count(*) from dba_tab_statistics where table_name = 'JKTST' and scope='SHARED';
rollback;
select * from JKTST;
In the above example, that last select returns no rows - due to the rollback. However if we now create the table like this with
on commit preserve rows:
create global temporary table JKTST(text varchar2(20)) on commit preserve rows;
insert into JKTST values('A row');
exec dbms_stats.gather_table_stats(user,'JKTST');
select count(*) from dba_tab_statistics where table_name = 'JKTST' and scope='SHARED';
rollback;
select * from JKTST;
That last select
returns the row. Why does
on commit preserve rows force an implicit commit in gather stats?
Is this a feature or a bug??On a related topic - does gathering session stats force a subsequent hard-parse every time? I.e. if I gather session stats on a temporary table prior to running a select statement on it, will a hard-parse be done each time I that (gather->execute query) (presuming those stats have changed since the last run)?
Thanks,
John
I spoke to the Optimizer PM on this one. It is "correct behaviour", the background being:
Up until 12c, every dbms_stats gather call does a commit. That has always been the case. Even with a GTT, because a gather stats is *still* making the those stats available to *all* sessions. But fast forward to the advent of *session* level statistics for GTT's, and now we have the capacity to make those stats available only to the session that populated the table.
So in the code for dbms_stats, there is a routine called (in essence): "should_i_commit?" and here's a cut from the comment section:
-- DESCRIPTION:
-- This procedure will decide whether the target table is a "on
-- commit delete rows" GTT with session private stats. If yes,
-- we do not commit. Otherwise we commit.