RE: Tuning Help - select condition from cdef$ where rowid=:1
- From: "Thomas Biju" <BThomas@xxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 14 Nov 2005 21:46:27 -0600
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@xxxxxxxxxxxxx on behalf of A. Bardeen
Sent: Thu 11/10/2005 2:26 PM
To: oracle-l@xxxxxxxxxxxxx
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.
1. Reduce the number of check constraints - not always feasible, but worth
investigating
2. Set OPTIMIZER_FEATURES_ENABLE to 8.1.7 - not what I would recommend, but
an option
nonetheless
3. 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...)
4. 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@xxxxxxxxxx> 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.
>
--
//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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
