Setting swap on Solaris 11

• After the system is installed, swap areas and swap files are listed in /etc/vfstab file
• Create swap

• zfs create -V 2G rpool/swap2

• swap -a /dev/zvol/dsk/rpool/swap2

• Increase the swap size to 40G

root@dat02:~# zfs get volsize rpool/swap
NAME        PROPERTY  VALUE  SOURCE
rpool/swap  volsize   4G     local

root@dat02:~# zfs set volsize=40G rpool/swap

root@dat02:~# zfs get volsize rpool/swap
NAME        PROPERTY  VALUE  SOURCE
rpool/swap  volsize   40G    local


• Has the system previously swapped

Check if kthr:w has non-zero value in vmstat output. Refer to following blog for additional info:
http://ritukamboj.blogspot.com/2012/03/vmstat-shows-high-value-in-w-column.html

• Is the system currently swapping

Check out the sr field of vmstat output

• What is the total swap available

swap field of vmstat output as well swap -s displays the value of available swap

root@etchst8:~# vmstat 2
 kthr      memory            page            disk          faults      cpu
 r b w   swap  free  re  mf pi po fr de sr s3 s1 s1 s1   in   sy   cs us sy id
 0 0 0 169977852 94409120 1192 930 341 0 0 0 1064 6 2 0 2 2793 6433 3146 0 1 99
 0 0 0 175557112 93864204 29 44 0 0 0 0  0  0  0  0  1 2268 1540 1531  0  0 100
 0 0 0 175556024 93863128 0 4 0 0  0  0  0  0  0  0  0 2450 1411 1620  0  0 100
 0 0 0 175554632 93861728 0 1 0 0  0  0  0  1  0  0  0 2382 1385 1581  0  0 100
^Croot@etchst8:~# swap -s -h
total: 552M allocated + 217M reserved = 768M used, 167G available
 

• Additional info:

http://www.solarisinternals.com/wiki/index.php/ZFS_Troubleshooting_Guide#Resizing_ZFS_Swap_and_Dump_Devices
https://blogs.oracle.com/observatory/entry/zone_swap_space
http://www.softpanorama.org/Solaris/Processes_and_memory/swap_space_management.shtml

Sort Area in PGA

If parallel servers are disabled, than the max size of the sort area is determined by _smm_max_size parameter. The value is in KB. The _pga_max_size is the max size of a single session pga. It is expressed in bytes.

You can view this paramter as under:
select a.ksppinm name, b.ksppstvl value from sys.x$ksppi a,sys.x$ksppcv b where
a.indx = b.indx and a.ksppinm='_smm_max_size';
select a.ksppinm name, b.ksppstvl value from sys.x$ksppi a,sys.x$ksppcv b where
a.indx = b.indx and a.ksppinm='_pga_max_size';
show parameters 'pga%';
For 10gR2:

When max_parallel_servers is set to 0:

If pga_aggr_target < 500mb , _smm_max_size is set to 20% of PAT (pga_aggr_target)
if PAT is between 500mb and 1gb , _smm_max_size is set to 100M
if PAT > 1GB, _smm_max_size is set to 10% of PAT

When parallel operations are enabled

Upper bound limited to 50% of PAT/DOP

when DOP <=5, _smm_max_size is used
when DOP > 5 _smm_px_max_size/DOP limits the max memory usuage

_pga_max_size defaults to 2* _smm_max_size

Shared Pool Sizing

This blog entry talks about how the library cache and dictionary cache statistics can be monitored to ensure they are optimally sized.

• Shared pool: Library cache statistics

When sizing the shared pool, the goal is to ensure that SQL statements that will be executed multiple times are cached in the library cache, without allocating too much memory.

The statistic that shows the amount of reloading (that is, reparsing) of a previously cached SQL statement that was aged out of the cache is the RELOADS column in the  V$LIBRARYCACHE view. In an application that reuses SQL effectively, on a system with an optimal shared pool size, the RELOADS statistic will have a value near zero.

The INVALIDATIONS column in V$LIBRARYCACHE view shows the number of times library cache data was invalidated and had to be reparsed. INVALIDATIONS should be near zero. This means SQL statements that could have been shared were invalidated by some operation (for example, a DDL). This statistic should be near zero on OLTP systems during peak loads.

The following query gives info about reloads and invalidations:

sql> col  namespace format a30
SQL> SELECT NAMESPACE, PINS, PINHITS, RELOADS, INVALIDATIONS
  FROM V$LIBRARYCACHE ORDER BY NAMESPACE;

NAMESPACE                            PINS    PINHITS    RELOADS INVALIDATIONS
------------------------------ ---------- ---------- ---------- -------------
APP CONTEXT                           525        524          0             0
BODY                                 7114       6993          0             0
CLUSTER                               529        496          0             0
DBINSTANCE                              0          0          0             0
DBLINK                                  0          0          0             0
EDITION                               482        479          0             0
INDEX                                  44          0          0             0
OBJECT ID                               0          0          0             0
QUEUE                                7316       7300          1             0
RULESET                                 7          6          0             0
SCHEMA                                  0          0          0             0
NAMESPACE                            PINS    PINHITS    RELOADS INVALIDATIONS
------------------------------ ---------- ---------- ---------- -------------
SQL AREA                         55853208   58144246         35           112
SUBSCRIPTION                           12          7          0             0
TABLE/PROCEDURE                     32025      28475         91             0
TRIGGER                               156        145          0             0
USER AGENT                              1          0          0             0
16 rows selected.

 

Another key statistic is the amount of free memory in the shared pool at peak times. The amount of free memory can be queried from V$SGASTAT, looking at the free memory for the shared pool. Optimally, free memory should be as low as possible, without causing any reloads on the system.

You can find out the free memory for the shared pool with the following query:

SELECT * FROM V$SGASTAT WHERE NAME = 'free memory' AND POOL = 'shared pool';


Lastly, a broad indicator of library cache health is the library cache hit ratio. This value should be considered along with the other statistics discussed in this section and other data, such as the rate of hard parsing and whether there is any shared pool or library cache latch contention.

To calculate the library cache hit ratio, use the following formula:
Library Cache Hit Ratio = sum(pinhits) / sum(pins)

The following query displays the library cache hit ratio:

select sum(pinhits)/ sum(pins) from v$librarycache;

• Shared pool: Dictionary cache statistics

Typically, if the shared pool is adequately sized for the library cache, it will also be adequate for the dictionary cache data.

Misses on the data dictionary cache are to be expected in some cases. On instance startup, the data dictionary cache contains no data. Therefore, any SQL statement issued is likely to result in cache misses. As more data is read into the cache, the likelihood of cache misses decreases. Eventually, the database reaches a steady state, in which the most frequently used dictionary data is in the cache. At this point, very few cache misses occur.

Each row in the V$ROWCACHE view contains statistics for a single type of data dictionary item. These statistics reflect all data dictionary activity since the most recent instance startup. The columns in the V$ROWCACHE view that reflect the use and effectiveness of the data dictionary cache are listed below.

PARAMETER
 Identifies a particular data dictionary item. For each row, the value in this column is the item prefixed by dc_. For example, in the row that contains statistics for file descriptions, this column has the value dc_files.

GETS
 Shows the total number of requests for information about the corresponding item. For example, in the row that contains statistics for file descriptions, this column has the total number of requests for file description data.

GETMISSES
 Shows the number of data requests which were not satisfied by the cache, requiring an I/O.

MODIFICATIONS
 Shows the number of times data in the dictionary cache was updated.


Use the following query to monitor the statistics in the V$ROWCACHE view over a period while your application is running. The derived column PCT_SUCC_GETS can be considered the item-specific hit ratio:

column parameter format a21
column pct_succ_gets format 999.9
column updates format 999,999,999
SELECT parameter  , sum(gets)  , sum(getmisses)  , 100*sum(gets - getmisses) / sum(gets)  pct_succ_gets
     , sum(modifications)                     updates
  FROM V$ROWCACHE
 WHERE gets 0
 GROUP BY parameter;


It is also possible to calculate an overall dictionary cache hit ratio using the following formula; however, summing up the data over all the caches will lose the finer granularity of data:
SELECT (SUM(GETS - GETMISSES - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;

Queries to get info about SGA

•  Find the size of SGA and its components:
• select name, value/(1024*1024*1024) as sizeGB from v$sga;
• select sum(value)/(1024*1024*1024) as totalGB from v$sga;
• Detailed look into SGA:
• select name, bytes/(1024*1024*1024)) as sizeGB ,resizeable from v$sgainfo;
• Find the size and contents of buffer pool:
• select sum(current_size)/1024  as sizeGB from v$buffer_pool;
• select name, block_size, current_size/1024 as sizeGB  from v$buffer_pool ;
• Find  the size and contents of variable SGA size
• the sum of following two queries should add up to variable sga as reported by show sga:
• select name, bytes/(1024*1024*1024) as sizeGB from v$sgainfo where name='Free SGA Memory Available';
•  select sum(bytes)/(1024*1024*1024) as sizeGB from v$sgastat where pool in ('shared pool' , 'java pool' ,'large pool') ;
• The amount of SGA memory available for future dynamic SGA operations can be found by either one of the following query:
• select current_size/(1024*1024*1024)  as freeGB from v$sga_dynamic_free_memory;
• select name, bytes/(1024*1024*1024) as sizeGB from v$sgainfo where name='Free SGA Memory Available';
• Query to find out the current, min and max size of each dynamic component in SGA. This query also returns the number of type an grow or shrink operation was performed on each component.
• column component format a25
• select component, current_size/(1024*1024) as currentsizeMB, min_size/(1024*1024) as minMB,max_size/(1024*1024) as maxMB,user_specified_size/(1024*1024) as userspecifiedMB  ,oper_count from  v$sga_dynamic_components;

Oracle Memory Essentials

• There are five ways of managing memory in Oracle

• Enable automatic memory management where Oracle manages both SGA and PGA
• http://www.oracle-base.com/articles/11g/AutomaticMemoryManagement_11gR1.php
• http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/AMM1/Default.aspx
• Enable automatic SGA managment where Oracle manages SGA
• http://download.oracle.com/docs/cd/E11882_01/server.112/e17120/memory004.htm#i1014121
• http://www.scribd.com/doc/2674966/Oracle-Automatic-Shared-Memory-Management-Asmm
• Enable automatic PGA management where Oracle manages PGA
• Manual SGA management
• Manual PGA management

• You can manage memory through EM as under:
• Under Home page -> Server-> Memory Advisor
• The following views provide information about dynamic resize operations:
  
  • V$MEMORY_CURRENT_RESIZE_OPS displays information about memory resize operations (both automatic and manual) which are currently in progress.
  • V$MEMORY_DYNAMIC_COMPONENTS displays information about the current sizes of all dynamically tuned memory components, including the total sizes of the SGA and instance PGA.
  • V$MEMORY_RESIZE_OPS displays information about the last 800 completed memory resize operations (both automatic and manual). This does not include in-progress operations.
  • V$MEMORY_TARGET_ADVICE displays tuning advice for the MEMORY_TARGET initialization parameter.
  • V$SGA_CURRENT_RESIZE_OPS displays information about SGA resize operations that are currently in progress. An operation can be a grow or a shrink of a dynamic SGA component.
  • V$SGA_RESIZE_OPS displays information about the last 800 completed SGA resize operations. This does not include any operations currently in progress.
  • V$SGA_DYNAMIC_COMPONENTS displays information about the dynamic components in SGA. This view summarizes information based on all completed SGA resize operations that occurred after startup.
  • V$SGA_DYNAMIC_FREE_MEMORY displays information about the amount of SGA memory available for future dynamic SGA resize operations.
• SGA Internals
• Relevant views: v$sga,v$sgainfo, v$sgastat
• You can display SGA related info by issuing show sga command
• SQL> show sga
• Total System Global Area 3.1277E+10 bytes
  Fixed Size                  2149312 bytes
  Variable Size            7247762496 bytes
  Database Buffers         2.3891E+10 bytes
  Redo Buffers              136527872 bytes
• The same info can be obtained as under:
• SQL> select name, value/(1024*1024*1024) as sizeGB from v$sga;
• NAME                     SIZEGB
  -------------------- ----------
  Fixed Size           .002001703
  Variable Size        6.75000483
  Database Buffers          22.25
  Redo Buffers         .127151489
  
  
  SQL> select sum(value)/(1024*1024*1024) as totalMB from v$sga;
  
     TOTALMB
  ----------
   29.129158
• Fixed Size
  Contains general information about the state of the database and the instance, which the background processes need to access. No user data is stored here. The size of the fixed portion is constant for a release and a plattform of Oracle, that is, it cannot be changed through any means such as altering the initialization parameters. For 11gR2, fixed SGA size is around 200MB
• Variable size
• The variable portion of SGA is called variable because its size (measured in bytes) can be changed.
  
  The variable portion consists of:
      Large Pool (large_pool_size)
      Shared Pool (shared_pool_size)
      Java Pool (java_pool_size)
  
  The size for the variable portion is roughly equal to the result of the following statement till Oracle 9i:
  
  SQL> select sum(bytes)/(1024*1024*1024) as sizeGB from v$sgastat where pool in ('shared pool' , 'java pool' ,'large pool') ;
  
      SIZEGB
  ----------
  6.75000446
• Post Oracle 9i, the variable component is calculated by adding free memory as shown by v$sgainfo to the above query

Variable Component(Show SGA) = Shared Pool + Large Pool + Java Pool + Overhead + free memory(9i onwards)

>  select name, bytes/(1024*1024*1024) as sizeGB from v$sgainfo;
Adding free sga memory available to the above query from v$sgastat should get you the variable SGA size as reported by show sga command.

• Database buffers consist of default buffer (db_cache_size), keep buffer (db_keep_cache_size) , recycle buffer (db_recycle_cache_size) and all non standard buffer (db_n_cache_size). Oracle has the following non standard buffer cache:
• DB_2k_cache_size
• DB_4K_cache_size
• DB_8k_cache_size
• db_16k_cache_size
• db_32k_cache_size.

 The default block size is determined by db_block_size parameter.Multiple buffer pools are only available for the standard block size. Non-standard block size caches have a single DEFAULT pool.

• Query v$buffer_pool to get the sizes of all database buffers configured for the instance.
  SQL> select name, block_size, current_size from v$buffer_pool ;
  NAME                 BLOCK_SIZE CURRENT_SIZE
  -------------------- ---------- ------------
  KEEP                       2048        10240
  RECYCLE                    2048         7168
  DEFAULT                    2048         2048
  DEFAULT                    8192          256
  DEFAULT                   16384         3072
  The above output indicates that the size of keep buffer is 10 GB, size of recycle buffer is 7GB and the size of default buffer is 2 GB. Also , an 8K buffer cache of size 256MB and a 16k buffer cache of size 3 gb is configured.
  The following query indicates that total buffer cache is 22GB as shown in the output of show sga command:
  SQL> select sum(current_size)/1024  as sizeGB from v$buffer_pool;
      SIZEGB
  ----------
       22.25