oracle性能监控（Oracle性能监控）.docVIP

oracle性能监控（Oracle性能监控） 1. wait for monitoring cases 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; 2. rollback contention Select, name, waits, gets, waits/gets, Ratio. From, v$rollstat, a, v$rollname, B Where a.usn = b.usn; 3. monitor the I/O ratio of tablespace Select, df.tablespace_name, name, df.file_name, file, f.phyrds, pyr, F.phyblkrd, PBR, f.phywrts, PYW, f.phyblkwrt, pbw From, v$filestat, F, dba_data_files, DF Where f.file# = df.file_id Order by df.tablespace_name; 4. 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.phywrts From, v$datafile, a, v$filestat, B Where a.file# = b.file#; 5. find all of the indexes under a user Select, user_indexes.table_name, user_indexes.index_name, uniqueness, column_name From, user_ind_columns, user_indexes Where user_ind_columns.index_name = user_indexes.index_name And user_ind_columns.table_name = user_indexes.table_name Order, by, user_indexes.table_type, user_indexes.table_name, User_indexes.index_name, column_position; 6. monitor SGA hit rate 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$sysstat, a, v$sysstat, B, v$sysstat, C Where, a.statistic# = 38, and, b.statistic# = 39 And c.statistic# = 40; 7. monitoring 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$rowcache Where gets+getmisses 0 Group, by, parameter, gets, getmisses; 8. monitoring the hit rate of shared caches in SGA should be less than 1% Select sum (pins), Total Pins, sum (reloads), Total Reloads, Sum (reloads), /sum (pins), *100, libcache From v$librarycache; Select, sum (pinhits-reloads), /sum (pins), hit, radio, s