-
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
-
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
-
-
bummer
*cry*
I'll see what I can do.
I can't PIN SQL stuff can I?
- Magnus
-
Nope - You can only pin packages.
-
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.
-
TerryD,
I thought you can pin any stored objects,
procedure, function, and package.
-
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
