Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 
 

Rewriting SQL for Faster Performance

Oracle Tips by Burleson Consulting
July 5, 2009

Rewriting SQL for faster performance

Because SQL is a declarative language, you can write the same query in many forms, each getting the same result but with vastly different execution plans and performance.  Re-writing SQL for easier readability (and maintenance) plus faster performance is an important tuning tool. 

  • Rewrite SQL to remove subqueries – Subqueries can be very problematic from a performance perspective.

  • Rewriting the SQL in PL/SQL  - For certain queries rewriting SQL in PL/SQL can result in more than a 20x performance improvement.

  • Rewrite SQL to simplify query - Decomposing a query into multiple queries using the WITH clause (or global temporary tables) greatly aids performance.

In this example, we select all books that do not have any sales.  Note that this is a non-correlated sub-query, but it could be re-written in several ways.

select
   book_key
from
   book
where
   book_key NOT IN (select book_key from sales);

There are serious problems with subqueries that may return NULL values. It is a good idea to discourage the use of the NOT IN clause (which invokes a sub-query) and to prefer NOT EXISTS (which invokes a correlated sub-query), since the query returns no rows if any rows returned by the sub-query contain null values.

select
   book_key
from
   book
where
   NOT EXISTS (select book_key from sales);

Subqueries can often be re-written to use a standard outer join, resulting in faster performance.  As we may know, an outer join uses the plus sign (+) operator to tell the database to return all non-matching rows with NULL values.  Hence we combine the outer join with a NULL test in the WHERE clause to reproduce the result set without using a sub-query.

select
   b.book_key
from
   book  b,
   sales s
where
   b.book_key = s.book_key(+)
and
   s.book_key IS NULL;

This execution plan will also be faster by eliminating the sub-query.

Rewriting SQL for better readability and faster response time speed

Here is an actual example of a poorly-written SQL query.  It's hard to read, and the execution plan is horrible:

SELECT ART.DEMO_MEMBER DEMO_MEMBER,
       (SELECT PARAMETER_VALUE
       FROM    PPM_CIA_PREMIUM_VAL CIA_VAL
       WHERE   CIA_VAL.PRD_DEMO_CHRSTC_MEMBER = ART.DEMO_MEMBER
           AND CIA_NAME IN ('Baseline')
           AND PRMA_MKT_MEMBER = ?
           AND INVOICE_TYPE_NAME IN ('Weekly')
           AND GROUP_TYPE_CODE = 'C'
       ) WEEKLY_VALS,
       (SELECT PARAMETER_VALUE
       FROM    PPM_CIA_PREMIUM_VAL CIA_VAL
       WHERE   CIA_VAL.PRD_DEMO_CHRSTC_MEMBER = ART.DEMO_MEMBER
           AND CIA_NAME IN ('Baseline')
           AND PRMA_MKT_MEMBER = ?
           AND INVOICE_TYPE_NAME IN ('Monthly')
           AND GROUP_TYPE_CODE = 'C'
       ) MONTHLY_VALS,
       (SELECT PARAMETER_VALUE
       FROM    PPM_CIA_PREMIUM_VAL CIA_VAL
       WHERE   CIA_VAL.PRD_DEMO_CHRSTC_MEMBER = ART.DEMO_MEMBER
           AND CIA_NAME IN ('Baseline')
           AND PRMA_MKT_MEMBER = ?
           AND INVOICE_TYPE_NAME IN ('90Day')
           AND GROUP_TYPE_CODE = 'C'
       ) NINETYDAY_VALS,
       (SELECT PARAMETER_VALUE
       FROM    PPM_CIA_PREMIUM_VAL CIA_VAL
       WHERE   CIA_VAL.PRD_DEMO_CHRSTC_MEMBER = ART.DEMO_MEMBER
           AND CIA_NAME IN ('Baseline')
           AND PRMA_MKT_MEMBER = ?
           AND INVOICE_TYPE_NAME IN ('Annual')
           AND GROUP_TYPE_CODE = 'C'
       )      ANNUAL_VALS,
       'C' AS GROUP_TYPE_CODE
FROM
       (SELECT DISTINCT PRD_DEMO_CHRSTC_MEMBER DEMO_MEMBER
       FROM             PPM_CIA_PREMIUM_VAL
       WHERE            CIA_NAME IN ('Baseline')
                    AND PRMA_MKT_MEMBER = ?
                    AND INVOICE_TYPE_NAME IN ('Weekly' ,
                                                  'Monthly',
                                                  'Annual' ,
                                                  '90Day')
                    AND GROUP_TYPE_CODE = 'C'
       GROUP BY         PRD_DEMO_CHRSTC_MEMBER
       ) ART
 
UNION ALL
SELECT   ART.DEMO_MEMBER DEMO_MEMBER,
         (SELECT PARAMETER_VALUE
         FROM    PPM_CIA_PREMIUM_VAL CIA_VAL
         WHERE   CIA_VAL.PRD_DEMO_CHRSTC_MEMBER = ART.DEMO_MEMBER
             AND CIA_NAME IN ('Baseline')
             AND PRMA_MKT_MEMBER = ?
             AND INVOICE_TYPE_NAME IN ('Weekly')
             AND GROUP_TYPE_CODE = 'T'
         ) WEEKLY_VALS,
         (SELECT PARAMETER_VALUE
         FROM    PPM_CIA_PREMIUM_VAL CIA_VAL
         WHERE   CIA_VAL.PRD_DEMO_CHRSTC_MEMBER = ART.DEMO_MEMBER
             AND CIA_NAME IN ('Baseline')
             AND PRMA_MKT_MEMBER = ?
             AND INVOICE_TYPE_NAME IN ('Monthly')
             AND GROUP_TYPE_CODE = 'T'
         ) MONTHLY_VALS,
         (SELECT PARAMETER_VALUE
         FROM    PPM_CIA_PREMIUM_VAL CIA_VAL
         WHERE   CIA_VAL.PRD_DEMO_CHRSTC_MEMBER = ART.DEMO_MEMBER
             AND CIA_NAME IN ('Baseline')
             AND PRMA_MKT_MEMBER = ?
             AND INVOICE_TYPE_NAME IN ('90Day')
             AND GROUP_TYPE_CODE = 'T'
         ) NINETYDAY_VALS,
         (SELECT PARAMETER_VALUE
         FROM    PPM_CIA_PREMIUM_VAL CIA_VAL
         WHERE   CIA_VAL.PRD_DEMO_CHRSTC_MEMBER = ART.DEMO_MEMBER
             AND CIA_NAME IN ('Baseline')
             AND PRMA_MKT_MEMBER = ?
             AND INVOICE_TYPE_NAME IN ('Annual')
             AND GROUP_TYPE_CODE = 'T'
         )      ANNUAL_VALS,
         'T' AS GROUP_TYPE_CODE
FROM
         (SELECT DISTINCT PRD_DEMO_CHRSTC_MEMBER DEMO_MEMBER
         FROM             PPM_CIA_PREMIUM_VAL
         WHERE            CIA_NAME IN ('Baseline')
                      AND PRMA_MKT_MEMBER = ?
                      AND INVOICE_TYPE_NAME IN ('Weekly' ,
                                                    'Monthly',
                                                    'Annual' ,
                                                    '90Day')
                      AND GROUP_TYPE_CODE = 'T'
         GROUP BY         PRD_DEMO_CHRSTC_MEMBER
         ) ART
ORDER BY GROUP_TYPE_CODE

Look like Greek?  That’s not a good SQL practice, to write convoluted SQL!  Let’s look at the same query, rewritten for clarity and faster performance.

The above convoluted query can be completely re-written.  This SQL runs faster and more efficiently.

Rewritten query:

The above convoluted query can be completely re-written.  This SQL runs faster and more efficiently.

SELECT ART.PRD_DEMO_CHRSTC_MEMBER DEMO_MEMBER,
       SUM(DECODE(ART.INVOICE_TYPE_NAME,'Weekly',ART.parameter_value))  WEEKLY_VALS,
       SUM(DECODE(ART.INVOICE_TYPE_NAME,'Monthly',ART.parameter_value)) MONTHLY_VALS,
       SUM(DECODE(ART.INVOICE_TYPE_NAME,'90Day',ART.parameter_value))   NINETYDAY_VALS,
       SUM(DECODE(ART.INVOICE_TYPE_NAME,'Annual',ART.parameter_value))  ANNUAL_VALS,
       ART.GROUP_TYPE_CODE
FROM   PPM_CIA_PREMIUM_VAL ART
       WHERE            CIA_NAME IN ('Baseline')
                    AND PRMA_MKT_MEMBER = ?
                    AND INVOICE_TYPE_NAME IN ('Weekly' ,
                                                  'Monthly',
                                                  'Annual' ,
                                                  '90Day')
                    AND GROUP_TYPE_CODE IN ('C','T')
GROUP BY ART.PRD_DEMO_CHRSTC_MEMBER,ART.GROUP_TYPE_CODE
ORDER BY ART.GROUP_TYPE_CODE, ART.PRD_DEMO_CHRSTC_MEMBER;

As we see, SQL is very flexible and an equivalent query can be written in many ways, all giving the same result, but with radically different readability and execution response time.

A  script to display the memory used by any specific session would look something like this:

display_session.ram.sql

select
    to_char(ssn.sid, '9999') || ' – ' || nvl(ssn.username,
    nvl(bgp.name, 'background')) || nvl(lower(ssn.machine), ins.host_name) ssession,
    to_char(prc.spid, '999999999')                       pid_thread,
    to_char((se1.value / 1024) / 1024, '999g999g990d00') current_size_mb,
    to_char((se2.value / 1024) / 1024, '999g999g990d00') maximum_size_mb
from
    v$statname  stat1,
    v$statname  stat2,
    v$session   ssn,
    v$sesstat   se1,
    v$sesstat   se2,
    v$bgprocess bgp,
    v$process   prc,
    v$instance  ins
where
 (See CODE DEPOT for full working script)
    stat1.name = 'session pga memory'
and
    stat2.name = 'session pga memory max'
and
    se1.sid = ssn.sid
and
    se2.sid = ssn.sid
and
    se2.statistic# = stat2.statistic#
and
    se1.statistic# = stat1.statistic#
and
    ssn.paddr = bgp.paddr(+)
and
    ssn.paddr = prc.addr(+);

Click for more details on displaying RAM memory used by an Oracle session.


 

 

��  
 
 
 
 

 
 
 

 
 
Oracle performance tuning software 
 
oracle dba poster
Oracle Linux poster
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.