In another blog posting, I asserted that statistics collected by one session on a Global Temporary table (GTT) would be used by other sessions that reference that table, even though each session has their own physical instance of the table. I thought I should demonstrate that behaviour, so here is a simple test.
We will need two database sessions. I will create a test Global Temporary table with a unique index.
In my first session, I’ll populate the table with 100 rows. The values in column A have the range 1 to 100.
And I’ll collect statistics on it.
The following settings are just to make the queries easy to read, and so I can use dbms_xplan to generate a plan.
So now let’s check the contents of the table. There are 100 rows in the range 1 to 100.
And the statistics agree with this.
So now let’s try a test query. The database returns 42 rows using a full scan. The statistics in the execution plan1 also predict that there will be 42 rows. Perfectly reasonable.
Now, let’s start a second session, and insert some slightly different data into the same GTT. There are still 100 rows, but this time column A is in the range 43 to 142.
I’ll collect statistics in the same way. First we will check that the data in the table is correct.
And I can also see that the statistics have changed.
If I run the same query, it correctly returns no rows, and uses an index scan to so. The statistics predict one row, but Oracle actually doesn’t find any. Again perfectly reasonable.
But now let’s go back to the first session and run the query again (having flushed the shared pool). Last time we ran it we got 42 rows with a full scan. We still get 42 rows, but now it is using the index range scan, the same execution plan as the other session. In fact, the costs in the execution plan are the same as in the other session. Oracle expected 1 row from the index, but this time it actually got 42. So changing the statistics in the other session has changed the plan in this session, and possibly not for the better. The two sessions are using the same execution plan for different sets of data.
If I now delete stats, I will use optimizer dynamic sampling.
And the plan changes back to a full scan.
(correct version reposted 1.11.2009)
Different instances of the same GTT do share statistics because there is only one location in the data dictionary to store statistics for each table. Therefore, collecting statistics on a GTT in one session will affect other sessions using the same table name, possibly adversely!
(Added 1.11.2009) Optimizer Dynamic Sampling may be a better option for GTTs, and is enabled by default from Oracle 9i, but the behaviour changes slightly in 10g. However, as Cokan points out in his comments below, if a query on a GTT from one session is still in the shared pool when it is used in a different session, then Oracle will not re-parse the statement, and will not choose a different execution plan.
Footnote 1: The execution plans in this posting have been obtained using dbms_xplan thus:
I have also flushed the shared pool between each statement; otherwise the numbers are aggregated across multiple executions of the same statement.