cpu等待事件

Oracle系統裡,CPU利用率過高的診斷2006-10-24 10:491,先查看alert log裡有沒有oracle 本身的錯誤資訊.

2,排除資料庫本身問題後,使用以下SQL查詢:

select sql_text,spid,v$session.program,process from v$sqlarea,v$session,v$process

where v$sqlarea.address=v$session.sql_address

and v$sqlarea.hash_value=v$session.sql_hash_value

and v$session.paddr=v$process.addr

and v$process.spid in (PID);

3,在UNIX主機裡用top找出CPU用的很高的進程PID號碼去替換 SQL 陳述式裡的 PID,會返回一個oracle進程所執行的SQL語句,CPU利用率很高的進程應該都是執行同一個SQL語句.

4,基本可以肯定是這個SQL語句導致CPU利用率過高.男那麼要看看這個進程的等待事件都有那些

select sid,event,p1,p1text from v$session_wait;

SQL> col sid for 999999;
SQL> col event for a30;
SQL> col p1 for 999999;
SQL> col p1text for a30;
select sid,event,p1,p1text from v$session_wait
where event not like 'SQL*Net message from client';

出現SQL*Net message from client 大量事件

指令碼
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
/

 

 

 

/home/oracle/expdat.dmp
/home/backup/ora10g/expdat.dmp
/oracle/app/product/10.2.0.1/db/network/expdat.dmp

set colsep' ';　　　 //域輸出分隔字元
set echo off;　　　　//顯示start啟動的指令碼中的每個sql命令，預設為on
set feedback off;　　//回顯本次sql命令處理的記錄條數，預設為on
set heading off;　　 //輸出域標題，預設為on
set pagesize 0;　　　//輸出每頁行數，預設為24,為了避免分頁，可設定為0。
set termout off;　　 //顯示指令碼中的命令的執行結果，預設為on
set trimout on;　　　//去除標準輸出每行的拖尾空格，預設為off
set trimspool on;　　//去除重新導向（spool）輸出每行的拖尾空格，預設為off

***************************************************************************
可以通過如下語句選擇一下物理讀和記憶體讀較高的語句:

SELECT
t.HASH_VALUE,
t.EXECUTIONS,
t.DISK_READS,
round(t.DISK_READS/t.EXECUTIONS) AS perDiskReads,
t.BUFFER_GETS,
round(t.BUFFER_GETS/t.EXECUTIONS) AS perBufferReads,
t.ELAPSED_TIME,
round(t.ELAPSED_TIME/t.EXECUTIONS) AS perElayTime,
t.CPU_TIME,
round(t.CPU_TIME/t.EXECUTIONS) AS perCpuTime,
t.FIRST_LOAD_TIME,
t.SQL_TEXT
FROM v$sql t
WHERE (t.DISK_READS/t.EXECUTIONS >
500 OR t.BUFFER_GETS/t.EXECUTIONS > 20000)
AND t.EXECUTIONS > 0
ORDER BY 6 DESC;

 *****************************************************************************
SQL>@$ORACLE_HOME/rdbms/admin/utlchain.sql

Table created.

SQL> ANALYZE TABLE CUSTOMER LIST CHAINED ROWS INTO chained_rows;

Table analyzed.

SQL>SELECT count(*) from chained_rows；

TABLE_NAME COUNT(*)

CUSTOMER 21306

1 rows selected.

查看在CUSTOMER表上存在的限制:

SQL>select CONSTRAINT_NAME,CONSTRAINT_TYPE,
TABLE_NAME from USER_CONSTRAINTS where TABLE_NAME='CUSTOMER';

CONSTRAINT_NAME C TABLE_NAME

------------------------------ - --

PK_CUSTOMER1 P CUSTOMER

SQL>select CONSTRAINT_NAME,CONSTRAINT_TYPE,
TABLE_NAME from USER_CONSTRAINTS
where R_CONSTRAINT_NAME='PK_CUSTOMER1';

no rows selected

SQL> CREATE TABLE CUSTOMER_temp AS

SELECT * FROM CUSTOMER WHERE rowid IN

(SELECT head_rowid FROM chained_rows

WHERE table_name = 'CUSTOMER');

Table created.

SQL>select count(*) from CUSTOMER;

COUNT(*)

----------

338299

SQL> DELETE CUSTOMER WHERE rowid IN

(SELECT head_rowid

FROM chained_rows

WHERE table_name = 'CUSTOMER');

21306 rows deleted.

SQL> INSERT INTO CUSTOMER SELECT * FROM CUSTOMER_temp;

21306 rows created.

SQL> DROP TABLE CUSTOMER_temp;

Table dropped.

SQL> commit;

Commit complete.

SQL> select count(*) from CUSTOMER;

COUNT(*)

----------

338299

SQL> truncate table chained_rows;

Table truncated.

SQL> ANALYZE TABLE CUSTOMER LIST CHAINED ROWS INTO chained_rows;

Table analyzed.

SQL> select count(*) from chained_rows;

COUNT(*)

----------

0

以上整個清除兩萬多行的行遷移過程在三分鐘左右，而且全部都在聯機的狀態下完成，基本上不會對業務有什麼影響，唯一就是在要清除行遷移的表上不能有對外鍵的限制，否則就不能採用這個方法去清除了。