RE: Tuning Help - select condition from cdef$ where rowid=:1
- From: "John Kanagaraj" <john.kanagaraj@xxxxxxx>
- To: <BThomas@xxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 10 Nov 2005 11:38:43 -0800
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
