Oracle Enterprise Manager Oracle Expert User's Guide

Library

Product

Contents

Index

Index

access methods

in application tuning, 1-4, 4-7

lack of support for rules-based optimizer, 4-7

support for rules-based optimizer, 4-7

adding

an object, 6-2, 6-8

advantages of Oracle Expert, 12-1

advantages of using Oracle Expert, 1-1

analysis

canceling, 7-5

errors during, 7-5

excluding objects from, 7-4, 7-5

Analysis report, 2-6

contents of, 10-2

generating, 10-2

Analysis Report dialog box, 10-2

Analyze page. See tuning session window.

ANALYZE statement

during Schema class collection, 5-19

analyzing data, 2-4, 7-1

application

excluding from an analysis, 7-5

in workload hierarchy, A-5

invalid, 5-33

reason for being invalid, 5-33

used in workload, 6-12

application tuning, 1-4

access methods, 1-4, 4-7

description, 4-7

SQL tuning, 1-4, 4-7

attribute

of a database object, 6-8

of an instance object, 6-8

base index. See index.

business unit

in workload hierarchy, A-5

used in workload, 6-12

canceling

a collection, 5-32

an analysis, 7-5

cardinality

collecting estimated values, 5-19

collecting exact values, 5-19

collecting for columns, 5-19

collecting for tables, 5-19

collecting using Oracle Expert, 5-15

collecting using SQL ANALYZE, 5-15

cardinality data

in schema tuning, 5-19

cardinality values

importance of, 6-10

children of an object, 6-4, 6-6

viewing, 6-8

class. See collection class.

cluster

as schema object, 6-11

reason for being invalid, 5-33

Collect Options property sheet

Database page, 5-7

displaying, 5-4

Environment page, 5-24

Instance page, 5-10

Schema page, 5-16

Workload page, 5-27

Collect page. See tuning session window.

collecting data, 5-1

classes of data, 2-3

incomplete data, 5-32

invalid data, 5-33

unusable data, 5-32

collection

canceling, 5-32

errors during, 5-32

restrictions during, 5-32

collection class

collecting data efficiently, 5-5

Database class, 5-6

description, 5-1, 5-2

determining whether to collect, 5-4

Environment class, 5-20

Instance class, 5-9

reducing collection time, 5-5

Schema class, 5-15

size of, 5-2

source of, 5-3

summary table, 5-2

volatility of, 5-2

Workload class, 5-25

column

obtaining cardinality values, 5-19

comprehensive tuning, 4-5

description, 1-5

configuring a new database

initial configuration, 12-2

reconfiguration, 12-3

Connect Repository dialog box, 3-1

connecting

to an Oracle Trace database, 5-29

to the repository, 3-1

control parameters, 2-6

description, 4-9

for tuning session, 7-2

selecting values, 4-8, 7-2

Control Parameters page. See New Tuning Session dialog box.

copying an object, 6-6

CPU data

used in Environment class, 5-23

creating a tuning session, 4-4

data

analyzing, 2-4, 7-1

collecting, 2-3, 5-1

collecting Database class, 5-6

collecting Environment class, 5-20

collecting incomplete, 5-32

collecting Instance class, 5-9

collecting invalid, 5-33

collecting Schema class, 5-15

collecting unusable, 5-32

collecting Workload class, 5-25

editing, 2-3, 6-1

excluding from an analysis, 7-4, 7-5

providing complete and accurate, 11-2

viewing, 2-3, 6-1

data collection. See collecting data.

data dictionary

using in schema tuning, 5-19

database

identifying, 4-3

privileges Oracle Expert requires to access, 4-4

steps in initial configuration, 12-2

when to configure a new, 12-2

workload, A-1

Database class, 5-6

automatic data collection, 5-8

collecting, 5-6

Database Users category, 5-6

deleting previously collected data for, 5-9

frequency of collection, 5-9

keeping previously collected data for, 5-9

Name/Version category, 5-6

Public Synonyms category, 5-6

recommendations from, 5-6

Tablespaces category, 5-6

when to collect, 5-9

database design

providing information for, 12-5

database object

attribute, 6-8

database user, 6-11

editing, 6-8

instance object, 6-8

public synonym, 6-11

rule, 6-8

schema object, 6-9

tablespace, 6-11

viewing, 6-8

Database page. See Collect Options property sheet.

database performance

examples of poor, A-2

database tuning, 1-2

initial configuration, 12-1

issues, 1-2

of application, 1-4

of instance parameters, 1-3

of structure, 1-4

resolving issues, 1-3

tasks, 1-2

database tuning. See also performance tuning.

database user

as database object, 6-11

Database Users category

of Database class, 5-6

default rules

description, 6-7

deleting

an object, 6-6, 6-8

disable tuning rule, 6-9

Duration list box

using during instance collection, 5-12

dynamic performance table, 5-9

using in Database class collection, 5-6

Edit pull-down menu, 6-2

Add option, 6-2

Default_Rules option, 6-7

Delete option, 6-6

Export option, 6-6

Modify option, 6-2

Validate option, 6-6

editing data, 2-3, 6-1

Edit pull-down menu, 6-2

View/Edit page, 6-8

enable tuning rule, 6-9

Environment class, 5-20

collection time not updated, 7-2

CPU data, 5-23

entering data manually, 5-22

frequency of collection, 5-25

importing data from .XDL file, 5-24

memory data, 5-23

operating system data, 5-23

recommendations for, 5-21

when to collect, 5-25

Environment page. See Collect Options property sheet.

error

possible cause of "table not found" error, 4-4

Estimated/Limit text box

collecting estimated statistics data, 5-19

excluding objects from an analysis, 7-4

Expert Definition Language. See XDL file.

Expert Scan option

collecting cardinality data using, 5-19

focused tuning

application tuning, 1-4, 4-7 access methods, 4-7

SQL tuning, 4-7

description, 1-5

instance tuning, 1-3, 4-6 I/O parameter, 4-7

parallel query parameter, 4-7

SGA parameter, 4-6

sort parameter, 4-7

structure tuning, 1-4, 4-8 placement, 4-8

sizing, 4-8

frequency, 6-12

frequency value

collecting using Oracle Trace, A-7

collecting using SQL cache, A-7

Full Scan option

collecting exact statistics data, 5-19

generating reports

Analysis report, 10-2

Session Data report, 10-1

historical data

importance of, 5-13, 6-9

I/O parameter

in instance tuning, 1-4, 4-7

Implement page. See tuning session window.

implementation files

description, 9-1

how to use, 9-2

implementation scripts, 2-4, 2-7

location, 9-1

tuning output, 2-7

implementation scripts, 2-4, 2-7

implementing recommendations, 2-4, 9-1

importance

used in workload description, 6-12

importance value

in workload class, 5-26

in workload data, A-7

possible values for, A-8

specifying for workload elements, A-7

incomplete data

collecting, 5-32

index

attributes, 6-10

in access method tuning, 4-7

reason for being invalid, 5-33

tuning in access method tuning, 4-8

Index summary style rule, 7-2

INIT.ORA file

using for tuning recommendations, 9-2

initial configuration, 12-2

of a database, 12-1

providing information for, 12-5

recommendations, 12-5

Instance class, 5-9

canceling multiple instance statistics samples, 5-13

collecting, 5-14

collecting multiple instance statistics samples, 5-12

Duration list box, 5-12

frequency of collection, 5-12

importing from .XDL file, 5-13

instance parameters category, 5-9

Instance Statistics category, 5-9

parameters tuned, 5-14

Sample Frequency list box, 5-12

tuning multiple instances, 5-11

when to collect, 5-14

instance object, 6-8

attribute, 6-8

rule, 6-8

statistics attribute, 6-8

Instance Parameters category of Instance class, 5-9

instance recommendations

implementing, 9-2

instance rules, 6-9

instance statistics

canceling collection of multiple samples, 5-13

collecting multiple samples, 5-12

how instance is performing, 5-12

maintaining history of, 5-12

sample, 5-12

Instance Statistics category

of Instance class, 5-9

instance tuning, 1-3

description, 1-3, 4-6

I/O parameter, 1-4, 4-7

parallel query parameter, 1-4, 4-7

recommendations, 5-13

SGA parameter, 1-3, 4-6

sort parameter, 1-4, 4-7

instantiation

of a rule, 6-4

removing, 6-5

invalid data

application, 5-33 purging invalid requests from, 6-6

cluster, 5-33

collecting, 5-33

deleting, 6-6

discovered during a collection, 5-33

discovered during an analysis, 7-6

excluding from an analysis, 5-33

index, 5-33

request, 5-33

schema, 5-33, 7-6

symbol used to mark, 5-33

table, 5-33

invalid requests

deleting as part of validation, 6-6

removing during workload collection, 5-29, 5-30

saving during workload collection, 5-29, 5-30

logical device data

deleting previously collected, 5-25

keeping previously collected, 5-25

logical device object, 6-11

matching. See SQL statement matching.

memory data

used in Environment class, 5-23

menu

Edit pull-down, 6-2

methodology

steps in Oracle Expert, 2-1

modifying object rules, 6-2

Name/Version category

of Database class, 5-6

naming of workload elements, A-7

New Tuning Session dialog box

Control Parameters page, 4-8

Scope page, 4-5

object

adding, 6-2

children of, 6-4, 6-6

deleting, 6-6

excluding from an analysis, 7-4

exporting, 6-6

invalid, 5-33

validating, 6-6

object rules

modifying, 6-2

opening

an existing tuning session, 4-9

operating system data

used in Environment class, 5-23

optimizer

cost-based access method rules addressing, 4-7

rules-based lack of rules addressing, 4-7

Oracle Expert

assigning names to workload elements, A-7

Oracle Server

impact of installing new version of, 5-14

Oracle Trace

collecting frequency value, A-7

using to collect workload data, 5-29, A-3

using to format raw data, A-4

Oracle Trace database

collecting workload data from, 5-29

connecting to, 5-29

importing data from, A-4

ORANT, 1-5

output. See tuning output.

Overwrite Existing option, 5-9, 5-20, 5-25, 5-31

deleting previously collected workload data, A-9

parallel query option

impact of enabling, 5-9, 5-14

parallel query parameter

in instance tuning, 1-4, 4-7

parallel server option

impact of enabling, 5-9, 5-14

parameter file, 2-4

tuning output, 2-7

password

for instances imported from .XDL files, 5-13

performance

examples of poor, A-2

performance tuning

comprehensive tuning, 1-5

focused tuning, 1-5 application tuning, 1-4

instance tuning, 1-3

structure tuning, 1-4

initial configuration, 1-5

types of, 1-3

performance tuning. See also database tuning.

Personnel session, 1-5

placement

in structure tuning, 1-4, 4-8

privileges

required to access a database, 4-4

public synonym

as database object, 6-11

Public Synonyms category of Database class, 5-6

recommendations

displaying index analysis, 7-2

effect of tuning categories on, 4-4

for initial configuration, 12-5

implementing, 2-4, 9-1

in Analysis report, 10-2

obtaining less conservative, 5-14

reviewing, 2-4

reports

generating, 10-1

reports. See also Session Data report, Analysis report.

repository, 4-3

connecting to, 3-1

request

in workload hierarchy, A-5

invalid, 5-33

used in workload, 6-12

Review Recommendations page. See tuning session window.

reviewing recommendations, 2-4, 8-1

Rule

Index summary style, 7-2

Tunable, 7-4

rules, 2-6

advanced, 6-5

general principles of instance, 6-9

instantiating, 6-4 affect, 6-4

of a database object, 6-8

of an instance object, 6-8

online description, 6-5

taking advantage of, 11-4

rules page, 6-4

Sample Frequency list box

using during instance collection, 5-12

sample tuning session, 1-5

Save Invalid Requests option

using during workload collection, 5-29, 5-30

Scan Columns option

collecting column cardinality, 5-19

schema

deleting previous collected data for, 5-20

keeping previous collected data for, 5-20

reason for being invalid, 5-33, 7-6

Schema category

of Schema class, 5-15

Schema class, 5-15

collecting data from .SQL file, 5-19

collecting data from .XDL file, 5-19

collecting from an instance, 5-18

data tuned, 5-15

frequency of collection, 5-20

recommendations for, 5-15

Schema category, 5-15

specifying schemas to collect, 5-18

specifying tables to collect, 5-18

Statistics category, 5-15

when to collect data for, 5-20

Schema data

using in schema tuning, 5-18

schema object, 6-9

cluster, 6-11

if missing, 6-10

synonym, 6-11

table, 6-10

used in tuning process, 6-9

validating, 6-6

view, 6-10

Schema page. See Collect Options property sheet.

Scope page. See New Tuning Session dialog box.

scope. See tuning scope.

selecting control parameter values, 4-9

server

impact of installing new version of, 5-14

Session Data report, 2-7

contents of, 10-2

generating, 10-1

Session Data Report dialog box, 10-1

session. See tuning session.

setting the scope of a tuning session, 4-4

SGA parameter in instance tuning, 1-3, 4-6

sizing in structure tuning, 1-4, 4-8

sort parameter in instance tuning, 1-4, 4-7

SQL ANALYZE statistics

collecting during schema collection, 5-15

SQL cache

collecting frequency value, A-7

collecting workload data from, 5-29

SQL Cache option

using during workload collection, 5-29

SQL statement matching, 4-7

SQL statements

ANALYZE, 5-15

collecting data about, A-3

used in workload, A-1

SQL tuning

in application tuning, 1-4, 4-7

SQL statement matching, 4-7

starting Oracle Expert, 3-1

statistics attribute

of an instance object, 6-8

Statistics category

collecting using Oracle Expert, 5-15

collecting using SQL ANALYZE, 5-15

of Schema class, 5-15

stopping

a collection, 5-32

an analysis, 7-5

structure tuning, 1-4

description, 1-4, 4-8

placement, 1-4, 4-8

sizing, 1-4, 4-8

synonym

as schema object, 6-11

system data

deleting previously collected, 5-25

keeping previously collected, 5-25

System Global Area. See SGA

system object, 6-11

table

as schema object, 6-10

deleting previously collected data for, 5-20

keeping previously collected data for, 5-20

obtaining cardinality values, 5-19

reason for being invalid, 5-33

used in application tuning, 6-10

tablespace

as database object, 6-11

Tablespaces category of Database class, 5-6

terminating

a collection, 5-32

an analysis, 7-5

transaction

in workload hierarchy, A-5

used in workload, 6-12

Tunable rule

defined, 7-4

tuning

excluding data from, 7-4

excluding objects from, 7-4

goals for, A-2

iterative, 11-3

tuning category

associated tuning type for, 4-4

used to specify tuning scope, 4-4

tuning input, 2-4, 2-5

control parameters, 2-6

database class, 2-5

environment class, 2-6

instance class, 2-5

rules, 2-6

schema class, 2-6

workload class, 2-6

tuning output, 2-4, 2-6

implementation files, 2-7

parameter file, 2-7

report, 2-6

tuning report

Analysis report, 2-6

Session Data report, 2-7

tuning rules

default rules applying to, 6-7

tuning scope

changing, 4-9

on analysis page, 7-1

refining, 11-1

setting, 4-4

tuning session

collecting data, 5-1

creating, 4-4

deleting, 4-10

modifying, 4-9

opening an existing, 4-9

representative workload in, A-2

resuming, 4-9

setting the scope, 2-2, 4-4

tuning session window

Analyze page, 7-1

Collect page, 5-1

Implement page, 9-1

Review Recommendations page, 8-1

View/Edit page, 6-1

tuning type

associated tuning categories for, 4-4

unusable data

collecting, 5-32

username

for instances imported from .XDL files, 5-13

users

impact of adding new, 5-9, 5-14

V$ tables. See dynamic performance table.

validating

schema object, 6-6

workload object, 6-6

view

as schema object, 6-10

View/Edit page

using with Edit pull-down menu, 6-8

View/Edit page. See also tuning session window.

viewing data, 2-3, 6-1

viewing data. See also editing data.

workload

application, 6-12

business unit, 6-12

database, A-1

description, 5-26, 6-12

importance, 6-12

managing, A-1

naming of elements in a, A-7

overwriting existing applications, 5-31

representative, A-2

request, 6-12

transaction, 6-12

workload analysis

in access method tuning, 4-8

Workload class, 5-25

exporting, 5-30

frequency of collection, 5-31

importance values, 5-26

naming of elements in, A-7

options, 5-31

recommendations for, 5-26

ways to collect data, 5-31

workload data

collecting, A-3

naming of elements, A-7

overwriting, A-9

providing, A-4

replacing, A-9

used in application tuning, A-2

used in structure tuning, A-2

using Oracle Trace to collect, 5-29

workload hierarchy

categories, A-5

workload object

validating, 6-6

Workload page. See Collect Options property sheet.

XDL file

importing Environment class from, 5-24

importing Instance class from, 5-13 adding password data for instance, 5-13

adding username data for instance, 5-13

importing Schema class from, 5-19

Prev Next

Copyright © 1996 Oracle Corporation. All Rights Reserved.

Library

Product

Contents

Index