SQl SGA 整理

--查看诊断位置信息

select * from v$diag_info;

--查看sga中内存分配信息

select * from sys.x$ksmfs;

--查看内存块还剩余多少

select pool,name,bytes/1024/1024 MB from v$sgastat where name='free memory';

--查看共享池的使用情况比率

select to_number(v$parameter.value) value,v$sgastat.bytes/1024/1024 "v$sgastat MB",

(v$sgastat.bytes/v$parameter.value)*100 "percent free"

from v$sgastat,v$parameter where v$sgastat.name='free memory' and v$parameter.name='shared_pool_size'

and v$sgastat.pool='shared pool';

--查看SGA内各个易失存储器块的情况

select * from v$sga_dynamic_components

--查询动态调整SGA内存块还有多少可使用空间

select * from v$sga_dynamic_free_memory;

--SGA信息

select * from v$sgainfo

--可根据此视图调整SGA大小

select * from v$sga_target_advice

 

PGA的内存命中：

SELECT name profile,cnt,decode( total, 0, 0, round( cnt * 100 / total)) percentage

FROM ( SELECT name, value cnt, ( SUM( value ) OVER()) total

FROM v$sysstat

WHERE name LIKE 'workarea exec%');

-- 数据缓冲区高速缓存

SELECT physical_reads, db_block_gets, consistent_gets, NAME,

100 * ( 1 - ( physical_reads / (consistent_gets + db_block_gets - physical_reads))) "Data Buffer Hit Ratio"

FROM v$buffer_pool_statistics;

-- 重做日至缓冲区

SELECT a.VALUE redo_entries, b.VALUE redo_buffer_allocation_retries,

ROUND ((1 - b.VALUE / a.VALUE) * 100, 4) log_buffer_ratio

FROM v$sysstat a, v$sysstat b

WHERE a.NAME = 'redo entries' AND b.NAME = 'redo buffer allocation retries';

-- 数据字典高速缓存

SELECT SUM (pinhits) / SUM (pins) * 100 "hit radio"

FROM v$librarycache;

-- 库高速缓存

SELECT TO_CHAR (ROUND ((1 - SUM (getmisses) / SUM (gets)) * 100, 1)) || '%' "Dictionary Cache Hit Ratio"

FROM v$rowcache;

-- 排序

SELECT a.VALUE disk_sort, b.VALUE memory_sort, ROUND ((1 - a.VALUE / (a.VALUE + b.VALUE)) * 100, 4) sort_ratio

FROM v$sysstat a, v$sysstat b

WHERE a.NAME = 'sorts (disk)' AND b.NAME = 'sorts (memory)';

--找出相关的sql根据系统pid

select se.username,se.machine,sq.cpu_time,sq.sql_text from

v$process p,v$session se,v$sqlarea sq

where p.addr=se.paddr and se.sql_hash_value=sq.hash_value and p.addr='&pid';

--列出cpu_time top 10

select cpu_time,sql_text

from (select sql_text,cpu_time,

rank() over (order by cpu_time desc) exec_rank

from v$sql

)

where exec_rank <=10;

--执行次数最多的top 10

select sql_text,executions

from (select sql_text,executions,

rank() over

(order by executions desc) exec_rank

from v$sql)

where exec_rank <=10;