Oracle Optimizer: Moving to and working with CBO - Part 6

Tuesday Dec 23rd 2003 by Amar Kumar Padhi

Previous installments of this series have covered how the Cost Based Optimizer works and behaves. In part 6 of the series, learn how to assist the optimizer to do its job properly.

Previous installments of this series have covered how the Cost Based Optimizer works and behaves. We will now look at how to assist the optimizer to do its job properly.

11. Hints

Hints are exactly what it means--clues or directives that will assist the optimizer in choosing an execution plan. Developers know more about the data they are dealing with and Hints provides us with the option of guiding the Optimizer to do things in a certain way--the way we would like a statement to run.

By using hints, part of the Optimizer's decision-making job is done by us.

A few things to remember about Oracle Hints:

1. Hints are not orders but directives to the optimizer.

2. Hints are provided in comment format that is embedded in the query. A plus sign is required at the beginning, right after the comment delimiter, for the optimizer to accept it as a hint. No space is permitted between the comment delimiter and the plus sign.

/*+ <hint> */
--+ <hint>

3. Multiple hints can be provided in a single comment for a statement, each separated with spaces.

4. Hints are meant for DML statements: INSERT, UPDATE, DELETE and SELECT.

5. Hints are not case sensitive.

6. If a wrong or invalid hint is provided, the optimizer ignores it and continues with the execution of the statement. The optimizer will not notify the user about such hints.

7. If multiple hints are provided that conflict with each other, the optimizer will ignore the same.

8. Comments are directly inserted in the SQL statements. These should be used with caution, as the executions plans are likely to remain the same.

9. Hints are CBO features. Using them in RBO setup will force the queries to run in cost mode. The exception to this is the RULE hint that invokes the RBO for executing a statement.

10. Hints can be used to influence the mode of the optimizer, the access path, the join order, the join method used etc..; this is mentioned in detail below.

11. Hints can be local or global. Local hints are provided directly in the SQL statements. Use global hints instead of embedding hints inside a view; this is discussed below.

12. SQL statements that are executed directly may behave differently when executed from within PL/SQL. Make use of hints in such cases to achieve the required results.

13. Table hints can be provided with the table name. . If an alias name is provided, use it instead of the table name.

14. Avoid the use of schema name along with the table name in hints, even if they appear in the FROM clause. Using aliases is a safe bet.

15. As stated by Oracle Documentation, the use of hints involves extra code that must be managed, checked and controlled. Use hints to tame queries that execute with sub-optimal execution plans, but take care to provide the right access paths.

Listed below are some important hints that are widely used.

ALL_ROWS : for good throughput and resource utilization.
FIRST_ROWS : for good response time.
RULE : Use rule-based optimization rather than cost.
CHOOSE : Decide on rule or cost optimization based on the existence of statistics.
FULL : for doing a full table scan on the table.
HASH : hash scan, applies only for clustered tables (do not confuse with HASH join).
ROWID : table scan by rowid.
CLUSTER : cluster scan, applies only for clustered tables.
INDEX : index scan, specify the table and the index name.
INDEX_ASC : for range scan, scan index in ascending order of values.
INDEX_DESC : for range scan, scan index in descending order of values.
INDEX_JOIN : use index join as an access path. Two indexes could be joined to return the required values.
INDEX_FFS : perform a fast full scan on the index rather than on the table.
NO_INDEX : avoid the use of the specified index or all indexes.
AND_EQUAL : merge 2-5 single column index scans to arrive at the output.
INDEX_COMBINE : explicitly choose a bitmap access path, make use of bitmap indexes.
ORDERED : access and join tables in the order mentioned in the FROM clause, left to right.
USE_NL : use Nested Loop for joining tables.
USE_HASH : use Hash joins.
USE_MERGE : use Sort-Merge joins.

The optimizer hints ALL_ROWS, FIRST_ROWS, RULE and CHOOSE affect the Optimizer mode for executing the query, irrespective of what is set at session level.

From Oracle 9i, FIRST_ROWS can be optionally provided with the number of rows that should be returned, e.g. FIRST_ROWS(10). Please note that this hint is ignored for statements where Oracle needs to retrieve all rows for processing.

The RULE Hint causes the Optimizer to use rule based optimization to choose the execution path. This is an instant solution for queries that ran perfectly in RBO but have slowed down in CBO. If you have time constraints or are unable to resolve performance issues in CBO, you may consider using this hint, but my recommendation is that you come back to these queries later on and fine-tune it to run without the hint.

The access path hints for accessing data from table and indexes (FULL, INDEX_FFS, INDEX, NO_INDEX, INDEX_COMBINE, INDEX_JOIN etc.) affect the choice of access path by the optimizer. The table being affected could be specified along with the hint. If an alias name is provided, use it instead of the table name.

e.g.: improving a queries response time.

select /*+ first_rows */ trx_value
from   jnc_rtl_sales_iface_hdr
where  trx_no = 1211;

e.g.: Full table scan directive.

