set lines 300
set heading OFF
col pf for a25
col vl for a20
select name as pf, lpad(to_char(value, '999,999,999,990'), 16, ' ') as vl from v$parameter where name in ('db_cache_size');
select name as pf, lpad(to_char(value, '999,999,999,990'), 16, ' ') as vl from v$parameter where name in ('db_block_buffers');
select name as pf, lpad(to_char(value, '999,999,999,990'), 16, ' ') as vl from v$parameter where name in ('db_block_size');
select name as pf, lpad(to_char(value, '999,999,999,990'), 16, ' ') as vl from v$parameter where name in ('log_buffer');
select name as pf, lpad(to_char(value, '999,999,999,990'), 16, ' ') as vl from v$parameter where name in ('buffer_pool_keep');
select name as pf, lpad(to_char(value, '999,999,999,990'), 16, ' ') as vl from v$parameter where name in ('buffer_pool_recycle');
select name as pf, lpad(to_char(value, '999,999,999,990'), 16, ' ') as vl from v$parameter where name in ('db_keep_cache_size');
select name as pf, lpad(to_char(value, '999,999,999,990'), 16, ' ') as vl from v$parameter where name in ('db_recycle_cache_size');
select name as pf, lpad(to_char(value, '999,999,999,990'), 16, ' ') as vl from v$parameter where name in ('large_pool_size');
select name as pf, lpad(to_char(value, '999,999,999,990'), 16, ' ') as vl from v$parameter where name in ('java_pool_size');
select name as pf, lpad(to_char(value, '999,999,999,990'), 16, ' ') as vl from v$parameter where name in ('shared_pool_size');
select name as pf, lpad(to_char(value, '999,999,999,990'), 16, ' ') as vl from v$parameter where name in ('shared_pool_reserved_size');
select name as pf, lpad(to_char(value, '999,999,999,990'), 16, ' ') as vl from v$parameter where name in ('sort_area_size');
select name, value from v$sysstat where name like 'sort%';
共有メモリ状態表示 shared_pool_size,large_pool_size,java_pool_sizeの割当容量、使用状況を調べる。
pro+--------------------+---------------+---------------+----------+;
pro|name |memory |free | %|;
pro+--------------------+---------------+---------------+----------+;
select a.dt ||
'|'|| lpad(to_char(b.bytes, '99,999,999,990'), 15, ' ') ||
'|'|| lpad(to_char((b.bytes / a.value * 100), '990.99'), 10, ' ') ||
'|'
from
(
select '|'|| rpad(name, 20, ' ') ||
'|'|| lpad(to_char(value, '99,999,999,990'), 15, ' ') as dt
, decode(name,'shared_pool_size', 1, decode(name, 'large_pool_size', 2, 3)) as dt_1
, value
from v$parameter
where name in ('shared_pool_size', 'large_pool_size', 'java_pool_size')
) a
,
(
select decode(pool, 'shared pool', 1, decode(pool, 'large pool', 2, 3)) as dt_1
, bytes
from v$sgastat
where pool in ('large pool', 'shared pool', 'java pool')
and name = 'free memory'
) b
where a.dt_1 = b.dt_1
;
pro+--------------------+---------------+---------------+----------+;
+--------------------+---------------+---------------+----------+;
|name |memory |free | %|;
+--------------------+---------------+---------------+----------+;
|shared_pool_size | 3,573,547,008| 411,966,640| 11.53|
|large_pool_size | 419,430,400| 356,368,552| 84.96|
|java_pool_size | 33,554,432| 28,631,040| 85.33|
+--------------------+---------------+---------------+----------+;
共有メモリの適正値
SQL> SELECT SUM(SHARABLE_MEM) FROM V$DB_OBJECT_CACHE;
SUM(SHARABLE_MEM)
-----------------
290789420
SQL> SELECT SUM(SHARABLE_MEM) FROM V$SQLAREA;
SUM(SHARABLE_MEM)
-----------------
289400089
SQL> SELECT SUM(250 * USERS_OPENING) FROM V$SQLAREA;
SUM(250*USERS_OPENING)
----------------------
214750
expr 290789420 + 289400089 + 214750 = 580404259
580,404,259 （最低限必要）
SQL> SELECT value FROM V$PARAMETER WHERE NAME = 'shared_pool_size';
754,525,536 580404259 × 1.3（適正な設定値）
Oracle Shared POOL調査用SQL
select sum(ksmchsiz) total, count(ksmchsiz) num,
sum(ksmchsiz)/count(ksmchsiz) avg, max(ksmchsiz) "max"
from x$ksmsp where ksmchcls='free';
total: 空きメモリの総量
max: 連続して取れる空きメモリの最大値
avg: 連続して取得可能な平均空きメモリ量
num: 空きメモリ領域の断片化の値