Easy ways to get Oracle performance reports

Everybody has standard ways to run Oracle provided performance reports. Personally I like to run them from the database server in $ORACLE_HOME/rdbms/admin. When I don't have access to the server then I need to run from another location. Working with multiple sites, the level of access granted varies from client to client.

One shortcut I often use is to run the queries from a client tool. I prefer to use SQL Developer, so if I can run the reports from there and save the output then that's the approach I take.

There are some common performance related reports that you can easily get from a query tool. I commonly use SQL Developer to gather these reports. The SQL Developer has options that allow for running some of these in a Reports window, but I prefer to run them as queries because it gives more flexibility.

The following are a few different types of reports I run as queries. Note that you'll need the appropriate licensing for these reports.

With the dbms_workload_repository package you can run AWR and ASH reports for the instance. You'll just need to get the DBID and snapshot numbers prior to executing the procedures. Since I use SQL Developer I just copy the output from the Query output and paste into a text file. If you use SQL Plus or another tool then you'll need to properly format the output

select output from table(dbms_workload_repository.awr_report_text( :dbid, :instnum,:startsnap, :stopsnap, 0));

You can adjust the timings for the ash report. I usually use 60 minutes or less for ASH reports.

select * from table(dbms_workload_repository.ash_report_text(:dbid, :instnum, SYSDATE-30/1440, SYSDATE-1/1440));

Using the dbms_sqltune package you can run SQL Monitor reports with a variety of output. A list report can be generated, but I get most benefit from the report_sql_monitor procedure. This procedure can be run with a variety of TYPE values, usually TEXT will provide enough information to troubleshoot issues, but if not the EM type will provide some additional data. With HTML or EM formatted output you can paste the data into a *.html file. 

select dbms_sqltune.report_sql_monitor(sql_id=> :sqlid, type=>'TEXT') from dual;

Some more information on SQL Monitoring will be provided in a later blog post.