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

 

 


 

 

 

 

 
 

Goals of SQL Tuning

Oracle Tips by Burleson Consulting

Oracle SQL tuning is a phenomenally complex subject, and entire books have been devoted to the nuances of Oracle SQL tuning. However, there are some general guidelines that every Oracle Remote DBA follows in order to improve the performance of their systems. The goals of SQL tuning are simple:

  • Remove unnecessary large-table full table scans Unnecessary full table scans cause a huge amount of unnecessary I/O, and can drag down an entire database. The tuning expert first evaluates the SQL based on the number of rows returned by the query. If the query returns less than 40 percent of the table rows in an ordered table, or 7 percent of the rows in an unordered table, the query can be tuned to use an index in lieu of the full table scan. The most common tuning for unnecessary full table scans is adding indexes. Standard B-tree indexes can be added to tables, and bitmapped and function-based indexes can also eliminate full table scans. The decision about removing a full table scan should be based on a careful examination of the I/O costs of the index scan vs. the costs of the full table scan, factoring in the multiblock reads and possible parallel execution. In some cases an unnecessary full table scan can be forced to use an index by adding an index hint to the SQL statement.

  • Cache small-table full table scans In cases where a full table scan is the fastest access method, the tuning professional should ensure that a dedicated data buffer is available for the rows. In Oracle7 you can issue alter table xxx cache. In Oracle8 and beyond, the small table can be cached by forcing it into the KEEP pool.

  • Verify optimal index usage This is especially important for improving the speed of queries. Oracle sometimes has a choice of indexes, and the tuning professional must examine each index and ensure that Oracle is using the proper index. This also includes the use of bitmapped and function-based indexes.

  • Verify optimal JOIN techniques Some queries will perform faster with NESTED LOOP joins, others with HASH joins.

These goals may seem deceptively simple, but these tasks comprise 90 percent of SQL tuning, and they don't require a thorough understanding of the internals of Oracle SQL. Let's begin with an overview of the Oracle SQL optimizers.

The Problem of Declarative SQL Syntax

With SQL being a declarative language, there are many options to use when writing an SQL query, and each option may have dramatically different performance. This is a big issue and the Oracle Remote DBA must be constantly on the lookout for malformed and convoluted SQL statements.

Let's illustrate this concept with a simple example. Let's assume that we have a student database for a university and we need to know the names of all students who received an A for any class last semester.

This query can be written in three ways, each providing identical results:

A Standard JOIN

L 11-1

SELECT
   *
FROM
   STUDENT,
   REGISTRATION
WHERE
    student.student_id = registration.student_id
AND
    registration.grade = 'A';

A Nested Query

L 11-2

SELECT
   *
FROM
   STUDENT
WHERE
    student_id =
    (SELECT student_id
        FROM REGISTRATION
        WHERE
        grade = 'A'
    );

A Correlated Subquery

L 11-3

SELECT
   *
FROM
   STUDENT
WHERE
    0 <
    (SELECT count(*)
        FROM REGISTRATION
        WHERE
        grade = 'A'
        AND
        student_id = STUDENT.student_id
    );

Each of these queries will return identical results, but with radically different execution plans and different performance. Most experienced Remote DBAs know that the standard JOIN will outperform the other queries, but developers and end users often write convoluted queries to answer a simple question. We will see the shortcomings of convoluted SQL later in this chapter, but now let's take a quick tour of the Oracle SQL optimizers.


This is an excerpt from "Oracle9i High Performance tuning with STATSPACK" by Oracle Press.


If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


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