BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 

 

 

Removing Sorts by Adding Indexes

Oracle Tips by Burleson Consulting

One of the best ways to remove full-table scans and unnecessary sorts is by adding indexes. When operating in first_rows mode, Oracle will always try to resolve an order by clause by using an index in lieu of a sort operation. However, you must always remember that while an index will retrieve the rows far faster than a sort, the index scan will have slower overall throughput than the sort operation. We will go into greater detail on this topic in Chapter 20.

Unnecessary Sorts

There are many cases when Oracle performs a sort operation even though a sort is not required. This generally happens when one of the following conditions is present:

  • Missing index Many Remote DBAs are not aware that a column index is required for a query until they begin SQL tuning.

  • Sort merge join Anytime a sort merge join is requested, a sort will be performed to join the key values. In many cases, a nested loop join is a better choice because it is more efficient and does not require sorting or full-table scans.

  • Using the distinct clause  Using the distinct clause on a query will always invoke a sort to remove the duplicate rows. There are many documented cases of SQL that have a distinct clause even though there can never be duplicate rows in the result set.

Once we have ensured that a sort is legitimate and cannot be removed with an index, we can then turn our attention to monitoring the overall sorting activity on our instance.

Monitoring Sorting Activity

The STATSPACK utility provides a great historical reference to sorting activity in Oracle. Upon increasing sort_area_size, you can track the reduction in disk sorts over time. The following STATSPACK query uses the stats$sysstat table. From this table we can get an accurate picture of memory and disk sorts.

rpt_sorts_alert.sql

set pages 9999;

column mydate heading 'Yr.  Mo Dy  Hr.' format a16
column sorts_memory  format 999,999,999
column sorts_disk    format 999,999,999
column ratio         format .99999

select
   to_char(snap_time,'yyyy-mm-dd HH24')                mydate,
   newmem.value-oldmem.value                           sorts_memory,
   newdsk.value-olddsk.value                           sorts_disk,
   ((newdsk.value-olddsk.value)/(newmem.value-oldmem.value)) ratio
from
   perfstat.stats$sysstat oldmem,
   perfstat.stats$sysstat newmem,
   perfstat.stats$sysstat newdsk,
   perfstat.stats$sysstat olddsk,
   perfstat.stats$snapshot    sn
where
   newdsk.snap_id = sn.snap_id
and
   olddsk.snap_id = sn.snap_id-1
and
   newmem.snap_id = sn.snap_id
and
   oldmem.snap_id = sn.snap_id-1
and
   oldmem.name = 'sorts (memory)'
and
   newmem.name = 'sorts (memory)'
and
   olddsk.name = 'sorts (disk)'
and
   newdsk.name = 'sorts (disk)'
and
   newmem.value-oldmem.value > 0
and
   newdsk.value-olddsk.value > 100
;

Note that this query restricts output to those cases where there are more than 100 sorts. This restriction can be used to make the script behave as an alert mechanism to let you know when the number of sorts has been exceeded. Here is the output from the script. Here you can clearly see the numbers of memory sorts and disk sorts as well as the ratio of disk to memory sorts:

Yr.  Mo Dy  Hr.  SORTS_MEMORY   SORTS_DISK   RATIO
---------------- ------------ ------------ -------
2000-12-20 12          13,166          166  .01261
2000-12-20 16          25,694          223  .00868
2000-12-21 10          99,183          215  .00217
2000-12-21 15          13,662          130  .00952
2000-12-21 16          17,004          192  .01129
2000-12-22 10          18,900          141  .00746
2000-12-22 11          19,487          131  .00672
2000-12-26 12          12,502          147  .01176
2000-12-27 13          20,338          118  .00580
2000-12-27 18          11,032          119  .01079
2000-12-28 16          16,514          205  .01241
2000-12-29 10          17,327          242  .01397
2000-12-29 16          50,874          167  .00328
2001-01-02 08          15,574          108  .00693
2001-01-02 10          39,052          136  .00348
2001-01-03 11          13,193          153  .01160
2001-01-03 13          19,901          104  .00523
2001-01-03 15          19,929          130  .00652

This report can be changed to send an alert when the number of disk sorts exceeds a predefined threshold, and we can also modify it to plot average sorts by hour of the day and day of the week.

Some Remote DBAs will locate periods of high sorting activity and then go to the stats$sql_summary table and extract the SQL for that snapshot for detailed analysis. Next, let’s look at how we can locate repeating periods of high disk sorting activity.


This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter