Wednesday, March 8, 2017

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.

Friday, December 16, 2016

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.

Frequent Execution of Recursive Sql Metrics Collector SQL_ID "frjd8zfy2jfdq" in (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)

Wednesday, October 12, 2016

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:

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 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.

Monday, June 13, 2016

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

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

 profile_name => 'pj_test',
 sql_text => 'select 1 from',
 translated_text => 'select 42 from');
 profile_name => 'pj_test',
 sql_text => 'select 2 from',
 translated_text => 'select * from op.dept');

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


 1 rows selected 

SQL> select 2 from;

    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

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

select * from op.dept                                                           
select 42 from 

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

 profile_name => 'pj_test',
 sql_text => 'select 3 from',
 translated_text => 'delete from op.dept where deptno=10');

Running the statement gives an error.
select 3 from;

ORA-00900: invalid SQL statement
00900. 00000 -  "invalid SQL statement"

Thursday, May 5, 2016

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.

Saturday, April 2, 2016


Oracle has a new function advertised with called APPROX_COUNT_DISTINCT. This SQL function is will allow the user to query a rough estimate of a group of distinct values. This feature is supposed to run more efficiently than the current methods.

This post will test that out with a couple of examples. I will look at accuracy and performance to evaluate the functionality. The first test will be of a full scan, the second test will use the SAMPLE clause, and the final test will use the new APPROX_COUNT_DISTINCT function.

To simplify the test I will create a copy of the DBA_OBJECTS table called BIG_TABLE. The query used will group by OWNER while performing a count on distinct OBJECT_ID. To evaluate the accuracy I will reduce the output of 38 rows to 7 key rows.

As a note, when I evaluate the execution plan I will be using the gather_plan_statistics hint to get ALLSTATS from dbms_xplan.display_cursor. When I execute different queries I often use an additional string in the hint to help search v$sql for the SQL_TEXT I want to review. That explains why you will see hints like /*+ gather_plan_statistics a*/.


The queries evaluated were the following:

A. Full Scan
select /*+ gather_plan_statistics a*/ owner, count(distinct object_id)
from big_table
group by owner
order by owner;

B. Using sample size of 20%
select /*+ gather_plan_statistics b*/ owner, count(distinct object_id)
from big_table sample (20)
group by owner
order by owner;

C. Using new feature
select /*+ gather_plan_statistics c*/ owner, approx_count_distinct (object_id)
from big_table
group by owner
order by owner;

The results will show that even though the sample size query ran faster than APPROX_COUNT_DISTINCT, the results of the latter were more accurate. The performance of the full scan had a run time of twice that of APPROX_COUNT_DISTINCT, so the performance was also much improved with the new function.

Detail Results

The details are provided here for anybody interested. Following this I will have a Summary of this information.

-------------------- ------------------------
APEX_050000                              3600
BI                                          8
HR                                         34
LBACSYS                                   237
PUBLIC                                  37125
SYS                                     42110
XFILES                                    141

SQL_ID  07q279qdm8pgp, child number 0                                           
select /*+ gather_plan_statistics a*/ owner, count(distinct object_id)          
from big_table group by owner order by owner                                    
Plan hash value: 2085152455                                                     
| Id  | Operation            | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  
|   0 | SELECT STATEMENT     |           |      1 |        |     38 |00:00:00.30 |    1577 |   1565 |     
|   1 |  SORT GROUP BY       |           |      1 |  90869 |     38 |00:00:00.30 |    1577 |   1565 |    
|   2 |   VIEW               | VM_NWVW_1 |      1 |  90869 |  93680 |00:00:00.27 |    1577 |   1565 |      
|   3 |    HASH GROUP BY     |           |      1 |  90869 |  93680 |00:00:00.24 |    1577 |   1565 |    
|   4 |     TABLE ACCESS FULL| BIG_TABLE |      1 |  90869 |  93680 |00:00:00.18 |    1577 |   1565 |       

-------------------- ------------------------
APEX_050000                               741
HR                                          4
LBACSYS                                    49
PUBLIC                                   7455
SYS                                      8508
XFILES                                     30

