Database Reference
In-Depth Information
@$ORACLE_HOME/rdbms/admin/awrrpti
...
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 3821847422 2 PROD PROD2 db1
3821847422 3 PROD PROD3 db2
3821847422 1 PROD PROD1 db3
Enter value for dbid: 3821847422
Using 3821847422 for database Id
Enter value for inst_num: 3
Using 3 for instance number
To generate an AWR report for the current instance, you can call awrrpt.sql file.
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
To generate an AWR report for all instances, you can execute awrrptg.sql script. This script pulls the data for all
active instances, prints metrics at both instance levels, and groups at cluster level. The AWR report generated from
this script is useful to compare the performance of instances quickly.
@$ORACLE_HOME/rdbms/admin/awrrptg.sql
...
ADDM is executed after AWR report capture automatically. You can review the recommendations from ADDM
with ease using the OEM tool. You can also use SQL*Plus tool to retrieve last task_name and retrieve the ADDM report.
SQL> select task_name, status from dba_advisor_tasks where task_id=
(select max(task_id) from dba_advisor_tasks where status='COMPLETED' );
ADDM:4227381283_3_59788 COMPLETED
SQL> SET LONG 1000000 PAGESIZE 0;
SQL> SELECT DBMS_ADDM.GET_REPORT('ADDM:4227381283_3_59788') from dual;
ASH Reports
Similar to AWR reports, you can also generate ASH reports for the local instance or a specific instance. To generate an
ASH report for a specific instance, use ashrpti.sql script.
@$ORACLE_HOME/rdbms/admin/ashrpti.sql
To generate an ASH report for a current instance, use ashrpt.sql.
@$ORACLE_HOME/rdbms/admin/ashrpt.sql
