Performance Presentation

Objective

To provide some techniques and tools to help evaluate and understand performance issues on the systems you support To provide information of activities the performance eam was involved

Caveat

'Performance' is a large area, the topics here focus mostly on troubleshooting the performance of SQL running on the database. Brief introduction of looking into AWR Reports Briefing of other activities performed by the Perf Team.

Performance Team Charter

Ensuring consistent response time and throughput for Oracle's business critical activities across all global production environments. Proactive monitoring and management of workload in Oracle's global production environments. Performance Load Testing .....(many more)

Typical Problems

Concurrent Requests

Used to run fast, now run consistently long Run long during certain periods Used to run fast, now run consistently long Run long during certain periods

User Activities

Assistance requested to troubleshoot slow running new functionality Assistance requested to help improve current run times

Typical Causes

A specific SQL is identified that is taking too much time

Scenario1: The plan used by the optimizer for this SQL has changed Reason1: The data volumes in the underlining tables has changed Reason2: Inaccurate statistics resulting in bad plan by optimizer Scenario2: The statement is executing more frequently than previously

Other concurrent activity on the system impacting this activity

A Problem Is Reported

User Generated SR Most Typical

Concurrent Requests running longer than normal OLTP activity taking longer than normal or acceptable Request to check if something is running 'normally' (this usually means 'Is it blocked?' or 'Is there something else on the system impacting the performance?') CPU load too high Average Active Sessions on the Database higher than usual

Automatically generated SRs

Identify the Problem

Get as much information about the issue as possible

Is the problem consistent and reproducible? If NOT reproducible, does it depend on time? and what are those times? If reproducible, get a test case you can manually reproduce, if possible When was the last time it ran properly?

Get end-to-end trace, if possible Concurrent Requests - Get request IDs of good and bad runs Observe live on the database if possible (Top Activity, Session Monitoring, SQLMonitor)

Tracing

The benefits of extended tracing:

Mastering Performance with Extended SQL Trace:

http://carymillsap.blogspot.com/2011/01/new-papermastering-performance-with.html

Performance Team Tracing Guidelines:

Very good detailed steps for Ebiz, OBIEE, and session based end to end tracing:

http://files.oraclecorp.com/content/AllPublic/SharedFolders/AI T%20Systems-Public/Public%20Procedures %20%26%20Standards/Tracing%20Guidelines/PERF %20Tracing%20Guidelines.html

Poorly Instrumented Application?

Some of the applications we support are not yet properly instrumented and are difficult to obtain end-to-end trace files on Demantra is one such application which currently lacks any method for users to initiate tracing - PRE_LOGON SHOULD CALL DBMS_APPLICATION_INFO.SET_MODULE If you come across this, log a Bug on the product

See Bug:11689205

Identifying performance problems from a user's perspective becomes exponentially simpler when an endto-end trace of the users activities are available

Initiating Manual Partial Trace

End-to-End Tracing is difficult when: The application does not provide a simple method for a user to initiate a trace The application is not properly instrumented (no client, module, or action information seen in v$session) It can still be beneficial to obtain a partial trace

If the performance issue is bad enough (minutes, not seconds), then it is possible to get a partial trace once the user's session is identified It takes some time to find the SID and enable the trace, so vital information at the start may be missing

10

Identify the Session

Sometimes the session can be easily identified visually as a top consumer in the OEM Top Activity Page

11

Manual Trace in OEM Session Details

Click the SID in the Top Activity or Search Sessions to get Session Details Page Enable SQL Trace Button

12

Manual Trace in OEM

Typical 10046 trace with option for Wait and Bind Information

13

Identify the Session using V$Session

Problem sessions can on occasion be singled out by checking ACTIVE sessions with high last_call_et values in v$session SQL> @/u03/scripts/database/session/qSessActive

14

Enable Trace on Identified Running Session

Once identified, the session can be traced via the familiar dbms_monitor or dbms_session packages

dbms_monitor.session_trace_enable(&SID,&SERIAL,TRUE,TRU E) Waits-TRUE,BINDS-TRUE dbms_monitor.session_trace_disable(&SID,&SERIAL)

15

We've Got a Trace File, Now What?

Use tools to summarize the trace file information into a more human readable format

Tkprof SQLDeveloper

16

Tkprof

TKPROF Syntax:

tkprof <tracefile> <location/outputfile> [explain=<username>/<passwd>@<database_sid>] [sort=<sort_options>] Suggested Sort Options: sort=exeela,fchela,prsela $ tkprof vcp1ap_ora_1444.trc vcp1ap_ora_1444.tkp

To generate TKPROF output, ordered chronologically:

To generate TKPROF output, ordered by elapsed time executing, elapsed time fetching, and elapsed time parsing:

$ tkprof vcp1ap_ora_1444.trc vcp1ap_ora_1444.txt sort=exeela,fchela,prsela

17

Tkprof Output

When using the typical sorting options sort=exeela,fchela,prsela

SQL Statements with the combined highest execution times will be listed first in the tkprof file If some statements are known and accepted to have high elapsed time, then skip past them until something is found that is not expected to take as long as it is

18

Viewing Trace Files in SQLDeveloper

Simply open the trc file in SQLDeveloper

19

Monitoring the Session Live

There are several good ways to gather more information on a currently running session

OEM Top Activity OEM Session Details SQLMonitor

20

SQLMonitor

21

SQLMonitor Key Areas Time & Wait Stats

Time Spent in DB vs. Code DB Wait Even Breakdown

