Something else I do automatically, to help performance, is always specify and link via the schema owner.

The below is based on a 10.2.0.3 database with fixed object statistics gathered, though a long while ago. A have a test partitioned table called TEST_P and I want to see how many blocks are in the table and also in the table partitions, so I link between DBA_TABLES and DBA_TAB_PARTITIONS. I only specify the table name as I know there is only one table called TEST_P:

Just for the record, I have run these statements several time so the consistent gets are constant. The form with the “AND DBTA.OWNER =DTP.TABLE_OWNER” line is consistently running at 580 consistent gets and the version without at 782 consistent gets.

If I now add in the where clause to look only for tables within the schema I am interested in, the code is even more efficient:

It’s maybe not at all suprising that fully specifying the schema owner of the table we are interested in allows a more efficient path through the data dictionary, but it is something we all often fail to specify as it means writing more SQL, and it works quickly enough. For one table.
But then if you convert a quick check script into part of your system housekeeping scripts and then use that housekeeping script on a very large database, things can start to run very slowly. I’ve never seen that happen, not with one of my own scripts, honest 🙂

The reason to always specify the schema owner, at all times when possible, when drilling down the data dictionary, is that so many things link back to sys.obj$ and it has an index with a leading column on owner {well,owner#}. The whole data dictionary tends towards 3rd normal form and parent-child relationships through IDs (name#, obj#, user#, ts#). Schema and name identify the objects and are one of the few links into this structure, which then links to other tables via obj# and other hash IDs.

So my advice is, if you are looking at data dictionary views where the schema owner is involved, always include that column in the join and always filter by schema owner if you can. And if things are still slow, pull out the view code and look at the indexes on the underlying sys.$ views. After all, the data dictionary is, to all intents and purposes, just a normal normalised database.