SQL Area

[jgmagnus]

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

[TerryD]

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

[Halo]

Confirm.

[jgmagnus]

*cry*

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

- Magnus

[TerryD]

Nope - You can only pin packages.

[TerryD]

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.

[sysdba]

TerryD,

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

[TerryD]

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