dcsimg

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

January 27, 2004

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.







The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers