RE: Tuning Help - select condition from cdef$ where rowid=:1
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <BThomas@xxxxxxxxxx>, "John Kanagaraj" <john.kanagaraj@xxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 10 Nov 2005 15:52:08 -0500
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]
Sent: Thursday, November 10, 2005 3:30 PM
To: John Kanagaraj; oracle-l@xxxxxxxxxxxxx
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@xxxxxxx]
Sent: Thursday, November 10, 2005 1:39 PM
To: Thomas Biju; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Thomas Biju
Sent: Thursday, November 10, 2005 9:29 AM
To: oracle-l@xxxxxxxxxxxxx
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.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
