Tài liệu ORACLE8i- P20 ppt

756 CHAPTER 16 • ORACLE8i SQL PERFORMANCE MONITORING AND TUNING

which results in the following execution plan and statistics from Autotrace:

Elapsed: 00:00:00.41

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=RULE

1 0 NESTED LOOPS

2 1 TABLE ACCESS (BY INDEX ROWID) OF ‘EMPLOYEE’

3 2 INDEX (RANGE SCAN) OF ‘ID_EMP’ (NON-UNIQUE)

4 1 INDEX (RANGE SCAN) OF ‘ID_DEPT’ (NON-UNIQUE)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

24 consistent gets

0 physical reads

14 rows processed

Notice the index lookup on the EMPLOYEE and DEPT tables. Every employee in

EMPLOYEE is a member of the research department. Notice also that EMPLOYEE is

the driving table. We know that DEPT is going to be a better driving table, however,

because only one row is needed from it (as compared to reading every row from

EMPLOYEE). So we’re scanning the index on the EMPLOYEE table for every

employee, and then we have to scan the DEPT index for every row in the EMPLOYEE

table. This is costing us 24 consistent gets. What if we just did a full table scan of the

EMPLOYEE table? We’re reading every row anyway, so wouldn’t it help? Let’s see.

To force the optimizer to do a full table scan, append a +0 to the left side of the

EMPNO < 7800 line in the WHERE clause:

SELECT empno, dname

FROM dept b, EMPLOYEE a

WHERE dname=’RESEARCH’

and empno+0< 7950

and a.deptno=b.deptno;

In the following output, you’ll see there’s not much improvement. In fact, we’ve

managed to increase the number of logical I/Os by 12. Also, the runtime has about

doubled.

Elapsed: 00:00:00.51

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=RULE

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com

757

1 0 NESTED LOOPS

2 1 TABLE ACCESS (FULL) OF ‘EMPLOYEE’

3 1 INDEX (RANGE SCAN) OF ‘ID_DEPT’ (NON-UNIQUE)

Statistics

----------------------------------------------------------

0 recursive calls

12 db block gets

24 consistent gets

0 physical reads

0 sorts (memory)

0 sorts (disk)

14 rows processed

Tuning is an iterative thing, so you try and try again. Let’s try again. We know we

want DEPT to be the driving table. Let’s disable the index on the DEPT table and see

what happens:

SELECT empno, dname

FROM dept b, EMPLOYEE a

WHERE dname=’RESEARCH’

and empno< 7950

and a.deptno||’ ’=b.deptno||’ ’;

Now look at the runtime; it’s slightly better—and the logical I/Os are very much

improved!

Elapsed: 00:00:00.40

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=RULE

1 0 NESTED LOOPS

2 1 TABLE ACCESS (FULL) OF ‘DEPT’

3 1 TABLE ACCESS (BY INDEX ROWID) OF ‘EMPLOYEE’

4 3 INDEX (RANGE SCAN) OF ‘ID_EMP’ (NON-UNIQUE)

Statistics

----------------------------------------------------------

0 recursive calls

4 db block gets

11 consistent gets

0 physical reads

0 sorts (memory)

0 sorts (disk)

14 rows processed

TUNING SQL STATEMENTS

Beyond Simple

Database Management

PART

III

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com

758 CHAPTER 16 • ORACLE8i SQL PERFORMANCE MONITORING AND TUNING

So the big gain here appears to have come from disabling the DEPT index, which

caused the RBO to make the DEPT table the driving table. Instead of 24 logical I/Os,

we got 15—a 62 percent improvement in performance. Not bad. As you can see, dis￾abling indexes can be a good thing or it can be a bad thing.

If you are using CBO, you can also disable the use of an index by using the

NO_INDEX hint. You can use the hint without any parameters, or you can include

parameters that cause certain indexes to be ignored by the optimizer. For example, if

you didn’t want to use the IX_EMPLOYEE index, you’d use

NO_INDEX(ix_employee)

Tuning Rule-Based Statements

One of the unfortunate facts of life about RBO is that the tuning options are some￾what limited. In this section we will discuss some issues that can make an impact on

the overall performance of your RBO-based SQL statements.

Choosing the Driving Table Wisely

As discussed, the driving table is the table that provides the row sources to be resolved

in the second table in the join. Often the driving table will be accessed through a full

table scan, if the column is not indexed. In a rule-based statement, the driving table is

generally the table with the lowest access path, but not always. When the access paths

are alike between two tables, you can change the join order in RBO by reversing the

table order in the FROM clause. This is because the last table in the FROM clause will

be the driving table, if the access paths for both tables are the same. Because the dri￾ving table is the main row source, you always want it to be the table that will generate

the smallest and thus most selective row source.

Let’s examine an example in which you can see the impact of a well-chosen dri￾ving table. Assume you have an EMP table and a DEPT table, both of which are not

indexed. Here is the first query:

SELECT a.empno, b.dname

FROM emp a, dept b

WHERE a.deptno=b.deptno;

And here are the execution plan and performance stats:

Elapsed: 00:00:39.87

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=RULE

1 0 MERGE JOIN

2 1 SORT (JOIN)

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com

3 2 TABLE ACCESS (FULL) OF ‘DEPT’

4 1 SORT (JOIN)

5 4 TABLE ACCESS (FULL) OF ‘EMP’

Statistics

----------------------------------------------------------

2072 recursive calls

325 db block gets

2227 consistent gets

3324 physical reads

25536 redo size

1391187 bytes sent via SQL*Net to client

212546 bytes received via SQL*Net from client

1913 SQL*Net roundtrips to/from client

7 sorts (memory)

1 sorts (disk)

28672 rows processed

In this case, the DEPT table is the driving table. Notice that both tables are using

full table scans, so they are ranked equally. To continue our example, let’s reverse the

order of the tables in the FROM clause and make the employee table the driving table,

as follows:

SELECT a.empno, b.dname

FROM dept b, emp a

WHERE a.deptno=b.deptno;

Take a look at the execution plan and performance stats now, and notice the reversal

of the join order of the DEPT and EMP tables:

Elapsed: 00:00:30.14

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=RULE

1 0 MERGE JOIN

2 1 SORT (JOIN)

3 2 TABLE ACCESS (FULL) OF ‘EMP’

4 1 SORT (JOIN)

5 4 TABLE ACCESS (FULL) OF ‘DEPT’

Statistics

----------------------------------------------------------

2072 recursive calls

297 db block gets

2227 consistent gets

TUNING SQL STATEMENTS 759

Beyond Simple

Database Management

PART

III

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Copyright ©2002 SYBEX, Inc., Alameda, CA www.sybex.com