Spotlight监控Oracle

1、Spotlight的使用一、首先建立Oracle的连接第一步要新建connection,这样能够使用spotlight连接到要监测的数据库。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二、系统主界面系统主界面反映了系统的整体运行情况，如果系统哪方面出现问题，会报相应的警告，不同级别显示不同的颜色，最严重为红色警告。然后据此警告可下钻到相应的子窗口，查看相应情况。下面介绍各子窗口。essionsSpotlightonOracleclpn背辿已网Fm”orit已匚”居旦已Ip:酒g心|LiveconnectionsDisFStoracObOcWiF；二1甲ating.yP&Alartst5D0WUsidXUWZB

3、ckgrourAProcesses1.Session面板:ResponsevlO,系统的响应时间TotalUsers:总用户SESSION数ActiveUsers:当前正在执行的用户SESSION数Host面板:CPU利用率内存的使用情况ServerProcesses面板：显示服务器进程的信息PGATarget/Used显示PGA目标总数及当前使用数dedicated显示专用服务器进程的个数Shared显示共享服务器进程的个数。Dispatchers显示dispathers的个数.JobQueue显示作业进程的个数SGA面板：显示SGA中各组件的内存使用情况(l)CurrentSize显示当前

4、sga使用M数(2)BufferCache,KeepPool,RecyclePool显示数据缓冲区的内存情况SharedPool:共享池的使用情况RedoLog:重作日志的使用情况LargePool:大池的使用情况JavaPool:java池的使用情况Backgroundprocess面板-后台进程面板:显示与磁盘I/O相关的后台进程。DBWR数据写入进程LGWR日志进程ARCH规档进程式磁盘存储面板:显示主要数据库文件的情况(控制文件除外)DatabaseFiles:显示数据文件使用情况。联机日志文件情况。包括组数及大小。归档日志情况。三、TopSessions通过topSession面板可

5、以查看当前哪个session当前占用了大量的资源，以此定位数据库问题。单击上部列表，会在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:根据条件来查看libraryCache中相应的sql.主要的选项为sorting可以据此来找出影响大的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六、配置和内存：主要显示SGA的使用情况及参数配置情况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