FAQ
Greeting,

In a two node RAC cluster running Oracle 10.2.0.3.0 on RHEL4 (I haven't checked a single node instance) awrrpts are showing a negative execute to parse ratio. This implies to me that there are SQL statements being parsed but not executed. What I am seeing by looking at v$sql and querying parse_calls and executions for sql statements is that for non system users (SYS,SYSMAN, etc.) the number of parse_calls is always greater than the number of executions, in the majority of cases the parse_calls are 1 more than the number of executions and for 5 other statements the difference is > 2.

Is this to be expected? This implies that a statement is being parsed at least once without being executed. Is that necessary for Oracle to determine the execution plan or something else? Am I about to learn something here?

Thanks.

Bill Wagman
Univ. of California at Davis
IET Campus Data Center
wjwagman_at_ucdavis.edu
(530) 754-6208

Search Discussions

  • LS Cheng at Mar 9, 2009 at 10:58 pm
    Hello Bill

    I dont think that is an Oracle issue, I have seen this before in some client
    server applications made by some tool called Windev and the problem was
    caused by Windev. I am not sure what was wrong but the developers changed
    their code the extra parse then went away.

    I observed this behaviour tracing several sessions with 10046.

    Thanks

    --
    LSC
    On Mon, Mar 9, 2009 at 10:12 PM, William Wagman wrote:

    Greeting,

    In a two node RAC cluster running Oracle 10.2.0.3.0 on RHEL4 (I haven't
    checked a single node instance) awrrpts are showing a negative execute to
    parse ratio. This implies to me that there are SQL statements being parsed
    but not executed. What I am seeing by looking at v$sql and querying
    parse_calls and executions for sql statements is that for non system users
    (SYS,SYSMAN, etc.) the number of parse_calls is always greater than the
    number of executions, in the majority of cases the parse_calls are 1 more
    than the number of executions and for 5 other statements the difference is >
    2.

    Is this to be expected? This implies that a statement is being parsed at
    least once without being executed. Is that necessary for Oracle to determine
    the execution plan or something else? Am I about to learn something here?

    Thanks.

    Bill Wagman
    Univ. of California at Davis
    IET Campus Data Center
    wjwagman_at_ucdavis.edu
    (530) 754-6208

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

    --
    http://www.freelists.org/webpage/oracle-l
  • Yong Huang at Mar 10, 2009 at 6:13 pm
    This implies that a statement is being parsed at least once without being executed.
    At least in the case of parallel executions, it can happen. I answered this same question at a Chinese Oracle site:

    "One important case I want to add to what Tom Kyte says is that if the SQL is run in parallel, the SQL will be parsed multiple times by the query coordinator and all parallel slaves I assume, but this particular SQL itself is only executed by the query coordinator, because after each slave parses it, the slave creates its only SQL with slightly different sql_text (and therefore hash_value and sql_id)."

    I posted it at http://www.itpub.net/archiver/tid-1112877.html

    Yong Huang

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedMar 9, '09 at 9:12p
activeMar 10, '09 at 6:13p
posts3
users3
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase