SQL statement for Oracle database performance monitoring

1. Waiting for monitoring cases

Select event, sum (decode (wait_time, 0, 0, 1) "Prev ",
Sum (decode (wait_time, 0, 0) "curr", count (*) "tot"
From v $ session_wait
Group by event order by 4;

2. rollback segment 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 tablespaces

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. I/O ratio of the monitored 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. Search for all 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. 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. 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;

9. 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_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 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 ');

11. 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 )');

12. 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;

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. This ratio is less than 1%, and close to 0% is better.

Select sum (gets) "dictionary gets", sum (getmisses) "dictionary cache get misses"
From v $ rowcache

14. Find the Oracle Character Set

Select * From SYS. Props $ where name = 'nls _ characterset ';

15. 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

16. Fragmentation degree

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 by segment_name
Having count (*) = (select max (count (*) from dba_segments group by segment_name );

17. 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;

18. Find a user session with multiple CPUs

12 is CPU used by this session

Select a. Sid, spid, status, substr (A. Program, 60/100) prog, A. Terminal, osuser, value/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 by value DESC;