Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "an example analysis of the high CPU problem in oracle database". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

I. Environmental preparation

1. View the current snapshot

SQL > select max (snap_id) from dba_hist_snapshot s; 28

2. Generate a snapshot by hand

Exec dbms_workload_repository.create_snapshot

3. Create an experimental table

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 (squart3roomid.Nextval.Nextvalddbmsroomroom.string ('upright, 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 (squart4roomid.Nextvalddbmsroomroom.string ('upright, 10), sysdate); 4 end loop; 5 end; 6 / SQL > select * from scott.t3 where name=dbms_random.string (' upright, 10); no rows selectedSQL > select * from scott.t4 where name=dbms_random.string ('upright, 10); no rows selected

4. Generate snapshots manually

Exec dbms_workload_repository.create_snapshot

At this point, the snapshot range when the simulation problem occurs is 29-30.

Second, use SPA for analysis

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 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 onSQL > set heading offSQL > set pagesize 2000SQL > set long 20000SQL > spool compare_report.htmlSQL > select DBMS_SQLPA.report_analysis_task (: sts_task,'HTML','ALL','ALL') from dual;SQL > spool off

Looking at the production HTML file, you can see the information of all sql running within the time frame.

This is the end of the content of "example Analysis of excessive CPU in oracle Database". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.