Oracle Enterprise Manager Oracle Expert User's Guide

Viewing and Editing the Collected Data

Edit Pull-Down Menu
View/Edit Page

When you use the View/Edit Page in conjunction with the Edit Pull-Down Menu, Oracle Expert gives you the ability to view, edit, add to, and delete from the data that you collect. Also, you can view and edit the rules and attributes associated with the data on the View/Edit page of the tuning session window. The hierarchical data on the View/Edit page consists of:

database
system
logical device
workload application

The following sections describe how you can add, change, and delete objects and their respective rules and attributes.

Edit Pull-Down Menu

From the Edit pull-down menu, you can add, modify, delete, validate, and export objects. You can also edit the default rules for Oracle Expert.

Use the Edit pull-down menu in conjunction with the View/Edit page. Highlight the desired object on the View/Edit page (see Figure 6-4), then select the desired option on the Edit menu.

As an alternative, you can also use the right-most mouse button to add, modify, delete, validate, and export objects.

The following sections explain the Edit pull-down menu options in detail.

Add

When you select a folder (see Figure 6-1), the Add option allows you to include objects you did not previously consider for tuning. However, be aware that objects that you add on this screen do not have collected data associated with them. You must add the required data manually.

Figure 6-1: Instance Folder

Some objects cannot be added. If you select a folder and the Add option is not available on the Edit menu, objects of this type cannot be added manually. These objects must be collected by Oracle Expert. Most schema objects cannot be added by the user.

When you add data, Oracle Expert displays the property sheet for the type of object you selected. The names of the attributes and values are appropriate for the object you are adding. Provide the values for the object you are adding or use the defaults.

Modify

The Modify option allows you to change the attributes and rules of a particular object. For example, for the Candidate_Status column, the Attributes page provides the Parent (Candidates) and Parent Type (Table) of the column (see Figure 6-2).

Figure 6-2: Attributes for the Candidate_Status Column

Changing Values

When you modify attribute and rule values, you will notice the different ways to change the values. When you click on the value, you will see one of the following:

down arrow

: The down arrow indicates that a list box is available from which to choose values. Click on the down arrow to display the list box and click on the value you want. Click the OK button to activate the changes.

multi-line

: When the lines you want to change are highlighted, type in the new information. For example, if you want to modify a SQL statement, click on the statement, and type in the new statement. Once you have made all your changes to the data on the screen, click the OK button to activate the changes.

single-line

: When the value you want to change is highlighted, type in the new value. Once you have made all your changes to the data on the screen, click the OK button to activate the changes.

Rules Page

The Rules page allows you to modify the Oracle Expert rules. Click the Instantiate button to instantiate a rule. When you instantiate a rule, Oracle Expert makes a copy of a default rule at the current object level, and the new copy of the rule is then owned by the object at that level. The object that appears in the rule's Owner field tells you where Oracle Expert will get the value to use for that rule at the current level. See Figure 6-3.

After you instantiate a rule, the letter R appears on the icon for the object on the View/Edit page, which indicates that a rule has been instantiated for the object.

For example, you can select the "Minimum value for open_cursors" rule for SGA parameters. Then, by highlighting the rule and pressing the Instantiate button, you instantiate the rule, which changes the owner from Expert to SGA parameter and the type to SGA parameter (see Figure 6-3). You can also modify the value of this parameter from 50 to whatever value you want.

Figure 6-3: Rules for SGA Parameters

But what happens if you change a rule? Does the change affect the child objects in the hierarchy? When you instantiate a rule for an object, the object's children that do not instantiate the rule are affected. For example, let's analyze the "Maximum number of index segments" rule for schemas and tables. You can change that rule to be different for a schema and table. (The schema is the parent of the table and the table is the child of the schema.)

In Table 6-1, the Database object defines the value of that rule to be 15, Schema A defines the value to be 10, Table A defines the value to be 6, and Table B defines the value to be 13.

However, Table C and Schema B do not instantiate that rule. Because of the Oracle Expert hierarchy, Table C will use a value of 10 (from its parent Schema A) and Schema B will use a value of 15 (from its parent Database).

Table 6-1: Understanding Rule Values for Objects

Object

Maximum Number of Index Segments

Database

15

Schema A
10

Table A

6

Table B

13

Table C

10

Schema B
15

The user who instantiates a rule can also change the value of a rule.

When you click the Rule Desc button, you see information explaining what the rule does and providing guidelines for changing the rule.

Click the Advanced button to display the advanced rules used by Oracle Expert for the object. These are the rules that you would rarely want to edit or view, for example, low-level constants that are factored into the algorithms used by the Oracle Expert rules. Oracle Expert displays the advanced rules with the basic rules. When the advanced rules are displayed, an "X" appears in the Adv column of each advanced rule and the Advanced button is renamed to the Basic button. To display only the basic rules, click the Basic button.

To change the value of a rule, select the rule and click the Instantiate button. Then you can change the value of the rule (by entering a new value or choosing an option from the pull-down list of values in the rule's Value column). Once you have made all your changes to the data on the screen, click the OK button to activate the changes.

When you click the Remove button, you remove the instantiation of the rule for the current object. The object will then use the value of the rule from the next higher level of the hierarchy where the rule is instantiated (or the default value of the rule, if none of the higher objects in the hierarchy have instantiated the rule). When you remove the instantiation of all the rules for an object, Oracle Expert removes the letter R from the icon for the object on the View/Edit page.

You cannot delete a rule.

Delete

The Delete option allows you to eliminate objects from the collected data for the tuning session. When you delete an object, you are deleting all its children. Also, if you delete an object that one or more other objects refer to, you invalidate the other objects. For example, if SQL statements in your workload reference a schema and you delete that schema object from Oracle Expert, the workload becomes invalid.

You cannot delete the database object.

Validate

The Validate option allows you to validate schema and workload objects. For example, you would use this option if you imported a workload but not its associated schema. Once you import the schema, you can validate the workload against that schema. You can also validate a schema against other schemas and public synonyms, where external references exist.

To validate an object, select the object you want to validate and choose Edit=>Validate.

When you validate a workload application, a message box displays and asks whether you want to "Purge invalid requests?". You have the following options:

YES

: Oracle Expert performs validation and does not save invalid requests.

NO

: Oracle Expert performs validation and saves all requests.

CANCEL

: Oracle Expert does not perform any validation.

Export

The Export option gives you the ability to copy various Oracle Expert objects to files. The default exported file type is .XDL (Expert Definition Language). To export an object, click the object you want to export and choose Edit=>Export. Provide the name of the .XDL file you want to create.

To export an entire tuning session, select the tuning session and choose File=>Export.

The Export option is intended for Oracle Expert use only. Use this option if you want to use this data in another tuning session (for collection purposes). For example, if you want to export system data, click on System on the View/Edit page, choose Edit=>Export and supply the name of the .XDL file you want to create. This will copy disk and system information of the current tuning session to the .XDL file.

Default Rules

Default rules are the Oracle Expert pieces of knowledge that govern the recommendations of Oracle Expert. Default rules are owned by Oracle Expert. (Expert displays in the Owner field for default rules.)

Oracle Expert default rules give you control over the adjustable parameters that affect the rules. The default values for these rules are intended to represent reasonable average settings for a database environment. If one or more of the settings do not adequately represent your database environment, Oracle Expert provides you with the ability to change the settings.

You can change the default values of the default rules Oracle Expert allows you to view. For example, by default, Oracle Expert recommends a maximum of 10 sorted indexes per table. If you want Oracle Expert to recommend a maximum of 3 sorted indexes per table, you can change the default value of the default Table rule "Maximum sorted indexes per table" from 10 to 3. (Select Edit=>Default Rules, choose Table for the Rule/Object Type in the Edit - Rules Default Settings dialog box.)

Changes to the default rules apply to every tuning session in Oracle Expert within that particular repository. Be aware that once you change the default value of a default rule, there is no automatic reset back to the rule's original value when you installed Oracle Expert. However, you can manually change the default back to the original value.

View/Edit Page

To add, modify, or delete an object, select the View/Edit page of the tuning session window (see Figure 6-4). Use this View/Edit page in conjunction with the Edit pull-down menu to perform the tasks you need.

Click on the plus (+) sign to see the children of an object.

Figure 6-4: View/Edit Page

Databases

For the database object, you can view and edit associated attributes and rules. To view and edit these rules and attributes, place your cursor on the name of the database, choose the Edit=>Modify menu option, and choose either the Attributes tab or the Rules tab. Attributes include Total Datafiles and Degree of Parallelism. Rules include the Common rules, and rules for Database, Schema, Table, Schema Analysis, Index Analysis, and Tablespace.

Instances

Under the instance object, you can change instance rules and attributes, and statistics attributes.

Each instance runs on a system. Oracle Expert associates default rules with each instance (use the Edit=>Default Rules menu option). An example of the system rules you can edit is "Default operating system block size."

Keep in mind as you review and modify the instance rules that the following general principles apply:

Rules regarding minimums establish the lower boundary of an instance parameter. Oracle Expert will not make recommendations below this value. For example, if the minimum value for the db_block_buffers parameter is 50, then Oracle Expert will always recommend at least a value of 50 for that parameter.
Rules regarding maximums establish the upper boundary of an instance parameter. Oracle Expert will not make recommendations above this value. For example, if the maximum value for the db_block_buffers parameter is 100, then Oracle Expert will not recommend more than 100 for that parameter.
Rules regarding multipliers establish the factor by which Oracle Expert rule values are multiplied. For example, if Oracle Expert recommends increasing the db_block_buffers parameter of average concurrent users by a factor of 2, then the resulting number will be twice as large as the original number. To disable this user rule, use the value 0. To decrease the factor, multiply by a decimal, for example, 0.5.
Rules regarding hit ratio (low or high) establish the upper and lower thresholds that affect the Oracle Expert activation point for different categories of rules. For example, if the buffer_cache_hit_ratio parameter falls below a LOW threshold, then Oracle Expert might recommend increasing the db_block_buffers parameter.
Rules that enable and disable the tuning of certain objects govern the capability of Oracle Expert to make recommendations for an object and its children.
The "Last statistics collection importance" rule (a Common rule) lets you tell Oracle Expert how important historical data is to the recommendations. The possible values are 0 to N (where N is an integer). The higher the number the more importance there is to the most recent collection.

Schemas

A schema is required for some tuning categories. Because Oracle Expert is not a schema editor, it does not keep 100% of a user's schema, nor does it provide editing capabilities for all possible schema information. In general, Oracle Expert only manages schema objects that are necessary to the tuning process.

Within Oracle Expert, you can edit data for all the schemas you can access in your database. The objects within a schema that you can edit are: tables (including columns, indexes, and constraints), clusters, and synonyms.

If a schema object is missing or incomplete for a tuning session that expects schema data, Oracle Expert will not attempt to make recommendations on objects related to the missing schema object.

The accuracy of the necessary data is very important. When Oracle Expert attempts to form tuning strategies, it assumes that the input data is accurate. If that is not the case, the quality of the recommendations may be unreliable. For example, cardinality is a critical attribute in both tables and columns. Oracle Expert relies heavily on the cardinality values to predict sizing and index formation. If the cardinality values are incorrect or missing, Oracle Expert may recommend very poor index structures or no index structures at all.

Tables

Oracle Expert needs the physical structure of a table to do sizing and access methods tuning. Oracle Expert uses the following table information:

cardinality (number of rows in a table)
record size (physical size of all columns)
columns
indexes - Oracle Expert manages all index characteristics except the PARALLEL option. Existing indexes can be collected from a database and analyzed by the rules to determine usefulness. The following index attributes are managed:
table constraints
storage attributes and statistics

Views

Through the View information, you can inspect the SQL text that makes up the view. You can also display the columns that make up the view.

Oracle Expert uses the View information to track SQL text backwards to the appropriate base table. If no workload exists, Oracle Expert can use views to form access methods strategies based on SQL text.

Clusters

As it does with indexes, Oracle Expert supports all cluster attributes except the PARALLEL option. If appropriate, Oracle Expert can analyze table accesses to identify clusterable entities.

Synonyms

Oracle Expert uses synonyms for validation of workload requests. Oracle Expert processes the synonyms to find out what objects the synonyms reference, such as a table.

Tablespaces

As with other physical schema information, Oracle Expert considers a tablespace to be a tunable entity. It may recommend changes to existing tablespaces or it may recommend new tablespaces.

Public Synonyms

Because SQL statement analysis may identify references to synonyms, Oracle Expert must have full knowledge of all public and private synonyms. You can edit the scope and target object synonym attributes.

Database Users

When a database user is defined, two types of tablespaces are used to define the database user: default tablespace and temporary tablespace. Through structure tuning, Oracle Expert uses database user information to verify that these tablespaces have the attributes necessary for optimum performance.

System

Oracle Expert uses the system information and the physical characteristics of the hardware where the instance is running to evaluate optimal parameter settings. For example, Oracle Expert uses the Total Memory attribute in conjunction with the Memory Utilization attributes to assess the SGA, memory, and configuration for each instance.

Logical Devices

Oracle Expert uses logical device information to obtain location, performance rating, disk size, percent available, and disk type of each logical device.

Workload Applications

Workload data describes to Oracle Expert the nature, frequency, and importance of transactions that access the database. You can edit the following workload elements:

application
business unit
transaction
request (SQL statement)

Importance is the value assigned to a workload element. You can supply any value (up to 9999), with lower values being of less significance.

Frequency is the number of times a request, transaction, or business unit is repeated when a specific transaction, business unit, or application (respectively) is executed.

Note that frequency and importance affect the recommendations given by Oracle Expert.

Application

You can edit the importance of an application. An application is the workload category at the top of the workload hierarchy.

Business Unit

You can edit the frequency, importance, and cycle of the business unit. A business unit is the workload category below the Application level in the workload hierarchy.

Transaction

You can edit the frequency and importance of the transaction. A transaction is a logical unit of work that consists of one or more SQL statements. A transaction begins with a user's first executable SQL statement and ends when it is explicitly committed or rolled back. Transactions appear at the level below Business Unit in the workload hierarchy.

Request

You can edit the importance, frequency, and SQL statements of a request. A request is a SQL statement. Requests are the elements at the lowest level of the workload hierarchy, below Transactions.

Copyright © 1996 Oracle Corporation.
All Rights Reserved.