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

 

 


 

 

 

 

 

 

 

The Barriers to SQL Tuning

Oracle Tips by Burleson Consulting

The tuning of Oracle SQL is one of the most time-consuming, frustrating, and annoying areas of Oracle tuning. There are several factors that make SQL tuning a maddening undertaking.

  • Locating the offensive SQL statement Later in this book we will explore technique for fishing SQL out of the Oracle library cache and tuning the statement. However, if you have an early release of Oracle8 that does not support optimizer plan stability, you must find the location of the SQL source code in order to make your tuning changes permanent. As we know, SQL source code can exist in a variety of locations, including PL/SQL, C programs, and client-side Visual Basic code.

  • Resistance from management SQL tuning is a time-consuming and expensive process, and it is not uncommon for managers to be reluctant to invest in the time required to tune the SQL. In many cases, the Remote DBA must prepare a cost-benefit analysis showing that the saving in hardware resources justifies the costs of tuning all of the SQL in a database.

  • Tuning with ad hoc SQL generators Products such as the SAP application dynamically create the Oracle SQL inside the SAP ABAP programs, and it is often impossible to modify the SQL source code.

  • Resistance from SQL programmers Many programmers are reluctant to admit that they have created a suboptimal SQL statement. This can make it quite difficult when the Oracle Remote DBA determines that the statement must be rewritten to improve performance.

  • Tuning nonreusable SQL statements Many third-party applications generate SQL statements with embedded literal values (e.g., select * from customer where name = ĎJONESí;). In these cases, the library is flooded with tens of thousands of nonreusable SQL statements, with many identical SQL statement that cannot be reused because they have no host variables (e.g., select * from customer where name = :var1;). In these cases, cursor_sharing must be implemented, and sometimes the library cache must be downsized or flushed with the alter system flush shared pool command.

  • Diminishing marginal returns As the Oracle Remote DBA identifies and tunes high-use SQL statements, it becomes harder to locate SQL statements for tuning. For example, there may be many dozens of infrequently executed SQL statements that would greatly benefit from tuning, but their sporadic appearance in the library cache makes them difficult to locate. Hence, we see a point where the marginal benefit from tuning will become less than the Oracle Remote DBA effort in locating the statements, as Figure 1-8 illustrates.

Figure 8: The marginal benefits of SQL tuning

Despite these difficulties and challenges, the tuning of Oracle SQL can make the Oracle Remote DBA a hero, both to management and to the programming staff. Enlightened companies always make the programmers submit SQL statements with their current execution plans to the Remote DBA for a review, prior to placing the SQL into their production environment.

Next, letís take a high-level tour of the steps involved in SQL tuning.

The Process of SQL Tuning

Once all of the prerequisite tuning has been done to the server, network, disk, instance, and objects, the process of SQL tuning can begin. Although we will be covering this subject in greater detail later, letís take a high-level tour of the steps of SQL tuning.

It is critical to remember that SQL tuning is an iterative activity. The Oracle Remote DBA is challenged to perform the following activities:

  • Locate high-use SQL statements The first step in SQL tuning is locating the  frequently executed SQL. This involves using STATSPACK or fishing through the library cache.

  • Tune the SQL statement The tuning of a SQL statement involves generating execution plan and evaluating alternative execution plans by:

  • Adding indexes You can add indexes (especially bitmapped and function-based indexes) to remove unwarranted full-table scans.

    • Changing the optimizer mode You can try changing the optimizer mode to rule, all_rows, or first_rows.

    • Adding hints You can add hints to force a change to the execution plan.

  • Make the tuning permanent Once you have tuned the SQL statement, you must make the change permanent by locating and changing the SQL source code or by using optimizer plan stability.

Letís take a close look at these steps.


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