Performance

15
C H A P T E R

he subject of performance could take up the entire contents of this book because there are so many different issues and techniques for performance tuning. This chapter covers tuning your SQL code, which is a key component of overall performance tuning. The chapter also briefly covers the Performance Monitor tool of Enterprise Manager. These techniques help you tune the performance for all types of applications. All applications ultimately generate SQL commands that are run in the database. When developers create their own SQL commands, these tuning and performance techniques are invaluable tools.

In This Chapter
Monitoring performance with Performance Manager Using ANALYZE Running EXPLAIN
PLAN

Analyzing the plan

Initialization Parameters
This section describes three important initialization parameters. Although others exist, first assess the following three parameters: 3 DB_BLOCK_BUFFERS 3 SHARED_POOL_SIZE 3 OPTIMIZER_MODE View all your initialization parameters in the Instance Manager tool. Figure 15-1 shows the Parameters window the highlighted parameters are discussed in the next sections.

Using SQL hints Tuning tips with SQL

296

Chapter 15 3 Performance

OPTIMIZER_MODE SHARED_POOL_SIZE DB_BLOCK_BUFFERS

Figure 15-1: The initialization parameters as displayed by the Instance Manager tool

Alternatively, view your initialization parameters by opening the initialization file in the DATABASE directory. If your database instance is named orcl then the file name is initorcl.ora.

DB_BLOCK_BUFFERS
The DB_BLOCK_BUFFERS parameter allocates the proper amount of your machines memory as a buffer when reading data from the database. Remember, this area contains more information than actual data returned from a query. Other data, such as privilege information from the Data Dictionary views, is also stored here. Increase the DB_BLOCK_BUFFERS to reduce I/O operations. Performance improves as Oracle8 reads more information in a single chunk into the buffer. If you have increases in page swapping after increasing the DB_BLOCK_BUFFERS, reduce the DB_BLOCK_BUFFERS until the page swapping diminishes. Because your computer must use memory for activities outside the database, you must be careful not to increase the DB_BLOCK_BUFFER to a point where the computer does not have enough room to run applications efficiently.

Chapter 15 3 Initialization Parameters

297

Monitor the page swapping by adding a new parameter to the Performance Monitor chart. The Performance Monitor is an add-on part to the Enterprise Manager basic package. Follow these steps to add page swapping to the Performance Monitor chart. 1. Start Performance Monitor. 2. Click the Chart View icon if needed. By default, the Chart View appears first. If you do not see it, click the chart icon to make the line graph appear. 3. Click Edit Add to Chart. This sequence opens a new window where you select from many different monitoring options. 4. Select the Page/sec Counter. Select Memory in the Object box. Select Page/sec in the Counter box. 5. Click Explain to read about this counter (optional). Figure 15-2 shows the Add to Chart window after selecting the Page/sec counter and clicking the Explain> >button.

Figure 15-2: Read about the Page/sec Counter in the Add to Chart window.

6. Click Add. The Page/sec Counter gets added to the chart and you return to the main chart window. Figure 15-3 shows the Performance Monitor with the Page/sec Counter added.

298

Chapter 15 3 Performance

Lower pages/Sec is better

Figure 15-3: The Page/sec monitors undesirable page swapping.

Keep the Page/sec low by first increasing the DB_BLOCK_BUFFER to reduce I/O and then decreasing the DB_BLOCK_BUFFER slightly if needed.

SHARED_POOL_SIZE
The SHARED_POOL_SIZE parameter allocates the proper amount of your computers memory for storing parsed SQL commands. When an SQL command is initially executed, it is parsed and stored in the shared pool. If an identical SQL command is executed, Oracle8 retrieves the parsed version from the shared pool, which saves CPU time. The shared pool holds as many commands as possible and removes commands when it runs out of space. Commands are removed using the least-used first algorithm, meaning the least-used commands are removed first. Reduce CPU usage by increasing the SHARED_POOL_SIZE. Virtually all of your applications work with the database by executing SQL commands. When multiple Users run the same application, Oracle8 seeks to minimize the work of parsing the SQL command by first looking for the command in the shared pool.

Chapter 15 3 Initialization Parameters

299

Your application design affects the efficiency of the shared pool. As your application reuses identical SQL commands, the SQL command in the shared pool can be reused as well. Monitor the effectiveness of the shared pool by assessing the %RELOAD/PIN counter in the Performance Monitor. Figure 15-4 shows where to find this counter in the chart.

Click here to select % reloads/pin

Lower is better for % reloads/pin

Figure 15-4: The %reload/pin monitors undesirable reparsing of SQL commands.

Reloads consist of the number of times Oracle8 parses and loads an SQL command into the shared pool. Reloads are caused by two conditions: 3 Running out of space in the shared pool. Increase the SHARED_POOL_SIZE parameter to correct this problem. 3 Encountering a new SQL command. Standardize and reuse SQL code in your application to reduce this problem. Pins consist of the number of times Oracle8 reuses a parsed SQL command in the shared pool. Keep the %RELOAD/PIN ratio close to zero by increasing the SHARED_POOL_SIZE.

300

Chapter 15 3 Performance

OPTIMIZER_MODE
The OPTIMIZER_MODE tells Oracle8s Optimizer what method or mode to use when evaluating SQL commands. In the CHOOSE default mode, the Optimizer chooses the optimization rules based on statistics for the Tables involved in the command. Oracle8s Optimizer is a cost-based Optimizer. To help the Optimizer work best, store statistics on your Tables by running the ANALYZE command. (Refer to the ANALYZE section later in this chapter to learn how to run the ANALYZE command.) When the OPTIMIZER_MODE is CHOOSE, the Optimizer considers the Table size, the Indexed Column values, the amount of chaining, and other factors. Then the Optimizer determines which access plan minimizes CPU and I/O costs while maximizing the best response time.
Tip

Prior to Oracle Version 7.3, the Optimizer used a RULE-based optimizing method. If you have an older system with applications tuned to run best on the RULE-based optimization, your system may not benefit from the new cost-based Optimizer rules. If you notice reduced response time after switching to Oracle8, try changing the OPTIMIZER_MODE to RULE, which mimics the older functionality of Oracle Version 7.2. Adjust the OPTIMIZER_MODE for any session using this ALTER SESSION syntax:
ALTER SESSION SET OPTIMIZER_MODE = ALL_ROWS | FIRST_ROW | RULE | CHOOSE

The four modes are: 3 ALL_ROWS. Optimize with a goal of returning all rows as quickly as possible. 3 FIRST_ROW. Optimize with a goal of returning the first row as quickly as possible. 3 RULE. Optimize using RULE-based approach only. 3 CHOOSE. Optimize using rules and Table statistics with a goal of balancing cost with good response time. Oracle8s cost-based Optimizer implements the OPTIMIZER_MODE.

Cost-Based Optimizer
The cost-based Optimizer needs statistics on Tables to determine the most efficient method of retrieving data. The Optimizer devises plans, which are maps to retrieve data. In any given SQL command, there may be four or five ways to retrieve the data. The WHERE, ORDER BY, and (sometimes) FROM clauses are evaluated by the Optimizer to determine the best plan.

Chapter 15 3 Cost-Based Optimizer

301

For example, the following query joins two Tables:

SELECT ANIMAL_NAME , T.TANK_NAME FROM AQUATIC_ANIMAL A, TANK T WHERE T.TANK_NO = A.TANK_NO AND ANIMAL_NAME LIKE B%

The Optimizer looks at each phrase in the WHERE clause. A phrase begins with WHERE, AND, or OR. In the preceding example, the two phrases are: 3 T.TANK_NO = A.TANK_NO 3 ANIMAL_NAME LIKE B% Each phrase is reviewed and rated. The Optimizer considers the presence and usability of Indexes, the size and makeup of rows, the Columns to be retrieved, and other factors. The Optimizer assigns a cost factor to the phrase. The Optimizer then devises a plan in which the phrase with the lowest cost is the starting point. Then an overall plan cost is assessed. The Optimizer may design and compare multiple plans to find the lowest cost plan. Your job is to help the Optimizer be as accurate as possible. To accomplish this, perform these four important functions: 3 ANALYZE all Tables. Gather timely statistics for the Optimizer. 3 Run EXPLAIN PLAN on critical SQL commands. Discover the Optimizer choices by running this command. 3 Use hints on some SQL commands. Hints instruct the Optimizer to alter its goal of selecting a plan with the lowest overall cost. 3 Add Indexes on some Tables. Indexes frequently reduce the cost of executing SQL commands. I describe these four tasks in the following sections, beginning with the ANALYZE command.

ANALYZE
Run the ANALYZE command in SQL Worksheet or SQL*Plus on all Tables in an application Schema. The syntax follows:
ANALYZE { INDEX [user.]index { { COMPUTE | ESTIMATE | DELETE } STATISTICS [ SAMPLE ( n PERCENT | n ROWS } ] | VALIDATE STRUCTURE } |

302

Chapter 15 3 Performance

{ TABLE [user.]table | CLUSTER [user.]cluster} { { COMPUTE | ESTIMATE | DELETE } STATISTICS [ SAMPLE ( n PERCENT | n ROWS } ] | VALIDATE STRUCTURE } [CASCASE] | LIST CHAINED ROWS [INTO [user.]table] }

Analyze Tables and Indexes.

Caution

Do not run ANALYZE on the SYS Schema this combination causes performance problems.

Caution

If your Table contains more than ten thousand rows, running these commands can take a long time. Use the ESTIMATE option. Assign the ANALYZE ANY privilege to a User. This assignment enables one User to analyze all Tables in any Schema. An example of an ANALYZE command follows:
ANALYZE TABLE AQUATIC_ANIMAL ESTIMATE STATISTICS;

After running ANALYZE, view the results by querying USER_TABLES in SQL Worksheet or SQL*Plus. The following SQL code queries the USER_TABLES Table to look at statistics on the AQUATIC_ANIMAL Table:
SELECT NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_ROW_LEN, CHAIN_CNT FROM USER_TABLES WHERE TABLE_NAME = AQUATIC_ANIMAL;

Oracle8 executes the query and displays the following results the statistics generated by the ANALYZE command:
NUM_ROWS BLOCKS EMPTY_BLOC AVG_ROW_LE CHAIN_CNT ...................................................... 10 1 8 49 0

These Columns show you information about your Tables blocks. Heres what the Columns mean: 3 NUM_ROWS: total rows in the Table. 3 BLOCKS: total blocks used by the Table. 3 EMPTY_BLOCKS: empty blocks tied up by the Table. A number other than zero means your Table has had a great deal of activity (many INSERTs and DELETEs). Reorganize the Table to reduce the amount of wasted space in these empty blocks.

Chapter 15 3 Cost-Based Optimizer

303

3 AVG_ROW_LENGTH: average length, in bytes, of a row in the Table. Helps you determine the number of blocks to allocate to the Table. 3 CHAIN_CNT: number of chained blocks. Any chaining degrades the retrieval speed for this Table. Reorganize the Table to eliminate chaining. The cost-based Optimizer can proceed more efficiently after you run ANALYZE on your Tables. If you have an SQL command that runs slowly, the next section shows you how to investigate by looking at the Optimizers execution plan for the command.

EXPLAIN PLAN
Note

You probably do not have time to review every SQL command that goes into an application. Save EXPLAIN PLAN for SQL commands that have performance problems. The general syntax for EXPLAIN PLAN follows:
EXPLAIN PLAN [SET STATEMENT ID = Statement_name] [ INTO [user.]table] FOR query

Prior to running the EXPLAIN PLAN, you must create the PLAN_TABLE. The DBA script called ultxplan.sql creates the Table. Make sure the User running EXPLAIN PLAN has SELECT, INSERT, UPDATE, and DELETE privileges on this Table. The next example shows how the EXPLAIN PLAN works. First, the following query needs to be evaluated:
SELECT ANIMAL_NAME , T.TANK_NAME FROM AQUATIC_ANIMAL A, TANK T WHERE T.TANK_NO = A.TANK_NO AND ANIMAL_NAME LIKE B%

The following command runs EXPLAIN PLAN and catalogs the results under the name TANK_1:
EXPLAIN PLAN SET STATEMENT_ID = TANK_1 FOR SELECT ANIMAL_NAME , T.TANK_NAME FROM AQUATIC_ANIMAL A, TANK T WHERE T.TANK_NO = A.TANK_NO AND ANIMAL_NAME LIKE B%

304

Chapter 15 3 Performance

Note

Before repeating the EXPLAIN PLAN, you should either delete the rows generated from your last run or use a different STATEMENT_ID. To delete the generated rows, run a command like the following:
DELETE FROM PLAN_TABLE WHERE STATEMENT_ID = TANK_1

After running the EXPLAIN PLAN, run a query to view the results. The following query extracts the information about the example query:
SELECT LPAD( ,2*(LEVEL-1)) || OPERATION || || OPTIONS || || OBJECT_NAME || || OBJECT_TYPE || DECODE ( ID,0,Cost: || COST) EXPL_PLAN FROM PLAN_TABLE CONNECT BY PRIOR ID = PARENT_ID AND STATEMENT_ID = TANK_1 START WITH ID=0 AND STATEMENT_ID = TANK_1

Oracle8 displays the following results:

EXPL_PLAN ............................................................... SELECT STATEMENT Cost:2 NESTED LOOPS TABLE ACCESS FULL AQUATIC_ANIMAL TABLE ACCESS BY INDEX ROWID TANK

Table 15-1 lists characteristics of the EXPLAIN PLAN.

Table 15-1 Reviewing the Optimizer Plan in the PLAN Table

What you find What it means Total cost of the command What to do Compare this number to COST found on other versions of the same command or the same command with new Indexes N/A N/A

COST:

INDEX RANGE SCAN TABLE ACCESS BY INDEX ROWID

An Index scans for values in a range An Index retrieves data from the Table

Chapter 15 3 Cost-Based Optimizer

305

What you find

What it means You must run a full Table scan to execute the command The elements listed below the NESTED LOOP are executed multiple times

What to do Consider Indexing Columns; review Column order of existing Indexes N/A

TABLE ACCESS FULL

NESTED LOOP

Two other useful tuning techniques, the addition of hints to the SQL command and the effective use of Indexes, are described in the following sections.

Hints
Hints are specially formatted comments embedded into a SQL command that tell the Optimizer to modify its evaluation method for the particular SQL command. The general syntax for hints follows:
SELECT | UPDATE | DELETE [,processornumber]] */ ... /* + hintname [(tablename

The requirements for using hints follow: 3 Use a hint on SELECT, UPDATE, or DELETE commands. 3 The hint comment must appear immediately after the first word in the command. 3 The hint must be preceded by a plus (+) sign. 3 Use comment markings before the plus sign. The two kinds of comment markings are: Comment delimiters. /* comment here */ Double dash. - - comment here 3 Specify the alias found in the FROM clause when naming Tables in a hint. The following example specifies the first_row hint for the SELECT statement:
SELECT /* + first_row */ ANIMAL_NAME , T.TANK_NAME FROM AQUATIC_ANIMAL A, TANK T WHERE T.TANK_NO = A.TANK_NO AND ANIMAL_NAME LIKE B%

306

Chapter 15 3 Performance

The next example specifies the full hint (full Table scan) for the TANK Table (alias T ) in the SELECT statement:
SELECT /* + full (T) */ ANIMAL_NAME , T.TANK_NAME FROM AQUATIC_ANIMAL A, TANK T WHERE T.TANK_NO = A.TANK_NO AND ANIMAL_NAME LIKE B%
Tip

Add hints and run the SQL command through the EXPLAIN PLAN process to see how the hint affects the cost of the plan. If it does not reduce the cost of the plan, try another hint. Table 15-2 lists some hints for tuning SQL commands.

Table 15-2 Hints and What They Mean

Hint Purpose Optimize with a goal of returning all rows as quickly as possible. For distributed sites only. Use the location of the specified Table as the execution site of the command. Replace tablename with the actual Table name. Optimize with a goal of returning the first row as quickly as possible. Execute a full Table scan on the specified Table. Replace tablename with the actual Table name. Scan the specified Index in descending order. Replace

all_rows driving site (tablename)

first_row full (tablename) index_desc (tablename Indexname) noparallel (tablename)

tablename with the actual Table name and replace indexname with the actual Index name.
For parallel processing sites only. Do not use parallel processors for this command. Replace tablename with the actual Table name. Join Tables in the order they appear in the FROM clause. For parallel processing sites only. Use the specified number of parallel processors for the specified Table. Replace tablename with the actual Table name. Replace processnumber with the actual number of processes. Access the specific Table by Row ID. Replace tablename with the actual Table name. Optimize using rule-based approach.

ordered parallel (tablename, processnumber)

rowid (tablename) rule

Chapter 15 3 Cost-Based Optimizer

307

The last section in this chapter covers the most useful tuning technique for SQL commands: Indexing.

Indexes
The first step in improving performance is the efficient addition of Indexes. Use EXPLAIN PLAN to discover which queries use a full Table scan to access a Table. The resulting Table is a prime candidate for an Index. Add an Index on Columns used in the WHERE clause. Foreign Keys are often good candidates for an Index. Under most circumstances, Oracle8 uses Indexes to access your data faster and the Optimizer chooses the Indexes you create to speed performance. Sometimes, however, you inadvertently write SQL code in your WHERE clause that causes the Optimizer to eliminate the Index and use a full Table scan instead. You must understand the rules the Optimizer uses to eliminate Indexes knowing these rules helps you create better-performing SQL commands. The following sections describe a list of conditions under which the Optimizer cannot use an Index, even when one is available.

IS NULL and IS NOT NULL

Note

The null value is not Indexed. Any row in which the Indexed Column contains a null value is not included in the Index. Even when the Index has multiple Columns, the row is not included in the Index if any of those Columns contains a null value. When you write a query that checks for nulls in a Column, an Index on the Column does not increase your performance. Any phrase in the WHERE clause that uses IS NULL or IS NOT NULL will not have Indexes allowed by the Optimizer.

Concatenated Columns
The Optimizer will not use Indexes on concatenated Columns, even if they are concatenated to a static value.
Example

For example, you have a query that finds a student with the name JOHN SMITH. Your STUDENT Table splits the name between two Columns: FIRST_NAME and LAST_NAME. One way to write the query combines the two Columns with the concatenation function and adds a single space between the Columns. Then you compare the combined Columns with the name. Heres the code:
SELECT * FROM STUDENT WHERE FIRST_NAME || || LAST_NAME = JOHN SMITH;

This code works well, except the Optimizer cannot use the Index created on LAST_NAME. I did tell you about the Index, didnt I?

308

Chapter 15 3 Performance

The following alternate method writes the query so Oracle8 uses the Index:
SELECT * FROM STUDENT WHERE FIRST_NAME = JOHN AND LAST_NAME = SMITH;

Lets say you are reading a variable called I_NAME into the query. I_NAME contains both the first and last name. How can you avoid a full Table scan? Use a function on the variable to pull out the first and last names. As long as the functions are not performed on the Indexed Column, the Optimizer can use the Index. The code follows:
SELECT * FROM STUDENT WHERE FIRST_NAME = SUBSTR(&&I_NAME,1,INSTR(&&I_NAME, )-1) AND LAST_NAME = SUBSTR(&&I_NAME, INSTR(&&I_NAME, )+1)

LIKE with wildcards (%)

Looking at the same STUDENT Table with an Index on LAST_NAME, now find any person whose last name contains SMITH. To find hyphenated last names like ADAM-SMITH, use a wildcard (%). Your query may resemble the following:
SELECT * FROM STUDENT WHERE LAST_NAME LIKE %SMITH;

The Optimizer cannot use the Index because the wildcard appears at the beginning of the word for which you are searching. Sometimes you cannot avoid this kind of query just remember the wildcard can slow the query. When the wildcard appears elsewhere in the string, the Optimizer can use the Index. The Index applies in the following example query:
SELECT * FROM STUDENT WHERE LAST_NAME LIKE J%;

The ORDER BY clause

The ORDER BY clause determines how Oracle8 sorts the returned rows from query results. The ORDER BY clause does not limit you to a particular Column. Moreover, you can include Columns with functions (such as concatenation or addition) in them as well. Any unindexed item in the ORDER BY clause slows performance, however. Review your ORDER BY clause for Columns or expressions that are not Indexes these elements slow performance. Resolve this performance problem by rewriting the ORDER BY clause to use Indexes or by creating another Index on the employed Column or Columns. Avoid expressions in the ORDER BY clause.

Chapter 15 3 Cost-Based Optimizer

309

NOT
All the phrases or parts of your WHERE clause contain some kind of logical operator, such as the equal sign (=) or the less than sign (<). NOT is added to reverse the logic of any of these operators. Use NOT explicitly by adding parentheses around a phrase and placing NOT immediately in front of the phrase, as in the following example:
... WHERE NOT (CHECKUP_TYPE = ANNUAL) NOT is contained implicitly in one logical operator: the NOT EQUAL operator. In other words, NOT resides in the operator even though you do not type the word in your query. Use the following three forms to write NOT EQUAL: ... WHERE CHECKUP_TYPE <> ANNUAL
Example

The following query uses NOT:

SELECT * FROM TANK WHERE TANK_NO <> 1

The same query, now rewritten to avoid using NOT, follows:

SELECT * FROM TANK WHERE TANK_NO > 1 OR TANK_NO < 1

The second query runs faster even though both queries produce identical results. The second query allows Oracle8 to use an Index on the TANK_NO Column, whereas the first query prohibits use of the Index.

IN versus EXISTS
Sometimes you compare a Column with a list of values. A common method to create the list uses a subquery inside your WHERE clause. You can use two general formats for a subquery in a WHERE clause. The first format uses the IN logical operator: ... WHERE COLUMN IN (SELECT ... FROM ... WHERE ... ) The second format uses the EXISTS logical operator: ... WHERE EXISTS (SELECT X FROM ... WHERE ...) Most people use the first format because it is much easier to code. The second format actually gives you better performance if you learn how to create it. You can

310

Chapter 15 3 Performance

convert nearly every subquery you create by using the IN logical operator so it uses the EXISTS logical operator. In the second format, the subquery correctly starts with SELECT X. Every subquery you write using EXISTS should start in this manner. EXISTS looks directly at the WHERE clause without regard to the data you pull from the Table in the subquery. By using a literal in the SELECT clause, the Optimizer does not have to access the Table for data and can do all its work on the Indexes assuming that Indexes are created on the Columns in the WHERE clause. EXISTS uses a correlated subquery, which is why it is more difficult to construct. By using EXISTS, Oracle8 saves time by evaluating the main query first and running the subquery only until it finds the first match. With IN, Oracle8 suspends processing of the main query while executing the subquery to make a list. Oracle8 stores this list in an Indexed temporary Table and then resumes processing of the main query. Because Oracle8 usually performs queries that use EXISTS faster than those that use IN, examine your WHERE clause for the IN condition. In most cases, you can replace the IN condition with EXISTS.
CrossReference

Chapter 9 discusses the correlated subquery and the EXISTS clause in the Joining Tables section. Refer to that chapter for a good explanation of how to construct a correlated subquery. The following example contains a subquery using EXISTS:
SELECT * FROM CARETAKER C WHERE EXISTS (SELECT X FROM TANK WHERE TANK.CHIEF_CARETAKER_NAME = C. CARETAKER_NAME)

Example

The same query, now using IN for the subquery, follows:

SELECT * FROM CARETAKER C WHERE C.CARETAKER_NAME IN (SELECT CHIEF_CARETAKER_NAME FROM TANK)

Although both queries produce the same results (the same rows retrieved), the first query runs faster because it uses EXISTS rather than IN.
Tip

Whenever possible, also replace NOT IN with NOT EXISTS. Even though both operators contain NOT (which can slow performance by preventing the use of Indexes), NOT EXISTS performs faster than NOT IN.

Chapter 15 3 Summary

311

Summary
Performance tuning covers a wide variety of activities from adjusting your hardware and software configuration to writing thought-out SQL code. Three important initialization parameters for performance tuning are the
DB_BLOCK_BUFFERS, the SHARED_POOL_SIZE, and the OPTIMIZER_MODE. The DB_BLOCK_BUFFERS and the SHARED_POOL_SIZE combine to create memory space

in which Oracle8 executes commands and retrieves data. Monitor these with the Performance Monitor tool. The OPTIMIZER_MODE enables you to choose the default optimization mode for the database. In addition, any individual session can specify the OPTIMIZER_MODE for that session. When tuning SQL commands, use the ANALYZE, EXPLAIN PLAN, and HINT commands. In addition, pay close attention to Indexing of appropriate Table Columns. An understanding of how the cost-based Optimizer chooses or eliminates Indexes is important to SQL code writers. This chapter describes some of the common Indexing pitfalls. The next chapter focuses on another important function: database backups.