14
Viewing and Editing the Collected Data

When you use the Review 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 Review Page of the tuning session window. The hierarchical data on the Review Page consists of:

• database
  • instance

  • schema

  • database user

  • public synonym

  • tablespace

• system

• workload application

Review Page

To add, modify, or delete an object, select the Review Page of the tuning session window. Use this Review Page in conjunction with the Edit pull-down menu to perform the tasks you need.

You can manually enter environment information under the system containers on the Review page under the System folder which is located under the Environment folder. You can modify existing systems by selecting the object then choosing Edit=>Modify. You can add new systems by selecting the container under the System folder on the Review page, then choosing Edit=>Add. Click Help from the Review Page for detailed information.

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.

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 9999. The higher the number the more importance there is to the most recent collection.

  Caution: If you have collected instance statistics, do not change the attribute value(s) for any instance parameters from the Review Page. The resulting recommendation(s) can be misleading.

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 manages only 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
  • datatypes and sizes

  • cardinality (relative uniqueness of column data)

  • column constraints

• 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:
  • index characteristics (uniqueness, type, and so on)

  • index columns

  • storage characteristics and statistics

• 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.

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 space management, Oracle Expert uses database user information to verify that these tablespaces have the attributes necessary for optimum performance.

Environment

The environment object displays environment data about the database you are tuning.

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 configuration for each instance.

Workload Applications

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

• application

• 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 is repeated when a specific application is executed.

Emphasis or statistical weight determines which factors are used to calculate importance.

Rank is given to a request based on the calculated importance. Rank is a good indicator of how valuable the element is in relation to other workload elements.

Note: Frequency and importance affect the recommendations produced by Oracle Expert. (See Chapter 21, "Managing Workloads" for more information.)

Application

You can edit the importance of an application. An application is the workload category that is used to group requests.

Request

You can edit the importance, frequency, and SQL statements of a request. A request is a SQL statement. Requests are the elements below Applications.