I/O Stats (Buffer Gets and I/O details) Graphical Explain Plan Current Plan Hash Value Arrows marking current operation (helps draw attention to repeating operations and where time is spent) Memory and Temp Utilization Live Estimated vs Actual Rows (helps highlight Optimizer/Stats inaccuracies)

22

SQLMonitor

23

SQLMonitor Graphical Plan Visualization

24

SQLMonitor - Graphical Plan Visualization

25

SQLMonitor via SQLPlus

set pagesize 0 echo off timing off linesize 1000 trimspool on trim on long 2000000 longchunksize 2000000 select DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id=>'&SQLID', report_level=>'ALL') as report from dual;

26

Has the Plan for the Statement Changed?

Statement takes 10x longer to complete today than yesterday Check if it is due to a change in the Plan Find Statement and Plan history via OEM Search SQL

Section: Additional Instance Monitoring Links Click -> Search Sessions

27

OEM SQL History

28

OEM SQL History

29

SQL Tuning Advisor

30

SQL Tuning Advisor Recommendations

31

SQL Tuning Advisor Compare Plans

32

Performance Team Scripts - Tuna

Suite of various performance team scripts Menu Driven With Descriptions WARNING: not all scripts are simple selects, some make modifications Be careful to check any unfamiliar script before running Use 'v' before menu number to view script without running

33

Available using 'perfrep'

34

Perfrep Performance Script Repository Random Script Lookup

35

Tuna Script Directory Menu

AMTS655:amaslows> (perfrep:dbClient_10201) /home/amaslows $ tuna

36

Tuna Scripts

37

AWR Reports

What Is AWR? A background process A set of tables A set of reports Takes snapshots of statistics every hour Takes snapshot of high-cost SQL every hour

38

Preparation for Analysis

Know your systems normal performance fingerprint Have normal AWR/Statspacks for comparison

39

AWR Reports

Top-Down Approach Report starts with settings overview Next provides Top-5 waits Use the Waits to guide further investigation

40

AWR Report Header

DB Name BPELAP DB Id Instance Inst Num Startup Time Release RAC

4248199829 bpel1ap

1 12-Feb-11 03:28 11.1.0.7.0 YES

Host Name dbsv1003

Platform Linux x86 64-bit

CPUs Cores Sockets Memory(GB) 4 4 1 15.69

Snap Id Begin Snap: End Snap: Elapsed: DB Time:

Snap Time

Sessions Curs/Sess 239 304 9.5 11.8

43285 29-Mar-11 00:00:50 43308 29-Mar-11 23:00:23 1,379.53 (mins) 5,739.34 (mins)

Cache Sizes Buffer Cache: Shared Pool Size:

Begin 1,840M 512M

End 1,840M Std Block Size: 512M Log Buffer: 8K 94,276K

41

AWR Reports

Know Your Load Type! Online Transaction Processing

Few reads Many writes Many small transactions Look for redo/undo and sequential read issues

Decision Support/Data Warehouse

Many reads Few writes (other then possible temp) Few transactions Look for sort/workarea and scattered read issues

Mixed or Hybrid

42

AWR Reports

What Are Your Efficiencies Should be close to 100% Parse issues usually are a result of: Bad bind variable usage Insufficient memory

43

Load Profile Section

Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 98.99 Buffer Hit %: 75.85 Library Hit %: 97.51 Execute to Parse %: 57.65 Parse CPU to Parse Elapsd %: 0.00 Redo NoWait %: 100.00 In-memory Sort %: 100.00 Soft Parse %: 99.72 Latch Hit %: 100.12 % Non-Parse CPU: 99.00

44

Top 5 Waits Section Critical to look closely at this section Use highest wait times to guide investigation

DB FILE type waits physical IO BUFFER type waits Logical IO LOG type waits Redo related PX Parallel Query GC Global Cache (RAC related) Undo Undo or rollback segment related

45

DB File Type Waits

DB File Sequential Reads memory starvation, non-selective indexes DB File Scattered Reads full table scans, insufficient indexing Direct Path Writes Appends, data loads Direct Path Reads Parallel slaves used to retrieve data DB File Parallel Writes Backup and partition use DB File Parallel Reads Partition use DB File Single Write File header writes, excessive data files

46

Log Type Waits log file parallel write Look for log file contention log buffer space Look at increasing log buffer size log file switch (checkpoint incomplete) May indicate files or slow IO subsystem excessive db

log file switch (archiving needed) Indicates archive files are written too slowly log file switch completion May need more log files per thread log file sync Could indicate excessive commits

47

Undo Events undo segment extension If excessive, tune undo latch: In memory undo latch If excessive could be bug, check for your version, may have to turn off in memory undo wait for a undo record Usually only during recovery of large transactions, look at turning off parallel undo recovery.

48

Top Sql Section

This section displays top SQL ordered by important SQL execution metrics. The top SQL section in the AWR report contains lists of SQL statements ordered by the following criteria: Elapsed Time: Statements are ordered according to elapsed execution times. CPU Time: Statements are ordered according to CPU time. Buffer Gets: Statements are ordered according to logical reads number. Physical Reads: Statements are ordered according to physical reads number. Execution Number: Statements are ordered according to execution number. Parse Calls: Statements are ordered according to parse number.

49

Performance Load Testing Designing, developing, and executing simulated software tests using
Oracle's Real Application Testing options (Database Replay & SQL Performance Analyzer) Oracle Application Testing Suite (ATS) Custom tools (Swingbench, Load Test Harness, etc.).

Employing mathematical models to simulate real life situations. Interpreting performance test results and identifying, communicating, and facilitating resolution of inefficiencies.

50

Discussion

51