(转)如何诊断和解决CPU高度消耗(100%)的数据库问题

                        $ ps -ef|grep 20521
                        oracle 20909 20875  0 10:50:53 pts/10   0:00 grep 20521
                        oracle 20521     1 47 10:43:59 ?        6:45 oraclejshs (LOCAL=NO)
                        $ ps -ef|grep 20845
                        oracle 20845     1 44 10:50:00 ?        0:55 oraclejshs (LOCAL=NO)
                        oracle 20918 20875  0 10:50:59 pts/10   0:00 grep 20845

                        SELECT   /*+ ORDERED */
                        sql_text
                        FROM v$sqltext a
                        WHERE (a.hash_value, a.address) IN (
                        SELECT DECODE (sql_hash_value,
                        0, prev_hash_value,
                        sql_hash_value
                        ),
                        DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)
                        FROM v$session b
                        WHERE b.paddr = (SELECT addr
                        FROM v$process c
                        WHERE c.spid = '&pid'))
                        ORDER BY piece ASC
                        /

                        $ sqlplus "/ as sysdba"
                        SQL*Plus: Release 9.2.0.3.0 - Production on Mon Dec 29 10:52:14 2003
                        Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
                        Connected to:
                        Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
                        With the Partitioning, OLAP and Oracle Data Mining options
                        JServer Release 9.2.0.3.0 - Production
                        SQL> @getsql
                        Enter value for spid: 20521
                        old  10: where c.spid = '&pid'
                        new  10: where c.spid = '20521'
                        SQL_TEXT
                        ----------------------------------------------------------------
                        select * from (select VC2URL,VC2PVDID,VC2MOBILE,VC2ENCRYPTFLAG,S
                        ERVICEID,VC2SUB_TYPE,CISORDER,NUMGUID,VC2KEY1, VC2NEEDDISORDER,V
                        C2PACKFLAG,datopertime from hsv_2cpsync where datopertime<=sysda
                        te and numguid>70000000000308 order by NUMGUid) where rownum<=20

SQL> @getsid
Enter value for spid: 20521
old 3: select addr from v$process where spid = &spid)
new 3: select addr from v$process where spid = 20521)

SID SERIAL# USERNAME MACHINE
----------------------------------------------------------------
45 38991 HSUSER_V51 hswapjsptl1.hurray.com.cn

SQL> exec dbms_system.set_sql_trace_in_session(45,38991,true);

PL/SQL procedure successfully completed.

SQL> !