RE: Perl Issues
Date: Fri, 22 Feb 2008 11:50:00 -0600 (CST)
Interesting -- I try this and I get index hits on OBJ$, albeit with a paltry 12K rows in the ALL_TABLES view.
What version of Oracle? This could be an issue with stats on the dictionary tables, either having them in 9i or not having them (or having bad ones) in 10g.
p.s. I will not complain about my table layout. Ever.
> Nope. Any query against All_Tables (or DBA_Tables or User_Tables) results
> in a Full Table Scan of OBJ$. Having 150,000+ tables and 230,000+ indexes
> in that one schema in the database results in about 420,000 rows in OBJ$.
> Any query using Table_Name causes over 6,400 Consistent Gets. Not
> horrible, but, when there are hundreds or thousands of such queries in a
> session, it adds up to a huge impact. In a "normal" database you'd
> probably not notice.
> As Sys in one of your databases, set Autotrace On in SQL*Plus, do the
> select below, and check out the Explain Plan - pretty convoluted, eh? See
> how many rows your OBJ$ has and how many Consistent Gets the query takes -
> there's a correlation, at least in my little brain.
> Jack C. Applewhite - Database Administrator