awr_sql_report_text
I want to see the SQL section for an AWR report without generating the whole report.
Answer: The awr_sql_report_text is a DBMS_WORKLOAD_REPOSITORY function that displays an Automatic Workload Repository (AWR) SQL report as plain text with multiple returned values of VARCHAR2(120).
In the code below, we generate the SQL section for an AWR report between snapshot 1230 and 1231:
Answer: The awr_sql_report_text is a DBMS_WORKLOAD_REPOSITORY function that displays an Automatic Workload Repository (AWR) SQL report as plain text with multiple returned values of VARCHAR2(120).
ARGUMENT
|
TYPE
|
IN / OUT
|
DEFAULT VALUE
|
L_DBID
|
NUMBER
|
IN
| |
L_INST_NUM
|
NUMBER
|
IN
| |
L_BID
|
NUMBER
|
IN
| |
L_EID
|
NUMBER
|
IN
| |
L_SQLID
|
VARCHAR2
|
IN
| |
L_OPTIONS
|
NUMBER
|
IN
|
0
|
Table 7.6: Awr_sql_report_text Parameters
select * from table (dbms_workload_repository.awr_sql_report_text
(&v_db_id, &v_inst_num, :b_snap_id, :e_snap_id, '&v_sql_id'));
select * from table (dbms_workload_repository.awr_sql_report_text
(1692970157, 1, 1230, 1231));
However, Oracle recommends using awrsqrpt.sql instead of awr_sql_report_text.
An easier way to generate an AWR SQL report is$ORACLE_HOME/rdbms/admin/awrsqrpt.sql.
Using awrsqrpt.sql you can generate execution plan from AWR SQL by passing the start snap id, end snap id and SQL ID. You will prompted for this information when you sun the script:
SQL>@ORACLE_HOME/rdbms/admin/awrsqrpt
Comments
Post a Comment