Database Reference
In-Depth Information
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("ID"<=100 AND "ID">=10))
Note
-----
- Global temporary table session private statistics used
To control whether shared statistics or session statistics are used, you can set the global_temp_table_stats
preference. Two values are supported: shared and session . The default value is session .
Working with Pending Object Statistics
Usually, as soon as the gathering is finished, the object statistics are published (that is, made available) to the query
optimizer. This means that it's not possible (for testing purposes, for instance) to gather statistics without overwriting
the current object statistics. Of course, test databases should be used for testing purposes, but sometimes it's not
possible to do so; you might want to do it in production. An example of this is when the data stored in the test
database isn't the same as the data in the production database.
As of version 11.1, it's possible to separate gathering statistics from publishing them, and it's possible to use
objects statistics that are unpublished, which are called pending statistics , for testing purposes. Here is the procedure
(a full example is provided in the pending_object_statistics.sql script):
1.
Disable automatic publishing by setting the publish preference to FALSE (the default value
is TRUE ). As described in the preceding section, you can do this at the global, database,
schema, or table level. The following example shows how to do it for a table belonging to
the current user:
dbms_stats.set_table_prefs(ownname => user,
tabname => 'T',
pname => 'PUBLISH',
pvalue => 'FALSE')
2.
Gather object statistics. Because the publish preference is set to FALSE for this table, the
newly gathered object statistics aren't published. A set of pending statistics is created
instead. This means the query optimizer keeps using the statistics available before the
gathering. At the same time, cursors depending on that table aren't invalidated:
dbms_stats.gather_table_stats(ownname => user, tabname => 'T')
3.
To test the impact of the pending statistics on an application or a set of SQL statements,
you can either set the optimizer_use_pending_statistics initialization parameter to
TRUE at the session level or use the opt_param('optimizer_use_pending_statistics'
'true') hint at the SQL statement level.
4.
If the test is successful, the pending statistics can be published (in other words, made
available to all sessions) by calling the publish_pending_stats procedure. The following
example shows how to do it for a single table. If the tabname parameter is set to NULL , all
pending statistics of the specified schema are published. This procedure also has two
additional parameters. The third, no_invalidate , controls the invalidation of the cursors
depending on the modified object statistics, as previously described. The fourth, force ,
 
Search WWH ::




Custom Search