Shulou(Shulou.com)06/02 Report--

Oracle's Outline technology can ensure the stability of the execution plan under special circumstances. In extreme cases, this technique can be used to temporarily lock down the execution plan.

The main usage scenarios are as follows:

① cannot complete the optimization task of SQL in a short period of time. You can use outline to temporarily lock the SQL execution plan.

② in CBO optimization mode, when there is a problem with the statistics, it will cause abnormal changes in the execution plan. You can use outline to temporarily adjust the SQL execution plan.

③ uses outline to lock the execution plan due to an exception in the execution plan of SQL due to the bug of the database.

Write down the usage of outline for reference.

1. Initialize the environment

1) create the user secooler and grant the appropriate permissions. Note that the user needs to have create any outline permissions

SYS@PROD > create user secooler identified by secooler

User created.

SYS@PROD > grant connect,resource to secooler

Grant succeeded.

SYS@PROD > grant create any outline,alter any outline to secooler

Grant succeeded.

2) create table T in secooler user

SYS@PROD > conn secooler/secooler

Connected.

SECOOLER@PROD > create table t as select * from all_objects

Table created.

SECOOLER@PROD > select count (*) from t

COUNT (*)

-

4448

two。 Create outline

1) unlock outln users

SECOOLER@PROD > conn / as sysdba

Connected.

SYS@PROD > alter user outln identified by outln account unlock

User altered.

2) create an outline named t_outln1, and specify its category name as CATEGORY_T

SYS@PROD > conn secooler/secooler

Connected.

SECOOLER@PROD > create outline t_outln1 for category CATEGORY_T on select * from t where OBJECT_ID=258

Outline created.

3) at this time, the information related to this operation will be recorded in the three tables OL$, OL$HINTS and OL$NODES under outln users. The execution plan is recorded in OL$HINTS.

SECOOLER@PROD > conn outln/outln

Connected.

OUTLN@PROD > select hint_text from ol$hints where ol_name = 'Tunable OUTLN1' order by hint#

HINT_TEXT

FULL (@ "SEL$1"T" @ "SEL$1")

OUTLINE_LEAF (@ "SEL$1")

ALL_ROWS

OPTIMIZER_FEATURES_ENABLE ('10.2.0.1')

IGNORE_OPTIM_EMBEDDED_HINTS

4) compare the corresponding implementation plan

OUTLN@PROD > conn secooler/secooler

Connected.

SECOOLER@PROD > set autotrace traceonly explain

SECOOLER@PROD > select * from t where OBJECT_ID=258

Execution Plan

Plan hash value: 1601196873

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 1 | 128 | 19 (0) | 00:00:01 |

| | * 1 | TABLE ACCESS FULL | T | 1 | 128 | 19 (0) | 00:00:01 |

Predicate Information (identified by operation id):

1-filter ("OBJECT_ID" = 258)

Note

-

-dynamic sampling used for this statement

A full table scan execution plan is recorded here.

5) basic information about the created outline can also be queried through the dba_outlines view

SECOOLER@PROD > conn / as sysdba

Connected.

SYS@PROD > select NAME,OWNER,CATEGORY,SQL_TEXT from dba_outlines

NAME OWNER

CATEGORY

-

SQL_TEXT

T_OUTLN1 SECOOLER

CATEGORY_T

Select * from t where OBJECT_ID=258

3. Use outline

1) for comparison, we create an index and change the execution plan of the SQL statement

(1) create an index in the X field of the T table

SYS@PROD > conn secooler/secooler

Connected.

SECOOLER@PROD > create index iTunt on t (object_id)

Index created.

(2) View the execution plan of SQL at this time

SECOOLER@PROD > set lines 200

SECOOLER@PROD > set autotrace traceonly explain

SECOOLER@PROD > select * from t where OBJECT_ID=258

Execution Plan

Plan hash value: 2928007915

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 1 | 128 | 2 (0) | 00:00:01 |

| | 1 | TABLE ACCESS BY INDEX ROWID | T | 1 | 128 | 2 (0) | 00:00:01 |

| | * 2 | INDEX RANGE SCAN | iTunt | 1 | | 1 (0) | 00:00:01 |

Predicate Information (identified by operation id):

2-access ("OBJECT_ID" = 258)

Note

-

-dynamic sampling used for this statement

As you can see from the execution plan, the index is used by the SQL at this time, and there is no full table scan.

2) force SQL to use the execution plan recorded in ontline

