FAQ
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.

Search Discussions

  • Egor Starostin at Nov 10, 2005 at 12:32 pm

    These are the stats from the trace file for this statement.

    select condition
    from
    cdef$ where rowid=:1
    [...]
    What are the next steps for me to diagnose the issue?
    Here's the next step (not a solution though):
    Find the parent(s) for this recursive statement. tkprof can't display
    recursive relationship between statements, for this task you need to
    use a profiler which can display it. Like OraSRP, for example.
  • GovindanK at Nov 10, 2005 at 12:37 pm
    On Thu, 10 Nov 2005 11:29:14 -0600, "Thomas Biju"
    said:
    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



    Thanks,
    Biju Thomas
    Database Administrator
    Did you do a FULL statistics gathering or SAMPLE? First time after patch
    it should be full. Also how much time it took with 8i?

    HTH

    GovindanK
  • Thomas Biju at Nov 10, 2005 at 12:41 pm
    Thank you. The trace file I have is 1.3GB. I downloaded the trace analyzer from Oracle. And its analysis is going on since 10PM last night
    (exec trca$i.trace_analyzer). Does this help to find the parent?

    Did full stats on all schema tables (COMPUTE) except 4 large ones where we did 10% sample.

    With 8i this particular job ran for 22 minutes, in 9i it takes about 4 hours.

    -----Original Message-----
    From: Egor Starostin
    Sent: Thursday, November 10, 2005 12:30 PM
    To: Thomas Biju
    Cc: oracle-l_at_freelists.org
    Subject: Re: Tuning Help - 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
    [...]
    What are the next steps for me to diagnose the issue?
    Here's the next step (not a solution though):
    Find the parent(s) for this recursive statement. tkprof can't display
    recursive relationship between statements, for this task you need to
    use a profiler which can display it. Like OraSRP, for example.

    --
    Egor
    http://www.oracledba.ru

    _____________________________________________________________________________________________________________

    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
  • GovindanK at Nov 10, 2005 at 12:57 pm
    On Thu, 10 Nov 2005 12:39:41 -0600, "Thomas Biju"
    said:
    Thank you. The trace file I have is 1.3GB. I downloaded the trace
    analyzer from Oracle. And its analysis is going on since 10PM last night
    (exec trca$i.trace_analyzer). Does this help to find the parent?

    Did full stats on all schema tables (COMPUTE) except 4 large ones where
    we did 10% sample.

    With 8i this particular job ran for 22 minutes, in 9i it takes about 4
    hours.


    -----Original Message-----
    From: Egor Starostin
    Sent: Thursday, November 10, 2005 12:30 PM
    To: Thomas Biju
    Cc: oracle-l_at_freelists.org
    Subject: Re: Tuning Help - 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
    [...]
    What are the next steps for me to diagnose the issue?
    Here's the next step (not a solution though):
    Find the parent(s) for this recursive statement. tkprof can't display
    recursive relationship between statements, for this task you need to
    use a profiler which can display it. Like OraSRP, for example.


    --
    Egor
    http://www.oracledba.ru
    set echo on;
    As a Temporary / Quick Fix this is what we did (later at the end of the
    day took maintenance window and did a FULL stats gathering on big
    tables) for the schemas the production application was connecting. You
    can do this for your batch user.

    CREATE OR REPLACE TRIGGER On_Logon_Trigger_dba2
    AFTER LOGON ON SCHEMA

    BEGIN

    execute immediate 'alter session set optimizer_mode=rule';
    EXCEPTION

    WHEN OTHERS THEN NULL;

    END;

    /

    HTH

    GovindanK
  • GovindanK at Nov 10, 2005 at 1:02 pm
    The Previous entry exceeded size limit. Hence reposting.

    On Thu, 10 Nov 2005 12:39:41 -0600, "Thomas Biju"
    said:
    Thank you. The trace file I have is 1.3GB. I downloaded the trace
    analyzer from Oracle. And its analysis is going on since 10PM last night
    (exec trca$i.trace_analyzer). Does this help to find the parent?

    Did full stats on all schema tables (COMPUTE) except 4 large ones where
    we did 10% sample.

    With 8i this particular job ran for 22 minutes, in 9i it takes about 4
    hours.
    ALL tables to have FULL atleast once. We had similar issue when we moved
    from 9201 to 9206. The generated plan for the query would be different
    if FULL is not done atleast once.

    As a Temporary / Quick Fix this is what we did (later at the end of the
    day took maintenance window and did a FULL stats gathering on big
    tables) for the schemas the production application was connecting. You
    can do this for your batch user.

    set echo on;
    CREATE OR REPLACE TRIGGER On_Logon_Trigger_dba2
    AFTER LOGON ON SCHEMA

    BEGIN

    execute immediate 'alter session set optimizer_mode=rule';
    EXCEPTION

    WHEN OTHERS THEN NULL;

    END;

    /

    HTH

    GovindanK
  • Egor Starostin at Nov 10, 2005 at 1:03 pm

    Thank you. The trace file I have is 1.3GB. I downloaded the trace analyzer from Oracle. And its analysis is going on since 10PM last night
    (exec trca$i.trace_analyzer). Does this help to find the parent?
    Afaik, no. Trace Analyzer TRCANLZR do not display relationship between
    statements.
  • John Kanagaraj at Nov 10, 2005 at 1:40 pm
    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_at_freelists.org
    On Behalf Of Thomas Biju
    Sent: Thursday, November 10, 2005 9:29 AM
    To: oracle-l_at_freelists.org
    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

    Execute 2328874 219.28 202.97 0 0 0

    Fetch 2328873 161.60 145.19 0 4658097 0
    2328873

    ------ -------- ---------- ---------- ---------- ----------

    total 6986621 542.38 497.81 0 4658097 0
    2328873
  • A. Bardeen at Nov 10, 2005 at 2:28 pm
    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 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
  • Oracle-l-bounce_at_freelists.org at Nov 10, 2005 at 2:31 pm
    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
    Sent: Thursday, November 10, 2005 1:39 PM
    To: Thomas Biju; oracle-l_at_freelists.org
    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_at_freelists.org
    On Behalf Of Thomas Biju
    Sent: Thursday, November 10, 2005 9:29 AM
    To: oracle-l_at_freelists.org
    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

    Execute 2328874 219.28 202.97 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.

    --
    http://www.freelists.org/webpage/oracle-l
  • Bobak, Mark at Nov 10, 2005 at 2:55 pm
    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_at_freelists.org

    Sent: Thursday, November 10, 2005 3:30 PM
    To: John Kanagaraj; oracle-l_at_freelists.org
    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
    Sent: Thursday, November 10, 2005 1:39 PM
    To: Thomas Biju; oracle-l_at_freelists.org
    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_at_freelists.org
    On Behalf Of Thomas Biju
    Sent: Thursday, November 10, 2005 9:29 AM
    To: oracle-l_at_freelists.org
    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

    Execute 2328874 219.28 202.97 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.

    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Thomas Biju at Nov 14, 2005 at 9:48 pm
    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 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedNov 10, '05 at 11:33a
activeNov 14, '05 at 9:48p
posts12
users7
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase