RE: Perl Issues
Date: Fri, 22 Feb 2008 14:56:49 -0600
9i (220.127.116.11) on RHEL 3.0 64bit. No stats on the Data Dictionary.
Here's what I did as Sys.
Set Autotrace On
Select '1' From All_Tables Where Table_Name = 'PLAN_TABLE' ;
0 SELECT STATEMENT Optimizer=CHOOSE 1 0 FILTER
2 1 NESTED LOOPS 3 2 NESTED LOOPS 4 3 NESTED LOOPS (OUTER) 5 4 NESTED LOOPS (OUTER) 6 5 NESTED LOOPS (OUTER) 7 6 NESTED LOOPS (OUTER) 8 7 NESTED LOOPS 9 8 TABLE ACCESS (FULL) OF 'OBJ$' 10 8 TABLE ACCESS (CLUSTER) OF 'TAB$' 11 10 INDEX (UNIQUE SCAN) OF 'I_OBJ#' (NON-UNIQUE) 12 7 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$' 13 12 INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE) 14 6 INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE) 15 5 TABLE ACCESS (CLUSTER) OF 'USER$' 16 15 INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE) 17 4 TABLE ACCESS (CLUSTER) OF 'SEG$' 18 17 INDEX (UNIQUE SCAN) OF 'I_FILE#_BLOCK#' (NON-UNIQUE) 19 3 TABLE ACCESS (CLUSTER) OF 'TS$' 20 19 INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE) 21 2 TABLE ACCESS (CLUSTER) OF 'USER$' 22 21 INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE) 23 1 NESTED LOOPS 24 23 FIXED TABLE (FULL) OF 'X$KZSRO' 25 23 INDEX (RANGE SCAN) OF 'I_OBJAUTH2' (NON-UNIQUE) 26 1 FIXED TABLE (FULL) OF 'X$KZSPR'
7 recursive calls 0 db block gets 6464 consistent gets 53 physical reads 0 redo size 485 bytes sent via SQL*Net to client 652 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
Jack C. Applewhite - Database Administrator
414.9715 (phone) / 935.5929 (pager)
"Rich Jesse" <rjoralist_at_society.servebeer.com>
Sent by: oracle-l-bounce_at_freelists.org
02/22/2008 12:32 PM
Please respond to
RE: Perl Issues
Interesting -- I try this and I get index hits on OBJ$, albeit with a
12K rows in the ALL_TABLES view.
What version of Oracle? This could be an issue with stats on the
tables, either having them in 9i or not having them (or having bad ones) in
p.s. I will not complain about my table layout. Ever.
> Nope. Any query against All_Tables (or DBA_Tables or User_Tables)
> in a Full Table Scan of OBJ$. Having 150,000+ tables and 230,000+
> in that one schema in the database results in about 420,000 rows in
> 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?
> 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