I’m still working on resolving the issues caused by bug 13914613.
Oracle support recommended that we apply a parameter change to resolve the issue but that change requires us to bounce the database and I was looking for a resolution that does not need a bounce. The bug caused very bad shared pool latch waits when the automatic memory management feature of our 11.2.0.3 database expanded the shared pool. Oracle support recommending setting _enable_shared_pool_durations=false and I verified that changing this parameter requires a bounce. It is a big hassle to bounce this database because of the application so I thought that I might try flushing the shared pool on a regular basis so the automatic memory management would not need to keep increasing the size of the shared pool. The shared pool was growing in size because we have a lot of SQL statements without bind variables. So, I did a test and in my test flushing the shared pool did not slow the growth of the shared pool.
Here is a zip of the scripts I used for this test and their outputs: zip
I set the shared pool to a small value so it was more likely to grow and I created a script to run many different sql statements that don’t use bind variables:
spool runselects.sql
select 'select * from dual where dummy=''s'
||to_char(sysdate,'HHMISS')||rownum||''';'
from dba_objects;
spool off
@runselects
So, the queries looked like this:
select * from dual where dummy='s0818111';
select * from dual where dummy='s0818112';
select * from dual where dummy='s0818113';
select * from dual where dummy='s0818114';
select * from dual where dummy='s0818115';
select * from dual where dummy='s0818116';
select * from dual where dummy='s0818117';
I ran these for an hour and tested three different configurations. The first two did not use the _enable_shared_pool_durations=false setting and the last did. The first test was a baseline that showed the growth of the shared pool without flushing the shared pool. The second test including a flush of the shared pool every minute. The last run included the parameter change and no flush of the shared pool. I queried V$SGA_RESIZE_OPS after each test to see how many times the shared pool grew. Here is the query:
SELECT OPER_TYPE,FINAL_SIZE Final,
to_char(start_time,'dd-mon hh24:mi:ss') Started,
to_char(end_time,'dd-mon hh24:mi:ss') Ended
FROM V$SGA_RESIZE_OPS
where component='shared pool'
order by start_time,end_time;
Here are the results.
Baseline – no flush, no parameter change:
OPER_TYPE FINAL STARTED ENDED
--------- ----------- --------------- ---------------
GROW 150,994,944 18-jun 05:03:54 18-jun 05:03:54
GROW 134,217,728 18-jun 05:03:54 18-jun 05:03:54
STATIC 117,440,512 18-jun 05:03:54 18-jun 05:03:54
GROW 167,772,160 18-jun 05:04:36 18-jun 05:04:36
GROW 184,549,376 18-jun 05:47:38 18-jun 05:47:38
Flush every minute, no parameter change:
OPER_TYPE FINAL STARTED ENDED
--------- ----------- --------------- ---------------
GROW 134,217,728 18-jun 06:09:15 18-jun 06:09:15
GROW 150,994,944 18-jun 06:09:15 18-jun 06:09:15
STATIC 117,440,512 18-jun 06:09:15 18-jun 06:09:15
GROW 167,772,160 18-jun 06:09:59 18-jun 06:09:59
GROW 184,549,376 18-jun 06:22:26 18-jun 06:22:26
GROW 201,326,592 18-jun 06:42:29 18-jun 06:42:29
GROW 218,103,808 18-jun 06:47:29 18-jun 06:47:29
Parameter change, no flush:
OPER_TYPE FINAL STARTED ENDED
--------- ------------ --------------- ---------------
STATIC 117,440,512 18-jun 07:16:09 18-jun 07:16:09
GROW 134,217,728 18-jun 07:16:18 18-jun 07:16:18
So, at least in this test – which I have run only twice – flushing the shared pool if anything makes the growth of the shared pool worse. But, changing the parameter seems to lock it in.
– Bobby