1 / 67

Tools for Analyzing Problems in Oracle Applications

Tools for Analyzing Problems in Oracle Applications. Jeff Slavitz Jeff@OracleAppsPro.com (415) 388-3003. 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.

kendra
Download Presentation

Tools for Analyzing Problems in Oracle Applications

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Tools for Analyzing Problems in Oracle Applications Jeff SlavitzJeff@OracleAppsPro.com (415) 388-3003

  2. 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

  3. 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

  4. 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.

  5. 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, …

  6. 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

  7. Click GO …

  8. Click the Advanced tab …

  9. …select an Application …

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

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

  12. When test is complete click View Report

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

  14. Users can run Setup, Activity and Data Collection tests.

  15. You can email, print and save test output

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

  17. Oracle Application Manager

  18. 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!)

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

  20. Click ’Oracle Applications Manager’

  21. and login ….

  22. Alternatively, login from Sysadmin screen

  23. Main OAM login screen

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

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

  26. Click ‘Autoconfig’ to update your context file

  27. Nicer interface than the old context editor

  28. From the Site Map, this is the Monitoring screen.

  29. Monitor SQL Activity

  30. Monitor JServ usage

  31. Monitor Database Sessions. Click on AUDSID ...

  32. … for details of a database session.

  33. Concurrent Processing usage

  34. Back to Site Map – Maintenance screen

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

  36. Click Timing Reports to view adadmin activity

  37. Diagnostics and Repair screen

  38. 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

  39. Statspack

  40. 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)

  41. 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 • connect / as sysdba • @$ORACLE_HOME/rdbms/admin/spcreate • Creates the user PERFSTAT which owns all Statspack data

  42. 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

  43. 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

  44. Running Statspack • To specify a level: execute statspack.snap(i_snap_level=>10) • 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’.

  45. Generating a Statspack Report • Now you’ve got all this great data, now what? • sqlplus perfstat/pwd • @$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

  46. 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

  47. 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

  48. Tracing

More Related