当前位置:网站首页>collect awr
collect awr
2022-08-11 09:22:00 【Two small oriole】
查看最大的snap_id
select max(snap_id) from dba_hist_snapshot;
手工生成awr快照
Before stress testing begins,手工生成awr快照,压测结束后,Manually generate againawr快照
begin
dbms_workload_repository.create_snapshot();
end;
/
创建awr报告
Two snapshots generated by hand,Generated separately at all nodesawr报告
@?/rdbms/admin/awrrpt.sql
批量生成awr报告
创建文件awrrpt_batch.sql
set serveroutput on;
set feedback off;
set linesize 300;
prompt ***************************************************************;
prompt usage:
prompt 1.noninteractive : SQL>@awrrpt_batch.sql dbid instance_num start_snap end_snap;
prompt 2.interactive : SQL>@awrrpt_batch.sql;
prompt author : Darren_Guo
prompt ***************************************************************;
pause press enter to continue or ctrl-c to exit.;
col snap_id for 999999999;
col snap dbid 9999999999;
col startup_time for a30;
col begin_interval_time for a30;
col end_interval_time for a30;
select dbid,snap_id,instance_number,startup_time,begin_interval_time,end_interval_time from dba_hist_snapshot order by dbid,instance_number,snap_id;
exec dbms_output.put_line(chr(13)||chr(10)||'please enter dbid,inst_number,start and end snap_id:');
declare
v_dbid number;
v_instance number;
v_b_id number;
v_e_id number;
v_code number;
v_errm varchar2(300);
v_sql varchar2(300);
v_html varchar2(20000);
cur_awrrpt_html SYS_REFCURSOR;
cur_snapshot SYS_REFCURSOR;
fileID utl_file.file_type;
v_filename varchar2(30);
v_snap_id number;
v_startup_time timestamp(3);
v_begin_snap_time timestamp(3);
v_end_snap_time timestamp(3);
v_dpath varchar2(60);
begin
v_dbid:=&1;
v_instance:=&2;
v_b_id:=&3;
v_e_id:=&4;
dbms_output.put_line(chr(13)||chr(10)||'awrrpt report files:');
for k in v_b_id..v_e_id-1 loop
v_filename:='pmdb_'||k||'_'||(k+1)||'.html';
fileID:=utl_file.fopen('DATA_PUMP_DIR',v_filename,'a',32767);
v_sql:='select output from table(dbms_workload_repository.awr_report_html('||v_dbid||','||v_instance||','||k||','||(k+1)||',8))';
open cur_awrrpt_html for v_sql;
loop
exit when cur_awrrpt_html%notfound;
fetch cur_awrrpt_html into v_html;
utl_file.put_line(fileID,v_html);
end loop;
utl_file.fclose(fileID);
execute immediate 'select directory_path from dba_directories where directory_name=:dname' into v_dpath using 'DATA_PUMP_DIR';
dbms_output.put_line(v_dpath||v_filename);
end loop;
exception
when others then
v_code:=SQLCODE;
v_errm:=SQLERRM;
dbms_output.put_line('ERROR CODE'||v_code||':'||v_errm);
end;
/
Prompted in the usage script
usage:
1.noninteractive : SQL>@awrrpt_batch.sql dbid instance_num start_snap end_snap;
2.interactive : SQL>@awrrpt_batch.sql;
author : Darren_Guo
***************************************************************;
pause press enter to continue or ctrl-c to exit.;
边栏推荐
猜你喜欢
力扣题解8/10
通过Xshell连接Vagrant创建的虚拟机
excel将数据按某一列值分组并绘制分组折线图
阿里云OSS上传文件超时 探测工具排查方法
无代码平台助力中山医院搭建“智慧化管理体系”,实现智慧医疗
One network cable to transfer files between two computers
Analysis of the Status Quo of Enterprise Server Host Reinforcement
Adobe LiveCycle Designer 报表设计器
golang string manipulation
Unity3D - modification of the Inspector panel of the custom class
随机推荐
WiFi cfg80211
刷题错题录2-向上取整、三角形条件、字符串拼接匹配、三数排序思路
shell之sed
gRPC系列(一) 什么是RPC?
Getting Started with Kotlin Algorithms Calculating Prime Numbers and Optimization
谁能解答?从mysql的binlog读取数据到kafka,但是数据类型有Insert,updata,
大佬们,我有一个MySQL source 通过旁路分流分了两个流,然后转表,现在想sink到两个hb
Contrastive Learning Series (3)-----SimCLR
SQL语句
Primavera Unifier 高级公式使用分享
One network cable to transfer files between two computers
腾讯电子签开发说明
清除微信小程序button的默认样式
【wxGlade学习】wxGlade环境配置
What should I do if the mysql data query causes the cup to be full because the query time span is too large
flex布局回顾
js将table生成excel文件并去除表格中的多余tr(js去除表格中空的tr标签)
halcon实例
四级独创的阅读词汇表
WordpressCMS主题开发01-首页制作