Hi,
The database version is 11.2.0.2 with a physical standby.
We are using automatic memory management, with memory_target set to 32GB.
My query is, would in any scenario Oracle should optimally grow shared pool to a size of 9.5 GB ?
The database is performing very slow and any suggestions are welcome.
SQL> SELECT component, current_size/1024/1024, min_size/1024/1024, max_size/1024/1024
FROM v$memory_dynamic_components
WHERE current_size != 0; 4 5 SQL> 2 3
COMPONENT CURRENT_SIZE/1024/1024 MIN_SIZE/1024/1024 MAX_SIZE/1024/1024
------------------------------ ---------------------- ------------------ ------------------
shared pool 9536 7424 10752
large pool 64 64 3392
java pool 64 64 64
streams pool 64 0 128
SGA Target 27136 21312 27136
DEFAULT buffer cache 11968 2880 12864
KEEP buffer cache 128 128 128
DEFAULT 16K buffer cache 5120 3136 5120
PGA Target 5632 5632 11456
9 rows selected.
Few other details from AWR that may help:
Begin Snap: 18-Jul-12
End Snap: 31-Jul-12
Elapsed: 19,559.69 (mins)
DB Time: 369,955.55 (mins)
Instance Efficiency Percentages (Target 100%)
Buffer Nowait %: 100.00
Redo NoWait %: 93.28
Buffer Hit %: 99.63
In-memory Sort %: 100.00
Library Hit %: -17.61
Soft Parse %: 88.13
Execute to Parse %: 81.78
Latch Hit %: 96.55
Parse CPU to Parse Elapsd %: 68.10
% Non-Parse CPU: 89.30
Thanks & Rgds,
Sanjay