jump to navigation

Data Dictionary Performance September 21, 2009

Posted by mwidlake in internals, performance.
Tags: ,
10 comments

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.

Follow

Get every new post delivered to your Inbox.

Join 156 other followers