TECH SOLUTIONS

Sunday, October 12, 2014

Steps for Oracle Performance Tuning

Question: I am new to Oracle tuning, and I want to know the steps for Oracle performance tuning.

Answer: As of Oracle 11g, Oracle has codified a top-down tuning approach, with the first steps being very broad, and successive steps becoming more focused. Oracle performance tuning is very complex, and where there are some generic steps for performance tuning, the world is not always that simple. Here are the steps for a top-down Oracle performance tuning approach:

Oracle tuning involves the following steps, with each step getting more specific and targeted:

Server & network tuning—This is always the first step, as not amount of tuning will help a poor server environment.
Instance tuning—Tuning the Oracle SGA is the next step, and all of the Oracle initialization parameters must be reviewed to ensure that the database has been properly configured for it's workload. In some cases, a database may have a bi-modal workload (online vs. batch) and the instance parms are adjusted as-needed during this step.
Object tuning—This step of performance tuning looks at the setting for Oracle tables and indexes. Table and index settings such as PCTFREE, PCTUSED, and FREELISTS can have a dramatic impact on Oracle performance.
SQL tuning—This is last step in tuning, and the most time-consuming tuning operation because there can be many thousands of individual SQL statements that access the Oracle database. If you have carefully optimized the workload as a whole from step 2, there you will only need to tune "outlier" SQL statements. Within this step, there are sub-steps:
Remove unnecessary large-table full-table scans—In this tuning step you evaluate the SQL based on the number of rows returned by the query. Standard b-tree indexes can be added to tables, and bitmapped and function-based indexes can also eliminate full-table scans.

Cache small-table full-table scans—In this step we ensure that a dedicated data buffer is available for the rows.

Verify optimal index usage—This step is critical because you may have "missing" indexes in your database, causing excessive I/O.

Materialize your aggregations and summaries for static tables - One features of the Oracle SQLAccess advisor is recommendations for new indexes and suggestions for materialized views.

Non-idle wait times sorted by time waited

rem *********************************************************** 
rem
rem File: topWaits.sql 
rem Description: Non-idle wait times sorted by time waited
rem   
rem From 'Oracle Performance Survival Guide' by Guy Harrison
rem  Chapter 3 Page 70
rem  ISBN: 978-0137011957
rem  See www.guyharrison.net for further information
rem  
rem  This work is in the public domain NSA 
rem   
rem
rem ********************************************************* 


/* Formatted on 2008/08/15 13:37 (Formatter Plus v4.8.7) */
SET lines 100
SET pages 10000
COLUMN wait_class format a12
COLUMN event format a30
COLUMN total_waits format 999999
COLUMN total_us format 999999999
COLUMN pct_time format 99.99
COLUMN avg_us format 999999.99
SET echo on

SELECT   wait_class, event, total_waits AS waits,
         ROUND (time_waited_micro / 1000) AS total_ms,
         ROUND (time_waited_micro * 100 / SUM (time_waited_micro) OVER (),
                2
               ) AS pct_time,
         ROUND ((time_waited_micro / total_waits) / 1000, 2) AS avg_ms
    FROM v$system_event
   WHERE wait_class <> 'Idle'
ORDER BY time_waited_micro DESC;

Time model unioned with wait data to show waits combined with CPU timings

rem *********************************************************** 
rem
rem File: timeModelSimple.sql 
rem Description: Time model unioned with wait data to show waits combined with CPU timings 
rem   
rem From 'Oracle Performance Survival Guide' by Guy Harrison
rem  Chapter 3 Page 73
rem  ISBN: 978-0137011957
rem  See www.guyharrison.net for further information
rem  
rem  This work is in the public domain NSA 
rem   
rem
rem ********************************************************* 


set lines 100
set pages 10000
set echo on 
column total_waits format  999999999


