Focusing Your Oracle Database Tuning Efforts For PeopleSoft Applications

1
Focusing Your Oracle Database Tuning Efforts For
PeopleSoft Applications

• Bobby Durrett
• U. S. Foodservice, Inc.

2
Introduction

• PeopleSoft is a complicated application
• There are many things that can be tuned
  hardware, operating system, Oracle and the
  application
• There are many books, articles, etc. on Oracle
  tuning
• Need a way to focus your efforts on the one area
  that will help the most

3
Oracle Server Processes

• Each PeopleSoft program connects to an Oracle
  server process
• If a PeopleSoft program that you care about is
  slow you need to see where its Oracle process is
  spending its time
• If you can find out where the server process is
  spending the majority of its time you can focus
  your tuning efforts on reducing that time

4
Oracle Server Processes - continued

• A reduction in the Oracle server processs time
  will result in an equal reduction in the
  PeopleSoft programs time
• An Oracle server process is a window on all the
  other pieces of the database - it is where the
  PeopleSoft application touches everything else on
  the database server

5
(No Transcript)
6
Performance Profile

• Oracle supplies CPU time and wait time for a
  server process
• You can produce a Performance Profile of the
  server process or session
• CPU time
• Wait time
• Elapsed time end time minus start time
• CPU time plus wait time should equal elapsed time
  but doesnt always

7
Example Of A Performance Profile
TIMESOURCE ELAPSED_SECONDS ----
------------------------- --------------- REALELAP
SED 25 db file
scattered read 16.000231 CPU
8.87 db file
sequential read 1.286592 SQLNet
message from client .257231 log file
sync .043774 direct path
write .028848 SQLNet message
to client .000039
8
Where We Are Going From Here

• Simple way to get a profile
• Four different categories time can fall under
• How to find the correct Oracle session
• A more accurate way to get a profile
• A tool that gives you a profile of SQL that has
  occurred in the past
• Conclusion and references

9
How To Get A Performance Profile Using V Tables
Before Script

• Run script to start monitoring an Oracle session
• Get waits from VSESSION_EVENT
• Get CPU from VSESSTAT
• Get current time
• Store these in a table

10
DROP TABLE BEFOREOTHERSESSION CREATE TABLE
BEFOREOTHERSESSION AS SELECT SID,EVENT
TIMESOURCE,(TIME_WAITED/100) SECONDS FROM
VSESSION_EVENT WHERE SIDMONITORED_SID INSER
T INTO BEFOREOTHERSESSION SELECT SID,'CPU'
TIMESOURCE,(VALUE/100) SECONDS FROM VSESSTAT
WHERE SIDMONITORED_SID AND STATISTIC(SELECT
STATISTIC FROM VSTATNAME WHERE NAME'CPU used
by this session') COMMIT
11
INSERT INTO BEFOREOTHERSESSION SELECT
SID,'REALELAPSED' TIMESOURCE, (SYSDATE-TO_DATE('01
/01/1900','MM/DD/YYYY'))246060 SECONDS FROM
VSESSION WHERE SIDMONITORED_SID COMMIT
12
How To Get A Performance Profile Using V Tables
After Script

• Run script to stop monitoring a session and to
  get a performance profile report
• Union together times for waits, CPU and current
  time
• Subtract the times stored by the before script
• Output the difference in order of most time to
  least

13
SELECT AFTER.TIMESOURCE, AFTER.SECONDS-BEFORE.SECO
NDS ELAPSED_SECONDS FROM (SELECT SID,EVENT
TIMESOURCE,(TIME_WAITED/100) SECONDS FROM
VSESSION_EVENT WHERE SIDMONITORED_SID UNION S
ELECT SID,'CPU' TIMESOURCE,(VALUE/100) SECONDS
FROM VSESSTAT WHERE SIDMONITORED_SID AND
STATISTIC(SELECT STATISTIC FROM VSTATNAME
WHERE NAME'CPU used by this session')
14
UNION SELECT SID,'REALELAPSED' TIMESOURCE, (SYSDAT
E-TO_DATE('01/01/1900','MM/DD/YYYY'))246060
SECONDS FROM VSESSION WHERE SIDMONITORED_SID)
AFTER, BEFOREOTHERSESSION BEFORE WHERE BEFORE.SID
AFTER.SID AND AFTER.TIMESOURCEBEFORE.TIMESOURCE
ORDER BY ELAPSED_SECONDS DESC DROP TABLE
BEFOREOTHERSESSION
15
Four Types of Time Main Point of the
Presentation

• Non-Idle waits - waits within the database
• Idle waits waits outside the database
• CPU
• Unaccounted-for time real time minus sum of
  waits and CPU

16
Non-idle Waits

• Ones that you hear most about
• Lots of documentation about what these mean
• Oracle documentation
• Metalink
• Books
• Articles
• Can usually reduce these by changing something in
  the database

17
Common Non-Idle Waits

• buffer busy waits
• db file scattered read
• db file sequential read
• enqueue
• latch free
• log file sync

18
Non-Idle Wait Example
TIMESOURCE ELAPSED_SECONDS ----
------------------------- --------------- REALELAP
SED 32 enqueue
30.8 CPU
0
19
Non-Idle Wait Example

• Enqueue wait comprises almost all of the time
• Indicates that the session is hung on a lock
• Just find the session that is holding the lock
  and kill it

20
Idle Waits

• Typically recommended that you ignore these
• Not as frequently discussed
• Many begin with SQLNet
• Cant fix by changing something in the database

21
Idle Wait Example
TIMESOURCE ELAPSED_SECONDS ----
------------------------- --------------- REALELAP
SED 37 SQLNet
message from dblink 35.51 SQLNet
message from client 1.19 CPU
.48 log file sync
.02
22
Idle Wait Example

• Almost all of the time spent waiting for SQLNet
  message from dblink
• This means that the time is spent on a remote
  database
• Need to go to the remote database and find the
  session there and tune it

23
CPU

• Server processs CPU time
• Means that the database blocks being accessed are
  in memory

24
CPU Example
TIMESOURCE ELAPSED_SECONDS ----
------------------------- --------------- REALELAP
SED 39 CPU
35.7 db file
sequential read 3.18 SQLNet
message from client 1.2 control
file parallel write .01 log file
sync .01
25
CPU Example

• Almost all of the time is spent using the CPU
• Tune SQL to reduce number of memory accesses
• No need to look at I/O systems performance

26
Unaccounted-For Time

• Least-documented concept in this talk
• Difference between real time that elapsed and the
  sum of all the waits and the CPU
• It is time that Oracles measurements do not
  account for
• Best examples are CPU queue wait time and time
  spent waiting on paging

27
Unaccounted-For Time Example
TIMESOURCE ELAPSED_SECONDS ------
--------------------- --------------- REALELAPSED
144 CPU
45.14 SQLNet message from
client .57 db file sequential read
.17 db file scattered read
.08 log file sync
.03
28
Unaccounted-For Time Example

• The majority of the time (2/3) is not accounted
  for by the CPU time or waits
• Three CPU-intensive SQLs running on my
  single-processor laptop
• Unaccounted-for time is time spent on the CPU
  queue
• Fixed by reducing contention for the CPU

29
Determining Your Oracle Session

• To get a profile you have to figure out which
  session corresponds to the PeopleSoft program
  that is slow
• This is complicated by the fact that all
  PeopleSoft programs login as the same Oracle user
• Also, some Oracle sessions are shared by multiple
  PeopleSoft users

30
Many PeopleSoft Programs Connect To One Oracle
Session Or Server Process

• Batch
• Sqr
• Cobol
• Crystal
• Nvision
• Application Engine
• Two-tier Windows client

31
Some PeopleSoft programs share Oracle sessions

• PeopleSofts application server has several
  connections to the Oracle database
• PeopleSoft programs that use the app server share
  the app servers Oracle sessions
• Web connections
• Three-tier Windows Client

32
(No Transcript)
33
How To Find Your Oracle Session Using V Tables

• Can use VSESSION and VSQLAREA to find the
  active PeopleSoft SQL statements
• Oracle user SYSADM
• Can use CLIENT_INFO column of VSESSION to
  determine PeopleSoft userid and other information
• Need to set parameter EnableDBMonitoring1 in app
  server config file to get CLIENT_INFO populated

34
Query To Find Oracle Session Id
SELECT A.SID, A.SERIAL, TO_CHAR(A.LOGON_TIME,'MM
-DD-YYYY HH24MISS') "Logon Time", A.CLIENT_INFO
, C.SQL_TEXT FROM VSESSION A,VSQLAREA C WHERE
A.SQL_ADDRESSC.ADDRESS () AND A.SQL_HASH_VALUE
C.HASH_VALUE () AND A.USERNAME 'SYSADM'
AND A.STATUS'ACTIVE' ORDER BY A.STATUS,A.SID,A.SE
RIAL
35
App Server CLIENT_INFO Example

• SMITH,,10.1.2.3,PROD,PSAPPSRV,
• PeopleSoft userid
• Users IP address
• Database name
• Program name

36
SQR CLIENT_INFO Example

• JONES,12904
• PeopleSoft user id
• SQR Unix process id

37
Using Extended SQL Trace And TKPROF To Get A
Profile

• Term comes from Cary Millsap and Jeff Holts book
  read it for tons more good information about
  this
• Get Oracle trace with waits Extended SQL
  Trace
• Use TKPROF to produce report with waits
• Piece together a profile from the TKPROF output
• More accurate than V tables contains wait
  details
• Only works in 9i or higher (TKPROF waitsyes)

38
Trace And TKPROF Commands

• Commands to start and stop trace work from
  sqlplus
• First two arguments are session id and serial
  number
• TKPROF arguments indicate use of waits
• Orders SQL by total elapsed time so you see the
  longest running SQLs first

39
Using Extended Trace and TKPROF
To start trace execute sys.dbms_system.set_ev(12
,23,10046,8,) To end trace execute
sys.dbms_system.set_ev(12,23,10046,0,) TKPROF
command tkprof tracefile.txt tkprofout.txt
waitsyes "sort(PRSELA,EXEELA,FCHELA)" SYSNO
40
Example TKPROF Output

• I highlight the numbers that will go on a
  performance profile in red
• Elapsed and CPU time come from the first part of
  the report
• The wait times come from the second part

41
Sample TKPROF Output With Waits
select count() from dba_segments call
count cpu elapsed disk query
current rows ------- ------ --------
---------- ---------- ---------- --------- Parse
1 0.13 0.25 10
52 0 Execute 1 0.00 0.00
0 0 0 Fetch 2
7.99 30.57 18953 319306
0 ------- ------ -------- ---------- ----------
---------- ---------- total 4 8.12
30.83 18963 319358 0
42
Sample TKPROF Output With Waits
Elapsed times include waiting on following
events Event waited on Times
Max. Wait Total Waited --------------------------
--- Waited ---------- ------------ SQLNet
message to client 2 0.00
0.00 db file scattered read 1974
0.06 17.14 db file sequential read
3621 0.03 5.25 SQLNet
message from client 2 0.01
0.01

43
TKPROF Output As A Performance Profile
TIMESOURCE ELAPSED_SECONDS ----
------------------------- --------------- REALELAP
SED 30.83 db file
scattered read 17.14 CPU
8.12 db file
sequential read 5.25 SQLNet
message from client 0.01
44
Using I3 To Get A Profile Of Something In The Past

• I3 is a tool from Veritas
• Formerly known as Precise
• Stores information you need to get a profile of a
  SQL statement that occurred in the past
• Have to piece together profile from two places

45
In Oracle Display

• What I3 calls In Oracle time corresponds to
  what I call
• Non-idle waits I/O Wait
• CPU Using CPU
• Unaccounted-for time CPU wait or Memory wait

46
(No Transcript)
47
Overall Activity Display

• What I3 calls Overall Activity time corresponds
  to what I call
• Idle waits Request Wait
• Everything else bundled together under In
  Oracle

48
(No Transcript)
49
Conclusion

• Get a profile of the session that corresponds to
  the PeopleSoft program that is slow
• Include all of the information Oracle gives you
  in the profile and compare it to the real time
  that elapses
• Use that profile to direct your tuning efforts so
  you dont waste time trying everything to improve
  it

50
References

• Direct Contention Identification Using Oracle's
  Session Wait Event Views - Craig Shallahamer
• Microstate Response-time Performance Profiling -
  Danisment Gazi Unal
• Optimizing Oracle Performance Cary Millsap and
  Jeff Holt