Free Oracle Tips   HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

Managing Categories with Stored Outlines Oracle Tips by Burleson

Oracle provides a method whereby specific categories of stored outlines may be created. The categories can be very useful when you want to segregate stored outlines for testing purposes or when you want to separate stored outlines.

However, the stored outlines in each category will always be used when a SQL statement enters the SQL parser, regardless of the category name, so it is important to remember that you cannot use stored outline categories to segregate stored outlines from execution. Categories are only used for the purpose of grouping related stored outlines. Let’s take a quick look at these procedures in the stored outline category.

The drop_by_cat Procedure

The drop_by_cat procedure drops all outlines that belong to a specific category. The procedure drop_by_cat has one input variable, cat, a VARCHAR2 that corresponds to the name of the category you want to drop.

create or replace outline
   cbo_sql
for category
   my_test
on
select
   dname,
   loc,
   sum(sal)
from
   emp,
   dept
where
   emp.deptno(+) = dept.deptno
and
   dept.deptno = 10
group by
   dname,
   loc
;

Now, we can display all stored outlines in the my_test category:

SQL> set long 1000;
SQL> select * from dba_outlines where category='MY_TEST';

NAME                           OWNER
------------------------------ ------------------------------
CATEGORY                       USED      TIMESTAMP
------------------------------ --------- ---------
VERSION
----------------------------------------------------------------
SQL_TEXT
---------------------------------------------------------------------
CBO_SQL                        OPS$ORACLE
MY_TEST                        UNUSED    15-APR-01
8.1.6.1.0
select
   dname,
   loc,
   sum(sal)
from
   emp,
   dept
where
   emp.deptno(+) = dept.deptno
and
   dept.deptno = 10
group by
   dname,
   loc

Now, to remove all stored outlines in the my_test category, we invoke the drop_by_cat procedure:

SQL> exec outline.drop_by_cat('MY_TEST');

PL/SQL procedure successfully completed.

SQL> select * from dba_outlines where category='MY_TEST';

no rows selected

Next, let’s look at the update_by_cat procedure.

The update_by_cat Procedure

The update_by_cat procedure merges all of the outlines in one category to a new category. This procedure is tricky because if an SQL in a stored outline already has an outline in the target category, then it is not merged into the new category. In other words, duplicates are not merged into the new category. Let’s illustrate this with a simple example.

Here we create three stored outlines. Please note that the SQL for prod_sql1 is identical to the SQL in test_sql1.

create outline
  test_sql1
for category
   test
on
select * from dba_indexes;

create outline
  test_sql2
for category
   test
on
select * from dba_constraints;

create outline
  prod_sql1
for category
   prod
on
select * from dba_indexes;

Now we can select the names and categories and verify the placement of the SQL stored outlines in their categories:

SQL> select
   name,
   category
from
   dba_outlines
order by
   category;

NAME                           CATEGORY
------------------------------ ------------------------------
PROD_SQL1                      PROD
TEST_SQL1                      TEST
TEST_SQL2                      TEST

Next, we execute the update_by_cat procedure to merge the TEST stored outlines into the PROD category.

SQL> exec outline.update_by_cat('TEST','PROD');

PL/SQL procedure successfully completed.

As we mentioned, because prod_sql1 is identical to test_sql1, we expect that the test_sql1 will not have been merged into the PROD category. Let’s check and see:

SQL> select name,category from dba_outlines order by category;

NAME                           CATEGORY
------------------------------ ------------------------------
TEST_SQL2                      PROD
PROD_SQL1                      PROD
TEST_SQL1                      TEST

To summarize, the update_by_cat is used to merge UNIQUE SQL statements from one stored outline category to another category. Duplicate stored outlines are not merged into the new category.

Conclusion

The optimizer plan stability feature of Oracle8i is an exciting new way to improve the speed of SQL and also provide a method for making tuning changes permanent. The major points of this chapter include these:

• Stored outlines improve SQL performance because SQL statements with stored outlines do not have to reformulate an execution plan when they are invoked.

• Stored outlines improve SQL tuning because an improved execution plan for a SQL statement can be stored without touching the original SQL source code.

• Stored outlines are great for tuning databases where the SQL source is not available, such as SAP and PeopleSoft applications products.

• Stored outlines can be turned on at the system level with the alter system set use_stored_outlines=true command. This will cause every SQL statement to store an outline with the name SYS_OUTLINE_nnn.

• Stored outlines can be set at the session level with the alter session set use_stored_outlines=true command. This is the recommended method for tuning individual SQL statements.

• You can change a stored outline for a SQL statement by creating and storing an improved stored outline and running the swap_outlines.sql script to swap in your improved stored outline.

• Databases with lots of nonreusable SQL with embedded literal values may quickly fill the outline tablespace unless cursor_sharing=force is set in the initialization file.

• Oracle provides a crude category management tool for creating and merging categories of stored outlines.

Next, let’s take a look at SQL tuning with the cost-based optimizer and see how you can maximize your benefits from exploiting the new cost-based hints.

This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.