RE: Tuning Help - select condition from cdef$ where rowid=:1

Biju,  

CDEF$ and CCOL$ seem to be the base tables that hold constraint information, and CDEF$.CONDITION is the LONG column that holds the text. It seems that this application may have throttled itself by using too many constraints. If you can change the app/design see if this can be reduced.  

Now that objects stats on SYS/SYSTEM is supported in 9i, maybe you can collect them too and see what happens. Also, it is possible that your shared pool size may need to be bumped up (I am looking at the high parse times and misses). This will be apparent in the STATSPACK report under the "SGA breakdown difference" section - compare this for 'normal' periods and when this is run. Also what are your top latches?

Also check out init.ora parameters that may be deprecated between versions as well......

John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)  

Co-Author: Oracle Database 10g Insider Solutions http://www.samspublishing.com/title/0672327910  

• The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers **

  ---

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Thomas Biju Sent: Thursday, November 10, 2005 9:29 AM To: oracle-l_at_freelists.org
Subject: Tuning Help - select condition from cdef$ where rowid=:1

We recently upgraded an 8i (test) database to 9.2.0.6, the batch jobs are taking more than 4 times longer to complete in the 9i environment. When traced one of the batch jobs using DBMS_SUPPORT (waits=yes, binds=no), the most "expensive" SQL seems to be "select condition from cdef$ where rowid=:1"

These are the stats from the trace file for this statement.

select condition

from

 cdef$ where rowid=:1

call count cpu elapsed disk query current rows

• ------ -------- ---------- ---------- ---------- ----------

  ---

Parse 2328874 161.50 149.63 0 0 0 0

Execute 2328874 219.28 202.97 0 0 0 0

Fetch 2328873 161.60 145.19 0 4658097 0 2328873

• ------ -------- ---------- ---------- ---------- ----------

  ---

total 6986621 542.38 497.81 0 4658097 0 2328873

--

http://www.freelists.org/webpage/oracle-l Received on Thu Nov 10 2005 - 13:40:54 CST