|Oracle Enterprise Manager Database Tuning with the Oracle Tuning Pack
Part Number A86647-01
One of the categories of application tuning. During access methods tuning, Oracle Expert determines what indexes are needed and generates the SQL statements to create, modify, and delete indexes as appropriate. Oracle Expert ensures that the indexing strategy is consistent with that of the Oracle cost-based optimizer. Oracle Expert addresses sorted (B-tree) and bit-mapped indexes. See also: SQL reuse.
The process by which Oracle Expert examines the tuning data collected for a database and generates tuning recommendations.
A report that describes the Oracle Expert tuning recommendations. It provides a detailed explanation of the data Oracle Expert evaluated, how Oracle Expert interpreted the data, and any risks associated with implementing the recommendations.
The ANALYZE command collects and stores table and index statistics which are essential for the efficient operation of the cost-based optimizer.
If an object has not been analyzed at least once and the CHOOSE optimizer method is the default, Oracle SQL Analyze will use the rule-based optimization method when generating an explain plan.
The frequency with which you analyze the objects depends on the rate of change within the objects. If you analyze a table, its associated tables are automatically analyzed as well.
This command may lock portions of the database while it collects statistics.
The workload category at the top of the workload hierarchy. See also Request.
A characteristic of a data object. You can view and edit attributes for objects using the Edit dialog box.
The part of access method tuning where Oracle Expert scans schemas selected for tuning for evidence of implicit search operations, such as constraints or views. If a constraint or view is found, Oracle Expert determines whether an index is necessary to improve performance when the constraint or view is executed.
Bitmap indexing provides the same functionality as regular indexes, but uses a different internal representation, which makes it very fast and space efficient. They work best when each key references many records, such as employee gender.
Bind variables allow variables within a SQL statement, such as data values or search keys, to be defined as parameters of the SQL statement. This approach allows SQL statements to be re-executed without re-parsing the statement.
While generating an explain plan for a SQL statement, Oracle SQL Analyze will request sample values for bind variables. Although it is not required to enter a value, the values will help Oracle SQL Analyze evaluate the statement and its environment to develop the best optimization plan.
A group of parameters whose values provide useful information to Oracle Expert for tuning your database environment. Examples of business characteristics for which you can supply values are the classes of workload (such as multipurpose, OLTP, or Data Warehousing) and the tolerance for database downtime. Oracle Expert takes business characteristic values into account when making tuning recommendations. (Previously referred to as control parameters and business characteristics.)
Can refer to either table cardinality (the number of rows in a table) or column cardinality (the number of distinct values in a column of a table).
See tuning category.
If a row is too large to fit into one data block when it is first inserted, Oracle stores the data for the row in a chain of data blocks (one or more) reserved for that segment. Row chaining most often occurs with large rows, such as rows that contain a column of datatype LONG or LONG RAW. When a row is chained, I/O performance associated with this row decreases because Oracle must scan more than one data block to retrieve the information for the row.
When you select Choose (optimizer's choice), the optimizer determines whether at least one object has been analyzed (using the ANALYZE command). If at least one object has been analyzed, the optimizer uses cost-based optimization for throughput. If not, the optimizer uses rule-based optimization.
For more information, see Oracle9i Database Performance Guide and Reference.
See collection class.
The gathering of data that Oracle Expert analyzes to make tuning session recommendations.
A category of information that Oracle Expert collects and analyzes. The collection classes are Database, Instance, Schema, Environment, and Workload.
The number of distinct values in a column of a table.
Compact views are representations of the explain plan that illustrate the join methodologies selected by the Oracle optimizer.
A tuning session in which you have selected all the tuning categories at the same time. During analysis for a comprehensive tuning session, Oracle Expert generates all the tuning recommendations of which it is capable (for all areas of your database environment).
Cost-based optimization considers statistical information about the volume and distribution of data within tables and indexes before determining the execution path for a statement. It then tries to choose the execution path that has the least "cost."
In this context, "cost" is a measurement of many factors, including the amount of computer resources (I/O and CPU consumption, for example), and the time to complete the execution.
When optimizing for response time (also known as "cost all" optimizing), the optimizer chooses to execute the statement in a way that most efficiently retrieves the first row of data.
At least one referenced object (table or index) must be analyzed before cost-based optimization can be performed.
For more information, see "Understanding the Oracle Optimizer", and the Oracle9i Database Performance Guide and Reference.
Cost-based optimization considers statistical information about the volume and distribution of data within tables and indexes before determining the execution path for a statement. It then tries to chose the execution path that has the least "cost."
In this context, "cost" is a measurement of many factors, including the amount of computer resources (I/O and CPU consumption, for example), and the time to complete the execution.
When optimizing for throughput, the optimizer chooses to execute the statement in a way that most efficiently process all rows specified.
At least one referenced object (table or index) must be analyzed before cost-based optimization can be performed.
A cursor is a handle (a name or pointer) for the memory associated with a specific statement. (The Oracle Call Interface, OCI, refers to these as statement handles.) For example, in precompiler application development, a cursor is a named resource available to a program and can be specifically used for parsing SQL statements embedded within the application.
The interval of time during which a sequence of a recurring events is completed.
A database object represents the database against which the active SQL statement is executed.
When the database object is selected, you can view some of the characteristics of the database, but you cannot edit them.
A process that involves tasks such as balancing competing database resources so that important applications get the resources they need, identifying and eliminating resource bottlenecks, and optimizing the use of existing resources in the database environment.
DDL statements define, maintain, and drop objects when they are no longer needed. DDL statements also include statements that permit a user to grant other users the privileges, or rights, to access the database and specific objects within the database.
The data dictionary is a read-only set of tables that provides information about its associated database. For example, a data dictionary can provide the following information:
The data dictionary is structured in tables and views, just like other database data. Because the data dictionary is read-only, users can issue only queries (SELECT statements) against the tables and views of the data dictionary.
DML statements manipulate the database's data. For example, querying, inserting, updating, and deleting rows of a table are all DML operations; locking a table or view and examining the execution plan of an SQL statement are also DML operations.
Decision support or data warehousing applications distill large amounts of information into understandable reports. Typically, decision support applications perform queries on the large amount of data gathered from OLTP applications. The key goals of a decision support system are response time, accuracy, and availability.
An example of a decision support system is a marketing tool that determines the buying patterns of consumers based on information gathered from demographic studies. The demographic data is assembled and entered into the system, and the marketing staff queries this data to determine which items sell best in which locations. This report helps to decide which items to purchase and market in the various locations.
A portion of the rules that make up the extensive Oracle Expert knowledge base. Each Oracle Expert rule exists at the Oracle Expert system level. Rules at the Oracle Expert system level are called default rules, and they have default values. A copy of a default rule can exist at one or more object levels. Oracle Expert allows you to view some default rules. Oracle Expert allows you to change the default value of any default rule it allows you to view. See also advanced rules and rules.
The number of parallel server processes associated with a single operation is known as the degree of parallelism.
The degree of parallelism is specified at the statement level (with hints or the PARALLEL clause), at the table or index level (in the table's or index's definition), or by default based on the number of disks or CPUs.
Note that the degree of parallelism applies directly only to intra-operation parallelism. If inter-operation parallelism is possible, the total number of parallel server processes for a statement can be twice the specified degree of parallelism. No more than two operations can be performed simultaneously.
The act of viewing, modifying, adding, or deleting collected data that appears on the View/Edit page of the tuning session window.
The statistical weight given to the frequency value of an element when computing its relative importance. The emphasis is based on either a user-supplied importance, calculated frequency, or the physical I/O count. By default, Oracle Expert gives more statistical weight to frequency. This can be changed by modifying the "Primary workload analysis style" and "Secondary workload analysis style" Workload user rules.
The physical resources of the database, for example, the system on which the database runs and the logical devices used by the database. Data about a database's environment is collected in the Environment collection class.
An explain plan is an access path determined by the query optimizer.
Oracle SQL Analyze displays explain plans based on different optimizer modes, and helps you "step through" the execution path.
For more information, see"Analyzing Explain Plans".
An explain plan node represents explain plans generated for a specific SQL statement.
Selecting an explain plan object displays the explain plan in the SQL Text window.
A workload collection of statements that Expert has decided are applicable to tuning the scope specified for a tuning session.
A tuning session in which you have not selected all of the Oracle Expert tuning categories at the same time. After an analysis is performed in a focused tuning session, Oracle Expert generates tuning recommendations for the selected tuning categories.
The number of times a workload element is executed by the workload element above it in the workload hierarchy. For example, for a workload Request, the frequency is the number of times the Request is executed during a workload Transaction.
Hints are instructions in the SQL code that direct the Oracle optimizer to use specific methods when creating an explain plan.
The process during which Oracle Expert generates implementation files and implementation scripts that can help you put the Oracle Expert tuning recommendations into effect. See also implementation files and implementation scripts.
Files that Oracle Expert generates to help you put its tuning recommendations into effect.
Scripts generated by Oracle Expert that you can run to put the Oracle Expert tuning recommendations into effect.
The importance (or priority) value assigned to a workload element relative to the other workload elements at the same level of the workload hierarchy. For example, given two workload Applications, where one is a production, high-availability application and the other is a maintenance application, you would assign the first a higher importance value than the second. Oracle Expert uses this value to ensure that higher priority Applications and Requests are favored over less important ones during the optimization process. The range of importance values is from 1 to 9999, with 1 being the least important. See also relative importance.
One of the categories of access methods tuning in which Oracle Expert analyzes indexes to identify those that suffer from index stagnation and should be rebuilt to enhance performance. See also index stagnation, optimal index use, and access methods.
A condition in which the amount of unusable space within the index increases to a level that negatively impacts performance.
A tuning session in which Oracle Expert is used to help configure a new database.
Initialization parameter objects, located in the Navigator window, represent specific instances of the database to which they are connected on the navigation tree.
By selecting an initialization parameter object, you can edit the parameters displayed for the purpose of simulating a SQL tuning environment other than that of the current database.
A focused tuning session in which one or more categories of instance tuning (SGA parameters, I/O parameters, parallel query parameters, Oracle Parallel Server parameters, operating system-specific parameters, or sort parameters) are selected for one or more instances. See also I/O parameters, parallel query parameters, SGA parameters, Oracle Parallel Server parameters, operating system-specific parameters, and sort parameters.
Making a copy of a default rule at an object level. This makes the object the owner of the copy of the rule. Oracle Expert uses an object's values for instantiated rules during an analysis. See also default rules and rules.
These instance parameters affect the throughput or distribution of I/O for the instance. Examples of these parameters include the checkpoint_process and db_file_multiblock_read_count parameters. These parameters can be selected as a category to be tuned as part of an instance tuning session. See also instance tuning session, parallel query parameters, SGA parameters, sort parameters, Oracle Parallel Server parameters, and operating system-specific parameters.
Joins allow rows of two or more tables to be merged, usually based on common key values. Improving the strategy the optimizer uses to join tables while executing a SQL statement is a primary method of enhancing performance.
In Oracle SQL Analyze, you can affect join order optimization by adding hints or by analyzing the statement with the Tuning Wizard.
If a row that originally fit into one data block is updated so that the overall row length increases, and the block's free space is already completely filled, the Oracle will migrate the data for the entire row to another data block, assuming the entire row can fit in a new block. Oracle preserves the original row piece of a migrate row to point to the new block containing the migrated row; the rowid of a migrated row does not change. When a row is chained or migrated, I/O performance associated with this row decreases because Oracle must scan more than one data block to retrieve the information for the row. See also chained rows.
Multipurpose applications are characterized by one or few users doing long transactions where response times are not critical. Typically, multipurpose or batch applications are executed overnight with the restriction that they must complete before morning. See also Data Warehousing and OLTP applications.
The node name is the host name of the machine where an instance is running.
Object details provide information about data objects referenced by a SQL statement.
These details include information about tables, clusters, and indexes related to an object, and can be used to further understand the performance of associated explain plans.
OLTP (online transaction processing) applications typically use simple queries that require quick response time against tables containing a mix of read and write requests. See also multipurpose applications and Data Warehousing.
Online transaction processing (OLTP) applications are high-throughput, insert/update intensive systems. These systems are characterized by constantly growing large volumes of data that several hundred users access concurrently. Typical OLTP applications include airline reservation systems, order-entry applications, and banking applications.
These instance parameters affect performance and are specific to certain hardware platforms. Examples of these parameters include the async_write and the db_writers parameters. These parameters can be selected as a category to be tuned as part of an instance tuning session. See also instance tuning session, I/O parameters, SGA parameters, parallel query parameters, sort parameters, and Oracle Parallel Server parameters.
One of the categories of access methods tuning in which Oracle Expert uses base index and workload analysis tuning to determine whether new indexes should be created or current indexes modified to enhance performance. See also index rebuild detection, base index tuning, and access methods.
The Oracle optimizer determines the execution path taken to perform the commands of a SQL statement.
There are four modes of optimization available:
The mode used for optimization is set via the OPTIMIZER_MODE parameter in each instance's init.ora file. The mode can be overridden by using the ALTER SESSION SET OPTIMIZER_GOAL command or by adding hints to a statement.
These parameters influence the performance or configuration of the parallel server environment. Selecting the Oracle Parallel Server parameters category for tuning is relevant only if the Oracle Parallel Server option is installed. These parameters can be selected as a category to be tuned as part of an instance tuning session. See also instance tuning session, I/O parameters, SGA parameters, sort parameters, parallel query parameters, and operating system-specific parameters.
These instance parameters are specific to the parallel query behavior for the instance. Examples of these parameters include the parallel_min_servers and parallel_max_servers parameters. Selecting the parallel query parameters category for tuning is relevant only if the parallel query option is installed. These parameters can be selected as a category to be tuned as part of an instance tuning session. See also instance tuning session, I/O parameters, SGA parameters, sort parameters, Oracle Parallel Server parameters, and operating system-specific parameters.
A category of structure tuning. Oracle Expert makes recommendations about placement of structures, such as segment partitioning. Oracle Expert may recommend separating segments into different tablespaces to minimize tablespace free space fragmentation and maximize administrative flexibility. See also sizing.
The Program Global Area (PGA) is a memory region that contains data and control information for a single process (server or background). Consequently, the PGA is referred to as the Program Global Area or the Process Global Area.
Oracle Expert considers the elements in the highest category of the workload hierarchy (Applications) to be most important and elements in each of the lower categories to be proportionately less important. Rank is ordered by sequence where 1 is most important.
A report that lists the Oracle Expert tuning recommendations. It provides the reasons why you received the recommendations.
A value that ranks a workload element's importance compared to all the other workload elements. Oracle Expert computes the relative importance for each workload element. The factors that Oracle Expert takes into account when computing a relative importance value for a workload element are the element's workload category (Application or Request), importance value, and frequency value. See also importance.
Information that Oracle Expert can generate about the collected data for a tuning session (Session Data report) or the recommendations it has generated (Analysis report or Recommendation Summary report). See also Analysis report, Session Data report, and Recommendation Summary report.
A schema in an Oracle database that stores the data associated with each Oracle Expert tuning session.
An Oracle SQL Analyze repository stores the information from SQL tuning sessions, including:
Object details are not saved in the repository.
You can return to a saved repository at any time to continue a tuning session.
A workload that includes a representative set of the SQL statements that execute during a period for which you want Oracle Expert to optimize database performance. You would typically provide valid importance values for the elements in a representative workload to allow Oracle Expert to optimize performance for the statements with the highest relative importance.
A SQL statement. Requests are the elements at the lowest level of the workload hierarchy. See also Application.
When you choose rule-based optimization, the Oracle optimizer executes SQL statements based on a set of syntactical rules and the rankings of various access paths. It does not consider statistical information relating to the volume and distribution of data within tables and indexes.
In most cases, cost-based optimization is the preferred approach for new applications and for data warehousing applications because it supports new and enhanced features.
In general, the rule-based optimizer takes the following approach:
For more information, see "Analyzing Explain Plans" and the Oracle9i Database Performance Guide and Reference.
Pieces of knowledge used by Oracle Expert to analyze collected data for a tuning session. Oracle Expert allows you to view some of its rules, and you can modify the value of any rule you view. Modifying rule values may change the behavior of Oracle Expert during an analysis and also its recommendations. See also advanced rules and default rules.
Rules-of-thumb are a set of guidelines that, when applied to a SQL statement, improve the syntax of the statement and make it more efficient. These guidelines have been developed over a number of years by the database experts at Oracle Corporation.
You can use Oracle SQL Analyze to apply rules-of-thumb to your SQL statements by analyzing them through the Tuning Wizard.
To learn more about rules-of-thumb and how they're applied, see "Applying Rules-of-Thumb".
The database name Oracle Expert will use for the Database object and the Instance object on the View/Edit page of the tuning session window
A setting that determines the categories of database tuning Oracle Expert will address for a given tuning session. You choose the scope when you create a new tuning session, and you can modify the scope for a tuning session. You can choose one or more tuning categories from one or more of the tuning types: instance tuning, application tuning, and structure tuning. See also instance tuning session, application tuning session, and structure tuning session.
See tuning session.
A report that describes all the collected data for an Oracle Expert tuning session. You can read this report before starting an analysis of the collected data to confirm that you provided Oracle Expert with all the data it expects for your tuning session.
These instance parameters affect the total size of the instance's System Global Area (SGA). The appropriate setting of these parameters results in efficient utilization of memory and prevents reparsing SQL statements except when necessary. Examples of these parameters include the db_block_buffers and shared_pool_reserved_size parameters. These parameters can be tuned as part of an instance tuning session. See also instance tuning session, I/O parameters, parallel query parameters, sort parameters, Oracle Parallel Server parameters, and operating system-specific parameters.
A category of structure tuning. Oracle Expert makes recommendations for sizing various segments in order to improve space usage and performance. See also placement.
These parameters influence how the Oracle Server performs sort operations on behalf of the user. Examples of these parameters include the sort_direct_write and sort_area_retained_size parameters. These parameters can be selected as a category to be tuned as part of an instance tuning session. See also instance tuning session, I/O parameters, parallel query parameters, sort parameters, Oracle Parallel Server parameters, and operating system-specific parameters.
The complete set of application SQL data and statistics that are executed within the database environment. The statements can be added to the SQL History from a variety of sources, including the SQL cache, Oracle Trace, and .XDL files. The SQL History provides a consistent source of SQL data for all of the Tuning Pack applications that make tuning recommendations. The SQL History also prevents the Tuning Pack applications from impacting the production database environment when SQL information is needed for an analysis.
SQL objects, located in the Navigator window, represent specific SQL statements that you can tune against the database session to which they are connected.
SQL objects may be created, copied or deleted. The statements within them may be edited in a number of ways.
Note, however, that the objects become read-only after an explain plan is generated for the statement. To proceed with further editing, you must create a copy of the node using SQL=>Create Like command.
One of the categories of application tuning. The Oracle Server maintains only one copy of a distinct SQL statement within the library cache to maximize memory and minimize redundant parsing and validating. To effectively use this feature, you must write duplicate SQL statements using identical structure and form (two statements are considered the same only when they match character for character, including spaces and punctuation). Oracle Expert compares your workload statements to determine if any can be rewritten to take advantage of the cache behavior and reports its findings. See also access methods.
One type of data warehouse design is known as a "star" schema. This typically consists of one or more very large "fact" tables and a number of much smaller "dimension" or reference tables. A star query is one that joins several of the dimension tables, usually by predicates in the query, to one of the fact tables.
Oracle cost-based optimization recognizes star queries and generates efficient execution plans for them; indeed, you must use cost-based optimization to get efficient star query execution. To enable cost-based optimization, simply ANALYZE your tables and be sure that the Optimizer mode is set to its default value of CHOOSE.
The star transformation is a cost-based query transformation aimed at executing star queries efficiently. Whereas the star optimization works well for schemas with a small number of dimensions and dense fact tables, the star transformation may be considered as an alternative if any of the following holds true:
The star transformation does not rely on computing a Cartesian product of the dimension tables, which makes it better suited for cases where fact table sparsity and/or a large number of dimensions would lead to a large Cartesian product with few rows having actual matches in the fact table. In addition, rather than relying on concatenated indexes, the star transformation is based on combining bitmap indexes on individual fact table columns.
The transformation can thus choose to combine indexes corresponding precisely to the constrained dimensions. There is no need to create many concatenated indexes where the different column orders match different patterns of constrained dimensions in different queries.
The star transformation works by generating new subqueries that can be used to drive a bitmap index access path for the fact table.
Stored Outlines are used to preserve execution plans Oracle's Plan Stability functionality. Oracle can create a public or private stored outline for one or all SQL statements. The optimizer then generates equivalent execution plans from the outlines when you enable the use of stored outlines.
Structured Query Language, or SQL, is an industry-standard programming language developed specifically to support access to relational databases.
The general characteristics of the SQL language are:
Oracle7 and Oracle8 databases support SQL and PL/SQL, a supplementary implementation of SQL. Oracle SQL Analyze currently supports only standard SQL.
Data about the machine on which an instance runs. You need to provide Oracle Expert with data such as the memory allocation and CPU utilization of the machine on which the instance runs.
The System Global Area (SGA) is a shared memory region that contains data and control information for one Oracle instance. An SGA and the Oracle background processes constitute an Oracle instance.
Oracle allocates the system global area when an instance starts and deallocates it when the instance shuts down. Each instance has its own system global area. Users currently connected to an Oracle Server share the data in the system global area. For optimal performance, the entire system global area should be as large as possible (while still fitting in real memory) to store as much data in memory as possible and minimize disk I/O.
The information stored within the system global area is divided into several types of memory structures, including the database buffers, redo log buffer, and the shared pool. These areas have fixed sizes and are created during instance startup.
The number of rows in a table.
TopSQL is an integrated function of Oracle SQL Analyze that lets you measure the resources a SQL statement consumes. Using these statistics, you can determine which statements consume the most resources and select them for tuning.
TopSQL takes all the SQL statements and statistics from V$SQLAREA. The V$SQLAREA view lists statistics on shared SQL areas and contains one row per SQL string. It provides statistics on SQL statements that are in memory, parsed, and ready for execution or that might have been executed already.
The TopSQL node, located in the Navigator window, represents the TopSQL session available for the database object it is connected to.
A separate TopSQL object is created each time you link to another database.
One of the specific tuning areas (such as SGA or sizing) that Oracle Expert addresses for a tuning type. Two or more tuning categories are associated with each Oracle Expert tuning type. See also tuning type.
The framework within which Oracle Expert performs its tuning activities. The steps in a tuning session include creating the session, specifying the session's tuning scope, collecting data, editing data, analyzing data, generating recommendations, validating recommendations, and implementing recommendations.
One of the general areas of the database environment that Oracle Expert can tune. The three tuning types are instance tuning, application tuning, and structure tuning. Two or more tuning categories are associated with each tuning type. See also tuning category.
See tuning type.
Oracle TopSQL uses information from V$SQLAREA to determine the resources used by a specific SQL statement.
V$SQLAREA is a view of an instance as a whole, and records statistics either since the startup of the instance or the current values, which remain constant until altered by some need to reallocate SGA space. Statistics are available for SQL statements that are in memory, parsed, and ready for execution.
The process of verifying, for a collected object, that other objects referenced by or dependent upon the collected object have also been collected. If other objects referenced by or dependent upon a collected object have not been collected, the collected object is marked as invalid. The international No symbol is used to mark invalid objects on the View/Edit page of the tuning session window.
A tuning session in which you modify the values of collected data such as disks, memory, CPU, or cardinality to a value you expect will be correct in the future. This type of tuning allows you to determine the effect of changes on a database before the changes are actually made.
Data that describes to Oracle Expert the nature, frequency, and importance of SQL statements that access a database. You should collect a representative workload for each tuning session that requires a workload. Workload data is displayed hierarchically on the View/Edit page of the tuning session window. See also representative workload, Application, and Request.
An Oracle Expert Definition Language file. Oracle Expert creates an .XDL file when it exports database, instance, schema, environment, or workload data.