Using Oracle Sql Stored Outlines & Optimizer Plan Stability (Oracle In-Focus Series)

Written by one the world's top Oracle script developers and author of 14 best-selling Oracle books, Mike Ault targets his substantial knowledge of Oracle tuning in this concise eBook. With decades of experience using Oracle, Mike Ault shares secrets for Oracle stored outlines and SQL optimizer plan stability. Team DDU Using Oracle SQL Stored Outlines & Optimizer Plan Stability 1 Cover 1 Notice 4 Table Of Contents 6 Publication Information 6 Table Of Contents 6 Introduction 9 Setting Up for Use of Outlines 9 Installing OUTLN Schema "After the Fact" 9 Script to Install OUTLN Schema 10 General Facts about OUTLN Schema 11 Requirements for OUTLINE Use 12 Some General Usage Notes: 13 Views Used With OUTLINES 13 Packages Used with OUTLINEs 14 Plan Stability 14 Creation of a OUTLINE object 15 Altering a OUTLINE 16 Dropping an OUTLINE 17 Use of the OUTLN_PKG To Manage SQL Stored Outlines 17 DROP_UNUSED 17 DROP_BY_CAT 19 UPDATE_BY_CAT 20 New Procedures for Oracle9i 21 Manually Editing Plans 22 Using DML and Packages to Edit Outlines 23 A Detailed Example 23 Using DBMS_OUTLN_EDIT GENERATE_SIGNATURE 28 Replacing a Non-Hinted Outline 29 Technique 30 Example 30 Moving OUTLINES from One DB to Another 32 Scenario 32 Technique 33 Example 34 Summary 36 Team DDU 1 Team DDU......Page 1 Notice......Page 4 Table Of Contents......Page 6 Installing OUTLN Schema "After the Fact"......Page 9 Script to Install OUTLN Schema......Page 10 General Facts about OUTLN Schema......Page 11 Requirements for OUTLINE Use......Page 12 Views Used With OUTLINES......Page 13 Plan Stability......Page 14 Creation of a OUTLINE object......Page 15 Altering a OUTLINE......Page 16 DROP_UNUSED......Page 17 DROP_BY_CAT......Page 19 UPDATE_BY_CAT......Page 20 New Procedures for Oracle9i......Page 21 Manually Editing Plans......Page 22 A Detailed Example......Page 23 Using DBMS_OUTLN_EDIT GENERATE_SIGNATURE......Page 28 Replacing a Non-Hinted Outline......Page 29 Example......Page 30 Scenario......Page 32 Technique......Page 33 Example......Page 34 Summary......Page 36