使用awrsqrpt.sql查看执行计划demo
SQL> @?/rdbms/admin/awrsqrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
2545367939 RAC 1 rac1
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
------------ -------- ------------ ------------ ------------
* 2545367939 1 RAC rac1 rac1
2545367939 2 RAC rac2 rac2
Using 2545367939 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: 1
Listing the last day's Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
rac1 RAC 3 14 Jan 2018 14:35 1
4 14 Jan 2018 16:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 3
Begin Snapshot Id specified: 3
Enter value for end_snap: 4
End Snapshot Id specified: 4
Specify the SQL Id
~~~~~~~~~~~~~~~~~~
Enter value for sql_id: 5mycvad72f8qc
SQL ID specified: 5mycvad72f8qc
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrsqlrpt_1_3_4.txt. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: lijiaman_awrsqpt_1.txt
Using the report name lijiaman_awrsqpt_1.txt
报告内容如下：
WORKLOAD REPOSITORY SQL Report
Snapshot Period Summary
DB Name DB Id Instance Inst Num Startup Time Release RAC
------------ ----------- ------------ -------- --------------- ----------- ---
RAC 2545367939 rac1 1 14-Jan-18 14:23 11.2.0.1.0 YES
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 3 14-Jan-18 14:35:02 46 1.0
End Snap: 4 14-Jan-18 16:00:03 48 1.1
Elapsed: 85.01 (mins)
DB Time: 0.63 (mins)
SQL Summary DB/Inst: RAC/rac1 Snaps: 3-4
Elapsed
SQL Id Time (ms)
------------- ----------
5mycvad72f8qc 18
Module: PL/SQL Developer
SQL ??????
select j.job_id, j.job_title, j.min_salary, j.max_s
alary, e.employee_id, e.first_name||e.last_name as name,
e.email, e.phone_number from jobs j,employees e where j.job_
id = e.job_id
-------------------------------------------------------------
SQL ID: 5mycvad72f8qc DB/Inst: RAC/rac1 Snaps: 3-4
-> 1st Capture and Last Capture Snap IDs
refer to Snapshot IDs witin the snapshot range
-> select j.job_id, j.job_title, j.min_salary, ...
Plan Hash Total Elapsed 1st Capture Last Capture
# Value Time(ms) Executions Snap ID Snap ID
--- ---------------- ---------------- ------------- ------------- --------------
1 303035560 18 2 4 4
-------------------------------------------------------------
Plan 1(PHV: 303035560)
----------------------
Plan Statistics DB/Inst: RAC/rac1 Snaps: 3-4
-> % 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) 18 9.1 0.0
CPU Time (ms) 7 3.5 0.0
Executions 2 N/A N/A
Buffer Gets 23 11.5 0.0
Disk Reads 1 0.5 0.0
Parse Calls 2 1.0 0.0
Rows 214 107.0 N/A
User I/O Wait Time (ms) 9 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 2 N/A N/A
Sharable Mem(KB) 34 N/A N/A
-------------------------------------------------------------
Execution Plan
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100) | |
| 1 | MERGE JOIN | | 107 | 8988 | 6 (17) | 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| JOBS | 19 | 627 | 2 (0) | 00:00:01 |
| 3 | INDEX FULL SCAN | JOB_ID_PK | 19 | | 1 (0) | 00:00:01 |
| 4 | SORT JOIN | | 107 | 5457 | 4 (25) | 00:00:01 |
| 5 | TABLE ACCESS FULL | EMPLOYEES | 107 | 5457 | 3 (0) | 00:00:01 |
-------------------------------------------------------------------------------------------
Full SQL Text
SQL ID SQL Text
------------ -----------------------------------------------------------------
5mycvad72f8q select j.job_id, j.job_title, j.min_salary, j.max_salary, e.emplo
oyee_id, e.first_name||e.last_name as name, e.email, e.phone_numb
er from jobs j, employees e where j.job_id = e.job_id
