DBAsupport.com Forums - Powered by vBulletin
Page 1 of 5 123 ... LastLast
Results 1 to 10 of 42

Thread: soft pare versus hard parse

  1. #1
    Join Date
    Oct 2003
    Posts
    312

    soft pare versus hard parse

    based on the result below:

    OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 4697 5.01 7.46 0 0 0 0
    Execute 3897 12.96 276.84 457 1490 15037 704
    Fetch 3092 2.00 1.95 0 18592 0 921
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 11686 19.98 286.25 457 20082 15037 1625

    Misses in library cache during parse: 23


    OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 1407 0.04 0.07 0 0 0 0
    Execute 1407 0.21 0.29 0 1404 1409 702
    Fetch 705 5.26 120.24 0 2113 702 704
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 3519 5.53 120.60 0 3517 2111 1406

    Misses in library cache during parse: 1

    shared pool was aged out and it had to parse from disk which is bad.
    I have too many hard parse, I have been reading some of the post in asktom and he has mentioned that try to increase session_cached_cursor to some positive number, right now, it's =0 on my database.

    Any inputs???

    as always, thanks for everything
    Last edited by learning_bee; 12-15-2004 at 11:28 AM.

  2. #2
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Shoot the developers for not using bind variables.

  3. #3
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334

    Re: soft pare versus hard parse

    Originally posted by learning_bee
    based on the result below:

    OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 4697 5.01 7.46 0 0 0 0
    Execute 3897 12.96 276.84 457 1490 15037 704
    Fetch 3092 2.00 1.95 0 18592 0 921
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 11686 19.98 286.25 457 20082 15037 1625

    Misses in library cache during parse: 2539


    Any inputs???

    as always, thanks for everything
    parsing more than you execute - fantastic

  4. #4
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    you may want to pin frequently used procs
    There are three kinds of lies: Lies, damned lies, and benchmarks...

    Unix is user friendly. It's just very particular about who it's friends are.

    Oracle DBA

  5. #5
    Join Date
    Oct 2003
    Posts
    312
    I thought the same thing and the users disagree and they said it's not the bind variable issues. beside Oracle doesn't use the same execution plan so that it has to parse the new one, what is the other reasons that could make the high parse??? could it be locking issues???

    How can I prove to these stubbron users.
    Last edited by learning_bee; 12-15-2004 at 06:21 PM.

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by learning_bee
    ... beside Oracle doesn't use the same execution plan so that it has to parse the new one...
    What do you/they mean by this?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    Originally posted by learning_bee
    I thought the same thing and the users disagree and they said it's not the bind variable issues. beside Oracle doesn't use the same execution plan so that it has to parse the new one, what is the other reasons that could make the high parse??? could it be locking issues???

    How can I prove to these stubbron users.
    high parse = lack of bind variables

  8. #8
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Originally posted by davey23uk
    high parse = lack of bind variables
    I would add to that:

    high parse = dynamic sql with concatenation

    And if that is the case, then I will agree with the phrase, "Shoot the developer!!!"

  9. #9
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142

    Question

    Hang on! According to T.Kyte 1-on-1, the PARSE count doesn't distinguish between hard & soft parses - total 4967.

    "Misses in library cache during parse: 23" says that 4944 of them WERE in the cache - i.e. soft parses. Sounds good to me.

  10. #10
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Originally posted by DaPi
    Hang on! According to T.Kyte 1-on-1, the PARSE count doesn't distinguish between hard & soft parses - total 4967.

    "Misses in library cache during parse: 23" says that 4944 of them WERE in the cache - i.e. soft parses. Sounds good to me.
    Well read.

Posting Permissions

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


Click Here to Expand Forum to Full Width