(1) set the session to use outline whose category is CATEGORY_T

SECOOLER@PROD > alter session set use_stored_outlines=CATEGORY_T

Session altered.

(2) View the execution plan of the SQL statement again

SECOOLER@PROD > select * from t where OBJECT_ID=258

Execution Plan

Plan hash value: 1601196873

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 51 | 6528 | 19 (0) | 00:00:01 |

| | * 1 | TABLE ACCESS FULL | T | 51 | 6528 | 19 (0) | 00:00:01 |

Predicate Information (identified by operation id):

1-filter ("OBJECT_ID" = 258)

Note

-

-outline "T_OUTLN1" used for this statement

As we can see from the final comment, the execution plan recorded in outln is used during the execution of the SQL statement. SQL takes a full table scan when getting the data.

3) eliminate the influence of ontline on SQL statements.

(1) the first method is to adjust the use_stored_outlines parameter to false.

SECOOLER@PROD > alter session set use_stored_outlines=false

Session altered.

SECOOLER@PROD > select * from t where OBJECT_ID=258

Execution Plan

Plan hash value: 2928007915

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 1 | 128 | 2 (0) | 00:00:01 |

| | 1 | TABLE ACCESS BY INDEX ROWID | T | 1 | 128 | 2 (0) | 00:00:01 |

| | * 2 | INDEX RANGE SCAN | iTunt | 1 | | 1 (0) | 00:00:01 |

Predicate Information (identified by operation id):

2-access ("OBJECT_ID" = 258)

Note

-

-dynamic sampling used for this statement

(2) the second method is to disable the specific outline.

This method can deactivate the specific outline if the use_stored_outlines parameter is at work.

SECOOLER@PROD > alter session set use_stored_outlines=CATEGORY_T

Session altered.

SECOOLER@PROD > select * from t where OBJECT_ID=258

Execution Plan

Plan hash value: 1601196873

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 51 | 6528 | 19 (0) | 00:00:01 |

| | * 1 | TABLE ACCESS FULL | T | 51 | 6528 | 19 (0) | 00:00:01 |

Predicate Information (identified by operation id):

1-filter ("OBJECT_ID" = 258)

Note

-

-outline "T_OUTLN1" used for this statement

SECOOLER@PROD > alter outline t_outln1 disable

Outline altered.

SECOOLER@PROD > select * from t where OBJECT_ID=258

Execution Plan

Plan hash value: 2928007915

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 1 | 128 | 2 (0) | 00:00:01 |

| | 1 | TABLE ACCESS BY INDEX ROWID | T | 1 | 128 | 2 (0) | 00:00:01 |

| | * 2 | INDEX RANGE SCAN | iTunt | 1 | | 1 (0) | 00:00:01 |

Predicate Information (identified by operation id):

2-access ("OBJECT_ID" = 258)

Note

-

-dynamic sampling used for this statement

At this point, ontline T_OUTLN1 has been disabled.

4. Clear outline

We can use dbms_outln.drop_by_cat to empty a specific category in a 10g environment.

SECOOLER@PROD > conn outln/outln

Connected.

OUTLN@PROD > select hint_text from ol$hints where ol_name = 'Tunable OUTLN1' order by hint#

HINT_TEXT

FULL (@ "SEL$1"T" @ "SEL$1")

OUTLINE_LEAF (@ "SEL$1")

ALL_ROWS

OPTIMIZER_FEATURES_ENABLE ('10.2.0.1')

IGNORE_OPTIM_EMBEDDED_HINTS

OUTLN@PROD > exec dbms_outln.drop_by_cat ('CATEGORY_T')

PL/SQL procedure successfully completed.

OUTLN@PROD > select hint_text from ol$hints where ol_name = 'Tunable OUTLN1' order by hint#

No rows selected

5. Description of the USE_STORED_OUTLINES parameter

Unlike normal parameters, the USE_STORED_OUTLINES parameter can be set in the parameter file, but we can modify it using normal methods.

ALTER SESSION SET USE_STORED_OUTLINES = TRUE | FALSE | category

ALTER SYSTEM SET USE_STORED_OUTLINES = TRUE | FALSE | category

6. Summary

Through the description in this article, we should have a more detailed understanding of Oracle Outline technology. In CBO optimization mode, you are likely to encounter unstable and inaccurate execution plans. In this scenario, we can consider using this technique to temporarily circumvent the performance problems caused by changes in the execution plan.

Good luck.

Secooler

10.09.08

-- The End--

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.