MANAGING PERFORMANCE IN ORACLE APPLICATIONS RELEASE 11 USING STATSPACK

1
MANAGING PERFORMANCE IN ORACLE APPLICATIONS
RELEASE 11 USING STATSPACK
Rocky Mountain Oracle Users Group20 February,
2002Tim Gorman - SageLogix, Inc.Mary Crystal -
Echostar Communications Corporation
2
Introduction

• Managing performance in any database is an
  iterative, proactive and reactive exercise
• DBA perspective on performance tuning
• User calls Why is it so slow?
• Where do you look first?
• How do you know if it is worth pursuing?
• Whats changed recently?
• What can help?

3
Introduction

• Lots of big expensive tools around
• Need something cheap, easy, and effective
• STATSPACK is a package included with the Oracle8i
  RDBMS
• Not installed automatically
• Not well understood
• Not designed specifically for Ora Apps R11
• It is the focus our discussion today

4
Agenda

• Total Performance Management
• Typical Oracle Apps R11 environment
• Choosing STATSPACK
• Installing and gotchas
• Reporting
• Adjusting the package for Oracle Apps R11
• Improving the package

5
Total Performance Management

• Know your environment.
• Based on a description, can you identify the
  components that are probably involved?
• Have a Baseline
• You cant determine whats abnormal until you
  know whats normal
• And you have to PROVE it with factual data!
• By retaining evaluating consistent metrics
• Trends can be identified

6
Total Performance Management

• Have a Baseline (contd)
• Keep history
• Start now!
• Even if you dont evaluate your data daily
• Beyond the database
• Use the same time intervals for Network O/S
  metrics allows for easier correlation
• Keep in mind the end-users experience.

7
The Typical R11 environment

• Several areas of complexity exist
• 3 Tiered architecture
• Browser-based end-user interface.
• Dont forget the Jinitiator!
• Application Server which includes the Forms and
  Web servers.
• Database Server
• Concurrent Managers

8
Choosing STATSPACK

• What is right about STATSPACK?
• Cheap
• as in FREE
• Portable across all Oracle platforms
• Written entirely in SQL and PL/SQL
• Easy to install and use
• and de-install and disable, then re-enable, if
  you wish
• Safe
• Oracle Support uses it to process TARs logged on
  tuning problems

9
Using STATSPACK

• Evolved from venerable BSTAT/ESTAT
• BSTAT/ESTAT has been around forever
• Simply displays delta (changes) of statistics in
  V views between two snapshots
• Report in file report.txt
• STATSPACK does the same as BSTAT/ESTAT
• Takes snapshots of statistics in V views
• But stores the information in tables
• Report is much more informative than report.txt
• But curiously as difficult to use as report.txt!

10
Using STATSPACK

• Everything resides within an account / schema
  named PERFSTAT
• Snapshots are scheduled and run using the
  DBMS_JOB package
• By default, takes a snapshot every hour
• configurable (of course!)
• Unlike BSTAT/ESTAT, also captures info from
  VSQLAREA
• Vital tuning information!

11
Using STATSPACK

• STATSPACK produces only one report
• But it is much more useable and comprehensive
  than BSTAT/ESTATs report.txt
• Contains some interpretations and advice
• Oracle9i contains an extra detailed report for
  drilling down on the history of a single SQL
  statement
• thats all folks!

12
Installing STATSPACK

• Use either the v8.1.7 or v9.0.1 versions of
  STATSPACK
• Regardless of version RDBMS you are using
• shouldnt bother with the v8.1.6 version of
  STATSPACK
• No worries! Each new version contains an upgrade
  script from the previous version
• Files in ORACLE_HOME/rdbms/admin
• Named stats. in v8.1.6 version
• Named sp. in v8.1.7 and v9.0.1 versions

13
Installing STATSPACK

• Read online documentation spdoc.txt first!
• - Overview of STATSPACK
• - Detailed installation instructions
• - Gathering data manually or through automated
  tools
• - Running a report interactively or via batch
• - Setting thresholds
• - Purging data
• CONNECT INTERNAL in SQLPlus

14
Installing STATSPACK

• Run SQLPlus script spcreate.sql
• Calls spcusr.sql to create PERFSTAT
• Assigns DEFAULT and TEMP tablespaces
• Prompts for tablespace names
• Good time to think about sizing
• performs GRANTS
• create session, alter session
• create table, create procedure, create sequence
• create public synonym, drop public synonym
• Adds new X tables to SYS schema

15
Installing STATSPACK

• Run SQLPlus script spcreate.sql (contd)
• Re-connects as PERFSTAT to continue
• Calls spctab.sql to create tables, sequences,
  synonyms
• NOTE indexes are created in DEFAULT tablespace
• Might have to re-create them in a more desirable
  tablespace
• Calls spcpkg.sql to create package STATSPACK
• Creates public synonyms for package

16
Installing STATSPACK

• Check spooled output files for errors
• grep i err spc.lis
• If you have any errors
• Correct them
• De-install and start over again
• CONNECT INTERNAL in SQLPlus
• Run SQLPlus script spdrop.sql
• Calls scripts spdtab.sql and spdusr.sql
• Check spooled output files spd.lis for errors!
• Then repeat installation steps

17
Installing STATSPACK

• Automatic job submission
• CONNECT PERFSTAT in SQLPlus
• Run SQLPlus script spauto.sql
• Configures STATSPACK.SNAP once an hour
• Edit the script if youd like a different
  frequency
• Purging data
• CONNECT PERFSTAT in SQLPlus
• Run SQLPlus script sppurge.sql
• Purges a range of snap-ids
• Gathering hourly, purging every 3 days retains
  300Mb in source database.

18
Special tasks

• Clearing out the STATSPACK tables
• CONNECT PERFSTAT in SQLPlus
• Run SQLPlus script sptrunc.sql
• Exporting the PERFSTAT schema
• Perhaps to send it to Oracle Support?
• Use parameter file spuexp.par
• exp parfilespuexp.par
• Password

19
Reporting from STATSPACK

• CONNECT PERFSTAT in SQLPlus
• Run SQLPlus script spreport.sql
• Will either prompt interactively for parameter
  values
• OR
• Can be run in batch mode when parameter values
  are supplied as SQLPlus substitution variables

20
Reporting from STATSPACK

• SQLgt connect perfstat/perfstat
• Connected.
• SQLgt _at_spreport
• DB Id DB Name Inst Num Instance
• ----------- ------------ -------- ------------
• 2618106428 PRD1 1 prd1
• Completed Snapshots
• Snap
  Snap
• Instance DB Name Id Snap Started
  Level Comment
• ------------ ------------ ----- -----------------
  -----
• prd1 PRD1 1 11 May 2001 1207
  5
• 2 11 May 2001 1208
  5

21
Reporting from STATSPACK

• Specify the Begin and End Snapshot Ids
• Enter value for begin_snap 1
• Begin Snapshot Id specified 1
• Enter value for end_snap 2
• End Snapshot Id specified 2
• Specify the Report Name
• The default report file name is sp_1_2 To use
  this name,
• hit ltreturngt to continue, otherwise enter an
  alternative.
• Enter value for report_name ltreturn or enter new
  namegt
• Using the report name sp_1_2

22
Reporting from STATSPACK

• Running from batch mode
• SQLgt connect perfstat
• Enter password
• Connected
• SQLgt define begin_snap1
• SQLgt define end_snap2
• SQLgt define report_namesp_1_2
• SQLgt _at_spreport

23
Reporting from STATSPACK

• Cache Sizes
• db_block_buffers 40960000 log_buffer
  1048576
• db_block_size 8192 shared_pool_size
  104857600
• Load Profile Per Second Per
  Transaction
• ---------------
  ---------------
• Redo size 54,378.06
  23,189.73
• Logical reads 128,947.97
  54,990.36
• Block changes 239.97
  102.33
• Physical reads 2,384.38
  1,016.83
• Physical writes 25.07
  10.69
• User calls 182.21 77.71

24
Reporting from STATSPACK

• Parses 29.49
  12.58
• Hard parses 0.35
  0.15
• Sorts 37.63
  16.05
• Logons 0.22
  0.09
• Executes 180.99
  77.18
• Transactions 2.34
• Blocks changed per Read 0.19
• Recursive Call 57.54
• Rollback per transaction 5.65
• Rows per Sort 68.35

25
Reporting from STATSPACK

• Instance Efficiency Percentages (Target 100)
• Buffer Nowait 99.98 Redo NoWait
  100.00
• Buffer Hit 98.15 In-memory Sort
  99.97
• Library Hit 99.89 Soft Parse
  98.80
• Execute to Parse 83.71 Latch Hit
  99.98
• Parse CPU to Parse Elapsed 77.98
• Non-Parse CPU 100.00
• Shared Pool Statistics Begin End
• ------ ------
• Memory Usage 74.94 71.95
• SQL with executionsgt1 80.60 80.55
• Memory for SQL w/execgt1 83.33 83.55

26
Reporting from STATSPACK

• Top 5 Wait Events
• Event Waits Time (cs) Wt
  Time
• -------------------- ------------ ------------
  -------
• enqueue 24,345 7,194,663
  49.50
• db file sequential r 40,848,049 3,490,281
  24.01
• db file scattered re 3,175,741 2,320,214
  15.96
• SQLNet message from 277,128 968,395
  6.66
• latch free 164,340 155,845
  1.07

27
Reporting from STATSPACK

• Buffer Gets Execs Gets/Exec Total Hash
  Value
• -------------- ------- ------------ -------
  -----------
• 1,537,435,479 90 17,082,616.4 33.2
  1394818125
• select a.report_id, decode(a.batch_id,null,
  b.batch_id, a.batch_id) rbatch_id, c.NAME,
  c.NUMBER, a.NAME, b.BANK_NAME, c.ACCOUNT_NAME,a.RE
  CEIPT_NUMBER, aps.
• DUE_DATE, decode(acr.selected_remittance_batch_id,
  null

28
YAPP Reports

• Yet Another Performance Profiler
• Available from http//www.oraperf.com
• Unofficial website run by members of Oracle
  Server Technologies division
• Upload either BSTAT/ESTAT report.txt file or
  STATSPACK report file
• Returns an HTML page containing an amazing
  response-time analysis report

29
YAPP Reports

• Organized from overview to hyperlinked detail
  sections
• Header (version info, time span of report, etc)
• Response-time breakout
• CPU Time or time spent processing SQL
• Parse, recursive, and other CPU Time breakouts
• Wait Time or time not spent processing SQL
• Initialization parameter settings
• Tuning advise summary

30
YAPP Reports

• General Information
• The following comments were generated while
  processing file C\Temp\sp_5349_5350.lst
• Disclaimer Use information at own risk !
• All timing information is in 1/100 sec, unless
  stated otherwise.
• The timing period in this report is too long to
  get any useful tuning advise.
• End Buffer Gets Threshold 100000
• Note that resources reported for PL/SQL includes
  the resources used by all SQL statements called
  within the PL/SQL code. As individual SQL
  statements are also reported, it is possible and
  valid for the summed total 10021160010 exceed 100
  
• End Executions Threshold 1000
• only latches with sleeps are shown
• ordered by name, sleeps desc NoWait Waiter
• Please be advised that running STATSNAP on
  releases before Oracle8i can give problems.
• Please be advised that Oracle8 version 8.0.6 is
  the terminal release for Oracle8. You are on an
  older release.
• Uploaded 167060 bytes in 5.10 seconds

31
YAPP Reports

Response Time
32
YAPP Reports

CPU Time

33
YAPP Reports

Wait Time
34
YAPP Reports
35
Customizing STATPACK

• How should STATSPACK be altered for Ora Apps
  R11/R11i?
• Capture OraApps Concurrent Manager info
• Display MODULE (from VSESSION and VSQLAREA) in
  reports
• For OraApps and other applications using the
  DBMS_APPLICATION_INFO package
• Increase thresholds when data gathering
• filter out huge amounts of unnecessary data
• Consume less space

36
Capture ConcMgr data

• Table FND_CONCURRENT_REQUESTS in Ora Apps
  foundation schema APPLSYS
• Contains valuable elapsed time information for
  batch jobs and reports
• Only elapsed time information from interactive
  forms is missing in Ora Apps
• Leaving a gap in the record of the total end-user
  experience
• It is a standard best-practice in Ora Apps
  administration to purge this table periodically

37
Capture ConcMgr data

• Customization Suggestion
• Replicate data to the back-end reporting and
  analysis database
• Retain ConcMgr job statistics for several
  business cycles
• Allowing analysis of ConcMgr activity
• Allows frequent purging of FND_CONCURRENT_REQUESTS
  table in Ora Apps foundation schema
• Optimizing ConcMgr performance

38
Display MODULE in reports

• Standard v8.1.6, v8.17, and v9.0.1 STATSPACK
  script spreport.sql does not include value of
  column MODULE (i.e. Ora Apps executable name)
• Helps in associating a poorly-performing SQL
  statement to a form, report, or program
• Metalink note 153502.1 fixes v8.1.7 spreport.sql
• Metalink note 153503.1 fixes v8.1.6 statsrep.sql
• Metalink note 153505.1 fixes v9.0.1 sprepins.sql

39
Increase thresholds

• When capturing SQL statement tuning information
  from VSQLAREA
• STATSPACK installed by default has very low
  statistics thresholds for selecting SQL
  statements
• Gathers info about rather unimportant SQL
  statements
• Advisable to set them higher for Ora Apps
• Reduces the amount of storage consumed by
  STATSPACK dramatically

40
Increase thresholds

• Metalink note 153507.1 contains good suggested
  thresholds
• But does not suggest a good method for setting
  the parameter
• Instead of adding new thresholds as parameters to
  STATSPACK.SNAP procedure call
• Update the table STATSSTATSPACK_PARAMETER with
  the threshold values to make the settings
  permanent

41
Customizing STATSPACK

• How can STATSPACK be improved?
• Automated ANALYZE of PERFSTAT
• Automated purge of PERFSTAT tables
• More data warehouse-like architecture
• Replicate data to a decision-support database
• More reporting capabilities
• More reports
• Ability to perform ad-hoc queries

42
Automated ANALYZE

• Just like anything else, the PERFSTAT schema
  should be analyzed periodically
• To optimize the performance of report processing
  and other queries in PERFSTAT
• Performance of STATSPACK.SNAP procedure not
  affected
• Only SELECTs from V views and INSERTs into
  PERFSTAT schema
• Customization Suggestion
• Run DBMS_STATS.GATHER_SCHEMA_STATS using the
  DBMS_JOB systems

43
Automated purge

• SQLPlus script sppurge.sql cannot be run from
  automated DBMS_JOB system
• Must run from an external job-scheduling facility
• UNIX cron, NT/2000 at, CA AutoSys or Unicenter,
  etc
• Customization Suggestion
• Rewrite the sppurge.sql script as a PL/SQL stored
  procedure
• Called from DBMS_JOB once per day

44
More DW-like architecture

• Reporting/analysis systems (DWs) are usually
  separated from operational systems
• STATSPACK data should be replicated to another
  system for reporting / analysis
• Customization Suggestion
• Leave standard STATSPACK on database being
  monitored
• Build custom replication package to customized
  STATSPACK on a back-end database

45
More DW-like architecture

• Customization Suggestion (contd)
• Purge data in PERFSTAT schema in front-end
  database (the one being monitored) to minimize
  the impact of storage
• Allow PERFSTAT schema in back-end database to
  retain more data for long-term analysis (i.e.
  trending, summaries, anomalies)
• Best to retain performance data across numerous
  distinct business cycles

46
More DW-like architecture

• Customization Suggestion (contd)
• Store data from multiple front-end databases in a
  single back-end database
• Standard STATSPACK labels all data by DBID and
  INSTANCE_NUMBER anyway
• The standard STATSPACK installation in the
  front-end database is not altered in any way
• Except by frequent data purges

47
More reporting capabilities

• Standard STATSPACK only captures snapshots of
  cumulative statistics
• So each row has the current total value
• Script spreport.sql has logic to calculate deltas
  or changes in value between two snapshots
• But this type of data prevents most reporting and
  analysis
• Cannot summarize
• Cannot analyze across instance restarts
• All statistics reset to zero

48
More reporting capabilities

• Customization Suggestion
• In the back-end (reporting and analysis)
  database, add and populate new columns for
  holding delta values between snapshots
• For example
• ALTER TABLE STATSSYSSTAT
• ADD (VALUE_INC NUMBER)
• Does not affect standard STATPACK installation in
  front-end database that is being monitored

49
More reporting capabilities

• Availability of delta data values allows simple
  queries across all snapshots
• select trunc(s.snap_time),
• sum(f.phyrds_inc)
• from statssnapshot s,
• statsfilestatxs f
• where s.snap_id f.snap_id
• group by trunc(s.snap_time)

50
Conclusion

• Cannot improve that which you dont measure!
• STATSPACK is a cheap tool for gathering
  measurement metrics
• Still needs some improvements
• but even out-of-the-box it is an excellent start!
• Use the YAPP report from www.oraperf.com
• Better than the standard report
• High-Performance Tuning with STATSPACK by Donald
  K. Burleson

51
QA