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.

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…

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).

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.

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.

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.

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.

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).