一、命中率查询
1.查询监控library cache 命中率
select 1-sum(l.RELOADS)/sum(l.PINS) "Librarycache hit(%)" from v$librarycache l;
3.查询dict cache命中率
SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW CACHE HINT Ratio(%)" FROM V$ROWCACHE;
3.查询sort排序,特定sid的session级别的sort排序,V$sort_usage;
select sort_mem.value "Sort memory" ,sort_dsk.value "Sort disk",(sort_mem.value/(sort_dsk.value+sort_mem.value))*100 "Sort mem_hit(%)"
from v$sysstat sort_mem,v$sysstat sort_dsk
where sort_mem.name like '%sort%memory%'
and sort_dsk.name like '%sorts%disk%';
Sort memory Sort disk Sort mem_hit(%)
----------- ---------- ---------------
558889 0 100
select name,value from v$sysstat where name like '%sort%';
NAME VALUE
---------------------------------------------------------------- ----------
sorts (memory) 12848
sorts (disk) 0
sorts (rows) 410458 -->多少行被排序
select sorts (disk)/sorts (memory) as sorts(disk) from dual; -->计算排序的大小
特定session的排序情况
select sy.CLASS,sy.NAME,se.SID,se.VALUE from v$sysstat sy,v$sesstat se
where sy.STATISTIC#=se.STATISTIC#
and sy.name like '%sort%'
and sid=(select sid from v$session where username='TOM') /*如果从awr,addm中找到top sql的话可以加sql_address,sql_hash_value精确定位*/
CLASS NAME SID VALUE
---------- ---------------------------------------------------------------- ---------- ----------
64 sorts (memory) 1088 94 -->94 是
64 sorts (disk) 1088 0
64 sorts (rows) 1088 3453
CLASS NAME SID VALUE
---------- ---------------------------------------------------------------- ---------- ----------
64 sorts (memory) 1088 95 -->95
64 sorts (disk) 1088 0
64 sorts (rows) 1088 3453
CLASS NAME SID VALUE
---------- ---------------------------------------------------------------- ---------- ----------
64 sorts (memory) 1088 95 -->95
64 sorts (disk) 1088 0
64 sorts (rows) 1088 3453 select st.name,st.VALUE/ (st.VALUE+sy.VALUE)*100 as hito from v$sysstat st,v$sysstat sy
select st.name,st.VALUE/(st.VALUE+sy.VALUE)*100 as hito from v$sysstat st,v$sysstat sy
where st.STATISTIC#=341 and sy.STATISTIC#=342
说明:
*.是session级别的,如果要查看特定的sql则可以从v$sql中的sort字段查看
*.sorts是个递增的,当运行2条同样的sorts时并不会增加,也就解释了temporary tablespace uniform(pct=0) 不马上回收空间,预留下次sort的原因了
二.rman
1.rman备份恢复
10grman 备份恢复有点不一样，在恢复的时候不允许所有步骤一起写，需要分开写
rman>restore database;
rman>recover database until time '09-06-22 08:55:00';(不能带automatic)
rman>alter database open resetlogs;
2.rman删除过期文件脚本
rman target / <<EOF
run{delete obsolete;}
yes
EOF
3.rman crosscheck archivelog
这种情况就是在rman有日志gap的时候做不完全恢复后，仍然会出现找不到日志的错误信息，解决办法：
step1:rman delete obsolete;
step2会提示不能删除需要crosscheck archivelog all;
step3:rman>crosscheck archivelog all;
step4:rman>可以正常备份archivelog
over!!
rman>crosscheck archivelog all;
三、热点块
1.热块
select * from (select * from x$bh order by tch) where rownum<=10;查询前10的热块
select object_name,object_type fromd dba_objects where object_id= x$bh.obj
2.热表 dba_tables,dba_tab_modifications,dbms_stat.flush_database_info;
step1
x$bh tch字段查询热的obj(object_id)
step2 dba_object id 查询object_owner,object_type,object_name
setep3 搜集信息 exec dbms_stats.flush_database_info;
step 4 dba_tables,dba_tab_modifications查询更新次数
select t.table_name,t.last_analyzed,m.inserts,m.updates,m.deletes from dba_tables t,dba_tab_modifications m
where t.table_name=m.table_name and t.owner='SYS'
order by m.inserts,m.updates,m.deletes
WRH$_SYSSTAT 2009-7-18 15: 726 0 9801
WRH$_LATCH 2009-7-18 15: 764 0 10314
JAVASNM$ 2009-6-30 22: 809 0 0
COM$ 2009-6-30 22: 836 12 0
WRH$_SYSTEM_EVENT 2009-7-18 15: 872 0 0
ALERT_QT 887 685 787
WRI$_OPTSTAT_HISTHEAD_HISTORY 2009-7-8 0:40 894 0 0
WRH$_SEG_STAT 2009-7-18 15: 913 0 0
WRH$_SQLSTAT 2009-7-18 15: 1011 0 0
IDL_UB1$ 2009-6-30 22: 1343 55 0
WRH$_SERVICE_STAT 2009-7-18 15: 1344 0 0
SYN$ 2009-6-30 22: 1384 58 0
OBJAUTH$ 2009-6-30 22: 1536 0 1
DEPENDENCY$ 2009-6-30 22: 3783 20 135
WRH$_PARAMETER 2009-7-18 15: 3960 0 0
WRH$_SYSSTAT 2009-7-18 15: 5445 0 0
WRH$_LATCH 2009-7-18 15: 6112 0 0
3.热块查找
SQL> select bh.FILE#,bh.DBABLK,count(*) from x$bh bh
2 group by bh.FILE#,bh.DBABLK
3 having count(*)>3;
FILE# DBABLK COUNT(*)
---------- ---------- ----------
3 2744 5
1 53665 6
1 14725 5
1 48072 5
3 14142 6
1 14722 5
1 27277 5
1 14724 5
1 48068 5
1 27273 5
1 27274 5
1 14726 5
1 27278 5
1 26990 6
1 14727 5
1 48069 5
