- 论坛徽章:
- 0
|
附上正常和故障时候的sp_sysmon输出，请大虾指点一下！
谢谢，谢谢啦
故障时的sysmon输出：
===============================================================================
Sybase Adaptive Server Enterprise System Performance Report
===============================================================================
Server Version: Adaptive Server Enterprise/12.5.3/EBF 12455 ESD#2/P/Sun_
Server Name: CSCS
Run Date: Aug 08, 2005
Sampling Started at: Aug 08, 2005 17:40:07
Sampling Ended at: Aug 08, 2005 17:45:13
Sample Interval: 00:05:06
Sample Mode: Reset Counters
===============================================================================
Kernel Utilization
------------------
Your Runnable Process Search Count is set to 1200
and I/O Polling Process Count is set to 10
Engine Busy Utilization CPU Busy I/O Busy Idle
------------------------ -------- -------- --------
Engine 0 100.0 % 0.0 % 0.0 %
Engine 1 100.0 % 0.0 % 0.0 %
Engine 2 100.0 % 0.0 % 0.0 %
Engine 3 100.0 % 0.0 % 0.0 %
Engine 4 100.0 % 0.0 % 0.0 %
Engine 5 100.0 % 0.0 % 0.0 %
Engine 6 100.0 % 0.0 % 0.0 %
------------------------ -------- -------- --------
Summary Total 700.0 % 0.0 % 0.0 %
Average 100.0 % 0.0 % 0.0 %
CPU Yields by Engine per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Total CPU Yields 0.0 0.0 0 n/a
Network Checks
Non-Blocking 37.6 1.7 11517 100.0 %
Blocking 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total Network I/O Checks 37.6 1.7 11517
Avg Net I/Os per Check n/a n/a 7.75532 n/a
Disk I/O Checks
Total Disk I/O Checks 52.5 2.4 16079 n/a
Checks Returning I/O 15.1 0.7 4614 28.7 %
Avg Disk I/Os Returned n/a n/a 3.40052 n/a
Tuning Recommendations for Kernel Utilization
---------------------------------------------
- Consider bringing more engines online
===============================================================================
Worker Process Management
-------------------------
per sec per xact count % of total
------------ ------------ ---------- ----------
Worker Process Requests
Total Requests 0.0 0.0 0 n/a
Worker Process Usage
Total Used 0.0 0.0 0 n/a
Max Ever Used During Sample 0.0 0.0 0 n/a
Memory Requests for Worker Processes
Total Requests 0.0 0.0 0 n/a
Tuning Recommendations for Worker Processes
-------------------------------------------
- Consider decreasing the 'number of worker processes'
configuration parameter.
===============================================================================
Parallel Query Management
-------------------------
Parallel Query Usage per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Total Parallel Queries 0.0 0.0 0 n/a
Merge Lock Requests per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Total # of Requests 0.0 0.0 0 n/a
Sort Buffer Waits per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Total # of Waits 0.0 0.0 0 n/a
===============================================================================
Task Management per sec per xact count % of total
--------------------------- ------------ ------------ ---------- ----------
Connections Opened 1.9 0.1 581 n/a
Task Context Switches by Engine
Engine 0 51.7 2.4 15818 15.0 %
Engine 1 47.2 2.2 14440 13.7 %
Engine 2 48.8 2.2 14922 14.1 %
Engine 3 48.9 2.2 14958 14.2 %
Engine 4 51.8 2.4 15847 15.0 %
Engine 5 46.1 2.1 14110 13.4 %
Engine 6 50.7 2.3 15527 14.7 %
------------------------- ------------ ------------ ----------
Total Task Switches: 345.2 15.8 105622
Task Context Switches Due To:
Voluntary Yields 57.7 2.6 17655 16.7 %
Cache Search Misses 0.3 0.0 100 0.1 %
System Disk Writes 0.2 0.0 56 0.1 %
I/O Pacing 12.0 0.6 3687 3.5 %
Logical Lock Contention 0.8 0.0 233 0.2 %
Address Lock Contention 0.0 0.0 0 0.0 %
Latch Contention 0.0 0.0 2 0.0 %
Log Semaphore Contention 1.4 0.1 430 0.4 %
PLC Lock Contention 0.1 0.0 17 0.0 %
Group Commit Sleeps 0.4 0.0 135 0.1 %
Last Log Page Writes 1.0 0.0 292 0.3 %
Modify Conflicts 0.3 0.0 103 0.1 %
I/O Device Contention 0.0 0.0 0 0.0 %
Network Packet Received 94.9 4.3 29039 27.5 %
Network Packet Sent 111.2 5.1 34023 32.2 %
Other Causes 64.9 3.0 19850 18.8 %
===============================================================================
Application Management
----------------------
Application Statistics Summary (All Applications)
-------------------------------------------------
Priority Changes per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
To High Priority 2.4 0.1 724 34.2 %
To Medium Priority 4.4 0.2 1349 63.8 %
To Low Priority 0.1 0.0 43 2.0 %
------------------------- ------------ ------------ ----------
Total Priority Changes 6.9 0.3 2116
Allotted Slices Exhausted per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
High Priority 0.0 0.0 0 0.0 %
Medium Priority 32.3 1.5 9888 100.0 %
Low Priority 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total Slices Exhausted 32.3 1.5 9888
Skipped Tasks By Engine per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Total Engine Skips 0.0 0.0 0 n/a
Engine Scope Changes 0.0 0.0 0 n/a
===============================================================================
ESP Management per sec per xact count % of total
--------------------------- ------------ ------------ ---------- ----------
ESP Requests 0.0 0.0 0 n/a
===============================================================================
Housekeeper Task Activity
-------------------------
per sec per xact count % of total
------------ ------------ ----------
Buffer Cache Washes
Clean 11.4 0.5 3496 92.9 %
Dirty 0.9 0.0 267 7.1 %
------------ ------------ ----------
Total Washes 12.3 0.6 3763
Garbage Collections 0.5 0.0 147 n/a
Pages Processed in GC 0.0 0.0 0 n/a
Statistics Updates 0.0 0.0 12 n/a
===============================================================================
Monitor Access to Executing SQL
-------------------------------
per sec per xact count % of total
------------ ------------ ---------- ----------
Waits on Execution Plans 0.0 0.0 0 n/a
Number of SQL Text Overflows 5.5 0.3 1691 n/a
Maximum SQL Text Requested n/a n/a 16810 n/a
(since beginning of sample)
Tuning Recommendations for Monitor Access to Executing SQL
----------------------------------------------------------
- Consider increasing the 'max SQL text monitored' parameter
to at least 8917 (i.e., half way from its current value
to Maximum SQL Text Requested).
===============================================================================
Transaction Profile
-------------------
Transaction Summary per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Committed Xacts 21.9 n/a 6703 n/a
Transaction Detail per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Inserts
APL Heap Table 6861.5 313.2 2099632 56.7 %
APL Clustered Table 5226.3 238.6 1599236 43.2 %
Data Only Lock Table 20.5 0.9 6275 0.2 %
------------------------- ------------ ------------ ---------- ----------
Total Rows Inserted 12108.3 552.8 3705143 100.0 %
Updates
APL Deferred 0.0 0.0 0 0.0 %
APL Direct In-place 0.0 0.0 0 0.0 %
APL Direct Cheap 0.0 0.0 0 0.0 %
APL Direct Expensive 0.0 0.0 0 0.0 %
DOL Deferred 0.1 0.0 20 5.6 %
DOL Direct 1.1 0.1 337 94.4 %
------------------------- ------------ ------------ ---------- ----------
Total Rows Updated 1.2 0.1 357 0.0 %
Data Only Locked Updates
DOL Replace 0.9 0.0 288 80.7 %
DOL Shrink 0.1 0.0 36 10.1 %
DOL Cheap Expand 0.0 0.0 15 4.2 %
DOL Expensive Expand 0.0 0.0 15 4.2 %
DOL Expand & Forward 0.0 0.0 3 0.8 %
DOL Fwd Row Returned 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total DOL Rows Updated 1.2 0.1 357 0.0 %
Deletes
APL Deferred 1.4 0.1 435 65.0 %
APL Direct 0.3 0.0 78 11.7 %
DOL 0.5 0.0 156 23.3 %
------------------------- ------------ ------------ ---------- ----------
Total Rows Deleted 2.2 0.1 669 0.0 %
========================= ============ ============ ==========
Total Rows Affected 12111.7 552.9 3706169
===============================================================================
Transaction Management
----------------------
ULC Flushes to Xact Log per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
by Full ULC 0.0 0.0 9 0.1 %
by End Transaction 2.4 0.1 747 8.1 %
by Change of Database 0.0 0.0 15 0.2 %
by Single Log Record 13.5 0.6 4142 45.0 %
by Unpin 12.2 0.6 3735 40.6 %
by Other 1.8 0.1 548 6.0 %
------------------------- ------------ ------------ ----------
Total ULC Flushes 30.1 1.4 9196
ULC Log Records 106.9 4.9 32719 n/a
Max ULC Size During Sample n/a n/a 8192 n/a
ULC Semaphore Requests
Granted 261.1 11.9 79904 100.0 %
Waited 0.1 0.0 17 0.0 %
------------------------- ------------ ------------ ----------
Total ULC Semaphore Req 261.2 11.9 79921
Log Semaphore Requests
Granted 36.0 1.6 11011 96.2 %
Waited 1.4 0.1 430 3.8 %
------------------------- ------------ ------------ ----------
Total Log Semaphore Req 37.4 1.7 11441
Transaction Log Writes 2.3 0.1 707 n/a
Transaction Log Alloc 1.4 0.1 414 n/a
Avg # Writes per Log Page n/a n/a 1.70773 n/a
Tuning Recommendations for Transaction Management
-------------------------------------------------
- Consider decreasing the 'user log cache size'
configuration parameter if it is greater than the
logical database page size.
===============================================================================
Index Management
----------------
Nonclustered Maintenance per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Ins/Upd Requiring Maint 0.3 0.0 82 n/a
# of NC Ndx Maint 0.3 0.0 82 n/a
Avg NC Ndx Maint / Op n/a n/a 1.00000 n/a
Deletes Requiring Maint 0.3 0.0 77 n/a
# of NC Ndx Maint 0.3 0.0 77 n/a
Avg NC Ndx Maint / Op n/a n/a 1.00000 n/a
RID Upd from Clust Split 0.0 0.0 0 n/a
# of NC Ndx Maint 0.0 0.0 0 n/a
Upd/Del DOL Req Maint 1.7 0.1 513 n/a
# of DOL Ndx Maint 0.7 0.0 225 n/a
Avg DOL Ndx Maint / Op n/a n/a 0.43860 n/a
Page Splits 6.3 0.3 1930 n/a
Retries 0.0 0.0 0 0.0 %
Deadlocks 0.0 0.0 0 0.0 %
Add Index Level 0.0 0.0 0 0.0 %
Page Shrinks 0.0 0.0 0 n/a
Index Scans per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Ascending Scans 4712.0 215.1 1441884 19.2 %
DOL Ascending Scans 19834.6 905.5 6069377 80.8 %
Descending Scans 0.6 0.0 189 0.0 %
DOL Descending Scans 0.1 0.0 23 0.0 %
------------ ------------ ----------
Total Scans 24547.3 1120.6 7511473
===============================================================================
Metadata Cache Management
-------------------------
Metadata Cache Summary per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Open Object Usage
Active n/a n/a 6859 n/a
Max Ever Used Since Boot n/a n/a 6948 n/a
Free n/a n/a 20000 n/a
Reuse Requests
Succeeded n/a n/a 0 n/a
Failed n/a n/a 0 n/a
Open Index Usage
Active n/a n/a 4291 n/a
Max Ever Used Since Boot n/a n/a 4307 n/a
Free n/a n/a 10000 n/a
Reuse Requests
Succeeded n/a n/a 0 n/a
Failed n/a n/a 0 n/a
Open Database Usage
Active n/a n/a 8 n/a
Max Ever Used Since Boot n/a n/a 8 n/a
Free n/a n/a 12 n/a
Reuse Requests
Succeeded n/a n/a 0 n/a
Failed n/a n/a 0 n/a
Object Manager Spinlock Contention n/a n/a n/a 0.0 %
Object Spinlock Contention n/a n/a n/a 0.0 %
Index Spinlock Contention n/a n/a n/a 0.0 %
Hash Spinlock Contention n/a n/a n/a 0.0 %
===============================================================================
Lock Management
---------------
Lock Summary per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Total Lock Requests 22368.8 1021.2 6844847 n/a
Avg Lock Contention 0.8 0.0 233 0.0 %
Deadlock Percentage 0.0 0.0 0 0.0 %
Lock Detail per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Table Lock Hashtable
Lookups 662.7 30.3 202777 n/a
Avg Chain Length n/a n/a 1.09853 n/a
Spinlock Contention n/a n/a n/a 0.0 %
Exclusive Table
Granted 3.8 0.2 1154 91.3 %
Waited 0.4 0.0 110 8.7 %
------------------------- ------------ ------------ ---------- ----------
Total EX-Table Requests 4.1 0.2 1264 0.0 %
Shared Table
Granted 0.3 0.0 86 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total SH-Table Requests 0.3 0.0 86 0.0 %
Exclusive Intent
Granted 21.1 1.0 6469 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total EX-Intent Requests 21.1 1.0 6469 0.1 %
Shared Intent
Granted 629.5 28.7 192624 100.0 %
Waited 0.3 0.0 80 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total SH-Intent Requests 629.8 28.7 192704 2.8 %
Page & Row Lock HashTable
Lookups 7993.4 364.9 2445986 n/a
Avg Chain Length n/a n/a 0.01232 n/a
Spinlock Contention n/a n/a n/a 0.0 %
Exclusive Page
Granted 2.1 0.1 636 99.8 %
Waited 0.0 0.0 1 0.2 %
------------------------- ------------ ------------ ---------- ----------
Total EX-Page Requests 2.1 0.1 637 0.0 %
Update Page
Granted 1.5 0.1 461 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total UP-Page Requests 1.5 0.1 461 0.0 %
Shared Page
Granted 5058.3 230.9 1547846 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total SH-Page Requests 5058.3 230.9 1547846 22.6 %
Exclusive Row
Granted 21.4 1.0 6542 99.7 %
Waited 0.1 0.0 19 0.3 %
------------------------- ------------ ------------ ---------- ----------
Total EX-Row Requests 21.4 1.0 6561 0.1 %
Update Row
Granted 0.9 0.0 264 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total UP-Row Requests 0.9 0.0 264 0.0 %
Shared Row
Granted 2894.4 132.1 885688 100.0 %
Waited 0.1 0.0 23 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total SH-Row Requests 2894.5 132.1 885711 12.9 %
Next-Key
Total Next-Key Requests 0.0 0.0 0 n/a
Address Lock Hashtable
Lookups 13734.8 627.0 4202844 n/a
Avg Chain Length n/a n/a 0.00123 n/a
Spinlock Contention n/a n/a n/a 0.0 %
Exclusive Address
Granted 863.2 39.4 264148 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total EX-Address Requests 863.2 39.4 264148 3.9 %
Shared Address
Granted 12871.6 587.6 3938696 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total SH-Address Requests 12871.6 587.6 3938696 57.5 %
Last Page Locks on Heaps
Granted 6861.5 313.2 2099632 100.0 %
Waited 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ---------- ----------
Total Last Pg Locks 6861.5 313.2 2099632 100.0 %
Deadlocks by Lock Type per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Total Deadlocks 0.0 0.0 0 n/a
Deadlock Detection
Deadlock Searches 0.2 0.0 48 n/a
Searches Skipped 0.0 0.0 0 0.0 %
Avg Deadlocks per Search n/a n/a 0.00000 n/a
Lock Promotions
Total Lock Promotions 0.0 0.0 0 n/a
Lock Timeouts by Lock Type per sec per xact count % of total
------------------------- ------------ ------------ ---------- ----------
Total Timeouts 0.0 0.0 0 n/a
Tuning Recommendations for Lock Management
------------------------------------------
- Consider increasing the 'deadlock checking period' parameter
by 50 ms.
===============================================================================
Data Cache Management
---------------------
Cache Statistics Summary (All Caches)
-------------------------------------
per sec per xact count % of total
------------ ------------ ---------- ----------
Cache Search Summary
Total Cache Hits 142302.9 6496.3 43544682 100.0 %
Total Cache Misses 40.7 1.9 12465 0.0 %
------------------------- ------------ ------------ ----------
Total Cache Searches 142343.6 6498.2 43557147
Cache Turnover
Buffers Grabbed 9285.3 423.9 2841302 n/a
Buffers Grabbed Dirty 0.0 0.0 0 0.0 %
Cache Strategy Summary
Cached (LRU) Buffers 139640.2 6374.7 42729913 100.0 %
Discarded (MRU) Buffers 0.0 0.0 3 0.0 %
Large I/O Usage
Large I/Os Performed 6.1 0.3 1870 100.0 %
Large I/Os Denied due to
Pool < Prefetch Size 0.0 0.0 0 0.0 %
------------------------- ------------ ------------ ----------
Total Large I/O Requests 6.1 0.3 1870
Large I/O Effectiveness
Pages by Lrg I/O Cached 48.9 2.2 14960 n/a
Pages by Lrg I/O Used 0.0 0.0 0 0.0 %
Asynchronous Prefetch Activity
APFs Issued 0.0 0.0 1 0.0 %
APFs Denied Due To
APF I/O Overloads 0.0 0.0 0 0.0 %
APF Limit Overloads 0.0 0.0 0 0.0 %
APF Reused Overloads 0.0 0.0 0 0.0 %
APF Buffers Found in Cache
With Spinlock Held 5.2 0.2 1588 0.0 %
W/o Spinlock Held 64418.1 2940.8 19711947 100.0 %
------------------------- ------------ ------------ ----------
Total APFs Requested 64423.3 2941.0 19713536
Other Asynchronous Prefetch Statistics
APFs Used 0.0 0.0 1 n/a
APF Waits for I/O 0.0 0.0 1 n/a
APF Discards 0.0   |
|
免费注册
发表于 2005-08-10 12:32
