memory tunning.sql

Oracle培训专题3-数据库配置.ppt Oracle培训专题4-备份和恢复.ppt Oracle培训专题5-浅谈调优.ppt

--SGA
SELECT * FROM v$sgastat

--library
SELECT * FROM v$librarycache
SELECT * FROM v$library_cache_memory
SELECT * FROM v$db_object_cache
SELECT * FROM v$sqlarea
SELECT * FROM v$sqltext
SELECT * FROM v$sql_plan
SELECT * FROM v$sql

SELECT SUM(pins) "Execution",SUM(reloads), SUM(reloads)/SUM(pins) FROM v$librarycache
SELECT namespace,SUM(pins) "Execution",SUM(reloads) FROM v$librarycache GROUP BY namespace

SELECT SUM(sharable_mem) FROM v$sqlarea 
SELECT SUM(sharable_mem) FROM v$db_object_cache
SELECT SUM(250* users_opening) FROM v$sqlarea

SELECT * FROM v$db_object_cache
WHERE sharable_mem > 10000
AND kept='NO'
AND TYPE IN ('PACKAGE','PACKAGE BODY','PROCEDURE','FUNCTION')

SELECT * FROM v$sqlarea WHERE command_type=47 AND length(sql_text)>500
SELECT * FROM v$parameter WHERE NAME LIKE 'open_cursors'

--共享池
--命中率
--librarycache
select 	sum(pins) "Hits",
	SUM(reloads) "Misses",
	(sum(pins)/(SUM(reloads)+sum(pins)))*100 "Hit Ratio%",
	(SUM(reloads)/SUM(pins))*100 "Reload%" 
FROM v$librarycache

--dictionary cache
select (1-(sum(GetMisses)/(sum(Gets)+sum(GetMisses))))*100 "Hit Rate%"
from v$RowCache
where Gets+GetMisses<>0

--查看重新解析的大对象
 select name,type,sharable_mem,loads,executions,kept
 from V$DB_OBJECT_CACHE
 order by sharable_mem desc

--查看没有固化的大对象
 select * from v$db_object_cache
 where sharable_mem > 10000
 and (type='PACKAGE' or type='PACKAGE BODY' or
 type='FUNCTION' or type='PROCEDURE')
 and KEPT='NO';

--大的SQL
 select sql_text from v$sqlarea
 where command_type = 47
 and length(sql_text) > 500;

dbmspool.sql
EXECUTE dbms_shared_pool.keep(‘package_name’);
execute dbms_shared_pool.keep(’address,hash_value’);


--db cache
--命中率
select sum(decode(name,'physical reads',value,0)) phys,
       sum(decode(name,'db block gets',value,0)) gets,
       sum(decode(name,'consistent gets',value,0)) con_gets,
       (1-(sum(decode(name,'physical reads',value,0))/
           (sum(decode(name,'db block gets',value,0))+
            sum(decode(name,'consistent gets',value,0)))))*100 hitratio
from v$sysstat

Select 1- (phy.value - dir.value - lob.value)/ses.value "Cache hit ratio"
From v$sysstat ses,v$sysstat phy,v$sysstat dir,v$sysstat lob
Where ses.name='session logical reads'
And phy.name='physical reads'
And dir.name ='physical reads direct'
And lob.name = 'physical reads direct (lob)'

--瓶颈检查
SELECT event, total_waits
FROM v$system_event
WHERE event in
('free buffer waits', 'buffer busy waits');

select * from V$WAITSTAT;

--内存表
CREATE INDEX cust_idx …
STORAGE (BUFFER_POOL KEEP …);

ALTER TABLE customer
STORAGE (BUFFER_POOL RECYCLE);

ALTER INDEX cust_name_idx
STORAGE (BUFFER_POOL KEEP);


--sort 
Select disk.value "Disk",
       Mem.value "Mem",
       disk.value / mem.value * 100 "Ratio"
  from v$sysstat disk, v$sysstat mem
 where disk.name = 'sorts (disk)'
   and mem.name = 'sorts (memory)'

--log
--日志内存空间不足
Select sId,event,seconds_in_wait,state 
from v$session_wait where event like 'log buffer space%'


select r.value "Retries", e.value "Entries",
 r.value/e.value*100 "Percentage"
 from v$sysstat r, v$sysstat e
 where r.name = 'redo buffer allocation retries'
 and e.name='redo entries';

--日志切换等待
select event, total_waits, time_waited, average_wait
from v$system_event
 where event like 'log file switch (check%';