本篇内容介绍了“oracle数据库CPU过高问题举例分析”的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让小编带领大家学习一下如何处理这些情况吧!希望大家仔细阅读,能够学有所成!一、
本篇内容介绍了“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的信息
“oracle数据库CPU过高问题举例分析”的内容就介绍到这里了,感谢大家的阅读。如果想了解更多行业相关的知识可以关注编程网网站,小编将为大家输出更多高质量的实用文章!
--结束END--
本文标题: oracle数据库CPU过高问题举例分析
本文链接: https://www.lsjlt.com/news/65092.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-21
2024-05-21
2024-05-21
2024-05-21
2024-05-21
2024-05-21
2024-05-21
2024-05-21
2024-05-21
2024-05-21
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0