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.

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:

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.

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.

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:

Applied Analytics Part 2

The analytic Pivot feature can be used to present useful information in an easy to read format. This is the case when you have information in multiple rows that you would rather display in multiple columns.

For example, recently I wanted to view information about the busiest database objects. When doing performance tuning, this information can be valuable. Oracle provides the v$segment_statistics view to contain performance metrics for each object.

Since I only wanted to review certain metrics and wanted to see all metrics from each object, the following pivot query was created. This query could be ordered by any column to show the top objects for that column's metric. Being able to see the other metrics for each object on the same line made the output more useful.

Example query:
With pivot_stats as (
select owner,object_name,statistic_name,value from v$segment_statistics
)
select * from pivot_stats
PIVOT
(sum(value) for statistic_name in ('logical reads', 'physical writes' ,'row lock waits' ))

Gather Data for SQL tuning

This post will not focus on how to tune SQL statments. There is already a wealth of information about tuning practices. Plus there are many fine tools available such as Profiler that will assist with tuning SQL statements.

I'll give an overview on identifying SQL statements that can benefit from tuning efforts. For this approach I will only assume the use of SQLPlus. These steps are much easier with the appropriate tools, but if you are in a situation where the tools are not available then it is important to know the basic steps to gather information.

The key steps to follow are:

• Use Applied Analytics part 1 to find top sql
• Use additional queries to get information about calling programs
• Use v$sql_bind_capture to get sample bind data
• Use autotrace with SQL Plus to get plan and execution information.

Details:
1.
Use the following query to get the top SQL statements by Logical Reads for the past couple of hours. This is outlined in my post Applied Anayltics part 1

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;

2.
Use the following query to get information about who is calling the SQL statement. This is an important element of troubleshooting. Sometimes you may identify poor statements that are actually program bugs. Getting the calling information can help identify if the statements should even be running at all.

select parsing_schema_name, service,module,action,program_id,program_line#
from v$sql
where sql_id=&SQL_ID_from_1;

3.
If the poor performing statement has bind variables, then you need to see what values are being passed to the statement. Getting this information will allow you to test some real examples of the query.

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;

4.
Run the query in an SQLPlus session with Autotrace set on. This will return an explain plan and execution results after the query finishes.

If the query returns with a good performance, then check against other bind variables. It could be that only certain values result in a poor execution. Also check the execution count. If a query has a small individual impact however the statement is an overall high consumer, then it could be due to an execptionally high number of executions.

If the individual execution demonstrates a performance problem, then steps can be begun to tune the specific SQL.

Applied Analytics Part 1

The Top x query can be applied to administrative queries. This common analytic query will come in handy if you are looking for top resource consumers

For example to see the top 10 largest tables in the database, the following query can be used:
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 <= 10

This query can also be used against system tables such as v$sqlstats and v$segment_statistics to obtain performance information about the database. Using rank you can easily select the data you want.

Optimizing Oracle SQL Class

I have completed the Hotsos Optimizing Oracle SQL, Intensive class this past week. The class was held in San Francisco, taught by Ric Van Dyke. Having worked with Oracle for so long, I am familiar with basic SQL Tuning concepts. This class was a deep dive into advanced topics related to SQL and the Oracle Optimizer.

After completing the course I was surprised at how much I learned during my week. In my most recent job I did less SQL tuning than I had in the past. Although most of my older knowledge was still valid, Ric covered several new items that updated my understanding of the optimizer engine.

Some of the key items I took back from the class include the following:
1. The importance of using Logical I/Os as a key metric for tuning.
2. The best way to quickly interpet the lengthy and confusing Oracle trace files.
3. Numerous tips to improve performance of queries.
4. New ways to structure SQL statements to be even more efficent.
5. A better understanding of how cool analytics really are.

I'd recommend this class for DBAs and Developers.