tuning private SQL area?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: tuning private SQL area?

  1. #1
    Join Date
    Mar 2001
    Posts
    40

    Question

    Why does my PARSE times always equal to EXECUTE times when I query data using the same sql statement with SQLPLUS? The initiation parameter OPEN_CURSORS equals to 100, is it too small? How can I tune it?
    Any suggestions will be appreciated.

  2. #2
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433
    EXECUTE TIME or elapse time , I think parse time is no relationship with execute time

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    http://asktom.oracle.com/pls/ask/f?p...0_P8_DISPLAYID,F4950_P8_CRITERIA:1137834554916,{soft}%20and%20{parse}

    basically sqlplus doesnt use cache cursors and causes soft parse everytime u execute the same query

  4. #4
    Join Date
    Jul 2000
    Location
    india
    Posts
    213

    Thumbs up

    thanks pando for ur help and for the link ...

    pras

  5. #5
    Join Date
    Mar 2000
    Location
    Chennai.Tamilnadu.India.
    Posts
    658

    Pvt SQL Area & Shared SQL Area

    Hi, 11th April 2001 12:03 hrs chennai

    There is a relationship between the No pf parse call to Execute calls .We have to find the solution for 2 categories as mentioned below.

    Identifying Unnecessary Parse Calls and Reducing Unnecessary Parse Calls should be done.

    Increasing the open_cursors parameter can be based on
    -----------------------------------------------------------------
    Find from v$sysstat or v$sessstat the name 'opened cursors cumulative' this can help you to query and increase the open_cursors parameter.

    (or) if you find the following error

    ORA-28554 pass-through SQL: out of cursors
    Cause: The maximum number of open cursors has been exceeded.

    Tuning Pvt SQL Area
    =============

    1)By setting the below init parameters.

    HOLD_CURSOR = yes
    RELEASE_CURSOR = no
    MAXOPENCURSORS = value

    Tuning Shared SQL Area
    ================
    Parse

    If an application makes a parse call for a SQL statement and the parsed representation of the statement does not already exist in a shared SQL area in the library cache, Oracle parses the statement and allocates a shared SQL area. You may be able to reduce library cache misses on parse calls by ensuring that SQL statements can share a shared SQL area whenever possible.

    Execute

    If an application makes an execute call for a SQL statement and the shared SQL area containing the parsed representation of the statement has been deallocated from the library cache to make room for another statement, Oracle implicitly reparses the statement, allocates a new shared SQL area for it, and executes it. You may be able to reduce library cache misses on execution calls by allocating more memory to the library cache.

    For more information look oracle doc.

    Cheers

    Padmam
    Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it

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