Oracle Database Discussions

Hello all,

When I was investigating a query using extended trace that was taking 10 mins to complete in production, I noticed the following.


Production Environment
--------------------------------
OS : HP-UX 11
Database: Oracle 10.2
Auditing: ON (actually heavy, about 177 policies are ON besides 57 VPDs)
Datawarehouse using Business Objects


Observation from tkprof output
---------------------------------------------


OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.44 0.42 0 0 0 0
Execute 2 0.01 0.00 0 0 0 0
Fetch 84 4.34 5.05 1303 3522 0 1235
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 88 4.79 5.48 1303 3522 0 1235

Misses in library cache during parse: 1

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 85 0.00 0.00
SQL*Net message from client 85 66.91 66.95
SQL*Net more data from client 1 0.00 0.00
db file sequential read 14 0.01 0.09
latch: session allocation 1 0.00 0.00
db file scattered read 326 0.02 0.53
latch: shared pool 2 0.00 0.00
latch: library cache 1 0.00 0.00


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 7121 0.68 0.56 0 0 0 0
Execute 21847 6.53 6.31 0 0 0 6998
Fetch 15396 686.64 674.91 2364 22572060 0 21579
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 44364 693.85 681.79 2364 22572060 0 28577

Misses in library cache during parse: 36
Misses in library cache during execute: 38


The figures for non recursive does not look too bad but recursive looks bad (I think this is mostly due to Audit). I inherited this database and I'm planning to make a recommendation to reduce the number of audits in place. In the interim, I'm trying my best to improve the situation with regards to the Library cache misses. Currently the shared pool is 400M and here are some of my observations..


SELECT NAMESPACE, PINS, PINHITS, RELOADS, INVALIDATIONS
FROM V$LIBRARYCACHE
ORDER BY NAMESPACE;

NAMESPACE PINS PINHITS RELOADS INVALIDATION

BODY 3582593 3581741 362 0
CLUSTER 14296 13932 179 0
INDEX 132338 112879 7460 0
JAVA DATA 0 0 0 0
JAVA RESOURCE 0 0 0 0
JAVA SOURCE 0 0 0 0
OBJECT 0 0 0 0
PIPE 0 0 0 0
SQL AREA 283694927 281523185 267596 *118090*
TABLE/PROC 215851629 215588814 105599 0
TRIGGER 5890804 5889652 986 0


select sum(pinhits)/sum(pins) from V$LIBRARYCACHE
0.9951717580770384466008350939793460248477


SELECT FROM V$SGASTAT*
WHERE NAME = 'free memory'
AND POOL = 'shared pool'

49920768 (value at 12PM on a business day)


Based on the fact that there were 118,090 SQLAREA invalidations within 2 days (database was restarted on Saturday) and the following information in the sql trace of the sql that I was tracing on Sunday.

Overall Total for recursive statments
---------------------------------------------------
Misses in library cache during parse: 36
Misses in library cache during execute: 38


I'm planning to increase the shared_pool from 400M to 600M and large_pool from 160M to 250M (lot of sqls have parallel hints in them). I don't won't parallel queries taking memory from shared_pool. Please let me know if I'm on the right track or any suggestions.

Thanks for your time.