As db cache advisor was turned on I checked gathered statistics in v$db_cache_advice table.
SQL> select size_for_estimate, size_factor, ESTD_PHYSICAL_READ_FACTOR, ESTD_PHYSICAL_READ_TIME from v$db_cache_advice; SIZE_FOR_ESTIMATE SIZE_FACTOR ESTD_PHYSICAL_READ_FACTOR ESTD_PHYSICAL_READ_TIME ----------------- ----------- ------------------------- ----------------------- 4 ,25 1,0004 118395436 8 ,5 1,0001 118200288 12 ,75 1 118161569 16 1 1 118131035 20 1,25 1 118124913 24 1,5 1 118120247 28 1,75 1 118113338 32 2 ,5617 86001273
From this output it was obvious that I should set db_cache_size=32M to get better performance. I've also checked ADDM for it's recommendations and ADDM confirmed that I should resize db_cache_size.
Size of SGA was limited to 396 MB with memory distribution:
NAME SIZE_MB RES -------------------------------- ---------- --- Maximum SGA Size 396 No Shared Pool Size 320 Yes Startup overhead in Shared Pool 32 No Streams Pool Size 32 Yes Java Pool Size 24 Yes Buffer Cache Size 16 Yes Granule Size 4 No Redo Buffers 2,79 No Fixed SGA Size 1,21 No Free SGA Memory Available 0 Large Pool Size 0 Yes
I've tried to resize db_cache size to 32MB:
SQL> alter system set db_cache_size=32M scope=both; alter system set db_cache_size=32M scope=both * ERROR at line 1: ORA-32017: failure in updating SPFILE ORA-00384: Insufficient memory to grow cache
There wasn't enough sufficient memory for resize operation so I decided to shrink shared pool size to 256 MB from 320 MB.
Checked shared pool usage:
select round(b.sm/1024/1024,2) SHARED_POOL_SIZE_MB, 100-round(a.bytes/b.sm*100,2)||' %' pctused from (select bytes from v$sgastat where name='free memory' AND pool='shared pool') a, (select sum(bytes) sm from v$sgastat where pool = 'shared pool') b / SHARED_POOL_SIZE_MB PCTUSED ------------------- ------------- 320,01 49,62 %
From shared pool usage I could see that it was just 49,62% used.
SQL> alter system set shared_pool_size=256M scope=both; alter system set shared_pool_size=256M scope=both * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-04034: unable to shrink pool to specified size
Something was blocking resize operation (maybe pinned objects, opened cursors,...).
I flushed shared_pool and tried to execute resize operation but again received same error. Then I waited for some time and flushed shared pool again but still resizing couldn't be completed.
I couldn't restart database easily (due to the business needs) to change parameter so I decided to enable ASMM.
Quickly searched about SGA_TARGET in documentation to get confirmation that this parameter is modifiable with ALTER SYSTEM and that I could enable ASMM dynamically.
SQL> alter system set sga_target=368M scope=both;
I hoped that this will enable ASMM and that Oracle will take care about buffer cache and shared pool sizes.
Then I queried v$sga_resize_ops to find out what was happening with SGA.
SQL> col sgares_parameter head PARAMETER for a25 truncate SQL> col sgares_component head COMPONENT for a28 truncate SQL> set lines 200 SQL> set pages 999 SELECT component sgares_component , oper_type , oper_mode , parameter sgares_parameter , initial_size , target_size , final_size , status , start_time , end_time FROM v$sga_resize_ops ORDER BY start_time /
COMPONENT OPER_TYPE OPER_MODE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME END_TIME ---------------------------- ------------- --------- ------------------------- ------------ ----------- ---------- --------- ------------------- ------------------- shared pool STATIC shared_pool_size 0 335544320 335544320 COMPLETE 03.07.2008 11:31:42 03.07.2008 11:31:42 large pool STATIC large_pool_size 0 0 0 COMPLETE 03.07.2008 11:31:42 03.07.2008 11:31:42 DEFAULT buffer cache INITIALIZING db_cache_size 16777216 16777216 16777216 COMPLETE 03.07.2008 11:31:42 03.07.2008 11:31:42 ASM Buffer Cache STATIC db_cache_size 0 0 0 COMPLETE 03.07.2008 11:31:42 03.07.2008 11:31:42 DEFAULT 32K buffer cache STATIC db_32k_cache_size 0 0 0 COMPLETE 03.07.2008 11:31:42 03.07.2008 11:31:42 DEFAULT 16K buffer cache STATIC db_16k_cache_size 0 0 0 COMPLETE 03.07.2008 11:31:42 03.07.2008 11:31:42 DEFAULT 8K buffer cache STATIC db_8k_cache_size 0 0 0 COMPLETE 03.07.2008 11:31:42 03.07.2008 11:31:42 DEFAULT 4K buffer cache STATIC db_4k_cache_size 0 0 0 COMPLETE 03.07.2008 11:31:42 03.07.2008 11:31:42 DEFAULT 2K buffer cache STATIC db_2k_cache_size 0 0 0 COMPLETE 03.07.2008 11:31:42 03.07.2008 11:31:42 RECYCLE buffer cache STATIC db_recycle_cache_size 0 0 0 COMPLETE 03.07.2008 11:31:42 03.07.2008 11:31:42 KEEP buffer cache STATIC db_keep_cache_size 0 0 0 COMPLETE 03.07.2008 11:31:42 03.07.2008 11:31:42 java pool STATIC java_pool_size 0 25165824 25165824 COMPLETE 03.07.2008 11:31:42 03.07.2008 11:31:42 streams pool STATIC streams_pool_size 0 33554432 33554432 COMPLETE 03.07.2008 11:31:42 03.07.2008 11:31:42 DEFAULT buffer cache STATIC db_cache_size 0 16777216 16777216 COMPLETE 03.07.2008 11:31:42 03.07.2008 11:31:42 DEFAULT buffer cache GROW MANUAL db_cache_size 16777216 33554432 16777216 ERROR 16.06.2009 13:52:41 16.06.2009 13:52:41 shared pool SHRINK MANUAL shared_pool_size 335544320 268435456 335544320 ERROR 16.06.2009 14:03:57 16.06.2009 14:03:57
Nothing happened during some time so I decided to search for more information about ASMM.
First to check Administrator's Guide - Automatic Tuning and the Shared Pool.
When the automatic shared memory management feature is enabled, the internal tuning algorithm tries to determine an optimal size for the shared pool based on the workload. However, the internal tuning algorithm typically does not attempt to shrink the shared pool, because the presence of open cursors, pinned PL/SQL packages, and other SQL execution state in the shared pool make it impossible to find granules that can be freed. Therefore, the tuning algorithm only tries to increase the shared pool in conservative increments, starting from a conservative size and stabilizing the shared pool at a size that produces the optimal performance benefit.
So this was the reason why shared pool size wasn't resized automatically.
Another note from documentation:
Note:
When enabling automatic shared memory management, it is best to set SGA_TARGET to the desired non-zero value before starting the database. Dynamically modifying SGA_TARGET from zero to a non-zero value may not achieve the desired results because the shared pool may not be able to shrink. After startup, you can dynamically tune SGA_TARGET up or down as required.
Database restart was obviously the only solution.
After day or two I tried again to shrink shared_pool manually and command succeeded.
SQL> alter system set shared_pool_size=256M scope=both; System altered. Then to check resize operations with query written above: COMPONENT OPER_TYPE OPER_MODE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME END_TIME ---------------------------- ------------- --------- ------------------------- ------------ ----------- ---------- --------- ------------------- ------------------- DEFAULT buffer cache GROW DEFERRED db_cache_size 16777216 29360128 16777216 ERROR 18.06.2009 11:03:44 18.06.2009 11:03:44 shared pool SHRINK DEFERRED shared_pool_size 335544320 322961408 335544320 ERROR 18.06.2009 11:04:14 18.06.2009 11:04:14 DEFAULT buffer cache GROW DEFERRED db_cache_size 16777216 29360128 16777216 ERROR 18.06.2009 11:04:14 18.06.2009 11:04:14 shared pool SHRINK DEFERRED shared_pool_size 335544320 322961408 335544320 ERROR 18.06.2009 11:04:44 18.06.2009 11:04:44 DEFAULT buffer cache GROW DEFERRED db_cache_size 16777216 29360128 16777216 ERROR 18.06.2009 11:04:44 18.06.2009 11:04:44 shared pool SHRINK DEFERRED shared_pool_size 335544320 322961408 335544320 ERROR 18.06.2009 11:05:14 18.06.2009 11:05:14 DEFAULT buffer cache GROW DEFERRED db_cache_size 16777216 29360128 16777216 ERROR 18.06.2009 11:05:14 18.06.2009 11:05:14 shared pool SHRINK DEFERRED shared_pool_size 335544320 322961408 335544320 ERROR 18.06.2009 11:05:44 18.06.2009 11:05:44 DEFAULT buffer cache GROW DEFERRED db_cache_size 16777216 29360128 16777216 ERROR 18.06.2009 11:05:44 18.06.2009 11:05:44 shared pool SHRINK DEFERRED shared_pool_size 335544320 322961408 335544320 ERROR 18.06.2009 11:06:14 18.06.2009 11:06:14 DEFAULT buffer cache GROW DEFERRED db_cache_size 16777216 29360128 16777216 ERROR 18.06.2009 11:06:14 18.06.2009 11:06:14 ... ...
Oracle was trying to shrink shared pool and resize buffer cache for some time but all operations were DEFERRED or CANCELED.
I finally gave up with online resizing and decided to wait opportunity to restart database.
After newly restart with changed parameters (db_cache_size to 32MB) I expect some reduction of disk reads and writes.
Also this case reminded me that I have to study further ASMM and Shared Pool Tuning topics to understand them better.
0 Comments:
Post a Comment