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

Ugh, that's ugly......

See:
http://asktom.oracle.com/pls/ask/f?p=4950:8:10535970414960841406::NO::F4 950_P8_DISPLAYID,F4950_P8_CRITERIA:896796299899 For a solution....

-Mark

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]

Sent: Thursday, November 10, 2005 3:30 PM To: John Kanagaraj; oracle-l_at_freelists.org Subject: RE: Tuning Help - select condition from cdef$ where rowid=:1

John,

Thanks for that tip on constraints, indeed, there are quite a few tables with more than 100 C constraints, and few tables have more than 2000 C constraints. The table has only 13 columns, the check constraints are repeated again and again (may be from imp or similar activity, not sure how we can have 6827 check constraints on a 13 column table!). Do you know an easy method or script to drop these duplicate constraints?

Biju

-----Original Message-----
From: John Kanagaraj [mailto:john.kanagaraj_at_hds.com] Sent: Thursday, November 10, 2005 1:39 PM To: Thomas Biju; oracle-l_at_freelists.org
Subject: 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

This electronic transmission and any attached files are intended solely for the person or entity to which they are addressed and may contain information that is privileged, confidential or otherwise protected from disclosure. Any review, retransmission, dissemination or other use, including taking any action concerning this information by anyone other than the named recipient, is strictly prohibited. If you are not the intended recipient or have received this communication in error, please immediately notify the sender and destroy this communication.

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l