FAQ
I have numerous sql statements in the library cache where the value for
executions is equal to 0. These statements have been loaded at various
times over the last few days. I would assume that the application is
parsing the statement, but not actually executing it, which seems odd to
me. Are there other reasons that cause the value of the executions column
to be zero? I know that executions could be zero while a statement is
executing for the first time, but many of these statements were loaded days
or hours ago - they weren't loaded a few minutes ago. I feel like I'm
missing something obvious here.
This is Oracle 10.2.0.5 on Windows 2008.

Thank you,
Jay

Search Discussions

  • Hemant K Chitale at Sep 19, 2011 at 3:49 pm
    Also look at cpu_time, disk_reads, buffer_gets etc. SQL executions that
    began but were interrupted / cancelled / killed.
    Hemant K Chitale
    http://hemantoracledba.blogspot.com
    http://hemantscribbles.blogspot.com

    sent from my smartphone
    On 19 Sep 2011 23:33, "Jay Hostetter" wrote:
    I have numerous sql statements in the library cache where the value for
    executions is equal to 0. These statements have been loaded at various
    times over the last few days. I would assume that the application is
    parsing the statement, but not actually executing it, which seems odd to
    me. Are there other reasons that cause the value of the executions column
    to be zero? I know that executions could be zero while a statement is
    executing for the first time, but many of these statements were loaded days
    or hours ago - they weren't loaded a few minutes ago. I feel like I'm
    missing something obvious here.
    This is Oracle 10.2.0.5 on Windows 2008.

    Thank you,
    Jay


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

    --
    http://www.freelists.org/webpage/oracle-l
  • Jay Hostetter at Sep 19, 2011 at 7:37 pm
    I did look at those fields as well. It turns out that the application is
    building queries on the fly and it validates the query as the statement is
    being built. So the base query gets validated, then the user adds the where
    clause, then the query is actually executed. Hence, the final query doesn't
    match the one that was originally parsed. So I have a bunch of parsed, but
    unexecuted queries. Seems like a waste of CPU time.
    Thank you,
    Jay
    On Mon, Sep 19, 2011 at 11:48 AM, Hemant K Chitale wrote:

    Also look at cpu_time, disk_reads, buffer_gets etc. SQL executions that
    began but were interrupted / cancelled / killed.

    Hemant K Chitale
    http://hemantoracledba.blogspot.com
    http://hemantscribbles.blogspot.com

    sent from my smartphone

    --
    http://www.freelists.org/webpage/oracle-l
  • Dunbar, Norman (Capgemini) at Sep 20, 2011 at 6:32 am
    Morning Jay,
    ....So I have a bunch
    of parsed, but
    unexecuted queries. Seems like a waste of CPU time.
    Not to mention that fact that each and every parse, whether eventually
    causing an execution or not, takes out a latch (like Highlanders, there
    can be only one!) and causes a bottleneck.

    I suspect the developers and/or vendor be treated to the "big stick" and
    educated in the errors of their ways.

    Good luck on that score though.


    Cheers,
    Norm.

    Norman Dunbar
    Contract Senior Oracle DBA
    Capgemini Database Build Team
    Internal : 7 28 2051
    External : 0113 231 2051


    Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

    We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
    We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

    If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
  • Niall Litchfield at Sep 20, 2011 at 12:07 pm
    Jay
    In addition to the other comments you have, I have in the past met an
    application that used a scrollable cursor for all resultsets. This turned
    out to result in a query with the rowid column prepended, the initial parse
    however was of the desired SQL
    On Tue, Sep 20, 2011 at 7:31 AM, Dunbar, Norman (Capgemini) wrote:

    Morning Jay,
    ....So I have a bunch
    of parsed, but
    unexecuted queries. Seems like a waste of CPU time.
    Not to mention that fact that each and every parse, whether eventually
    causing an execution or not, takes out a latch (like Highlanders, there
    can be only one!) and causes a bottleneck.

    I suspect the developers and/or vendor be treated to the "big stick" and
    educated in the errors of their ways.

    Good luck on that score though.


    Cheers,
    Norm.

    Norman Dunbar
    Contract Senior Oracle DBA
    Capgemini Database Build Team
    Internal : 7 28 2051
    External : 0113 231 2051


    Information in this message may be confidential and may be legally
    privileged. If you have received this message by mistake, please notify the
    sender immediately, delete it and do not copy it to anyone else.

    We have checked this email and its attachments for viruses. But you should
    still check any attachment before opening it.
    We may have to make this message and any reply to it public if asked to
    under the Freedom of Information Act, Data Protection Act or for litigation.
    Email messages and attachments sent to or from any Environment Agency
    address may also be accessed by someone other than the sender or recipient,
    for business purposes.

    If we have sent you information and you wish to use it please read our
    terms and conditions which you can get by calling us on 08708 506 506. Find
    out more about the Environment Agency at www.environment-agency.gov.uk
    --
    http://www.freelists.org/webpage/oracle-l


    --
    Niall Litchfield
    Oracle DBA
    http://www.orawin.info


    --
    http://www.freelists.org/webpage/oracle-l
  • Niall Litchfield at Sep 20, 2011 at 12:10 pm
    grrr, trackpads causing me to hit send...
    The result was that I got a load of statements of the form

    select <col list> from table where <conditions>

    all of which had zero executions, but then there were also a lot of

    select rowid,<col list> from table where <conditions>

    which all had sensible execution counts (well they would have if they'd used
    bind variables ~ sigh )
    The pattern shows up very well in a 10046 trace.
    On Tue, Sep 20, 2011 at 1:06 PM, Niall Litchfield wrote:

    Jay

    In addition to the other comments you have, I have in the past met an
    application that used a scrollable cursor for all resultsets. This turned
    out to result in a query with the rowid column prepended, the initial parse
    however was of the desired SQL


    On Tue, Sep 20, 2011 at 7:31 AM, Dunbar, Norman (Capgemini) <
    norman.dunbar.capgemini@environment-agency.gov.uk> wrote:
    Morning Jay,
    ....So I have a bunch
    of parsed, but
    unexecuted queries. Seems like a waste of CPU time.
    Not to mention that fact that each and every parse, whether eventually
    causing an execution or not, takes out a latch (like Highlanders, there
    can be only one!) and causes a bottleneck.

    I suspect the developers and/or vendor be treated to the "big stick" and
    educated in the errors of their ways.

    Good luck on that score though.


    Cheers,
    Norm.

    Norman Dunbar
    Contract Senior Oracle DBA
    Capgemini Database Build Team
    Internal : 7 28 2051
    External : 0113 231 2051


    Information in this message may be confidential and may be legally
    privileged. If you have received this message by mistake, please notify the
    sender immediately, delete it and do not copy it to anyone else.

    We have checked this email and its attachments for viruses. But you should
    still check any attachment before opening it.
    We may have to make this message and any reply to it public if asked to
    under the Freedom of Information Act, Data Protection Act or for litigation.
    Email messages and attachments sent to or from any Environment Agency
    address may also be accessed by someone other than the sender or recipient,
    for business purposes.

    If we have sent you information and you wish to use it please read our
    terms and conditions which you can get by calling us on 08708 506 506. Find
    out more about the Environment Agency at www.environment-agency.gov.uk
    --
    http://www.freelists.org/webpage/oracle-l


    --
    Niall Litchfield
    Oracle DBA
    http://www.orawin.info


    --
    Niall Litchfield
    Oracle DBA
    http://www.orawin.info


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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedSep 19, '11 at 3:33p
activeSep 20, '11 at 12:10p
posts6
users4
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase