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

Tuesday Jan 27th 2004 by Amar Kumar Padhi
Share:

The final installment of this series covers the basics of using Stored Outlines and Oracle Application specific information.

In the final installment of this series, we will cover the basics of using Stored Outlines and Oracle Application specific information.

14. Stored Outlines

Oracle provides the option of changing the execution plan for running queries, without changing the code itself. This feature also allows us to insulate our application execution plans against any structural changes or upgrades. This feature is known as Stored Outlines and the idea is to store the execution plan in Oracle provided tables, which will later be referred to by the optimizer to execute the queries, thus providing optimizer plan stability.

Stored outlines are global and apply to all identical statements, regardless of the user firing it.

Setup for Stored Outlines

The user OUTLN is created automatically during installation. This user should be locked or password protected by the DBA.

The following System or session specific parameters are provided for creating and using stored outlines. These are not initialization parameters and are not set in the Initialization file.

CREATE_STORED_OUTLINES

Setting this to TRUE or an appropriate category name will automatically enable creating and storing of outlines for every subsequent query fired. A DEFAULT category is used unless specified.

e.g.:
SQL> alter session set create_stored_outlines=true;

SQL> alter session set create_stored_outlines=AM_OLTP;

SQL> alter system set create_stored_outlines = AM_OLTP;

SQL> alter system set create_stored_outlines = false;

USE_STORED_OUTLINES

This parameter enables the use of public stored outlines. Setting USE_STORED_OUTLINES to TRUE causes the execution plan stored in the DEFAULT category to take effect. Set the parameter to a proper category name to explicitly use a different category.

e.g.:
SQL> alter session set use_stored_outlines=true;

SQL> alter session set use_stored_outlines=AM_OLTP;

SQL> alter system set use_stored_outlines = AM_OLTP;

SQL> alter system set use_stored_outlines = false;

This option is also provided to test and use private outlines (CREATE PRIVATE OUTLINE, USE_PRIVATE_OUTLINES)

The outlines present in the session's private area are used rather than the publicly stored outlines.

Optionally, stored outlines can be created for individual statements with the CREATE OUTLINE command. This has to be used with care, as the SQL statement provided should be identical to what is present in the application.

e.g.:
SQL> variable b1 number;

SQL> create or replace outline run25 on
  2  select transaction_date, creation_date from mtl_material_transactions
  3  where transaction_id = :b1;

The package DBMS_OUTLN is used for managing stored outlines. This is synonymous with the OUTLN_PKG package.

The following are some of the important procedures provided for maintenance.

UPDATE_BY_CAT: Changes all outlines from one category to another.

e.g.: moving all DEFAULT category outlines to AM_OLTP category.
SQL> exec outln_pkg.update_by_cat('DEFAULT', 'AM_OLTP');

DROP_UNUSED: Drops outlines that have never been used.

SQL> exec outln_pkg.drop_unused;

DROP_BY_CAT: Drops all outlines in the specified category.

SQL> exec outln_pkg.drop_by_cat('AM_OLTP');

Limitations

Stored outlines are not used in the following cases:

1. A hint present in the Stored Outline has become invalid.

For example, if a stored outline was created that included a hint for using an index that was dropped later on, the outline plan will no longer be valid.

2. The parameter CURSOR_SHARING is set to FORCE.

This parameter was introduced in Oracle 8i. Setting it internally replaces literal values in queries with bind variables, allowing these statements to be shared.

Dictionary tables

DBA_OUTLINES - Stores all outlines in the database. The USED column indicates whether the outline has ever been used or not (USED, UNUSED or UNDEFINED).

OL$ - Owned by OUTLN schema. DBA_OUTLINES is a view on this table.

It provides more information, including the statement text.

DBA_OUTLINE_HINTS- Stores the execution path recommendation for all the stored outlines.

OL$HINTS - Owned by OUTLN schema. DBA_OUTLINE_HINTS is a view on this table. More information can be viewed from this table.

To test whether a statement used a stored outline or not, query the OUTLINE_CATEGORY column in V$SQL. If this column is null the statement executed did not use the stored outline.

Example

--creating stored outline for a batch script.
SQL> conn APPS/<>@tst
Connected.

SQL> alter session set create_stored_outlines = AM_OLTP;

Session altered.

SQL> @AM
..

SQL> alter session set create_stored_outlines =FALSE;

Session altered.

--checking if outlines have been created.
SQL> SELECT * FROM DBA_OUTLINES;

NAME                        |OWNER |CATEGORY |USED     |TIMESTAMP|VERSION    
____________________________|______|_________|_________|_________|__________
SYS_OUTLINE_040118090903266 |APPS  |AM_OLTP  |UNUSED   |18-JAN-04|8.1.7.4.0  


SQL> SELECT * FROM DBA_OUTLINE_HINTS;

NAME                       |OWNER| NODE| STAGE|JOIN_POS|HINT
___________________________|_____|_____|______|________|______________________________
SYS_OUTLINE_040118090903266|APPS |    1|     3|       0|NO_EXPAND
SYS_OUTLINE_040118090903266|APPS |    1|     3|       0|ORDERED
SYS_OUTLINE_040118090903266|APPS |    1|     3|       0|NO_FACT(MTL_MATERIAL_TRANSACTI
                           |     |     |      |        |ONS)

SYS_OUTLINE_040118090903266|APPS |    1|     3|       1|INDEX(MTL_MATERIAL_TRANSACTION
                           |     |     |      |        |S MTL_MATERIAL_TRANSACTIONS_U1
                           |     |     |      |        |)

SYS_OUTLINE_040118090903266|APPS |    1|     2|       0|NOREWRITE
SYS_OUTLINE_040118090903266|APPS |    1|     1|       0|NOREWRITE

6 rows selected.


--checking if outlines have been used.
SQL> SELECT USED FROM DBA_OUTLINES WHERE CATEGORY = 'AM_OLTP';

USED
_________
UNUSED

--Making use of stored outlines.
SQL> ALTER SESSION SET USE_STORED_OUTLINES = AM_OLTP;

Session altered.

SQL> @AM
..

--Checking if outlines have been used. 
SQL> SELECT USED FROM DBA_OUTLINES WHERE CATEGORY = 'AM_OLTP';

USED
_________
USED

--checking if outlines have been used, in V$SQL.
SQL> SELECT SQL_TEXT, OUTLINE_CATEGORY, OPTIMIZER_COST, OPTIMIZER_MODE
  2  FROM V$SQL WHERE lower(SQL_TEXT) LIKE 'select transaction_source_name%';

SQL_TEXT                                |OUTLINE_CA|OPTIMIZER_COST|OPTIMIZER_
________________________________________|__________|______________|__________
SELECT TRANSACTION_SOURCE_NAME   FROM MT|          |             1|CHOOSE
L_MATERIAL_TRANSACTIONS  WHERE TRANSACTI|          |              |
ON_ID = :b1                             |          |              |

SELECT TRANSACTION_SOURCE_NAME   FROM MT|AM_OLTP   |             1|CHOOSE
L_MATERIAL_TRANSACTIONS  WHERE TRANSACTI|          |              |
ON_ID = :b1                             |          |              |


--as could be seen above, the stored outlines are being used by oracle to choose the 
--execution path. We will now change some session setting to see if oracle still 
--looks at the execution path. Below I log in as different user and change 
--parameters that adversely affect the optimizer.

SQL> conn datg/<>@tst
Connected.

SQL> alter session set optimizer_mode = all_rows;

Session altered.

SQL> alter session set optimizer_index_cost_adj = 100;

Session altered.

SQL>  alter session set optimizer_index_caching = 10;

Session altered.

SQL> alter session set optimizer_max_permutations=20000;

Session altered.

--Setting to use the stored outlines.
SQL> alter session set use_stored_outlines = am_oltp;

Session altered.

SQL> @am

--checking to see if stored outlines were used.
SQL> SELECT SQL_TEXT, OUTLINE_CATEGORY, OPTIMIZER_COST, OPTIMIZER_MODE
  2  FROM V$SQL WHERE lower(SQL_TEXT) LIKE 'select transaction_source_name%';

SQL_TEXT                                |OUTLINE_CA|OPTIMIZER_COST|OPTIMIZER_
________________________________________|__________|______________|__________
SELECT TRANSACTION_SOURCE_NAME   FROM MT|          |             1|CHOOSE
L_MATERIAL_TRANSACTIONS  WHERE TRANSACTI|          |              |
ON_ID = :b1                             |          |              |

SELECT TRANSACTION_SOURCE_NAME   FROM MT|AM_OLTP   |             1|CHOOSE
L_MATERIAL_TRANSACTIONS  WHERE TRANSACTI|          |              |
ON_ID = :b1                             |          |              |

SELECT TRANSACTION_SOURCE_NAME   FROM MT|AM_OLTP   |             3|ALL_ROWS
L_MATERIAL_TRANSACTIONS  WHERE TRANSACTI|          |              |
ON_ID = :b1                             |          |              |

--As could be seen above, the last record shows that outline_category is being used
--even after changing the parameters. Thus the scipt will run the same way in all
--setups.

15. New Privileges

New privileges have been introduced in Oracle with the introduction of new features. For Optimizer maintenance, privileges for analyzing objects are the same as before. A user should have the ANALYZE ANY TABLE privilege in order to analyze objects owned by others.

example: Error when trying to generate statistics 
on objects without sufficient privileges.
jaJA>exec dbms_stats.delete_schema_stats('RON');
BEGIN dbms_stats.delete_schema_stats('RON'); END;
*
ERROR at line 1:
ORA-20000: Schema RON does not exist or 
insufficient privileges to analyze an object 
within it
ORA-06512: at "SYS.DBMS_STATS", line 3028
ORA-06512: at "SYS.DBMS_STATS", line 3050
ORA-06512: at "SYS.DBMS_STATS", line 3128
ORA-06512: at line 1

For creating stored outlines, the CREATE ANY OUTLINE privilege is required. Apart from this, for maintenance of stored outlines, the EXECUTE permission should be available on the DBMS_OUTLN/OUTLN_PKG package.

16. Oracle Applications specific information for CBO

Oracle Applications release 11i runs in CHOOSE mode that defaults to ALL_ROWS(COST). At my site, I found performance issues in some modules for online query screens. As users required better response time at my site, I had to make the queries run faster without actually changing the source.

The following Profile setup was one of the things I did to enforce a good response time. Please note that this should not be done if you are using CRM module; I came across a situation where the query screens did not return any data because of this setup.

Set profile value for sessions and concurrent requests:

Profile -> for users -> Initialization SQL statement - Custom

Set this to:

begin fnd_ctl.fnd_sess_ctl('FIRST_ROWS', 'ALL_ROWS', '', '', '', ''); end;

The above statement tells Oracle to run online sessions in FIRST_ROWS and concurrent requests in ALL_ROWS mode. This could be set for individual users or at the application level. Please try this out in test mode if you intend to use it, and check the Oracle Corporation Metalink site for more details. If you set this wrong, further logins will not be allowed, so be careful. You may query this setting from the backend using the following query:

select b.level_id, b.level_value, b.profile_option_id, b.profile_option_value
from   fnd_profile_options a, fnd_profile_option_values b
where  a.profile_option_name = 'FND_INIT_SQL'
and    b.profile_option_id = a.profile_option_id;

This could be reset from backend:

update fnd_profile_option_values
set    profile_option_value = null
where  profile_option_id = <profile_option_id>
and    level_id = <level_id>
and    level_value = <level_value>;

Another place where Optimizer mode could be set for a concurrent request in Oracle Applications is in the Concurrent Program definition screen. This will allow you to define mode settings at individual request programs.

SYSADMIN -> Concurrent -> Program -> Define -> Session Control.

My recommendation is to aim at achieving Nested-loop and Hash joins for online queries. Sort-Merge and Hash joins can be used for batch processing and heavy reporting tools. If your site has done customization in Oracle Applications check to see if you are generating statistics on the custom tables. Look out for Oracle recommendations for performance tuning on the Metalink site.

The direct use of the DBMS_STATS package in the Oracle Applications database is not recommended. Oracle Applications is provided with its own package, FND_STATS, for this very purpose. This package is invoked when a concurrent request (Gather Schema Statistics, Gather Table Statistics) is submitted. It can also be directly invoked from SQL*Plus.

E.g.: Generating statistics for Application schema. This is done from APPS schema login.
SQL> exec fnd_stats.gather_schema_statistics('MRP');

17. Conclusion

Start planning for the move to CBO!

Oracle is not a one-size fits all setup, it comes with a lot of features and options that should be customized and set as per the need of an organization. Oracle strongly recommends that applications should be thoroughly tested prior to moving the production box to CBO. This will help in identifying unforeseen circumstances and glitches that may be unique to your setup. I hope you enjoyed this series as much as I did writing it!

» See All Articles by Columnist Amar Kumar Padhi

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