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

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

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.

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

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';

select degree from dba_tables where table_name = 'GL_INTERFACE';

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

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

NAME                                              |CUR                 |ORIG
__________________________________________________|____________________|_______
optimizer_mode                                    |FIRST_ROWS          |CHOOSE
hash_join_enabled                                 |FALSE               |TRUE

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.

e.g.:

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';

Explained.

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)                                                        |

   3|   1|    TABLE ACCESS (BY INDEX ROWID) OF 'JNC_PURCAT' (Cost=2 Ca|
    |    |rd=82 Bytes=5248)                                           |

   4|   3|      INDEX (RANGE SCAN) OF 'JNC_PURCAT_N1' (NON-UNIQUE) (Co|
    |    |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