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.

APPROX_COUNT_DISTINCT overview

Oracle has a new function advertised with 12.1.0.2 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*/.

Queries

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.

A:
OWNER                COUNT(DISTINCTOBJECT_ID)
-------------------- ------------------------
APEX_050000                              3600
BI                                          8
HR                                         34
LBACSYS                                   237
PUBLIC                                  37125
SYS                                     42110
XFILES                                    141


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


B:
OWNER                COUNT(DISTINCTOBJECT_ID)
-------------------- ------------------------
APEX_050000                               741
HR                                          4
LBACSYS                                    49
PUBLIC                                   7455
SYS                                      8508
XFILES                                     30



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



C:
OWNER                APPROX_COUNT_DISTINCT(OBJECT_ID)
-------------------- --------------------------------
APEX_050000                                      3630
BI                                                  8
HR                                                 34
LBACSYS                                           236
PUBLIC                                          37338
SYS                                             42118
XFILES                                            140



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

Summary

To summarize the information seen

Performance

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

Accuracy

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

Conclusion

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 >= 12.1.0.2 then you should consider this feature. If exact numbers are still needed then you will need to perform a full scan.

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.

New Oracle Top-N query options

What is the best way to get the Top-N values from a table in Oracle? In the past the answer to this question has always been a little convoluted. There are a couple of clear ways to perform this task but they can feel kludgey when explaining them to an inexperienced Oracle developer or analyst. The processes work, but they don't seem as clear cut as they should.

With 12c Oracle has added a feature to allow for Top-N queries with FETCH FIRST|NEXT|PERCENT clauses. This approach appears more elegant than the "old" ways since it is easier to explain. Also there is some nice functionality with this syntax to allow for offsetting the values returned, including duplicate values, and even allowing to fetch the top X percent of values. See the Oracle documentation for examples.

In this post I want to do a quick comparision between the new Top-N approach and some older approaches. In the example I ran one case of the old approach ran much better when a particular index was involved, so it goes to show that something "new" isn't always "better". 

As with any feature, be sure to investigate the performance against representative amounts of data as you develop.

In the following example I'll query against big_tab which is a copy of the dba_objects table. After initial tests I'll unhide an index and see that only one query uses that index.

Note, all of the queries return the same data so there are no issues seen with functionality.

The tests were using the parameters
compatible                12.1.0.0.0
optimizer_features_enable 12.1.0.1

The FETCH FIRST clause runs in 2.4 seconds and does 48,190 logical reads. This is comparable to the other methods investigated which included a RANK() clause and the older subquery with rownum<=10.

Option 1.
SELECT /*+ gather_plan_statistics*/  owner, object_id FROM op.big_tab
ORDER BY object_id DESC    
FETCH FIRST 10 ROWS ONLY

Option 2.
select /*+ gather_plan_statistics */ owner, object_id
from
   (select  owner, object_id, rank() over (order by object_id desc ) as rank
   from big_tab )
where rank <=10

Option 3.
select /*+ gather_plan_statistics */  stage.owner, stage.object_id   
from ( select owner, object_id from op.big_tab order by object_id desc) stage
where rownum <=10

The detailed execution plans are listed out in the following sections. These show the details for Actual time and Buffer Gets to allow for comparing behavior.

Execution plan for Option 1.

SQL_ID  7thwrzawu40gz, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics*/  owner, object_id FROM op.big_tab
ORDER BY object_id DESC     FETCH FIRST 10 ROWS ONLY

Plan hash value: 4205637774

----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |         |      1 |        |     10 |00:00:02.40 |   48190 |  48185 |       |       |          |
|*  1 |  VIEW                    |         |      1 |   2866K|     10 |00:00:02.40 |   48190 |  48185 |       |       |          |
|*  2 |   WINDOW SORT PUSHED RANK|         |      1 |   2866K|     10 |00:00:02.40 |   48190 |  48185 | 80896 | 80896 |71680  (0)|
|   3 |    TABLE ACCESS FULL     | BIG_TAB |      1 |   2866K|   2866K|00:00:08.15 |   48190 |  48185 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("OBJECT_ID") DESC )<=10)
 
Execution plan for Option 2

SQL_ID  4y49s6tk4crha, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ owner, object_id from (select
owner, object_id, rank() over (order by object_id desc ) as rank from
big_tab ) where rank <=10

Plan hash value: 4205637774

----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |         |      1 |        |     10 |00:00:02.47 |   48190 |  48185 |       |       |          |
|*  1 |  VIEW                    |         |      1 |   2866K|     10 |00:00:02.47 |   48190 |  48185 |       |       |          |
|*  2 |   WINDOW SORT PUSHED RANK|         |      1 |   2866K|     11 |00:00:02.47 |   48190 |  48185 | 95232 | 95232 |83968  (0)|
|   3 |    TABLE ACCESS FULL     | BIG_TAB |      1 |   2866K|   2866K|00:00:08.52 |   48190 |  48185 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RANK"<=10)
   2 - filter(RANK() OVER ( ORDER BY INTERNAL_FUNCTION("OBJECT_ID") DESC )<=10)

Execution plan for Option 3

SQL_ID  7x3nv0d1r475r, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */  stage.owner, stage.object_id   
from ( select owner, object_id FROM op.big_tab ORDER BY object_id DESC)
stage   where rownum <=10

Plan hash value: 2431194888

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |         |      1 |        |     10 |00:00:02.54 |   48198 |  48187 |       |       |          |
|*  1 |  COUNT STOPKEY          |         |      1 |        |     10 |00:00:02.54 |   48198 |  48187 |       |       |          |
|   2 |   VIEW                  |         |      1 |   2866K|     10 |00:00:02.54 |   48198 |  48187 |       |       |          |
|*  3 |    SORT ORDER BY STOPKEY|         |      1 |   2866K|     10 |00:00:02.54 |   48198 |  48187 | 80896 | 80896 |71680  (0)|
|   4 |     TABLE ACCESS FULL   | BIG_TAB |      1 |   2866K|   2866K|00:00:13.03 |   48198 |  48187 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=10)
   3 - filter(ROWNUM<=10)

Note that when an index was created on the object_id field Options 1 and 2 did not utilize the index. Option 3 (with the subquery), however, did utilize the index as we can see below. This is an interesting result that should be kept in mind as these types of queries are developed.

Execution plan for Option 3 (utilizing index)

SQL_ID  7x3nv0d1r475r, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */  stage.owner, stage.object_id   
from ( select owner, object_id FROM op.big_tab ORDER BY object_id DESC)
stage   where rownum <=10

Plan hash value: 1151062491

------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |      1 |        |     10 |00:00:00.01 |       4 |      4 |
|*  1 |  COUNT STOPKEY                |         |      1 |        |     10 |00:00:00.01 |       4 |      4 |
|   2 |   VIEW                        |         |      1 |     10 |     10 |00:00:00.01 |       4 |      4 |
|   3 |    TABLE ACCESS BY INDEX ROWID| BIG_TAB |      1 |   2866K|     10 |00:00:00.01 |       4 |      4 |
|   4 |     INDEX FULL SCAN DESCENDING| BIG_IDX |      1 |     10 |     10 |00:00:00.01 |       3 |      3 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=10)


In conclusion, in most cases it looks like the new FETCH FIRST query will run just as well as the pre-12c methods for collecting the TOP-N queries. In this test, the subquery method (Option 3) was able to utilize an index the others could not, so if there are performance consideration you should check the plans against production level amounts of data.

Urge to Purge Part 3

Another area to help identify tables that can be purged is for high number of similar tables. The query below will identify tables with similar names, note that you may need to adjust the numbers to better identify such tables. Some examples of cases like this from Oracle's E-Business Suite are listed out below.

The query to use:
select substr(table_name, 1, 15), count(1)
from dba_tables
group by substr(table_name, 1, 15) having count(1) > 30
order by 2 desc;

In Oracle E-Business Suite there are several cases where interim and temp tables are not being cleaned out by the application. Below are a few cases that depend on the module used and the current release of the applications.

There are a couple of groups of temporary tables that have not been cleaned out by Oracle. These tables can be manually removed, but review notes indicated below to check that. The tables may not be causing any significant issues, they are just consuming space on the system. These objects can also slow down gathering object level statistics if there are a large number of them.

