By Mike Ault
Read or Download Using Oracle Sql Stored Outlines & Optimizer Plan Stability (Oracle In-Focus Series) PDF
Similar databases books
It doesn't matter what DBMS you're using—Oracle, DB2, SQL Server, MySQL, PostgreSQL—misunderstandings can consistently come up over the fitting meanings of phrases, misunderstandings which may have a significant influence at the good fortune of your database initiatives. for instance, listed below are a few universal database phrases: characteristic, BCNF, consistency, denormalization, predicate, repeating workforce, subscribe to dependency.
Additional info for Using Oracle Sql Stored Outlines & Optimizer Plan Stability (Oracle In-Focus Series)
Importing table "OL$HINTS" 17 rows imported Import terminated successfully without warnings. Of course you could have also imported the tables into a different user and then used an INSERT using a SELECT to add the outlines if you wanted to retain the outlines already in production. Summary The OUTLN_PKG and DBMS_OUTLN_EDIT packages are powerful new features in Oracle. By their capability to add “stealth” hints to Oracle SQL statements without altering code they allow the DBA greater flexibility in tuning “hands off” systems than was ever available before.
Create the OUTLINE for HINTSQL 4. Exchange the OUTLINE plan between the two OUTLINES 5. Drop the OUTLINE for HINTSQL 6. Now the OUTLINE plan for ORIGINALSQL is the same as the execution plan of HINTSQL which uses HINTs. Only the point 5 (Exchange the OUTLINE plan between the two OUTLINEs) is detailed below. The rest of the steps are covered in the bulk of the paper. OL$HINTS SET OL_NAME=DECODE(OL_NAME,'HINTSQL','ORIGINALSQL','ORIGINALSQL','HINTSQL') WHERE OL_NAME IN ('HINTSQL','ORIGINALSQL'); Commit; Example Let’s look at an example using the SCOTT schema using the example schema objects.
By using import and export and specialized editing techniques statement execution plans can be easily modified “under the hood” without touching production code. PAGE 28 COPYRIGHT © 2003 RAMPANT TECHPRESS. ALL RIGHTS RESERVED.