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.

Watch out for these 12c bugs

A quick little post, but I think this is an important one as most people have upgraded/plan to upgrade to 12c

Oracle performance can be impacted by Oracle itself. People are often too quick to jump to the conclusion that bad performance is the result of a bug. That does not mean that there aren't situations where bugs do occur.

In 12c I have seen a couple of instances where bugs are using a noticable amount of system time. This consumes CPU cycles and potentially takes away time from other processes.

Looking at top statements and top wait events can lead to identifying potential bugs. With the 12c bugs being encountered they can be seen using AWR, ASH, or queries against system views to identify the top statements by Elapsed Time or other key metrics.

Once these key consumers of time are identified it is worth the effort to perform a quick search on My Oracle Support to see if there are known issues associated with this issue. There may be a patch or workaround identified that will resolve the bug or "performance feature".

A couple of examples seen recently are related to SQL Ids frjd8zfy2jfdq and 47r1y8yn34jmj. There are a couple of 12c patches which can improve this activity.

See
Frequent Execution of Recursive Sql Metrics Collector SQL_ID "frjd8zfy2jfdq" in 12.1.0.2 (Doc ID 2059121.1)
Higher CPU and Slow Performance after Migration to 12c with Top Sql as SELECT DEFAULT$ FROM COL$ WHERE ROWID=:1 (Doc ID 2013851.1)

Don't Forget System Level Statistics

If you do any work with performance tuning, you know the importance of Object Level statistics. Oracle uses this information to estimate how many rows will be returned by different steps in the plan. These estimates help the Optimizer form what it thinks is the best plan.

There are numerous subtopics that can have large impacts on SQL Statements. One statistics related item that is often overlooked is System Level Statistics.

When I talk about system level statistics I am referring to three different things:
1. Fixed object statistics
2. Data Dictionary statistics
3. System stats.

If these items are not kept up to date, then you can see some performance degradation. Oracle has several blog posts detailing the importance of these statistics. These statistics should be updated following upgrades to the database software. The system statistics should be updated following any hardware changes.

Fixed Object Statistics

This is different from user level object statistics. For example if you are querying the EMP and DEPT tables then you usually will want to have accurate statistics on the tables which reflect the data distribution of values in the columns of those tables. I say usually because there may be exceptions to this rule.

You can check the status of the fixed object statistics using the following query:
SELECT owner
     , table_name
     , last_analyzed
FROM   dba_tab_statistics
WHERE  table_name IN
             (SELECT name
              FROM   v$fixed_table
              WHERE  type = 'TABLE'
             )
ORDER BY last_analyzed NULLS LAST;

Fixed Objects are the X$ objects defined in the database. These objects are not owned by regular users, so gathering stats on user level objects will not impact these objects. There is a different command, EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS, that will gather stats on these objects. The statistics should be run while the system is performing a normal workload. If there are significant updates to the workload then the statistics should be regathered. Also, the statistics should be regathered following any upgrades.

For more information about the importance of fixed objects can be seen here:
https://blogs.oracle.com/optimizer/entry/fixed_objects_statistics_and_why

Data Dictionary Statistics

Similar to the fixed objects, data dictionary statistics are also used by the system to build plans for system queries. If these are not kept up to date you will see some performance issues related to queries against those objects.

You can check the status of data dictionary statistics with the following query:
SELECT owner
     , table_name
     , last_analyzed
FROM   dba_tab_statistics
WHERE  owner='SYS'
ORDER BY last_analyzed NULLS LAST;

The command to update these statistics is DBMS_STATS.GATHER_DICTIONARY_STATS.

At a minimum this should be run after every database upgrade. To keep these statistics up to date, you may choose to run the gather command more frequently such as every quarter.

For additional information you can also look at the My Oracle Support Document, How to Gather Statistics on Objects Owned by the 'SYS' User and 'Fixed' Objects (Doc ID 457926.1)

System Statistics

The system statistics collect information about the hardware, in particular CPU performance and I/O performance. The Oracle optimizer will use this information to determine the cost of a statement. If this data is not accurate then it can result in suboptimal plans. This data is collected with the command EXEC DBMS_STATS.GATHER_SYSTEM_STATS. As part of any hardware changes or database upgrades, these statistics should be recollected. Note that if you are running on Exadata, you should use the command EXEC DBMS_STATS.GATHER_SYSTEM_STATS(‘EXADATA’).

Warning! There is a case where a bug caused the command to collect inaccurate information. Oracle bug 9842771 impacted the system statistics collected in release 11.2.0.2. So it is advised to review the values before and after collecting.

To check the system statistics use the following query

select    *
from    sys.aux_stats$;

These fixed object and system statistics provide a foundation that is used by the optimizer to impact many different statements. If you are collecting user level objects but not keeping the other statistics up to date then you are putting yourself at risk for poor performance.

Fun with SQL Translation Framework

An interesting feature in 12c that is not talked about a lot is SQL Translation Framework. Kerry Osborne has an excellent blog post on this feature that should also be reviewed.

The advertised purpose of this software component is to allow statements written for another database to be translated into Oracle standard SQL. This will aid in migrations of programs from other database systems. In fact there is some relation with Oracle SQL Developer and its application scanner scripts in order to facilitate this sort of activity.

The SQL Translation Profile is a database object that contains the non-Oracle statements along with their translations. The profile can also change Oracle standard statements to different statements. I'll focus on the second feature of the component as it provides some interesting results.

