SQL Area
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: SQL Area

  1. #1
    Join Date
    May 2000
    Location
    Portsmouth, NH, USA
    Posts
    378

    Unhappy

    My SQL Area has really bad performance.
    Everything else looks GREAT. How can I tune ONLY the Sql Area? My shared pool sizing seems right on. Just the SQL Area is messed up. Any advice out there?

    - Magnus
    ps: I looked up some archives regarding this but nothing mentioned how to specifically tune just the SQL Area.

    some output:
    LIBRARY GETS GETHITRATI PINS PINHITRATI RELOADS INVALIDATI
    ------------ ---------- ---------- ---------- ---------- ---------- ----------
    BODY 571 .998 572 .997 0 0
    CLUSTER 685 .99 794 .982 0 0
    SQL AREA 14203 .535 62793 .778 760 7
    TABLE/PROCED 15931 .977 52876 .974 608 0
    TRIGGER 953 .993 953 .933 49 0

  2. #2
    Join Date
    Sep 2000
    Posts
    128
    SQL AREA is part of the shared pool, so only way to increase hit ratio on it is to increase the shared_pool_size to my knowledge.

    Terry

  3. #3
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    Confirm.

  4. #4
    Join Date
    May 2000
    Location
    Portsmouth, NH, USA
    Posts
    378

    Unhappy bummer


    *cry*

    I'll see what I can do.
    I can't PIN SQL stuff can I?

    - Magnus

  5. #5
    Join Date
    Sep 2000
    Posts
    128

    Unhappy

    Nope - You can only pin packages.

  6. #6
    Join Date
    Sep 2000
    Posts
    128
    Just found this on Metalink - don't know if it's relevant:

    How to pin:

    - a cursor associated with a SQL statement

    1. Issue SQL statement to load it into the shared pool.

    2. Determine the ADDRESS and HASH_VALUE of the SQL statement. This can
    usually be obtained from calling DBMS_SHARED_POOL.SIZES. For example,
    in the output from the SIZES procedure above you can determine that:

    ADDRESS = 70378A10
    HASH_VALUE = -614308548

    for the SQL statement:

    select ename from emp where empno = 7788

    If the information cannot be determined from SIZES, then check the view
    V$SQLAREA.

    3. Issue the following statement to keep the cursor:

    SQL> exec dbms_shared_pool.keep('70378A10, -614308548', 'c')

    PL/SQL procedure successfully completed.


  7. #7
    Join Date
    Jun 2000
    Posts
    295
    TerryD,

    I thought you can pin any stored objects,
    procedure, function, and package.

  8. #8
    Join Date
    Sep 2000
    Posts
    128
    Yes, you're right - you can pin any stored obect - I just wasn't sure about individual SQL statements, although the above I found on metalink seems to indicate you can.

    I'm really not sure - Never actually tried pinning anything other than Packages/Procedures myself

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