Home » RDBMS Server » Performance Tuning » STATSPACK
STATSPACK [message #287530] Wed, 12 December 2007 09:15 Go to next message
lotusdeva
Messages: 198
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 #287534 is a reply to message #287530] Wed, 12 December 2007 09:37 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

1. you forget to post your oracle version ?
2. interval between two snapshot is not more than 15 to 30 minutes.
3. if 10g then generate statspack report during performance problem time.

statspack report post here with your sga configuration.

Re: STATSPACK [message #287538 is a reply to message #287530] Wed, 12 December 2007 09:44 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
If you've never used STATSPACK before I seriously doubt it will provide you useful information to solve your performance problem.

SQL_TRACE & TKPROF provide (me) much more focused tuning information.
Re: STATSPACK [message #287544 is a reply to message #287534] Wed, 12 December 2007 10:36 Go to previous messageGo to next message
lotusdeva
Messages: 198
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 Smile )

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 Go to previous messageGo to next message
lotusdeva
Messages: 198
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 #287549 is a reply to message #287530] Wed, 12 December 2007 11:09 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
lotusdeva
>Messages: 114
How sad that after more than 100 post you still choose to NOT format your posts so they can be easily read.

You're On Your Own (YOYO)!

Re: STATSPACK [message #287551 is a reply to message #287549] Wed, 12 December 2007 11:13 Go to previous messageGo to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
Embarassed I thought I actually formatted my post, no?
Re: STATSPACK [message #287558 is a reply to message #287551] Wed, 12 December 2007 11:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can use the "Preview Message" button to see what your message looks before clicking on "Submit Reply".
In OraFAQ Forum Guide there is a section "How to format your post?".

Regards
Michel

[Updated on: Wed, 12 December 2007 11:51]

Report message to a moderator

Re: STATSPACK [message #287559 is a reply to message #287530] Wed, 12 December 2007 11:54 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>we fed statspack into statspack analyzer as it provided a nice explanation of where the problem seems to be
So proceed to fix the problem (what ever it may be since you chose not to share it with us).
Re: STATSPACK [message #287565 is a reply to message #287530] Wed, 12 December 2007 12:45 Go to previous messageGo to next message
lotusdeva
Messages: 198
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 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
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 #287616 is a reply to message #287544] Wed, 12 December 2007 23:22 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Again i said generate statspack with 30 minutes interval.

your entire database is slow or some report ?

I didn't see your sga configuration.

[Updated on: Wed, 12 December 2007 23:23]

Report message to a moderator

Re: STATSPACK [message #287823 is a reply to message #287616] Thu, 13 December 2007 12:29 Go to previous messageGo to next message
lotusdeva
Messages: 198
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 Smile

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 #288158 is a reply to message #287823] Sat, 15 December 2007 03:07 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

1. According statspack report "db cache size" is small, so increase and check performance.

You can use Buffer cache advisor for how much increase buffer cache size.

2. CPU is not wait event.

3. Soft Parse %: 87.98
Yes, your sql is not shared, review application and use bind variable.

wait for expert comments also.
Re: STATSPACK [message #288184 is a reply to message #287530] Sat, 15 December 2007 09:49 Go to previous message
BlackSwan
Messages: 25050
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.
Previous Topic: Oracle Memory Leakage
Next Topic: Some questions on SQL tuning
Goto Forum:
  


Current Time: Sun Dec 11 04:34:22 CST 2016

Total time taken to generate the page: 0.12691 seconds