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;
/
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26179867/viewspace-733846/，如需转载，请注明出处，否则将追究法律责任。
上一篇：
由于网关导致映射环境连接失败
下一篇：
JOB时间间隔设定
请登录后发表评论
登录
全部评论
