AWR SQL 보고서 생성
- AWR 보고서를 분석하다 보면 비효율적으로 수행되는 SQL에 대한 전체 구문 및 실행 계획과 실행 통계 정보를 분석해야 할 경우 발생.
- AWR DB 보고서에는 이에 대한 정보가 나타나지 않는다.
- awrsqrpt.sql, awrsqrpi.sql
SQL> @?/rdbms/admin/awrsqrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
1490765173 ORATEST 1 ORATEST
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: text
Type Specified: text
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
1490765173 1 ORATEST ORATEST dbhost1
* 1490765173 1 ORATEST ORATEST dbhost
Using 1490765173 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 <return> without
specifying a number lists all completed snapshots.
Enter value for num_days: 2
Listing the last 2 days of Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
ORATEST ORATEST 38641 14 Dec 2012 00:00 1
38642 14 Dec 2012 00:20 1
38643 14 Dec 2012 00:40 1
...
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
ORATEST ORATEST 38697 14 Dec 2012 18:40 1
38698 14 Dec 2012 19:00 1
38699 14 Dec 2012 19:20 1
...
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 38698
Begin Snapshot Id specified: 38698
Enter value for end_snap: 38699
End Snapshot Id specified: 38699
Specify the SQL Id
~~~~~~~~~~~~~~~~~~
Enter value for sql_id: 12xttzc893c2g
SQL ID specified: 12xttzc893c2g
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrsqlrpt_1_38698_38699.txt. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: awrsqlrpt_1_38698_38699.txt.
Using the report name awrsqlrpt_1_38698_38699.txt
WORKLOAD REPOSITORY SQL Report
Snapshot Period Summary
DB Name DB Id Instance Inst Num Startup Time Release RAC
------------ ----------- ------------ -------- --------------- ----------- ---
ORATEST 1490765173 ORATEST 1 30-Nov-12 16:48 11.2.0.3.0 NO
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 38698 14-Dec-12 19:00:30 28 1.8
End Snap: 38699 14-Dec-12 19:20:40 26 2.0
Elapsed: 20.17 (mins)
DB Time: 0.04 (mins)
SQL Summary DB/Inst: ORATEST/ORATEST Snaps: 38698-38699
Elapsed
SQL Id Time (ms)
------------- ----------
12xttzc893c2g 193
insert into WRH$_RSRC_CONSUMER_GROUP (snap_id, dbid, instance_number, seque
nce#, consumer_group_id, consumer_group_name, requests, cpu_wait_time, cpu_wa
its, consumed_cpu_time, yields, active_sess_limit_hit, undo_limit_hit, swi
tches_in_cpu_time, switches_out_cpu_time, switches_in_io_megabytes, switches_
-------------------------------------------------------------
SQL ID: 12xttzc893c2g DB/Inst: ORATEST/ORATEST Snaps: 38698-38699
-> 1st Capture and Last Capture Snap IDs
refer to Snapshot IDs witin the snapshot range
-> insert into WRH$_RSRC_CONSUMER_GROUP (snap_id, dbid, instance_number...
Plan Hash Total Elapsed 1st Capture Last Capture
# Value Time(ms) Executions Snap ID Snap ID
--- ---------------- ---------------- ------------- ------------- --------------
1 4185999918 193 1 38699 38699
-------------------------------------------------------------
Plan 1(PHV: 4185999918)
-----------------------
Plan Statistics DB/Inst: ORATEST/ORATEST Snaps: 38698-38699
-> % Total DB Time is the Elapsed Time of the SQL statement divided
into the Total Database Time multiplied by 100
Stat Name Statement Per Execution % Snap
---------------------------------------- ---------- -------------- -------
Elapsed Time (ms) 193 193.5 9.2
CPU Time (ms) 37 37.2 1.5
Executions 1 N/A N/A
Buffer Gets 562 562.0 0.6
Disk Reads 2 2.0 13.3
Parse Calls 1 1.0 0.1
Rows 80 80.0 N/A
User I/O Wait Time (ms) 154 N/A N/A
Cluster Wait Time (ms) 0 N/A N/A
Application Wait Time (ms) 0 N/A N/A
Concurrency Wait Time (ms) 0 N/A N/A
Invalidations 0 N/A N/A
Version Count 1 N/A N/A
Sharable Mem(KB) 73 N/A N/A
-------------------------------------------------------------
Execution Plan
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 2 (100)| |
| 1 | LOAD TABLE CONVENTIONAL | | | | | |
| 2 | NESTED LOOPS OUTER | | 1 | 568 | 2 (50)| 00:00:01 |
| 3 | HASH JOIN | | 1 | 563 | 1 (100)| 00:00:01 |
| 4 | FIXED TABLE FULL | X$KSKPLW | 1 | 52 | 0 (0)| |
| 5 | FIXED TABLE FULL | X$KGSKSCS | 1 | 511 | 0 (0)| |
| 6 | INDEX RANGE SCAN | I_OBJ1 | 1 | 5 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Full SQL Text
SQL ID SQL Text
------------ -----------------------------------------------------------------
12xttzc893c2 insert into WRH$_RSRC_CONSUMER_GROUP (snap_id, dbid, instance_num
ber, sequence#, consumer_group_id, consumer_group_name, requests,
cpu_wait_time, cpu_waits, consumed_cpu_time, yields, active_sess
_limit_hit, undo_limit_hit, switches_in_cpu_time, switches_out_cp
u_time, switches_in_io_megabytes, switches_out_io_megabytes, swit
ches_in_io_requests, switches_out_io_requests, sql_canceled, acti
ve_sess_killed, idle_sess_killed, idle_blkr_sess_killed, queued_t
ime, queue_time_outs, io_service_time, io_service_waits, small_re
ad_megabytes, small_write_megabytes, large_read_megabytes, large_
write_megabytes, small_read_requests, small_write_requests, large
_read_requests, large_write_requests, pqs_queued, pq_queued_time,
pq_queue_time_outs, pqs_completed, pq_servers_used, pq_active_ti
me) (select :snap_id, :dbid, :instance_number, cg.sequence#, cg.i
d, cg.name, cg.requests, cg.cpu_wait_time, cg.cpu_waits, cg.consu
med_cpu_time, cg.yields, cg.active_sess_limit_hit, cg.undo_limit_
hit, cg.switches_in_cpu_time, cg.switches_out_cpu_time, cg.switch
es_in_io_megabytes, cg.switches_out_io_megabytes, cg.switches_in_
io_requests, cg.switches_out_io_requests, cg.sql_canceled, cg.act
ive_sess_killed, cg.idle_sess_killed, cg.idle_blkr_sess_killed, c
g.queued_time, cg.queue_time_outs, cg.io_service_time, cg.io_serv
ice_waits, cg.small_read_megabytes, cg.small_write_megabytes, cg.
large_read_megabytes, cg.large_write_megabytes, cg.small_read_req
uests, cg.small_write_requests, cg.large_read_requests, cg.large_
write_requests, cg.pqs_queued, cg.pq_queued_time, cg.pq_queue_tim
e_outs, cg.pqs_completed, cg.pq_servers_used, cg.pq_active_time f
rom v$rsrc_cons_group_history cg, v$rsrc_plan_history pl where cg
.sequence# = pl.sequence# and pl.id is not null)
Report written to awrsqlrpt_1_38698_38699.txt
SQL>