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

Part Number A86647-01

Library

Product

Contents

Index

Go to previous page Go to next page

13
Collecting the Data

This chapter describes how to collect data for a tuning session. The topics discussed in this chapter include:

Overview of Data Collection

The selected tuning categories for a tuning session determine the data that must be collected and stored in the Oracle Expert repository. After the appropriate data is collected, Oracle Expert can analyze (apply its rules to) the collected data and generate tuning recommendations.

The Collect page of the Oracle Expert tuning session window allows you to specify the type of data to collect for a tuning session. The types of data you can select on the Collect page are referred to as collection classes.

Collecting the Collection Classes

The collection classes appear on the Collect page of the tuning session window. They are:

Table 13-1 provides summary information for each of the collection classes, including the size of the class data, its volatility (which governs how frequently the class should be collected), whether Oracle Expert collects the class data automatically, and the source or sources of the data.

Table 13-1 Summary Table for Collection Classes
Collection Class  Size of Class Data   Volatility/
Collection
Frequency
 
Automatic
Collection
 
Sources of Class Data 

Database 

Variable 

Low 

Yes 

Instance or .XDL file 

Instance 

Small 

High 

Yes 

Instance or .XDL file 

Schema 

Variable 

Low to MediumFoot 1 

Yes 

Instance or .XDL file 

System 

Small 

Low to Medium 

No 

User input or .XDL file 

Workload 

Variable 

Variable 

YesFoot 2 

Instance (SQL cache, Oracle Trace, .XDL file, or SQL History) 

1 Schema Statistics, medium; Schema Data, low
2 See "Collecting the Workload Class".

How Oracle Expert Collects Class Data

When you display the Collect page, one or more of the collection classes is enabled and selected. After considering the tuning categories selected for the tuning session, Oracle Expert enables a collection class if data from that class is required. If it can automatically collect the data for a required class from the database being tuned, Oracle Expert selects the class. For more information about the required collection classes for each tuning category, see Table 13-1.

When the System Class is required for a selected tuning category, Oracle Expert enables the System Class option on the Collect page.

The Last Collected column on the Collect page shows the date and time that data was last collected for a class or "Never" if it has never been collected.

The Options Set? column indicates whether a class is ready for collection. A green check mark means valid options have been set for that class. A red X means valid options have not been set for the class.

Also, the following scenarios are possible:

Specifying the Class Data to Collect

Each collection class has an associated Options button that brings you to an appropriate dialog box. These dialog boxes allow you to choose class options and provide Oracle Expert with the information it needs to collect class data automatically.

The selected tuning categories for a tuning session determine the classes you should collect as well as which class options you should collect. When you have entered data correctly in a Collect Options dialog box, a check mark is displayed at the bottom of the page.

You can start a collection at any time by clicking the Collect button on the Collect page. However, if some of the selected collection classes have a red X in the Options Set? column, Oracle Expert will display a message box stating that invalid options exist and prompt you whether you want to continue with the collection of the classes that have valid option settings. If none of the collection classes have valid option settings, Oracle Expert will display a message stating so and no collection is performed.

Collecting Data Efficiently

You can reduce the time you spend collecting tuning session data by collecting the minimum amount of data Oracle Expert requires to generate tuning recommendations for your selected tuning categories.

For example, suppose you have selected the Check for Optimal Data Access tuning scope for a tuning session to determine whether one or more tables in a particular schema have appropriate indexes. Although Oracle Expert requires that you collect Schema class data for the Optimal Data Access category, you should select a small number of tables that you want to tune. The more tables you choose, the longer the evaluation will take and the longer the reports will be.

If a SQL History exists, and it is a complete representation of SQL execution in the database, then collecting from the SQL History will perform better.

When you limit the amount of data you collect, you shorten the collection, analysis, and recommendation review cycle.


Note:

If you do have to collect a large amount of data, you do not need to visually monitor Oracle Expert for completion. You can configure your machine to produce an audible .WAV alert at the end of the operation. The audible alert will sound whether Oracle Expert is minimized or not. Please refer to your operating system's sound property page for more information. 


For each class, the following sections describe:

Collecting the Database Class

When the Database class is selected on the Collect page of the tuning session window, Oracle Expert can collect the following categories of Database class data:

