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

From: Thomas Biju <BThomas_at_br-inc.com>
Date: Mon, 14 Nov 2005 21:46:27 -0600
Message-ID: <DBB7FBD3841A45458F8BAB3D7BE2BF8302EEDDEB@FTWP57NS.br-inc.net>

Update on posting:

Thank you all for the great suggestions and tips.

After reducing the number of check constraints from 32000+ to 6200, the database parse times have reduced tremendously. Since Friday was a holiday (Canada), could not run many jobs, what we ran today appear to be working ok.

FYI... To remove the duplicate check constraints, generated a script for all SYS_ check constraints using the dba_constraints view, then used "sort -u" to get unique "alter table add constraint" statements. Dropped all SYS_ check constraints from the schema and created them using the script. Since I had the luxury of dropping and recreating the constraints, took the easy way.

Thanks,
Biju Thomas
Database Administrator

From: oracle-l-bounce_at_freelists.org on behalf of A. Bardeen Sent: Thu 11/10/2005 2:26 PM
To: oracle-l_at_freelists.org
Subject: Re: Tuning Help - select condition from cdef$ where rowid=:1

Biju,

This is a result of a change to the CBO in 9.2 and affects queries against tables that have check constraints.

I can think of at least four options, not all of which may be implemented in your case, but mentioning them anyway.

Reduce the number of check constraints - not always feasible, but worth investigating
Set OPTIMIZER_FEATURES_ENABLE to 8.1.7 - not what I would recommend, but an option nonetheless
Use a RULE hint - may not be possible if the sql can't be changed or the underlying query objects force the CBO to be used (e.g. degree of parallelism, partitioning etc...)
Set event 10195 to disable this option for the CBO (see Note 271999.1 "EVENT: 10195 "Disable generation of predicates from CHECK constraints" - Reference Note"

HTH,

Anita
- Thomas Biju <BThomas_at_br-inc.com> wrote:

> 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






_____________________________________________________________________________________________________________

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

Received on Mon Nov 14 2005 - 21:48:32 CST