oracle定位数据库读写高,oracle数据库CPU过高问题定位、分析（三）

一、环境准备

1、查看当前快照情况

SQL> select max(snap_id) from dba_hist_snapshot s;

28

2、手工生成一份快照

exec dbms_workload_repository.create_snapshot;

3、创建实验表

SQL> create sequence s_t3_id minvalue 1 maxvalue 9999999999 start with 1 increment by 1 cache 20 order;

SQL> create table t3 (id number,name varchar2(20),created date);

Table created.

SQL> begin

2  for i in 1 .. 5000000 loop

3  insert into t3 values (s_t3_id.Nextval,dbms_random.string('u', 10),sysdate);

4  end loop;

5  end;

6  /

SQL> create sequence s_t4_id minvalue 1 maxvalue 9999999999 start with 1 increment by 1 cache 20 order;

SQL> create table t4 (id number,name varchar2(20),created date);

Table created.

SQL> begin

2  for i in 1 .. 3000000 loop

3  insert into t4 values (s_t4_id.Nextval,dbms_random.string('u', 10),sysdate);

4  end loop;

5  end;

6  /

SQL> select * from scott.t3 where name=dbms_random.string('u', 10)；

no rows selected

SQL> select * from scott.t4 where name=dbms_random.string('u', 10)；

no rows selected

4、手工生成快照

exec dbms_workload_repository.create_snapshot;

至此，模拟问题出现时的快照区间为29-30

二、使用SPA进行分析

begin

2  dbms_sqltune.create_sqlset(

3  sqlset_name=>'cpu_test',

4  description => 'High cpu read tuning set');

5  end;

6  /

PL/SQL procedure successfully completed.

SQL> declare

2  base_cur dbms_sqltune.sqlset_cursor;

3  begin

4  open base_cur for

5  select value(x) from table(DBMS_SQLTUNE.select_workload_repository(29,30,NULL,NULL,'cpu_time',NULL,NULL,NULL,10)) x;

6  --

7  dbms_sqltune.load_sqlset(sqlset_name=>'cpu_test',populate_cursor => base_cur);

8  end;

9  /

PL/SQL procedure successfully completed.

SQL> variable sts_task  VARCHAR2(64);

SQL> EXEC :sts_task :=DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name=>'cpu_test',order_by=>'cpu_time',description=>'process workload ordered by cpu_time');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_SQLPA.execute_analysis_task(task_name=>:sts_task,execution_params=>dbms_advisor.arglist('TIME_LIMIT','1800'));

PL/SQL procedure successfully completed.

SQL> set serveroutput on

SQL> set heading off

SQL> set pagesize 2000

SQL> set long 20000

SQL> spool compare_report.html

SQL> select DBMS_SQLPA.report_analysis_task(:sts_task,'HTML','ALL','ALL') from dual;

SQL> spool off

查看生产的HTML文件，可看到时间范围内运行的所有sql的信息

三、结论：

因为报告是根据CPU的使用进行排序的，所以可以看到top1与top2消耗的cpu资源相对其他sql更多，因此可定位cpu高的起因为这两条sql