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).

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

In the code below, we generate the SQL section for an AWR report between snapshot 1230 and 1231:

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