Fixed Objects Statistics and why they are important

Fixed objects are the x$ tables and their indexes. The v$performance views in Oracle are defined in top of X$ tables (for example V$SQL and V$SQL_PLAN). Since V$ views can appear in SQL statements like any other user table or view then it is important to gather optimizer statistics on these tables to help the optimizer generate good execution plans. However, unlike other database tables, dynamic sampling is not automatically use for SQL statement involving X$ tables when optimizer statistics are missing. The Optimizer uses predefined default values for the statistics if they are missing. These defaults may not be representative and could potentially lead to a suboptimal execution plan, which could cause severe performance problems in your system. It is for this reason that we strong recommend you gather fixed objects statistics.

Fixed Object statistics must be manually gathered. They are not created or maintained by the automatic statistics gathering job. You can collect statistics on fixed objects using DBMS_STATS.GATHER_FIXED_OBJECTS_STATS. BEGIN
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
END;

The DBMS_STATS.GATHER_FIXED_OBJECTS_STATS procedure gathers the same statistics as DBMS_STATS.GATHER_TABLE_STATS except for the number of blocks. Blocks is always set to 0 since the x$ tables are in memory structures only and are not stored on disk. You must have the ANALYZE ANY DICTIONARY or SYSDBA privilege or the DBA role to update fixed object statistics.

Because of the transient nature of the x$ tables it is import that you gather fixed object statistics when there is a representative workload on the system. This may not always be feasible on large system due to additional resource need to gather the statistics. If you can’t do it during peak load you should do it after the system has warmed up and the three key types of fixed object tables have been populated:

Structural data - for example, views covering datafiles, controlfile contents, etc
Session based data - for example, v$session, v$access, etc.
Workload data - for example, v$sql, v$sql_plan,etc

It is recommended that you re-gather fixed object statistics if you do a major database or application upgrade, implement a new module, or make changes to the database configuration. For example if you increase the SGA size then all of the x$ tables that contain information about the buffer cache and shared pool may change significantly, such as x$ tables used in v$buffer_pool or v$shared_pool_advice.

+Maria Colgan

Comments:

So glad to see this out there. I can't believe how often I have found this missing in environments!

Posted by Kellyn Pot'Vin on January 10, 2012 at 01:49 PM PST #

Those statistics are so important, why doesn't oracle simply collect it automatically? Data statistics are automatically collected as well nowadays.

Posted by guest on January 12, 2012 at 05:16 AM PST #

We are working on a mechanism were we automatically gather fixed object statistics. The reason it hasn't been done before is the transient nature of the x$ tables. When would be a good time to automatically gather the statistics? We will let you know once we have an automatically solution but for now we recommend you gather fixed object statistics.

Posted by Maria Colgan on January 12, 2012 at 05:58 AM PST #

Maria,
in "[ID 457926.1] How to gather statistics on SYS objects and fixed_objects ?"

The answer is :

To gather the dictionary stats:-
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('SYS');
SQL> exec DBMS_STATS.GATHER_DATABASE_STATS (gather_sys=>TRUE);
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

Gather_fixed_objects_stats also gathers statistics for dynamic tables e.g. the X$ tables which
loaded in SGA during the startup. Gathering statistics for fixed objects would normally be recommeded if poor performance is encountered while querying dynamic views e.g. V$ views.
Since fixed objects record current database activity; statistics gathering should be done when database has a representative load so that the statistics reflect the normal database activity .

To gather the fixed objects stats use:-
EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

what's the main difference between These 4 procedures ? Why not simply having one single procedure for everything which is related to dictionnary data ?

Thanks
Olivier

Posted by Olivier on January 12, 2012 at 07:22 PM PST #

Hi Maria,

Is there any way to tell if fixed object stats are stale?

How frequently should fixed object statistics be gathered considering that the data is transient?

Thanks
Kezie

Posted by guest on January 13, 2012 at 10:19 PM PST #

Hello Maria, thank you for the post.

I have seen that with using DBMS_STATS.GATHER_FIXED_OBJECTS_STATS, you need to take great care in not running during times when there is load on the system. This is mentioned in the following support note: GATHER_FIXED_OBJECTS_STATS Considerations 798257.1.

If someone does mistakenly run this during a time when there is load, library cache waits start stacking up and we begin to experience concurrency issues.

Typically, with this command, it appears to only work about 50% of the time the first time. When attempting to log SRs and recreate the issue, the command decides to start working. Usually the failures are due to this error:

"ORA-31011: XML parsing failed"
"ORA-19202: Error occurred in XML processing"
"LPX-00245: extra data after end of document"

Subsequent attempts to rerun the command end up resolving the issue, but no root cause has ever been determined due to the inconsistency of the error.

It's up to you whether or not you wish to mention any potential errors in the post, but I think that given the concurrency issues this command can cause if not run carefully, it may be worth mentioning at least further considerations that should be made when running this command (similar to text in the support note).

Thank you Maria.

Posted by guest on February 14, 2012 at 08:46 AM PST #

Another vote here on guidance if Fixed Object stats are stale (or just need a 2nd look for verification).

This reminds me of the first incarnations of System stats... Definitely can see the importance but info not easy to come by on them.

Posted by David Mann on February 20, 2012 at 12:15 PM PST #

Guess I could have explored a little first... Hidden in plain sight...

SELECT * FROM DBA_TAB_STATISTICS WHERE OBJECT_TYPE='FIXED TABLE';

Posted by David Mann on February 22, 2012 at 08:42 PM PST #

As is true of many people, over the years I have written a collection of useful reports based on various v$ and gv$ views, reports that include queries that are a key part of doing my Oracle performance work. These reports and many other v$ queries from tools like Toad and Grid Control run very slowly in one of our production databases, to the point that they appear to hang. This greatly hampers our ability to diagnose problems in this database. Temporary solution: I compared execution plans for the diagnostic queries from the problem database with plans from databases where the queries ran quickly. A common theme emerged, the queries involving gv$session, gv$lock , gv$transaction, etc. were using mostly nested loops joins at the outermost level of the plan, and in the faster databases they are using hash joins. I added use_hash hints to my reports, now they run quickly in the problem db. Fixed obj stats will be gathered later at an appropriate time, which I suspect will fix this problem across the board, allowing the third-party queries to run quickly as well.

Posted by guest on August 15, 2012 at 12:48 AM PDT #

Hi Maria,

Quick one: what is the differences between

BEGIN
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
END;

and

BEGIN
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS(null);
END;

Cheers,
Dani

Posted by Danyc on October 15, 2012 at 04:29 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

The Oracle Optimizer blog is written by members of the Optimizer development team. The goal of this blog is to provide an insight into the workings of the Optimizer and the statistics it relies on. The views expressed on this blog are our own and do not necessarily reflect the views of Oracle and its affiliates. The views and opinions expressed by visitors on this blog are theirs solely and may not reflect ours.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today