Frequent execution of sql referencing dictionary views

A colleague asked me to have a look at a AWR report for an older application.
Looking at the top sqls I noticed that the application code was accessing dictonary table user_table around 40-50.000 times per hour. Even if it wasn’t using  a huge amount of cpu I started to think how I can tune this statement.

Looking further of the statement which by the way is
SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME = UPPER(:B1 )

I noticed that it was more or less looking for 3 different tables in the application schema.
My first thought was can I use the result cache for this statement ? I have used the result cache before with good results. For those of you that do not know what the result cache is it is a way to cache the rows returned instead of caching the blocks as we do in the buffer cache. Since this was a dictionary table I was uncertain if it is possible/supported to use the result cache. So i used the Oracle forum and asked if it was supported/possible.

Having the discussion I agreed that it isn’t possible since the dictionary tables are stored in the dictionary cache and not in the buffer cache. As the result cache works with the buffer cache we could not use it against dictionary objects.
This is what the documentation says about the dictionary cache.

The data dictionary consists of base tables and views.

These objects are defined as follows:
  • Base tables These store information about the database. 
    Only Oracle Database should write to and read these tables.
    Users rarely access the base tables directly because they are 
    normalized and most data is stored in a cryptic format.

 

Then I got an example in my forum post how to workaround this using a copy of the user_tables. It was an interesting solution.

I used that information and did some adjustments, I decided to use a materialized view instead. Here are my steps to get the sql accessing dictionary tables to use the result cache.

First I create the materialized view and indexes in my application schema.

SQL> CREATE materialized VIEW user_table_mv
 2 build IMMEDIATE
 3 AS SELECT *
 4 FROM user_tables;

Materialized view created.

SQL> CREATE INDEX idx_user_table_mv 
 ON user_table_mv(table_name); 

Index created.

In my case the application is an old application, we do not add/drop tables to this schema frequent. Applications tables are normally not changed that frequent, it is done when you upgrade or patch the application. I do not expect this materialized view to be refreshed on a regular basis. I think it can be done manually when needed.

Next step is to create a synonym so my sql will use the materialized view.

SQL> CREATE synonym user_tables FOR user_table_mv;

Synonym created.

 

 

Next we need to hint the sql to use the result cache. If you can control the code, you can add the hint in your code /*+RESULT_CACHE */. What if you can’t do that ?
In my forum post I got another useful tip, which at first was an interesting feature but seemed not useful to me in this case. But now I think it is a really neat feature to use.

What I need is to have the hint for result cache merged into my application code. I will use the sql_patch feature pointed out in my forum post.

DECLARE 
 ht VARCHAR2(100); 
BEGIN 
 sys.dbms_sqldiag_internal.I_create_patch( 
 sql_text => 'SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME = UPPER(:B1)', 
 hint_text => 'RESULT_CACHE', 
 name => 'sql_patch', 
 description => 'We need to hint result cache for the sql'); 
END;
/

PL/SQL procedure successfully completed.

Unless you have the needed privileges to execute the dbms_sqldiag package, you have to run this as sysdba user. When it is created we can test our sql.

sqlpatch3

It returned 1 row as expected. Looking at the execution plan and additional information we can see.

sqlpatch2

Our hint for result cache is used. And a sql patch named sql_patch is used.

We have now improved our select against a dictionary view. You can do this against other dictionary views as well, but I shouldn’t do it against views that are frequently updated.

 

Advertisements

2 thoughts on “Frequent execution of sql referencing dictionary views

  1. I think and easy:
    CREATE table user_tables AS SELECT * FROM user_tables;

    you must be keep table structure = viewname user_tables.
    other toad don’t display table definition.

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