Logo Background

Oracle SQL Tuning 101

  • By on October 8, 2012 | 3 Comments

    Oracle Performance SQL Tuning 101 – Learn how to optimize query performance to make your Oracle applications run faster.

    If you’re a DBA helping developers to tune their SQL, you can improve performance by suggesting more efficient queries or table and index-organization schemes.

    If you’re a production DBA, you’ll be dealing with user perceptions of a slow database, batch jobs taking longer and longer to complete and so on.

    Performance tuning focuses primarily on writing efficient SQL, allocating appropriate computing resources and analyzing wait events and contention in the system.

    Here’s the SQL queries that you may use to analyze the SQL explain plan, wait time, locking and others for performance tuning purposes.

    Oracle Tuning And Diagnostics Script

    --Active Session Info
    SELECT b.sid, b.serial#, a.spid, b.sql_id, b.program, b.osuser, b.machine,
    b.type, b.event, b.action, b.p1text, b.p2text, b.p3text, b.state, c.sql_text,b.logon_time
    FROM v$process a, v$session b, v$sqltext c
    WHERE a.addr=b.paddr
    AND b.sql_hash_value = c.hash_value
    AND b.STATUS = 'ACTIVE'
    ORDER BY a.spid, c.piece
     
    --Trace SQL Query Average Execution Time Using SQL ID
    SELECT sql_id, child_number, plan_hash_value plan_hash, executions execs,
    (elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions) avg_etime,
    buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio, sql_text
    FROM v$sql s
    WHERE s.sql_id='4n01r8z5hgfru'
     
    --Get The Detail Explain Plan Using SQL ID
    SELECT plan_table_output FROM TABLE(dbms_xplan.display_cursor('dtdqt19kfv6yx'))
     
    -- Get The History Of Explain Plan Execution Using SQL ID In AWR report
    SELECT plan_table_output FROM TABLE(dbms_xplan.display_awr('dtdqt19kfv6yx'))
     
    --Session Elapsed Processing Time 
    SELECT s.sid, s.username, s.module,
    round(t.value/1000000,2) "Elapsed Processing Time (Sec)"
    FROM v$sess_time_model t, v$session s
    WHERE t.sid = s.sid
    AND t.stat_name = 'DB time'
    AND s.username IS NOT NULL
    AND t.value/1000000 >= '1' --running more than 1 second
    ORDER BY round(t.value/1000000,2) DESC
     
    --Session Elapsed Processing Time Statistic By SID
    SELECT  a.sid, b.username, a.stat_name, ROUND((a.value/1000000),2) "Time (Sec)"
    FROM v$sess_time_model a, v$session b
    WHERE a.sid = b.sid
    AND b.sid = '194'  
    ORDER BY ROUND((a.value/1000000),2) DESC
     
    --Use Longops To Check The Estimation Query Runtime
    SELECT sid, serial#, opname, target, sofar, totalwork, units, start_time, 
    last_update_time, time_remaining "REMAIN SEC", round(time_remaining/60,2) "REMAIN MINS", 
    elapsed_seconds "ELAPSED SEC", round(elapsed_seconds/60,2) "ELAPSED MINS", 
    round((time_remaining+elapsed_seconds)/60,2)"TOTAL MINS", message TIME 
    FROM v$session_longops
    WHERE sofar<>totalwork
    AND time_remaining <> '0'
     
    --Detect Blocking Session
    SELECT sid, serial#, username, STATUS, state, event, 
    blocking_session, seconds_in_wait, wait_time, action, logon_time
    FROM gv$session 
    WHERE state IN ('WAITING') 
    AND wait_class != 'Idle'
    AND event LIKE '%enq%'
    AND TYPE='USER'
     
    --Active Table Locking
    SELECT b.sid, b.serial#, b.program, b.osuser, b.machine, b.TYPE, b.action,
    c.sql_text,b.logon_time, e.owner, e.object_name "Table Lock"
    FROM v$session b, v$sqltext c, v$locked_object d, dba_objects e
    WHERE b.sql_address = c.address
    AND b.sid = d.session_id
    AND d.object_id = e.object_id
    AND b.STATUS = 'ACTIVE'
    ORDER BY b.sid, c.piece
     
    --RAC Active Table Locking
    SELECT b.sid, b.serial#, a.spid, b.program, b.osuser, b.machine, 
    b.type, b.event, b.action, b.p1text, b.p2text, b.p3text, 
    b.state, c.sql_text,b.logon_time, 
    b.STATUS,  e.owner, e.object_name "Table Lock"
    FROM gv$process a, gv$session b, gv$sqltext c, gv$locked_object d, dba_objects e
    WHERE a.addr=b.paddr
    AND b.sql_address = c.address
    AND b.sid = d.session_id
    AND d.object_id = e.object_id
    AND b.STATUS = 'ACTIVE'
    ORDER BY a.spid, c.piece
     
    --Monitor Top Waiting Event Using Active Session History (ASH)
    SELECT h.event,
    SUM(h.wait_time + h.time_waited) "Total Wait Time (ms)"
    FROM v$active_session_history h, v$sqlarea SQL, dba_users u, v$event_name e 
    WHERE h.sample_time BETWEEN sysdate - 1/24 AND sysdate --event in the last hour
    AND h.sql_id = SQL.sql_id
    AND h.user_id = u.user_id
    AND h.event# = e.event#
    GROUP BY h.event
    ORDER BY SUM(h.wait_time + h.time_waited) DESC
     
    --Monitor Highest SQL Wait Time Using Active Session History (ASH)
    SELECT h.session_id, h.session_serial#, h.sql_id, h.session_state, 
    h.blocking_session_status, h.event, e.wait_class, h.module, u.username, SQL.sql_text,
    SUM(h.wait_time + h.time_waited) "Total Wait Time (ms)"
    FROM v$active_session_history h, v$sqlarea SQL, dba_users u, v$event_name e 
    WHERE h.sample_time BETWEEN sysdate - 1/24 AND sysdate --event in the last hour
    AND h.sql_id = SQL.sql_id
    AND h.user_id = u.user_id
    AND h.event# = e.event#
    GROUP BY h.session_id, h.session_serial#, h.sql_id, h.session_state, 
    h.blocking_session_status, h.event, e.wait_class, h.module, u.username, SQL.sql_text
    ORDER BY SUM(h.wait_time + h.time_waited) DESC
     
    --Monitor Highest Object Wait Time Using Active Session History (ASH)
    SELECT o.owner, o.object_name, o.object_type, h.session_id, h.session_serial#, 
    h.sql_id, h.module, SUM(h.wait_time + h.time_waited) "Total Wait Time (ms)"
    FROM v$active_session_history h, dba_objects o, v$event_name e 
    WHERE h.sample_time BETWEEN sysdate - 1/24 AND sysdate --event in the last hour
    AND h.current_obj# = o.object_id
    AND e.event_id = h.event_id
    GROUP BY  o.owner, o.object_name, o.object_type, h.session_id, h.session_serial#,
    h.sql_id, h.module
    ORDER BY SUM(h.wait_time + h.time_waited) DESC
     
    --Monitor Highest Event Wait Time Using Active Session History (ASH)
    SELECT h.event "Wait Event", SUM(h.wait_time + h.time_waited) "Total Wait Time (ms)"
    FROM v$active_session_history h, v$event_name e
    WHERE h.sample_time BETWEEN sysdate - 1/24 AND sysdate --event in the last hour
    AND h.event_id = e.event_id
    AND e.wait_class <> 'Idle'
    GROUP BY h.event
    ORDER BY SUM(h.wait_time + h.time_waited) DESC
     
    --Database Time Model Statistic
    SELECT wait_class, NAME, ROUND (time_secs, 2) "Time (Sec)",
    ROUND (time_secs * 100 / SUM (time_secs) OVER (), 2) pct
    FROM 
    (SELECT n.wait_class, e.event NAME, e.time_waited / 100 time_secs
    FROM v$system_event e, v$event_name n
    WHERE n.NAME = e.event 
    AND n.wait_class <> 'Idle'
    AND time_waited > 0
    UNION
    SELECT 
    'CPU', 
    'Server CPU', 
    SUM (VALUE / 1000000) time_secs
    FROM v$sys_time_model
    WHERE stat_name IN ('background cpu time', 'DB CPU'))
    ORDER BY time_secs DESC;
     
    --Monitor I/O On Data Files
    SELECT vfs.file#, dbf.file_name, dbf.tablespace_name, dbf.bytes, vfs.phyrds/vfs.phywrts,
    vfs.phyblkrd/vfs.phyblkwrt, vfs.readtim, vfs.writetim 
    FROM v$filestat vfs, dba_data_files dbf
    WHERE vfs.file# = dbf.file_id
     
    --I/O Stats For Data Files & Temp Files
    SELECT file_no,
    filetype_name, 
    small_sync_read_reqs "Synch Single Block Read Reqs",
    small_read_reqs "Single Block Read Requests",
    small_write_reqs "Single Block Write Requests",
    round(small_sync_read_latency/1000,2) "Single Block Read Latency (s)",
    large_read_reqs "Multiblock Read Requests",
    large_write_reqs "Multiblock Write Requests",
    async_io "Asynch I/O Availability"
    FROM v$iostat_file
    WHERE filetype_id IN (2,6) --data file and temp file
     
    --I/O Stats By Functionality
    SELECT function_name,
    small_read_reqs "Single Block Read Requests",
    small_write_reqs "Single Block Write Requests",
    large_read_reqs "Multiblock Read Requests",
    large_write_reqs "Multiblock Write Requests",
    number_of_wait "I/O Waits",
    round(wait_time/1000,2) "Total Wait Time (ms)"
    FROM v$iostat_function
    ORDER BY function_name
     
    --Temporary Tablespace Usage By SID
    SELECT tu.username, s.sid, s.serial#, s.sql_id, s.sql_address, tu.segtype, 
    tu.extents, tu.blocks, SQL.sql_text
    FROM v$tempseg_usage tu, v$session s, v$sql SQL
    WHERE tu.session_addr = s.addr
    AND tu.session_num = s.serial#
    AND s.sql_id = SQL.sql_id
    AND s.sql_address = SQL.address
     
    --Monitor Overall Oracle Tablespace 
    SELECT d.STATUS "Status",
    d.tablespace_name "Name",
    d.contents "Type",
    d.extent_management "Extent Management",
    d.initial_extent "Initial Extent",
    TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)",
    TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,'99,999,999.999') "Used (M)",
    TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "Used %",
    TO_CHAR(NVL(a.maxbytes / 1024 / 1024, 0),'99,999,990.900') "MaxSize (M)",
    TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.maxbytes * 100, 0), '990.00') "Used % of Max"
    FROM sys.dba_tablespaces d,
    (SELECT tablespace_name, 
    SUM(bytes) bytes, 
    SUM(decode(autoextensible,'NO',bytes,'YES',maxbytes))
    maxbytes FROM dba_data_files GROUP BY tablespace_name) a,
    (SELECT tablespace_name, SUM(bytes) bytes FROM dba_free_space 
    GROUP BY tablespace_name) f
    WHERE d.tablespace_name = a.tablespace_name(+)
    AND d.tablespace_name = f.tablespace_name(+)
    ORDER BY 10 DESC;
     
    --Cache Hit Ratio
    SELECT ROUND(((1-(SUM(DECODE(name,
    'physical reads', VALUE,0))/
    (SUM(DECODE(name, 'db block gets', VALUE,0))+
    (SUM(DECODE(name, 'consistent gets', VALUE, 0))))))*100),2)
    || '%' "Buffer Cache Hit Ratio"
    FROM v$sysstat --Use gv$sysstat if running on RAC environment
     
    --Library Cache Hit Ratio
    SELECT SUM(pins) "Total Pins", SUM(reloads) "Total Reloads",
    SUM(reloads)/SUM(pins) *100 libcache
    FROM v$librarycache --Use v$librarycache if running on RAC environment
     
    --DB Session Memory Usage
    SELECT se.sid,n.name, MAX(se.value) maxmem
    FROM v$sesstat se, v$statname n
    WHERE n.statistic# = se.statistic#
    AND n.name IN ('session pga memory','session pga memory max',
    'session uga memory','session uga memory max')
    GROUP BY n.name, se.sid
    ORDER BY MAX(se.value) DESC

    Useful Oracle Hint For Tuning – Gather_Plan_Statistics

    The hint /*+ gather_plan_statistics */ enables collection of rowsource execution statistics for the statement.

    The hint is useful if you want to have clear overview of the execution plan chosen by Oracle together with estimated rows or actual rows fetch in the SQL execution.

    SET serveroutput off;
    SET linesize 300;
     
    SELECT /*+  gather_plan_statistics */ SUM(nvl(period_net_dr,0) - nvl(period_net_cr,0)) 
    FROM gl_balances
    WHERE code_combination_id = '133110'
    GROUP BY code_combination_id;
     
    SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL, NULL, 'last allstats'));

    Oracle Wait Events FAQ

    Buffer Busy Waits

    Area: Buffer Cache, DBWR
    Possible Causes: The block is being read into the buffer by another session, so the waiting session must wait for the block read to complete.
    Examine: Examine V$SESSION while the problem is occurring to determine the type of block in contention. Tune SQL to access rows with fewer block reads by adding indexes, adjusting the database writer or adding freelists to tables and indexes. Even if there is a huge db_cache_size, the DBA may still see buffer busy waits and increasing the buffer size will not help in this case.

    Free Buffer Waits

    Area: Buffer cache, DBWR, I/O
    Possible Causes: Slow DBWR (possibly due to I/O?). Cache too small
    Examine: Examine write time using operating system statistics. Check buffer cache statistics for evidence of too small cache.

    DB File Scattered Read

    Area: I/O, SQL statement tuning
    Possible Causes: Poorly written SQL. Slow I/O system
    Examine: Investigate V$SQLAREA to see whether there are SQL statements performing many disk reads. Cross-check I/O system and V$FILESTAT for poor read time.

    DB File Sequential Read

    Area: I/O, SQL statement tuning
    Possible Causes: Poorly written SQL. Slow I/O system
    Examine: Investigate V$SQLAREA to see whether there are SQL statements performing many disk reads. Cross-check I/O system and V$FILESTAT for poor read time.

    Enqueue Waits (enq:)

    Area: Locks
    Possible Causes: Depends on type of enqueue
    Examine: Look at V$ENQUEUE_STAT

    Library Cache Latch Waits (librarycache, library cache pin and library cache lock)

    Area: Latch contention
    Possible Causes: SQL parsing or sharing
    Examine: Check V$SQLAREA to see whether there are SQL statements with a relatively high number of parse calls or a high number of child cursors (column VERSION_COUNT). Check parse statistics inV$SYSSTAT and their corresponding rate for each second.

    Log Buffer Space

    Area: Log buffer, I/O
    Possible Causes: Log buffer small. Slow I/O system
    Examine: Check the statistic redo buffer allocation retries in V$SYSSTAT. Check configuring log buffer section in configuring memory chapter. Check the disks that house the online redo logs for resource contention.

    Log File Sync

    Area: I/O, over-commit
    Possible Causes: Slow disks that store the online logs. Unbatched commits
    Examine: Check the disks that house the online redo logs for resource contention. Check the number of transactions (commits + rollbacks) each second, from V$SYSSTAT.

    Instance Tuning Using Performance Views – Oracle

    V$ACTIVE_SESSION_HISTORY – view displays active database session activity, sampled once every second.

    V$SESS_TIME_MODEL and V$SYS_TIME_MODEL – views contain time model statistics, including DB time which is the total time spent in database calls

    V$SESSION_WAIT – view displays the resources or events for which active sessions are waiting.

    V$SESSION – view contains the same wait statistics that are contained in the V$SESSION_WAIT view. If applicable, this view also contains detailed information on the object that the session is currently waiting for (object number, block number, file number, and row number), plus the blocking session responsible for the current wait.

    V$SESSION_EVENT – view provides summary of all the events the session has waited for since it started.

    V$SESSION_WAIT_CLASS – view provides the number of waits and the time spent in each class of wait events for each session.

    V$SESSION_WAIT_HISTORY – view provides the last ten wait events for each active session.

    V$SYSTEM_EVENT – view provides a summary of all the event waits on the instance since it started.

    V$EVENT_HISTOGRAM – view displays a histogram of the number of waits, the maximum wait, and total wait time on a per-child cursor basis.

    V$FILE_HISTOGRAM – view displays a histogram of times waited during single block reads for each file.

    V$SYSTEM_WAIT_CLASS – view provides the instance wide time totals for the number of waits and the time spent in each class of wait events. This view also shows the object number for which the session is waiting.

    V$TEMP_HISTOGRAM – view displays a histogram of times waited during single block reads for each temporary file.

    Tuning Oracle Shared Pool

    Sometimes your database might hit the error ORA-04031: unable to allocate 4048 bytes of shared memory (“shared pool”,”unknown object”,”sga heap(1,0)”,”library cache”).

    You can use the dbms_shared_pool package to pin large packages, reduce your use of shared memory or increase the amount of available shared memory by increasing the value of the INIT.ORA parameters “shared_pool_reserved_size” and “shared_pool_size”.

    Increasing the shared pool size might resolve the problem but it is not necessary will improve the performance of library caching.

    Always check the advisory with v$shared_pool_advice view to see the estimated time improvement statistic if you increase the shared pool area.

    --Free Memory Available In Shared Pool
    SELECT *
    FROM v$sgastat
    WHERE name = 'free memory'
    AND pool = 'shared pool'
     
    --Database Memory Setting
    SELECT component, current_size/1024/1024 "Current Size (MB)",
    min_size/1024/1024 "Min Size (MB)", max_size/1024/1024 "Max Size (MB)",
    user_specified_size/1024/1024 "User Specified Size (MB)", 
    last_oper_type "Type" 
    FROM v$sga_dynamic_components
     
    --Estimation Of Shared Memory Pool Size vs. Time Saved
    SELECT shared_pool_size_for_estimate "Pool Size (MB)",
    estd_lc_size "Lib Cache Size (MB)", 
    estd_lc_time_saved/1000000 "Lib Cache Time Saved (Sec)"
    FROM v$shared_pool_advice

    Tuning Oracle Buffer Cache

    v$db_cache_advice contains rows that predict the number of physical reads for the cache size corresponding to each row. This view is populated when the DB_CACHE_ADVICE initialization parameter is set to ON.

    When the advisory is enabled, there is a small increase in CPU usage as overhead associated with this advisory.

    The rows also compute a “physical read factor” which is the ratio of the number of estimated reads to the number of reads actually performed by the real buffer cache during the measurement interval.

    --Estimation Of Buffer Cache Size vs. Physical Reads
    SELECT size_for_estimate "Cache Size (MB)", 
    buffers_for_estimate "Buffers", 
    estd_physical_read_factor "Estd Phys|Read Factor", 
    estd_physical_reads "Estd Phys| Reads"
    FROM v$db_cache_advice
    WHERE name = 'DEFAULT'
    AND block_size = (SELECT VALUE FROM V$PARAMETER WHERE name = 'db_block_size')
    AND advice_status = 'ON'
     
                                    Estd Phys    Estd Phys
     Cache SIZE (MB)      Buffers READ Factor        Reads
    ---------------- ------------ ----------- ------------
                  30        3,802       18.70  192,317,943      10% OF CURRENT SIZE 
                  60        7,604       12.83  131,949,536
                  91       11,406        7.38   75,865,861
                 121       15,208        4.97   51,111,658
                 152       19,010        3.64   37,460,786
                 182       22,812        2.50   25,668,196
                 212       26,614        1.74   17,850,847
                 243       30,416        1.33   13,720,149
                 273       34,218        1.13   11,583,180
                 304       38,020        1.00   10,282,475      CURRENT SIZE 
                 334       41,822         .93    9,515,878
                 364       45,624         .87    8,909,026
                 395       49,426         .83    8,495,039
                 424       53,228         .79    8,116,496
                 456       57,030         .76    7,824,764
                 486       60,832         .74    7,563,180
                 517       64,634         .71    7,311,729
                 547       68,436         .69    7,104,280
                 577       72,238         .67    6,895,122
                 608       76,040         .66    6,739,731      200% OF CURRENT SIZE

    The following output shows that if the cache was 212 MB, rather than the current size of 304 MB, the estimated number of physical reads would increase by a factor of 1.74 or 74%. This means it would not be advisable to decrease the cache size to 212MB.

    However, increasing the cache size to 334MB would potentially decrease reads by a factor of .93 or 7%. If an additional 30MB memory is available on the host machine and the SGA_MAX_SIZE setting allows the increment, it would be advisable to increase the default buffer cache pool size to 334MB.

    Previous
    Next
    » SQL Performance Tuning With ASH And SQL Profile
  1. #1 oracle
    November 25, 2010 3:37 am

    v$session_longops displays the status of various operations that run for longer than 6 seconds (in absolute time). These operations currently include many backup and recovery functions, statistics gathering, and query execution, and more operations are added for every Oracle release.

    To monitor query execution progress, you must be using the cost-based optimizer and:
    – Set the TIMED_STATISTICS or SQL_TRACE parameters to true
    – Gather statistics for your objects with the ANALYZE statement or the DBMS_STATS package

    You may used dbms_application_info.set_session_longops if the SQL not appearing in v$session_longops. The set_session_longops procedure can be used to show the progress of long operations by inserting rows in the v$session_longops view.

    Post ReplyPost Reply
  1. #2 tuning@oracle
    November 29, 2010 11:22 pm

    It’s good to leverage Automatic Workload Repository (AWR), Automatic Database Diagnostic Monitor (ADDM) & SQL Tuning Advisor in OEM (Oracle Enterprise Manager) for the database performance monitoring.

    The AWR takes regular snapshots of the system activity including high-load SQL statements ranked by relevant statistics such as CPU consumption and wait time. A user can view the AWR and identify the high-load SQL of interest and run SQL Tuning Advisor on them.

    By default, ADDM runs proactively once every hour and analyzes key statistics gathered by the Automatic Workload Repository (AWR) over the last hour to identify any performance problems including high-load SQL statements.

    SQL Tuning Advisor in OEM is useful with the Automatic SQL Tuning feature. It has the capability of the query optimizer that automates the entire SQL tuning process. Using the newly enhanced query optimizer to tune SQL statements, the automatic process replaces manual SQL tuning which is a complex, repetitive, and time-consuming function.

    Post ReplyPost Reply
  1. #3 Jing Hong
    January 15, 2013 2:59 am

    select sql_id, plan_hash_value,
    sum(execs) execs,
    — sum(etime) etime,
    sum(etime)/sum(execs) avg_etime,
    sum(cpu_time)/sum(execs) avg_cpu_time,
    sum(lio)/sum(execs) avg_lio,
    sum(pio)/sum(execs) avg_pio
    from (
    select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
    nvl(executions_delta,0) execs,
    elapsed_time_delta/1000000 etime,
    (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
    buffer_gets_delta lio,
    disk_reads_delta pio,
    cpu_time_delta/1000000 cpu_time,
    (buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio,
    (cpu_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta)) avg_cpu_time
    from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
    where sql_id = ‘057fsakvnn2bc’
    and ss.snap_id = S.snap_id
    and ss.instance_number = S.instance_number
    — and executions_delta > 0
    )
    group by sql_id, plan_hash_value
    order by 5

    Post ReplyPost Reply
Leave a Comment