Statspack Data Calculation SheetFor

Oracle 8i,9i,10g ,….

Developed

By

V. P. Mohammed Yacoob.B.Tech

Software Engineer,

AppLabs Technologies, India

E-mail: yacoobit@yahoo.co.in

About Me

• Working as a Performance Engineer at AppLabs Technologies – India

• Worked on more than 3 Engineering process

About Statspack

• Oracle Database has an in build feature called Statspack, Which will give performance statistics of the database.

• Statspack will generate the report with the file name .lst extention

About Snapshot

• How to take snapshot

…… answer

1) Just before start of the test execution

2) Mid of the test execution

3) End of the test execution

How it will be ?

• NAME SNAP_ID Date/Time• --------- ---------- -------------------• XXXXXX 120 27.03.2007:07:41:12• XXXXXX 121 28.03.2007:06:40:30• XXXXXX 117 04.12.2006:19:29:06• XXXXXX 118 04.12.2006:19:31:06• XXXXXX 115 04.12.2006:19:25:06• XXXXXX 119 04.12.2006:19:33:06• XXXXXX 113 04.12.2006:19:21:06• XXXXXX 114 04.12.2006:19:23:06• XXXXXX 116 04.12.2006:19:27:06

Report

How to collect list file ?

The following command will collect the statistic report with reference to snap_id

@$ORACLE_HOME/rdbms/admin/spreport

Reports will generate as like sp_119_120.lst

Yacoob –SP - Data Calculation Report

Yacoob – SP – Data Calculation Report

-- Helpful for beginners

Features

-- Time conversion

-- Total Response Time Graph

-- Total Wait Time

-- Physical Reads statistic

-- graph (Total Response/ idle wait Time )

Oracle DB- Total wait / Response Time

0

10

20

30

40

50

60

70

80

90

100

18:13:06 18:14:11 18:15:17 18:16:22 18:17:28 18:18:33 18:19:38 18:20:44 18:21:49 18:22:55 18:24:00

Time (hr:min:ss)

CP

U U

tilizati

on

Total Wait Time Total Response Time

Physical Read Statistics

Physical Read Statistics

0

4.5

9

13.5

18

22.5

27

31.5

36

40.5

45

16 46.4 76.8 107.2 137.6 168 198.4 228.8 259.2 289.6 320

Memory (KB)

Physic

al R

eads

Estimate Buffer (KB) Physical Reads (KB)

-- Response Time

• It can describe as Service Time + Wait Time• Where Service Time = Time spent on the CPU.• Wait Time = The sum of time spent on Wait

Event • Note:

– Service time is compressed of time spent on the CPU for parsing, Recursive CPU usage (for Recursive PL/SQL and Recursive SQL) and CPU used for execution of SQL statements (CPU other)