| STATSPACK [message #287530] |
Wed, 12 December 2007 09:15  |
lotusdeva
Messages: 201
Registered: March 2005
|
Senior Member |
|
|
|
Hi, I have a question about STATSPACK. This is the first time that I used it (one of our applicatons is having performance issues). We installed it, ran it (for an interval of 2 hours) and fed it into Statspack Analyzer tool. Would 2 hours of info be sufficient to figure out what the problem is? I read that the numbers obtained via STATSPACK are most useful when there is a baseline for comparsion. I am not sure what baseline is in our situation (when the system was functioning efficiantly?)
|
|
|
|
|
|
|
|
| Re: STATSPACK [message #287544 is a reply to message #287534] |
Wed, 12 December 2007 10:36  |
lotusdeva
Messages: 201
Registered: March 2005
|
Senior Member |
|
|
1. We are on Oracle 9.2.0.6.0 version.
2. We took the interval when we had the most users and the app was slow (so I guess 2 hours is more then enough  )
3. SGA configuration is as follows:
SQL> show SGA;
Total System Global Area bytes
Fixed Size bytes
Variable Size bytes
Database Buffers bytes
Redo Buffers bytes
4. Here is a portion of the STATSPACK:
DB Name DB Id Instance Inst Num Release Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
mydb DEV3 1 9.2.0.6.0 NO sunMM
Snap Id Snap Time Sessions Curs/Sess Comment
--------- ------------------ -------- --------- -------------------
Begin Snap: 962 11-Dec-07 11:00:01 10 91.3
End Snap: 974 11-Dec-07 13:00:00 11 90.6
Elapsed: 119.98 (mins)
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 125M Std Block Size: 8K
Shared Pool Size: 1,920M Log Buffer: 160K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 3,626.11 5,098.51
Logical reads: 4,887.86 6,872.60
Block changes: 10.20 14.33
Physical reads: 118.00 165.92
Physical writes: 62.44 87.79
User calls: 39.33 55.30
Parses: 10.33 14.52
Hard parses: 0.84 1.18
Sorts: 4.62 6.50
Logons: 0.99 1.39
Executes: 10.70 15.05
Transactions: 0.71
% Blocks changed per Read: 0.21 Recursive Call %: 43.09
Rollback per transaction %: 1.97 Rows per Sort: 963.15
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 99.98
Buffer Hit %: 98.69 In-memory Sort %: 99.47
Library Hit %: 96.59 Soft Parse %: 91.89
Execute to Parse %: 3.54 Latch Hit %: 99.99
Parse CPU to Parse Elapsd %: 97.64 % Non-Parse CPU: 97.36
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 57.69 63.80
% SQL with executions>1: 29.42 29.73
% Memory for SQL w/exec>1: 27.61 27.63
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time 1,425 66.98
direct path write 30,888 240 11.29
control file parallel write 2,410 149 6.99
db file sequential read 50,837 136 6.38
direct path read 39,587 63 2.94
-------------------------------------------------------------
I can see that the biggest issue is CPU usage here. Would u agree?
|
|
|
|
| Re: STATSPACK [message #287547 is a reply to message #287538] |
Wed, 12 December 2007 10:39 |
lotusdeva
Messages: 201
Registered: March 2005
|
Senior Member |
|
|
|
true. but we fed statspack into statspack analyzer as it provided a nice explanation of where the problem seems to be. I am not sure about some parts of it though..
|
|
|
|
|
|
|
|
|
|
|
|
| Re: STATSPACK [message #287565 is a reply to message #287530] |
Wed, 12 December 2007 12:45 |
lotusdeva
Messages: 201
Registered: March 2005
|
Senior Member |
|
|
Actually, I asked a question about the use of STATSPACK in my first post. Here is my original post:
Hi, I have a question about STATSPACK. This is the first time that I used it (one of our applications is having performance issues). We installed it, ran it (for an interval of 2 hours) and fed it into Statspack Analyzer tool. Would 2 hours of info be sufficient to figure out what the problem is? I read that the numbers obtained via STATSPACK are most useful when there is a baseline for comparison. I am not sure what baseline is in our situation (when the system was functioning efficiently?)
I said that our application is having a performance issue, I never used STATSPACK before (get all my stats via TPKROF/Autotrace usually) and that I had some questions about the way it is used (see above). Someone asked me to post some stats, which I did. I really don’t see what is so terrible about my post? I don’t use Orafaq very often (although I been a member since 2005), so if I made mistakes posting/asking, I did so without knowing.
|
|
|
|
| Re: STATSPACK [message #287601 is a reply to message #287530] |
Wed, 12 December 2007 21:03 |
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
|
Senior Member |
|
|
- You should use format code/code or quote/quote to make your post more luminous.
- Some thing you should share, because if you were me, you wouldn't understand all of my question, unless we make it clearly.
- In the Statspack, there are many thing to see, but only queries it marks in, you should use TKPROF or Explain Table or TraceOnly to view statistic.
|
|
|
|
|
|
| Re: STATSPACK [message #287823 is a reply to message #287616] |
Thu, 13 December 2007 12:29 |
lotusdeva
Messages: 201
Registered: March 2005
|
Senior Member |
|
|
Sorry, here it is:
1. SGA setting is as follows:
Total System Global Area 2480936320 bytes
Fixed Size 734592 bytes
Variable Size 2348810240 bytes
Database Buffers 131072000 bytes
Redo Buffers 319488 bytes
2. We are running statspack every 10 minutes. However, the report contains data for 2 hours because that is the time when we have been running load test to check how efficiently the app will respond. We just ran another load test and ran the report for an hour (while the load test was going on). Here is a sample of the report, which I did my best to format this time
3. Here is my new report:
DB Name DB Id Instance Inst Num Release Cluster Host
------------ ---------- ------------ -------- ----------- ------- -------------------
Db00 0000 Db00 1 9.2.0.6.0 NO sun11
Snap ID Snap Time Sessions Curs/Sess Comment
------- ------------------ --------
Begin Snap: 842 13-Dec-07 11:00:01 9 89.2
End Snap: 848 13-Dec-07 12:00:00 9 89.2
Elapsed: 59.98 (mins)
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 125M Std Block Size: 8K
Shared Pool Size: 1,920M Log Buffer: 160K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 4,078.54 3,362.04
Logical reads: 6,327.29 5,215.74
Block changes: 13.86 11.42
Physical reads: 326.20 268.89
Physical writes: 28.87 23.79
User calls: 44.09 36.34
Parses: 11.56 9.53
Hard parses: 1.39 1.14
Sorts: 4.53 3.73
Logons: 1.01 0.83
Executes: 9.38 7.73
Transactions: 1.21
% Blocks changed per Read: 0.22 Recursive Call %: 37.42
Rollback per transaction %: 0.00 Rows per Sort: 388.40
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.84 Redo NoWait %: 99.98
Buffer Hit %: 95.24 In-memory Sort %: 99.77
Library Hit %: 94.49 Soft Parse %: 87.98
Execute to Parse %: -23.24 Latch Hit %: 99.85
Parse CPU/Parse Elapsd %: 95.06 % Non-Parse CPU: 96.38
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 51.86 57.42
% SQL with executions>1: 31.28 29.50
% Memory for SQL w/exec>1: 29.73 27.71
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~
%Total
Event Waits Time(s) Ela Time
-------------------------------------------- ------------
CPU time 824 56.53
db file sequential read 526,307 364 24.94
db file scattered read 72,075 69 4.75
control file para write 1,211 68 4.69
direct path write 7,229 58 4.00
It looks like the biggest bottelneck is CPU (I am checking what SQL is most SQL intensive). By the values of Execute to Parse and Soft Parse I can see that sql is not being shared efficiently (no bind variables probably). It also looks like % SQL with executions>1 value is low = meaning that shared pool is not being utilized efficiently. Do you experts have any other suggestions? Thank you!
|
|
|
|
|
|
| Re: STATSPACK [message #288184 is a reply to message #287530] |
Sat, 15 December 2007 09:49 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
lotusdeva,
Congratulations on using <code tags> to make your post readable.
Now my eyes don't hurt & strain when I try to understand the information in them.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
I thought I actually formatted my post, no? 