jump to navigation

Data Dictionary Performance September 21, 2009

Posted by mwidlake in internals, performance.
Tags: ,
trackback

Which data dictionary view you use to look at information about your schemas and objects can be important from a performance point of view.

{Part two is here.}

Try this:

set autotrace on statistics
set timi on
set pause off

select count(tablespace_name) from user_tables;
select count(tablespace_name) from all_tables;
select count(tablespace_name) from dba_tables;

You might want to execute each select statement a couple of times until the consistent gets are stable.

These are the stats off my test system, version 10.2.0.3:

USER_TABLES
8 records
0.14 seconds
14217 consistent gets

ALL_TABLES
4455 records
0.34 seconds
29097 consistent gets

DBA_TABLES
4455 records
0.21 seconds
16834 consistent gets

The select against user_tables is fastest and uses the least consistent gets as the view implicitly limits the search to your objects with the clause “where o.owner# = userenv(‘SCHEMAID’)”. You use the USER_ views when you really only want to see your objects.

More interesting are the timings for ALL_TABLES and DBA_TABLES. I have SELECT_ANY_TABLE and SELECT_ANY_DICTIONARY so I see all tables with either view. But the DBA_ view is quicker and takes less consistent gets than the ALL_ view.

The reason is that the ALL_ views take into account checking your privileges to see if you have the right to see any particular object. The DBA_ views do not as you can only see them if you have system privileges to do so.

ALL_TABLES includes the following to check for access rights:

and (o.owner# = userenv('SCHEMAID')
or o.obj# in
(select oa.obj#
from sys.objauth$ oa
where grantee# in ( select kzsrorol
from x$kzsro
)
)
or /* user has system privileges */
exists (select null from v$enabledprivs
where priv_number in (-45 /* LOCK ANY TABLE */,
-47 /* SELECT ANY TABLE */,
-48 /* INSERT ANY TABLE */,
-49 /* UPDATE ANY TABLE */,
-50 /* DELETE ANY TABLE */)
)
)

So, if you are using an account that has access rights to see everything (DBA access typically), use the DBA_ views and they will perform better.

If you only want your objects then the best performance will come from using the USER_ views and not the ALL_ views, correct? It makes sense! Remember my cautions in earlier posts about theory and practice. There is a sting in this particular tale…

select count(tablespace_name) from user_tables;
COUNT(TABLESPACE_NAME)
———————-
8
Elapsed: 00:00:00.15

Statistics
——————————————————
0 recursive calls
0 db block gets
14217 consistent gets

Compared to

select count(tablespace_name) from all_tables
where owner=USER;
COUNT(TABLESPACE_NAME)
———————-
8
Elapsed: 00:00:00.10

Statistics
—————————————————–
0 recursive calls
0 db block gets
4117 consistent gets

selecting from ALL_TABLES and filtering by USER performs significantly better on my system than using the USER_TABLES view!

One caveat. I suspect my test system has poor fixed-object statistics, so I would be interested if anyone else finds different results on their system.

About these ads

Comments»

1. coskan - September 21, 2009

As you requested:)

Nothing special then your results on 11.1.0.6

SQL> select * from v$version;

BANNER
———————————————————————–
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
PL/SQL Release 11.1.0.6.0 – Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 – Production
NLSRTL Version 11.1.0.6.0 – Production

SQL> set autotrace on statistics
SQL> set timi on
SQL> set pause off
SQL>
SQL> select count(tablespace_name) from user_tables;

COUNT(TABLESPACE_NAME)
———————-
109

Elapsed: 00:00:00.23

Statistics
———————————————————-
1787 consistent gets
SQL> select count(tablespace_name) from all_tables;

COUNT(TABLESPACE_NAME)
———————-
1700

Elapsed: 00:00:00.29

Statistics
———————————————————-
5094 consistent gets
SQL> select count(tablespace_name) from dba_tables;

COUNT(TABLESPACE_NAME)
———————-
1700

Elapsed: 00:00:00.15

Statistics
———————————————————-
2810 consistent gets

SQL> select count(tablespace_name) from user_tables;

COUNT(TABLESPACE_NAME)
———————-
109

Elapsed: 00:00:00.14

Statistics
——————————————————-
1787 consistent gets

SQL> select count(tablespace_name) from all_tables
2 where owner=USER;

COUNT(TABLESPACE_NAME)
———————-
109

Elapsed: 00:00:00.26

Statistics
——————————————————-
1573 consistent gets

Very nice post for addition to this topic is the one below (Jonathan Lewis already mentioned it but it is still something to remember)

http://oratips-ddf.blogspot.com/2008/01/lies-damned-lies-and-statistics.html

mwidlake - September 21, 2009

Interesting, and thanks for repeating the tests Coskan. (I’m dissapointed it is not 11.2 though :-) Only Kidding :-)
I presume you have not gathered fixed object statistics. (I’m not so sure this is the problem though).

coskan - September 21, 2009

actually I re-run it after gathering fixed object stats but the results were nearly smilar(only all_tables were only 1 more different) thats why I did not mentioned them.

here are the results

SQL> select count(tablespace_name) from user_tables;

COUNT(TABLESPACE_NAME)
———————-
109

Elapsed: 00:00:00.15

Statistics
——————————————————–
1787 consistent gets

SQL> select count(tablespace_name) from all_tables;

COUNT(TABLESPACE_NAME)
———————-
1700

Elapsed: 00:00:00.35

Statistics
——————————————————–
5095 consistent gets
SQL> select count(tablespace_name) from dba_tables;

COUNT(TABLESPACE_NAME)
———————-
1700

Elapsed: 00:00:00.17

Statistics
——————————————————–
2810 consistent gets

mwidlake - September 21, 2009

Actually, that is a significant point Coskan. From that I think we can deduce that lack of fixed object stats is not causing odd behaviour here and that the use of ALL_ views with a “WHERE owner=user” predicate is always worth checking to see if it runs more quickly than using the USER_ views. Maybe of interest only when working against the data dictionary for regular administration tasks but good to know.

coskan - September 21, 2009

and for the all_tables with USER filter

SQL> select count(tablespace_name) from all_tables
2 where owner=USER;

COUNT(TABLESPACE_NAME)
———————-
109

Elapsed: 00:00:00.15

Statistics
——————————————————–
1571 consistent gets

2. PdV - September 22, 2009

Martin, others,

Nice experiment, I have used the following stmtns:

select count ( tablespace_name) as user_tabs
from user_tables ;
from ALL_tables ;
from ALL_tables where owner = user ;
from DBA_tables ;
from DBA_tables where owner = user ;

Threw the whole thing in an SQL-script and then I tested on 11.1.06 (Linux, AMD, hosted somewhere in Rotterdam, courtesey of Anjo Kolk) and on Windows on Intel Dual-core T7500@2.2GHz (on me cumpany lappy-clunker).

The “timing on” data seemed mostly due to sql-net round trips, hence I will disregard. To keep it short n simple, I’ll send you the original SQL and spoolfile in mail.

Here is my summary of consistent gets:

1023 @ Windows:
no filter filter
User tables 399
All Tables 2963 401
dba tables 1021 401

11106 on Linux:
no filter filter
User tables 1578
All Tables 6044 944
dba tables 2826 944

My comments:

1. The windows db is mini-minimalistic, 10 users and a total of 839 tables. I prefer to (re)create my test-db very Very minimalistic (I’m impatient), and I weed out the DBCU script to the bare minimum. The 11g db seems DBCU default created, and has 33 users with 2871 tables.
Bad practice on my part of course, but my mini-mini does spend only half or less of the effort doing these queries ;-).

2. In both my cases the “ALL” is less efficient then the “DBA” tables (my SCOTT has DBA and some sys-privs granted). I think the use of “ALL_%” in any code is going to rank low with me now, unless there is some where-clause involved.

3. NB: I also tried “owner = ‘SCOTT'; “, and I tried count (‘x’).
The results (on 2nd execute) are equal to “owner = user”.

Viola, my 2 cents (from the lobby of the SIOUG.si conference – nice location!)

Hvala (that is Thank You, in Slovenian)

PdV

mwidlake - September 22, 2009

Many thanks to yourself and Anjo (and thanks or the raw data by email)

All evidence seems to be supporting the use of DBA_ over ALL_ views for best performance (which makes sense once you know about the security code in the view). It also supports the potential use of user-qualifed DBA_ views over USER_views. Which is far less obvious.

Nice

3. PdV - September 22, 2009

Apologies for the layout,
wordpress removed “filler spaces”.
What was wrong with 24×80 terminals using courier?

mwidlake - September 22, 2009

Stop complaining about “better user experience” through complex GUI environments Piet :-)

I “love” using a 1024*768 screen with modern Windows which gives me a working area of 15 lines of text and a load of “helpful icons and windowing” around the screen. SOOOO much better than 24*80. So much better… (grrrrrrr).

4. B. Polarski - September 24, 2009

nice post.


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

Follow

Get every new post delivered to your Inbox.

Join 156 other followers

%d bloggers like this: