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.

Utilize Oracle Provided Health Checks

Over time you will develop custom scripts to monitor database activity for tuning purposes. There are several health checks that you will determine are important based upon your experiences with the database.

Oracle provides a lot of different tools that can also be used to check on settings, server configuration, etc. This post will review a couple of those tools. Note that these tools are updated periodically by Oracle so be sure you have the latest version prior to running.

As with Automatic Database Diagnostic Monitor (ADDM), it is important to review the output to determine if the identified items are really an issue or not. Some expert interpretation is still required when using these tools to determine if the recommendations are valid for your system.

The two Oracle provided health checks covered here are ORAchk and DBSAT. The output will not be discussed in detail because that could account for a whole blog post for each script.

ORAchk
For detail information review My Oracle Support note: ORAchk - Health Checks for the Oracle Stack (Doc ID 1268927.2).

This script will check OS and DB settings. If you are using RAC, Exadata, E-Business Suite, then this script will check settings of those products. This will also run the parallel processing health check

To run this script follow note details. The script will output an HTML file that can be viewed at any time. Note that there are many sections of this report that should be reviewed.

DBSAT
For detail information review My Oracle Support note: Oracle Database Security Assessment Tool (DBSAT) (Doc ID 2138254.1).

This script runs a variety of security checks. Compared to ORAchk, this script is a little more involved to run. It requires a version of Python > 2.6. 

The script is run in two parts: the Collector and the Reporter. The first step collects information about the system with statements run on the database and server. The second part analyzes the data and builds the output. 

These outputs can be encrypted to provide an extra level of security. The output can be large, so take time to review the findings.


In conclusion, even if you have a robust, custom built set of health checks you should incorporate these tools into your review process. Oracle will provide up to date checks that might be outside of the scope of items you review.

Carefully review the output to determine what action items are needed based upon Oracle's findings.

Is Oracle Picking The Wrong Index?

At the 15th annual Hotsos Symposium I was fortunate enough to do a presentation on the second day. My presentation focused on using SQL Monitoring reports to tune statements. I went over numerous examples to show useful information provided by the reports.

One of the examples is common enough that I wanted to include in a blog post. Statements picking the wrong index is very easy to see in the report. A couple of screen shots will clearly show that time is being spent due to a poor index choice. The columns of note are drastic changes in the Actual Rows or a high percentage of Activity.

In the following report snippet you can see from the Activity percentage column that most time is spent on the step filtering out index results in the table access. Using the index access there are 23 million rows returned which are then filtered at the table level to only leave 8 rows. This is a lot of work performed on the database to return a small number of rows.

Now the problem is clear but the solution may be more difficult. The resolution will depend on the actual problem.  Either a better index exists that Oracle is not selecting due to some issue or a better index needs to be created. Some examples for resolving this problem include:
1. Update object level statistics
If there is a better index to be chosen then this step may be enough to help the optimizer make the best choice.

2. Create extended column statistics and histogram
If there is a better index that is not being chosen, you may need to create extended column statistics with a histogram. Oracle may be making a poor estimate on rows returned if there are two or more related columns. The extended column statistics and histogram will give the optimizer the information it needs to build the best plan.

3. Create a better index
If there is no existing index that returns a smaller number of rows, then you may need to create an index on the appropriate columns. You can look at the filter predicates on the table to determine which columns to index. In the SQL Monitoring report you can click on the green filter icon on the plan line to see which columns should be added to the index. To limit the rows indexed, the columns can be investigated to determine the minimum number of rows need to limit the rows returned.