Statspack Data Calculation Sheet
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Statspack Data Calculation Sheet as PDF for free.
More details
- Words: 391
- Pages: 12
Statspack Data Calculation Sheet For Oracle 8i,9i,10g ,….
Developed By V.P. Mohammed Yacoob E-mail: [email protected]
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 --------XXXXXX XXXXXX XXXXXX XXXXXX XXXXXX XXXXXX XXXXXX XXXXXX XXXXXX
SNAP_ID ---------120 121 117 118 115 119 113 114 116
Date/Time ------------------27.03.2007:07:41:12 28.03.2007:06:40:30 04.12.2006:19:29:06 04.12.2006:19:31:06 04.12.2006:19:25:06 04.12.2006:19:33:06 04.12.2006:19:21:06 04.12.2006:19:23:06 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 -- Total Wait Time -- Physical Reads statistic
Graph
-- graph (Total Response/ idle wait Time ) Oracle DB- Total wait / Response Time Physical Read Statistics 45
90
40.5
80
36
70
31.5
60 50 40
Physical Reads
CPU Utilization
100
27 22.5 18 13.5
30 20 10 0 18:13:06
9 4.5 0 16
46.4
76.8
107.2
137.6
168
198.4
228.8
259.2
289.6
320
Memory (KB) 18:14:11
18:15:17
18:16:22
18:17:28
18:18:33
Time Estimate Buffer (KB) Total Wait Time
18:19:38
18:20:44
18:21:49
Physical Reads (KB)
Total Response Time
18:22:55
18:24:00
Physical Read Statistics Physical Read Statistics 45 40.5 Physical Reads
36 31.5 27 22.5 18 13.5 9 4.5 0 16
46.4
76.8
107.2
137.6
168
198.4
228.8
259.2
Memory (KB) Estimate Buffer (KB)
Physical Reads (KB)
289.6
320
-- 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)
Developed By V.P. Mohammed Yacoob E-mail: [email protected]
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 --------XXXXXX XXXXXX XXXXXX XXXXXX XXXXXX XXXXXX XXXXXX XXXXXX XXXXXX
SNAP_ID ---------120 121 117 118 115 119 113 114 116
Date/Time ------------------27.03.2007:07:41:12 28.03.2007:06:40:30 04.12.2006:19:29:06 04.12.2006:19:31:06 04.12.2006:19:25:06 04.12.2006:19:33:06 04.12.2006:19:21:06 04.12.2006:19:23:06 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 -- Total Wait Time -- Physical Reads statistic
Graph
-- graph (Total Response/ idle wait Time ) Oracle DB- Total wait / Response Time Physical Read Statistics 45
90
40.5
80
36
70
31.5
60 50 40
Physical Reads
CPU Utilization
100
27 22.5 18 13.5
30 20 10 0 18:13:06
9 4.5 0 16
46.4
76.8
107.2
137.6
168
198.4
228.8
259.2
289.6
320
Memory (KB) 18:14:11
18:15:17
18:16:22
18:17:28
18:18:33
Time Estimate Buffer (KB) Total Wait Time
18:19:38
18:20:44
18:21:49
Physical Reads (KB)
Total Response Time
18:22:55
18:24:00
Physical Read Statistics Physical Read Statistics 45 40.5 Physical Reads
36 31.5 27 22.5 18 13.5 9 4.5 0 16
46.4
76.8
107.2
137.6
168
198.4
228.8
259.2
Memory (KB) Estimate Buffer (KB)
Physical Reads (KB)
289.6
320
-- 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)
Related Documents
Statspack Data Calculation Sheet
November 2019 9
Data Sheet And Calculation
November 2019 22
Esp Calculation Data Sheet Final
June 2020 30
Statspack
July 2020 3
Calculation Sheet
November 2019 15