Toad World® Forums

Confusion over outlines


#1

SELECT MAX(EMP_SALARY)

INTO V_SALARY

FROM EMPLOYEE,

WHERE TO_CHAR(EMP_EXP_DATE, ‘YYYY’) = ‘1998’ AND EMP_DEPT = DPT_ID

The alternative syntax is:
SELECT /*+ NO_CPU_COSTING */ MAX(EMP_SALARY)

INTO V_SALARY

EMPLOYEE

AND EMP_DEPT = DPT_ID || **’’
**Why would the alternative statement be considered a candidate for being deployed as an outline? I think I’m stuck on this notion that the alternative statement has to be identical to the original (or currently deployed) statement.

WHERE TO_CHAR(EMP_EXP_DATE, ‘YYYY’) = ‘1998’

I’ve run the optimizer on a particulare statement and have received an alternative statment that is a candidate for being deployed as an outline. I’m confused as to how the new statement could be considered as a candidate given that the SQL syntax is not identical to the original.

The original syntax is as follows:
DEPARTMENT FROM DEPARTMENT,


#2

One thing good about the Oracle Stored Outline is that you are able to save a compatible outline with the SQL text to influence how the SQL is executed. Therefore outline that is produced by the alternative SQL text does not have to be the same as the original SQL for the outline to be compatible.

For example:

Optimizing SQL1 will produce:

Alternatives Alt1 Alt2 Alt3

Outlines S1 S2 S3

Plans P1 P2 P3

If Alt2 produces the best improvement, you can save O2 against SQL1. So next time SQL1 is executed it will Oracle will produce an execution plan based on O2 to produce an execution plan P2.


#3

Hi Alex,

In looking through the Help documentation on the Outline Manger, I ran across this statement:

Warning: The SQL text that is saved with the outline needs to be identical to the SQL statement or the outline will not be used. Oracle will consider two SQL statements as not identical if there is any difference in spacing, carriage return, embedded hints, comments, and upper/lowercase.

So, in the example I gave, I believe there is a difference in the embedded hints between the original SQL and the Alternative SQL. This would mean that the alternative outline wouldn’t be used, right?


#4

A stored outline is used to force Oracle to use a specific execution plan each time a SQL statement is executed. This is done by created a stored outline for the SQL statement. You can take the execution plan from one SQL statement and deploy it as the stored outline for another SQL statement.

So if I have an original SQL statement

select * from employee

which has these 2 steps in its execution plan

  1. Every row in the table EMPLOYEE is read.
  2. Rows were returned by the SELECT statement.

I can deploy a stored outline for this statement using the execution from this statement

select /*+ INDEX(EMPLOYEE) */ * from employee

which has these 3 steps in its execution plan

  1. Rows were retrieved by performing a sequential read of all records in index IDX_EMP_ID_ADDRESS in ascending order.
  2. Rows from table EMPLOYEE were accessed using rowid got from an index.
  3. Rows were returned by the SELECT statement.

When this outline is in a “Category” that is “enabled” then when this SQL statement, “select * from employee”, is executed, this execution plan is used.

  1. Rows were retrieved by performing a sequential read of all records in index IDX_EMP_ID_ADDRESS in ascending order.
  2. Rows from table EMPLOYEE were accessed using rowid got from an index.
  3. Rows were returned by the SELECT statement.

The Warning that you copied from online means this:

If I deploy an outline for this SQL statement

select * from employee

I must format the SQL statement in the same way each time I use it in order the stored outline to be used.

Therefore, Oracle will use the stored outline if I executed this

select * from employee

Oracle will not use the stored outline if I execute the same statement formatted this way.

select *

from employee


#5

Great! Rene, thanks for clearing all of that up.