SELECT   event, total_waits,
         ROUND (time_waited_micro / 1000000) AS time_waited_secs,
         ROUND (time_waited_micro * 100 / 
            SUM (time_waited_micro) OVER (),2) AS pct_time
    FROM (SELECT event, total_waits, time_waited_micro
            FROM v$system_event
           WHERE wait_class <> 'Idle'
          UNION
          SELECT stat_name, NULL, VALUE
            FROM v$sys_time_model
           WHERE stat_name IN ('DB CPU', 'background cpu time'))
ORDER BY 3 DESC;

Show the full name and path of the trace file for the current session

rem *********************************************************** 
rem
rem File: CurrentSessionTraceStatus.sql 
rem Description: Show the full name and path of the trace file for the current session 
rem   
rem From 'Oracle Performance Survival Guide' by Guy Harrison
rem  Chapter 3 Page 55
rem  ISBN: 978-0137011957
rem  See www.guyharrison.net for further information
rem  
rem  This work is in the public domain NSA 
rem   
rem
rem ********************************************************* 


SELECT s.sql_trace, s.sql_trace_waits, s.sql_trace_binds,
          traceid, tracefile
     FROM v$session s JOIN v$process p ON (p.addr = s.paddr)
    WHERE audsid = USERENV ('SESSIONID')

Using the AWR History Tables to compare performance - Part 2

In the Part 1 of this post, we saw how we can use the AWR history tables to compare the top wait events between two different time periods.

We will now use the history tables to track and identify problem SQL statements.

Suppose we have been informed that there was a I/O performance issue early in the morning around 4.30 AM …

1) We obtain the SNAP_ID for the period in question

SQL> select snap_id,begin_interval_time,end_interval_time
2 from dba_hist_snapshot where to_char(begin_interval_time,’DD-MON-YYYY HH24:MI’)=’28-JUL-2009 04:00′;

   SNAP_ID BEGIN_INTERVAL_TIME
---------- ---------------------------------------------------------------------------
END_INTERVAL_TIME
---------------------------------------------------------------------------
     10951 28-JUL-09 04.00.08.054 AM
28-JUL-09 05.00.58.732 AM

2) We then obtain the Top 5 SQL statements executing during that period – we can sort on a number of variables like disk reads, buffer gets, I/O waits, Cpu time etc. We will sort it by disk reads to find the top 5 SQL statements with the most disk reads.

SQL> select * from
2 (
select
3 4 sql.sql_id c1,
5 sql.buffer_gets_delta c2,
6 sql.disk_reads_delta c3,
7 sql.iowait_delta c4
8 from
9 dba_hist_sqlstat sql,
10 dba_hist_snapshot s
11 where
12 s.snap_id = sql.snap_id
13 and
14 s.snap_id=10951
15 order by
16 c3 desc)
17 where rownum < 6
18 ;

C1                    C2         C3         C4
------------- ---------- ---------- ----------
gyvak7ftvukcy    6131099     216469  613703400
7vk0vmj5hubqt   16027977     157384  484194526
0ay748ut6y71y     293922     142957  852668499
0utjstq2kntvx   13269295     100043  299328212
ahx483x819uu1   23584192      81361  797258856

3) Now that we have the SQL ID, we will obtain the SQL Query for that SQL ID

SQL> select sql_text from dba_hist_sqltext
2 where sql_id=’gyvak7ftvukcy’;

SQL_TEXT
——————————————————————————–
select /*NORULE */ ‘DATAPOINT extents_left ‘ || ‘ ‘ ||
nvl(min(a.MAXEXTS – a.EXTENTS), 111) || CHR(10) ||
‘DATAPOINT extents_left_pct’ || ‘ ‘ ||
round(nvl(min(round(a.MAXEXTS – a.EXTENTS) * 100 / a.MAXEXTS), 100), 0) bpb
from (select ds.header_file file#,ds.header_block block#,
ds.extents,ds.max_extents maxexts,st.ts#,su.user#
from dba_segments ds,sys.ts$ st,sys.user$ su where
st.name=ds.tablespace_name and
su.name=ds.owner
and segment_type not in (‘SPACE HEADER’,’CACHE’ ) ) a,
……………..
…………………

4) We can then examine a single SQL statement and see if there are any performance trends. It shows that during the night between the hours of 1.00 AM and 3.00 AM, the I/O waits are the highest for this particular query and now we can then isolate the problem further by investigating into what other concurrent activities are happening during the same time interval.

1 select
2 s.snap_id,
3 to_char(s.begin_interval_time,’HH24:MI’) c1,
4 sql.executions_delta c2,
5 sql.buffer_gets_delta c3,
6 sql.disk_reads_delta c4,
7 sql.iowait_delta c5,
8 sql.apwait_delta c6,
9 sql.ccwait_delta c7,
10 sql.cpu_time_delta c8,
11 sql.elapsed_time_delta c9
12 from
13 dba_hist_sqlstat sql,
14 dba_hist_snapshot s
15 where
16 s.snap_id = sql.snap_id
17 and to_char(s.begin_interval_time,’DD-MON-YYYY’)=’28-JUL-2009′
18 and
19 sql.sql_id=’gyvak7ftvukcy’
20* order by 1
SQL> /

   SNAP_ID C1            C2         C3         C4         C5         C6         C7         C8         C9
---------- ----- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
     10947 00:00          2     294862     100954  580304536          0       7601   21400306  597529767
     10948 01:00          1     148163      50573 1284907667          0         39   10410248 1295096968
     10949 02:00          1     146961      71551 1313801418          0        177   11410211 1324273555
     10950 03:00          1     146961      71485 1249378673          0         74   11980186 1259394083
     10951 04:00          2     293922     142957  852668499          0          0   19720409  872476253
     10952 05:00          2     259310     115831  576725343          0         52   14970399  590388211
     10953 06:00          1     181574      77533  482981819          0          0   16200248  495323735
     10954 07:00          2     293922     142096  608989402          0         30   20550363  625696011
     10955 08:00          2     237469      94186  522814616          0          0   10070410  533824217
     10956 09:00          1     203949     127931 1022499631          0          0   15140178 1039516623
     10957 10:00          2     159078      87453  570435749          0          0    6830445  580726055
     10958 11:00          1     282066     134766 1071336554          0          0   15960187 1089728917
     10959 12:00          2     293922     142264  494751879          0         66   20480362  511719534
     10960 13:00          1     148228      69950  293789050          0          0    9280086  302702194
     10961 14:00          2     293922     141880  719430817          0         40   18620390  737121746

15 rows selected.

Using the AWR History Tables to compare performance

Using the DBA_HIST AWR history tables, we can compare the top wait events between different days or different time periods.

Let us assume that we find that batch jobs which are executed at night particularly between 1 and 2 AM are experiencing performance issues.

In this case we are comparing performance of a particular database on the 20th and 21st of July for the time periods 01:00 to 02:00.

SQL>
1 select snap_id,to_char(BEGIN_INTERVAL_TIME,’DD-MON-YY HH24:MI:SS’) “Runtime”
2 from dba_hist_snapshot
3* where trunc(BEGIN_INTERVAL_TIME)=’21-JUL-2009′ order by snap_id;

SNAP_ID Runtime
———- ——————
10781 21-JUL-09 00:00:14
10782 21-JUL-09 01:01:01
10783 21-JUL-09 02:00:27
10784 21-JUL-09 03:00:42
……..

Since the snapshots are collected every hour, for the same time period on the previous day we substract 24 – so the snap_ids for the 20th of July are 10758 and 10759.

