In Oracle, whenever a query is made, a component called the optimizer makes a decision as to how to best retrieve the return query set. Oracle supports two optimizers: the Rule-Based Optimizer (RBO), the “original” optimizer, and the Cost-Based Optimizer (CBO), first introduced in Oracle7. The CBO has been enhanced greatly since and is now the optimizer-of-choice for Oracle.
Oracle provides full CBO support for the first time in Oracle’s Siebel 7.7. Enterprise Application. Oracle’s decision to support CBO was made for several reasons:
In the vast majority of cases, performance for the Siebel application with CBO will meet or exceed what it would be under RBO, ceteris paribus (i.e. under similar architecture/network topologies, hardware configurations, data volumes/distributions, storage characteristics, etc.). Of course, this assumes that best practices for implementing CBO, such as collecting statistics on data objects (see below), are followed. NOTE: if best practices regarding collection of data statistics for CBO are not followed, performance will fall short, and in some cases far short, of potential performance benchmarks.
CBO allows for support of performant case-insensitive sorts and queries in Siebel. Performant case-insensitive sort/query requires Function-Based Indexes, which are only supported by the CBO, and not the RBO.
The CBO supports more functionality than the RBO. The below list shows some of the features that are only available with the CBO:
o Partitioned tables and indexes
o Index-organized tables
o Reverse key indexes
o Function-based indexes
o SAMPLE clauses in a SELECT statement
o Parallel query and parallel DML
o Star transformations and star joins
o Extensible optimizer
o Query rewrite with materialized views
o Enterprise manager progress meter
o Hash joins
o Bitmap indexes and bitmap join indexes
o Index skip scans
o CPU speed as an input to query optimization
There is less risk of adverse performance impacts when adding new indexes.
For example, RBO’s rigid processing rules often choose a new custom index over an existing more optimal index, based on similar columns. CBO is capable of selecting the best index for the job based on information received from table/index statistics. Siebel often requires the creation of new indexes in order to tune SQL generated by application customization. Under CBO, this process becomes easier, carries less risk of adverse effects and is more predictable.
It is Oracle’s recommended approach. Per Oracle’s Database Performance Tuning Guide and Reference: “In general, use the cost-based approach. Oracle Corporation is continually improving the CBO and new features require CBO. Note: Oracle Corporation strongly advises the use of cost-based optimization. The rule-based optimization is available for backward compatibility with legacy applications and will be deprecated in a future release.”
© 2006, www.oracledatabase12g.com. 版权所有.文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.
相关文章 | Related posts:
Oracle CBO and Siebel Business Applications
Applies to:
Siebel System Software – Version: 7.7.2 [18325] to 8.0 [20405] – Release: V7 to V8
Information in this document applies to any platform.
Area(s):Installation, System Administration
Release(s):V7 (Enterprise), V7 (Professional), V8 (Enterprise), V8 (Professional)
Database(s):Oracle
App Server OS(s):All Supported Platforms
Latest release tested against:V8 (Enterprise)
Keywords:CBO, RBO, tuning, performance, 9i, parameter, statistics, cost, choose, optimizer
This document was previously published as Siebel Technical Note 582.
IMPORTANT NOTE: This document is no longer being updated. Ultimate reference for Oracle performance and tuning for Siebel Application is the White paper:
Performance Tuning Guide for Siebel CRM Application on Oracle (Doc ID 781927.1)
Purpose
Oracle Cost Based Optimizer (CBO) is supported for Siebel versions 7.7.x, 7.8.x, and 8.0 when deployed with Oracle RDBMS 9i and 10g.
For Siebel version 7.5.x or earlier, the only optimizer mode supported is RULE for any of the database versions supported by the Siebel versions.
Siebel Version
Oracle RDBMS Version
Optimizer Mode
Init.ora parameter OPTIMIZER_MODE
7.5.x or higher
8.1.7.x (where x >= 3)
RULE
RULE
9.0.1.3
RULE
RULE
9.2.0.x (where x >= 2)
RULE
RULE
7.7.x or higher
8.1.7.x (where x >= 3)
RULE
RULE
9.2.0.x (where x >= 4)
COST
CHOOSE *
7.7.2.5 or higher
10.1.0.x (where x>= 4), 10.2.0.x.
COST
ALL_ROWS
7.8.x or higher
9.2.0. x (where x>=4)
COST
CHOOSE
10.1.0.x (where x>=3), 10.2.0.x.
COST
ALL_ROWS
8.0
10.2.0.2 and above
COST
ALL_ROWS
(*) except during the Upgrade process on 9i, when the OPTIMIZER_MODE should be set to RULE as described in the Document 477789.1 document.
The purpose of this document is to cover the main aspects of the Cost Based Optimizer mode and its relation with Siebel versions 7.7, 7.8, and 8.0.
Scope and Application
This document is informational and intended for any user.
Oracle CBO and Siebel Business Applications
A query optimizer determines the best Execution Path for performing each query. The Oracle Rule Based Optimization (RBO) method chooses its plans based on a set of fixed rules. While the Cost Based Optimization method leverages a costing model based on statistics, which evaluates all of the different access and join methods for the specific query and uses the most efficient one.
RBO enumerates a deterministic, ordered (fixed rank) set of plans based on a pre-defined set of rules and always picks the first applicable plan in the order. For example, if you have a query of the form:
SELECT * from EMP where EMPNO > 50
and if there is an index on column EMPNO, then one of the RBO’s rules specifies that this query will always be executed using the index. The behavior of the RBO is entirely fixed; the execution plan for the sample query remains the same regardless of whether the EMP table has 10 rows or 10 million rows, and regardless of whether the “EMPNO > 50” predicate will return 2 rows or 2 million rows.
On the other hand, the cost-based optimization strategy generates multiple execution plans for a given query, with each having an estimated cost computed for each plan. The query optimizer chooses the plan with the most efficient cost (for example, the highest performance and lowest cost). The CBO requires accurate statistics in order to deliver good query performance. Hence, it is crucial that the statistics the CBO uses are available and accurately maintained.
Preparing and Administrating the Siebel Database for CBO
When using Oracle’s Cost Based Optimization with the Siebel database, there are some requirements that should be followed regarding the database parameters configuration and the Optimizer statistics generation on tables, columns and indexes, as specified below:
Database Parameters
For Oracle 10g, OPTIMIZER_MODE should be set to ALL_ROWS which is the default value. For Oracle 9i, OPTIMIZER_MODE should be set to CHOOSE unless you are performing an upgrade. Besides setting the OPTIMIZER_MODE to CHOOSE or ALL_ROWS, about 60 other parameters affect the generation of the query plan (46 of them are hidden parameters). Some of the visible parameters are listed below. The details of these parameters are not within the scope of this document; you may refer to the Oracle Database Reference Manual to get more information.
The following visible parameters must be set to their default values where applicable:
Oracle Parameter
9i Default Value
10g Default Value
HASH_JOIN_ENABLED
True
** Obsolete
OPTIMIZER_FEATURES_ENABLE
9.2.0
10.1.0 *
OPTIMIZER_INDEX_CACHING
0
0
OPTIMIZER_MODE
CHOOSE
ALL_ROWS
PARTITION_VIEW_ENABLED
False
** Obsolete
QUERY_REWRITE_INTEGRITY
Enforced
Enforced
STAR_TRANSFORMATION_ENABLED
False
False
* It is always a good idea to set the OPTIMIZER_FEATURES_ENABLE = to get full benefit of a series of optimizer feature supported by Oracle DB version. Hence, if your db version is already 10.2.x, then you can set the OPTIMIZER_FEATURES_ENABLE=10.2.x.
In addition, for the Siebel application to work effectively and efficiently, the following parameters need to be set to the recommended values other than default values.
Oracle Parameter
Description
9i Default Value
10g Default Value
Recommended Value
OPTIMIZER_DYNAMIC_SAMPLING
This parameter controls the level of dynamic sampling performed by the optimizer.
1
2
1
OPTIMIZER_MAX_PERMUTATIONS
This parameter restricts the number of permutations of the tables the optimizer will consider in queries with joins. A higher value result in higher parse time, a lower value carries the possibility of overlooking a good plan.
NOTE: Starting with Oracle Database 10g, this parameter has been made obsolete. Refer to the Oracle documentation for further information.
2000
** Obsolete
100
OPTIMIZER_INDEX_COST_ADJ
Controls the access path selection to be more or less index friendly.
100
100
1
QUERY_REWRITE_ENABLED
Enable or disable query rewriting globally for the database
False
True
False
(**) Do not automatically alter the values of these parameters which have been deprecated to hidden parameters in 10g, based on the recommendations for visible parameters in version 9i.
The parameters below should be set according to the information documented in the following Siebel Bookshelf references:
* Siebel Installation Guide for Microsoft Windows Version 8.0 > Configuring the RDBMS > Configuring an Oracle Database for Siebel Applications > Guidelines for Configuring Settings in the init.ora File.
* Siebel Installation Guide for UNIX Version 8.0 > Configuring the RDBMS > Configuring an Oracle Database for Siebel Applications > Guidelines for Configuring Settings in the init.ora File.
* Siebel Installation Guide for Microsoft Windows: Servers, Mobile Web Clients, Tools Version 7.8 > Configuring the RDBMS > Configuring Oracle for Siebel Applications > Guidelines for Configuring Settings in the init.ora File.
* Siebel Installation Guide for UNIX: Servers, Mobile Web Clients, Tools Version 7.8 > Configuring the RDBMS > Configuring Oracle for Siebel Applications > Guidelines for Configuring Settings in the init.ora File.
* Siebel Installation Guide for Microsoft Windows: Servers, Mobile Web Clients, Tools Version 7.7 > Guidelines for Configuring the RDBMS > Configuring Oracle for Siebel Applications.
* Siebel Installation Guide for UNIX: Servers, Mobile Web Clients, Tools Version 7.7 > Guidelines for Configuring the RDBMS > Configuring Oracle for Siebel Applications.
Oracle Parameter
Description
Recommended
Value
DB_FILE_MULTIBLOCK_READ_COUNT
The database buffer cache parameter dictates the number of data blocks read in a single Oracle I/O operation during a table scan.
For most implementations, this value should be set between 16 and 32 blocks and adjusted as necessary. You may want to set an initial value of 32.
SORT_AREA_SIZE
This value is specified in bytes, and may be adjusted according to the number of users, the amount of RAM available, and the size of sorted queries.
Set PGA_AGGREGATE_TARGET instead. When not setting PGA_AGGREGATE_TARGET – You should start with an initial value of 1,000,000 (1 MB). Optimal performance can be achieved for the Dictionary Managed TEMP tablespaces if the extents are uniform and a multiple of the SORT_AREA_SIZE.
SORT_AREA_RETAINED_SIZE
Specifies (in bytes) the maximum amount of the user global area (UGA) memory retained after a sort run completes. The retained size controls the size of the read buffer, which Oracle uses to maintain a portion of the sort in memory.
Set PGA_AGGREGATE_TARGET instead. When not setting PGA_AGGREGATE_TARGET – Derived from SORT_AREA_SIZE.
The following related parameters should be set by a trained DBA and according to the information in the applicable Oracle database documentation and requirements for your implementation. General guidance on these related settings is provided below:
Oracle Parameter
Description
General Guidance
PGA_AGGREGATE_TARGET
Specifies the target aggregate PGA memory available to all server processes attached to the instance.
For most production implementations with a substantial number of users, this value should be set to 1 GB or greater. Monitor PGA target advisory views for additional guidance.
STATISTICS_LEVEL
All the advisories in Oracle9i Release 2 including the Buffer Cache Advisor are controlled by a newly introduced parameter STATISTICS_LEVEL.
TYPICAL in production and ALL in TEST environments.
HASH_AREA_SIZE
Relevant to parallel execution operations and to the query portion of DML or DDL statements. It specifies the maximum amount of memory, in bytes, to be used for hash joins.
Set PGA_AGGREGATE_TARGET instead. When not setting PGA_AGGREGATE_TARGET – Derived: 2 * SORT_AREA_SIZE
WORKAREA_SIZE_POLICY
Specifies the policy for sizing work areas. This parameter controls the mode in which working areas are tuned.
If PGA_AGGREGATE_TARGET is set, then AUTO. If PGA_AGGREGATE_TARGET is not set, then MANUAL.
Optimizer Statistics
Optimizer statistics on tables, columns and indexes are the single most important factor that drives the generation of optimal query plans. As such, it is important to ensure that accurate optimizer statistics are collected and made available to the query optimizer.
Collecting Optimizer Statistics
Oracle provides two ways for collecting optimizer statistics:
1. Using the ANALYZE command
2. Using a PL/SQL package DBMS_STATS
Oracle Corporation highly recommends using the DBMS_STATS package for optimizer statistics maintenance. This package has several options for collecting optimizer statistics. Each one of them has advantages and disadvantages. However, it should be mentioned that the more accurate optimizer statistics that are maintained will typically result in better execution plans.
The examples below are only to illustrate some usages of DBMS_STATS to collect and delete statistics; do not use it as a guideline. For more details, please refer to the Oracle Database Performance and Tuning Guide and Reference, and Supplied PL/SQL Packages and Types Reference manuals.
* Collects the optimizer statistics for the table, columns, indexes and histograms for all columns. Recommend this method when the initial optimizer statistics collection window is enough (depending on the table size it may take more time to complete) and space is not a constraint.
EXECUTE DBMS_STATS.GATHER_TABLE_STATS(
ownname => ‘
tabname => ‘
method_opt => ‘FOR ALL COLUMNS SIZE AUTO’,
granularity => ‘ALL’,
cascade => TRUE,
degree => DBMS_STATS.DEFAULT_DEGREE)
* Collects the optimizer statistics for the table, indexed columns, indexes and histograms for all indexes columns:
EXECUTE DBMS_STATS.GATHER_TABLE_STATS(
ownname => ‘
tabname => ‘
method_opt => ‘FOR ALL INDEXED COLUMNS’,
granularity => ‘ALL’,
cascade => TRUE,
degree => DBMS_STATS.DEFAULT_DEGREE)
* Collects the optimizer statistics for the table, columns, indexes and histograms for columns where the data shape is skewed. Only collects histograms for skewed column. In general it is slower to execute therefore it would not be suitable when the optimizer statistic collection window is short.
EXECUTE DBMS_STATS.GATHER_TABLE_STATS(
ownname => ‘
tabname => ‘
method_opt => ‘FOR ALL COLUMNS SIZE SKEWONLY’,
granularity => ‘ALL’,
cascade => TRUE,
degree => DBMS_STATS.DEFAULT_DEGREE)
* Collects only the optimizer statistics for the index. Useful when you add a new index.
EXECUTE DBMS_STATS.GATHER_INDEX_STATS(‘, ‘)
ownname => ‘
indname => ‘
Deleting Optimizer Statistics
* Deletes the optimizer statistics collected for the tables, all columns, indexes associated with the table:
EXECUTE DBMS_STATS.DELETE_TABLE_STATS(
ownname => ‘
tabname => ‘
* Deletes the optimizer statistics collected for the index:
EXECUTE DBMS_STATS.DELETE_INDEX_STATS(‘, ’);
ownname => ‘
indname => ‘
Verifying Optimizer Statistics
The followings Optimizer statistics are used by the Cost-based Optimizer in Oracle RDBMS 9i and 10g. Please refer to the Oracle Database Performance and Tuning Guide and Reference for more information.
* Table (DBA_TABLES, ALL_TABLES, USER_TABLES)
o Number of row
o Number of blocks
o Average row length
* Column (DBA_TAB_COLUMNS, ALL_TAB_COLUMNS, USER_TAB_COLUMNS, DBA_HISTOGRAMS, ALL_HISTOGRAMS, USER_HISTOGRAMS)
o Number of distinct value
o Number of NULL
o Density
o Data distribution (Histograms)
* Index (DBA_INDEXES, ALL_INDEXES, USER_INDEXES)
o Number of leaf block
o Distinct Key
o Levels
o Clustering factor
To verify the optimizer statistics, you may use the STAT.SQL script provided below:
Attachment 478028.1:1
This script can be run from the Oracle SQL*Plus utility. You need to provide the table name after the file. The output lists the optimizer statistics and also let you know when it was last collected. This helps you to understand the execution plan of a SQL statement and decide whether it is time to re-gather the statistics again.
Example:
SQL> @stat s_party
Table ….
TABNAME LAST_ANALYZED NUM_ROWS BLOCKS AVG_ROW_LEN SAMPLE_SIZE
——————– ——————- ———- ———- ———– ———–
S_PARTY 01-23-2004 11:58:25 5360604 32602 100 5360604
Columns(s) ….
COLUMN_NAME LAST_ANALYZED NUM_DISTINCT NUM_NULLS DENSITY NUM_BUCKETS
—————————— ——————- ———— ———- ————— ———–
ROW_ID 01-23-2004 11:58:25 5360604 0 .00000019 75
CREATED
CREATED_BY
LAST_UPD
LAST_UPD_BY
MODIFICATION_NUM
CONFLICT_ID 01-23-2004 11:58:25 67931 0 .00000019 1
PARTY_TYPE_CD 01-23-2004 11:58:25 9 0 .00000009 8
PARTY_UID 01-23-2004 11:58:25 5360604 0 .00000019 75
ROOT_PARTY_FLG 01-23-2004 11:58:26 2 0 .00000009 1
GROUP_TYPE_CD 01-23-2004 11:58:26 18 145776 .00000010 17
NAME 01-23-2004 11:58:26 4654599 138347 .00000111 75
PAR_PARTY_ID 01-23-2004 11:58:26 15906 5344562 .00007114 75
Index(s) ….
INDNAME LAST_ANALYZED LFBLK DKEYS ALVLBLK AVGDBLK CF BLEVEL
—————— ——————- ———- ———- ———- ———- ———- ———-
S_PARTY_F1 01-23-2004 11:58:32 23 15906 1 1 1030 1
S_PARTY_M1 01-23-2004 11:58:38 4837 2 2418 18798 37597 2
S_PARTY_M2 01-23-2004 11:59:31 13232 4679585 1 1 5475889 2
S_PARTY_M3 01-23-2004 12:00:21 13716 4679801 1 1 5059433 2
S_PARTY_M4 01-23-2004 12:00:59 9387 4679568 1 1 5233052 2
S_PARTY_P1 01-23-2004 11:58:31 6570 5468509 1 1 254834 2
S_PARTY_U1 01-23-2004 12:01:04 11084 5460497 1 1 110498
To verify the histograms, you may use the COLDIST.SQL script provided below:
Attachment 478028.1:2
This script can also be run from the SQL*Plus utility. You need to provide the table name and column name to get the histograms.
The histograms partition the values of the columns into bands, so that all column values in a band fall within the same range. It is similar to the column distributions on DB2/UDB database. It helps the optimizer to choose a better plan only when the data is highly skewed and the SQL statement uses a literal.
Example:
SQL> @coldist S_PARTY.PARTY_TYPE_CD
Histograms ….
ENDPOINT_NUMBER ENDPOINT_VALUE
————— ————–
31 3.3952E+35
73803 3.7611E+35
1843632 4.1251E+35
5309265 4.1744E+35
5360486 4.1764E+35
5360493 4.1770E+35
5360507 4.3334E+35
5360521 4.3334E+35
5360604 4.4369E+35
Changes Made in Siebel 7.7, Siebel 7.8 and Siebel 8.0 for Supporting CBO
Some changes were made in the Siebel application to support Oracle CBO in Siebel 7.7, Siebel 7.8 and Siebel 8.0. The changes were mainly for the first rows optimization for the client queries. Typically the client queries fetch a screen full of rows, keep the cursor open and fetch more rows when the user presses next record set or next record on the last row. This makes it important to optimize the query plan for returning the first few rows quickly.
For better performance in Siebel versions 7.8 and 8.0 connecting to Oracle 10.1.x or 10.2.x, with optimizer mode set as CBO, it has been observed that SQL query performance is satisfactory with the following two parameter settings in the init.ora file:
* _b_tree_bitmap_plans = FALSE
* _no_or_expansion = FALSE
The Siebel database connector makes the following session level changes, when the SQL style is set to Oracle CBO:
* Sets the optimization mode to the first_rows_10: ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_10
* Disables hash join: ALTER SESSION SET HASH_JOIN_ENABLED = FALSE, for Oracle 9i. ALTER SESSION SET _HASH_JOIN_ENABLED = FALSE, for Oracle 10g
* Disables sort-merge: ALTER SESSION SET _OPTIMIZER_SORTMERGE_JOIN_ENABLED = FALSE
* Enables sanity check for join using two columns. These are done by issuing the following command: ALTER SESSION SET _OPTIMIZER_JOIN_SEL_SANITY_CHECK = TRUE
Whenever the query fetches all rows, a hint /*+ all_row */ gets appended to the SQL statement after the SELECT clause, to change the optimization technique to all rows.
NOTE: Parameter HASH_JOIN_ENABLED has been deprecated to the hidden parameter _HASH_JOIN_ENABLED in Oracle 10g, which causes the following message to appear in the database alert log file:
08:59:06 2005 ALTER SESSION SET hash_join_enabled specifies an obsolete parameter
Change Request 12-R5WXCB has been logged to address this product defect by changing the parameter HASH_JOIN_ENABLED to the hidden parameter _HASH_JOIN_ENABLED preventing the alert log error message. The alert log message regarding the deprecated parameter can be safely ignored in situations where it is generated. The behavior has been fixed in Siebel versions 8.0, 7.8.2.2 and 7.7.2.5.
Generating Execution Plans for Oracle CBO
Applies to:
Siebel System Software – Version: 7.7.2 [18325] to – Release: V7 to V8
Information in this document applies to any platform.
Area(s):Siebel.CRM.System Administration
Release(s):V7 (Enterprise), V8 (Enterprise)
Database(s):Oracle
App Server OS(s):GENERIC (All Platforms)
Latest release tested against:V8 (Enterprise)
Purpose
Starting with Siebel version 7.7, if using Oracle9i or above, the Database Server must run with Cost-Based Optimizer (CBO).
The way an Application Object Manager (AOM) works with Oracle CBO is a bit different from the Rule-Based Optimizer (RBO) approach, so there are some steps to follow in order to get an accurate Execution Plan.
Scope and Application
This document is informational and intended for any user.
When using Oracle CBO, a Siebel AOM sets some database parameters at session level which influence the behavior of the Optimizer when parsing a SQL statement, and makes use of Bind Variables in order to take advantage of SQL sharing.
This is considered by the CBO when determining the best Execution Plan for each statement, and must be taken into consideration when tuning the performance of a SQL statement, so that you can see the exact access path used by Oracle within a Siebel session.
Generating Execution Plans for Oracle CBO
The following steps must be executed on the same database used by Siebel Business Application, because each database has different statistics, due to different data distribution, which affects the Execution Plan.
1. Identifying the Query
In order to identify the query that is impacting the application performance, increase all SQL-related Event Log Levels for your AOM to 4, by running the following on Server Manager Command Line:
C:\> srvrmgr /g /e /s /u /p
bind variables with names bind variables :b
srvrmg> change evtloglvl SQLParseAndExecute=4 for comp
srvrmg> change evtloglvl ObjMgrSqlLog=4 for comp
srvrmg> change evtloglvl SQLProfiling=4 for comp
srvrmg> change evtloglvl SQLSummary=4 for comp
srvrmg> change evtloglvl SQLSlowQuery=4 for comp
Then, reproduce the performance degradation behavior on the Siebel Web Client, locate the appropriate log file from %SIEBEL_ROOT%\siebsrvr\log foder, and identify the longest query by comparing the Execute Times contained in the log file:
***** SQL Statement Execute Time for SQL Cursor with ID xxx: 0.000 seconds *****
Right before this entry, you will find the complete SQL statement and the list of Bind Variable values, if any:
ObjMgrSqlLog Detail 4 … SELECT statement with ID: xxx
SELECT
…
ObjMgrSqlLog Detail 4 … Bind variable 1: SADMIN
2. Identifying Bind Variables type
If you find columns in the WHERE clause being assigned values in the format “:X”, where “X” is a number starting with 1, these are Bind Variables as the example below:
T6.LOGIN = :1
You need to describe the corresponding table to see the data type and size of this column.
In this example, considering that T6 is the qualifier used for S_USER table, you would see something like this:
SQL> desc S_USER
…
LOGIN NOT NULL VARCHAR2(50 CHAR)
…
3. Creating the PLAN_TABLE table
In order to investigate this query, you are recommended to log in as a database user to whom the DBA role has been granted, which must not be SYS, and must have a PLAN_TABLE table created in its schema, so that the Execution Plan can be generated.
In order to create the PLAN_TABLE table, log into SQL*Plus with this DBA user, and run UTLXPLAN.SQL script, which is located under the Database Server %ORACLE_HOME%\rdbms\admin folder:
C:\> sqlplus/
SQL> @?\rdbms\admin\utlxplan.sql
4. Editing the script with the information gathered
Case A – use this script when you want to retrieve the execution plan only – in this case the Oracle Optimizer will output the execution plan without running the sql itself
By coding the bind variables in the script below, the optimizer will parse and execute the given SQL with the same execution plan as SIEBEL would. The SQL*PLus script must also include the correct Siebel session hints. Script should be run from SQL*Plus to ensure correct explain plans are obtained.
You need to edit the script by specifying the bind variables types found in (2), the bind variables values found in (1), and the SQL statement itself.
– *********************************************************************
– SCRIPT: probl.sql Provided by SIEBEL ENGINEERING
– *********************************************************************
spool probl.log
/* use for tuning purposes
– ******* REQUIRED SIEBEL HINTS, SET AT SESSION LEVEL *****************
alter session set optimizer_mode = first_rows_10
/
alter session set “_hash_join_enabled” = FALSE — for Oracle 9i this would not be a hidden parameter
/
alter session set “_optimizer_sortmerge_join_enabled” = false
/
alter session set “_optimizer_join_sel_sanity_check” = true
/
set echo off
– declare bind variables and set them
variable b1 varchar2(100);
variable b2 varchar2(100);
begin
:b1 :=’1-65-48′;
:b2 :=’05/08/2008 18:05:43′;
end;
/
– get explain plan
bind variables with names bind variables :b‘) END ||‘) END
set timing on
set linesize 3000
DELETE plan_table
WHERE statement_id = ‘prob’
/
commit
/
alter session set events ’10053 trace name context forever, level 1′
–/
set echo on
EXPLAIN PLAN
SET statement_id = ‘prob’
FOR
SELECT
/
set echo off
SELECT decode(id,0,”,lpad(‘ ‘,2*(level-1))||level||’.'||position)||’ ‘||
operation||’ ‘||options||’ ‘||object_name||’ ‘||
decode(object_name, null, null, decode(object_instance,null,null,’-T’||object_instance))||’ ‘||
object_type ||’ ‘||decode(id,0, ‘Cost =’||decode(position,null,’RBO’,position))||
decode(cost,null,null, ‘ (Cost=’||Cost||’ Rows=’||Cardinality||’ Bytes=’||Bytes||’)')||
CASE WHEN access_predicates IS NULL THEN ”
ELSE (‘
CASE WHEN filter_predicates IS NULL THEN ”
ELSE (‘
as “Query Plan”
FROM plan_table
connect by prior id = parent_id
and statement_id = ‘prob’
start with id = 0 and statement_id = ‘prob’
order siblings by id, position
/
spool off
Case B – use this script when there is a need of collecting time statisitcs for analysis of the performance issue along with the execution plan – use when you want to generate a trace file with time statistics; note that in this case the sql with performance issue will be executed and hence it can take some time to execute
– *********************************************************************
– SCRIPT: probl2.sql
– *********************************************************************
spool probl2.log
/* use for tuning purposes
– ******* REQUIRED SIEBEL HINTS, SET AT SESSION LEVEL *****************
alter session set optimizer_mode = first_rows_10
/
alter session set “_hash_join_enabled” = FALSE — for Oracle 9i this would not be a hidden parameter
/
alter session set “_optimizer_sortmerge_join_enabled” = false
/
alter session set “_optimizer_join_sel_sanity_check” = true
/
set echo off
– declare bind variables and set them
variable b1 varchar2(100);
variable b2 varchar2(100);
begin
:b1 :=’1-65-48′;
:b2 :=’05/08/2008 18:05:43′;
end;
/
alter session set timed_statistics=true;
/
alter session set events ’10046 trace name context forever, level 12′
/
alter session set events ’10046 trace name context off’
–/
5. Formatting the SQL Trace file generated for case 4.B
Go to the folder specified by USER_DUMP_DEST parameter (usually %ORACLE_BASE%\admin\%ORACLE_SID%\udump) and locate the most recent trace file, whose filename is in format %ORACLE_SID%_ora_.trc, where is the Operating System Process Identifier (OS PID) of your Database Session Server Process, which can be found by running the following query:
NOTE: There are some occasions where in order to reproduce the performance issue from sql plus there is a need of setting the _optim_peek_user_binds variable to false. This is related to the the fact that bind peek is removed in some versions. Refer to Doc ID 781927.1 to check which versions the bind peek is removed or not.
SQL> SELECT DISTINCT p.spid
2 FROM v$process p, v$session s, v$mystat m
3 WHERE s.paddr = p.addr
4 AND s.sid = m.sid;
Run the following at your Database Server Operating System Command Prompt to format the trace file:
C:\> tkprof
Note that the and
arguments specified here should match the username and password of the DBA user logged into SQL*Plus to generate the SQL Trace file, and must not be SYS, because you are running TKPROF with the SYS option set to NO, which eliminates all SQL statements (recursive or not) run by user SYS.
The SQL Trace file used as input to TKPROF utility is also known as the raw trace file, while the output file is the TKPROF’d trace, or TKPROF formatted output.
This TKPROF output will contain the Timed Statistics Table, the Row Source Operations, the Execution Plan, and the Wait Events, which will help your Database Administrator in tuning this query and locating the bottleneck that is causing performance degradation on this specific SQL statement.