Public synonym data is used to validate workload data. When Check for Optimal Data Access is selected, workload is validated as part of the collection. Depending on which data access scope was selected, public synonym data is either automatically collected or needs to be collected as part of the database class.

When performing comprehensive index evaluation of tables you specify or performing index fragmentation evaluation on tables you specify, you should collect public synonyms prior to collecting the workload. If you do not, the workload data you collect may be incomplete.

When performing comprehensive index evaluation on tables referenced by worst performing SQL statements, the collection of public synonyms is automatically driven by the SQL validation requirement on the tables.

Collecting Database Class Data from an Instance

You can automatically collect Database class data directly from an instance. Database class data is primarily obtained from the database's data dictionary and dynamic performance tables (V$ tables) You must use this method the first time you collect the database class for a database.

Collecting Database Class Data from a File

You can collect Database class data from an .XDL (Expert Definition Language) file. Use the Browse button to browse the available .XDL files on your system. The .XDL file for a Database class contains name and version data (including installed options and database-wide statistics) for a database.

The file can also include SQL information in the form of database users, tablespaces, and public synonyms data.

An .XDL file containing Database class data is created when a Database class is exported or when an entire tuning session is exported.

Re-Collecting Database Class Data

During a tuning session, you can collect the Database class more than once. If you do, you can collect one or more categories of Database class data that have been collected previously. In this situation Oracle Expert replaces the existing category data with the new data collected for the category.

After you have collected the Database class for a tuning session, you do not need to collect it again for that session unless there are changes to:

Collecting the Instance Class

When the Instance class is selected on the Collect page of the tuning session window, Oracle Expert can collect:

Oracle Expert collects the instance statistics and instance parameters categories of Instance class data from a database's dynamic performance tables (V$ tables).

Collecting Instance Class Data from One or More Instances

You can automatically collect Instance class data directly from one or more instances. You must use this method the first time you collect the Instance class data from an instance. If only one instance exists for the tuning session, the Instance class will be collected from that instance. If multiple instances exist, choose the instances from which to collect the instance class by moving them to the Collect list on the Instance Collect Options dialog box.

At any given moment, the instance statistics in a database's V$ tables provide a snapshot of how the instance is performing. When you collect the Instance class during a peak period, Oracle Expert uses the data from the instance statistics sample to generate tuning recommendations to improve database performance during that period. Oracle Expert maintains a history of all the instance statistics samples for a database, so when samples are collected during different peak periods, Oracle Expert gains insight into the instance's performance in different situations and generates better tuning recommendations over time.

Collecting Multiple Instance Statistics Samples During a Collection

By default Oracle Expert collects multiple instance statistics samples during an Instance class collection. This enables Oracle Expert to make better tuning recommendations because:

The Options section of the Instance Collect Options dialog box lets you collect multiple instance statistics samples during a single Instance class collection and specify the length of time for which the instance statistics samples will be collected. For example, if you select a Duration of 1 hour and a Sample Frequency of 12 per hour, Oracle Expert will collect 12 instance statistics samples in an hour as part of the collection process (each instance statistics sample would begin 5 minutes after the previous sample).

If you request that multiple instance statistics samples be collected, a separate process is created to collect samples, so that the collection can continue even if you exit Oracle Expert.

Note that while multiple instance statistics are being collected, you cannot:

Oracle Expert displays your instance statistics samples choices during the collection process. This dialog box displays the name of the tuning session, the collection duration, the sample frequency, and the time stamp of the last sample taken. It also displays the current status of the instance statistics sample collection (either "Pausing Between Samples" or "Collecting Instance Statistics") and the number of scheduled instance statistics sample collections that have been completed (for example, "2 of 13"). Click the Cancel button to cancel an instance statistics collection. After the current statistics sample is collected, Oracle Expert terminates the collection process.

Collecting Instance Class Data from a File

You can collect instance data for the Instance class from an .XDL file. Use the Browse button to browse the available .XDL files on your system. The .XDL file for an Instance class contains instance statistics and instance parameters data.

If you collect instance data from an .XDL file created by exporting an instance during a previous tuning session, after the import operation you need to manually enter data for the instance's Username and Password attributes. To do this, select the instance on the Review page, choose Edit=>Modify, and add the correct data on the Attributes page.

An .XDL file containing Instance class data is created when a Database class is exported.

Getting Less Conservative Instance Tuning Recommendations

By design, Oracle Expert is conservative with instance tuning recommendations until it has compiled a statistically significant amount of historical data about an instance.

The value of the "Samples for statistical significance" rule determines the number of instance statistics samples Oracle Expert will collect before reporting recommendations that reduce current instance resource allocations. The default value for the "Samples for statistical significance" rule is 10. This means, for example, that if Oracle Expert determines from the first Instance class collection that the instance's SGA could be reduced by 10% with no performance loss, this recommendation is withheld until 10 instance statistics samples are collected.

To have Oracle Expert report its less conservative instance tuning recommendations more quickly, you can do one or both of the following:

These measures can be most safely employed in a non-volatile database, for example, a database in which the same applications run at the same time every day, and the number of users and amount of data are fairly stable. For this type of database, the instance statistics samples taken at peak periods over time would probably be very similar. This means there is less risk of Oracle Expert making instance tuning recommendations that are detrimental to the database's performance.

Re-Collecting Instance Class Data

You should re-collect instance statistics and instance parameters if you have made any of the following changes to your database:

In general, instance statistics should be collected regularly over time to help give Oracle Expert a better sense of how the instance uses resources in different situations.

Collecting the Schema Class

When the Schema class is selected on the Collect page of the tuning session window, Oracle Expert can collect the following categories of Schema class data:

One use of schema data is to validate workload data. If you plan to collect workload data during a tuning session, collect Schema data for the schemas and tables referred to by the workload requests prior to collecting the workload. During a workload collection, Oracle Expert removes those requests that refer to schemas or tables which have not been collected.

Collecting Schema Class Data from One or More Instances

You can choose the instance that will be used to collect schema information.

To collect Schema class data directly from an instance, select an instance from the Instance list box on the Schema Collect Options dialog box. Then click the Get Schemas button, and Oracle Expert accesses the instance and displays a list of all the instance's schemas. To collect Schema class data for all the tables in a schema, move the schema to the Collect box. To reduce collection time, specify only the schemas you want to tune.

If you do not want to collect Schema class data for all the tables in a schema, click the plus sign next to the schema icon to display the list of tables for the schema. Then move the tables you want to collect to the Collect box. To reduce collection time, specify only the tables you want to tune. Schemas and tables marked with a question mark have not had data collected. If a schema does not have a question mark, yet one of its tables does have a question mark, then you need to collect data for that particular table.

When the Schema data category in the Options section of the Schema Collect Options dialog box is selected, the schema data is collected directly from the instance's data dictionary tables.

When the Statistics category of Schema class data is selected, the statistics data is collected directly from an instance. If you specify the Expert Scan option, Oracle Expert executes a SQL "SELECT COUNT DISTINCT" statement during the collection to determine the table cardinality (the number of rows) of each selected table and stores the values in the repository. Oracle Expert automatically obtains column cardinality values (the number of distinct values in a column) for each of the columns in the tables being collected.

If you specify the Run ANALYZE Command option, Oracle Expert executes the SQL ANALYZE command with the STATISTICS option on each selected table. Oracle Expert then retrieves the resulting statistics from the database's data dictionary. This option updates the data dictionary statistics on the target node. It required write access privileges to data dictionary tables.

If you specify Read Existing ANALYZE Results, Oracle Expert assumes that a previous SQL ANALYZE operation was performed and retrieves the existing statistics from the database's data dictionary.

You can obtain exact or estimated statistical information when you select the Expert Scan or the Run ANALYZE Command option. Choose the Exact option to obtain exact statistical information. Select and provide a value for the Estimated/Limit option to obtain estimated statistical information. When the Expert Scan option is selected, the value in the Estimated/Limit text box causes Oracle Expert to scan that number of rows in each selected table before estimating the table's cardinality and the column cardinality for each column in the selected table. When the Run ANALYZE Command option is selected, the value in the Estimated/Limit text box causes the SQL ANALYZE command to sample that number of table rows before estimating statistics values. Estimated statistics are usually accurate and can be collected more quickly than exact statistics. Oracle Expert provides better recommendations when provided current statistics.

Collecting Schema Class Data from a File

You can collect the Schema and Statistics data category (cardinality data only) of Schema class data from an .XDL file. Use the Browse button to browse the available .XDL files on your system.

If you collect the Schema data category, Oracle Expert reads only the SQL DDL (Data Definition Language) statements it uses for the Schema data category and ignores SQL DDL statements it does not need. Oracle Expert provides warnings on items in the file that it cannot parse.

Re-Collecting Schema Class Data

After you collect the Schema class the first time, you do not need to collect it again unless one or more of the following is true:

In general, however, schema statistics should be collected regularly over time to help give Oracle Expert a better sense of the physical structure of the schema, for example, cardinality and index stagnation.

Collecting the System Class

When System Class data is required due to the selected tuning categories for a tuning session, Oracle Expert expects to be provided with system data. This category includes memory, CPU, and operating system page size data.

The two ways to provide Oracle Expert with System Class data are:

  1. Entering it manually

    You must use this method the first time you provide system data for a database.

  2. Collecting it from a file

    You can use this method if you have previously exported system data during a tuning session.

Entering System Class Data Manually

The first time you provide Oracle Expert with system data for a database, you must enter it manually. You cannot manually enter System Class data using the System Collect Options dialog box. You must use the Review page.

Entering System Data Manually

System data includes memory, CPU, and operating system data.

Before you can enter the appropriate values for these attributes, the system must be created on the Review page under the System folder (located under the Environment folder). In some cases, Oracle Expert may have already added to the System folder a system object for the system on which the instance runs.

After you add a new system to the System folder, you should make sure the value of the "Percent physical memory available" rule for the system is appropriate for the database being tuned. This rule represents the percent of physical memory available to the instance on the system for the database being tuned. The default value of 80% assumes that:

If either or both of these assumptions are incorrect, you should reduce the percentage of the "Percent physical memory available" rule, taking into account the amount of physical memory required for other databases or applications.

If there are multiple instances running on a system, the "Percent physical memory available" rule gives you control over the proportion of available memory to allocate to each instance. For example, you could allocate 40% to instance A, 20% to instance B, and so on.

Collecting System Class Data from a File

On the System Collect Options dialog box, you can provide System Class data to Oracle Expert by collecting it from an .XDL file. An .XDL file with System Class data is created by exporting a System Class. For example, to export system data, click on the on the Review 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.

To collect System Class data from an .XDL file, specify the name of the .XDL file in the File text box. Use the Browse button to browse the available .XDL files on your system.

Re-Collecting the System Class

If the logical device or system data has changed since the last System Class collection, you should provide Oracle Expert with updated system data.

During a tuning session, you can collect the System Class more than once. If you do, you can import one or more categories of System Class data that have been collected previously. In this situation, if you selected the Overwrite Existing option, Oracle Expert replaces the existing category data with the new data imported for the category. If you did not select the Overwrite Existing option, Oracle Expert displays an error message telling you that it cannot overwrite the existing data.

Collecting the Workload Class

When the Workload class is selected on the Collect page of the tuning session window, Oracle Expert can collect Workload class data.

A workload contains data that describes to Oracle Expert the nature, frequency, and relative importance of applications and requests (SQL statements) that access a database.

The Workload class has the single biggest impact on the Oracle Expert data access tuning recommendations. Therefore, when you select tuning categories that require workload data, it is important to provide Oracle Expert with a representative workload. The SQL History should contain the set of SQL statements used in the database environment.

If you consider some of your applications to be more important than others, you might want to optimize your database to provide its best performance for your more important applications. Oracle Expert can help with this optimization when you provide importance values for workload elements. For more information about providing importance values for workload elements, see "Specifying Importance Values".

Workload Options

The Workload Collect Options dialog box allows you to specify how to collect the Workload class for your tuning session. In addition, you can tell Oracle Expert how to store the collected workload by selecting one of the following options:

If you selected Perform comprehensive index evaluation on tables referenced by worst performing SQL statements on the Scope page of the Tuning Wizard, you will see the following additional options:

Collecting Workload Class Data from a Database's SQL Cache

If you choose the current SQL Cache option, Oracle Expert can collect workload data from the SQL cache of the database you are tuning. At any given time, a database's SQL cache contains the statements that are currently the most frequently executed against the database. Therefore, the statements in the SQL cache are likely to be different at different times, depending on which applications are running.

When there are multiple instances of the database, Oracle Expert collects the statements in the SQL caches of all the instances that are part of the tuning session.

A workload request (SQL statement) is invalid if it refers to schema elements for which data has not been collected. Workload validation may or may not occur. Validation depends on the tuning scopes you select. When data access validation takes place, you may or may not have to collect tables.

Collecting Workload Class Data from a SQL History

If a SQL History already exists, you can use the SQL History as a source for collecting workload.

If the source for a workload collection is set to SQL History, the SQL History options will not be available.

The SQL History is shared between SQL Analyze, Oracle Expert, and the Index Tuning wizard. The SQL History is intended to contain a complete set of SQL for the database environment. A SQL History will ensure that consistent index tuning recommendations are made across the three tools.

Collecting Workload Class Data from a File

To collect workload data from an .XDL file, choose the File option. You create an .XDL file by exporting workload data from an existing tuning session.

In some cases it can be useful to collect workload data from an .XDL file even if the workload data is not exactly what you want to use for the database being tuned. After you import a workload from an .XDL file, you can modify the data for individual workload elements until you have a representative workload for your tuning session.

Collecting Workload Class Data from an Oracle Trace Database

You can use Oracle Trace to collect workload data directly from a database in real time (while users and applications are accessing the database). After collecting data with Oracle Trace, you can format it and store it in an Oracle Trace formatted database.

To collect Workload class data from an Oracle Trace database, select the Trace option on the Workload Collect Options dialog box and identify the database from which to collect the Oracle Trace data. You identify the database by supplying information in the user, password, service, and collection name fields.

A workload request (SQL statement) is invalid if it refers to schema elements for which data has not been collected.

Re-Collecting Workload Class Data

During a tuning session, you can collect the Workload class more than once. If you have previously collected workload data for a tuning session, Oracle Expert will automatically update the collection.

Collecting Workload Class Data Manually

You can also manually enter workload data on the View/Edit page of the tuning session window. This is a tedious process. Manually entering workload data is a reasonable approach only when you are using Oracle Expert to configure a new database (which no applications or users are accessing yet). If you know the important statements that will run against the database, you can manually enter them to help Oracle Expert generate a better initial configuration.

Starting a Collection

To begin a collection, you must have at least one collection class selected. However, if there are no valid options for that collection class, Oracle Expert displays a message stating it cannot perform a collection.

When you have selected valid options for a collection class, a green check mark appears in the Options Set? column for that class on the Collect page of the tuning session window. If there are invalid options for a collection class, a red X appears. Oracle Expert collects data only for collection classes that have valid options.

If you want specific details of the collection's progress while the collection is running, click the Work in Progress button in the toolbar.

Restrictions During a Collection

During a collection, you cannot:

Canceling a Collection

To cancel a collection that is in progress, choose Cancel=>Process.

If some of the selected classes were not collected, a dialog box asks whether you want Oracle Expert to collect the uncollected classes. Choose Yes to continue collecting the other classes, No to stop the collection process and return to the Collect page.

If the collection stops while the last class is being collected, you may be given only the choice to Continue, which ends the collection and returns you to the Collect page.

If a collection stops due to an unexpected error, the dialog box displayed may include the Abort option, which allows you to exit Oracle Expert.

After a collection is terminated, some of the data collected may be incomplete and unusable. If Oracle Expert did not exit when the collection was terminated, a dialog box displayed after the collection informs you that incomplete and unusable data exists for a particular schema. This dialog box gives you the option of having Oracle Expert delete all the data for this schema and its children or having Oracle Expert remove the incomplete flag and use the data as it is. If Oracle Expert exited when the collection was terminated, this dialog box is displayed when you open the tuning session again.

Collecting Invalid Data

Some objects that Oracle Expert collects have dependencies on or references to other objects. If Oracle Expert collects data about a particular object and cannot find collected data about other objects referred to by the object, it displays error messages at the end of the collection and considers the collected object to be invalid. For example:

When Oracle Expert determines during collection that a collected object is invalid, the object is marked with the international No symbol on the View/Edit page of the tuning session window.

By default, Oracle Expert includes invalid data when it analyzes tuning session data, but it cannot generate any recommendations regarding invalid objects. Different methods of dealing with invalid objects include:

Oracle Expert discovers that some objects are invalid during an analysis.


Go to previous page Go to next page
Oracle
Copyright © 2001 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index