Skip to main content

Fixed Objects and Data Dictionary Statistics

I Recently came across a performance issue in a 12c database. Queries that used to run in 500 ms, now were taking several minutes to complete, in some cases 30 or more minutes. While troubleshooting the issue, many different ideas came to mind to try to resolve the issue - gather schema stats, change optimizer settings, partition related objects, etc.

After trying all these options - and some more, we were able to improve response time; now it was consistently around 5 seconds, but still way too far from the 500 ms that was before.

While analysing the (still) issue, I realized that when getting an explain plan for the queries, it would take around 5 seconds to get it. So I decided to gather stats on fixed objects and on data dictionary in order to improve explain plan response time.

After having done this, overall system performance improved considerably, explain plan response time came down to couple milliseconds, and problematic queries returned to normality, ~500ms.

Obviously this is not black magic, now I know this is just how Oracle works. In order for the optimizer to choose an optimal execution plan, it relies on the fixed objects to generate such - those are the x$ tables. If those objects have stale or missing statistics, then the optimizer uses a set of predefined default values for these tables that could lead to suboptimal execution plans.

In the end the solution was easy, but getting to it was not that easy. This area seems to be something that not many people talk about, I don’t know if that is because is too obvious or because is not generally known. Fixed object stats should be refresh any time a major change occurs on the database, for example, partitioning tables, purging data, dropping objects, changing database parameters, index rebuilds - anything that could affect the data stored in the x$ tables.

Next time when you run out of options when tuning SQLs or the database, remember that may be suffering from fixed objects stale stats.

By the way, this is the command I used:

begin
DBMS_STATS.GATHER_DICTIONARY_STATS;
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;end;/



To read further: https://blogs.oracle.com/optimizer/entry/fixed_objects_statistics_and_why

Labels:

Comments

Post a Comment

Popular posts from this blog

Oracle Linux 7 authentication to Active Directory

Introduction I've been curious from some time to see how Active Directory users could natively authenticate to Linux, or said in a different way, how to configure Linux to allow AD users to log in without the need to have those users in each and every Linux box manually. Although there are several different ways to accomplish this, I found that the easiest and less time consuming way to do it is with the use of Samba WinBind. Later I will show how to use Oracle Internet Directory (OID) to accomplish the same feat. Using an IdM solution (AD, OID) can help to reduce the time to deploy users, you can centrally manage access to linux servers from Active Directory Users and Computers (ADUC) app, and can allow administrative tasks thru sudo depending on the group the users belong to. For my Proof of Concept I created two virtual machines, one with Windows 2012 Server Essentials, and another one with Oracle Linux 7.1. In AD I created 1 group named osd-linux-oretail, and several tes
Post a Comment
Read more

RMAN-06613: Connect identifier for DB_UNIQUE_NAME not configured

I ran into an issue with a recently cloned database. Every time I ran "resync catalog from db_unique_name all;" command in RMAN, it error out with RMAN-6613: connect identifier for db_unique_name not configured. We know that this command is not needed for non-dg enabled databases, but because we have a mix of DG and non-DG enabled databases, maintaining different scripts per type of database is not worth it. To the extent of my knowledge, this command is harmless; the error is more like a warning on non-dg databases, still is a symptom of something not well configured. Troubleshooting Once you connect to the target and catalog repository with RMAN, you try to resync catalog with the following command: RMAN> resync catalog FROM DB_UNIQUE_NAME ALL; starting full resync of recovery catalog full resync complete resyncing from database with DB_UNIQUE_NAME DBTST RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE
Read more

RMAN 12c - SBT vs TAPE

I recently upgraded a database from 11g to 12c without issues, but after several days, I noticed that the archived redo logs were not being deleted from the filesystem, even after they were backed up to tape. At first I thought that there might be some issues with that specific database, but when I verified other 12c databases that uses the same backup script, I found that they were exhibiting the same behaviour. So I started the troubleshooting process by checking the RMAN configuration, nothing seemed out of the ordinary. I use the following settings: CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR; CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO TAPE; CONFIGURE DEVICE TYPE 'SBT_TAPE' PARALLELISM 4 BACKUP TYPE TO BACKUPSET; CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET; CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE BACKUP OPTIMIZATION OFF; The RMAN command is this: run { backup filesperset 1 tag ‘arl_test’ archivelog all; d
Post a Comment
Read more