Tools for Analyzing Problems in Oracle Applications Jeff

Tools for Analyzing Problems in Oracle Applications Jeff Slavitz Jeff@Oracle. Apps. Pro. com (415) 388 -3003

Agenda Ø High-level overview of: l l Oracle Diagnostics Oracle Application Manager Statspack Tracing Ø Working with support Ø Other tools and resources Ø Bring up questions as we go along

Oracle Diagnostics Ø Users can run Diagnostics on their own! Ø Diagnostics v 2. x replaces/supplements standalone diagnostic tests Ø Use for regression testing Ø Write your own Diagnostics

Installing Oracle Diagnostics Ø Note 179661. 1 is the portal to Diagnostics knowledge. l l Instructions on how to install the latest Diagnostics pack (comes out monthly) Diagnostic catalog showing a list of all diagnostics and their function Ø Update your Diagnostics regularly. probably have a very old version. You

What are Oracle Diagnostics? Ø Diagnostics are divided into three categories: l Setup Diagnostics • Examine profile values, general application setup l Data Collection • Examine invoice, customer, project, … l Activity Diagnostics • Examine a process – period closing, invoicing, …

Logging into Diagnostics Ø Users with Sysadmin responsibility can login through Oracle Application Manager Ø Everybody can login using the URL: http: //<web-tier-host: port>/OA_HTML/jtfqalgn. htm

Click GO …

Click the Advanced tab …

…select an Application …

… then select a test from the column on the left.

• Fill in parameters and click Run Test • Some tests require a user to have a particular responsibility

When test is complete click View Report

Review report and messages. Some ‘problems’ found are really warnings.

Users can run Setup, Activity and Data Collection tests.

You can email, print and save test output

Oracle Diagnostics Summary Ø Available to end-users and DBAs Ø Use for proactive and reactive testing Ø Monthly updates from Support Ø Customize with your own Diagnostics

Oracle Application Manager

Oracle Applications Manager Ø Variety of monitoring, analysis and administration tools Ø Wealth of information Ø Workflow setup and monitoring Ø Downtime management Ø Spawns concurrent request ‘OAM Application Dashboard Collection’ (verify only one!)

Go to Rapid Install Portal page. Click Apps Logon Links.

Click ’Oracle Applications Manager’

and login ….

Alternatively, login from Sysadmin screen

Main OAM login screen

Click ‘Site Map’. This is the Administration screen.

Click ‘License Manager’ to license new products. Alternative to adlicmgr. sh

Click ‘Autoconfig’ to update your context file

ØNicer interface than the old context editor

From the Site Map, this is the Monitoring screen.

Monitor SQL Activity

Monitor JServ usage

Monitor Database Sessions. Click on AUDSID. . .

… for details of a database session.

Concurrent Processing usage

Back to Site Map – Maintenance screen

Click Applied Patches and then Timing Details to find the details of a particular patch application (useful for upgrade timing!).

Click Timing Reports to view adadmin activity

Diagnostics and Repair screen

OAM Summary Ø Lots of tools Ø Lots of data Ø Use site map to find tools that are useful to you Ø Watch that you never have more than one OAM Application Dashboard Collection concurrent request running

Statspack

Statspack Ø Creates permanently stored database performance statistical information Ø Uses ‘snapshots’ to report on performance Ø Adjust time between snapshots based on reporting needs l l General performance use long time (1 hour+) Specific problem use short time (15 minutes)

Installing Statspack Ø Note 228913. 1 is your portal to Statspack knowledge Ø In init. ora set TIMED_STATISTICS=TRUE Ø Create new tablespace for Statspack user Ø Install Statspack l l connect / as sysdba @$ORACLE_HOME/rdbms/admin/spcreate Ø Creates the user PERFSTAT which owns all Statspack data

Running Statspack Ø sqlplus perfstat/perfstat Ø execute statspack. snap Ø [wait some amount of time] Ø execute statspack. snap Ø Consider scheduling with cron or using $ORACLE_HOME/rdbms/admin/spauto. sql

Running Statspack Ø For more detailed data collection specify a level when executing snap. l l 0 gathers general performance data 5 (default) additionally gathers info on high resource usage SQL statements 6 additionally gathers execution plan information for statements found in level 5 10 additionally gathers child latches

Running Statspack Ø To specify a level: execute statspack. snap(i_snap_level=>10) Ø There are other parameters: l l Capture data for a specific session only Define threshholds for which level 5 snaps consider high usage SQL statements Ø Set note 149121. 1 for more detail on running ‘snap’.

Generating a Statspack Report Ø Now you’ve got all this great data, now what? l l Ø sqlplus perfstat/pwd @$ORACLE_HOME/rdbms/admin/spreport Previously run snapshots will be displayed. You will be prompted for: l l l The beginning snapshot Id The ending snapshot Id The name of the report text file to be created Begin and End snapshots must not include an instance shutdown during that time period Ø See note 149124. 1 for more detail on spreport Ø

