Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Jan 27, 2004

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

By Amar Kumar Padhi

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.


Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM