Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: extremely high "consistent gets" count on query of all_constraints view

Re: extremely high "consistent gets" count on query of all_constraints view

From: mike <sjmnet_at_gmail.com>
Date: 19 May 2006 13:41:56 -0700
Message-ID: <1148071316.086759.77260@g10g2000cwb.googlegroups.com> 






TKPROF: Release 9.2.0.6.0 - Production on Tue May 2 15:36:40 2006



Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.



Trace file:


\\uspmuusryeorad1\d$\oraclesvrdba\id13\udump\id13_ora_3144.trc
Sort options: exeela  fchela



count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for
update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

SELECT     p_constraint_table.CONSTRAINT_NAME
,          p_constraint_table.OWNER
,          p_constraint_table.TABLE_NAME
,          f_constraint_table.CONSTRAINT_NAME
,          f_constraint_table.OWNER
,          f_constraint_table.TABLE_NAME
FROM       ALL_CONSTRAINTS f_constraint_table
,          ALL_CONSTRAINTS p_constraint_table


WHERE


f_constraint_table.R_CONSTRAINT_NAME=p_constraint_table.CONSTRAINT_NAME
AND        p_constraint_table.CONSTRAINT_TYPE='P'
AND        f_constraint_table.CONSTRAINT_TYPE='R'
AND        f_constraint_table.OWNER ='GPW'
AND       ( f_constraint_table.TABLE_NAME='FCI_AGREEMENT'      or


f_constraint_table.TABLE_NAME='FCI_CONTRACT'  )



call     count       cpu    elapsed       disk      query    current


    rows


------- ------  -------- ---------- ---------- ---------- ----------



Parse        1      0.06       0.07          0          3          0
       0
Execute      1      0.00       0.00          0          0          0
       0
Fetch        2    114.64     124.88          0    7738081          0
       1




------- ------  -------- ---------- ---------- ---------- ----------



total        4    114.70     124.95          0    7738084          0
       1





Misses in library cache during parse: 1


Optimizer goal: CHOOSE


Parsing user id: 30



Rows     Row Source Operation

-------  ---------------------------------------------------
      1  FILTER
      1   NESTED LOOPS OUTER
      1    NESTED LOOPS OUTER
      1     FILTER
    749      NESTED LOOPS OUTER
    749       NESTED LOOPS OUTER
    749        NESTED LOOPS
  70406         NESTED LOOPS OUTER
  70406          NESTED LOOPS
3723279           NESTED LOOPS
    749            NESTED LOOPS
    749             NESTED LOOPS OUTER
    749              NESTED LOOPS OUTER
    749               NESTED LOOPS
    749                NESTED LOOPS OUTER
    749                 NESTED LOOPS OUTER
    749                  NESTED LOOPS
    749                   NESTED LOOPS OUTER
    749                    NESTED LOOPS
      1                     TABLE ACCESS BY INDEX ROWID USER$
      1                      INDEX UNIQUE SCAN I_USER1 (object id 44)
    749                     TABLE ACCESS FULL CDEF$
    726                    INDEX UNIQUE SCAN I_OBJ1 (object id 36)
    749                   TABLE ACCESS BY INDEX ROWID OBJ$
    749                    INDEX UNIQUE SCAN I_OBJ1 (object id 36)
      0                  INDEX UNIQUE SCAN I_OBJ1 (object id 36)
      0                 TABLE ACCESS BY INDEX ROWID CON$
      0                  INDEX UNIQUE SCAN I_CON2 (object id 49)
    749                TABLE ACCESS BY INDEX ROWID CON$
    749                 INDEX UNIQUE SCAN I_CON2 (object id 49)
      0               TABLE ACCESS CLUSTER USER$
      0                INDEX UNIQUE SCAN I_USER# (object id 11)
      0              TABLE ACCESS CLUSTER USER$
      0               INDEX UNIQUE SCAN I_USER# (object id 11)
    749             TABLE ACCESS CLUSTER USER$
    749              INDEX UNIQUE SCAN I_USER# (object id 11)
3723279            TABLE ACCESS BY INDEX ROWID CON$
3723279             INDEX RANGE SCAN I_CON1 (object id 48)
  70406           TABLE ACCESS BY INDEX ROWID CDEF$
3723279            INDEX UNIQUE SCAN I_CDEF1 (object id 50)
  70406          INDEX UNIQUE SCAN I_OBJ1 (object id 36)
    749         TABLE ACCESS BY INDEX ROWID OBJ$
  70406          INDEX UNIQUE SCAN I_OBJ1 (object id 36)
    749        INDEX UNIQUE SCAN I_OBJ1 (object id 36)
    749       TABLE ACCESS BY INDEX ROWID CON$
    749        INDEX UNIQUE SCAN I_CON2 (object id 49)
      1     TABLE ACCESS CLUSTER USER$
      1      INDEX UNIQUE SCAN I_USER# (object id 11)
      1    TABLE ACCESS CLUSTER USER$
      1     INDEX UNIQUE SCAN I_USER# (object id 11)
      1   NESTED LOOPS
     19    FIXED TABLE FULL X$KZSRO
      1    INDEX RANGE SCAN I_OBJAUTH2 (object id 109)
      0   FIXED TABLE FULL X$KZSPR
      1   NESTED LOOPS
     19    FIXED TABLE FULL X$KZSRO
      1    INDEX RANGE SCAN I_OBJAUTH2 (object id 109)
      0   FIXED TABLE FULL X$KZSPR


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total


Waited




select text


from


 view$ where rowid=:1




call     count       cpu    elapsed       disk      query    current


    rows


------- ------  -------- ---------- ---------- ---------- ----------



Parse        2      0.00       0.00          0          0          0
       0
Execute      2      0.01       0.00          0          0          0
       0
Fetch        2      0.00       0.00          0          4          0
       2




------- ------  -------- ---------- ---------- ---------- ----------



total        6      0.01       0.00          0          4          0
       2





Misses in library cache during parse: 1


Optimizer goal: CHOOSE


Parsing user id: SYS   (recursive depth: 1)



Rows     Row Source Operation

-------  ---------------------------------------------------
      1  TABLE ACCESS BY USER ROWID VIEW$

********************************************************************************





select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname,
  o.dataobj#,o.flags


from


 obj$ o where o.obj#=:1




call     count       cpu    elapsed       disk      query    current


    rows


------- ------  -------- ---------- ---------- ---------- ----------



Parse        1      0.00       0.00          0          0          0
       0
Execute      1      0.00       0.00          0          0          0
       0
Fetch        1      0.00       0.00          0          3          0
       1




------- ------  -------- ---------- ---------- ---------- ----------



total        3      0.00       0.00          0          3          0
       1





Misses in library cache during parse: 1


Optimizer goal: CHOOSE


Parsing user id: SYS   (recursive depth: 2)





alter session  SET EVENTS '10046 trace name context off'




call     count       cpu    elapsed       disk      query    current


    rows


------- ------  -------- ---------- ---------- ---------- ----------



Parse        1      0.00       0.00          0          0          0
       0
Execute      1      0.00       0.00          0          0          0
       0
Fetch        0      0.00       0.00          0          0          0
       0




------- ------  -------- ---------- ---------- ---------- ----------



total        2      0.00       0.00          0          0          0
       0





Misses in library cache during parse: 1


Optimizer goal: CHOOSE


Parsing user id: 30





alter session  SET EVENTS '10046 trace name context forever, level 12'




call     count       cpu    elapsed       disk      query    current


    rows


------- ------  -------- ---------- ---------- ---------- ----------



Parse        0      0.00       0.00          0          0          0
       0
Execute      1      0.00       0.00          0          0          0
       0
Fetch        0      0.00       0.00          0          0          0
       0




------- ------  -------- ---------- ---------- ---------- ----------



total        1      0.00       0.00          0          0          0
       0





Misses in library cache during parse: 0


Misses in library cache during execute: 1
Optimizer goal: CHOOSE


Parsing user id: 30



Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total


Waited





OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS


call     count       cpu    elapsed       disk      query    current


    rows


------- ------  -------- ---------- ---------- ---------- ----------



Parse        2      0.06       0.07          0          3          0
       0
Execute      3      0.00       0.00          0          0          0
       0
Fetch        2    114.64     124.88          0    7738081          0
       1




------- ------  -------- ---------- ---------- ---------- ----------



total        7    114.70     124.95          0    7738084          0
       1





Misses in library cache during parse: 2


Misses in library cache during execute: 1



Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total


Waited




OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS


call     count       cpu    elapsed       disk      query    current


    rows


------- ------  -------- ---------- ---------- ---------- ----------



Parse        3      0.00       0.00          0          0          0
       0
Execute      3      0.01       0.00          0          0          0
       0
Fetch        3      0.00       0.00          0          7          0
       3




------- ------  -------- ---------- ---------- ---------- ----------



total        9      0.01       0.00          0          7          0
       3





Misses in library cache during parse: 2



    3  user  SQL statements in session.


    3  internal SQL statements in session.
    6  SQL statements in session.




Trace file:


\\uspmuusryeorad1\d$\oraclesvrdba\id13\udump\id13_ora_3144.trc
Trace file compatibility: 9.02.00


Sort options: exeela  fchela

       1  session in tracefile.
       3  user  SQL statements in trace file.
       3  internal SQL statements in trace file.
       6  SQL statements in trace file.
       5  unique SQL statements in trace file.
     157  lines in trace file.


Received on Fri May 19 2006 - 15:41:56 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US