ORACLE
TEXT AWR 생성 및 파싱하기
corekms
2015. 4. 21. 16:24
AWR 레포트를 스냅샷 구간별로 텍스트파일을 통하여 생성하고, 파싱을 통하여 시간대별 챠트를 작성해 보았습니다.
1. 원하는 스냅샷 구간을 설정하여 AWR 레포트 파일을 출력하기 위한 사전 스크립트를 생성합니다. 스크립트 생성기 소스는 아래와 같습니다.
2. 스크립트를 실행하면 아래와 같은 과정을 거쳐 AWR TEXT파일이 생성됩니다.
3. parser 를 리다이렉트와 함께 실행하면 아래와 같이 CSV 형태로 파일을 생성할 수 있습니다.
4. 생성한 CSV 파일로 엑셀을 통하여 생성한 차트 예 입니다.
1. 원하는 스냅샷 구간을 설정하여 AWR 레포트 파일을 출력하기 위한 사전 스크립트를 생성합니다. 스크립트 생성기 소스는 아래와 같습니다.
REM AWR-Generator.sql: Script for creating multiple consecutive Oracle AWR Reports REM REM Creates an output SQL script which, when run, will generate all AWR Reports REM between the specificed start and end snapshot IDs, for all instances REM REM For educational purposes only - no warranty is provided REM Test thoroughly - use at your own risk REM set feedback off set echo off set verify off set timing off -- Set AWR_FORMAT to "text" or "html" define AWR_FORMAT = 'text' define DEFAULT_OUTPUT_FILENAME = 'awr-generate.sql' define NO_ADDM = 0 -- Get values for dbid and inst_num before calling awrinput.sql set echo off heading on column inst_num heading "Inst Num" new_value inst_num format 99999; column inst_name heading "Instance" new_value inst_name format a12; column db_name heading "DB Name" new_value db_name format a12; column dbid heading "DB Id" new_value dbid format 9999999999 just c; prompt prompt Current Instance prompt ~~~~~~~~~~~~~~~~ select d.dbid dbid , d.name db_name , i.instance_number inst_num , i.instance_name inst_name from v$database d, v$instance i; -- Call the Oracle common input script to setup start and end snap ids @@?/rdbms/admin/awrinput.sql -- Ask the user for the name of the output script prompt prompt Specify output script name prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~ prompt This script produces output in the form of another SQL script prompt The output script contains the commands to generate the AWR Reports prompt prompt The default output file name is &DEFAULT_OUTPUT_FILENAME prompt To accept this name, pressto continue, otherwise enter an alternative prompt set heading off column outfile_name new_value outfile_name noprint; select 'Using the output file name ' || nvl('&&outfile_name','&DEFAULT_OUTPUT_FILENAME') , nvl('&&outfile_name','&DEFAULT_OUTPUT_FILENAME') outfile_name from sys.dual; set linesize 800 set serverout on set termout off -- spool to outputfile spool &outfile_name -- write script header comments prompt REM Temporary script created by awr-generator.sql prompt REM Used to create multiple AWR reports between two snapshots select 'REM Created by user '||user||' on '||sys_context('userenv', 'host')||' at '||to_char(sysdate, 'DD-MON-YYYY HH24:MI') from dual; set heading on -- Begin iterating through snapshots and generating reports DECLARE c_dbid CONSTANT NUMBER := :dbid; c_inst_num CONSTANT NUMBER := :inst_num; c_start_snap_id CONSTANT NUMBER := :bid; c_end_snap_id CONSTANT NUMBER := :eid; c_awr_options CONSTANT NUMBER := &&NO_ADDM; c_report_type CONSTANT CHAR(4):= '&&AWR_FORMAT'; v_awr_reportname VARCHAR2(100); v_report_suffix CHAR(5); CURSOR c_snapshots IS select inst_num, start_snap_id, end_snap_id from ( select s.instance_number as inst_num, s.snap_id as start_snap_id, lead(s.snap_id,1,null) over (partition by s.instance_number order by s.snap_id) as end_snap_id from dba_hist_snapshot s where s.dbid = c_dbid and s.snap_id >= c_start_snap_id and s.snap_id <= c_end_snap_id ) where end_snap_id is not null order by inst_num, start_snap_id; BEGIN dbms_output.put_line(''); dbms_output.put_line('prompt Beginning AWR Generation...'); dbms_output.put_line('set heading off feedback off lines 800 pages 5000 trimspool on trimout on'); -- Determine report type (html or text) IF c_report_type = 'html' THEN v_report_suffix := '.html'; ELSE v_report_suffix := '.txt'; END IF; -- Iterate through snapshots FOR cr_snapshot in c_snapshots LOOP -- Construct filename for AWR report v_awr_reportname := 'awrrpt_'||cr_snapshot.inst_num||'_'||cr_snapshot.start_snap_id||'_'||cr_snapshot.end_snap_id||v_report_suffix; dbms_output.put_line('prompt Creating AWR Report '||v_awr_reportname ||' for instance number '||cr_snapshot.inst_num||' snapshots '||cr_snapshot.start_snap_id||' to '||cr_snapshot.end_snap_id); dbms_output.put_line('prompt'); -- Disable terminal output to stop AWR text appearing on screen dbms_output.put_line('set termout off'); -- Set spool to create AWR report file dbms_output.put_line('spool '||v_awr_reportname); -- call the table function to generate the report IF c_report_type = 'html' THEN dbms_output.put_line('select output from table(dbms_workload_repository.awr_report_html(' ||c_dbid||','||cr_snapshot.inst_num||','||cr_snapshot.start_snap_id||','||cr_snapshot.end_snap_id||','||c_awr_options||'));'); ELSE dbms_output.put_line('select output from table(dbms_workload_repository.awr_report_text(' ||c_dbid||','||cr_snapshot.inst_num||','||cr_snapshot.start_snap_id||','||cr_snapshot.end_snap_id||','||c_awr_options||'));'); END IF; dbms_output.put_line('spool off'); -- Enable terminal output having finished generating AWR report dbms_output.put_line('set termout on'); END LOOP; dbms_output.put_line('set heading on feedback 6 lines 100 pages 45'); dbms_output.put_line('prompt AWR Generation Complete'); -- EXCEPTION HANDLER? END; / spool off set termout on prompt prompt Script written to &outfile_name - check and run in order to generate AWR reports... prompt --clear columns sql undefine outfile_name undefine AWR_FORMAT undefine DEFAULT_OUTPUT_FILENAME undefine NO_ADDM undefine OUTFILE_NAME set feedback 6 verify on lines 100 pages 45
2. 스크립트를 실행하면 아래와 같은 과정을 거쳐 AWR TEXT파일이 생성됩니다.
SQL*Plus: Release 11.2.0.3.0 Production on Wed Feb 27 17:59:49 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> @awr-generator.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
1334550050 ORCL 1 orcl
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 1334550050 1 ORCL orcl half-server4
Using 1334550050 for database Id
Using 1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing without
specifying a number lists all completed snapshots.
Enter value for num_days: 1
Listing the last day's Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
orcl ORCL 1389 27 Feb 2013 00:00 1
1390 27 Feb 2013 01:00 1
1391 27 Feb 2013 02:00 1
1392 27 Feb 2013 03:00 1
1393 27 Feb 2013 04:00 1
1394 27 Feb 2013 05:00 1
1395 27 Feb 2013 06:00 1
1396 27 Feb 2013 07:00 1
1397 27 Feb 2013 08:00 1
1398 27 Feb 2013 09:00 1
1399 27 Feb 2013 10:00 1
1400 27 Feb 2013 11:00 1
1401 27 Feb 2013 12:00 1
1402 27 Feb 2013 13:00 1
1403 27 Feb 2013 14:00 1
1404 27 Feb 2013 15:00 1
1405 27 Feb 2013 16:00 1
1406 27 Feb 2013 17:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1400
Begin Snapshot Id specified: 1400
Enter value for end_snap: 1404
End Snapshot Id specified: 1404
Specify output script name
~~~~~~~~~~~~~~~~~~~~~~~~~~
This script produces output in the form of another SQL script
The output script contains the commands to generate the AWR Reports
The default output file name is awr-generate.sql
To accept this name, press to continue, otherwise enter an alternative
Enter value for outfile_name:
Using the output file name awr-generate.sql
Script written to awr-generate.sql - check and run in order to generate AWR reports...
SQL> @awr-generate.sql
Beginning AWR Generation...
Creating AWR Report awrrpt_1_1400_1401.txt for instance number 1 snapshots 1400 to 1401
Creating AWR Report awrrpt_1_1401_1402.txt for instance number 1 snapshots 1401 to 1402
Creating AWR Report awrrpt_1_1402_1403.txt for instance number 1 snapshots 1402 to 1403
Creating AWR Report awrrpt_1_1403_1404.txt for instance number 1 snapshots 1403 to 1404
AWR Generation Complete
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@server4 ~]$ ls -l awrrpt*
-rw-r--r--. 1 oracle oinstall 152333 Feb 27 18:00 awrrpt_1_1400_1401.txt
-rw-r--r--. 1 oracle oinstall 152676 Feb 27 18:00 awrrpt_1_1401_1402.txt
-rw-r--r--. 1 oracle oinstall 153511 Feb 27 18:00 awrrpt_1_1402_1403.txt
-rw-r--r--. 1 oracle oinstall 148614 Feb 27 18:00 awrrpt_1_1403_1404.txt
3. parser 를 리다이렉트와 함께 실행하면 아래와 같이 CSV 형태로 파일을 생성할 수 있습니다.
$ ./awr-parser.sh
Usage: ./awr-parser.sh [ -n | -H ] [ -s | -p | -v ] (wildcards are accepted)
Version v1.02 (published on 05/01/2015)
Script for parsing multiple AWR reports and extracting useful information
Redirect stdout to a CSV file to import into Excel
Errors and info are printed to stderr
Options:
-h Help (print help and version information)
-H Header (print the header row only and then exit)
-n NoHeader (do not print the header row in the CSV file)
-p Print (print AWR report values to screen)
-s Silent (do not print AWR processing information)
-v Verbose (show extra AWR processing details)
Example usage:
./awr-parser.sh awr*.txt > awr.csv
$ ./awr-parser.sh SampleAWRs/*.txt > output.csv
Info : Parsing file SampleAWRs/awr_09h00_09h30.txt at 2014-12-01 15:43:00
Info : Parsing file SampleAWRs/awr_09h30_10h00.txt at 2014-12-01 15:43:58
Info : Parsing file SampleAWRs/awr_10h00_10h30.txt at 2014-12-01 15:44:53
Info : Parsing file SampleAWRs/awr_10h30_11h00.txt at 2014-12-01 15:45:51
Info : Parsing file SampleAWRs/awr_11h00_11h30.txt at 2014-12-01 15:46:51
Info : Parsing file SampleAWRs/awr_11h30_12h00.txt at 2014-12-01 15:47:45
Info : Parsing file SampleAWRs/awr_12h00_12h30.txt at 2014-12-01 15:48:38
Info : Parsing file SampleAWRs/awr_12h30_13h00.txt at 2014-12-01 15:49:35
Info : Parsing file SampleAWRs/awr_13h00_13h30.txt at 2014-12-01 15:50:31
Info : Parsing file SampleAWRs/awr_13h30_14h00.txt at 2014-12-01 15:51:26
Info : Parsing file SampleAWRs/awr_14h00_14h30.txt at 2014-12-01 15:52:24
Info : Parsing file SampleAWRs/awr_14h30_15h00.txt at 2014-12-01 15:53:47
Info : Parsing file SampleAWRs/awr_15h00_15h30.txt at 2014-12-01 15:55:07
Info : Parsing file SampleAWRs/awr_15h30_16h00.txt at 2014-12-01 15:56:12
Info : Parsing file SampleAWRs/awr_16h00_16h30.txt at 2014-12-01 15:57:14
Info : Parsing file SampleAWRs/awr_16h30_17h00.txt at 2014-12-01 15:58:14
Info : Parsing file SampleAWRs/awr_17h00_17h30.txt at 2014-12-01 15:59:16
Info : Parsing file SampleAWRs/awr_17h30_18h00.txt at 2014-12-01 16:00:20
Info : No more files found
Info :
Info : ______SUMMARY______
Info : Files found : 18
Info : Files processed : 18
Info : Processing errors : 0
Info :
Info : Completed successfully
4. 생성한 CSV 파일로 엑셀을 통하여 생성한 차트 예 입니다.
출처 : http://flashdba.com/database/useful-scripts