RE: Perl Issues
- From: JApplewhite@xxxxxxxxxxxxx
- To: rjoralist@xxxxxxxxxxxxxxxxxxxxx
- Date: Fri, 22 Feb 2008 14:56:49 -0600
9i (9.2.0.4) 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' ;
Execution Plan
----------------------------------------------------------
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'
Statistics
----------------------------------------------------------
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
Austin I.S.D.
414.9715 (phone) / 935.5929 (pager)
"Rich Jesse" <rjoralist@xxxxxxxxxxxxxxxxxxxxx>
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
02/22/2008 12:32 PM
Please respond to
rjoralist@xxxxxxxxxxxxxxxxxxxxx
To
oracle-l@xxxxxxxxxxxxx
cc
Subject
RE: Perl Issues
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.
Rich
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.
>
> Thanks.
>
> Jack C. Applewhite - Database Administrator
--
//www.freelists.org/webpage/oracle-l
Other related posts:
