Index

of a database object, 13-2

used in workload, 13-6

attribute

of an instance object, 13-2

Autotune, 19-1

implementing recommendations, 19-3

starting, 19-2

stopping, 19-2

viewing recommendations, 19-2

average blocks per cluster key, 5-21

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

canceling

collecting estimated values, 12-10

collecting exact values, 12-10

collecting for columns, 12-10

collecting for tables, 12-10

collecting using Oracle Expert, 12-9

collecting using SQL ANALYZE, 12-9

cardinality data

in schema tuning, 12-10

cardinality values

importance of, 13-3

Cartesian products, 5-35

chained and/or migrated rows, 5-19

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

choose, glossary-3

class. See collection class.

cluster

as schema object, 13-4

average blocks per cluster key, 5-21

cluster details

displaying, 5-20, 5-23

distinct hash values, 5-21

Cluster Properties, 5-20

Cluster Statistics dialog, 5-20

clustering factor, 5-24

Collect Options property sheet

displaying, 12-3

System page, 12-13

Workload page, 12-14

Collect page. See tuning session window.

collecting data, 12-1

classes of data, 9-3

incomplete data, 12-17

collection

restrictions during, 12-17

collection class

collecting data efficiently, 12-3

Database class, 12-4

description, 12-1

determining whether to collect, 12-3

Instance class, 12-6

reducing collection time, 12-3

Workload class, 12-13

column

obtaining cardinality values, 12-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 explain plans, 5-38

comparing SQL statements, 5-38

compatible, 5-4

comprehensive tuning, 11-3

description, 8-4

configuring a new database

initial configuration, 18-2

reconfiguration, 18-3

connecting

to an Oracle Trace database, 12-16

cost-based optimization for response time, glossary-4

cost-based optimization for throughput, glossary-5

CPU data

used in System class, 12-12

Creating a SQL History, 10-4

creating a tuning session, 11-1

Cross Reference report

generating, 16-2

Cursor, glossary-5

cursor space for time, 5-5

data

analyzing, 9-4

collecting, 9-3, 12-1

collecting Database class, 12-4

collecting incomplete, 12-17

collecting Instance class, 12-6

collecting invalid, 12-18

collecting Schema class, 12-9

collecting System class, 12-12

collecting unusable, 12-17

collecting Workload class, 12-13

editing, 9-4, 13-1

excluding from an analysis, 14-2

providing complete and accurate, 17-2

viewing, 9-4, 13-1

data collection. See collecting data.

Data Definition Statements (DDL), glossary-6

data dictionary, glossary-6

using in schema tuning, 12-10

Data Manipulation Statements (DML), glossary-6

database

identifying, 10-3

privileges Oracle Expert requires to access, 11-2

steps in initial configuration, 18-2

when to configure a new, 18-2

workload, 20-1

database block buffers, 5-5

Database Buffer Cache, 5-6

Database class, 12-4

automatic data collection, 12-5

collecting, 12-4

Database Users category, 12-4

deleting previously collected data for, 12-5

frequency of collection, 12-5

Name/Version category, 12-4

Public Synonyms category, 12-4

Tablespaces category, 12-4

when to collect, 12-5

database design

providing information for, 18-3

database file multi-block read count, 5-6

database object, glossary-5

attribute, 13-2

database user, 13-4

editing, 13-2

instance object, 13-2

rule, 13-2

Database page. See Collect Options property sheet.

Database Parameters View

opening, 5-2

database tuning, 8-2

initial configuration, 18-1

issues, 8-2

process, 3-3

resolving issues, 8-3

tasks, 8-2

database tuning. See also performance tuning.

database user

as database object, 13-4

Database Users category

of Database class, 12-4

Decision Support System (DSS), glossary-7

degree of parallelism, glossary-8

deleting

an object, 13-1

deleting disconnected nodes, 4-5

Density, 5-22

Details Window, 4-7

disable tuning rule, 13-2

disconnected nodes, 4-5, 4-6

deleting, 4-5

displaying Cluster Details, 5-20

displaying Index Details, 5-23

displaying Table Details, 5-17

distinct hash values, 5-21

distinct keys, 5-24

Distinct Values, 5-22

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

dynamic performance table, 12-6

editing data, 9-4, 13-1

Review page, 13-1

Editing Initialization Parameters, 5-8

empty blocks, 5-18

cluster details

empty blocks, 5-21

enable tuning rule, 13-2

Entering a New Statement, 4-13

Environment page. See Collect Options property sheet.

error

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

Estimated/Limit text box

collecting estimated statistics data, 12-10

examining views, 5-25

Excessive row migration, 22-2

execution statistics

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, 12-10

explain plan, glossary-8

partition start, 5-35

partition stop, 5-35

query text, 5-35

explain plan object, glossary-8

Explain plan rules-of-thumb

explain plans

extent sizes

cluster details

index details

file

filtered workload, 12-14

focused tuning

description, 8-4

instance tuning

I/O parameter, 11-5

OS specific parameter, 11-5

parallel query parameter, 11-5

SGA parameter, 11-4

sort parameter, 11-5

frequency, 13-5

from Tablespace Map, 23-4

Full Scan option

collecting exact statistics data, 12-10

Full Table, 5-36

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

General Details, 5-17

generating recommendations, 9-4

generating reports

Analysis report, 16-1

Recommendation Summary report, 16-2

Session Data report, 16-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, 12-8, 13-3

How This Manual is Organized, xvi

implementation files

description, 15-1

how to use, 15-2

implementation scripts, 9-5, 9-7

location, 15-1

tuning output, 9-7

implementation scripts, 9-5, 9-7

implementing recommendations, 9-5, 15-1

importance

used in workload

description, 13-5

importance value

in workload class, 12-14

in workload data, 20-5

possible values for, 20-5

specifying, 20-5

Importing SQL Statements, 4-14

incomplete data

collecting, 12-17

index

attributes, 13-4

in access method tuning, 11-6

tuning in access method tuning, 11-7

Index merges, 5-35

index properties, 5-22

Index Statistics dialog, 5-23

indexes

disorganized, 22-2

Inefficient extent sizes, 22-3

initial configuration, 18-2

of a database, 18-1

providing information for, 18-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, 15-2

Instance class, 12-6

canceling multiple instance statistics samples, 12-7

collecting, 12-9

collecting multiple instance statistics samples, 12-7

importing from .XDL file, 12-7

instance parameters category, 12-6

Instance Statistics category, 12-6

tuning multiple instances, 12-6

when to collect, 12-9

instance object, 13-2

attribute, 13-2

rule, 13-2

statistics attribute, 13-2

Instance Optimizations, 8-4, 11-4

Instance Parameters category of Instance class, 12-6

instance recommendations

implementing, 15-2

instance rules, 13-2

instance statistics

canceling collection of multiple samples, 12-7

collecting multiple samples, 12-7

how instance is performing, 12-6

maintaining history of, 12-6

sample, 12-6

Instance Statistics category

of Instance class, 12-6

instance tuning

description, 11-4

I/O parameter, 11-5

OS specific parameter, 11-5

parallel query parameter, 11-5

parallel server parameter, 11-5

recommendations, 12-8

invalid data

discovered during a collection, 12-18

discovered during an analysis, 14-3

excluding from an analysis, 12-19

index, 12-18

request, 12-18

schema, 12-18, 14-3

symbol used to mark, 12-18

table, 12-18

I/O parameter

join strategy optimization, glossary-11

join method, 5-36

leaf blocks, 5-23

logical device data

deleting previously collected, 12-13

keeping previously collected, 12-13

Main window, 4-4

Managing SQL History Data, 9-3

matching. See SQL statement matching.

memory data

used in System class, 12-12

methodology

steps in Oracle Expert, 9-1

Migrated Rows, 5-19

Name/Version category

of Database class, 12-4

Navigator Pane, 4-5

Navigator window, 4-4

New Tuning Session dialog box

NLS Date Format, 5-13

Parameters, 5-13

NLS Date Language, 5-14

NLS ISO Currency, 5-14

NLS Language, 5-14

NLS Numeric Characters, 5-14

object details, glossary-11

NLS sort, 5-15

nodes

disconnected, 4-5, 4-6

object

invalid, 12-18

general, 5-17

object name, 5-36

object owner, 5-36

object properties, 5-16

viewing, 5-16

Online Transaction Processing (OLTP), glossary-11

opening

an existing tuning session, 11-9

Opening a Previously Used Tuning Session, 4-14

Opening the Database Parameters View, 5-2

operating system data

used in System class, 12-12

operation node, 5-34

operation type, 5-35

Optimal Data Access, 11-6

optimizer, glossary-12

cost-based

access method rules addressing, 11-6

rules-based

lack of rules addressing, 11-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

option, 20-6

Oracle Index Tuning wizard, 21-1

Oracle Server

impact of installing new version, 12-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, 12-16, 20-3

using to format raw data, 20-4

Oracle Trace database

collecting workload data from, 12-16

connecting to, 12-16

importing data from, 20-4

OS specific parameter

Overwrite Existing option, 12-5, 12-11, 12-13

output. See tuning output.

panes

Navigator, 4-5

Parallel query bottlenecks, 5-36

parallel query option

impact of enabling, 12-5, 12-9

parallel query parameter

impact of enabling, 12-5, 12-9

parallel server option

parallel server parameter

allocated blocks, 5-21, 5-23

parameter file, 9-5

tuning output, 9-7

Parameters

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

hash join enabled, 5-9

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

optimizer percent parallel, 5-10

optimizer search limit, 5-10

parse calls, 4-11

parse calls per execution, 4-11

partition view enabled, 5-10

rows processed, 4-11

sort area size, 5-11

sort direct writes, 5-12

parameters

Parse Calls Per Execution, 4-11

parse calls per execution, 4-11

partition ID, 5-35

partition start, 5-35

partition stop, 5-35

partition view enabled, 5-10

password

for instances imported from .XDL files, 12-7

performance tuning

comprehensive tuning, 8-4

focused tuning, 8-4

initial configuration, 8-4

types of, 8-3

performance tuning. See also database tuning.

Personnel session, 8-5

preface

conventions table sample, xvii

Printing, 4-14

privileges

required to access a database, 11-2

Program Global Area (PGA), glossary-13

public synonym

as database object, 13-4

Public Synonyms category of Database class, 12-4

query text, 5-35

Recommendation Summary report, 9-7

generating, 16-2

Recommendation Summary Report dialog box, 16-2

recommendations

declining, 14-5

effect of tuning categories on, 11-3

implementing, 9-5, 15-1

in Analysis report, 16-1, 21-4

obtaining less conservative, 12-8

reviewing, 9-4, 14-4

Recommendations page. See tuning session window.

Remote queries, 5-36

Reorg Wizard, 22-1

Accessing, 22-3

failure prediction, 22-1

Impact Summary Report, 22-5

recovery from reorganization job failure, 22-6

reorganization job submission, 22-5

reviewing reorganization job, 22-6

specifying objects to reorganize, 22-4

reports

Analysis, 16-1

Cross Reference, 16-2

generating, 16-1

Recommendation Summary, 16-2

Session Data, 16-2

repository, 4-3, 4-14

request

invalid, 12-18

used in workload, 13-6

Review page

using with Edit pull-down menu, 13-1

Review page. See also tuning session window.

reviewing recommendations, 9-4, 14-1

rows, 5-19

rows processed, 4-11

Rule

Tunable, 14-2

rule-based optimization, glossary-14

rules, 9-6

general principles of instance, 13-2

of a database object, 13-2

of an instance object, 13-2

taking advantage of, 17-3

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

sample tuning session, 8-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, 11-10

saving your work, 4-3

Scan Columns option

collecting column cardinality, 12-10

schema

deleting previous collected data for, 12-11

keeping previous collected data for, 12-11

reason for being invalid, 12-18, 14-3

Schema category

of Schema class, 12-9

Schema class, 12-9

collecting data from .SQL file, 12-11

collecting data from .XDL file, 12-11

collecting from an instance, 12-9

data tuned, 12-9

frequency of collection, 12-11

Schema category, 12-9

specifying schemas to collect, 12-10

specifying tables to collect, 12-10

Statistics category, 12-9

when to collect data for, 12-11

used in tuning process, 13-3

view, 13-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.

Segement Analysis, 23-4

Segment Analyis Report, 23-5

Selecting a Statement for Tuning, 4-8

selecting control parameter values, 11-7

Selecting Statements with TopSQL, 4-9

server

impact of installing new version of, 12-9

service, 10-4

Session Data report, 9-7

generating, 16-2

Session Data Report dialog box, 16-2

session. See tuning session.

setting the scope of a tuning session, 11-3

SGA parameter in instance tuning, 11-4

Showing and Editing Initialization Parameters, 5-7

sort area size, 5-11

sort direct writes, 5-12

sort parameter in instance tuning, 11-5

sorts, 4-11

SQL ANALYZE statistics

collecting during schema collection, 12-9

SQL cache

collecting workload data from, 12-15

SQL Cache option

using during workload collection, 12-15

SQL files

SQL History

SQL statement matching, 11-6

SQL statements

ANALYZE, 12-9

collecting data about, 20-3

comparing, 5-38

SQL tuning

SQL statement matching, 11-6

SQL Tuning Wizard, 6-12

SQLADMIN Role

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, 13-2

Statistics category

collecting using Oracle Expert, 12-9

collecting using SQL ANALYZE, 12-9

of Schema class, 12-9

stopping

a collection, 12-17

an analysis, 14-3

Structured Query Language (SQL), glossary-19

Support, 11-10

synonym

as schema object, 13-4

System class, 12-12

CPU data, 12-12

entering data manually, 12-12

frequency of collection, 12-13

importing data from .XDL file, 12-13

memory data, 12-12

operating system data, 12-12

when to collect, 12-13

system data

deleting previously collected, 12-13

keeping previously collected, 12-13

System Global Area, glossary-19

System Global Area. See SGA

system object, 13-5

table

as schema object, 13-3

deleting previously collected data for, 12-11

keeping previously collected data for, 12-11

obtaining cardinality values, 12-10