Last time, in Part 2, I reviewed
A comment on that post from Bert Scalzo provided a great segue into today's Part 3 of the series. In this post, I will cover collecting stats on data dictionary and fixed objects.
Fixed Object StatisticsFixed objects are the X$ tables upon which the dynamic V$ views are built. If you use Toad or SQL Developer or any product that provides a graphical view into your database, those tools execute loads of SQL using V$ views - and by extension - the X$ tables. Views like V$SESSION, V$SQL, V$SQL_PLAN and so on are examples of views built on X$ tables. You can look at the source SQL for any V$ view to see what X$ tables it uses with the following query (using a lookup for V$SESSION):
select * from v$fixed_view_definitionwhere upper(view_name) like upper(nvl('V$SESSION',view_name))and upper(view_definition) like upper(nvl('V$SESSION',view_definition));
Since you can query these tables via the various V$ views, it is important for statistics to exist on the underlying tables and indexes so that the optimizer can make good execution plan choices.
Although we haven't discussed it yet, later in the series I'll review dynamic sampling (DS) in more detail. For now, note that DS is a feature whereby the optimizer will dynamically gather statistics when a query is parsed and there are no statistics present for an object used in the query. This happens by default for "normal" database tables, but when the X$ tables are involved, it's a whole different story. This ability to dynamically sample tables is important so that the optimizer's calculations for cardinality and selectivity are as accurate as possible. Without statistics, Oracle uses default values which are sorely deficient in terms of accuracy.
The reason why X$ tables aren't dynamically sampled is that they are really just memory objects and not actual tables. That means their data is transient and not stored on disk. X$ tables contain information regarding things like controlfile contents, session information, and SQL executions. All of these things are constantly changing so the X$ tables don't actually store the information to disk but keep it in memory instead. So, dynamic sampling isn't used against them and therefore any of them that don't have statistics use defaults. That can lead to bad plan choices by the optimizer which leads to poor performance.
You may be wondering if the X$ tables are memory objects, then how do statistics get collected on them? Well, the key here is that you want to collect statistics of these tables after some period of time has passed where a normal, representative workload has occurred. Note that I say "after"...not during. If you're in the middle of normal to heavy activity, the collection of fixed stats can actually cause performance issues as Oracle tries to read these objects while they're undergoing frequent change.
The best thing to do is wait until the evening - or whatever time period is the quietest in your system - and execute the fixed objects collection then. The idea is that the collection won't cause so much contention if the system is quiet and it will be able to complete faster. Once the collection is done, you will have a representative set of information the optimizer can use and any queries against the V$ views (i.e. the X$ tables) will have more accurate execution plans produced and performance will be better.
In Part 2, we discussed the automatic statistics collection feature where Oracle uses a default, pre-defined maintenance window to execute prioritized stats collections on objects that have no stats at all or have stats that are stale by 10% or higher. Well, that points to another issue with collecting stats on fixed objects. If you think your automatic collections are gathering fixed object stats, think again. They don't. Oracle specifically states so in the Managing Oracle Statistics chapter of the Oracle Performance Tuning Guide as follows:
"You must manually collect statistics on fixed objects, such as the dynamic performance tables, using GATHER_FIXED_OBJECTS_STATS procedure. Fixed objects record current database activity. You should gather statistics when the database has representative activity."
Note that when you execute the fixed objects collection, you can simply execute the procedure without any parameters. However, you can also provide a table that can retain a copy of the current stats before overlaying them with the new collection. To do that, you must first use the CREATE_STAT_TABLE procedure. That way, you'll know you have a good backup from which to restore if your new collection produces unexpected results. Here's an example of how to collect fixed object stats:
dbms_stats.create_stat_table(ownname => 'STATS_USER',stattab => '2014_JUL_FIXED_OBJ_STATS_BKUP');
dbms_stats.gather_fixed_objects_stats(stattab => '2014_JUL_FIXED_OBJ_STATS_BKUP',statown => 'STATS_USER');
Finally, note that you don't have to collect fixed objects stats over and over. Once you have collected them based on your database having executed a representative workload, you don't need to collect again unless you have something like a major parameter change that might effect what is being used/tracked/contained in the fixed objects. Otherwise, the fixed objects should stay about the same size (generally speaking) and don't require frequent collections.
Data Dictionary StatsData dictionary tables are those owned by 'SYS', 'SYSTEM' and other Oracle RDBMS components. So, basically, collecting data dictionary stats is just like collecting stats on the tables owned by your application schemas. As a matter of fact, if you have the automatic statistics collection job enabled, when it runs it will include the collection of data dictionary stats. I think it's here where some folks get mixed up about fixed objects being collected. As I already discussed, fixed object stats require a manual collection and they are not included in the automatic collection job. But, the good news is that the data dictionary tables are included!
This points out a possible downside if you disable the automatic collection job: when it is disabled, it also disables the collection of data dictionary stats. However, you can also manually gather data dictionary stats using the GATHER_DICTIONARY_STATS procedure. This procedure takes the same parameters as does GATHER_*_STATS does for all other objects (more to come on these parameters later in the series). And, if you really want to disable the auto collection job while leaving the dictionary stats collection in place, you can just use:
Using this option leaves the data dictionary stats collections in an on-going "auto-collect" state. That way, if the staleness of any of the objects hits the threshold, they will automatically be included in the next collection. Setting preferences is a topic all in itself, and I'll cover that in more detail later in the series.
Of course not collecting data dictionary stats would likely only be an issue if your 'SYS' and 'SYSTEM' owned tables are changing rapidly. This isn't usually the case, but when you have a database upgrade (between versions particularly) or an application upgrade where there are lots of DDL changes, you should collect data dictionary stats then.
So, to wrap up, think of fixed object stats and data dictionary stats as just one more piece of the entire statistics pie. The key factor when collecting statistics is the need to provide the optimzier with representative statistics from which it will make execution plan choices. The better info (in the form of statistics) that is provided to the optimizer, the better the plans and performance will be.
Thanks for the mention - excellent blog. Question - if you have a nightly data load for say a data warehouse where you add tens to hundreds of millions of rows per night and utilize a sliding window partitioning scheme (whether manual or automatic), would not a final nightly job step to to refresh both the fixed and data dictionary stats be a reasonable idea? Possible new table and index partitions (segments) as well as lots more extents in data dictionary tables. Not saying mandatory - but am wondering if advisable (i.e. chicken soup - can't hurt and might possibly be worthwhile).
your first query should look like:
select * from v$fixed_view_definition
where view_name like upper(nvl('%V$SESSION%',view_name))
and upper(view_definition) like upper(nvl('%V$SESSION%',view_definition));