|Oracle Enterprise Manager Database Tuning with the Oracle Tuning Pack
Part Number A86647-01
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:
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.
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.
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:
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.
Oracle Expert needs the physical structure of a table to do sizing and access methods tuning. Oracle Expert uses the following table information:
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.
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.
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.
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.
Because SQL statement analysis may identify references to synonyms, Oracle Expert must have full knowledge of all public and private synonyms.
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.
The environment object displays environment data about the database you are tuning.
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 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:
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.
Frequency and importance affect the recommendations produced by Oracle Expert. (See Chapter 21, "Managing Workloads" for more information.)
You can edit the importance of an application. An application is the workload category that is used to group requests.
You can edit the importance, frequency, and SQL statements of a request. A request is a SQL statement. Requests are the elements below Applications.