Oracle-common monitoring SQL statements

Oracle-commonly used monitoring SQL statement 1. monitoring instance waiting: select event, sum (decode (wait_time, 1) prev, sum (decode (wait_time, 0) curr, count (*) from v $ session_wait group by event order by 4; 2. contention for rollback segments: www.2cto.com select name, waits, gets, waits/gets ratio from v $ rollstat a, v $ rollname B where. usn = B. usn; 3. i/O ratio of the monitored 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 dfwhere f. file # = df. file_id4. i/O ratio of the empty file system: select substr (. file #, 1, 2) "#", substr (. name, 1, 30) "name",. status,. bytes, B. phyrds, B. phywrtsfrom v $ datafile a, v $ filestat bwhere. file # = B. file #5. search for all indexes under a user: select user_indexes.table_name, user_indexes.index_name, uniqueness, column_namefrom user_ind_columns, user_indexeswhere user_ind_columns.index_name = user_indexes.inde X_nameand 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. monitoring SGA hit rate select. value + B. value "logical_reads", c. value "phys_reads", round (100 * (. value + B. value)-c. value)/(. value + B. value) "buffer hit ratio" from v $ sysstat a, v $ sysstat B, v $ sysstat cwhere. statistic # = 38 and B. statisti C # = 39 and c. statistic # = 40; 7. monitor the hit rate of the Dictionary Buffer in SGA: www.2cto.com 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. monitor the hit rate of the shared cache in SGA, which should be less than 1% select sum (pins) "Total Pins", sum (reloads) "Total Reloads", sum (reloads)/sum (pins) * 100 li Bcachefrom v $ librarycache; select sum (pinhits-reloads)/sum (pins) "hit radio", sum (reloads)/sum (pins) "reload percent" from v $ librarycache; 9. show the category 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_ob Ject_size group by type order by 2; 10. monitor the hit rate of the log cache area in SGA, which should be less than 1% SELECT name, gets, misses, immediate_gets, immediate_misses, Decode (gets, 100, misses/gets *) ratio1, decode (Keys + immediate_misses, 100, immediate_misses/(immediate_gets + immediate_misses) *) ratio2FROM v $ latch WHERE name IN ('redo allocation', 'redo copy'); 11. monitor the sort ratio of memory to hard disk, preferably make it smaller. 10, add sort_area_sizeSELECT name, value FROM v $ Sysstat WHERE name IN ('sorts (memory) ', 'sorts (disk)'); 12. monitor who is running the current database. SELECT osuser, username, SQL _text from v $ session a, v $ sqltext bwhere. SQL _address = B. address order by address, piece; 13. monitoring 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 divided by the former, which is less than 1%, close to 0% is better. Select sum (GETS) "dictionary gets", SUM (GETMISSES) "dictionary cache get misses" from v $ ROWCACHE14. find the ORACLE Character Set select * from sys. props $ where name = 'nls _ CHARACTERSET '; 15. monitor MTSselect busy/(busy + idle) "shared servers busy" from v $ dispatcher; when this value is greater than 0.5, the select sum (wait)/sum (totalq) parameter must be increased) "dispatcher waits" from v $ queue where type = 'dispatcher '; select count (*) from v $ dispatcher; select servers_highwater from v $ mts; servers_highwater is close to mts_max_servers, increase the parameter value by 16. 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 asselect tablespace_name, block_id, bytes, blocks, 'free space' segment_name from dba_free_spaceunion allselect tablespace_name, block_id, bytes, blocks, segment_name from dba_extents; select * from region; select tablespace_name, sum (bytes), max (bytes), count (block_id) from dba_free_space group by tablespace_name; View tables with high fragmentation levels SELECT segment_name table_name, COUNT (*) extentsFROM dba_segments WHERE owner not in ('sys ', 'system') group by segment_nameHAVING COUNT (*) = (select max (COUNT (*) FROM dba_segments group by segment_name ); 17. check the storage of tables and indexes www.2cto.com 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. If session12 is used by a user with more CPUs, It is CPU used by this sessionselect. sid, spid, status, substr (. program, 1, 40) prog,. terminal, osuser, value/60/100 valuefrom v $ session a, v $ process B, v $ sesstat cwhere c. statistic # = 12 and c. sid =. sid and. paddr = B. addr order by value desc; 19. monitor the usage of log_buffer: (the value should be smaller than 1%; otherwise, the size of log_buffer is increased.) select rbar. name, rbar. value, re. name, re. value, (rbar. value * 100)/re. value | '%' "radio" from v $ sysstat rbar, v $ sysstat rewhere rbar. name = 'redo buffer allocation retries' and re. name = 'redo entries'; 20. view the running SQL statement: SELECT SQL _TEXTFROM V $ SQL