1. Waiting for 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. Strike Square SELECT NAME, WAITS, GETS, WAITS / GETS "Ratio" from v $ ROLLSTAT A, V $ ROLLNAME B Where A.usn = B. USN; 3. Monitoring table space I / O ratio Select DF.TABLESPACE_NAME NAME, DF.FILE_NAME "file", F.Phyrds Pyr, F.Phyblkrd PBR, F.Phyword 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 file system I / O ratio 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. Under a user find all indexes 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, COL UMN_Position; 6. Monitor SGA's 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 $ SSSTAT A, V $ SYSSTAT B, V $ sysstat c where a.statistic # = 38 and c.statistic # = 40; 7. Monitor the hit rate of the Dictionary buffer in SGA Select Parameter, Gets, getMisses, getMisses / (Gets getMisses) * 100 "Miss Ratio", (1- (getMisses) / (SUM (Gets) Sum (GetMisses ))) * 100 "Hit Ratio" from V $ ROWCACHE WHERE GETS GETMISSES <> 0 group by parameter, gets, getMisses; 8. Monitor the hit rate of the CCA 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, Sum (Reloads ) / sum (pins) "reload percent" from v $ librarycache; 9. Show all size categories and database objects select count (name) num_instances, type, sum (source_size) source_size, sum (parsed_size) parsed_size, sum (code_size) code_size, sum (error_size) error_size, sum (source_size) sum (parsed_size) sum (code_size) sum (error_size) size_required from dba_object_size group by type order by 2; 10. monitoring SGA redo log buffer hit ratio it should be less than 1% SELECT name, gets, misses, immediate_gets, immediate_misses, Decode (gets, 0,0, misses / gets * 100) ratio1, Decode (immediate_gets immediate_misses, 0,0, immediate_misses / (immediate_gets immediate_misses) * 100) Ratio2 from V $ latch where name in ('redo allocation "; 11. Monitor memory and hard disk sorting ratio, it is best to make it less than .10, add sort_area_size SESSTAT, Value from V $ sysstat WHERE Name in ('sorts ",' sorts (disk)); 12. Monitor the current database Who is running what SQL statement Select Osuser, UserName, SQL_TE XT from V $ sessions a, v $ sqltext b where a.sql_address = b.address order by address = b.address; 13. Monitor Dictionary Buffer Select (SUM (Pins - Reloads)) / SUM (Pins) "Lib Cache" from V $ librarycache; select (sum (gets - getmisses - usage - fixed) / sum (gets) "Row Cache" from v $ rowcache; select sum (pins) "Executions", SUM (RELOADS) "Cache Misses While Executing" From V $ librarycache; the latter is less than 1%, which is close to 0%.
Select SUM "Dictionary Gets", SUM (GETMISSES) "Dictionary Cache Get Misses" from v $ ROWCACHE 14. Find Oracle Characters Select * from sys.props $ Where name = 'nls_characterset'; 15. Monitor MTS SELECT Busy / (Busy IDLE) "Shared Servers Busy" from V $ dispatcher; This value is greater than 0.5, parameters need to increase Select SUM (WAIT) / SUM (Totalq) "Dispatcher Waits" from V $ queue where type = 'dispatcher' ; select count (*) from v $ dispatcher; select servers_highwater from v $ mts; when servers_highwater close mts_max_servers, parameter 16. The need to increase the degree of fragmentation select tablespace_name, count (tablespace_name) from dba_free_space group by tablespace_name having count (tablespace_name)> 10 ; alter tablespace name coalesce; alter table name deallocate unused; create or replace view ts_blocks_v as select tablespace_name, block_id, bytes, blocks, 'free space' segment_name from dba_free_space union all select tablespace_name, block_id, bytes, blocks, segment_name from dba_extents; select * from TS_BLOCKS_V; SELECT TABLESPACE_NAME, SUM (Bytes), Max (Bytes), Count (Block_ID) from DBA_FREE_SPACE GROUP BY TABLESPA ce_name; High view Fragmented Table SELECT segment_name table_name, COUNT (*) extents FROM dba_segments WHERE owner NOT IN ( 'SYS', 'SYSTEM') GROUP BY segment_name HAVING COUNT (*) = (SELECT MAX (COUNT (*)) fROM dba_segments GROUP bY segment_name); 17. table stores the inspection select segment_name index, sum (bytes), count (*) ext_quan from dba_extents where tablespace_name = '& tablespace_name' and segment_type = 'tABLE' group by tablespace_name, segment_name; select Segment_name, count (*) from dba_extents where segment_type = 'index' and owner = '& oowner' group by segment_name;
