SQL statements for Oracle database monitoring

SQL statements for Oracle database monitoring

1. Waiting for monitoring cases:
　　
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. rollback segment contention:
　　
Select name, waits, gets, Waits/gets ratio fromv $ rollstat A, V $ rollname B where a. USN = B. USN;
　　
3. I/O ratio of the monitored tablespace:
　　
Selectdf. tablespace_namename, DF. file_name "file", F. phyrds Pyr, F. phyblkrd PBR, F. phywrtspyw, F. phyblkwrtpbw fromv $ filestatf, dba_data_files DF
　　
Where F. File # = DF. file_id
　　
4. Search for all indexes under a user:
　　
Selectuser_indexes.table_name, user_indexes.index_name, uniqueness, column_namefrom 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;
　　
5. 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;
　　
6. 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;
　　
7. Monitor the hit rate of the shared cache area in SGA, which 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;
　　
8. display 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_sizecode_size, sum (Er
　　
Ror_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;
　　
9. Monitor the log cache hit rate in SGA, which should be less than 1%
　　
Select name, gets, misses, immediate_gets, immediate_misses,
　　
Decode (gets, 100, misses/gets *) 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 ');
　　
10. Monitor the sorting ratio of memory to hard disk. It is best to make it smaller than 10 and increase sort_area_size.
　　
Select name, value from V $ sysstat where name in ('sorts (memory) ', 'sorts (Disk )');
　　
11. Monitor who is running the SQL statement of the current database
　　
Select osuser, username, SQL _text from V $ session A, V $ sqltext B
　　
Where a. SQL _address = B. Address order by address, piece;
　　
12. 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. This ratio is less than 1%, and it is better to approach 0%. Select sum (gets) "dictionary gets", sum (getmisses)
"Dictionary cache get misses" from V $ rowcache
　　
13. Find the Oracle Character Set
　　
Select * From SYS. Props $ where name = 'nls _ characterset ';
　　
14. Monitor MTS
　　
Select busy/(busy + idle) "shared servers busy" from V $ dispatcher;
　　
When the value is greater than 0.5, the parameter must 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, increase the Parameter
　　
15. Fragmentation level
　　
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
　　
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 tables with high fragmentation levels
　　
Select segment_name table_name, count (*) extents
　　
From dba_segments where owner not in ('sys ', 'system') group
　　
Segment_name
　　
Having count (*) = (select max (count (*) from dba_segments Group
　　
By segment_name );
　　
16. Check the storage of tables and Indexes
　　
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;
　　
17. Find a user session with multiple CPUs
　　
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 v $ session A, V $ process B, V $ sesstat C
　　
Where C. Statistic # = 12 and C. Sid = A. Sid and A. paddr = B. ADDR order
　　
Value DESC;
　　
18. Monitor the usage of log_buffer: (the value should be smaller than 1%; otherwise, increase the size of log_buffer)
　　
Seletname, rbar. Value, re. Name, re. value,
　　
(Rbar. Value * 100)/Re. Value '%' "radio"
　　
From v $ sysstat rbar, V $ sysstat re
　　
Where rbar. Name = 'redo Buffer Allocation retries'
　　
And re. Name = 'redo entries ';
　　
19. view the running SQL statements:
　　
Select SQL _textfrom v $ SQL 10 Gb webspace, 256 MB RAM tuned by German engineers

20. Check that the current database has several user connections:

Select username, Sid, serial # from V $ session;

 

Reproduced in: http://hi.baidu.com/suozw/item/ac8dc644406b8b2210ee1ed8