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 22.214.171.124 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.