Release 1.4.0
A53653_01
 Library |
 Product |
 Contents |
 Index |
| Oracle Enterprise Manager Oracle Expert User's Guide Release 1.4.0 A53653_01 |
|
This chapter describes how to collect data for a tuning session. The topics discussed in this chapter include:
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.
The collection classes appear on the Collect page of the tuning session window (see Figure 5-1). They are:
Table 5-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.
| 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 |
Yes |
Instance or .XDL file |
|
Environment |
Small |
Low to Medium |
No |
User input or .XDL file |
|
Workload |
Variable |
Variable |
Yes1 |
Instance (SQL cache), Oracle Trace, .XDL file, or user input |
|
1
See "Collecting the Workload Class". |
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 5-2.
When the Environment class is required for a selected tuning category, Oracle Expert enables the Environment class option on the Collect page. Select the Environment class option if you plan to import Environment class data from a file created by exporting Environment class data during a previous tuning session. For more information about collecting Environment class data, see "Collecting the Environment Class".
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. Note that Oracle Expert does not update the date and time in the Last Collected column for the Environment class when Environment class data is manually entered.
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, as shown in Figure 5-2:
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. Table 5-2 shows the required collection class options for each Oracle Expert tuning category. If you are tuning multiple categories and the same collection class option is required by more than one category, you need to collect the option only once.
| Instance Tuning |
Application Tuning |
Structure Tuning | ||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| Collection Class Options | SGA | I/O | Sort | Parallel Query | OPS1 | OS2 | SQL Tuning | Access Methods | Sizing | Placement |
|
Database Class: |
|
|
|
|
|
|
|
|
|
|
|
Name/Version |
Y |
Y |
Y |
Y |
Y |
Y |
Y |
Y |
Y |
Y |
|
Database Users |
N |
N |
N |
N |
N |
N |
Y |
Y |
N |
Y |
|
Tablespaces |
N |
N |
N |
N |
Y |
N |
Y |
Y |
Y |
Y |
|
Public Synonyms |
N |
N |
N |
N |
N |
N |
Y |
Y |
Y |
N |
|
Instance Class: |
|
|
|
|
|
|
|
|
|
|
|
Instance Statistics |
Y |
Y |
Y |
Y |
Y |
Y |
N |
N |
Y |
N |
|
Instance Parameters |
Y |
Y |
Y |
Y |
Y |
Y |
N |
Y |
Y |
Y |
|
Schema Class: |
|
|
|
|
|
|
|
|
|
|
|
Schema |
N |
N |
N |
N |
N |
N |
Y |
Y |
Y |
N3 |
|
Statistics |
N |
N |
N |
N |
N |
N |
N |
Y |
Y |
N |
|
Environment Class: |
|
|
|
|
|
|
|
|
|
|
|
Device Data |
N |
N |
N |
N |
N |
N |
N |
N |
N |
N |
|
System/Node Data |
Y |
Y |
Y |
Y |
Y |
Y |
N |
N |
N |
N |
|
Workload Class: |
N |
N |
N |
N |
N |
N |
Y |
Y |
Y |
N |
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 data. If none of the collection classes have valid data, Oracle Expert will display a message stating so and no collection is performed.
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 Access Methods tuning category 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 Access Methods category, you do not need to collect Schema class data for all the schemas in your database. Instead, you can collect Schema class data for only the schemas and tables that you want to tune.
When you limit the amount of data you collect, you shorten the collection, analysis, and recommendation review cycle.
For each class, the following sections describe:
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:
Oracle Expert obtains Database class data primarily from a database's data dictionary and dynamic performance tables (V$ tables).
Database user, tablespace, and public synonym data are important for workload validation. By default, only valid workload requests are allowed into the repository. If you do not collect database user, tablespace, and public synonym data, the workload data that you collect will be unusable.
Oracle Expert uses Database class data to make recommendations about:
The Database Collect Options dialog box (see Figure 5-3) allows you to specify how to collect the Database class for your tuning session.
The following sections describe:
These elements appear in the Source section of the Database Collect Options dialog box:
The instance from which the Database class is to be collected.
The file from which the Database class is to be collected.
Allows you to browse the .XDL files on your system to select a file.
These elements appear in the Options section of the Database Collect Options dialog box:
Indicates whether or not the name, version, installed options, and database-wide statistics category of Database class data is to be collected.
Indicates whether or not the tablespaces category of Database class data is to be collected.
Indicates whether or not the database users category of Database class data is to be collected.
Indicates whether or not the public synonyms category of Database class data is to be collected.
Indicates whether or not existing categories of Database class data will be overwritten with the categories to be collected.
You can automatically collect Database class data directly from an instance. You must use this method the first time you collect the Database class for a database. On the Database Collect Options dialog box, use the Instance list box to identify the instance from which you want Oracle Expert to collect the Database class data.
You can collect Database class data from an .XDL file (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 option and database-wide statistics data) 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. For more information about exporting Database class data, see "Export".
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, if you select the Overwrite Existing option, Oracle Expert replaces the existing category data with the new data collected for the category. If you do not select the Overwrite Existing option, Oracle Expert displays an error message telling you that it cannot overwrite the existing data.
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:
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).
The Instance Collect Options dialog box (see Figure 5-4) allows you to specify how to collect the Instance class for your tuning session.
The following sections describe:
These elements appear in the Source section of the Instance Collect Options dialog box:
The option to choose when the Instance class data is to be collected from one or more instances.
When multiple instances exist, the instances from which the Instance class is not to be collected.
When multiple instances exist, the instances from which the Instance class is to be collected.
The option to choose when the Instance class data is to be collected from an .XDL file.
Allows you to browse the .XDL files on your system to select a file.
These elements appear in the Options section of the Instance Collect Options dialog box:
Indicates whether or not the instance statistics category of Instance class data is to be collected.
Indicates whether an instance statistics sample is to be a snapshot collection or a duration collection. The default duration is 1/4 hour.
When an instance statistics sample is to be a duration collection, the number of samples to be collected per hour. The default is 12 per hour.
Indicates whether or not the instance parameters category of Instance class data is to be collected.
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 box 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 increase 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.
By default Oracle Expert collects multiple instance statistics samples during an Instance class collection. This enables Oracle Expert to make better tuning recommendations when you collect multiple instance statistics samples during an Instance class collection 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. The Duration list box has a default value of Once, which means a single instance statistics sample is collected. If you change the default value of the Duration list box, you can select values in the Sample Frequency list box. 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 multiple instance statistics samples, so that the collection of instance statistics samples 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.
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 an instance 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 View/Edit 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. For more information about exporting Database class data, see "Export".
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:
By collecting more samples with each Instance class collection, you can collect a statistically significant number of samples in less time.
If you reduce the value of the "Samples for statistical significance" rule from 10 to 5, for example, Oracle Expert will report recommendations to reduce instance resource allocations after 5 instance statistics samples have been collected. To change the rule's value, select the database for which you want to change the rule on the View/Edit page of the tuning session window, then choose Edit=>Modify. Select the Rules tab of the Edit dialog box, then the Common tab. Then click in the rule's Value column and enter a new value.
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.
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.
Oracle Expert uses the data in the Instance class to recommend changes to the following parameters:
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:
Schema data is used 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. By default, during a workload collection, Oracle Expert removes those requests that refer to schemas or tables for which no data has been collected.
Oracle Expert uses the data in the Schema class to make index creation, formation, and placement recommendations.
The Schema Collect Options dialog box (see Figure 5-5) allows you to specify how to collect the Schema class for your tuning session.
The following sections describe:
These elements appear in the Source section of the Schema Collect Options dialog box:
Indicates the schema class to be collected from an instance.
Displays the schemas for the selected instance.
Lists schemas or tables that will not be collected. Schemas and tables marked with a question mark have not had data collected. This element displays if you choose to collect from a file.
Lists schemas or tables that will be collected. Schemas and tables marked with a question mark have not had data collected. However, a schema may not have a question mark but one of its tables could have a question mark. This element displays if you choose to collect from a file.
Indicates the schema class to be collected from a file.
Allows you to browse the files on your system.
These elements appear in the Options section of the Schema Collect Options dialog box:
Indicates whether or not the Schema category of Schema class data is to be collected.
Indicates whether previously existing schema data is to be deleted before a schema with the same name is collected.
Indicates whether previously existing table data is to be deleted before a table with the same name is collected.
Indicates that no schema or table data should be deleted during a collection. If a schema or table with the same name exists, Oracle Expert terminates that schema collection and generates an error message.
Indicates whether or not the Statistics category of Schema class data is to be collected.
Allows you to collect cardinality data for the tables being collected.
Indicates that Oracle Expert should perform a SQL ANALYZE command and then retrieve cardinality data and statistics about the tables to be collected.
Indicates that Oracle Expert should retrieve cardinality data and statistics about the table to be collected from results of a previously executed SQL ANALYZE command.
When selected, exact statistical information is obtained because every row of the selected table is scanned.
The specified number of rows are scanned, and estimated statistical values are computed for the selected tables based on the data in those rows.
You can collect the Schema data category and Statistics category of Schema class data directly from one or more instances.
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 New SQL ANALYZE 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.
If you specify Existing SQL ANALYZE, 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, the New SQL ANALYZE, or the Existing SQL ANALYZE option. Choose the Full Scan 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 New SQL ANALYZE 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 with current statistics.
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.
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 the Options section of the Schema Collect Options dialog box, you can specify whether or not to allow Oracle Expert to overwrite existing data for schemas and tables, as follows:
When Environment class data is required due to the selected tuning categories for a tuning session, Oracle Expert expects to be provided with one or both categories of Environment class data:
This category includes memory, CPU, and operating system page size data
The two ways to provide Oracle Expert with Environment class data are:
You must use this method the first time you provide logical device data or system data for a database.
You can use this method if you have previously exported logical device data or system data during a tuning session.
Oracle Expert uses the data in the Environment class to help it make instance recommendations.
The following sections describe:
These elements appear in the Source section of the Environment Collect Options dialog box (see Figure 5-6):
Indicates that you will manually input data using the View/Edit page. Oracle Expert places a reminder to collect the data in the Last Collected column of the Collect page.
Indicates the Environment class to be collected from a file.
Allows you to browse the files on your system.
These elements appear in the Options section of the Environment Collect Options dialog box:
Indicates whether logical device data is required based on the selected tuning categories.
Indicates whether system data is required based on the selected tuning categories.
Indicates whether or not Oracle Expert will overwrite existing Environment class data with the new Environment class data to be collected.
The first time you provide Oracle Expert with logical device data or system data for a database, you must enter it manually. You cannot manually enter Environment class data using the Environment Collect Options dialog box. You must use the View/Edit page.
The first time you provide logical device data for a database, you must enter it manually.
To enter logical device data manually, follow these steps:
The name of the logical device
The physical location of the device
A numerical ranking of the logical device compared with the other logical devices on the system, with 1 for the fastest and higher values for slower devices
The device size, expressed in megabytes
The percentage of disk space available for the database being tuned
Either READ_WRITE or WRITE_ONCE
The first time you provide system data for an instance, you must enter it manually. System data includes memory, CPU, and operating system data.
To enter system data manually, follow these steps:
For each system that is added to a tuning session, Oracle Expert expects to be provided with values for the following system data attributes:
The name of the system
The amount of memory available on the system, expressed in megabytes
The percentage of the total memory that the system routinely uses
The percentage of the total memory that the system uses during its peak period
The percentage of the total CPU resources that the system routinely uses
The percentage of the total CPU resources that the system uses during its peak period
The number of CPUs on the system
The size of an operating system page, expressed in bytes
Before you can enter the appropriate values for these attributes, the system must be created on the View/Edit 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. If a system has been added, follow these steps:
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.
On the Environment Collect Options dialog box (see Figure 5-6), you can provide Environment class data to Oracle Expert by collecting it from an .XDL file. An .XDL file with Environment class data is created by exporting an Environment class. For example, 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. For more information about exporting an Environment class, see "Export".
To collect Environment 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.
If the logical device or system data has changed since the last Environment class collection, you should provide Oracle Expert with updated logical device or system data.
During a tuning session, you can collect the Environment class more than once. If you do, you can import one or more categories of Environment 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.
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, business units, transactions, and requests (SQL statements) that access a database. For more information about determining a representative workload for your tuning session, see "Determining a Representative Workload for a Tuning Session".
The Workload class has the single biggest impact on the Oracle Expert application tuning recommendations. Therefore, when you select tuning categories that require workload data, it is important to provide Oracle Expert with a representative workload.
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 for Workload Elements".
Oracle Expert uses the data in the Workload class to make recommendations about:
The Workload Collect Options dialog box (see Figure 5-7) allows you to specify how to collect the Workload class for your tuning session.
The following sections describe:
These elements appear in the Source section of the Workload Collect Options dialog box:
Choose the Trace option to collect workload data from an Oracle Trace database. An Oracle Trace database is a database that contains formatted Oracle Trace workload data.
The username of the Oracle Trace database from which the Workload class is to be collected.
A valid password for the user.
The service name can optionally be entered for the remote Oracle Trace database user.
The collection name for the Oracle Trace collection from which the workload class is to be collected. Note that the collection name is case sensitive.
Choose this option to collect the Workload cache from each of the instances that have been identified for the tuning session.
Choose this option to collect workload data from an .XDL file. The .XDL file is a workload that has previously been exported.
Allows you to browse the .XDL files on your system.
These elements appear in the Options section of the Workload Collect Options dialog box:
Saves the existing workload data for a particular application and also saves the worload data being imported for that same application. Oracle Expert assigns a new name to the newly imported workload data for the application. For additional information, see "Appending to Workload Data".
Deletes existing workload data and replaces it with new workload data being collected.
If this option is selected, all the SQL statements in a workload are collected and saved. Otherwise, only valid SQL statements (the statements that refer to previously collected schemas and tables) are saved.
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. This manual refers to a database that contains Oracle Trace formatted data as an Oracle Trace 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 username, 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. By default, Oracle Expert does not save invalid requests when it collects workload data. This means that if you first collect data for the schemas and tables you are interested in tuning and then collect workload data, only requests that refer to the collected schemas are saved. This allows Oracle Expert to tune only the statements that access the schemas and tables you want to tune.
If you enable the Save Invalid Requests option, all the statements in the Oracle Trace database are collected and saved. After collecting the workload data, you can collect data for the schemas and tables you are interested in tuning, then validate invalid requests. Oracle Expert does not use invalid requests during analysis. Analysis will work faster if invalid requests are purged during collection or validation. For more information about invalid requests, see "Collecting Invalid Data". For more information about validating invalid objects, see "Validate".
For more information on using Oracle Trace to collect workload information to be provided to Oracle Expert, see "Collecting Workload Information with Oracle Trace".
If you choose the 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 against the database.
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 collected data for the tuning session.
A workload request (SQL statement) is invalid if it refers to schema elements for which data has not been collected. By default, Oracle Expert does not save invalid requests when it collects workload data. This means that if you first collect data for the schemas and tables you are interested in tuning and then collect workload data, requests that do not refer to the collected schemas and tables are deleted during the workload collection. This allows Oracle Expert to tune only the statements that access the schemas and tables you want to tune.
If you enable the Save Invalid Requests option, all the statements in each instance's SQL cache are collected and saved. After collecting the workload data, you can collect data for the schemas and tables you are interested in tuning, then validate invalid requests. You can choose not to validate invalid requests, but invalid requests slow down an Oracle Expert analysis and Oracle Expert cannot make any recommendations for invalid requests. For more information about invalid requests, see "Collecting Invalid Data". For more information about validating invalid objects, see "Validate".
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 during a tuning session. 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.
A workload request (SQL statement) is invalid if it refers to schema elements for which data has not been collected. By default, Oracle Expert does not save invalid requests when it collects workload data. This means that if you first collect data for the schemas and tables you are interested in tuning and then collect workload data, requests that do not refer to the collected schemas and tables are deleted during the workload collection. This allows Oracle Expert to tune only the statements that access the schemas and tables you want to tune.
If you enable the Save Invalid Requests option, all the statements in the .XDL file are collected and saved. After collecting the workload data, you can collect data for the schemas and tables you are interested in tuning, then validate invalid requests. You can choose not to validate invalid requests, but invalid requests slow down an Oracle Expert analysis and Oracle Expert cannot make any recommendations for invalid requests. For more information about invalid requests, see "Collecting Invalid Data". For more information about validating invalid objects, see "Validate".
During a tuning session, you can collect the Workload class more than once. If you have previously collected workload data for a tuning session, use the Overwrite Existing option to specify how Oracle Expert should manage the previously collected workload data.
For Workload class data, the Overwrite Existing option pertains to workload data at the Application level only. If you select Overwrite Existing, Oracle Expert deletes the existing workload data for a particular application and replaces it with the workload data being collected for that same application.
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.
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 (see Figure 5-1). 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.
When a collection is in progress, Oracle Expert:
During a collection, you cannot:
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.
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:
Oracle Expert considers a collected application to be invalid if it contains invalid requests (SQL statements that refer to schema objects that have not been collected). Only requests that were successfully validated will be used during analysis.
When Oracle Expert collects a table, cluster, or index, the segment information for that object is also collected. If the tablespace containing the segment has not been collected, Oracle Expert is unable to create the segment in the repository and considers the table, cluster, or index to be invalid.
If any schema object is marked invalid during a collection, the schema is also marked invalid.
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:
This method allows Oracle Expert to make recommendations about the object. For more information about validating an invalid object, see "Validate".
This method allows Oracle Expert to exclude the object during an analysis, which speeds up the analysis. Oracle Expert cannot make any recommendations about the object. For more information about excluding objects from an analysis, see "Analyzing Data Efficiently".
This method removes the object from the repository. For more information about deleting an object, see "Delete".
Oracle Expert discovers that some objects are invalid during an analysis. For more information about objects that are found to be invalid during an analysis, see "Invalid Objects Discovered During an Analysis".
