教育机构推出小程序,学习娱乐化填补碎片化时间
157
2024-01-03
本篇内容介绍了“oracle数据库CPU过高问题举例分析”的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让小编带领大家学习一下如何处理这些情况吧!希望大家仔细阅读,能够学有所成!
一、环境准备
1、查看当前快照情况
SQL> select max(snap_id) from dba_hist_snapshot s; 282、手工生成一份快照
execdbms_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 selected4、手工生成快照
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 2base_cur dbms_sqltune.sqlset_cursor;3 begin 4 open base_cur for 5 select value(x) fromtable(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> setheading off SQL>set pagesize 2000 SQL> set long 20000SQL> 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过高问题举例分析”的内容就介绍到这里了,感谢大家的阅读。如果想了解更多行业相关的知识可以关注网站,小编将为大家输出更多高质量的实用文章!
版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。
发表评论
暂时没有评论,来抢沙发吧~