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

以上整个清除两万多行的行迁移过程在三分钟左右，而且全部都在联机的状态下完成，基本上不会对业务有什么影响，唯一就是在要清除行迁移的表上不能有对外键的限制，否则就不能采用这个方法去清除了。