Spotlight監(jiān)控Oracle

1、Spotlight的使用一、首先建立Oracle的連接第一步要新建connection,這樣能夠使用spotlight連接到要監(jiān)測的數(shù)據(jù)庫。Tj|SpotlightFileViewFavoritesTuulsHelpHfi八？WelcometoSpotlightSpotlightConnectionKanager*11PrevioustipLpPropertiesTxGeneralDetails1Oracle匚oni已匚tiondetailsConnectionString:LPMTEST_128.6496.46V|Orad已Usernam已：匚IpmtestOraclePassword:Se

2、rverconne匚tidideteiib廠unitorOS；兩2日”已p日55岡iMd日已tails（forthis匚匚iii已匚tion）OK|CancelHelp二、系統(tǒng)主界面系統(tǒng)主界面反映了系統(tǒng)的整體運行情況，如果系統(tǒng)哪方面出現(xiàn)問題，會報相應的警告，不同級別顯示不同的顏色，最嚴重為紅色警告。然后據(jù)此警告可下鉆到相應的子窗口，查看相應情況。下面介紹各子窗口。essionsSpotlightonOracleclpn背辿已網(wǎng)Fm”orit已匚”居旦已Ip:酒g心|LiveconnectionsDisFStoracObOcWiF；二1甲ating.yP&Alartst5D0WUsidXUWZB

3、ckgrourAProcesses1.Session面板:ResponsevlO,系統(tǒng)的響應時間TotalUsers:總用戶SESSION數(shù)ActiveUsers:當前正在執(zhí)行的用戶SESSION數(shù)Host面板:CPU利用率內(nèi)存的使用情況ServerProcesses面板：顯示服務器進程的信息PGATarget/Used顯示PGA目標總數(shù)及當前使用數(shù)dedicated顯示專用服務器進程的個數(shù)Shared顯示共享服務器進程的個數(shù)。Dispatchers顯示dispathers的個數(shù).JobQueue顯示作業(yè)進程的個數(shù)SGA面板：顯示SGA中各組件的內(nèi)存使用情況(l)CurrentSize顯示當前

4、sga使用M數(shù)(2)BufferCache,KeepPool,RecyclePool顯示數(shù)據(jù)緩沖區(qū)的內(nèi)存情況SharedPool:共享池的使用情況RedoLog:重作日志的使用情況LargePool:大池的使用情況JavaPool:java池的使用情況Backgroundprocess面板-后臺進程面板:顯示與磁盤I/O相關的后臺進程。DBWR數(shù)據(jù)寫入進程LGWR日志進程ARCH規(guī)檔進程式磁盤存儲面板:顯示主要數(shù)據(jù)庫文件的情況(控制文件除外)DatabaseFiles:顯示數(shù)據(jù)文件使用情況。聯(lián)機日志文件情況。包括組數(shù)及大小。歸檔日志情況。三、TopSessions通過topSession面板可

5、以查看當前哪個session當前占用了大量的資源，以此定位數(shù)據(jù)庫問題。單擊上部列表，會在sessionInformation中顯示該會話的所有詳細信息。ss23oracle56SYS(CKPT00SIDSYS(匚KPT)斗ResourcmOracleuserMachineO5userSerial#:05user:ClientPID:ServerPID:Physicalreads:CPUutilization(ms):Hitratio(%):xmkf_A41I”?5essions諂閔iis廻!間申碣riBBPBBaraNoneFound*SpotlightonOracle-clpnFileVie

6、wFavoritesToolsHelp也伺出a餉祈得1(CKPTjInwaiting(05j她aamwESiEia14W513014SJ751305essionInformation匚LPMTESTSYS(PMON)SYS(DBWO)SYS(LGWR)WORKGROUPLIJUNLIxmkf_A斗1xmkf_A41xmkf_A41SYS(SMON)SYS(RECO)Kmkf_A41Kmkf_A41fflUEMU也JjaiM335005QLlilijunoracleoracleoracleoracleoracleProgram:E；l匚kgets:Consistentgets:：Bio匚k匚ha

7、nges:Waitstate/time;WaitingFor:Mostrecent5QL:OracleSID:Username;Server:Status:下面是幾個應注意的地方：mostrecentsql:可以用來確定當前占用資源最大的sql語句Sessionwaits:可以找出與該session相關的等待事件。SessionInformationSessionInformationEventWaitsWait/s%totalWaittime(ms)Waittime/scontrolfileparallelwrite5.00.32100.005.14.33latchfree.00.00.00

8、drnntrnlflippnijpntiAl口Aid.nn.nn.nn.nn.nn1LlsessionWaits5ession5SessionLocksTrackSessionClKArlp口TmCGIICPK1廠IIKKAnhIAIAil,ResourcmUsageSessionInformationSessionInformationSessionInformationSessionInformationSessionlocks:顯示相關的鎖信息。SessionInformationSessionInformationSessionInformationSessionInformation

9、四、topsql:根據(jù)條件來查看libraryCache中相應的sql.主要的選項為sorting可以據(jù)此來找出影響大的sqlMManasql(Allusers)Sorting#SpotlightonOracle-clpFileViev-.1FavoritesToolsHelp5ele匚ticinCriteriaMinimumexeojtions:Parsinguser;Contents;MinimumbuffergetshqjMinimumdiskreads:Minimumrows;Minimumbuffergets/exec:Minimumdiskreads/exe匚;Minimumrow

10、s/exec:TupSQLBuffergetsSortby:AscendingorderDescendingorderFetchSQLClear匚riteria點擊FetchSQL按鈕，可以查看到相應的SQL語句fr-SpotlightonOracle一clpnFile迪曰mFavorit已名ToolsHelp7032fr-SpotlightonOracle一clpFileViewFavoritesToolsHelpT722.B3422.B3613,92513,9196,602,1696,213B540,836,85714.6B46.327.BB3697,669.39661select.fro

11、mwli_worklist_tasktaskjwli_worklist_task_state吐誠巳wli_worklist_datare(22,83632,082,8219.6421,404.92select匚口urit(*)asojuritmrfrom(selectIs.customer-nuiTijIs.customer-namejIs.contract,13,92723,206,0006.9791,666.56select匚ount(*)ascounterfrom(select*from(selectIs.customer-name,Is.customer_m27B.49123,205,

12、6026.9733,7261,667.19select*from(selectrow_.rownumrownum_from(select*from(selectb.custome.19,193,4805.77536,0B619,193,480.00536select/*+nested_table_get_refs+*/clpmwli.wli_process_event.*fromdpmwli.wl.13,309,2104.0032.02select.fromdocument_type_definitiondoctypeboO_where(doctypeboO_.document41,631B,

13、342,4092.51744,0682,70003.0024Sselecta,customer-nuiTija.handling_user_numjcount(contract_num)匚口ntract_numfrorrA6,598,119IB,100.36,200,90010.SB05.49select.fromdocument_clir_definitiondocfbrderconnectbydocforder.document_clirj456,74032,004,1859.6435,9651,405.11select*from(selectrow_.*trownumrownum_fro

14、m(selectIs.customer-nuiTijIs.custoi52,637.5,915,6271.7B252952.14selectdocumentstO_.doCLjmmrit_ty|om_udas匚口1_匚i_0_from日口匚uments_type_acldocuir11,5005,590,8071.6B.x/XHe丄.350,0312,795,403.50175biteriaSQLI93w3i%orcocalUv或細耐就他iwgagjtKiMgaMTup5QL五、Activity:這個窗口，主要提供了等待事件，鎖等待，閆鎖等待，當前事務等。fr-SpotlightonOracl

15、e一clpnFile迪曰mFavorit已名ToolsHelpfr-SpotlightonOracle一clpnFile迪曰mFavorit已名ToolsHelp“AnOAIs取lhl#=|B8383BH88H3HActivitykSummaryWaits|WaitingEventsEb匚kingLo匚ksLogicalIOBos-s75CDco_d8.10006,0004.00U2000E；l匚k匚hangesCurrentreadsConsistentreads0-14:30o14:3014:5015:0015:10RedowritesDatafilewriteDatafilereadsP

16、hysicalIOuooouoouooT乙senondooooo4321cm-匸OQ-14:3014:5015:106004002000-14:3014:50mswmirsIT;-1?:DBbuffer-Bu.DiskI/O-Con.DiskI/O-DBf.DiskI/O-Dat.15:10六、配置和內(nèi)存：主要顯示SGA的使用情況及參數(shù)配置情況pCDrnITInro_uo_uo864214:3014:5015:10fr-SpotlightonOracle一clpnFile迪曰mFavorit已名ToolsHelp0SGAStorageSummary(KB)sharedpoolsharedpoo

17、lsharedpoolsharedpoolsharedpoolsharedpoolsharedpoolsharedpoolsharedpoolsharedpoolsharedpoolsharedpoolPooljoxsheapinitfixedallocation匚allbacktriggerinformtriggersourcePLSnon-libhptriggerdefiniKQRSSOKGKheappartitioningdPL/SQLSOURCEtabledefinitiPL/SQLPPCODEName輕SpotlightonOracle-clpileViewFavoritesTool

18、sHelpSGAUtilizationbuffer匚日匚!sharedpooljavapoollargepoolotherLiveconne匚ti口ns*ConFigurdtion&Memory200400ME：6008001,000fr-SpotlightonOracle一clpnFile迪曰mFavorit已名ToolsHelpfr-SpotlightonOracle一clpnFile迪曰mFavorit已名ToolsHelp七、磁盤存儲情況:顯示表空間利用率和在線日志的使用情況。紙SpotlightonOracle一clpaFileViewFavoritesToolsHelp紙SpotlightonOracle一clpaFileViewFavoritesToolsHelp紙SpotlightonOracle一clpaFileViewFavoritesToolsHelp紙SpotlightonOracle一clpaFileViewFavoritesToolsHelpLiveconnections匕DiskStorage紙Sp