CPU使用率极高
环境:ASE 12.5系统平台:Compaq
业务系统使用很慢,在高峰时执行了一个IO,BLOCK,CPU查询计划.输出日志如下:
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
spid type object_name dbname cmd program
------ ------ --------------- ---------- ---------------- ----------------
(0 rows affected)
(return status = 0)
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
spid cmd program_name phy_io hostname phy_io_add
------ ---------------- ---------------- ----------- ---------- -----------
7 CHECKPOINT SLEEP 33761275 23
2590 SELECT 5 10
1456 SELECT 12 9
3718 AWAITING COMMAND 0 8
(4 rows affected)
------------------------
execute plan : 7
(1 row affected)
The specified spid value '7' applies to a server internal process, which does
not execute a query plan.
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
------------------------
execute plan : 2590
(1 row affected)
The query plan for spid '2590' is unavailable. Possibly the query has not
started or has finished executing.
SQL Text: WHERE A.PID=C.PID AND A.PNID=C.ID AND ( A.INFO_ID = B.ID ) AND
A.PID=E.PID AND A.PNID=E.ID AND A.PID=F.PID AND E.FID=F.ID ANDC.ACT_ID=G.ID
AND (B.OBJCLASS = D.OBJCLASS)AND ( C.STATUS>=0 )AND F.USER_ID=I.IDAND
B.ID NOT IN (SELECT INFO_ID FROM LW WHERE NEEDBACK=3)AND B.STATUS >= 0 AND
((A.USER_ID=2800 AND A.HANDLEWAY=0) OR (A.USER_ID IN (2800,1412,1633,1738,2324)
AND A.HANDLEWAY=2) ) ORDER BY A.RDATE DESC
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
------------------------
execute plan : 1456
(1 row affected)
QUERY PLAN FOR STATEMENT 1 (at line 1).
STEP 1
The type of query is INSERT.
The update mode is direct.
Worktable2 created for REFORMATTING.
FROM TABLE
G_ACTS
G
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
TO TABLE
Worktable2.
STEP 2
The type of query is INSERT.
The update mode is direct.
Worktable3 created for REFORMATTING.
FROM TABLE
G_OBJS
D
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
TO TABLE
Worktable3.
STEP 3
The type of query is INSERT.
The update mode is direct.
Worktable1 created, in allpages locking mode, for ORDER BY.
FROM TABLE
LW
J
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
FROM TABLE
G_INFOS
B
Nested iteration.
Index : IKEY_G_INFOS
Forward scan.
Positioning by key.
Keys are:
IDASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
FROM TABLE
G_INBOX
A
Nested iteration.
Index : IX_G_INBOX_INFO_ID
Forward scan.
Positioning by key.
Keys are:
INFO_IDASC
Using I/O Size 16 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
FROM TABLE
G_PNODES
C
Nested iteration.
Index : IKEY_G_PNODES
Forward scan.
Positioning by key.
Keys are:
PIDASC
IDASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
FROM TABLE
Worktable2.
Nested iteration.
Using Clustered Index.
Forward scan.
Positioning by key.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
FROM TABLE
Worktable3.
Nested iteration.
Using Clustered Index.
Forward scan.
Positioning by key.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
FROM TABLE
G_PROUTE
E
Nested iteration.
Index : IX_PID_ID
Forward scan.
Positioning by key.
Keys are:
PIDASC
IDASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
FROM TABLE
G_PNODES
F
Nested iteration.
Index : IKEY_G_PNODES
Forward scan.
Positioning by key.
Keys are:
PIDASC
IDASC
Using I/O Size 16 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
FROM TABLE
G_USERS
I
Nested iteration.
Index : IKEY_G_USERS
Forward scan.
Positioning by key.
Keys are:
IDASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf pages.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
TO TABLE
Worktable1.
STEP 4
The type of query is SELECT.
This step involves sorting.
FROM TABLE
Worktable1.
Using GETSORTED
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 16 Kbytes for data pages.
With MRU Buffer Replacement Strategy for data pages.
Total estimated I/O cost for statement 1 (at line 1): 3094488.
SQL Text: LW J WHERE A.PID=C.PID AND A.PNID=C.ID AND ( A.INFO_ID = B.ID ) AND
A.PID=E.PID AND A.PNID=E.ID AND A.PID=F.PID AND E.FID=F.ID ANDC.ACT_ID=G.ID
AND (B.OBJCLASS = D.OBJCLASS)AND ( C.STATUS>=0 )AND F.USER_ID=I.ID AND
B.ID=J.INFO_ID AND J.NEEDBACK=3AND B.STATUS >= 0 AND ((A.USER_ID=2374 AND
A.HANDLEWAY=0) OR (A.USER_ID IN (2374,2288,2302,2768,3004) AND A.HANDLEWAY=2) )
ORDER BY A.RDATE DESC
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
(return status = 0)
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
spid cmd program_name cpu hostname cpu_add
------ ---------------- ---------------- ----------- ---------- -----------
2128 SELECT 103 51
5936 AWAITING COMMAND 111 10
6742 AWAITING COMMAND 78 4
2524 FETCH CURSOR 51 4
2310 SELECT 15 3
5385 SELECT 0 3
3896 AWAITING COMMAND 197 -196
(7 rows affected)
------------------------
execute plan : 2128
(1 row affected)
QUERY PLAN FOR STATEMENT 3 (at line 9).
STEP 1
The type of query is SELECT.
FROM TABLE
WDZX.G_PNODES
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.
Total estimated I/O cost for statement 3 (at line 9): 289798.
SQL Text: ?
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
------------------------
execute plan : 5936
(1 row affected)
The query plan for spid '5936' is unavailable. Possibly the query has not
started or has finished executing.
SQL Text: namoCursor9940
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
------------------------
execute plan : 6742
(1 row affected)
The query plan for spid '6742' is unavailable. Possibly the query has not
started or has finished executing.
SQL Text: commit
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
(return status = 0)
页:
[1]