Differences

This shows you the differences between two versions of the page.

--- tuning [2018/12/06 21:05] – created 91.177.234.129
+++ tuning [2020/04/04 17:21] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
-====== Tuning ======
+  *  [[http://www.slideshare.net/khailey/ooug-oracle-performance-tuning-with-aas|Oracle Performance Tuning using AWR/ASH - Kyle Hailey]]
+  *  [[http://www.slideshare.net/khailey/oracle-10g-performance-chapter-02-aas|Average Active Sessions, the golden metric - Kyle Hailey]]
+  *  [[http://www.slideshare.net/vbarun01/sql-performance-tuning-with-ash-amp-awr-real-world-use-cases|Sql Performance Tuning with ASH & AWR - Oracle]]
+  *  [[http://guyharrison.typepad.com/SystematicOracletuning.pdf|http://guyharrison.typepad.com/SystematicOracletuning.pdf]]
+  *  [[http://www.slideshare.net/sagai/wait-events-10g?related=1|http://www.slideshare.net/sagai/wait-events-10g?related=1]]
+  *  [[http://www.dba-oracle.com/t_how_to_read_an_awr_report.htm|http://www.dba-oracle.com/t_how_to_read_an_awr_report.htm]]
+  *  [[https://www.youtube.com/watch?v=knPdTWil664|Beginning Performance Tuning with Arup Nanda]]. [[http://proligence.com/pres/rmoug13/|Downloads from Presentation]]
+  *  [[https://www.youtube.com/watch?v=xn5FXlB94v8|Effective PL_SQL with Tom Kyte]]
+  *  [[http://logicalread.solarwinds.com/oracle-db-file-sequential-read-wait-event-part1-mc01/#.Vj8zy5RNS9j|Understanding the Oracle db file sequential read Wait Event]]
+  *  [[https://logicalread.com/oracle-log-file-sync-wait-event-dr01/|Oracle Log File Sync Wait Event - logicalread.com]]
+  *  [[http://docs.oracle.com/cd/E68491_01/OEXUG/quick-start-guide.htm#OEXUG-GUID-0724119D-BBAE-437A-810C-528395E4BADC|ORAchk and EXAchk]]
-  * [[http://www.slideshare.net/khailey/ooug-oracle-performance-tuning-with-aas|Oracle Performance Tuning using AWR/ASH - Kyle Hailey]]
+  *  [[http://www.dba-oracle.com/t_dropping_deleting_column_histograms.htm|Dropping and deleting Oracle histograms]]
-  * [[http://www.slideshare.net/khailey/oracle-10g-performance-chapter-02-aas|Average Active Sessions, the golden metric - Kyle Hailey]]
+  *  [[https://blogs.oracle.com/optimizer/how-do-i-drop-an-existing-histogram-on-a-column-and-stop-the-auto-stats-gathering-job-from-creating-it-in-the-future|How do I drop an existing histogram on a column and stop the Auto Stats gathering job from creating it in the future?]]
-  * [[http://www.slideshare.net/vbarun01/sql-performance-tuning-with-ash-amp-awr-real-world-use-cases|Sql Performance Tuning with ASH & AWR - Oracle]]
-  * [[http://guyharrison.typepad.com/SystematicOracletuning.pdf|http://guyharrison.typepad.com/SystematicOracletuning.pdf]]
-  * [[http://www.slideshare.net/sagai/wait-events-10g?related=1|http://www.slideshare.net/sagai/wait-events-10g?related=1]]
-  * [[http://www.dba-oracle.com/t_how_to_read_an_awr_report.htm|http://www.dba-oracle.com/t_how_to_read_an_awr_report.htm]]
-  * [[https://www.youtube.com/watch?v=knPdTWil664|Beginning Performance Tuning with Arup Nanda]]  (Downloads from Presentation: http://proligence.com/pres/rmoug13/)
-  * [[https://www.youtube.com/watch?v=xn5FXlB94v8|Effective PL_SQL with Tom Kyte]]
-  * [[http://logicalread.solarwinds.com/oracle-db-file-sequential-read-wait-event-part1-mc01/#.Vj8zy5RNS9j|Understanding the Oracle db file sequential read Wait Event]]
-  * [[https://logicalread.com/oracle-log-file-sync-wait-event-dr01/|Oracle Log File Sync Wait Event - logicalread.com]]
-  * [[http://docs.oracle.com/cd/E68491_01/OEXUG/quick-start-guide.htm#OEXUG-GUID-0724119D-BBAE-437A-810C-528395E4BADC|ORAchk and EXAchk]]
-=====Wait-based tuning query=====
-<code>0@@</code>
-=====Identifying I/O Problems Using V$ Views=====
+==== Wait-based tuning query ====
-The V$IOSTAT_FILE view captures I/O statistics of database files that are or have been accessed.<br />
+<code>
-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 />
+set pages 999
-This defines the responsiveness of the storage subsystem based on the current load.<br />
+set lines 190
-If there is a high latency for critical datafiles, you may want to consider relocating these files to improve their service time.<br />
+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
+/
+</code>
+==== 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.
-<code>1@@</code>
+<code>
+select file_no
+,      filetype_id
+,      filetype_name
+,      small_read_megabytes
+,      small_write_megabytes
+,      large_read_megabytes
+,      large_write_megabytes
+,      small_sync_read_latency
+from   v$iostat_file
+order  by small_sync_read_latency desc
+/
+</code>
-=====Show sql statements running at specific times (between 2 snapshot id's)=====
+==== Show sql statements running at specific times (between 2 snapshot id's) ====
 List is taken from ASH/AWR tables
-<code>2@@</code>
+<code>
+col ssnap new_value ssnap_var
+col esnap new_value esnap_var
+set lines 100 pages 999
+select snap_id,
+  to_char(begin_interval_time, 'dd/mm/yy hh24:mi:ss') begin
+from
+   dba_hist_snapshot
+order by 1
+/
+col SAMPLE_TIME for a18
+col SQL_EXEC_START for a20
+col SESSION_ID for 9999
+col SQL_OPNAME for a15
+col PROGRAM for a10
+col SESSION_SERIAL# for 99999
+col USERNAME for a20
+set long 999999999
+set lines 500
+set pages 50000
+select '&starting_snap_id' ssnap, '&ending_snap_id' esnap  from dual;
+DEFINE LOGFILE=Sessions_dmls_detail_&&ssnap_var._to_&&esnap_var..log
+spool &&LOGFILE
+select a.SAMPLE_TIME,a.SESSION_ID,a.SESSION_SERIAL#,c.USERNAME ,a.SQL_OPNAME, a.SQL_EXEC_START, a.program, b.SQL_TEXT
+from DBA_HIST_ACTIVE_SESS_HISTORY a, dba_hist_sqltext b, dba_users c
+where a.SQL_ID = b.SQL_ID and a.user_id=c.user_id and a.snap_id between &&ssnap_var and &&esnap_var and b.command_type in (2,6,7) and lower(sql_text) like 'insert into query_result%'
+order by a.SQL_EXEC_START asc;
+spool off;
+exit
+/
+</code>
+=== 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]]
-====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]]