Topic: Database Administration >> What do you see in statspack?
|
Disclaimer: The purpose of all dbapool forums including OCP and Other Oracle Certification forums is to help each other with specific issues but not to share dump and copyrighted exam content, materials or intellectual property.
You may review the entire Oracle Certification Program Candidate Agreement online Here.
|
|
|
|
| Title: What do you see in statspack? |
| vikhardba |
Posted: Dec 13, 2007 03:50:48 PM |
Total Post: 84
Joined: Apr, 2007
|
Hi to One and All,
After taking the statspack report and saving the statspack file. What do you do with it and what do you see in it for performance?
Familiar with taking statspack report. Currently i am using Windows 2000 and Oracle 10g is installed in it?
How effective it is?
Thanks in advance. |
|
|
 Murtuja |
| Posted: Dec 13, 2007 11:41:33 PM |  |
|
Total Post: 814
Joined: Jan, 2006
|
Hi,
You can understand all the components of statspack report by reading many articles.and be sure that it will take lot of time to master it.
You can start from here
www.akadia.com/services/ora_statspack_survival_guide.html
And you can upload your statspack report to
http://www.dbapool.com/analyzer/
Get free Database Tuning recommendations from instantly from this online analyzer. Just Upload your AWR or Statspack report and get recommendations. It is fast, free and easy.
|
|
|
|
|
| Vinod |
| Posted: Dec 14, 2007 05:56:19 AM | |
|
Total Post: 54
Joined: Oct, 2007
|
Hi ,
As a quick overview i would like to highlight few points listed below which you have to check .In 10g you can make use of AWR ,ADDR and ASH reports .
9i statspack report -Things to remember
- Top 5 wait Events section
Top 5 Wait Events
================== Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
db file sequential read 12,131,221 173,910 58.04
db file scattered read 93,310 86,884 29.00
log file sync 18,629 9,033 3.01
log file parallel write 18,559 8,449 2.82
buffer busy waits 304,461 7,958 2.66
Cluster Statistics
====================
Global Lock Statistics
----------------------
Ave global lock get time (ms): 0.3
Ave global lock convert time (ms): 0.0
Ratio of global lock gets vs global lock releases: 1.0
Global cache statistics
-----------------------
Global cache hit %: 0.3
Ave global cache get time (ms): 1.7
Ave global cache convert time (ms): 3.1
.
.
.
.
===================================
Global Cache Service - Workload Characteristics
-----------------------------------------------
Ave global cache get time (ms): 4.6
Ave global cache convert time (ms): 20.2
Ave build time for CR block (ms): 0.0
Ave flush time for CR block (ms): 0.6
Ave send time for CR block (ms): 0.1
Ave time to process CR block request (ms): 0.7
Ave receive time for CR block (ms): 0.9
.
.
.
- Background Wait Events:
==============================
Event Waits Timeouts Time (cs) (ms) /txn
---------------------------- ------------ ---------- ----------- ------ ------
latch free 88,578 32,522 18,341 2 ######
enqueue 319 230 5,932 186 14.5
row cache lock 4,941 0 2,307 5 224.6
control file parallel write 1,172 0 332 3 53.3
db file parallel write 176 0 67 4 8.0
log file parallel write 315 0 65 2 14.3
db file scattered read 137 0 62 5 6.2
LGWR wait for redo copy 66 10 47 7 3.0
TOP SQL ordered by Physical Reads:
======================================
SQL ordered by Reads for DB: PHS2 Instance: phs2 Snaps: 100 -104
-> End Disk Reads Threshold: 1000
Physical Reads Executions Reads per Exec % Total Hash Value
--------------- ------------ -------------- ------- ------------
98,401 16 6,150.1 14.2 3004232054
SELECT C0 C0 FROM (SELECT C0 C0 FROM (SELECT /*+ NO_EXPAND ROWID
(A1) */ A1."PID" C0 FROM "PHASE"."P0201F00_PLAT_MCOP_TB" PX_GRAN
ULE(0, BLOCK_RANGE, DYNAMIC) A1) UNION ALL SELECT C0 C0 FROM (S
ELECT /*+ NO_EXPAND ROWID(A2) */ A2."PID" C0 FROM "PHASE"."P0201
F00_UCS_MCOP_TB" PX_GRANULE(1, BLOCK_RANGE, DYNAMIC) A2) UNION
PGA Memory Statistics
=========================
Statistic Begin (M) End (M) % Diff
----------------------------------- ---------------- ---------------- ----------
maximum PGA allocated 10.405 10.405 .00
total PGA allocated 7.201 7.285 1.17
total PGA inuse 6.681 6.684 .04
Thanks & Regards,
Vinod
|
|
|
|
|
| vikhardba |
| Posted: Dec 15, 2007 03:36:44 PM | |
|
Total Post: 84
Joined: Apr, 2007
|
Sorry for the delay in response. Thanks to both of you for providing information about statspack report.
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
|
|
|
|
Forum Rules & Description
Who Can Read The Forum? Any registered user or guest
Who Can Post New Topics? Any registered user
Who Can Post Replies? Any registered user
|
| |
Get FREE Magazines
|
Top 10 Forum User
|
| Murtuja Khokhar | 814 |
| Mohammed Taj | 694 |
| Jayanta Sur | 479 |
| Vigyan Kaushik | 386 |
| positive fanatic | 361 |
| Gitesh Trivedi | 322 |
| Gopu Gopi | 239 |
| neeraj sharma | 228 |
| Ramesh Jois | 226 |
| snehalatha p | 169 |
|
|
