Oracle Enterprise Manager Database Tuning with the Oracle Tuning Pack
Release 9.0.1

Part Number A86647-01

Library

Product

Contents

Go to previous page

Index

A  B  C  D  E  F  G  H  I  J  L  M  N  O  P  Q  R  S  T  U  V  W  X 


A

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

B

base index. See index.
bind variables, glossary-2
Bitmap Indexes
definition, glossary-2
bitmap merge area size, 5-4
blank trimming, 5-4

C

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

D

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

E

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

F

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

G

General Details, 5-16
generating recommendations, 10-4
generating reports
Analysis report, 17-1
Recommendation Summary report, 17-2
Session Data report, 17-2

H

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

I

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

J

join method, 5-36
join strategy optimization, glossary-11

L

leaf blocks, 5-23
logical device data
deleting previously collected, 13-13
keeping previously collected, 13-13

M

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

N

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

O

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

P

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

Q

query text, 5-35

R

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
reports. See also Session Data report, Analysis report.
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

S

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

T

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

U

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

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

W

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

X

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

Go to previous page
Oracle
Copyright © 2001 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents