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

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 CDEF$

The summary from the trace file shows this:

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse     4574   4587.43    4592.92          0      49524          2           0
Execute   9092    135.40     139.18        324      34430      17285        2806
Fetch     6411    239.32     326.33     367909    1003318        905        6998
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    20077   4962.15    5058.43     368233    1087272      18192        9804

Misses in library cache during parse: 4574
Misses in library cache during execute: 1665


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse   2344604    170.09     158.22          9       1466         12           0
Execute 2408815    237.74     221.85         28       4610       5781        1826
Fetch   2551702    180.34     162.90         83    5080195        528     2492330
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   7305121    588.17     542.99        120    5086271       6321     2494156

Misses in library cache during parse: 456
Misses in library cache during execute: 157

 5298  user  SQL statements in session.
2343981  internal SQL statements in session.
2349279  SQL statements in session.

The statspack report also shows this and another recursive statement as the top two "buffer gets per execute" statement. 
select intcol#,nvl(pos#,0),col# from ccol$ where con#=:1

All the tables/indexes except owned by SYS are analyzed using DBMS_STATS. 

What are the next steps for me to diagnose the issue? How to eliminate the high parse time (do not think the application is using bind
variables, I believe they generate dynamic SQL). Any help much appreciated.

The shared_pool_size is set to 150MB and db_cache_size is 500MB. The session_cached_cursors is set to 100. I believe the program executed is
from Oracle Forms. 

Thanks,
Biju Thomas
Database Administrator

 



_____________________________________________________________________________________________________________

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