oracle 9i the complete reference phần 8 doc

The analyze command can be used to generate a listing of the chained rows within a table.

This listing of chained rows can be stored in a table called CHAINED_ROWS. To create the

CHAINED_ROWS table in your schema, run the utlchain.sql script (usually found in the /rdbms/

admin subdirectory under the Oracle home directory).

To populate the CHAINED_ROWS table, use the list chained rows into clause of the analyze

command, as shown in the following listing:

analyze TABLE BIRTHDAY list chained rows into CHAINED_ROWS;

The CHAINED_ROWS table lists the Owner_Name, Table_Name, Cluster_Name (if the table

is in a cluster), Partition_Name (if the table is partitioned), Subpartition_Name (if the table contains

subpartitions), Head_RowID (the RowID for the row), and an Analyze_TimeStamp column that

shows the last time the table or cluster was analyzed. You can query the table based on the

Head_RowID values in CHAINED_ROWS, as shown in the following example:

select * from BIRTHDAY

where RowID in

(select Head_RowID

from CHAINED_ROWS

where Table_Name = 'BIRTHDAY');

If the chained row is short in length, then it may be possible to eliminate the chaining by

deleting and reinserting the row.

PLAN_TABLE

When tuning SQL statements, you may want to determine the steps that the optimizer will take to

execute your query. To view the query path, you must first create a table in your schema named

PLAN_TABLE. The script used to create this table is called utlxplan.sql, and is usually stored in

the /rdbms/admin subdirectory of the Oracle software home directory.

After you have created the PLAN_TABLE table in your schema, you can use the explain plan

command, which will generate records in your PLAN_TABLE, tagged with the Statement_ID

value you specify for the query you want to have explained:

explain plan

set Statement_ID = 'MYTEST'

for

select * from BIRTHDAY

where LastName like 'S%';

The ID and Parent_ID columns in PLAN_TABLE establish the hierarchy of steps (Operations)

that the optimizer will follow when executing the query. See Chapter 38 for details on the Oracle

optimizer and the interpretation of PLAN_TABLE records.

Interdependencies: USER_DEPENDENCIES

and IDEPTREE

Objects within Oracle databases can depend upon each other. For example, a stored procedure

may depend upon a table, or a package may depend upon a package body. When an object

Chapter 37: The Hitchhiker’s Guide to the Oracle9i Data Dictionary 717

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 37

Blind Folio 37:717

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:15:02 PM

Color profile: Generic CMYK printer profile

Composite Default screen

within the database changes, any procedural object that depends upon it will have to be recompiled.

This recompilation can take place either automatically at runtime (with a consequential performance

penalty) or manually (see Chapter 29 for details on compiling procedural objects).

Two sets of data dictionary views are available to help you track dependencies. The first is

USER_DEPENDENCIES, which lists all direct dependencies of objects. However, this only goes

one level down the dependency tree. To fully evaluate dependencies, you must create the recursive

dependency-tracking objects in your schema. To create these objects, run the utldtree.sql script

(usually located in the /rdbms/admin subdirectory of the Oracle home directory). This script creates

two objects you can query: DEPTREE and IDEPTREE. They contain identical information, but

IDEPTREE is indented based on the pseudo-column Level, and is thus easier to read and interpret.

DBA-Only Views

Since this chapter is intended for use by developers and end users, the data dictionary views

available only to DBAs are not covered here. The DBA-only views are used to provide information

about distributed transactions, lock contention, rollback segments, and other internal database

functions. For information on the use of the DBA-only views, see the Oracle9i Database

Administrator’s Guide.

Oracle Label Security

Users of Oracle Label Security can view additional data dictionary views, including ALL_SA_

GROUPS, ALL_SA_POLICIES, ALL_SA_USERS, and ALL_SA_USER_PRIVS. For details on the

usage of these views, see the Oracle Label Security Administrator’s Guide.

SQL*Loader Direct Load Views

To manage the direct load option within SQL*Loader, Oracle maintains a number of data

dictionary views. These generally are only queried for debugging purposes, upon request from

Oracle Customer Support. The SQL*Loader direct load option is described under the “SQLLDR”

entry in the Alphabetical Reference; its supporting data dictionary views are listed here:

■ LOADER_COL_INFO

■ LOADER_CONSTRAINT_INFO

■ LOADER_FILE_TS

■ LOADER_PARAM_INFO

■ LOADER_PART_INFO

■ LOADER_REF_INFO

■ LOADER_TAB_INFO

■ LOADER_TRIGGER_INFO

For details on the use of these views, see the catldr.sql script, usually located in the

/rdbms/admin subdirectory of the Oracle home directory.

718 Part VI: Hitchhiker’s Guides

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 37

Blind Folio 37:718

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:15:02 PM

Color profile: Generic CMYK printer profile

Composite Default screen

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 37

Blind Folio 37:719

Chapter 37: The Hitchhiker’s Guide to the Oracle9i Data Dictionary 719

National Language Support (NLS) Views

Three data dictionary views are used to display information about the National Language Support

parameters currently in effect in the database. Nonstandard values for the NLS parameters (such

as NLS_DATE_FORMAT and NLS_SORT) can be set via the database’s parameter file or via the

alter session command. (See the alter session command in the Alphabetical Reference for further

information on NLS settings.) To see the current NLS settings for your session, instance, and database,

query NLS_SESSION_PARAMETERS, NLS_INSTANCE_PARAMETERS, and NLS_DATABASE_

PARAMETERS, respectively.

Libraries

Your PL/SQL routines (see Chapter 27) can call external C programs. To see which external C

program libraries are owned by you, you can query USER_LIBRARIES, which displays the name of

the library (Library_Name), the associated file (File_Spec), whether or not the library is dynamically

loadable (Dynamic), and the library’s status (Status). ALL_LIBRARIES and DBA_LIBRARIES are

also available; they include an additional Owner column to indicate the owner of the library. For

further information on libraries, see the entry for the create library command in the Alphabetical

Reference.

Heterogeneous Services

To support the management of heterogeneous services, Oracle provides 16 data dictionary views.

All of the views in this category begin with the letters HS instead of DBA. In general, these views

are used primarily by DBAs. For details on the HS views, see the Oracle9i Database Reference.

Indextypes and Operators

Operators and indextypes are closely related. You can use the create operator command to create

a new operator and define its bindings. You can reference operators in indextypes and in SQL

statements. The operators, in turn, reference functions, packages, types, and other user-defined

objects.

You can query the USER_OPERATORS view to see each operator’s Owner, Operator_Name,

and Number_of_Binds values. Ancillary information for operators is accessible via USER_

OPANCILLARY, and you can query USER_OPARGUMENTS to see the operator arguments.

You can query USER_OPBINDINGS to see the operator bindings.

USER_INDEXTYPE_OPERATORS lists the operators supported by indextypes. Indextypes, in

turn, are displayed via USER_INDEXTYPES. There are “ALL” and “DBA” views of all the operator

and indextype views.

Outlines

When you use stored outlines, you can retrieve the name of, and details for, the outlines

via the USER_OUTLINES data dictionary views. To see the hints that make up the outlines,

query USER_OUTLINE_HINTS. There are “ALL” and “DBA” versions of USER_OUTLINES and

USER_OUTLINE_HINTS.

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:15:03 PM

Color profile: Generic CMYK printer profile

Composite Default screen

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 37

Blind Folio 37:720

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:15:03 PM

Color profile: Generic CMYK printer profile

Composite Default screen

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 38

Blind Folio 38:721

CHAPTER

38

The Hitchhiker’s

Guide to the

Oracle Optimizer

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:15:03 PM

Color profile: Generic CMYK printer profile

Composite Default screen

722 Part VI: Hitchhiker’s Guides

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 38

Blind Folio 38:722

W ithin the relational model, the physical location of data is unimportant.

Within Oracle, the physical location of your data and the operation used to

retrieve the data are unimportant—until the database needs to find the data.

If you query the database, you should be aware of the operations Oracle

performs to retrieve and manipulate the data. The better you understand the

execution path Oracle uses to perform your query, the better you will be able to manipulate and

tune the query.

In this chapter, you will see the operations Oracle uses to query and process data, presented

from a user’s perspective. First, the operations that access tables are described, followed by index

access operations, data set operations, joins, and miscellaneous operations. For each type of

operation, relevant tuning information is provided to help you use the operation in the most

efficient and effective manner possible.

The focus of this chapter is the operations Oracle goes through when executing SQL

statements. If you are attempting to tune an application, you should evaluate the application

architecture and operating environment to determine if they are appropriate for your users’

requirements before examining the SQL. An application that performs a large number of queries

across a slow network just to display a data entry screen will be perceived as slow even if the

database activity portion is fast; tuning the SQL in that example may yield little in the way of

performance improvement.

Before beginning to tune your queries, you need to decide which optimizer you will be using.

Which Optimizer?

The Oracle optimizer has two primary modes of operation: cost-based or rule-based. To set

the optimizer goal, you can specify CHOOSE (for cost-based) or RULE (for rule-based) for the

OPTIMIZER_MODE parameter in your database’s initialization parameter file. You can override

the optimizer’s default operations at the query and session level, as shown later in this chapter.

NOTE

As of Oracle9i, you can store parameters in a system parameter

file, replacing the init.ora parameter file used in prior versions.

Setting OPTIMIZER_MODE to RULE invokes the rule-based optimizer (RBO), which evaluates

possible execution paths and rates the alternative execution paths based on a series of syntactical

rules. In general, the RBO is seldom used by new applications, and is found primarily in applications

developed and tuned for earlier versions of Oracle.

Setting OPTIMIZER_MODE to CHOOSE invokes the cost-based optimizer (CBO). You can use

the analyze command and the DBMS_STATS package to generate statistics about the objects in

your database. The generated statistics include the number of rows in a table and the number of

distinct keys in an index. Based on the statistics, the CBO evaluates the cost of the available

execution paths and selects the execution path that has the lowest relative cost. If you use the CBO,

you need to make sure that you analyze the data frequently enough for the statistics to accurately

reflect the data within your database. If a query references tables that have been analyzed and

tables that have not been analyzed, the CBO selects values for the missing statistics—and it may

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:15:04 PM

Color profile: Generic CMYK printer profile

Composite Default screen

decide to perform an inappropriate execution path. To improve performance, you should use either

the RBO or the CBO consistently throughout your database. Since the CBO supports changes in

data volumes and data distribution, you should favor its use.

To use the CBO, you should first analyze your tables and indexes. You can analyze individual

tables, indexes, partitions, or clusters via the analyze command (see the Alphabetical Reference

for the full syntax). When analyzing, you can scan the full object (via the compute statistics

clause) or part of the object (via the estimate statistics clause). In general, you can gather

adequate statistics by analyzing 10 to 20 percent of an object—in much less time than you

would need to compute the statistics. Here is a sample analyze command:

analyze table BOOKSHELF estimate statistics;

Once you have analyzed an object, you can query the statistics-related columns of the

data dictionary views to see the values generated. See Chapter 37 for a description of those

views and their statistics-related columns.

The DBMS_STATS package is a replacement for the analyze command, and is the

recommended method as of Oracle9i. The GATHER_TABLE_STATS procedure within

DBMS_STATS requires two parameters: the schema owner and the name of the table; all

other parameters (such as partition name and the percent of the table to be scanned via the

estimate statistics method) are optional. The following command gathers the statistics for the

BOOKSHELF table in the PRACTICE schema:

execute DBMS_STATS.GATHER_TABLE_STATS('PRACTICE','BOOKSHELF');

Other procedures within DBMS_STATS include GATHER_INDEX_STATS (for indexes),

GATHER_SCHEMA_STATS (for all objects in a schema), GATHER DATABASE_STATS (for all

objects in the database), and GATHER_SYSTEM_STATS (for system statistics). You can use

other procedures within the DBMS_STATS package to migrate statistics from one database to

another, avoiding the need to recalculate statistics for different copies of the same tables. See

Oracle’s Supplied PL/SQL Packages and Types Reference for further information on the

DBMS_STATS package.

The examples in this section assume that the cost-based optimizer is used and that the tables

and indexes have been analyzed.

Operations That Access Tables

Two operations directly access the rows of a table: a full table scan and a RowID-based access

to the table. For information on operations that access table rows via clusters, see “Queries That

Use Clusters,” later in this chapter.

TABLE ACCESS FULL

A full table scan sequentially reads each row of a table. The optimizer calls the operation used

during a full table scan a TABLE ACCESS FULL. To optimize the performance of a full table

scan, Oracle reads multiple blocks during each database read.

Chapter 38: The Hitchhiker’s Guide to the Oracle Optimizer 723

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 38

Blind Folio 38:723

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:15:04 PM

Color profile: Generic CMYK printer profile

Composite Default screen

A full table scan is used whenever there is no where clause on a query. For example, the

following query selects all of the rows from the BOOKSHELF table:

select *

from BOOKSHELF;

To resolve the preceding query, Oracle will perform a full table scan of the BOOKSHELF

table. If the BOOKSHELF table is small, a full table scan of BOOKSHELF may be fairly quick,

incurring little performance cost. However, as BOOKSHELF grows in size, the cost of performing

a full table scan grows. If you have multiple users performing full table scans of BOOKSHELF,

then the cost associated with the full table scans grows even faster.

With proper planning, full table scans need not be performance problems. You should

work with your database administrators to make sure the database has been configured to take

advantage of features such as the Parallel Query Option and multiblock reads. Unless you have

properly configured your environment for full table scans, you should carefully monitor their use.

NOTE

Depending on the data being selected, the optimizer may choose

to use a full scan of an index in place of a full table scan.

You can display Oracle’s chosen execution path via a feature called an “explain plan.” You

will see how to generate explain plans later in this chapter. For this example, the explain plan

would look like this:

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=31 Bytes=1209

)

1 0 TABLE ACCESS (FULL) OF 'BOOKSHELF' (Cost=1 Card=31 Bytes=1

209)

The TABLE ACCESS (FULL) operation shown in the explain plan shows that the optimizer

chose to perform a full table scan of the BOOKSHELF table. Each step has an ID (in this case, 0

and 1) with step 0 being the step that returns data to the user. Steps may have parent steps (in this

case, step 1 provides its data to step 0, so it is listed as having a parent ID of 0). As queries grow

more complicated, the explain plans grow more complicated. The emphasis in this chapter is on

the operations Oracle uses; you can verify the steps by generating the explain plans. To simplify

the discussion, a walkthrough of the generation and interpretation of complex explain plans will

be deferred until later in the chapter; a graphical method of depicting the explain plan will be

used to describe the major execution path steps and data flow.

TABLE ACCESS BY ROWID

To improve the performance of table accesses, you can use Oracle operations that access rows

by their RowID values. The RowID records the physical location where the row is stored. Oracle

uses indexes to correlate data values with RowID values—and thus with physical locations of the

data. Given the RowID of a row, Oracle can use the TABLE ACCESS BY ROWID operation to

retrieve the row.

724 Part VI: Hitchhiker’s Guides

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 38

Blind Folio 38:724

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:15:05 PM

Color profile: Generic CMYK printer profile

Composite Default screen

When you know the RowID, you know exactly where the row is physically located. However,

you do not need to memorize the RowIDs for your rows; instead, you can use indexes to access

the RowID information, as described in the next major section, “Operations That Use Indexes.”

Because indexes provide quick access to RowID values, they help to improve the performance

of queries that make use of indexed columns.

Related Hints

Within a query, you can specify hints that direct the CBO in its processing of the query. To

specify a hint, use the syntax shown in the following example. Immediately after the select

keyword, enter the following string:

/*+

Next, add the hint, such as

FULL(bookshelf)

Close the hint with the following string:

*/

Hints use Oracle’s syntax for comments within queries, with the addition of the “+” sign

at the start of the hint. Throughout this chapter, the hints relevant to each operation will be

described. For table accesses, there are two relevant hints: FULL and ROWID. The FULL hint

tells Oracle to perform a full table scan (the TABLE ACCESS FULL operation) on the listed table,

as shown in the following listing:

select /*+ FULL(bookshelf) */ *

from BOOKSHELF

where Title like 'T%';

If you did not use the FULL hint, Oracle would normally plan to use the primary key index

on the Title column to resolve this query. Since the table is presently small, the full table scan is

not costly. As the table grows, you would probably favor the use of a RowID-based access for

this query.

The ROWID hint tells the optimizer to use a TABLE ACCESS BY ROWID operation to

access the rows in the table. In general, you should use a TABLE ACCESS BY ROWID operation

whenever you need to return rows quickly to users and whenever the tables are large. To use

the TABLE ACCESS BY ROWID operation, you need to either know the RowID values or use

an index.

Operations That Use Indexes

Within Oracle are two major types of indexes: unique indexes, in which each row of the indexed

table contains a unique value for the indexed column(s), and nonunique indexes, in which the

rows’ indexed values can repeat. The operations used to read data from the indexes depend on

the type of index in use and the way in which you write the query that accesses the index.

Chapter 38: The Hitchhiker’s Guide to the Oracle Optimizer 725

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 38

Blind Folio 38:725

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:15:05 PM

Color profile: Generic CMYK printer profile

Composite Default screen

726 Part VI: Hitchhiker’s Guides

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 38

Blind Folio 38:726

Consider the BOOKSHELF table:

create table BOOKSHELF

(Title VARCHAR2(100) primary key,

Publisher VARCHAR2(20),

CategoryName VARCHAR2(20),

Rating VARCHAR2(2),

constraint CATFK foreign key (CategoryName)

references CATEGORY(CategoryName));

The Title column is the primary key for the BOOKSHELF table—that is, it uniquely identifies each

row, and each attribute is dependent on the Title value.

Whenever a PRIMARY KEY or UNIQUE constraint is created, Oracle creates a unique index

to enforce uniqueness of the values in the column. As defined by the create table command, a

PRIMARY KEY constraint will be created on the BOOKSHELF table. The index that supports the

primary key will be given a system-generated name, since the constraint was not explicitly named.

You can create indexes on other columns of the BOOKSHELF table manually. For example,

you could create a nonunique index on the CategoryName column via the create index

command:

create index BOOKSHELF$CATEGORY

on BOOKSHELF(CategoryName)

tablespace INDEXES

compute statistics;

The BOOKSHELF table now has two indexes on it: a unique index on the Title column, and

a nonunique index on the CategoryName column. One or more of the indexes could be used

during the resolution of a query, depending on how the query is written and executed. As part

of the index creation, its statistics were gathered via the compute statistics clause. Since the

table is already populated with rows, you do not need to execute a separate command to

analyze the index.

INDEX UNIQUE SCAN

To use an index during a query, your query must be written to allow the use of an index. In most

cases, you allow the optimizer to use an index via the where clause of the query. For example,

the following query could use the unique index on the Title column:

select *

from BOOKSHELF

where Title = 'INNUMERACY';

Internally, the execution of the preceding query will be divided into two steps. First, the Title

column index will be accessed via an INDEX UNIQUE SCAN operation. The RowID value that

matches the title ‘INNUMERACY’ will be returned from the index; that RowID value will then be

used to query BOOKSHELF via a TABLE ACCESS BY ROWID operation.

If all of the columns selected by the query had been contained within the index, then Oracle

would not have needed to use the TABLE ACCESS BY ROWID operation; since the data would

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:15:05 PM

Color profile: Generic CMYK printer profile

Composite Default screen