oracle dba常用sql脚本分类文档

-1-Oracle正常SQL监控SQL1.监控事例的等待：selectevent,sum(decode(wait_time,0,0,1))prev,sum(decode(wait_time,0,1,0))curr,count(*)fromv$session_waitgroupbyeventorderby4;2.回滚段的争用情况：selectname,waits,gets,waits/getsratiofromv$rollstata,v$rollnamebwherea.usn=b.usn;3.监控表空间的I/O比例：selectdf.tablespace_namename,df.file_namefile,f.phyrdspyr,f.phyblkrdpbr,f.phywrtspyw,f.phyblkwrtpbwfromv$filestatf,dba_data_filesdfwheref.file#=df.file_id4.监空文件系统的I/O比例：selectsubstr(a.file#,1,2)#,substr(a.name,1,30)name,a.status,a.bytes,b.phyrds,b.phywrtsfromv$datafilea,v$filestatbwherea.file#=b.file#5.在某个用户下找所有的索引：selectuser_indexes.table_name,user_indexes.index_name,uniqueness,column_namefromuser_ind_columns,user_indexeswhereuser_ind_columns.index_name=user_indexes.index_nameanduser_ind_columns.table_name=user_indexes.table_nameorderbyuser_indexes.table_type,user_indexes.table_name,user_indexes.index_name,column_position;6.进程监控：selectdistinctp.spidunix_process,s.terminal,-2-to_char(s.logon_time,'YYYY/MON/DDHH24:MI')Logon_Time,s.usernamefromv$processp,v$sessionswherep.addr=s.paddrorderby27.监控SGA中字典缓冲区的命中率selectparameter,gets,Getmisses,getmisses/(gets+getmisses)*100missratio,(1-(sum(getmisses)/(sum(gets)+sum(getmisses))))*100Hitratiofromv$rowcachewheregets+getmisses0groupbyparameter,gets,getmisses;8.监控SGA中共享缓存区的命中率，应该小于1%selectsum(pins)TotalPins,sum(reloads)TotalReloads,sum(reloads)/sum(pins)*100libcachefromv$librarycache;selectsum(pinhits-reloads)/sum(pins)hitradio,sum(reloads)/sum(pins)reloadpercentfromv$librarycache;9.显示所有数据库对象的类别和大小selectcount(name)num_instances,type,sum(source_size)source_size,sum(parsed_size)parsed_size,sum(code_size)code_size,sum(error_size)error_size,sum(source_size)+sum(parsed_size)+sum(code_size)+sum(error_size)size_requiredfromdba_object_sizegroupbytypeorderby2;10.监控SGA中重做日志缓存区的命中率，应该小于1%SELECTname,gets,misses,immediate_gets,immediate_misses,Decode(gets,0,0,misses/gets*100)ratio1,Decode(immediate_gets+immediate_misses,0,0,immediate_misses/(immediate_gets+immediate_misses)*100)ratio2FROMv$latchWHEREnameIN('redoallocation','redocopy');11.监控内存和硬盘的排序比率，最好使它小于.10，增加sort_area_size-3-SELECTname,valueFROMv$sysstatWHEREnameIN('sorts(memory)','sorts(disk)');12.监控当前数据库谁在运行什么SQL语句SELECTosuser,username,sql_textfromv$sessiona,v$sqltextbwherea.sql_address=b.addressorderbyaddress,piece;13.监控字典缓冲区SELECT(SUM(PINS-RELOADS))/SUM(PINS)LIBCACHEFROMV$LIBRARYCACHE;SELECT(SUM(GETS-GETMISSES-USAGE-FIXED))/SUM(GETS)ROWCACHEFROMV$ROWCACHE;SELECTSUM(PINS)EXECUTIONS,SUM(RELOADS)CACHEMISSESWHILEEXECUTINGFROMV$LIBRARYCACHE;后者除以前者,此比率小于1%,接近0%为好。SELECTSUM(GETS)DICTIONARYGETS,SUM(GETMISSES)DICTIONARYCACHEGETMISSESFROMV$ROWCACHE14.找ORACLE字符集select*fromsys.props$wherename='NLS_CHARACTERSET';15.监控MTSselectbusy/(busy+idle)sharedserversbusyfromv$dispatcher;此值大于0.5时，参数需加大selectsum(wait)/sum(totalq)dispatcherwaitsfromv$queuewheretype='dispatcher';selectcount(*)fromv$dispatcher;selectservers_highwaterfromv$mts;servers_highwater接近mts_max_servers时，参数需加大16.碎片程度selecttablespace_name,count(tablespace_name)fromdba_free_spacegroupbytablespace_namehavingcount(tablespace_name)10;altertablespacenamecoalesce;altertablenamedeallocateunused;-4-createorreplaceviewts_blocks_vasselecttablespace_name,block_id,bytes,blocks,'freespace'segment_namefromdba_free_spaceunionallselecttablespace_name,block_id,bytes,blocks,segment_namefromdba_extents;select*fromts_blocks_v;selecttablespace_name,sum(bytes),max(bytes),count(block_id)fromdba_free_spacegroupbytablespace_name;查看碎片程度高的表SELECTsegment_nametable_name,COUNT(*)extentsFROMdba_segmentsWHEREownerNOTIN('SYS','SYSTEM')GROUPBYsegment_nameHAVINGCOUNT(*)=(SELECTMAX(COUNT(*))FROMdba_segmentsGROUPBYsegment_name);17.表、索引的存储情况检查selectsegment_name,sum(bytes),count(*)ext_quanfromdba_extentswheretablespace_name='&tablespace_name'andsegment_type='TABLE'groupbytablespace_name,segment_name;selectsegment_name,count(*)fromdba_extentswheresegment_type='INDEX'andowner='&owner'groupbysegment_name;18、找使用CPU多的用户session12是cpuusedbythissessionselecta.sid,spid,status,substr(a.program,1,40)prog,a.terminal,osuser,value/60/100valuefromv$sessiona,v$processb,v$sesstatcwherec.statistic#=12andc.sid=a.sidanda.paddr=b.addrorderbyvaluedesc;20.监控log_buffer的使用情况：(值最好小于1%，否则增加log_buffer的大小)selectrbar.name,rbar.value,re.name,re.value,(rbar.value*100)/re.value||'%'radiofromv$sysstatrbar,v$sysstatrewhererbar.name='redobufferallocationretries'-5-andre.name='redoentries';19、查看运行过的SQL语句：SELECTSQL_TEXTFROMV$SQL常用用户SQL表：select*fromcat;select*fromtab;selecttable_namefromuser_tables;视图：selecttextfromuser_viewswhereview_name=upper('&view_name');索引：selectindex_name,table_owner,table_name,tablespace_name,statusfromuser_indexesorderbytable_name;触发器：selecttrigger_name,trigger_type,table_owner,table_name,statusfromuser_triggers;快照：selectowner,name,master,table_name,last_refresh,nextfromuser_snapshotsorderbyowner,next;同义词：select*fromsyn;序列：select*fromseq;数据库链路：select*fromuser_db_links;约束限制：selectTABLE_NAME,CONSTRAINT_NAME,SEARCH_CONDITION,STATUSfromuser_constraintsWHERETABLE_name=upper('&TA