select /*+ full(a) */ a.shop_no, a.subinventory_code, b.item_code
from  jnc_shop_mapping a, jnc_rtl_sales_iface_hdr b
where b.shop_no = a.shop_no;

e.g.: Prevent use of some indexes.

select /*+ no_index(b jnc_catdtl_n2 jnc_catdtl_n1) */ *
from   jnc_purcat a, jnc_catdtl b
where  b.purcat = a.purcat
and    b.catnam = 'roger'
and    b.cat_id = 1;

e.g.: Direct use of indexes.

select /*+ index(a jnc_purcat_n1) full(b) */ *
from   jnc_purcat a, jnc_catdtl b
where  b.purcat = a.purcat
and    b.catnam = 'roger'
and    b.cat_id = 1;

Using Hints in Views

If a query with hints is selecting data from a complex view, chances are that the hints will be ignored if they do not propagate inside the view. Hints can be provided in the query itself on which the view is based. In general, it is not recommended to use hints in views as the data can be selected with different conditions. You may use it if the need arises, but beware of how the data will be selected from the view.

Oracle recommends the use of Global hints instead of embedding hints in the view itself. A global hint specifies the table name present in the view along with the access path to be chosen. For example:

/*+ index(emp_v.emp emp_pk */

In the above hint, EMP_V is the view name and EMP is the table name used in the view.

e.g.: Make use of an index on a table present inside the view.

SQL> create or replace view emp_v as
  2  select ename, dname
  3  from   emp, dept
  4  where  dept.deptno = emp.deptno;
View created.

SQL> select /*+ index(emp_v.emp emp_n2) */ * 
  2  from emp_v where dname = 'Dubai';

12. Statistics for SYS schema.

One issue that has always been in doubts is whether to generate statistics for SYS schema. Generating statistics for dictionary tables owned by SYS is not recommended in Oracle 8i. The dictionary views that reference the SYS tables execute efficiently with the Rule Based Optimizer.

You may generate statistics in Oracle 9i but you will have to evaluate this option for your setup. As per a note I came across, Oracle does not perform any regression testing with dictionary analyzed and there may be a possibility of performance issues. Oracle 10 and above would require statistic generation for SYS schema as RBO will be desupported.

Ways to deal with SYS related queries

1. Run your setup in CHOOSE mode. Generate statistics for application specific schemas. Avoid doing so for SYS schema.

This way, RBO will be used when accessing the dictionary and CBO when your application runs. The only catch is that CBO will resort to ALL_ROWS that may cause issues in OLTP systems. Setting the initialization parameters appropriately and extensive use of hints for application queries will stabilize the system in due course.

2. Run your setup in ALL_ROWS or FIRST_ROWS mode. Generate statistics for application specific schemas. Avoid doing so for SYS schema. Make extensive use of RULE hints for dictionary queries that are slow.

This way, Dictionary related queries will still be on RBO and the application can run on CBO. Some internal recursive queries may be affected on some setups, if the time taken is significant, do raise a TAR with Oracle support.

3. Run your setup in ALL_ROWS or FIRST_ROWS mode. Generate statistics for application specific schemas. Generate statistics for SYS schema! Make extensive use of RULE hint for dictionary queries that are slow, or allow the dictionary to run in Cost.

This is not recommended for Oracle 8i. Some internal recursive queries may run slow in this scenario also.

You may of course arrive at a strategy for your own setup. From my experience all the three are good options, depending on what will be appropriate for your setup.

Verifying SYS statistics

To verify if SYS schema has statistics, check the LAST_ANALYZED column for the dictionary tables.

If you are generating statistics at database level, chances are that SYS is also being analyzed. You may remove statistics for SYS by using the following option. This can be added to your auto-statistics generation process, if any.

exec dbms_stats.delete_schema_stats('SYS');

13. How to analyze execution plans in CBO?

DML performance tuning in CBO can be a challenging and interesting task. With many new dependencies present, one will have to do more than just check for the use of indexes. I present here a brief note on what developers should look at when writing or fine-tuning queries.

13.1 Basic checks

These are some basic things that one should check for when ever a performance issue is reported in CBO. You may add more checks as per your setup requirements.

1. Check the optimizer mode of the session in which the query fails.

select value from v$parameter where name = 'optimizer_mode';

2. Check if the concerned tables and indexes are analyzed.

select num_rows, last_analyzed, sample_size from dba_tables 
where table_name = 'GL_INTERFACE';
select index_name, num_rows, last_analyzed, sample_size from dba_indexes
where table_name = 'GL_INTERFACE';

3. Check if the degree is set to a value greater than 1, to invoke parallel processing on the tables.

select degree from dba_tables where table_name = 'GL_INTERFACE';

4. Check if SYS schema is analyzed/not analyzed, as per your setup requirements.

5. Check if the parameters affecting the optimizer are set as per the original setup specifications. You may store the original parameter settings in a table and compare it with V$PARAMETER to identify any changes done in the setup. This strategy is very important for maintaining multiple installations of the same application.

