Oracle數(shù)據(jù)庫(kù)日常管理方案精

1、 Oracle數(shù)據(jù)庫(kù)日常管理方案Oracle數(shù)據(jù)庫(kù)的運(yùn)維處理，更主要的是做好數(shù)據(jù)庫(kù)的管理工作，做好數(shù)據(jù)庫(kù)的檢查與備份工作，然后需要熟悉相關(guān)的邏輯結(jié)構(gòu)故障處理方法、物理結(jié)構(gòu)故障處理方法，應(yīng)對(duì)數(shù)據(jù)庫(kù)損壞的情況，在數(shù)據(jù)庫(kù)發(fā)生損壞而無(wú)法修復(fù)的時(shí)候，能利用數(shù)據(jù)庫(kù)管理工作中所做的數(shù)據(jù)備份及時(shí)恢復(fù)系統(tǒng)、數(shù)據(jù)，恢復(fù)正常生產(chǎn)工作。數(shù)據(jù)庫(kù)管理Oracle數(shù)數(shù)據(jù)庫(kù)的管理理主要是要做做好日常的檢檢查管理工作作，并檢查好好數(shù)據(jù)庫(kù)的備備份情況，在在發(fā)生緊急情情況時(shí)能及時(shí)時(shí)不僅切換到到備用系統(tǒng)上上進(jìn)行工作，并并要能在數(shù)據(jù)據(jù)庫(kù)數(shù)據(jù)出現(xiàn)現(xiàn)異常的時(shí)候候，能及時(shí)恢恢復(fù)數(shù)據(jù)。因因此，做好日日常的檢查與與管理工作尤尤其重要，主主要工作

2、包括括：數(shù)據(jù)庫(kù)的性能監(jiān)監(jiān)控檢查數(shù)據(jù)庫(kù)狀態(tài)巡檢檢檢查數(shù)據(jù)庫(kù)備份情況況與可恢復(fù)性性檢查數(shù)據(jù)庫(kù)性能監(jiān)控控管理通過(guò)對(duì)數(shù)據(jù)庫(kù)性性能監(jiān)控的管管理工作，可可以有效的防防范風(fēng)險(xiǎn)事件件的發(fā)生，并并能從長(zhǎng)時(shí)間間的運(yùn)行數(shù)據(jù)據(jù)中，總結(jié)出出數(shù)據(jù)庫(kù)性能能的狀態(tài)，為為系統(tǒng)性能優(yōu)優(yōu)化、隱患事事件排查提供供更好的依據(jù)據(jù)。查找前十條性能能差的sqllSELECT * FROOM (seelect PARSIING_USSER_IDD,EXECCUTIONNS,SORRTS,COOMMANDD_TYPEE, DISK_READSS,sql_text FROM v$sqllarea orderr BY ddisk_rreads DE

3、SC )wherre ROWWNUM 8 AND aa.evennt = enqueeue OORDER BY ussernamme;如何確定哪個(gè)表表空間讀寫(xiě)頻頻繁select name,phyrdds,phyywrts,readttim,wrritetiim froom v$ffilesttat a,v$dbffile bb wherre a.ffile#=b.fille# oorder by reeadtimm descc;SELECT s.sidd, kgllpnmodd “Modde”, kkglpnrreq “RReq”, SPID “OS PProcesss” FRROM v$se

4、ssiion_waait w, x$kgglpn pp, v$ssessioon s ,v$proocess o WHEERE p.kglpnnuse=ss.sadddr ANDD kglppnhdl=w.p1rraw annd w.eevent like %libbrary cachee pin% andd s.paaddr=oo.addrr;查詢?nèi)頀呙璧牡谋鞸QL cool namme forr a30SQL seelect name,valuee fromm v$syysstatt wwhere name in (tablee scanns (shhort ttabless),ttab

5、le scanss (lonng tabbles);NAME VVALUE- -table sscans (shorrt tabbles) 118602table sscans (longg tablles) 111SQL seelect countt(targget),ttargett fromm v$seessionn_longgops wwhere opnamme = Tablee Scann grooup byy targget;COUNT(TTARGETT) TARGGET- - 884 ECM_AAPPL.RRPTLOGG 1559 ECM_DDCTM_OOTHR.DDMR_COO

6、NTENTT_S 9 ECM_DDCTM_OOTHR.DDM_SYSSOBJECCT_R 2 ECM_DDCTM_OOTHR.DDM_SYSSOBJECCT_S查出全表掃描的的表Select sql_ttext ffrom vv$sqlttext tt, v$ssql_pllan p Wheree t.haash_vaalue=pp.hashh_valuue Andd p.opperatiion=TTABLE ACCESSS Annd p.ooptionn=FULLL Orrder bby p.hhash-vvalue, t.piiece;查找Fast Full indexx 掃描的Sqll語(yǔ)

7、句可以這這樣: Select sql_ttext ffrom vv$sqlttext tt, v$ssql_pllan p Wheree t.haash_vaalue=pp.hashh_valuue Andd p.opperatiion=IINDEX And p.opttion=FULL SCAN Ordeer by p.hassh-vallue, tt.piecce;查詢硬語(yǔ)法分析析的次數(shù)SQL seelect name,valuee fromm v$syysstatt wherre namme likke paarse ccount%;NAME VALUEE- -parse ccount

8、(totaal) 116103parse ccount (hardd) 343parse ccount (faillures) 5該項(xiàng)顯示bufffer ccache大大小是否合適適。公式：1-(physiical rreads-physiical rreads direcct-phyysicall readds dirrect (lob) / seessionn logiical rreads)執(zhí)行：select 1-(aa.valuue-b.vvalue-c.vallue)/dd.valuue) ffrom vv$syssstat aa,v$syysstatt b,v$syssttat c

9、,v$syssstat d whhere e=phyysicall readds annd e=phyysicall readds dirrect and c.nname=physiical rreads direcct (loob) aand e=sesssion logiccal reeads;顯示buffeer命中率公式：1-(pphysiccal reeads/ (db bblock gets+consiistentt getss)執(zhí)行：select 1-(suum(deccode(nname,physiical rreads,valuue,0)/(suum(deccode(nname

10、,db bllock ggets,valuee,0)+sum(ddecodee(namee,connsisteent geets,vvalue,0) bufffer hiit rattio ffrom vv$syssstat;Soft paarse rratio：這項(xiàng)將顯示系統(tǒng)統(tǒng)是否有太多多硬解析。該該值將會(huì)與原原始統(tǒng)計(jì)數(shù)據(jù)據(jù)對(duì)比以確保保精確。例如如，軟解析率率僅為0.22則表示硬解解析率太高。不不過(guò)，如果總總解析量(pparse countt totaal)偏低，這這項(xiàng)值可以被被忽略。公式：1 - ( parrse coount (hard) / paarse ccount (totaal)

11、 ) 執(zhí)行：select 1-(a.valuee/b.vaalue) fromm v$syysstatt a,v$syssttat b Wherre a.nname=parsee counnt (haard) and e=parrse coount (totall);In-memoory soort raatio：該該項(xiàng)顯示內(nèi)存存中完成的排排序所占比例例。最理想狀狀態(tài)下，在OOLTP系統(tǒng)統(tǒng)中，大部分分排序不僅小小并且能夠完完全在內(nèi)存里里完成排序。公式：sortts (meemory) / ( sortss (memmory) + sorrts (ddisk) )執(zhí)行：select a.vall

12、ue/(b.vaalue+cc.valuue) ffrom vv$syssstat aa,v$syysstatt b,v$syssttat c wherre a.nname=sortss (memmory) and b.namme=soorts (memorry) aand =sortts (diisk);Parse tto exeecute ratioo：在生產(chǎn)環(huán)環(huán)境，最理想想狀態(tài)是一條條sql語(yǔ)句一一次解析多數(shù)數(shù)運(yùn)行。公式：1 - (parsse couunt/exxecutee counnt)執(zhí)行：select 1-(a.valuee/b.vaalue) from v$syssstat

13、a,v$syssstat b wheere =parsse couunt (ttotal) andd b.naame=eexecutte couunt;Parse CCPU too totaal CPUU ratiio：該項(xiàng)顯顯示總的CPPU花費(fèi)在執(zhí)執(zhí)行及解析上上的比率。如如果這項(xiàng)比率率較低，說(shuō)明明系統(tǒng)執(zhí)行了了太多的解析析。公式：1 - (parsse timme cpuu / CPPU useed by this sessiion)執(zhí)行：select 1-(a.valuee/b.vaalue) fromm v$syysstatt a,v$syssttat b wherre a.nname=p

14、arsee timee cpu and b.nname=CPU uused bby thiis sesssion;Parse ttime CCPU too parsse timme elaapsed：通常，該項(xiàng)項(xiàng)顯示鎖競(jìng)爭(zhēng)爭(zhēng)比率。這項(xiàng)項(xiàng)比率計(jì)算是是否時(shí)間花費(fèi)費(fèi)在解析分配給CPU進(jìn)行周周期運(yùn)算(即生產(chǎn)工作作)。解析時(shí)間間花費(fèi)不在CCPU周期運(yùn)運(yùn)算通常表示示由于鎖競(jìng)爭(zhēng)爭(zhēng)導(dǎo)致了時(shí)間間花費(fèi)。公式：parsse timme cpuu / paarse ttime eelapseed執(zhí)行：select a.vallue/b.valuee froom v$ssysstaat a,vv$syssstat bb

15、 wheere =parsse timme cpuu andd b.naame=pparse time elapssed;從V$SYSSSTAT獲取取負(fù)載間檔(Load Profiile)數(shù)據(jù)據(jù)負(fù)載間檔是是監(jiān)控系統(tǒng)吞吞吐量和負(fù)載載變化的重要要部分，該部部分提供如下下每秒和每個(gè)個(gè)事務(wù)的統(tǒng)計(jì)計(jì)信息：loogons cumullativee, parrse coount (totall), paarse ccount (hardd), exxecutees, phhysicaal reaads, pphysiccal wrrites, blocck chaanges, and redo size.被

16、格式化的數(shù)據(jù)據(jù)可檢查ratess是否過(guò)高，或或用于對(duì)比其其它基線數(shù)據(jù)據(jù)設(shè)置為識(shí)別別systeem proofile在在期間如何變變化。例如，計(jì)計(jì)算每個(gè)事務(wù)務(wù)中blocck chaanges可可用如下公式式：db blocck chaanges / ( uuser ccommitts + uuser rrollbaacks )執(zhí)行：select a.vallue/(b.vaalue+cc.valuue) ffrom vv$syssstat aa,v$syysstatt b,v$syssttat c wherre a.nname=db bllock cchangees annd =userr co

17、mmmits and e=useer rolllbackks;其它計(jì)算統(tǒng)計(jì)以以衡量負(fù)載方方式，如下：Blocks changged foor eacch reaad：這項(xiàng)顯顯示出bloock chhangess在blockk readds中的比例例。它將指出出是否系統(tǒng)主主要用于只讀讀訪問(wèn)或是主主要進(jìn)行諸多多數(shù)據(jù)操作(如：inseerts/uupdatees/delletes)公式：db bblock changges / sessiion loogicall readds執(zhí)行：select a.vallue/b.valuee froom v$ssysstaat a,vv$syssstat bb

18、 wheere =db bblock changges aand e=sesssion logiccal reeads ;Rows foor eacch sorrt：數(shù)據(jù)排排序情況公式：sortts (roows) / ( soorts (memorry) + sortss (dissk) )執(zhí)行：select a.vallue/(b.vaalue+cc.valuue) ffrom vv$syssstat aa,v$syysstatt b,v$syssttat c wherre a.nname=sortss (rowws) aand b.namme=soorts (memorry) aand

19、=sortts (diisk);查看某表的約束束條件select consttraintt_namee, connstraiint_tyype,seearch_condiition, r_coonstraaint_nname ffrom uuser_cconstrraintss wheree tablle_namme = uupper(&tabble_naame);select c.connstraiint_naame,c.consttraintt_typee,cc.ccolumnn_namee froom useer_connstraiints cc,userr_conss_coluumns

20、 ccc whhere cc.owneer = uupper(&tabble_owwner) and c.tabble_naame = upperr(&taable_nname) annd c.oowner = cc.ownerr and c.connstraiint_naame = cc.coonstraaint_nname oorder by ccc.posiition;查看表的信息Select * froom useer_tabbles;查看表空間的名名稱及大小select t.tabblespaace_naame, rround(sum(bbytes/(10244*10244),0)

21、ts_ssize ffrom ddba_taablesppaces t, dbba_datta_filles d wheree t.taablesppace_nname = d.taablesppace_nname ggroup by t.tableespacee_namee;select t.tabblespaace_naame, rround(sum(bbytes/(10244*10244),0) ts_ssize from dba_ttablesspacess t, ddba_daata_fiiles dd whhere tt.tabllespacce_namme = dd.tablle

22、spacce_namme grooup byy t.taablesppace_nname;TABLESPPACE_NNAME TS_SIZE- -CWMLITEE 20DRSYS 20ODM 20PERFSTAAT 99SYSTEM 400查看回滾段名稱稱及大小select segmeent_naame, ttablesspace_name, r.sttatus, (innitiall_exteent/10024) IInitiaalExteent, (nextt_exteent/10024) NNextExxtent, maxx_exteents, v.currext CCurExttent

23、 From dba_rrollbaack_seegs r, v$roollstaat v Wherre r.ssegmennt_id = v.uusn(+) ordder byy segmment_nname ;移動(dòng)一個(gè)表的多多個(gè)分區(qū)BEGIN FOR x IN (SELEECT paartitiion_naame FRROM usser_taab_parrtitioons WHEERE taable_nname = BIG_TTABLE22) LOOPP EEXECUTTE IMMMEDIATTE aalter tablee big_tablee2 movve parrtitioon | x

24、.paartitiion_naame; END LOOP;END;/查看LOCKSELECT /*+ OORDEREED USEE_HASHH(H,R) */ H.SIID HOLLD_SIDD, R.SIID WAIIT_SIDD, decoode(H.type, ”MMR”, ”Meddia Reecoverry”, ”RRT”, ”Reddo Thrread”, ”UUN”, ”Useer Namme”, ”TTX”, ”Traansacttion”, ”TTM”, ”DMLL”, ”UUL”, ”PL/SQL UUser LLock”, ”DDX”, ”Disstribuuted XX

25、actioon”, ”CCF”, ”Conntrol File”, ”IIS”, ”Insstancee Statee”, ”FFS”, ”Fille Sett”, ”IIR”, ”Insstancee Recoovery”, ”SST”, ”Dissk Spaace Trransacction”, ”TTS”, ”Temmp Seggment”, ”IIV”, ”Libbrary Cachee Invaalidattion”, ”LLS”, ”Logg Starrt or Switcch”, ”RRW”, ”Roww Waitt”, ”SSQ”, ”Seqquencee Numbber”,

26、”TTE”, ”Exttend TTable”, ”TTT”, ”Temmp Tabble”, HH.typee) typpe, decoode(H.lmodee, 00, ”Nonne”, 1, ”Nulll”, 22, ”Roww-S (SSS)”, 3, ”Row-X (SXX)”, 44, ”Shaare”, 5, ”S/Roow-X (SSX)”, 66, ”Excclusivve”, to_char(H.lmoode) hold, deccode(rr.requuest, 0, ”Nonne”, 11, ”Nulll”, 2, ”Row-S (SSS)”, 33, ”Roww-X

27、(SSX)”, 4, ”Sharre”, 55, ”S/RRow-X (SSX)”,6, ”Excllusivee”, tto_chaar(R.rrequesst) rrequesst, R.IDD1,R.IID2,R.CTIMEEFROM VLLOCK HH,VLOCCK R WHEREE H.BLLOCK = 1 ANND R.BBLOCK=0 and HH.TYPEE ”MMR” ANND R.TTYPE ”MRR” AND H.ID11 = R.ID1 AAND H.ID2 = R.IDD2找ORACLEE字符集select * froom pps$ whhere nname=NLS_

28、CCHARACCTERSEET; 查看ORACLLE運(yùn)行的OS平臺(tái)SQL ruun beggin dbmms_outtput.pput_liine(dbbms_uttilityy.portt_striing); endd; /查看空間詳細(xì)使使用情況CREATE OR REEPLACEE PROCCEDUREE showw_spacce ( p_seegnamee IN VARCHHAR2, p_owwner IN VARCHHAR2 DDEFAULLT USEER, p_tyype IN VARCHHAR2 DDEFAULLT TAABLE, p_paartitiion IN VARCHHAR

29、2 DDEFAULLT NULLL )AS l_tootal_bblockss NNUMBERR; l_tootal_bbytes NNUMBERR; l_unnused_blockks NNUMBERR; l_unnused_bytess NNUMBERR; l_laastuseedextffileidd NNUMBERR; l_laastuseedextbblockiid NNUMBERR; l_laast_ussed_bllock NNUMBERR; PROCEDDURE p (p_llabel IN VAARCHARR2, p_num IIN NUMMBER) IS BEGIIN DD

30、BMS_OOUTPUTT.put_line (RPADD (p_llabel, 40, .) | p_num); END;BEGIN DBMSS_SPACCE.unuused_sspace (seegmentt_owneer = p_oowner, seegmentt_namee = p_seegnamee, ssegmennt_typpe = p_type, parttitionn_namee = p_partiition, ttotal_blockks = l_totall_bloccks, ttotal_bytess = l_totall_bytees, uunusedd_bloccks

31、 = l_unuseed_bloocks, uunusedd_bytees = l_unuseed_byttes, llast_uused_eextentt_filee_id = l_lastuusedexxtfileeid, laast_ussed_exxtent_blockk_id = ll_lasttusedeextbloocki laast_ussed_bllock = l_last_used_blockk ); p (Totall Bloccks, l_tottal_bllocks); p (Totall Bytees, ll_totaal_byttes); p (Unuseed B

32、loocks, l_unnused_blockks); p (Unuseed Byttes, l_unuused_bbytes); p (Last Used Ext FFileIdd, l_lastuusedexxtfileeid); p (Last Used Ext BBlockIId, ll_lasttusedeextbloockid); p (Last Used Blockk, l_last_used_blockk);END;/顯示緩沖區(qū)的相相關(guān)SQLSELECT tch, file#, dbaablk, CASE WHHEN obbj = 442949667295 THENN rbss

33、/comppat seegmenttELSE (SSELECTT MAX ( (| objject_ttype| ) | owwner| .| objject_nname) | DEECODE (COUNNT (*), 1, , maybbe!) FROMM dba_objeccts WHEREE dataa_objeect_idd = x.obj) END wwhatFROM (SSELECTT tch,fiile#,ddbablkk,obj FROM x$bh WHEREE statte 0 ORDDER BYY tch DESC) xWHERE RROWNUMM = 55;獲取生成的根據(jù)

34、據(jù)文件名select c.vallue |/ | d.iinstannce_naame | _orra_ |a.sppid | .trrc trrace ffrom vv$proccess aa,v$seessionn b,v$parammeter c,v$iinstannce d wheree a.adddr=b.paddrr and b.auddsid=uuserennv(seessionnid) and e=useer_dummp_desst ;在v$sesssion_llongopps視圖中，soofar字段段表示已經(jīng)掃掃描的塊數(shù)，totalwork表示總得需要掃描的塊數(shù)，所以我們即可

35、以對(duì)正在運(yùn)行的長(zhǎng)查詢進(jìn)行監(jiān)控，比如在索引創(chuàng)建時(shí)，查看索引創(chuàng)建的進(jìn)度，也可以查看系統(tǒng)中以往的長(zhǎng)查詢。col opnname fformatt a32col tarrget_ddesc fformatt a32col perrwork formaat a122set linnes 1331select sid,OOPNAMEE,TARGGET_DEESC,soofar,TTOTALWWORK,ttrunc(sofarr/totaalworkk*100,2)|% ass perwwork ffrom vv$sesssion_llongopps wheere soofar!=totallwork;se

36、t linnes 1221set pagges 9999col opnname fformatt a29col tarrget fformatt a29col tarrget_ddesc fformatt a12col perrwork formaat a122col remmain fformatt 99col staart_tiime foormat a21col soffar foormat 999999999col tottalworrk forrmat 9999999999col sqll_textt formmat a1101col buffgets formaat 9999999

37、999select opnamme,tarrget,tto_chaar(staart_tiime,yyy-mm-dd:hhh24:mii:ss) starrt_timme,elaapsed_seconnds ellapsedd,execcutionns exeecs,buuffer_gets/decodde(exeecutioons,0,1,exeecutioons) bbufgetts,moddule,ssql_teext frrom v$sessiion_loongopss sl,vv$sqlaarea ssa wheere sll.sql_hash_valuee = saa.hashh_

38、valuue andd uppeer(subbstr(mmodulee,1,4) RMANN andd subsstr(oppname,1,4) RRMAN and mmodulee SQL*PPlus and ssl.staart_tiimetrrunc(ssysdatte) orrder bby staart_tiime;IO事件監(jiān)控1. 監(jiān)控事例例的等待select eventt,sum(decodde(waiit_Timme,0,00,1) Prevv, sum(decodde(waiit_Timme,0,11,0) Currr,couunt(*) Tott froom v$ssess

39、ioon_Waiit grouup by eventt ordeer by 4;2. 回滾段的的爭(zhēng)用情況select name, waitts, geets, wwaits/gets Ratiio froom v$rrollsttat a, v$roollnamme b whhere aa.usn = b.uusn;3. 監(jiān)控表空空間的 I/O 比例select df.taablesppace_nname nname,ddf.fille_namme fiile,ff.phyrrds pyyr, f.phhyblkrrd pbrr,f.phhywrtss pyw, f.phhyblkwwrt p

40、bbw from v$fillestatt f, ddba_daata_fiiles ddf wheree f.fiile# = df.ffile_iid orderr by ddf.tabblespaace_naame;4. 監(jiān)控文件件系統(tǒng)的 II/O 比例例select substtr(a.ffile#,1,2) #, substtr(a.nname,11,30) Namee, a.sstatuss, a.bbytes, b.phhyrds, b.phhywrtss from v$dattafilee a, vv$fileestat b wherre a.ffile# = b.ffile#

41、;在某個(gè)用戶下找找所有的索引引select user_indexxes.taable_nname, user_indexxes.inndex_nname,uuniqueeness, coluumn_naame frrom usser_innd_collumns, userr_indeexes wwhere user_ind_ccolumnns.inddex_naame=usser_inndexess.indeex_namme andd userr_ind_colummns.taable_nname = userr_indeexes.ttable_name orderr by uuser_iin

42、dexees.tabble_tyype, uuser_iindexees.tabble_naame, uuser_iindexees.inddex_naame, ccolumnn_posiition;監(jiān)控 SGA 的命中率select a.vallue + b.vallue llogicaal_reaads, c.vallue pphys_rreads, rouund(1000* (a.vallue+b.valuee)-c.vvalue) / (a.vaalue+bb.valuue) BUFFEER HITT RATIIO frrom v$syssttat a, v$syysstatt b,

43、vv$syssstat cc wwhere a.staatistiic# = 38 annd b.sstatisstic# = 39 and c.staatistiic# = 40;監(jiān)控 SGA 中字典緩沖沖區(qū)的命中率率 select parammeter, getss,Getmmissess , geetmissses/(ggets+ggetmissses)*100 miss ratioo, (1-(sum(ggetmissses)/ (summ(getss)+summ(getmmissess)*100 Hit rratio ffrom vv$rowccache wwhere gets+ge

44、tmiisses 0 grooup byy paraameterr, getts, geetmissses; 監(jiān)控 SGA 中共享緩存存區(qū)的命中率率，應(yīng)該小于于1%select sum(ppins) Totaal Pinns, ssum(reeloadss) Tootal RReloadds, summ(relooads)/sum(ppins) *100 libcaache froom v$llibrarrycachhe; seleect suum(pinnhits-reloaads)/ssum(piins) hit rradio,sum(reloaads)/ssum(piins) reloa

45、ad perrcent from v$libbraryccache;顯示所有數(shù)據(jù)庫(kù)庫(kù)對(duì)象的類別別和大小 selecct couunt(naame) nnum_innstancces ,ttype ,sum(ssourcee_sizee) souurce_ssize , sum(parseed_sizze) paarsed_size ,sum(code_size) codee_sizee ,summ(erroor_sizze) errror_ssize, sum(ssourcee_sizee) +suum(parrsed_ssize) +sum(code_size) +summ(erroor_s

46、izze) siize_reequireed from dba_oobjectt_sizee groupp by ttype oorder by 2;監(jiān)控 SGA 中重做日志志緩存區(qū)的命命中率，應(yīng)該該小于1% SELECT name,gets,missees,immmediatte_getts, immmediaate_miisses,Decodde(getts,0,00,missses/geets*1000) raatio1, Decoode(immmediaate_geets+immmediaate_miisses,0,0, immeddiate_missees/(immmediaate_

47、geets+ immeddiate_missees)*1000) raatio2 FROM v$lattch WHHERE nname IIN (rredo aallocaation, reedo coopy);監(jiān)控內(nèi)存和硬盤盤的排序比率率，最好使它它小于 .110，增加 soort_arrea_siize:SELECT name, valuue FROOM v$ssysstaat WHEERE naame INN (soorts (memorry), sortts (diisk);監(jiān)控當(dāng)前數(shù)據(jù)庫(kù)庫(kù)誰(shuí)在運(yùn)行什什么SQL語(yǔ)句SELECT osuseer, ussernamme, sqql_tex

48、xt froom v$ssessioon a, v$sqlltext b wheere a.sql_aaddresss =b.addreess orrder bby adddress, piecce;監(jiān)控字典緩沖區(qū)區(qū)select (sum(pins - relloads) / ssum(piins) lib ccache fromm v$liibraryycachee; selecct (suum(getts - ggetmissses - usagge - ffixed) / ssum(geets) row ccache fromm v$roowcachhe; seleect suum(pi

49、nns) eexecuttions, summ(relooads) cachhe missses wwhile execuuting fromm v$liibraryycachee;后者除以前者,此比率小于于1%,接近0%為好。select sum(ggets) dicttionarry getts,suum(gettmissees) ddictioonary cachee get missees ffrom vv$rowccache;監(jiān)控 MTS select busy/(busyy+idlee) shhared serveers buusy ffrom vv$disppatcheer; 此

50、值大于0.55時(shí)，參數(shù)需需加大。 select sum(wwait)/sum(ttotalqq) diispatccher wwaits fromm v$quueue wwhere type=disppatcheer; select countt(*) ffrom vv$disppatcheer; select serveers_hiighwatter frrom v$mts; serverss_highhwaterr接近mts_max_sserverrs時(shí)，參數(shù)數(shù)需加大碎片程度select tableespacee_namee,counnt(tabblespaace_naame) ffrom

51、 ddba_frree_sppace ggroup by taablesppace_nname haaving countt(tabllespacce_namme)100;alter ttablesspace name coaleesce; alter ttable name dealllocatee unussed;create or reeplacee vieww ts_bblockss_v as select tableespacee_namee,blocck_id,bytess,bloccks,ffree sspace segmment_nname ffrom ddba_frree_s

52、ppace uniion alll select tableespacee_namee,blocck_id,bytess,bloccks,seegmentt_namee fromm dba_extennts;select * froom ts_blockks_v;select tableespacee_namee,sum(bytess),maxx(bytees),coount(bblock_id) ffrom ddba_frree_sppace groupp by ttablesspace_name;查看碎片程度高高的表SELECT segmeent_naame taable_nname ,

53、COUNNT(*) extennts FRROM dbba_seggmentss WHERRE ownner NOOT IN (SYSS, SSYSTEMM) GRROUP BBY seggment_name HAVINNG COUUNT(*) = (SSELECTT MAX( COUNNT(*) ) FROOM dbaa_segmments GROUPP BY ssegmennt_namme);表、索引的存儲(chǔ)儲(chǔ)情況檢查select segmeent_naame,suum(byttes),ccount(*) exxt_quaan froom dbaa_exteents wwhere table

54、espacee_namee =&taablesppace_nname and ssegmennt_typpe=TAABLE groupp by ttablesspace_name, segmeent_naame;select segmeent_naame,coount(*) froom dbaa_exteents wwhere segmeent_tyype=IINDEX and ownerr=&owwner grouup by segmeent_naame;找使用CPU多多的用戶seessionncpu useed by this sessiion：查找找各回話連接接的CPU使用率率selec

55、t a.sidd,spidd,stattus,suubstr(ogram,1,40) progg,a.teerminaal,osuuser,vvalue/60/1000 vallue from v$sesssion a,v$pprocesss b,vv$sessstat cc wheere c.statiistic#=12 aand c.sid=aa.sid and aa.padddr=b.aaddr oorder by vaalue ddesc;尋找CPU使用用過(guò)量的seessionnSELECT sql_ttext FFROM vv$sqlttext aa WHERRE (a.hash_v

56、aluee, a.aaddresss) INN (SELLECT ddecodee(sql_hash_valuee, 0, prev_hash_valuee, sqll_hashh_valuue), ddecodee(sql_hash_valuee, 0, prev_sql_aaddr, sql_aaddresss) FFROM vv$sesssion bb WHERRE b.ppaddr = (SEELECT addr FROM v$proocess c WHHERE cc.spidd = &pid) ORRDER BBY pieece ASSC;數(shù)據(jù)庫(kù)巡檢管理理日常檢測(cè)檢測(cè)系統(tǒng)卷狀態(tài)態(tài)df

57、 -k可以以看到系統(tǒng)各各個(gè)卷的使用用情況; 如果文件件系統(tǒng)的剩余余空間小于 20% ，需需刪除不用的的文件以釋放放空間。 或找系統(tǒng)管管理員調(diào)整空空間大小。檢查表空間的使使用情況 SELECT tabllespacce_namme, maax_m, countt_bloccks frree_bllk_cntt, sum_ffree_mm, to_chhar(1000*summ_freee_m/suum_m, 99.999) | % AS ppct_frree FRROM ( SELLECT ttablesspace_name, sum(bbytes)/10244/10244 AS sum_mm

58、FROMM dba_data_filess GROUPP BY tabllespacce_namme), ( SELEECT taablesppace_nname AAS fs_ts_naame, mmax(byytes)/1024/1024 AS maax_m, countt(bloccks) AS countt_bloccks, ssum(byytes/11024/11024) AS sum_free_m FROOM dbaa_freee_spacce GROOUP BYY tabllespacce_namme ) WHEREE tabllespacce_namme = ffs_ts_na

59、me:select sum(bbytes)/(10224*10224) ass freee_spacce,tabblespaace_naame ffrom ddba_frree_sppace groupp by ttablesspace_name;檢測(cè)表空間狀態(tài)態(tài)DBA_FREEE_SPAACE是數(shù)據(jù)據(jù)庫(kù)的一個(gè)視視圖,可以看到數(shù)數(shù)據(jù)庫(kù)的空間間使用信息。SELECT tableespacee_namee ,summ(bytees)/10024/10024 MMB Freee FRROM dbba_freee_spaace WHHERE ttablesspace_name TEEMP GGROUP

60、 BY taablesppace_nname;檢測(cè)Oraclle運(yùn)行狀態(tài)態(tài)ORACLE的的狀態(tài)可以通通確認(rèn)所有的的 INSTTANCE 狀態(tài)正常 登陸到所有數(shù)據(jù)據(jù)庫(kù)或例程 , 檢測(cè) ORAACLE 后后臺(tái)進(jìn)程：$ps eff|grepp ora來(lái)來(lái)查看;檢查數(shù)據(jù)庫(kù)版本本信息sqlpluss “/ as sysdbba”select * froom v$vversioon;檢查日志文件和和 tracce 文件記記錄 aleert 和 tracce 文件中中的錯(cuò)誤連接到每個(gè)需管管理的系統(tǒng)，使用 teelnet 登陸，對(duì)每個(gè)個(gè)數(shù)據(jù)庫(kù) ,cd 到 bdummp 目錄 , 通常是是 $ORAACLE_B