Before parsing, the Translation framework will replace the text. There are some translations that are invalid, we'll look at one of those below as well.

To use this component the framework needs to be enabled. The session then needs to be altered to allow for the framework to be used. The steps are outlined as follows. A framework will be created and then access will be granted to a database  user, OP. After the setup is completed some translations will be created.

 exec dbms_sql_translator.create_profile('pj_test');

 grant all on sql translation profile pj_test to  op;

 alter session set sql_translation_profile=pj_test;
 alter system flush shared_pool;
 alter session set events = '10601 trace name context forever, level 32';


If there are any issues with the setup you will see messages like the following:
ORA-24252: SQL translation profile does not exist
24252. 00000 -  "SQL translation profile does not exist"
*Cause:    An attempt was made to access a SQL translation profile that
           either did not exist or for which the current user did not have
           privileges on.
*Action:   Check the SQL translation profile name and verify that the
           current user has appropriate privileges on the SQL translation
           profile.

You can change the statement slightly or you can access a totally different query by assigning a translation.

 begin
 dbms_sql_translator.register_sql_translation(
 profile_name => 'pj_test',
 sql_text => 'select 1 from op.vc',
 translated_text => 'select 42 from op.vc');
 end;
 /
 begin
 dbms_sql_translator.register_sql_translation(
 profile_name => 'pj_test',
 sql_text => 'select 2 from op.vc',
 translated_text => 'select * from op.dept');
 end;
 /

Now we can see the output
SQL> select 1 from op.vc;

        42
----------
        42

 1 rows selected 

SQL> select 2 from op.vc;

    DEPTNO DNAME          LOC         
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK     
        20 RESEARCH       DALLAS       
        30 SALES          CHICAGO      
        40 OPERATIONS     BOSTON       

 4 rows selected 

If I look in the data dictionary to see what has been run (factoring out some other unrelated queries) I see the following translations in place, but not the original statements (e.g. select 1 from op.vc).

SQL> select sql_text from v$sql where sql_text like 'select%from op.%';

SQL_TEXT                                                                       
--------------------------------------------------------------------------------
select * from op.dept                                                           
select 42 from op.vc 


You can imagine that this could be quite a security risk, if somebody were to translate a select statement into a delete/update/insert. Fortunately that type of translation is not allowed. For example, when I translate a select into a delete

 begin
 dbms_sql_translator.register_sql_translation(
 profile_name => 'pj_test',
 sql_text => 'select 3 from op.vc',
 translated_text => 'delete from op.dept where deptno=10');
 end;
 /

Running the statement gives an error.
select 3 from op.vc;

ORA-00900: invalid SQL statement
00900. 00000 -  "invalid SQL statement"
*Cause:    
*Action:

Tips for travel (update)

This is a followup to a Tips for Travel post I had created several years ago.

Over the course of time there have been technology changes that I wanted to address. Also I have accumulated additional advice that I think will be helpful updates to the prior tips.

Technology updates
I still use my iPhone heavily when I travel. The apps I use like CamScanner and Maps continue to be some of the most important items on my phone. One update is that Maps will provide audio prompts which is much safer than trying to check the phone while driving.

Some other apps that are useful include the apps provided by airline, hotel, and rental car companies. I often use these apps to make travel changes without having to find a computer or connection for my laptop. With the airline apps I will check in with my phone and save the boarding pass to my Wallet app. This reduces the paper I have to print and carry.

The other apps I use most often are Audible, SiriusXM, and Podcasts. I have a subscription with Audible which provides me with 2 audiobooks every month. A typical book is around 15 hours which gives you plenty of entertainment while you are traveling. We have Sirius radio in our car, so I downloaded the Sirius app which allows me to listen to music, talk radio, etc while I travel. Note that if you are not connected to wifi, then the Sirius app will use quite a bit of data. Finally there are several Podcasts I listen to on a regular basis. The latest episodes can be downloaded to your device when you are connected to wifi then listened to at your leisure in order to save data usage.

Lastly, I am a fan of Amazon Prime. With my Kindle device I can download movies and TV shows I've purchased, but also with a prime membership you can download movies or shows that are available for streaming. This allows you to watch something when you are not connected to wifi, such as on a plane or sitting in an airport. I have noticed that my Bose in-ear headphones allow me to hear the shows much better, especially when I'm on a plane. Also as a prime member I can use the Kindle lending library to read some books for free. Note the selection is pretty weak, but you can probably find something that would do in a pinch.

Travel updates
Enroll in TSA PreCheck. Most airports have different lines for PreCheck which will allow you to get to your gate much faster than going through regular security. If you travel internationally then you should look into the Global Entry program, but for domestic flights PreCheck is all you need.

Accept the fact that you will have flight delays. Between weather issues, mechanical problems, and cascading delays it's almost surprising that flights leave on time at all. When you want to get home or if you are in a hurry to get to a client's site, delays can be very frustrating but you have to remind yourself to accept the things you cannot change. Stressing about these delays only hurt yourself. Given that, you should always check your flight connection times to ensure that you give yourself enough time to reach your next gate.

I don't have memberships to the airline clubs, but with their credit cards you can get a one-time pass for a reasonable amount of money. If I know I have a multi hour delay I will sometimes take advantage of that feature to spend time in the clubs. The free house drinks, free wifi, snacks, and comfortable chairs makes those delays pass by much faster. With a good audiobook or a downloaded movie on my Kindle, the time goes by even faster.