E.g.: Maintain a table called sys_recommended_syspar_values (or as per your standards) in SYS schema that has the original setup values. In case of any issues, comparing the present setup with the recommended setup can provide a clue as to what could have gone wrong.

jaJA>desc sys.sys_recommended_syspar_values
 Name                            Null?    Type
 ------------------------------- -------- ----
 NAME                                     VARCHAR2(64)
 VALUE                                    VARCHAR2(512)

jaJA>select, a.value cur, b.value orig
  2  from   v$parameter a, sys.sys_recommended_syspar_values b
  3  where (+) =
  4  and    b.value != a.value;

NAME                                              |CUR                 |ORIG
optimizer_mode                                    |FIRST_ROWS          |CHOOSE
hash_join_enabled                                 |FALSE               |TRUE

6. If you are not sure about what query is causing a performance issue, generate trace at session level or for the concerned processes and evaluate the resulting output file to identify resource intensive and time-consuming queries.

Also consider using DBMS_PROFILER; I have found this tool to be very handy to identify time-consuming lines of code.

13.2 Elements to evaluate in execution plans

With CBO, many new execution paths have been made available. The execution plan shows information about the access path chosen; this should be evaluated in terms of best throughput and response time.

Expensive operations can be identified by checking three crucial elements:

  1. Response time (mainly for OLTP systems)
  2. Cost
  3. Cardinality
  4. Bytes (optional)

Most often, if a portion of the execution plan shows an extremely high cost or cardinality, it may be a good place to start for tuning the query.

Response time is a crucial aspect for OLTP systems. Execution time does not form part of the Explain plan generated, and should be explicitly measured. One way of doing this is with the SQL*Plus TIMING option that shows the elapsed time between the firing of the query and the return of the results. The latest versions of SQL*Plus have timings displayed in "hour : minutes : seconds . milli-seconds" format.


Elapsed: 00:00:00.61

13.3 Generating the execution plans

Execution plan for individual queries can be derived by using the EXPLAIN PLAN option of Oracle. This can be invoked by using the EXPLAIN PLAN command or by enabling AUTOTRACE in SQL*Plus. A PLAN_TABLE is required for storing the execution plans; create it by using the UTLXPLAN.SQL script.

Below is an example of using the EXPLAIN PLAN command. Two scripts, UTLXPLS.SQL (serial) and UTLXPLP.SQL (parallel executions), are provided by oracle to show the formatted execution plans. You may alternatively use your own customized query on PLAN_TABLE.

e.g.: derive the execution plan.

SQL> explain plan for
  2  select count(1)
  3  from   mtl_system_items_b a, mtl_item_categories b, mtl_categories c
  4  where  a.segment4 like '5033%'
  5  and    b.inventory_item_id = a.inventory_item_id
  6  and    b.organization_id = a.organization_id
  7  and    c.category_id = b.category_id
  8  and    c.segment1 = 'WSARDN';


SQL> @c:\oracle\rdbms\admin\utlxpls

Plan Table
| Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |
| SELECT STATEMENT          |          |     1 |   47 |      4 |       |       |
|  SORT AGGREGATE           |          |     1 |   47 |        |       |       |
|   NESTED LOOPS            |          |     1 |   47 |      4 |       |       |
|    NESTED LOOPS           |          |    36 |    1K|      3 |       |       |
|     MERGE JOIN CARTESIAN  |          |    36 |  972 |      2 |       |       |
|      TABLE ACCESS BY INDEX|MTL_SYSTE |     4 |   64 |      1 |       |       |
|       INDEX RANGE SCAN    |MSII_IDX1 |     4 |      |      3 |       |       |
|      SORT JOIN            |          |     9 |   99 |      1 |       |       |
|       TABLE ACCESS BY INDE|MTL_CATEG |     9 |   99 |      1 |       |       |
|        INDEX RANGE SCAN   |MTL_CATEG |     9 |      |        |       |       |
|     INDEX UNIQUE SCAN     |MTL_CATEG |     3K|   25K|        |       |       |
|    INDEX RANGE SCAN       |MTL_ITEM_ |   594K|    7M|        |       |       |

15 rows selected.

The AUTOTRACE option of SQL*Plus is simple to use and provides execution plan immediately for the queries executed.

SQL> set autotrace on
SQL> select /*+ index(a jnc_purcat_n1) full(b) */ *
  2  from   jnc_purcat a, jnc_catdtl b
  3  where  b.purcat = a.purcat
  4  and    b.catnam = 'roger'
  5* and    b.cat_id = 1;
Execution Plan
   0|    |SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=98)|
   1|   0|  NESTED LOOPS (Cost=3 Card=1 Bytes=98)                     |
   2|   1|    TABLE ACCESS (FULL) OF 'JNC_CATDTL' (Cost=1 Card=1 Bytes|
    |    |=34)                                                        |

    |    |rd=82 Bytes=5248)                                           |

    |    |st=1 Card=82)                                               |

In the concluding installment of this series, we will look at achieving plan stability with Stored Outlines.

» See All Articles by Columnist Amar Kumar Padhi

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved