Tuning and Timing

Oracle offers two features you can invoke from SQL*Plus to monitor and improve the performance of your scripts and SQL statements: SQL*Plus timers and the EXPLAIN PLAN command.

SQL*Plus has a built-in timing feature that can be used to monitor the length of time it takes to execute a SQL statement, a PL/SQL block, or any other part of a script. To measure the time it takes to execute a SQL statement, you start a timer prior to executing the statement and display the value of the timer immediately after the statement is executed.

EXPLAIN PLAN, although it is a SQL statement and not a SQL*Plus command, is often issued from SQL*Plus. EXPLAIN PLAN can be used to find out how Oracle intends to execute any given SQL query. It will tell you, for example, whether an index will be used, and what the name of that index will be. Once you know how Oracle intends to execute the query, you can use hints to influence or alter Oracle's default plan based on your knowledge of the data. A hint is a command to the optimizer that is embedded in a comment within a SQL query. The optimizer is the part of Oracle that determines how best to retrieve the data required by a SQL statement.

This chapter is not intended to be an exhaustive reference for tuning SQL statements. Several good books have been written on this subject. This chapter provides a quick overview for SQL*Plus users of the mechanics of tuning and a convenient summary of the hints available to you.


Introduction to SQL*Plus

Command-Line SQL*Plus

Browser-Based SQL*Plus

A Lightning SQL Tutorial

Generating Reports with SQL*Plus

Creating HTML Reports

Advanced Reports

Writing SQL*Plus Scripts

Extracting and Loading Data

Exploring Your Database

Advanced Scripting

Tuning and Timing

The Product User Profile

Customizing Your SQL*Plus Environment

Appendix A. SQL*Plus Command Reference

Appendix B. SQL*Plus Format Elements

Oracle SQL Plus The Definitive Guide, 2nd Edition
Oracle SQL*Plus: The Definitive Guide (Definitive Guides)
ISBN: 0596007469
EAN: 2147483647
Year: N/A
Pages: 151

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net