A.            FA_JOURNALS_INTERIM_xxx
Review note FA_JOURNALS_INTERIM Tables are Not Dropped after Successful Create Journal Entry and Journal Import (Doc ID 757666.1)

B.            GL_CONS_INTERFACE_xxxx
Review note Many Interim Tables and Indexes are filling up the GL and GL_NDX tablespaces (Doc ID 287534.1)

C.            GL_POSTING_INTERIM_xxx
Review note GL_POSTING_INTERIM_xxxx Tables are Not Being Dropped after Successful Posting (Doc ID 364040.1)

D.            XLA_GLT_xxx tables not being cleaned up by oracle
Review note XLA_GLT_XXXX Temporary Tables For Purge Program 'Purge Temporary Journal Import Interface Tables' Not Dropped (Doc ID 1158934.1)

Tuning Advice - Be Sure to Check the Bind Variables

An occasional problem I encounter is with queries doing too much work due to the bind variables used. When looking at queries performing a high number of reads, part of the investigation should include a review of all of the bind variables passed into the query.

Recall from older posts that the top queries can be identified in AWR/Statspack reports or by running a query against the data dictionary such as:

select buffer_gets, elapsed_time,cpu_time,executions,sql_id,sql_text
from (select buffer_gets, elapsed_time,cpu_time,executions,sql_id,sql_text, rank() over
(order by buffer_gets desc
) as rank from v$sqlstats
where last_active_time > (sysdate - 1/12)
) where rank <=20;

A trace with binds and waits will give you information about the bind variables that were used for a query. You can also query v$sql_bind_capture. If you have the appropriate license you can query dba_hist_sqlbind to get the bind variables for older queries.

select child_number,position,name,datatype_string,value_string 
from v$sql_bind_capture 
where sql_id=&SQL_ID_from_1
order by child_number,position;

When the bind variables have been identified, check the occurrences of those values in the table columns being queried. A value that is heavily skewed could represent a programming issue if too much data is being read or retrieved. Also any date variables that are causing the query to read multiple years worth of data should be reviewed.

Sometimes the fix to a query performing high reads is a programming change to alter how those queries are used. If bad variables are used against a query, then you will be judged for the performance issues created.

The Smith family seeing a query using bad variables:

Urge to Purge part 2

Part 1 of this blog post discussed using standard purge functions to reduce overhead on the system. A query to identify the largest objects was provided as a starting point for purge investigations.

A couple of other items are worth reviewing. Often systems will have backup tables created to assist with troubleshooting. These tables should be removed when they are no longer needed.

One way to locate backup tables is to run a query like the following. It may not catch all tables depending upon the naming standards, but it is a good place to start. The list that gets returned may include valid tables, so be sure to check to determine if the tables are being used. You can also edit this query to use other possible strings such as TEMP, TMP, ARCH, ARCHIVE, HIST, BACK, OLD, etc.

select * from dba_segments
where owner not in ('SYS','SYSTEM')
and segment_name like '%BKP%'
and segment_type='TABLE'
order by bytes desc;

Another place to review, involves the log files for materialized views. Sometimes these logs can become quite large. There are a few options to reclaim the space, review Oracle notes for cleaning up materialized view logs.  If you are using E-Business Suite, look up the view name in My Oracle Support as there may be additional steps needed to reclaim the space. The following query will list the materialized view logs ordered by size. Focus on only the largest objects.

select *
from dba_segments
where segment_name like 'MLOG$_%'
order by bytes desc;

Urge to Purge part 1

Purge is a four letter word in many organizations. Resistance to purging data is understandable. The technical staff will be at odds with the functional groups if there is ever a time when needed data is no longer available.

The penalty against purging is performance degredation and additional storage requirements. The additional storage also has an adverse effect on backup and recovery operations. In addition to purchasing additional storage, companies often have to purchase additional CPU which can also impact licensing costs.

If you're not sure of the best place to start your purge investigation, for EBS customers you should be purging as much of the Concurrent Request histor and Workflow history as possible. Next you should investigate the purging options for the features that are most heavily used at your site.

If you are unsure of which modules result in excessive storage usage, then check for the largest objects in your database. Based upon the tables returned, you can then use My Oracle Support to check for appropriate purge routines.

A simple query to check for the 50 largest objects:
select owner,segment_name, segment_type, bytes
from (select owner,segment_name, segment_type, bytes, rank() over
(order by bytes desc
) as rank from dba_segments
) where rank <=50;

Hotsos Symposium 2013

Good news! I found out today that I will get to present at the 2013 Hotsos Symposium. I was slotted in as a fill-in speaker for another presentation that had to cancel.

The Symposium is always a great event. This conference focuses totally on practical performance tuning and database management topics. I'm proud to be part of the company that hosts this event. Last year's Tenth Annual event was great.

My presentation, "Hunting Down Performance Problems", is suitable for beginner - intermediate level DBAs. The presentation will focus on examples of performance problem identification and resolution. I hope to see some of you there.

For more information see -My Speaker profile

OOW 2013 Update

Oracle Open World is coming up very soon. If you are going, be sure to stop by the EBS Application Technology Stack SIG meeting on Sunday. The details for the meeting are:

Sunday, September 30th
10:30 to Noon
Moscone West 3018

As usual, there will be a lot of great content from Oracle provided during the SIG meeting. The Special Interest Group meeting typically allow for more interaction with the speakers and other attendees.

Speaking of conferences, the Hotsos Symposium is taking submissions for next years conference.

Symposium speaker submission

If you are not interested in speaking, you can still register to attend.

Also, on the Hotsos front, be sure to check out the latest newsletter. There are a lot of big updates from Hotsos.

Hotsos Newsletter

My COLLABORATE 12 Session

I had a lot of fun presenting my topic to the attendees at COLLABORATE 12 this year in Vegas.
My presentation can be found at the following location:
http://mwsig.oaug.org/WhitePapers/keep_it_simple_pjackson.pdf

There are a lot of My Oracle Support notes and SQL commands included in the presentation. Check them out and let me know if you have any questions or issues.

COLLABORATE 12 Info

After a very informative and enjoyable Hotsos Symposium, the next educational opportunity for me is COLLABORATE 12 in Las Vegas.

There are a couple of sessions worth specific mentioning in the blog.

1. On Monday 4/23, I will be presenting a Performance Tuning session called "Keep it Simple - Common, Overlooked Performance Tuning Tips". The session will be from 9:45 AM - 10:45 AM in South Seas E. I will be covering a variety of performance tuning tips and recommendations.

2. Also on Monday 4/23, The OAUG EBS Applications Technology SIG will be presenting a two part SIG meeting from 2:30 PM - 4:45 PM in South Seas F. During this SIG meeting there will be two high quality presentations from Oracle. For EBS customers, attend one or both meetings. I have been a board member of the EBS Applications Technology SIG since its inception. I currently serve as the vice-president of the board.

I hope to see you at COLLABORATE 12.

Hotsos Symposium Update

Hotsos has sent the following information out in an email. I wanted to post on this site in case any of you are not on the email list. Here is the body of the message:

Hotsos Symposium 2012 is just around the corner and the early registration discount expires this Friday (February 10, 2012).  Register now for a fun week in Dallas with the world’s top Oracle performance talent.

We are tweeting about Hotsos Symposium this year.  Follow @hotsosgg or #HotSym12 to keep up-to-date on Symposium events and what our speakers have to say.

Once again we have a great line-up of speakers.  You'll want to hear returning speakers Tom Kyte, Tanel Poder, Alex Gorbachev, Doug Burns, Cary Millsap, Tim Gorman, Maria Colgan, Neil Gunther, Wolfgang Breitling and many more.  New speakers this year include Baron Schwartz, Carlos Sierra, Iordan Iotzov and more.  Visit https://webmail.hotsos.com/exchweb/bin/redir.asp?URL=http://cts.vresp.com/c/?HotsosEnterprisesLtd/df35405401/37d4d9c301/b115417b12 for the complete list.

Jonathan Lewis is leading the optional Training Day on March 8, 2011.  His topic is “Designing Optimal SQL”.  Read more about this topic at https://webmail.hotsos.com/exchweb/bin/redir.asp?URL=http://cts.vresp.com/c/?HotsosEnterprisesLtd/df35405401/37d4d9c301/7724722b2f.

For more information, contact us at +1.817.488.6200 x520 or symposium@hotsos.com. We're looking forward to seeing you all!