Tools for Analyzing Problems in Oracle Applications

1
Tools for Analyzing Problems in Oracle
Applications

• Jeff SlavitzJeff_at_OracleAppsPro.com
• [phone]

2
(No Transcript)
3
Agenda

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

4
Oracle Diagnostics

• Users can run Diagnostics on their own!
• Diagnostics v2.x replaces/supplements standalone
  diagnostic tests
• Use for regression testing
• Write your own Diagnostics

5
Installing Oracle Diagnostics

• Note 179661.1 is the portal to Diagnostics
  knowledge.
• 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. You probably
  have a very old version.

6
What are Oracle Diagnostics?

• Diagnostics are divided into three categories
• Setup Diagnostics
• Examine profile values, general application setup
• Data Collection
• Examine invoice, customer, project,
• Activity Diagnostics
• Examine a process period closing, invoicing,

7
Logging into Diagnostics

• Users with Sysadmin responsibility can login
  through Oracle Application Manager
• Everybody can login using the URL

http//ltweb-tier-hostportgt/OA_HTML/jtfqalgn.htm
8
Click GO
9
(No Transcript)
10
Click the Advanced tab
11
select an Application
12
then select a test from the column on the left.
13

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

14
When test is complete click View Report
15
Review report and messages. Some problems
found are really warnings.
16
Users can run Setup, Activity and Data Collection
tests.
17
You can email, print and save test output
18
Oracle Diagnostics Summary

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

19
Oracle Application Manager
20
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!)

21
Go to Rapid Install Portal page. Click Apps
Logon Links.
22
Click Oracle Applications Manager
23
and login .
24
Alternatively, login from Sysadmin screen
25
Main OAM login screen
26
Click Site Map. This is the Administration
screen.
27
Click License Manager to license new
products. Alternative to adlicmgr.sh
28
Click Autoconfig to update your context file
29

• Nicer interface than the old context editor

30
From the Site Map, this is the Monitoring screen.
31
Monitor SQL Activity
32
Monitor JServ usage
33
Monitor Database Sessions. Click on AUDSID ...
34
for details of a database session.
35
Concurrent Processing usage
36
Back to Site Map Maintenance screen
37
Click Applied Patches and then Timing Details to
find the details of a particular patch
application (useful for upgrade timing!).
38
Click Timing Reports to view adadmin activity
39
Diagnostics and Repair screen
40
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

41
Statspack
42
Statspack

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

43
Installing Statspack

• Note 228913.1 is your portal to Statspack
  knowledge
• In init.ora set TIMED_STATISTICSTRUE
• Create new tablespace for Statspack user
• Install Statspack
• connect / as sysdba
• _at_ORACLE_HOME/rdbms/admin/spcreate
• Creates the user PERFSTAT which owns all
  Statspack data

44
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

45
Running Statspack

• For more detailed data collection specify a level
  when executing snap.
• 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

46
Running Statspack

• To specify a level
• execute statspack.snap(i_snap_levelgt10)
• There are other parameters
• 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.

47
Generating a Statspack Report

• Now youve got all this great data, now what?
• sqlplus perfstat/pwd
• _at_ORACLE_HOME/rdbms/admin/spreport
• Previously run snapshots will be displayed. You
  will be prompted for
• 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

48
Statspack Output

• spreport generates a LOT of output with a LOT of
  good information.
• 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

49
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

50
Tracing
51
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

52
Tracing

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

53
Concurrent Program Tracing
54
Forms Tracing
55
Enable Trace, perform activity to be traced
56

• and then disable trace

57
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

58
Now what?

• Trace file located in udump directory. Check
  init.ora for location or
• SELECT value
• FROM vparameter
• WHERE nameuser_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

59
Tkprof

• Usage tkprof tracefile outputfile explain
  table
• print insert sys sort
  
• tableschema.tablename Use 'schema.tablename'
  with 'explain' option.
• explainuser/password Connect to ORACLE
  and issue EXPLAIN PLAIN.
• printinteger List only
  the first 'integer' SQL statements.
• insertfilename List SQL and
  data inside INSERT statements.
• sysno Do not
  list SQL statements run as user SYS.
• sortoption Sort
  options
• For example
• tkprof inputfile outputfile explainapps/xxx
  sort'(prsela,exeela,fchela)'

60
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(B2 ) BETWEEN
• TRUNC( A.EFFECTIVE_START_DATE ) AND TRUNC(
  A.EFFECTIVE_END_DATE ) AND
• A.PERSON_ID B1 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.00
  0 0 0 0
• Execute 406 0.03 0.02
  0 0 0 0
• Fetch 406 509.91 503.79 106
  50344 0 406
• ------- ------ -------- ----------
  ---------- ---------- ---------- ----------

61
Tkprof output

• Execution Plan
• Rows Row Source Operation
• ------- -----------------------------------------
  ----------
• 406 NESTED LOOPS
• 406 NESTED LOOPS
• 406 TABLE ACCESS FULL PA_IMPLEMENTATIONS_AL
  L
• 406 TABLE ACCESS BY INDEX ROWID
  PER_ALL_ASSIGNMENTS_F
• 1500982 INDEX RANGE SCAN PER_ASSIGNMENTS_F_FK1
  (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

62
Working with Support
Help is just a phone call away
63
Working with Support

• Make the most out of Metalink search ability
• 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

64
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 analysts area of
  expertise help him realize that and request a SR
  transfer
• If analysts time zone is an issue, request a SR
  transfer

65
Working with Support

• If not getting prompt response you may need to
  more aggressively manage your SR
• 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

66
Other Tools and Resources

• Third party products such as Foglight
• google
• asktom.oracle.com
• www.oaug.org/listservers
• NorCalOAUG and OAUG events

67

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