DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: misses in librarycache during parse

  1. #1
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    Hi Freinds..

    I have three questions.

    1.)

    One thing I don't understand is how one statement can have 2,3 or 4 hard parses.

    I traced a session to identify sql performance issues.
    One thing I noticed was that many statements had a misses in library cache during parse of 2, 3 and 4. I would expect this to be 1 (only reload once if text is not already there)

    These are considered 'hard parses' i.e space in the shared pool has to be made available and cpu is consumed.

    2.)
    How can the amount of hard parses be reduced ?

    I understand that setting session_cached_cursors and increasing shared pool would help, but I have limited resources and the reloads/pins ratio is below 1% anyway.

    Can anyone gve me pointers on how the sql can be changed to reduce hard parses ?

    3.)

    When timed statistics are set to true, can cpu time and elapsed time be trusted ? Most of the statements which had many hard parses had a very low cpu time and elapsed time. Considering my reloads/pins ratio is low (0.3%) Does this mean that I can ignore these hard parses as a performance issue?

    Thanks

    Suresh

    BTW, most of the problems with performance identified by the traced session were full table scans and massive buffer gets numbers. I am now attempting to establish whether hard parses are a performance problem.
    Once you have eliminated all of the impossible,
    whatever remains however improbable,
    must be true.

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Make sure your query uses the bind variables. Then if you cannot control how the query gets defined, then set

    ALTER SYSTEM SET CURSOR_SHARING=FORCE;

    make the changes in the init.ora file too
    CURSOR_SHARING=FORCE

    This would force cursor sharing but would increase the soft parses too.

    Sam

    [Edited by sambavan on 10-09-2001 at 05:03 PM]
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Dec 1999
    Location
    Cincinnati, Ohio USA
    Posts
    99
    Be care with this, we had some major bugs in Oracle 8.1.6.2 and ended up not being able to use it.
    Doug

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