SQL_ID  729uapczb0dd2, child number 0                                           
select /*+ gather_plan_statistics b*/ owner, count(distinct object_id)          
from big_table sample (20) group by owner order by owner                        
Plan hash value: 1425374893                                                     
------------------------------------------------------------------------------------------------------------| Id  | Operation              | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | 
|   0 | SELECT STATEMENT       |           |      1 |        |     37 |00:00:00.06 |    1577 |   1565 |     
|   1 |  SORT GROUP BY         |           |      1 |   3635 |     37 |00:00:00.06 |    1577 |   1565 | 
|   2 |   VIEW                 | VM_NWVW_1 |      1 |   3635 |  18904 |00:00:00.05 |    1577 |   1565 |    
|   3 |    HASH GROUP BY       |           |      1 |   3635 |  18904 |00:00:00.05 |    1577 |   1565 | 
|   4 |     TABLE ACCESS SAMPLE| BIG_TABLE |      1 |   3635 |  18904 |00:00:00.03 |    1577 |   1565 |     

-------------------- --------------------------------
APEX_050000                                      3630
BI                                                  8
HR                                                 34
LBACSYS                                           236
PUBLIC                                          37338
SYS                                             42118
XFILES                                            140

SQL_ID  3bmz5b1a9uzqh, child number 0                                           
select /*+ gather_plan_statistics c*/ owner, approx_count_distinct              
(object_id) from big_table group by owner order by owner                        
Plan hash value: 3184991183                                                     
------------------------------------------------------------------------------------------------------------| Id  | Operation            | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | 
|   0 | SELECT STATEMENT     |           |      1 |        |     38 |00:00:00.14 |    1577 |   1565 |      
|   1 |  SORT GROUP BY APPROX|           |      1 |  90869 |     38 |00:00:00.14 |    1577 |   1565 |   
|   2 |   TABLE ACCESS FULL  | BIG_TABLE |      1 |  90869 |  93680 |00:00:00.09 |    1577 |   1565 |     


To summarize the information seen


Method Run time
Count Full 0.30
Count Sample 0.06
Count Approx 0.14


Owner Count full Count sample Count sample*5 Count Approx
APEX_050000 3600 741 3705 3630
BI 8 0 0 8
HR 34 4 20 34
LBACSYS 237 49 245 236
PUBLIC 37125 7455 37275 37338
SYS 42110 8508 42540 42118
XFILES 141 30 150 140


The new function runs faster than the full scan and it is more accurate than using the sample feature. With the 20% sample, the BI records were not even seen. If you multiple the 20% sample by 5 you can get an estimate of the full table values with this method, but the values are not as close as the APPROX_COUNT_DISTINCT.

If you need to get a rough estimate of counts in a table and you are using a version >= then you should consider this feature. If exact numbers are still needed then you will need to perform a full scan.

Sunday, March 13, 2016

Hotsos Symposium 2016 Wrap-up

Another Hotsos symposium is in the books. There were a so many great sessions that it is hard to quickly outline things. In addition to the presentations, there was a wonderful Training Day by Richard Foote.

The topic of indexes (Richard Foote's training day material) is more vast than we realize when doing day-to-day work in Oracle. Richard went through a great deal of information on indexing. During the course of his day long session he debunked many widely held myths about indexes. For example, is it best with a composite index to have the most selective values at the start of the index? Are you sure about that...?

This basic information is critical to supporting an Oracle system. A long standing belief of mine is that people should spend more time on the fundamentals of database design and performance rather than going down rabbit holes of features or techniques that are often not really needed. Many times I see people using these rare techniques to resolve issues that sometimes have much simpler fixes.

Throughout the conference there were a mix of presentations from veteran presenters and new participants. The typical big names including Tanel Poder, Kerry Osborne, Jeff Smith, et al provided the high quality presentations we are used to seeing from them. As usual for the Hotsos Symposium, the content was performance centric however it covered a wide variety of topics.

The training day wrapped up the Symposium but the start was from Kellyn Pot'Vin-Gorman and ThatJeffSmith on the importance of social media presence. Their presentation served to remind me of how infrequent I have been blogging and interacting in the social media space lately. As a result of their wonderful presentation, I will be working on doing more than a just few blog posts a year.

In addition to the increased social media activity I also plan on being more active with presentations and articles. I have done quite a bit in the past, but have taken a little bit of a break lately.

I guess that is the sign of a good conference... Being more energized when it is over.