Database Reference
In-Depth Information
EODA@ORA11GR2> insert into gtt select * from scott.emp;
14 rows created.
EODA@ORA11GR2> set autotrace traceonly explain
EODA@ORA11GR2> select * from gtt;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1218 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| GTT | 14 | 1218 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
EODA@ORA11GR2> set autotrace off
We get the right cardinality without having to ask for it. Dynamic sampling does not come free, however—there
is a cost associated with having to perform it at query parse time. If we gathered appropriate representative statistics
ahead of time, we could avoid this at hard parse time. That leads us into
DBMS_STATS
.
There are three methods to use
DBMS_STATS
to gather representative statistics. The first way is to use
DBMS_STATS
with the
GATHER_SCHEMA_STATS
, or
GATHER_DATABASE_STATS
call. These procedures allow you to pass in a parameter,
GATHER_TEMP
, which is a Boolean and defaults to
FALSE
. When set to
TRUE
, any
ON COMMIT PRESERVE ROWS
global
temporary table will have statistics gathered and stored (this technique will
not
work on
ON COMMIT DELETE ROWS
tables). Consider the following (note that this was done in an empty schema; the only objects are those you see
created):
EODA@ORA11GR2> create table emp as select * from scott.emp;
Table created.
EODA@ORA11GR2> create global temporary table gtt1 ( x number )
2 on commit preserve rows;
Table created.
EODA@ORA11GR2> create global temporary table gtt2 ( x number )
2 on commit delete rows;
Table created.
EODA@ORA11GR2> insert into gtt1 select user_id from all_users;
49 rows created.
EODA@ORA11GR2> insert into gtt2 select user_id from all_users;
49 rows created.
EODA@ORA11GR2> exec dbms_stats.gather_schema_stats( user );
PL/SQL procedure successfully completed.
