Database Reference
In-Depth Information
EODA@ORA11GR2> select table_name, last_analyzed, num_rows from user_tables;
TABLE_NAME LAST_ANALYZED NUM_ROWS
------------------------------ -------------- ----------
EMP 17-JAN-14 14
GTT2
GTT1
As you can see, only the
EMP
table was analyzed in this case; the two global temporary tables were ignored.
We can change that behavior by calling
GATHER_SCHEMA_STATS
with
GATHER_TEMP => TRUE
:
EODA@ORA11GR2> insert into gtt2 select user_id from all_users;
49 rows created.
EODA@ORA11GR2> exec dbms_stats.gather_schema_stats( user, gather_temp=>TRUE );
PL/SQL procedure successfully completed.
EODA@ORA11GR2> select table_name, last_analyzed, num_rows from user_tables;
TABLE_NAME LAST_ANALYZED NUM_ROWS
------------------------------ -------------- ----------
EMP 17-JAN-14 14
GTT1 17-JAN-14 49
GTT2 17-JAN-14 0
Notice that the
ON COMMIT PRESERVE
rows table has accurate statistics, but the
ON COMMIT DELETE ROWS
does not.
DBMS_STATS
commits and that wipes out any information in that table. Do note, however, that
GTT2
does now have
statistics, which in itself is
a bad thing
, because the statistics are very much incorrect! It is doubtful the table will have
0 rows in it at runtime. So, if you use this approach, be aware of two things:
•
Make sure to populate your global temporary tables with representative data
in the session that
gathers the statistics
. If not, they will appear empty to
DBMS_STATS
.
If you have
•
ON COMMIT DELETE ROWS
global temporary tables, this approach should not be
used, as you will definitely gather inappropriate values.
The second technique that works with
ON COMMIT PRESERVE ROWS
global temporary tables is to use
GATHER_TABLE_STATS
directly on the table. You would populate the global temporary table as we just did, and
then execute
GATHER_TABLE_STATS
on that global temporary table. Note that just as before, this
does not work
for
ON COMMIT DELETE ROWS
global temporary tables, as the same issues as just described would come into play.
The last technique using
DBMS_STATS
uses a manual process to populate the data dictionary with representative
statistics for our temporary tables. For example, if on average the number of rows in the temporary table will be 500,
the average row size will be 100 bytes, and the number of blocks will be 7, we could simply use the following:
EODA@ORA11GR2> create global temporary table t ( x int, y varchar2(100) )
2 on commit preserve rows;
Table created.
