X

Insights into Statistics, Query Optimization and the Oracle Optimizer

Fixed Objects Statistics and Why They are Important

Maria Colgan
Master Product Manager

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 sub-optimal 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.

Prior to Oracle Database 12c Release 1 fixed object statistics 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 important 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.

From Oracle Database 12c Release 1 the automatic statistics gathering job will gather statistics for fixed tables that have missing stats. For this to happen, there will need to be some time available inside the batch window after statistics for the other tables in the system have been gathered. Even with this new functionality, it is still good practice to gather fixed table stats with DBMS_STATS.GATHER_FIXED_OBJECTS_STATS when there's a representative workload running, especially after major changes have been made to the system.

Join the discussion

Comments ( 14 )
  • Kellyn Pot'Vin Tuesday, January 10, 2012

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


  • guest Thursday, January 12, 2012

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


  • Maria Colgan Thursday, January 12, 2012

    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.


  • Olivier Friday, January 13, 2012

    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


  • guest Saturday, January 14, 2012

    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


  • guest Tuesday, February 14, 2012

    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.


  • David Mann Monday, February 20, 2012

    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.


  • David Mann Thursday, February 23, 2012

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

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


  • guest Wednesday, August 15, 2012

    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.


  • Danyc Monday, October 15, 2012

    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


  • Ruslan Shamritckii Thursday, May 24, 2018
    Hi Maria,

    What would be your advice in case when auto job is disabled?

    Should gather_dictionary_stats be executed with default parameters or it is better to set following options: cascade => true, options=>'GATHER'?

    Regards,
  • Nigel Bayliss Monday, June 4, 2018
    Always use the defaults. If you don't want to use AUTO for you application schemas, use this instead...

    DBMS_STATS.SET_GLOBAL_PREFS(pname=>'autostats_target',pvalue=>'oracle')

    You can take care of application schemas yourself, but dictionary stats will be maintained.

    Regards,
    Nigel
  • Bhavin Wednesday, September 19, 2018
    Do we need to run fixed object stats on CDB every time new PDB is added (plugged) to CDB?
    What about dictionary stats in this case?
  • Nigel Bayliss Wednesday, September 26, 2018
    Hi Bhavin -

    Treat PDBs as you would treat a non-multitenant database and gather fixed table stats after creation and when there's significant change. Auto stats will in fact gather fixed table stats for you first time. Let auto stats gather dictionary stats for you too (and this is a continuous process rather than one-time operation). If you clone or relocate a PDB, then of course the statistics will not require special treatment (assuming they are being maintained).

    For the CDB, let auto stats deal with dictionary stats. When you create a new PDB, the contents of the CDB fixed tables change to some extent as you would expect. The relative amount of change is generally pretty small, and will decline as you add more PDBs (because the fixed tables will have more rows). It is therefore something that becomes less relevant as the system grows. Gather fixed table stats in the CDB just as you would when any database undergoes a large amount of change.
    Regards,
    Nigel.
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha