Sorting Operations   «Prev 

Improve Productivity through Oracle Tuning

Through the release of Oracle8, Oracle8i, and Oracle9i, Oracle has evolved into a database solution that sovles many problems. The challenge for IT professionals is to ensure that they are able to leverage Oracle's powerful features to improve the productivity of their organizations. One of the most effective ways to do this is through Oracle tuning. As a result, there is a large set of tuning parameters and techniques, all designed to improve the performance of your Oracle database. Oracle tuning is a complex subject and entire books have been written about the specifics of Oracle tuning. However, there are some general goals that every Oracle DBA should follow in order to improve the performance of their systems.
A DBA should focus on the following Oracle topics:
  1. External tuning: We must remember that Oracle does not run in a vacuum. Here we will look at tuning the Oracle server for high performance.
  2. Row re-sequencing to reduce disk I/O: We must understand how reducing I/O is the most important goal of Oracle tuning.
  1. Oracle SQL tuning: Oracle SQL tuning is one of the most important areas of Oracle tuning, and it is not uncommon to dramatically improve the performance of an SQL statement by using a few simple SQL tuning rules.
  2. Tuning Oracle sorting: Sorting is a small but very important component of Oracle performance.
  3. Tuning Oracle contention: The setting for table and index parameters has a huge impact on UPDATE and INSERT performance.
We always start by tuning the Oracle external environment. No amount of Oracle tuning is going to help if the server has a shortage of RAM or CPU resources.

Oracle Query that invokes Sort

Query Sort 
SQL>explain plan for 

select /*+  ordered */
  e.deptno,
  d.deptno
from
  emp e,
  dept d
where
  e.deptno = d.deptno
order by 
  e.deptno, d.deptno 
  
SELECT STATEMENT [CHOOSE] Cost =17
  MERGE JOIN
    SORT JOIN 
      TABLE ACCESS FULL EMP [ANALYZED]
    SORT
      TABLE ACCESS FULL DEPT [ANALYZED]

#1 The syntax of this query requires that full-table scans are performed against each of the target tables.
#2 A sort operation is used to join the result sets together.
#3 Rows are produced by the EMP full-table scan and are then sorted.
#4 Rows from the DEPT full-table scan are then sorted by the same sort key as the EMP table.