Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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

Comments

Popular posts from this blog

PostgreSQL Pgbadger Installation On Linux

PostgreSQL Sequence

Postgresql maximum size

PostgreSQL pgBadger

How to configure Replication Manager (repmgr) ?