CPU使用率极高

donniejeck

环境: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 AND  C.ACT_ID=G.ID
AND (B.OBJCLASS = D.OBJCLASS)  AND ( C.STATUS>=0   )  AND F.USER_ID=I.ID  AND
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:
            ID  ASC
        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_ID  ASC
        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:
            PID  ASC
            ID  ASC
        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:
            PID  ASC
            ID  ASC
        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:
            PID  ASC
            ID  ASC
        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:
            ID  ASC
        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 AND  C.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=3  AND 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)