管理员日常工作中必备的sql列表.docx
《管理员日常工作中必备的sql列表.docx》由会员分享，可在线阅读，更多相关《管理员日常工作中必备的sql列表.docx（23页珍藏版）》请在冰豆网上搜索。
管理员日常工作中必备的sql列表
数据库管理员日常工作中必备的sql列表
目录
1.捕捉运行很久的SQL
2.求DISKREAD较多的SQL
3.求DISKSORT严重的SQL
4.计算databuffer命中率
5.监控索引是否使用
6.求数据文件的I/O分布
7求某个隐藏参数的值
8求系统中较大的latch
9求归档日志的切换频率（生产系统可能时间会很长）
10.求回滚段正在处理的事务
11.求出无效的对象
12.求process/session的状态
13.求当前session的状态
14.求表的索引信息
15.显示表的外键信息
16.显示表的分区及子分区（user_tab_subpartitions）
17.使用dbms_xplan生成一个执行计划
18.求某个事务的重做信息（bytes）
19.求cache中缓存超过其5%的对象
20.求谁阻塞了某个session（10g）
21.求session的OS进程ID
22.查会话的阻塞
23.求等待的事件及会话信息/求会话的等待及会话信息
24.求会话等待的file_id/block_id
25求会话等待的对象
26求buffercache中的块信息
27求日志文件的空间使用
28求等待中的对象
29求当前事务的重做尺寸
30唤醒smon去清除临时段
31求回退率
32求DISKREAD较多的SQL
33求DISKSORT严重的SQL
34求对象的创建代码
35求表的索引
36求索引中行数较多的
37求当前会话的SID，SERIAL#
38求表空间的未用空间
39求表中定义的触发器
40求未定义索引的表
41执行常用的过程
42求freememory
43查看用户的回滚段的信息
44生成执行计划
45查看执行计划
46查看内存中存的使用
47查看表空间状态
48查看系统请求情况
49计算databuffer命中率
50查看内存使用情况
51查看用户使用内存情况
52查看对象的缓存情况
53查看库缓存命中率
54查看某些用户的hash
55查看字典命中率
56查看undo段的使用情况
57无效的对象
58求出某个进程，并对它进行跟踪
59求出锁定的对象
60求当前session的跟踪文件
61求对象所在的文件及块号
62求对象发生事务时回退段及块号
63.9i的在线重定义表
64常用的logmnr脚本（cybercafe）
65与权限相关的字典
66如何用dbms_stats分析表及模式？
67查看数据库的名字和归档状态
68查看数据库的instance名字和状态
69查看表空间名称、状态和管理方式
70查看控制文件的位置和名字
71查看日志文件的组名、成员数量、状态和大小
72查看日志文件的位置
73查看数据文件的位置和大小
74查看用户拥有的对象和类型
75查看临时文件的信息
76查看归档日志信息
捕捉运行很久的SQL
columnusernameformata12
columnopnameformata16
columnprogressformata8
SELECTUsername,Sid,Opname,
Round（Sofar*100/Totalwork,0）||'%'ASProgress,Time_Remaining,
Sql_Text
FROMV$session_Longops,V$sql
WHERETime_Remaining<>0
ANDSql_Address=Address
ANDSql_Hash_Value=Hash_Value;
求DISKREAD较多的SQL
SELECTSt.Sql_Text
FROMV$sqls,V$sqltextSt
WHEREs.Address=St.Address
ANDs.Hash_Value=St.Hash_Value
ANDs.Disk_Reads>300;
求DISKSORT严重的SQL
SELECTSess.Username,SQL.Sql_Text,Sort1.Blocks
FROMV$sessionSess,V$sqlareaSQL,V$sort_UsageSort1
WHERESess.Serial#=Sort1.Session_Num
ANDSort1.Sqladdr=SQL.Address
ANDSort1.Sqlhash=SQL.Hash_Value
ANDSort1.Blocks>200;
计算databuffer命中率
SELECTa.VALUE+b.VALUE"logical_reads",c.VALUE"phys_reads",
Round（100*（（a.VALUE+b.VALUE）-c.VALUE）/（a.VALUE+b.VALUE））"BUFFERHITRATIO"
FROMV$sysstata,V$sysstatb,V$sysstatc
WHEREa.Statistic#=40
ANDb.Statistic#=41
ANDc.Statistic#=42;
SELECTNAME,
（1-（Physical_Reads/（Db_Block_Gets+Consistent_Gets）））*100h_Ratio
FROMV$buffer_Pool_Statistics;
--监控索引是否使用
alterindex&index_namemonitoringusage;
alterindex&index_namenomonitoringusage;
select*fromv$object_usagewhereindex_name=&index_name;
--求数据文件的I/O分布
selectdf.name,phyrds,phywrts,phyblkrd,phyblkwrt,singleblkrds,readtim,writetim
fromv$filestatfs,v$dbfiledf
wherefs.file#=df.file#orderbydf.name;
--求某个隐藏参数的值
colksppinmformata54
colksppstvlformata54
selectksppinm,ksppstvl
fromx$ksppipi,x$ksppcvcv
wherecv.indx=pi.indxandpi.ksppinmlike'\_%'escape'\'andpi.ksppinmlike'%meer%';
--求系统中较大的latch
selectname,sum（gets）,sum（misses）,sum（sleeps）,sum（wait_time）
fromv$latch_children
groupbynamehavingsum（gets）>50orderby2;
--求归档日志的切换频率（生产系统可能时间会很长）
selectstart_recid,start_time,end_recid,end_time,minutesfrom（selecttest.*,rownumasrn
from（selectb.recidstart_recid,to_char（b.first_time,'yyyy-mm-ddhh24:
mi:
ss'）start_time,
a.recidend_recid,to_char（a.first_time,'yyyy-mm-ddhh24:
mi:
ss'）end_time,round（（（a.first_time-b.first_time）*24）*60,2）minutes
fromv$log_historya,v$log_historybwherea.recid=b.recid+1andb.first_time>sysdate-1
orderbya.first_timedesc）test）ywherey.rn<30
--求回滚段正在处理的事务
selecta.name,b.xacts,c.sid,c.serial#,d.sql_text
fromv$rollnamea,v$rollstatb,v$sessionc,v$sqltextd,v$transactione
wherea.usn=b.usnandb.usn=e.xidusnandc.taddr=e.addr
andc.sql_address=d.addressandc.sql_hash_value=d.hash_valueorderbya.name,c.sid,d.piece;
--求出无效的对象
select'alterprocedure'||object_name||'compile;'
fromdba_objects
wherestatus='INVALID'andwner='&'andobject_typein（'PACKAGE','PACKAGEBODY'）;
/
selectowner,object_name,object_type,statusfromdba_objectswherestatus='INVALID';
--求process/session的状态
selectp.pid,p.spid,s.program,s.sid,s.serial#
fromv$processp,v$sessionswheres.paddr=p.addr;
--求当前session的状态
selectsn.name,ms.value
fromv$mystatms,v$statnamesn
wherems.statistic#=sn.statistic#andms.value>0;
--求表的索引信息
selectui.table_name,ui.index_name
fromuser_indexesui,user_ind_columnsuic
whereui.table_name=uic.table_nameandui.index_name=uic.index_name
andui.table_namelike'&table_name%'anduic.column_name='&column_name';
--显示表的外键信息
colsearch_conditionformata54
selecttable_name,constraint_name
fromuser_constraints
whereconstraint_type='R'andconstraint_namein（selectconstraint_namefromuser_cons_columnswherecolumn_name='&1'）;
selectrpad（child.table_name,25,''）child_tablename,
rpad（cp.column_name,17,''）referring_column,rpad（parent.table_name,25,''）parent_tablename,
rpad（pc.column_name,15,''）referred_column,rpad（child.constraint_name,25,''）constraint_name
fromuser_constraintschild,user_constraintsparent,
user_cons_columnscp,user_cons_columnspc
wherechild.constraint_type='R'andchild.r_constraint_name=parent.constraint_nameand
child.constraint_name=cp.constraint_nameandparent.constraint_name=pc.constraint_nameand
cp.position=pc.positionandchild.table_name='&table_name'
orderbychild.owner,child.table_name,child.constraint_name,cp.position;
--显示表的分区及子分区（user_tab_subpartitions）
coltable_nameformata16
colpartition_nameformata16
colhigh_valueformata81
selecttable_name,partition_name,HIGH_VALUEfromuser_tab_partitionswheretable_name='&table_name'
--使用dbms_xplan生成一个执行计划
explainplansetstatement_id='&sql_id'for&sql;
select*fromtable（dbms_xplan.display）;
--求某个事务的重做信息（bytes）
selects.name,m.value
fromv$mystatm,v$statnames
wherem.statistic#=s.statistic#ands.namelike'%redosize%';
--求cache中缓存超过其5%的对象
selecto.owner,o.object_type,o.object_name,count（b.objd）
fromv$bhb,dba_objectso
whereb.objd=o.object_id
groupbyo.owner,o.object_type,o.object_name
havingcount（b.objd）>（selectto_number（value）*0.05fromv$parameterwherename='db_block_buffers'）;
--求谁阻塞了某个session（10g）
selectsid,username,event,blocking_session,
seconds_in_wait,wait_time
fromv$sessionwherestatein（'WAITING'）andwait_class!
='Idle';
--求session的OS进程ID
colprogramformata54
selectp.spid"OSThread",b.name"Name-User",s.program
fromv$processp,v$sessions,v$bgprocessb
wherep.addr=s.paddrandp.addr=b.paddr
UNIONALL
selectp.spid"OSThread",s.username"Name-User",s.program
fromv$processp,v$sessionswherep.addr=s.paddrands.usernameisnotnull;
--查会话的阻塞
coluser_nameformata32
select/*+rule*/lpad（'',decode（l.xidusn,0,3,0））||l.oracle_usernameuser_name,o.owner,o.object_name,s.sid,s.serial#
fromv$locked_objectl,dba_objectso,v$sessions
wherel.object_id=o.object_idandl.session_id=s.sidorderbyo.object_id,xidusndesc;
colusernameformata15
collock_levelformata8
colownerformata18
colobject_nameformata32
select/*+rule*/s.username,decode（l.type,'tm','tablelock','tx','rowlock',null）lock_level,o.owner,o.object_name,s.sid,s.serial#
fromv$sessions,v$lockl,dba_objectso
wherel.sid=s.sidandl.id1=o.object_id（+）ands.usernameisnotnull;
--求等待的事件及会话信息/求会话的等待及会话信息
selectse.sid,s.username,se.event,se.total_waits,se.time_waited,se.average_wait
fromv$sessions,v$session_eventse
wheres.usernameisnotnullandse.sid=s.sidands.status='ACTIVE'andse.eventnotlike'%SQL*Net%'orderbys.username;
selects.sid,s.username,sw.event,sw.wait_time,sw.state,sw.seconds_in_wait
fromv$sessions,v$session_waitsw
wheres.usernameisnotnullandsw.sid=s.sidandsw.eventnotlike'%SQL*Net%'orderbys.username;
--求会话等待的file_id/block_id
coleventformata24
colp1textformata12
colp2textformata12
colp3textformata12
selectsid,event,p1text,p1,p2text,p2,p3text,p3
fromv$session_wait
whereeventnotlike'%SQL%'andeventnotlike'%rdbms%'andeventnotlike'%mon%'orderbyevent;
selectname,wait_timefromv$latchlwhereexists（select1from（selectsid,event,p1text,p1,p2text,p2,p3text,p3
fromv$session_wait
whereeventnotlike'%SQL%'andeventnotlike'%rdbms%'andeventnotlike'%mon%'
）xwherex.p1=l.latch#）;
--求会话等待的对象
colownerformata18
colsegment_nameformata32
colsegment_typeformata32
selectowner,segment_name,segment_type
fromdba_extents
wherefile_id=&file_idand&block_idbetweenblock_idandblock_id+blocks-1;
--求buffercache中的块信息
selecto.OBJECT_TYPE,substr（o.OBJECT_NAME,1,10）objname,b.objd,b.status,count（b.objd）
fromv$bhb,dba_objectso
whereb.objd=o.data_object_idando.owner='&1'groupbyo.object_type,o.object_name,b.objd,b.status;
--求日志文件的空间使用
selectle.leseqcurrent_log_sequence#,100*cp.cpodr_bno/le.lesizpercentage_full
fromx$kcccpcp,x$kcclele
wherele.leseq=cp.cpodr_seq;
--求等待中的对象
select/*+rule*/s.sid,s.username,w.event,o.owner,o.segment_name,o.segment_type,
o.partition_name,w.seconds_in_waitseconds,w.state
fromv$session_waitw,v$sessions,dba_extentso
wherew.eventin（selectnamefromv$event_namewhereparameter1='file#'
andparameter2='block#'andnamenotlike'control%'）
ando.owner<>'sys'andw.sid=s.sidandw.p1=o.file_idandw.p2>=o.block_idandw.p2 --求当前事务的重做尺寸
selectvalue
fromv$mystat,v$statname
wherev$mystat.statistic#=v$statname.stat
升级会员 