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

 

 


 

 

 

 

 

 

 

SQL Tuning with STATSPACK

Oracle Tips by Burleson Consulting

The Oracle STATSPACK utility was first introduced in Oracle8i, with a back-port to Oracle8. Introduced with little fanfare, the STATSPACK utility is one of the most powerful Oracle utilities for system tuning and for Oracle SQL tuning.

The tuning of individual SQL statements is the most time-consuming of all of the processes in Oracle tuning. While Oracle SQL tuning is a very time-consuming job, the tuning of SQL also promises the most benefits in the overall performance of the Oracle system. It is not uncommon to increase performance by an order of magnitude by using the proper Oracle SQL tuning techniques.

This chapter will also show how to use Oracle STATSPACK utility in order to monitor the behavior of SQL within your library cache and periodically alert the Oracle professional to SQL statements that may not be optimized for maximum performance. This is done by examining the SQL source in the stats$sql_summary table.

There is also a section on managing SQL statements within the library cache. As every Oracle professional knows, SQL statements are very transient within the Oracle instance and may only reside in the library cache for a short period of time. At any given point in time, information with the library cache may change. SQL statements that enter the library cache remain in the library cache until they age out and are no longer available to the Oracle instance. I will show you how to capture information from the library cache in a way that will build the foundation for using special scripts that will interrogate the library cache at a given point in time and prepare detailed reports showing the execution plans for all of the important SQL statements that are in the Oracle library cache.

The STATSPACK utility monitors the library cache information and can be used to create automated alert reports that will show whenever poorly tuned SQL statements are being executed within the Oracle instance.

The tuning of individual SQL statements will show how to change the execution plans with the use of SQL hints. We will examine actual examples for SQL tuning and show how the execution time of SQL statements can be reduced from hours down to only a few minutes.

In addition to storing system statistics, STATSPACK is also very useful for the tuning of Oracle SQL statements. Because STATSPACK stored all SQL statements that meet the snapshot selection criteria, you can create a historical record of all of the important SQL statements that were in the library cache at the time of the snapshot. Because most shops take STATSPACK snapshots hourly, you can quickly develop a complete picture of all historical SQL statements.

From this database of SQL statements, you can write STATSPACK extraction utilities to extract and re-explain all SQL statements after making changes to important Oracle initialization parameters or adding indexes. In this fashion, you can accurately predict the performance benefit of a SQL tuning change without impacting the production database.

This chapter contains the following topics:

  • Setting the STATSPACK SQL collection thresholds

  • Querying the historical STATSPACK table to extract SQL

  • Extracting specific SQL statements from STATSPACK

Let’s begin by reviewing the Oracle STATSPACK utility and review how STATSPACK collects and stores historical SQL statements.


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