Oracle performance monitoring script

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 it is better to close to 0%.
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) space, 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;
 
1. Detect database events and waits
SELECT event, total_waits, total_timeouts, time_waited, average_wait
FROM v $ system_event
2. query session events and wait time
Select sid, event, total_waits, average_wait
From v $ session_event where sid = 10;
3. query the waiting process
SELECT sid, seq #, event, wait_time, state
FROM v $ session_wait;
4. monitor the performance of the global Zone
Select * from v $ sgastat;
5. query hit rate
Select gethitratio
From v $ librarycache
Where namespace = 'SQL region ';
6. Current SQL statement
Select SQL _text, users_executing,
Executions, loads
From v $ sqlarea;
7. query the hit rate in the cache.
Select sum (pins) "Executions", sum (reloads) "Cache Misses ",
Sum (reloads)/sum (pins)
From v $ librarycache;
8. query the effective loading times in the global dictionary.
Select namespace, pins, reloads, invalidations
From v $ librarycache;
9. rollback segment contention

Select name, waits, gets, waits/gets "Ratio"
From v $ rollstat a, v $ rollname B
Where a. usn = B. usn;
10. 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;
11. 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 #;
12. 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;
13. 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;
14. 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;
15. 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;
16. 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 ');
17. Monitor the sorting ratio of memory to hard disk, preferably make it smaller than. 10, increase sort_area_size
SELECT name, value FROM v $ sysstat WHERE name IN ('sorts (memory) ', 'sorts (disk )');
18. 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 close to 0%.
Select sum (gets) "dictionary gets", sum (getmisses) "dictionary cache get misses"
From v $ rowcache
19. Find the ORACLE Character Set
Select * from sys. props $ where name = 'nls _ CHARACTERSET ';
20. 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
21. 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 );
22. 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;
23. Find a user session with many 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;

 

Author: "anyonefeng"