Waiting for the monitoring case
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. Strive to roll
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 the table space
Select Df.TablesPace_Name Name, DF.File_name "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 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 the hit rate of SGA
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. 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 $ ROWCACHE WHERE GETS GETMISSES <> 0 Group by parameter, gets, getMisses; 8. Monitoring 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 the categories and size of all 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. Monitor the hit rate of the SGA medium-red log buffer, 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', 'redo copy');
11. Monitor memory and hard disk sorting ratio, it is best to make it less than .10, increase sort_area_size
Select Name, Value from V $ Sysstat WHERE Name in ('sorts (a ");
12. Monitor the current database Who is running what SQL statement
Select OSuser, Username, SQL_Text from V $ Session A, V $ SQLText B Where A.SQL_Address = B.Address Order by Address, Piece
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 except for the former, this ratio is less than 1%, close to 0% is good.
Select Sum (Gets) "Dictionary Gets", SUM (GetMisses) "Dictionary Cache Get Misses" from v $ ROWCACHE
14. Looking for Oracle character sets
Select * from sys.props $ where name = 'nls_characterset';
15. Monitor MTS
Select Busy / (Busy IDle) "Shared Servers Busy" from V $ dispatcher;
When this value is greater than 0.5, the parameters need to be increased.
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 is close to MTS_MAX_SERVERS, the parameters need to increase
16. Degradation
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 TableSpace_name;
View the high degree of fragmentation
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, indexed storage check
select segment_name, 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 = '& Owner' group by segment_name;
18, find more users with CPU session
12 is CPU Used by this session
Select A.SID, SPID, STATUS, SUBSTR (A.Program, 1,40) PROG, A.TERMINAL, OSUSER, VALUE / 60/100 VALUE FROM VALUE / 60/100 VALUE FROM VALUE / 60/100 VALUE FROM V $ SESSION A, V $ Process B, V $ SESSSTAT C Where C .statistic # = 12 and c.SID = a.sid and a.paddr = b.addr Order by value desc;
