Dontcheff

On gathering dictionary statistics: do I analyze the SYS schema?

In Database tuning, DBA, Grid Control, Oracle database on June 4, 2011 at 21:29

Attorneys specialize nowadays in neighbor’s-trees-blocking-your-view litigation, I try to specialize in fine-grain-database-tuning.

First you specialize in databases, then you narrow to Oracle, then you specialize in performance tuning. And then I narrow to certain types of tuning: I think automatic tuning is the future. But who knows, I have often been wrong before. People ask me: “Julian, are you getting lazy nowadays that you do not want to do the manual work?”

Anyway, you express opinion on a certain subject, some people take it for granted, some want to see a bulletproof one. So, how about this question: should we analyze the SYS schema? “Beats me” is the most common answer.

Rusted DBAs who still go memory lane 7.3.4 after a glass of whiskey will probably tell you no way! But most DBAs from the new school have already read 245051.1, where it clearly says: “In Oracle 10g collecting of data dictionary statistics is mandatory for CBO to work properly”.

1. Dictionary Statistics. In another MOS note, 457926.1, we read the following: “Gather_schema_stats gathers statistics for objects owned by the SYS Schema. We recommend gathering statistics for the SYS schema, specifically if you are using Oracle APPS.” Here is more:

2- SQL Performance Analyzer. Question is, is this enough convincing? Granted that all we have seen documented and praised, implemented in reality is not always a one-to-one match. Right?

Solution is to use the SQL Performance Analyzer with a SQL Tuning set containing only system queries. Delete the data dictionary statistics first, flush the shared pool and analyze the SYS schema without publishing the statistics. I did it and here is the report (click on the picture to enlarge the image):

22% improvement is nothing great but at least it clearly shows that you better analyze SYS than not. Of course, with a different SQL set you might have a different result. Try for yourself! Look at the top 10 statements affecting the performance, I witnessed the following:

Have a look also at the following good articles: Data Dictionary Statistics Gathering and
Gathering System Statistics in Oracle.

3. How and when to gather SYS stats. Granted we agree that we have to analyze SYS, it would be quite natural to ask: when and how often? Usually, I would do this after big changes to the database. Like creation of a new schema with lots of objects, before/after big datapump operations, etc. Check for example: Poor Performance With DataPump Export on Large Databases [ID 473423.1]

Or even better have it as a database job. If you check “Oracle Sun Database Machine Application Best Practices for Data Warehousing” [ID 1094934.1], you will note the following:

“When to gather dictionary statistics: Statistics on the dictionary tables will be maintained via the automatic statistics gathering job run during the nightly maintenance window. If you choose to switch off the automatic statistics gathering job for your main application schema consider leaving it on for the dictionary tables. You can do this by changing the value of AUTOSTATS_TARGET to ORACLE instead of AUTO using the procedure DBMS_STATS.SET_GLOBAL_PREF.

EXEC DBMS_STATS.SET_GLOBAL_PREFS(AUTOSTATS_TARGET,’ORACLE’);

If you choose to switch off the auto job complete you will have to maintain the dictionary statistics manually using the GATHER_DICTIONARY_STATS procedure.”

By default, automatic optimizer statistics collection calls the DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC procedure. By default GATHER_SYS is true (?).

4. Fixed tables’ statistics. Neil Johnson wrote a good one on When to Gather Fixed Object Optimiser Statistics. The example is enough convincing to me at least.

Fixed Objects Statistics Considerations [ID 798257.1] suggests why those statistics are needed:

1. Missing or bad statistics on the X$ / fixed tables can lead to performance degradation or hangs. Various X$ views are protected by latches and as a result can be very expensive to query in large / busy systems.
2. Most commonly this issue is seen on the underlying X$ tables for DBA_EXTENTS, V$ACCESS, V$RMAN_BACKUP_JOB_DETAILS, and V$RMAN_STATUS, but any fixed table protected through latching can experience this.
3. Another commonly seen symptom is extreme TEMP space usage driven by poor plans against the fixed tables.
4. RMAN, Data Guard, Streams, and Grid Control make heavy usage of the fixed tables through the DBA/V$ views and so can often bear the brunt of performance issues.

5. Locked statistics in the Data Dictionary. Starting 11.2.0.2, statistics for some objects in the Dtata Dictionary are locked by default with a likely goal of preserving access paths for those queries. And in fact, not only in the SYS schema! You can modify the following SQL to find out what is locked in your database by default:

select TABLE_NAME, LAST_ANALYZED, STATTYPE_LOCKED 
from DBA_TAB_STATISTICS
where STATTYPE_LOCKED is not null and owner='SYS'
/

Let me finish with a screenshot of the statements which Oracle considered unsupported during my SQL Performance Analyzer test:

Do you know a DBA specialized in Oracle bugs?

Advertisements
  1. Hi Julian,

    I just noticed the inbound link to our blog from the “Fixed tables’ statistics” section in your post and wanted to correct the author you have credited the post to: It was actually Neil Johnson’s work rather than mine.

    Thanks,
    Martin

  2. I want to to thank you for this great read!! I absolutely enjoyed every bit of
    it. I have you book marked to look at new things you post…

  3. I am really impressed along with your writing talents and also with the structure on
    your weblog. Is that this a paid subject matter or did you modify it your self?

    Anyway keep up the nice quality writing, it’s uncommon to see a great weblog like this one these days..

  4. Hi to every single one, it’s actually a fastidious for me to pay a visit this website, it contains useful Information.

  5. magnificent submit, very informative. I wonder why the other specialists of this sector do not understand this.
    You must proceed your writing. I am confident, you’ve a great readers’ base already!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: