**** Waiting for the case of the survey **** SELECT EVENT, SUM (Decode (Wait_Time, 0, 0, 1)) "prev", SUM (Decode (Wait_Time, 0, 1, 0)) "Curr", Count (*) "Tot" from V $ session_wait group by Event ORDER BY 4;
**** Surveillance Strong Segments **** Select Name, Waits, Gets, Waits / Gets "Ratio" from V $ ROLLSTAT A, V $ ROLLNAME BWHERE A.USN = B.USN;
**** Surveillance table space I / O ratio **** select df.tablespace_name name, df.file_name "file", f.phyrds pyr, f.phyblkrd PBR, F.PhyWRTS PYW, F.Phyblkwrt PBWFROM V $ Filestat F, DBA_DATA_FILES DFWHERE F.FILE # = DF.FILE_IDORDER BY DF.TABLESPACE_NAME;
**** Monitor the I / O ratio of the file system **** SELECT SUBSTR (A.File #, 1, 2) "#", Substr (A.Name, 1, 30) "Name", A.status, A.BYTES, B.PHYRDS, B.PHYWRTSFROM V $ DATAFILE A, V $ filestat bwhere a.file # = b.file #;
**** Monitor SGA's hits **** SELECT A.VALUE B.VALUE "Logical_Reads", C.Value "Phys_Reads", Round (100 * ((A.Value B.Value) -c.value ) / (A.Value b.Value) "Buffer Hit Ratio" from V $ SSISSTAT A, V $ SYSSTAT B, V $ sysstat cwhere a.statistic # = 38 and b.statistic # = 39 and C.Statistic # = 40;
**** Monitor the hit rate of the Dictionary buffer in SGA **** Select Parameter, Gets, GetMisses, getMisses / (Gets getMisses) * 100 "Miss Ratio", (1- (SUM (getMisses) / (SUM Gets) SUM (getMisses))))))))))))) * 100 "Hit Ratio" from V $ ROWCACHEWHERE GETS GETMISSES <> 0Group by parameter, gets, getMisses;
**** Monitor the hit rate of the CPCs in SGA, should be less than 1% **** Select SUM (Pins) "Total Pins", SUM (Reloads) "Total Reloads", SUM (Reloads) / SUM (Pins) * 100 Libcachefrom v $ librarycache;
Select SUM (PINHITS-RELOADS) / SUM (PINS) "Hit Radio", SUM (RELOADS) / SUM (PINS) "Reload Percent" from v $ librarycache
**** Monitor the hit rate of the SGA-heavy log buffer, should be less than 1% **** SELECT NAME, GETS, MISSES, IMMEDIATE_GETS, IMMEDIATE_MISS, DECODE (Gets, 0, 0, Misses / Gets * 100) Ratio1 , Decode (immediate_gets immediate_misses, 0,0, immediate_misses / (immediate_gets immediate_misses) * 100) ratio2FROM v $ latch WHERE name iN ( 'redo allocation', 'redo copy'); **** monitor memory and hard disk ordering The ratio is best to make it less than .10, otherwise you need to add sort_area_size **** Sort_AME, Value from V $ SSSTAT WHERE NAME IN ('Sorts',' Sorts (Disk);
