1.建立存放sql记录表record_sql_stats
create table record_sql_stats as
select sql_text,
sql_fulltext,
parsing_schema_name,
module,
action,
sql_id,
executions,
disk_reads,
buffer_gets,
first_load_time,
last_load_time,
last_active_time,
hash_value,
trunc(elapsed_time / 1000000) elap_ss,
round(elapsed_time / 1000000 / 60, 2) elap_mi
from v$sqlarea
where parsing_schema_name = 'FONEPLAYERUSER'
AND round(elapsed_time / 1000000 / 60, 2) > 5;
2.建立抓取执行效率低下SQL的过程
create or replace procedure pro_record_sql_stats as
begin
delete from record_sql_stats
where hash_value in
(select hash_value
from v$sqlarea
where parsing_schema_name = 'FONEPLAYERUSER'
AND round(elapsed_time / 1000000 / 60, 2) > 5
and hash_value in (select hash_value from record_sql_stats));
commit;
insert into record_sql_stats
select sql_text,
sql_fulltext,
parsing_schema_name,
module,
action,
sql_id,
executions,
disk_reads,
buffer_gets,
first_load_time,
last_load_time,
last_active_time,
hash_value,
trunc(elapsed_time / 1000000) elap_ss,
round(elapsed_time / 1000000 / 60, 2) elap_mi
from v$sqlarea
where parsing_schema_name = 'FONEPLAYERUSER'
AND round(elapsed_time / 1000000 / 60, 2) > 5;
commit;
end;
3.建立自动执行job
begin
sys.dbms_job.submit(job => ,
what => 'pro_record_sql_stats;',
next_date => to_date('04-06-2012 16:55:00', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'TRUNC(sysdate,''mi'') + 10 / (24*60)');
commit;
end;
/