Difference between revisions of "Tuning"
From dbawiki
(→Wait-based tuning query) |
(→Wait-based tuning query) |
||
| Line 9: | Line 9: | ||
column c5 heading 'Average|Wait|(in secs)' format 99,999.999 | column c5 heading 'Average|Wait|(in secs)' format 99,999.999 | ||
ttitle 'System-wide Wait Analysis|for current wait events' | ttitle 'System-wide Wait Analysis|for current wait events' | ||
| − | select | + | select event c1 |
| − | + | , total_waits c2 | |
| − | + | , time_waited / 100 c3 | |
| − | + | , total_timeouts c4 | |
| − | + | , average_wait /100 c5 | |
| − | + | from sys.v_$system_event | |
| − | from | + | where event not in ( |
| − | + | 'dispatcher timer' | |
| − | where | + | , 'lock element cleanup' |
| − | + | , 'Null event' | |
| − | 'dispatcher timer', | + | , 'parallel query dequeue wait' |
| − | + | , 'parallel query idle wait - Slaves' | |
| − | + | , 'pipe get' | |
| − | + | , 'PL/SQL lock timer' | |
| − | + | , 'pmon timer' | |
| − | + | , 'rdbms ipc message' | |
| − | + | , 'slave wait' | |
| − | + | , 'smon timer' | |
| − | + | , 'SQL*Net break/reset to client' | |
| − | + | , 'SQL*Net message from client' | |
| − | + | , 'SQL*Net message to client' | |
| − | + | , 'SQL*Net more data to client' | |
| − | + | , 'virtual circuit status' | |
| − | + | , 'WMON goes to sleep' | |
| − | + | ) | |
| − | + | and event not like 'DFS%' | |
| − | + | and event not like '%done%' | |
| − | + | and event not like '%Idle%' | |
| − | + | and event not like 'KXFX%' | |
| − | + | order by c2 desc | |
| − | and | + | / |
| − | event not like '%done%' | ||
| − | and | ||
| − | event not like '%Idle%' | ||
| − | |||
| − | |||
| − | order by | ||
| − | |||
</pre> | </pre> | ||
| + | ===Identifying I/O Problems Using V$ Views=== | ||
| + | The V$IOSTAT_FILE view captures I/O statistics of database files that are or have been accessed.<br /> | ||
| + | The SMALL_SYNC_READ_LATENCY column displays the latency for single block synchronous reads (in milliseconds), which translates directly to the amount of time that clients need to wait before moving onto the next operation.<br /> | ||
| + | This defines the responsiveness of the storage subsystem based on the current load.<br /> | ||
| + | If there is a high latency for critical datafiles, you may want to consider relocating these files to improve their service time.<br /> | ||
| + | To calculate latency statistics, timed_statistics must be set to TRUE. | ||
| + | <pre> | ||
| + | select file_no | ||
| + | , filetype_id | ||
| + | , filetype_name | ||
| + | , small_read_megabytes | ||
| + | , small_write_megabytes | ||
| + | , large_read_megabytes | ||
| + | , large_write_megabytes | ||
| + | , small_sync_read_latency/1000 small_sync_read_latency_in_secs | ||
| + | from v$iostat_file | ||
| + | order by small_sync_read_latency | ||
| + | / | ||
| + | </pre> | ||
| + | |||
| + | ====References==== | ||
| + | [http://www.dba-oracle.com/tips_oracle_v$system_event.htm dba-oracle] | ||
| + | [http://docs.oracle.com/cd/B28359_01/server.111/b28274/instance_tune.htm#CACGBHBD Oracle doc] | ||
Revision as of 15:51, 7 January 2014
Wait-based tuning query
set pages 999
set lines 190
column c1 heading 'Event|Name' format a40
column c2 heading 'Total|Waits' format 999,999,999,999
column c3 heading 'Seconds|Waiting' format 999,999,999,999
column c4 heading 'Total|Timeouts' format 999,999,999,999
column c5 heading 'Average|Wait|(in secs)' format 99,999.999
ttitle 'System-wide Wait Analysis|for current wait events'
select event c1
, total_waits c2
, time_waited / 100 c3
, total_timeouts c4
, average_wait /100 c5
from sys.v_$system_event
where event not in (
'dispatcher timer'
, 'lock element cleanup'
, 'Null event'
, 'parallel query dequeue wait'
, 'parallel query idle wait - Slaves'
, 'pipe get'
, 'PL/SQL lock timer'
, 'pmon timer'
, 'rdbms ipc message'
, 'slave wait'
, 'smon timer'
, 'SQL*Net break/reset to client'
, 'SQL*Net message from client'
, 'SQL*Net message to client'
, 'SQL*Net more data to client'
, 'virtual circuit status'
, 'WMON goes to sleep'
)
and event not like 'DFS%'
and event not like '%done%'
and event not like '%Idle%'
and event not like 'KXFX%'
order by c2 desc
/
Identifying I/O Problems Using V$ Views
The V$IOSTAT_FILE view captures I/O statistics of database files that are or have been accessed.
The SMALL_SYNC_READ_LATENCY column displays the latency for single block synchronous reads (in milliseconds), which translates directly to the amount of time that clients need to wait before moving onto the next operation.
This defines the responsiveness of the storage subsystem based on the current load.
If there is a high latency for critical datafiles, you may want to consider relocating these files to improve their service time.
To calculate latency statistics, timed_statistics must be set to TRUE.
select file_no , filetype_id , filetype_name , small_read_megabytes , small_write_megabytes , large_read_megabytes , large_write_megabytes , small_sync_read_latency/1000 small_sync_read_latency_in_secs from v$iostat_file order by small_sync_read_latency /
