Database Reference
In-Depth Information
Next, insert some data:
EODA@ORA12CR1> insert into gt select user_id from all_users;
51 rows created.
Now generate statistics for the table:
EODA@ORA12CR1> exec dbms_stats.gather_table_stats( user, 'GT' );
PL/SQL procedure successfully completed.
We can verify the existence of session-level statistics by querying
USER_TAB_STATISTICS
:
EODA@ORA12CR1> select table_name, num_rows, last_analyzed, scope
2 from user_tab_statistics
3 where table_name like 'GT';
TABLE_NAME NUM_ROWS LAST_ANALYZED SCOPE
------------------------- ---------- -------------- -------
GT SHARED
GT 51 18-JAN-14 SESSION
We can further verify the optimizer's awareness of session private statistics via autotrace:
EODA@ORA12CR1> set autotrace on;
EODA@ORA12CR1> select count(*) from gt;
Near the bottom of the output is this optimizer note:
Note
-----
- Global temporary table session private statistics used
Keep in mind that session-level statistics are only valid for the duration of the session. If you disconnect and
reconnect, the statistics are gone:
EODA@ORA12CR1> disconnect
EODA@ORA12CR1> connect eoda
Enter password:
Rerunning the query showing the existence of statistics shows that no session statistics exist now:
EODA@ORA12CR1> select table_name, num_rows, last_analyzed, scope
2 from user_tab_statistics
3 where table_name like 'GT';
TABLE_NAME NUM_ROWS LAST_ANALYZED SCOPE
-------------------- ---------- -------------- -------
GT SHARED