Statspack Output Ø spreport generates a LOT of output with a LOT of good information. l l Instance cache size Load profile (reads, writes, …) Instance efficiency ratio (buffer hit %, buffer nowait %, …) Top 5 events and much more Ø See note 228913. 1 for more detail

Statspack Summary Ø Schedule on a regular basis to monitor general performance Ø Review data with spreport on a regular basis Ø Use when unknown system performance problem

Tracing

Tracing Ø Records every SQL statement executed to a file Ø Records timing information if TIMED_STATISTICS is true Ø Allows you to see what is happening inside a report, concurrent program or form Ø The easy part is getting the trace file Ø Review note 117129. 1 for more details

Tracing Ø Tracing concurrent programs Ø Tracing within Forms Ø Tracing within Self-Service

Concurrent Program Tracing

Forms Tracing

Enable Trace, perform activity to be traced …

… and then disable trace

Self-Service Tracing Ø Set profile option FND: Diagnostics to Yes at any level. Site allows everybody to do this. Ø In Self-Service, click on Diagnostics icon Ø Select Set Trace Level Ø Execute process in Self-Service Ø Click on Diagnostics icon to turn off trace

Now what? Ø Trace file located in udump directory. Check init. ora for location or SELECT value FROM v$parameter WHERE name=‘user_dump_dest’ Ø A single trace may result in multiple trace files, especially with self-service Ø Use ‘tkprof’ to convert trace file to a more useful format

Tkprof Usage: tkprof tracefile outputfile [explain= ] [table= ] [print= ] [insert= ] [sys= ] [sort= ] table=schema. tablename Use 'schema. tablename' with 'explain=' option. explain=user/password Connect to ORACLE and issue EXPLAIN. print=integer List only the first 'integer' SQL statements. insert=filename List SQL and data inside INSERT statements. sys=no Do not list SQL statements run as user SYS. sort=option Sort options For example: tkprof inputfile outputfile explain=apps/xxx sort='(prsela, exeela, fchela)'

Tkprof output ØSQL Statement SELECT A. JOB_ID, A. ORGANIZATION_ID FROM PER_ASSIGNMENT_STATUS_TYPES S , PER_ASSIGNMENTS_F A , PA_IMPLEMENTATIONS I WHERE A. JOB_ID IS NOT NULL AND A. PRIMARY_FLAG = 'Y' AND TRUNC(: B 2 ) BETWEEN TRUNC( A. EFFECTIVE_START_DATE ) AND TRUNC( A. EFFECTIVE_END_DATE ) AND A. PERSON_ID = : B 1 AND A. BUSINESS_GROUP_ID = I. BUSINESS_GROUP_ID AND A. ASSIGNMENT_TYPE = 'E' AND S. PER_SYSTEM_STATUS = 'ACTIVE_ASSIGN' AND S. ASSIGNMENT_STATUS_TYPE_ID = A. ASSIGNMENT_STATUS_TYPE_ID ØTiming in CPU seconds call count cpu elapsed disk query current rows -------------------Parse 1 0. 00 0 0 Execute 406 0. 03 0. 02 0 0 Fetch 406 509. 91 503. 79 106 50344 0 406 ---------- ----------total 813 509. 94 503. 82 106 50344 0 406

Tkprof output Ø Execution Plan Rows Row Source Operation -----------------------------406 NESTED LOOPS 406 TABLE ACCESS FULL PA_IMPLEMENTATIONS_ALL 406 TABLE ACCESS BY INDEX ROWID PER_ALL_ASSIGNMENTS_F 1500982 INDEX RANGE SCAN PER_ASSIGNMENTS_F_FK 1 (object id 42980) 406 TABLE ACCESS BY INDEX ROWID PER_ASSIGNMENT_STATUS_TYPES 406 INDEX UNIQUE SCAN PER_ASSIGNMENT_STATUS_TYPE_PK (object id 43099) Ø Ø For more details on tkprof review note 117129. 1 For more details on explain plan review note 46234. 1

Working with Support Help is just a phone call away

Working with Support Ø Make the most out of Metalink search ability l l Use Advanced Search Enter exact text of error message Specify form or report name, if known Search Technical Forums Ø Open Service Request in parallel with trying to solve problem

Working with Support Ø Recognize Support is busy Ø Some problems are easier to solve than others Ø Be nice – the Support community is small Ø If problem is outside of analyst’s area of expertise help him realize that and request a SR transfer Ø If analyst’s time zone is an issue, request a SR transfer

Working with Support Ø If not getting prompt response you may need to more aggressively manage your SR l l Frequent updates to SR for status Three updates without analyst response changes SR to immediate response required You need to call 800 number to see if analyst is out, to ping analyst or to duty manage SR Ø For repeated problems have your manager talk with the Oracle customer rep Ø

Other Tools and Resources Ø Third party products such as Foglight Ø google Ø asktom. oracle. com Ø www. oaug. org/listservers Ø Nor. Cal. OAUG and OAUG events

Questions? Other tips and techniques? Want a copy of this presentation? Feel free to email or call me … Jeff Slavitz Jeff@Oracle. Apps. Pro. com (415) 388 - 3003