select * from
(select event, waits “Waits”, time “Wait Time (s)”, pct*100 “Percent of Total”, waitclass “Wait Class”
from (select e.event_name event, e.total_waits – nvl(b.total_waits,0) waits,
(e.time_waited_micro – nvl(b.time_waited_micro,0))/1000000 time
, (e.time_waited_micro – nvl(b.time_waited_micro,0))/(select sum(e1.time_waited_micro – nvl(b1.time_waited_micro,0))
from dba_hist_system_event b1 , dba_hist_system_event e1
where b1.snap_id(+) = b.snap_id and e1.snap_id = e.snap_id and b1.dbid(+) = b.dbid
and e1.dbid = e.dbid and b1.instance_number(+) = b.instance_number
and e1.instance_number = e.instance_number
and b1.event_id(+) = e1.event_id
and e1.total_waits > nvl(b1.total_waits,0)
and e1.wait_class <> ‘Idle’
) pct
, e.wait_class waitclass
from
dba_hist_system_event b ,
dba_hist_system_event e
where b.snap_id(+) = &pBgnSnap
and e.snap_id = &pEndSnap
and b.event_id(+) = e.event_id
and e.total_waits > nvl(b.total_waits,0)
and e.wait_class <> ‘Idle’
order by waits desc
)
where rownum < 11)
;

Note: we are filtering the rows to display only the Top 10 Wait Events

SnapID’s 10758 and 10759 (20th July)

EVENT                                         Waits Wait Time (s) Percent of Total Wait Class
---------------------------------------- ---------- ------------- ---------------- --------------------
control file sequential read                4803994     79.589894        2.4698686 System I/O
PX qref latch                               1451898      3.587572       .111331112 Other
db file sequential read                       57463    1914.86419       59.4229078 User I/O
SQL*Net message to client                     52040       .077309       .002399087 Network
db file scattered read                         9169    223.497921        6.9356858 User I/O
log file parallel write                        6692    371.933451       11.5420025 System I/O
SQL*Net break/reset to client                  6610       3.75458       .116513778 Application
log file sync                                  3175    211.309884       6.55746127 Commit
direct path read                               2077      1.349465       .041877191 User I/O
control file parallel write                    1857    112.049356        3.4771649 System I/O

10 rows selected.

SnapId’s 10782 and 10783 (21st July)

EVENT                                         Waits Wait Time (s) Percent of Total Wait Class
---------------------------------------- ---------- ------------- ---------------- --------------------
control file sequential read                6006299    101.375645       .512584025 System I/O
SQL*Net message to client                   1045461       1.16951       .005913374 Network
db file sequential read                      312287    7147.10849       36.1378085 User I/O
log file sync                                 86597    8044.46297       40.6750873 Commit
log file parallel write                       62614    3257.61227       16.4714119 System I/O
SQL*Net more data from client                 25708      6.923053        .03500492 Network
SQL*Net break/reset to client                 24824    361.693308       1.82882399 Application
db file scattered read                        18289    178.619015       .903148421 User I/O
SQL*Net more data to client                   14721       .531947       .002689675 Network
db file parallel write                         3128    310.442873       1.56968725 System I/O

10 rows selected.

Looking at this output for the same time period on two days, we find that on the 21st of July the top wait events seem to be all I/O related and if we see the wait event “log file sync” on the second day is significantly higher than the first day. The wait event “db file sequential read” is also significantly higher on the second day as well as compared to the first day.

We can use this information to quickly triage the problem and make the following checks:

OPSG PKG Installation

Many scripts can be run without any installation providing that the user has access to V$ views. However, a few require specialized views and that the OPSG_PKG be installed. To install the package:

1. Open a command prompt within the "install" directory
2. execute either install_opsg.bat (Windows) or install_opsg.sh (Linux/Unix)
3. Respond to the prompts

Here is an example session:

C:\tmp\opsg\install>install_opsg

C:\tmp\opsg\install>sqlplus /nolog @install_opsg

SQL*Plus: Release 11.1.0.7.0 - Production on Thu Sep 10 10:55:02 2009

Copyright (c) 1982, 2008, Oracle. All rights reserved.

Enter password for the (new) OPSG user:opsg
Enter SYS password:
Enter TNSNAMES entry:g11r2ga

The script creates a user OPSG which has privileges to run all of the scripts and which has appropriate permissions. You can modify the script if you want to install a different user. You do need the SYS password to install the user.