Results 1 to 7 of 7

Thread: SQL Parsing

  1. #1
    Join Date
    Mar 2006
    Posts
    2

    Unanswered: SQL Parsing

    Hi,
    I have done some experiment regarding parsing sql stmts.Whenever same query is repeated then parsing is by passed....only execution value will be increased by 1 in v$sqlarea view....but i tried something like below...but here both parse_calls and executions values are get increased by 1.I dont know what should i do....can u help me?

    ################################################## ########################
    SQL> SELECT priority, value, group_id FROM h_group, s_c_group WHERE h_group.id = s_c_group.group_id;

    no rows selected

    SQL> select parse_calls,executions from v$sqlarea where sql_text='SELECT priority, value, group_id FROM h_group, s_c_group WHERE h_group.id = s_c_group.group_id';

    PARSE_CALLS EXECUTIONS
    ----------- ----------
    1 1

    SQL> SELECT priority, value, group_id FROM h_group, s_c_group WHERE h_group.id = s_c_group.group_id;

    no rows selected

    SQL> select parse_calls,executions from v$sqlarea where sql_text='SELECT priority, value, group_id FROM h_group, s_c_group WHERE h_group.id = s_c_group.group_id';

    PARSE_CALLS EXECUTIONS
    ----------- ----------
    2 2

    ################################################## ########################

    Thanks N Regards,
    Raja

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Forget about it and get to work!
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    You parsed it two times and you executed it two times as well. SQL*Plus hard parse querys everytime you run them.

  4. #4
    Join Date
    Feb 2006
    Posts
    15
    Quote Originally Posted by JMartinez
    SQL*Plus hard parse querys everytime you run them.
    Can I ask what you mean by this?

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    What part of "SQL*Plus hard parse querys everytime you run them." do you not understand?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Here's the technical explanation from Steve Adams, basically the parse_calls columns is not the same as hard parses.

    In PL/SQL, Pro*C and many other environments it is possible to explicitly open, parse, execute and close cursors. If an SQL statement is parsed in a cursor and then executed repeatedly without closing the cursor or parsing another statement in it, then the V$SQLAREA statistics will show many more EXECUTIONS than PARSE_CALLS for that statement. If statements are never reused, then EXECUTIONS and PARSE_CALLS are normally identical. Note that the count of parse calls includes hits in the session cursor cache, which don't even touch the shared cursor. This is done by updating the statistics when the shared cursor is unpinned after execution.

    Alan

  7. #7
    Join Date
    Mar 2006
    Posts
    2
    Hi,
    I got the answer.....there are two types of parsing soft n hard......if the parse_calls == executions then it means that sql is hard parsed 1st time and n-1 times it is soft parsed......if you see with autotrace....then n-1 times the number of recursive call is very less when compared to first time of that query execution.

    Many Thanks,
    Raja

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •