Oracle Enterprise Manager Database Tuning with the Oracle Tuning Pack -- Index

access methods

in application tuning, 12-6

lack of support for rules-based optimizer, 12-6

support for rules-based optimizer, 12-6

adding

an object, 14-1

advantages of Oracle Expert, 19-1

advantages of using Oracle Expert, 9-1

allocated blocks, 5-18, 5-20, 5-23

always anti-join, 5-3

analysis

canceling, 15-3

excluding objects from, 15-2

Analysis report, 10-7

generating, 17-1

Analysis Report dialog box, 17-1

ANALYZE SQL command, glossary-1

ANALYZE statement

during Schema class collection, 13-10

application

excluding from an analysis, 15-2

invalid, 13-18

reason for being invalid, 13-18

used in workload, 14-6

attribute

of a database object, 14-2

of an instance object, 14-2

Autotune, 20-1

implementing recommendations, 20-3

starting, 20-2

stopping, 20-2

viewing recommendations, 20-2

average blocks per cluster key, 5-20

average data blocks per key, 5-24

average free space per block, 5-19

average leaf blocks per key, 5-24

average row length, 5-19

base index. See index.

bind variables, glossary-2

Bitmap Indexes

definition, glossary-2

bitmap merge area size, 5-4

blank trimming, 5-4

canceling

a collection, 13-17

an analysis, 15-3

cardinality, 5-34

collecting estimated values, 13-10

collecting exact values, 13-10

collecting for columns, 13-10

collecting for tables, 13-10

collecting using Oracle Expert, 13-9

collecting using SQL ANALYZE, 13-9

cardinality data

in schema tuning, 13-10

cardinality values

importance of, 14-3

Cartesian products, 5-35

chained and/or migrated rows, 5-18

Chained Rows (also known as migrated rows), glossary-3

choose, glossary-3

class. See collection class.

cluster

as schema object, 14-4

reason for being invalid, 13-18

cluster details

average blocks per cluster key, 5-20

displaying, 5-20, 5-22

distinct hash values, 5-21

Cluster Properties, 5-19

Cluster Statistics dialog, 5-20

clustering factor, 5-24

Collect Options property sheet

displaying, 13-3

System page, 13-12

Workload page, 13-14

Collect page. See tuning session window.

collecting data, 13-1

classes of data, 10-3

incomplete data, 13-17

invalid data, 13-17

unusable data, 13-17

collection

canceling, 13-17

errors during, 13-17

restrictions during, 13-17

collection class

collecting data efficiently, 13-3

Database class, 13-4

description, 13-1

determining whether to collect, 13-3

Instance class, 13-6

reducing collection time, 13-3

Schema class, 13-9

size of, 13-2

source of, 13-2

summary table, 13-2

System class, 13-11

volatility of, 13-2

Workload class, 13-13

column

obtaining cardinality values, 13-10

Column Name, 5-22

Column Statistics, 5-22

column name, 5-22

density, 5-22

distinct values, 5-22

compact view

definition, glossary-4

compact views, 5-36

walking through, 5-36

comparing

execution statistics, 5-38

explain plans, 5-38

SQL statements, 5-38

compatible, 5-4

comprehensive tuning, 12-3

description, 9-4

configuring a new database

initial configuration, 19-2

reconfiguration, 19-3

connecting

to an Oracle Trace database, 13-16

cost-based optimization for response time, glossary-4

cost-based optimization for throughput, glossary-5

CPU data

used in System class, 13-12

Creating a SQL History, 11-4

creating a tuning session, 12-1

Cross Reference report

generating, 17-2

Cursor, glossary-5

cursor space for time, 5-5

data

analyzing, 10-4

collecting, 10-3, 13-1

collecting Database class, 13-4

collecting incomplete, 13-17

collecting Instance class, 13-6

collecting invalid, 13-17

collecting Schema class, 13-9

collecting System class, 13-11

collecting unusable, 13-17

collecting Workload class, 13-13

editing, 10-4, 14-1

excluding from an analysis, 15-2

providing complete and accurate, 18-2

viewing, 10-4, 14-1

data collection. See collecting data.

Data Definition Statements (DDL), glossary-6

data dictionary, glossary-6

using in schema tuning, 13-10

Data Manipulation Statements (DML), glossary-6

database

identifying, 11-3

privileges Oracle Expert requires to access, 12-2

steps in initial configuration, 19-2

when to configure a new, 19-2

workload, 21-1

database block buffers, 5-5

Database Buffer Cache, 5-6

Database class, 13-4

automatic data collection, 13-5

collecting, 13-4

Database Users category, 13-4

deleting previously collected data for, 13-5

frequency of collection, 13-5

Name/Version category, 13-4

Public Synonyms category, 13-4

Tablespaces category, 13-4

when to collect, 13-5

database design

providing information for, 19-3

database file multi-block read count, 5-6

database object, glossary-5

attribute, 14-2

database user, 14-5

editing, 14-2

instance object, 14-2

public synonym, 14-4

rule, 14-2

schema object, 14-3

tablespace, 14-4

viewing, 14-2

Database page. See Collect Options property sheet.

Database Parameters View

opening, 5-2

database tuning, 9-2

initial configuration, 19-1

issues, 9-2

process, 3-3

resolving issues, 9-3

tasks, 9-2

database tuning. See also performance tuning.

database user

as database object, 14-5

Database Users category

of Database class, 13-4

Decision Support System (DSS), glossary-7

degree of parallelism, glossary-8

deleting

an object, 14-1

deleting disconnected nodes, 4-5

Density, 5-22

Details Window, 4-7

disable tuning rule, 14-3

disconnected nodes, 4-5, 4-6

deleting, 4-5

displaying Cluster Details, 5-20

displaying Index Details, 5-22

displaying Table Details, 5-17

distinct hash values, 5-21

distinct keys, 5-23

Distinct Values, 5-22

Do not use columns on both sides of operator, 6-10

dynamic performance table, 13-6

editing data, 10-4, 14-1

Review page, 14-1

Editing Initialization Parameters, 5-8

empty blocks, 5-18

cluster details

empty blocks, 5-21

enable tuning rule, 14-3

Entering a New Statement, 4-13

Environment page. See Collect Options property sheet.

error

possible cause of "table not found" error, 12-2

Estimated/Limit text box

collecting estimated statistics data, 13-10

examining views, 5-25

Excessive row migration, 23-2

execution statistics

comparing, 5-38

viewing, 5-37

execution step, 5-34, 5-36

executions, 4-11

expected rows, 5-34, 5-37

Expert Definition Language. See XDL file.

Expert Scan option

collecting cardinality data using, 13-10

explain plan, glossary-8

execution step, 5-34

expected rows, 5-34

operation node, 5-35

operation type, 5-35

partition ID, 5-35

partition start, 5-35

partition stop, 5-35

query text, 5-35

explain plan object, glossary-8

Explain plan rules-of-thumb

Explain plan, 5-35

explain plans

comparing, 5-38

extent sizes

inefficient, 23-3

extents, 5-17

cluster details

extents, 5-20

index details

extents, 5-23

file

saving, 4-14

filtered workload, 13-14

focused tuning

description, 9-4

instance tuning

I/O parameter, 12-5

OS specific parameter, 12-5

parallel query parameter, 12-5

SGA parameter, 12-4

sort parameter, 12-5

frequency, 14-5

from Tablespace Map, 24-4

Full Scan option

collecting exact statistics data, 13-10

Full Table, 5-36

Full Table scans as non-driving tables in nested loop joins, 5-36

General Details, 5-16

generating recommendations, 10-4

generating reports

Analysis report, 17-1

Recommendation Summary report, 17-2

Session Data report, 17-2

hash area size, 5-8

hash join enabled, 5-9

hash multi-block I/O count, 5-9

hash values, 5-21

hints, glossary-9

historical data

importance of, 13-8, 14-3

How This Manual is Organized, xvi

implementation files

description, 16-1

how to use, 16-2

implementation scripts, 10-5, 10-7

location, 16-1

tuning output, 10-7

implementation scripts, 10-5, 10-7

implementing recommendations, 10-5, 16-1

importance

used in workload

description, 14-5

importance value

in workload class, 13-13

in workload data, 21-5

possible values for, 21-5

specifying, 21-5

Importing SQL Statements, 4-14

incomplete data

collecting, 13-17

index

attributes, 14-4

in access method tuning, 12-6

reason for being invalid, 13-18

tuning in access method tuning, 12-7

Index merges, 5-35

index properties, 5-22

Index Statistics dialog, 5-22

indexes

disorganized, 23-2

Inefficient extent sizes, 23-3

initial configuration, 19-2

of a database, 19-1

providing information for, 19-3

initialization parameter object, glossary-10

initialization parameters, 5-7

editing, 5-8

showing, 5-7

viewing, 5-7

INIT.ORA file

using for tuning recommendations, 16-2

Instance class, 13-6

canceling multiple instance statistics samples, 13-7

collecting, 13-9

collecting multiple instance statistics samples, 13-7

importing from .XDL file, 13-7

instance parameters category, 13-6

Instance Statistics category, 13-6

tuning multiple instances, 13-6

when to collect, 13-9

instance object, 14-2

attribute, 14-2

rule, 14-2

statistics attribute, 14-2

Instance Optimizations, 9-4, 12-4

Instance Parameters category of Instance class, 13-6

instance recommendations

implementing, 16-2

instance rules, 14-2

instance statistics

canceling collection of multiple samples, 13-7

collecting multiple samples, 13-7

how instance is performing, 13-6

maintaining history of, 13-6

sample, 13-6

Instance Statistics category

of Instance class, 13-6

instance tuning

description, 12-4

I/O parameter, 12-5

OS specific parameter, 12-5

parallel query parameter, 12-5

parallel server parameter, 12-5

recommendations, 13-8

SGA parameter, 12-4

sort parameter, 12-5

invalid data

application, 13-18

cluster, 13-18

collecting, 13-17

discovered during a collection, 13-18

discovered during an analysis, 15-3

excluding from an analysis, 13-18

index, 13-18

request, 13-18

schema, 13-18, 15-3

symbol used to mark, 13-18

table, 13-18

I/O parameter

in instance tuning, 12-5

join method, 5-36

join strategy optimization, glossary-11

leaf blocks, 5-23

logical device data

deleting previously collected, 13-13

keeping previously collected, 13-13

Main window, 4-4

Managing SQL History Data, 10-3

matching. See SQL statement matching.

memory data

used in System class, 13-12

methodology

steps in Oracle Expert, 10-1

Migrated Rows, 5-18

migrated rows, 23-1

Name/Version category

of Database class, 13-4

Navigator Pane, 4-5

Navigator window, 4-4

New Tuning Session dialog box

Scope page, 12-3

NLS Calendar, 5-12

Parameters, 5-12

NLS Currency, 5-13

Parameters, 5-13

NLS Date Format, 5-13

Parameters, 5-13

NLS Date Language, 5-13

Parameters, 5-13

NLS ISO Currency, 5-14

Parameters, 5-14

NLS Language, 5-14

Parameters, 5-14

NLS Numeric Characters, 5-14

Parameters, 5-14

NLS sort, 5-14

nodes

disconnected, 4-5, 4-6

object

invalid, 13-18

object details, glossary-11

general, 5-16

object name, 5-36

object owner, 5-37

object properties, 5-16

viewing, 5-16

Online Transaction Processing (OLTP), glossary-12

opening

an existing tuning session, 12-9

Opening a Previously Used Tuning Session, 4-14

Opening the Database Parameters View, 5-2

operating system data

used in System class, 13-12

operation node, 5-35

operation type, 5-35

Optimal Data Access, 12-6

optimizer, glossary-12

cost-based

access method rules addressing, 12-6

rules-based

lack of rules addressing, 12-6

Optimizer Index Cost Adjustment, 5-9

Paremeters, 5-9

Optimizer Maximum Permutation, 5-10

Parameters, 5-10

optimizer mode, 5-7

optimizer percent parallel, 5-10

optimizer search limit, 5-10

Oracle Index Tuning wizard, 22-1

Oracle Server

impact of installing new version, 13-9

Oracle SQL Analyze

Benefits, 3-2

Introduction, 3-2

Main window, 4-4

Oracle SQL Analyze Repository, 4-3

Oracle Trace

using to collect workload data, 13-16, 21-3

using to format raw data, 21-4

Oracle Trace database

collecting workload data from, 13-16

connecting to, 13-16

importing data from, 21-4

OS specific parameter

in instance tuning, 12-5

Outline Management

accessing, 25-2

using, 25-3

output. See tuning output.

Overwrite Existing option, 13-5, 13-13

panes

Navigator, 4-5

Parallel query bottlenecks, 5-36

parallel query option

impact of enabling, 13-5, 13-9

parallel query parameter

in instance tuning, 12-5

parallel server option

impact of enabling, 13-5, 13-9

parallel server parameter

in instance tuning, 12-5

parameter file, 10-5

tuning output, 10-7

Parameters

allocated blocks, 5-20, 5-23

always anti-join, 5-3

average data blocks per key, 5-24

average leaf blocks per key, 5-24

bitmap merge area size, 5-4

blank trimming, 5-4

clustering factor, 5-24

compatible, 5-4

cursor space for time, 5-5

database block buffers, 5-5

Database Buffer Cache, 5-6

database file multi-block read count, 5-6

distinct keys, 5-23

empty blocks, 5-18

executions, 4-11

hash area size, 5-8

hash join enabled, 5-9

hash multi-block I/O count, 5-9

initialization, 5-7

leaf blocks, 5-23

NLS sort, 5-14

optimizer mode, 5-7

optimizer percent parallel, 5-10

optimizer search limit, 5-10

parse calls, 4-11

parse calls per execution, 4-11

rows processed, 4-11

sort area size, 5-11

sort direct writes, 5-12

sorts, 4-11

tree depth, 5-23

parameters

initialization, 5-7

parse calls, 4-11

Parse Calls Per Execution, 4-11

parse calls per execution, 4-11

partition ID, 5-35

partition start, 5-35

partition stop, 5-35

password

for instances imported from .XDL files, 13-7

performance tuning

comprehensive tuning, 9-4

focused tuning, 9-4

initial configuration, 9-4

types of, 9-3

performance tuning. See also database tuning.

Personnel session, 9-5

preface

conventions table sample, xvii

Printing, 4-14

privileges

required to access a database, 12-2

Program Global Area (PGA), glossary-13

public synonym

as database object, 14-4

Public Synonyms category of Database class, 13-4

query text, 5-35

Recommendation Summary report, 10-7

generating, 17-2

Recommendation Summary Report dialog box, 17-2

recommendations

declining, 15-5

effect of tuning categories on, 12-3

implementing, 10-5, 16-1

in Analysis report, 17-1, 22-4

obtaining less conservative, 13-8

reviewing, 10-4, 15-4

Recommendations page. See tuning session window.

Remote queries, 5-36

Reorg Wizard, 23-1

Accessing, 23-3

failure prediction, 23-1

Impact Summary Report, 23-6

recovery from reorganization job failure, 23-6

reorganization job submission, 23-6

reviewing reorganization job, 23-6

specifying objects to reorganize, 23-4

reports

Analysis, 17-1

Cross Reference, 17-2

generating, 17-1

Recommendation Summary, 17-2

Session Data, 17-2

repository, 4-3, 4-14

request

invalid, 13-18

used in workload, 14-6

Review page

using with Edit pull-down menu, 14-1

Review page. See also tuning session window.

reviewing recommendations, 10-4, 15-1

rows, 5-19

rows processed, 4-11

Rule

Tunable, 15-2

rule-based optimization, glossary-15

rules, 10-6

general principles of instance, 14-2

of a database object, 14-2

of an instance object, 14-2

taking advantage of, 18-3

rules-of-thumb, 5-35, 6-7, glossary-15

sample tuning session, 9-5

Save to Repository, 4-3

saving, 4-14

file, 4-14

saving a file, 4-14

saving a session, 4-14

Saving Tuning Session Data to a Log File, 12-10

saving your work, 4-3

Scan Columns option

collecting column cardinality, 13-10

schema

reason for being invalid, 13-18, 15-3

Schema category

of Schema class, 13-9

Schema class, 13-9

collecting data from .SQL file, 13-11

collecting data from .XDL file, 13-11

collecting from an instance, 13-9

data tuned, 13-9

frequency of collection, 13-11

Schema category, 13-9

specifying schemas to collect, 13-10

specifying tables to collect, 13-10

Statistics category, 13-9

when to collect data for, 13-11

schema object, 14-3

cluster, 14-4

if missing, 14-3

synonym, 14-4

table, 14-3

used in tuning process, 14-3

view, 14-4

Schema page. See Collect Options property sheet.

Scope page. See New Tuning Session dialog box.

scope. See tuning scope.

Script page. See tuning session window.

Segment Analysis, 24-4

Selecting a Statement for Tuning, 4-8

selecting control parameter values, 12-7

Selecting Statements with TopSQL, 4-9

server

impact of installing new version of, 13-9

service, 11-4

Session Data report, 10-7

generating, 17-2

Session Data Report dialog box, 17-2

session. See tuning session.

setting the scope of a tuning session, 12-3

SGA parameter in instance tuning, 12-4

Showing and Editing Initialization Parameters, 5-7

sort area size, 5-11

sort direct writes, 5-12

sort parameter in instance tuning, 12-5

sorts, 4-11

SQL ANALYZE statistics

collecting during schema collection, 13-9

SQL cache

collecting workload data from, 13-15

SQL Cache option

using during workload collection, 13-15

SQL files

importing from, 4-14

SQL History

creating, 11-4

managing, 10-3

SQL Reuse, 12-5

SQL statement matching, 12-6

SQL statements

ANALYZE, 13-9

collecting data about, 21-3

comparing, 5-38

SQL tuning

SQL statement matching, 12-6

SQL Tuning Wizard, 6-12

process, 6-12

using, 6-12

SQLADMIN Role

VMQROLE.SQL, 4-2

SQLText Window, 4-6

Star Queries, glossary-17

star schema, 5-11

Star Transformation, glossary-18

Star Transformation Enabled, 5-11

Parameters, 5-11

starting TopSQL, 4-9

statistics attribute

of an instance object, 14-2

Statistics category

collecting using Oracle Expert, 13-9

collecting using SQL ANALYZE, 13-9

of Schema class, 13-9

stopping

a collection, 13-17

an analysis, 15-3

Structured Query Language (SQL), glossary-19

Support, 12-10

synonym

as schema object, 14-4

System class, 13-11

CPU data, 13-12

entering data manually, 13-12

frequency of collection, 13-13

importing data from .XDL file, 13-12

memory data, 13-12

operating system data, 13-12

when to collect, 13-13

system data

deleting previously collected, 13-13

keeping previously collected, 13-13

System Global Area, glossary-19

System Global Area. See SGA

system object, 14-5

table

as schema object, 14-3

obtaining cardinality values, 13-10

reason for being invalid, 13-18

used in application tuning, 14-3

table details

allocated blocks, 5-18

average free space per block, 5-19

average row length, 5-19

chained rows

migrated rows, 5-18

displaying, 5-17

extents, 5-17

rows, 5-19

used blocks, 5-18

Table Properties, 5-17

tablespace

as database object, 14-4

Tablespace Map, 24-1

accessing, 24-2

detecting problematic segments, 24-4

launching Reorg Wizard from, 24-6

printing, 24-1

Tablespaces category of Database class, 13-4

terminating

a collection, 13-17

an analysis, 15-3

TopSQL, 3-2, glossary-20

starting, 4-9

using, 4-9

TopSQL object, glossary-20

tree depth, 5-23

Tunable rule

defined, 15-2

Tuning

Tasks

Viewing Object Details and Statistics, 5-16

tuning

excluding data from, 15-2

excluding objects from, 15-2

iterative, 18-2

tuning input, 10-5, 10-6

database class, 10-6

environment class, 10-6

instance class, 10-6

rules, 10-6

schema class, 10-6

tuning session characteristics, 10-6

workload class, 10-6

tuning output, 10-5, 10-7

implementation files, 10-7

parameter file, 10-7

report, 10-7

Tuning Process

Methodology, 3-7

tuning report

Analysis report, 10-7

Recommendation Summary, 10-7

Session Data report, 10-7

tuning scope

changing, 12-9

refining, 18-1

setting, 12-3

tuning session

collecting data, 13-1

creating, 12-1

creating, using Tuning Session wizard, 12-2

deleting, 12-10

modifying, 12-9

opening an existing, 12-9

resuming, 12-9

setting the scope, 10-2, 12-3

tuning session characteristics, glossary-3

providing accurate data, 18-2

selecting values, 12-7

tuning session window

Collect page, 13-1

Recommendations page, 15-4

Scope page, 12-9

View/Edit page, 14-1

Tuning Session wizard, 12-2

Tuning Sessions

Opening previously used, 4-14

Understanding Index Tuning Recommendations, 6-2

understanding rules-of-thumb, 6-7

Understanding Statistical Information, 5-1

unusable data

collecting, 13-17

use operators differently to enable indexes, 6-10

use TRUNC differently to enable indexes, 6-9

use UNION ALL instead of UNION, 6-12

use WHERE in place of HAVING, 6-11

used blocks, 5-18

username

for instances imported from .XDL files, 13-7

users

impact of adding new, 13-6, 13-9

V$ tables. See dynamic performance table.

V$SQLAREA, glossary-21

verifying SQL Performance, 7-1

view

as schema object, 14-4

viewing

object properties, 5-16

viewing data, 10-4, 14-1

viewing execution statistics, 5-37

viewing initialization parameters, 5-7

views

examining, 5-25

Virtual Index Wizard, 6-4

walking through compact views, 5-36

windows

Details, 4-7

Main, 4-4

SQL Text, 4-6

workload

application, 14-5, 14-6

creating a SQL History, 11-4

database, 21-1

description, 13-13, 14-5

emphasis, 14-5

filtered, 13-14

importance, 14-5

managing, 21-1

rank, 14-5

request, 14-6

workload analysis

in access method tuning, 12-7

Workload class, 13-13

exporting, 13-15

frequency of collection, 13-16

importance values, 13-13

recommendations for, 13-13

ways to collect data, 13-16

workload data

collecting, 21-3

providing, 21-4

SQL History data, 10-3

using Oracle Trace to collect, 13-16

XDL file

importing database class from, 13-5

importing Instance class from, 13-7

adding password data for instance, 13-7

adding username data for instance, 13-7

importing Schema class from, 13-11

importing System class from, 13-12

importing workload class from, 13-15

Index

A

B

C

D

E

F

G

H

I

J

L

M

N

O

P

Q

R

S

T

U

V

W

X