SGA sizing and ASMM

Thursday, June 18, 2009

During regular DB checks I've noticed that we had some performance problems due to the insufficient buffer cache pool which was 16 MB. Version of database was 10.2.0.4 with ASMM turned off.

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.


Share/Bookmark

0 comments:

Post a Comment