Oracle Database 11g SQL Tuning Workshop.pdf

600  10  Download (0)

Full text

(1)Unauthorized reproduction or distribution prohibitedฺ Copyright© 2010, Oracle and/or its affiliatesฺ. i a j i V. e. le. b fera. s. n a r t n-. Oracle Database no 11g: SQL a Tuning eฺ hasWorkshop. d. ahu. ) Gui m o Student t c enGuide ฺ l i a d m Stu g 1@ this 2 y ja use i v u to sah. (. S. D52163GC20 Edition 2.0 October 2010 D69160. ns e c li.

(2) Copyright © 2010, Oracle and/or its affiliates. All rights reserved.. Unauthorized reproduction or distribution prohibitedฺ Copyright© 2010, Oracle and/or its affiliatesฺ. Disclaimer This document contains proprietary information and is protected by copyright and other intellectual property laws. You may copy and print this document solely for your own use in an Oracle training course. The document may not be modified or altered in any way. Except where your use constitutes "fair use" under copyright law, you may not use, share, download, upload, copy, print, display, perform, reproduce, publish, license, post, transmit, or distribute this document in whole or in part without the express authorization of Oracle. The information contained in this document is subject to change without notice. If you find any problems in the document, please report them in writing to: Oracle University, 500 Oracle Parkway, Redwood Shores, California 94065 USA. This document is not warranted to be error-free. Restricted Rights Notice If this documentation is delivered to the United States Government or anyone using the documentation on behalf of the United States Government, the following notice is applicable: U.S. GOVERNMENT RIGHTS The U.S. Government’s rights to use, modify, reproduce, release, perform, display, or disclose these training materials are restricted by the terms of the applicable Oracle license agreement and/or the applicable U.S. Government contract.. le. Trademark Notice. b fera. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.. s. n a r t n-. Vij. no a has uideฺ ) Technical Contributors and Reviewers m tG o c ฺ l Abhinav Gupta, Branislav Valny, ClintonaShaffer, Donna i en Keesling, Ira Singer, Howard Bradley, d u Sean Kim, Sue Harper, Teria Kidd gm t S @ s 1 hi 2 t y e This book was published s Oracle Tutor ija using: u v u o t ah s ( hu a S ai Author James Spiller, Tulika Srivastava. e. ns e c li.

(3) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2010, Oracle and/or its affiliatesฺ. Table of Contents Exploring the Oracle Database Architecture .................................................................................................1-1 Exploring the Oracle Database Architecture ..................................................................................................1-2 Objectives ......................................................................................................................................................1-3 Oracle Database Server Architecture: Overview ............................................................................................1-4 Connecting to the Database Instance ............................................................................................................1-5 Oracle Database Memory Structures: Overview ............................................................................................1-7 Database Buffer Cache ..................................................................................................................................1-8 Redo Log Buffer .............................................................................................................................................1-9 Shared Pool ...................................................................................................................................................1-10 Processing a DML Statement: Example .........................................................................................................1-11 COMMIT Processing: Example ......................................................................................................................1-13 Large Pool ......................................................................................................................................................1-14 Java Pool and Streams Pool ..........................................................................................................................1-16 Program Global Area (PGA) ..........................................................................................................................1-17 Background Process ......................................................................................................................................1-18 Automatic Shared Memory Management .......................................................................................................1-20 Automated SQL Execution Memory Management .........................................................................................1-21 Automatic Memory Management ...................................................................................................................1-22 Database Storage Architecture ......................................................................................................................1-23 Logical and Physical Database Structures .....................................................................................................1-25 Segments, Extents, and Blocks......................................................................................................................1-27 SYSTEM and SYSAUX Tablespaces.............................................................................................................1-28 Quiz................................................................................................................................................................1-29 Summary ........................................................................................................................................................1-32 Practice 1: Overview ......................................................................................................................................1-33. le. b fera. s. n a r t n-. Vija. no a has uideฺ ) om nt G c ฺ l ai tude m g sS @ 1 hi 2 t y e s ija.............................................................................................................................2-1 Introduction to SQL Tuning u v u o t h Tuning ............................................................................................................................2-2 Introduction toaSQL s Objectives( ......................................................................................................................................................2-3 hu for Inefficient SQL Performance ......................................................................................................2-4 Reasons a S i. Inefficient SQL: Examples ..............................................................................................................................2-6 Performance Monitoring Solutions .................................................................................................................2-8 Monitoring and Tuning Tools: Overview .........................................................................................................2-10 EM Performance Pages for Reactive Tuning .................................................................................................2-11 Tuning Tools: Overview .................................................................................................................................2-12 SQL Tuning Tasks: Overview.........................................................................................................................2-14 CPU and Wait Time Tuning Dimensions ........................................................................................................2-15 Scalability with Application Design, Implementation, and Configuration ........................................................2-16 Common Mistakes on Customer Systems .....................................................................................................2-17 Proactive Tuning Methodology .......................................................................................................................2-19 Simplicity in Application Design......................................................................................................................2-20 Data Modeling ................................................................................................................................................2-21 Table Design ..................................................................................................................................................2-22 Index Design ..................................................................................................................................................2-23 Using Views ...................................................................................................................................................2-24 SQL Execution Efficiency ...............................................................................................................................2-25 Writing SQL to Share Cursors ........................................................................................................................2-27 Performance Checklist ...................................................................................................................................2-29 Copyright © 2010, Oracle and/or its affiliates. All rights reserved.. Oracle Database 11g: SQL Tuning Workshop Table of Contents i. e. ns e c li.

(4) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2010, Oracle and/or its affiliatesฺ. i a j i V. Development Environments: Overview ..........................................................................................................2-30 What Is Oracle SQL Developer? ....................................................................................................................2-31 Coding PL/SQL in SQL*Plus ..........................................................................................................................2-32 Quiz................................................................................................................................................................2-34 Summary ........................................................................................................................................................2-38 Practice 2: Overview ......................................................................................................................................2-39 Introduction to the Optimizer ..........................................................................................................................3-1 Introduction to the Optimizer ..........................................................................................................................3-2 Objectives ......................................................................................................................................................3-3 Structured Query Language ...........................................................................................................................3-4 SQL Statement Representation .....................................................................................................................3-6 SQL Statement Implementation .....................................................................................................................3-7 SQL Statement Processing: Overview ...........................................................................................................3-8 SQL Statement Processing: Steps .................................................................................................................3-9 Step 1: Create a Cursor .................................................................................................................................3-10 Step 2: Parse the Statement ..........................................................................................................................3-11 Steps 3 and 4: Describe and Define...............................................................................................................3-12 Steps 5 and 6: Bind and Parallelize ...............................................................................................................3-13 Steps 7 Through 9..........................................................................................................................................3-14 SQL Statement Processing PL/SQL: Example ..............................................................................................3-15 SQL Statement Parsing: Overview.................................................................................................................3-16 Why Do You Need an Optimizer? ..................................................................................................................3-18 Optimization During Hard Parse Operation ....................................................................................................3-20 Transformer: OR Expansion Example............................................................................................................3-21 Transformer: Subquery Unnesting Example ..................................................................................................3-22 Transformer: View Merging Example .............................................................................................................3-23 Transformer: Predicate Pushing Example ......................................................................................................3-24 Transformer: Transitivity Example..................................................................................................................3-25 Cost-Based Optimizer ....................................................................................................................................3-26 Estimator: Selectivity ......................................................................................................................................3-27 Estimator: Cardinality .....................................................................................................................................3-29 Estimator: Cost...............................................................................................................................................3-30 Plan Generator ...............................................................................................................................................3-31 Controlling the Behavior of the Optimizer .......................................................................................................3-32 Optimizer Features and Oracle Database Releases ......................................................................................3-37 Quiz................................................................................................................................................................3-38 Summary ........................................................................................................................................................3-41 Practice 3: Overview ......................................................................................................................................3-42. le. b fera. s. n a r t n-. ahu. no a has uideฺ ) om nt G c ฺ l ai tude m g sS @ 1 hi 2 t y e ija us v u to sah. (. S. Interpreting Execution Plans...........................................................................................................................4-1 Interpreting Execution Plans ..........................................................................................................................4-2 Objectives ......................................................................................................................................................4-3 What Is an Execution Plan? ...........................................................................................................................4-4 Where to Find Execution Plans? ....................................................................................................................4-6 Viewing Execution Plans ................................................................................................................................4-8 The EXPLAIN PLAN Command .....................................................................................................................4-9 The EXPLAIN PLAN Command: Example .....................................................................................................4-11 PLAN_TABLE ................................................................................................................................................4-12 Displaying from PLAN_TABLE: Typical .........................................................................................................4-14 Displaying from PLAN_TABLE: ALL ..............................................................................................................4-16 Copyright © 2010, Oracle and/or its affiliates. All rights reserved.. Oracle Database 11g: SQL Tuning Workshop Table of Contents ii. e. ns e c li.

(5) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2010, Oracle and/or its affiliatesฺ. i a j i V. The EXPLAIN PLAN Command .....................................................................................................................4-18 Displaying from PLAN_TABLE: ADVANCED .................................................................................................4-19 Explain Plan Using SQL Developer................................................................................................................4-20 AUTOTRACE .................................................................................................................................................4-21 The AUTOTRACE Syntax ..............................................................................................................................4-22 AUTOTRACE: Examples ...............................................................................................................................4-23 AUTOTRACE: Statistics .................................................................................................................................4-24 AUTOTRACE Using SQL Developer .............................................................................................................4-26 Using the V$SQL_PLAN View .......................................................................................................................4-27 The V$SQL_PLAN Columns ..........................................................................................................................4-28 The V$SQL_PLAN_STATISTICS View..........................................................................................................4-29 Links Between Important Dynamic Performance Views .................................................................................4-30 Querying V$SQL_PLAN .................................................................................................................................4-32 Automatic Workload Repository (AWR) .........................................................................................................4-34 Managing AWR with PL/SQL .........................................................................................................................4-36 Important AWR Views ....................................................................................................................................4-38 Querying the AWR .........................................................................................................................................4-40 Generating SQL Reports from AWR Data ......................................................................................................4-42 SQL Monitoring: Overview .............................................................................................................................4-43 SQL Monitoring Report: Example...................................................................................................................4-45 Interpreting an Execution Plan .......................................................................................................................4-49 Execution Plan Interpretation: Example 1 ......................................................................................................4-51 Execution Plan Interpretation: Example 2 ......................................................................................................4-55 Execution Plan Interpretation: Example 3 ......................................................................................................4-57 Reading More Complex Execution Plans .......................................................................................................4-59 Reviewing the Execution Plan ........................................................................................................................4-60 Looking Beyond Execution Plans ...................................................................................................................4-62 Quiz................................................................................................................................................................4-63 Summary ........................................................................................................................................................4-67 Practice 4: Overview ......................................................................................................................................4-68. le. b fera. s. n a r t n-. ahu. no a has uideฺ ) om nt G c ฺ l ai tude m g sS @ 1 hi 2 t y e ija us v u to sah. (. Application Tracing..........................................................................................................................................5-1 Application Tracing.........................................................................................................................................5-2 Objectives ......................................................................................................................................................5-3 End-to-End Application Tracing Challenge ....................................................................................................5-4 End-to-End Application Tracing......................................................................................................................5-5 Location for Diagnostic Traces .......................................................................................................................5-6 What Is a Service? .........................................................................................................................................5-7 Using Services with Client Applications .........................................................................................................5-8 Tracing Services ............................................................................................................................................5-9 Use Enterprise Manager to Trace Services ...................................................................................................5-11 Service Tracing: Example ..............................................................................................................................5-12 Session Level Tracing: Example ....................................................................................................................5-14 Trace Your Own Session ...............................................................................................................................5-16 The trcsess Utility ...........................................................................................................................................5-17 Invoking the trcsess Utility ..............................................................................................................................5-18 The trcsess Utility: Example ...........................................................................................................................5-20 SQL Trace File Contents ................................................................................................................................5-21 SQL Trace File Contents: Example ................................................................................................................5-23 Formatting SQL Trace Files: Overview ..........................................................................................................5-24. S. Copyright © 2010, Oracle and/or its affiliates. All rights reserved.. Oracle Database 11g: SQL Tuning Workshop Table of Contents iii. e. ns e c li.

(6) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2010, Oracle and/or its affiliatesฺ. i a j i V. Invoking the tkprof Utility ................................................................................................................................5-26 tkprof Sorting Options ....................................................................................................................................5-28 Output of the tkprof Command .......................................................................................................................5-30 tkprof Output with No Index: Example ............................................................................................................5-35 tkprof Output with Index: Example .................................................................................................................5-36 Quiz................................................................................................................................................................5-37 Summary ........................................................................................................................................................5-40 Practice 5: Overview ......................................................................................................................................5-41 Optimizer Operators ........................................................................................................................................6-1 Optimizer Operators .......................................................................................................................................6-2 Objectives ......................................................................................................................................................6-3 Row Source Operations .................................................................................................................................6-4 Main Structures and Access Paths ................................................................................................................6-5 Full Table Scan ..............................................................................................................................................6-6 Full Table Scans: Use Cases .........................................................................................................................6-7 ROWID Scan..................................................................................................................................................6-9 Sample Table Scans ......................................................................................................................................6-10 Indexes: Overview..........................................................................................................................................6-12 Normal B*-tree Indexes ..................................................................................................................................6-14 Index Scans ...................................................................................................................................................6-15 Index Unique Scan .........................................................................................................................................6-16 Index Range Scan..........................................................................................................................................6-17 Index Range Scan: Descending .....................................................................................................................6-19 Descending Index Range Scan ......................................................................................................................6-20 Index Range Scan: Function-Based...............................................................................................................6-21 Index Full Scan ..............................................................................................................................................6-22 Index Fast Full Scan ......................................................................................................................................6-23 Index Skip Scan .............................................................................................................................................6-24 Index Skip Scan: Example .............................................................................................................................6-26 Index Join Scan..............................................................................................................................................6-27 B*-tree Indexes and Nulls ..............................................................................................................................6-28 Using Indexes: Considering Nullable Columns ..............................................................................................6-29 Index-Organized Tables .................................................................................................................................6-30 Index-Organized Table Scans ........................................................................................................................6-32 Bitmap Indexes ..............................................................................................................................................6-33 Bitmap Index Access: Examples ....................................................................................................................6-35 Combining Bitmap Indexes: Examples ...........................................................................................................6-37 Combining Bitmap Index Access Paths .........................................................................................................6-38 Bitmap Operations .........................................................................................................................................6-39 Bitmap Join Index...........................................................................................................................................6-40 Composite Indexes ........................................................................................................................................6-42 Invisible Index: Overview ...............................................................................................................................6-43 Invisible Indexes: Examples ...........................................................................................................................6-44 Guidelines for Managing Indexes ...................................................................................................................6-45 Investigating Index Usage ..............................................................................................................................6-47 Quiz................................................................................................................................................................6-49 Summary ........................................................................................................................................................6-52 Practice 6: Overview ......................................................................................................................................6-53. le. b fera. s. n a r t n-. ahu. no a has uideฺ ) om nt G c ฺ l ai tude m g sS @ 1 hi 2 t y e ija us v u to sah. (. S. Optimizer: Join Operators ...............................................................................................................................7-1 Copyright © 2010, Oracle and/or its affiliates. All rights reserved.. Oracle Database 11g: SQL Tuning Workshop Table of Contents iv. e. ns e c li.

(7) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2010, Oracle and/or its affiliatesฺ. Optimizer: Join Operators ..............................................................................................................................7-2 Objectives ......................................................................................................................................................7-3 Join Methods ..................................................................................................................................................7-4 Nested Loops Join .........................................................................................................................................7-6 Nested Loops Join: Prefetching .....................................................................................................................7-7 Nested Loops Join: 11g Implementation ........................................................................................................7-8 Sort Merge Join ..............................................................................................................................................7-9 Hash Join .......................................................................................................................................................7-11 Cartesian Join ................................................................................................................................................7-12 Join Types ......................................................................................................................................................7-13 Equijoins and Nonequijoins ............................................................................................................................7-14 Outer Joins .....................................................................................................................................................7-15 Semijoins .......................................................................................................................................................7-17 Antijoins .........................................................................................................................................................7-18 Quiz................................................................................................................................................................7-19 Summary ........................................................................................................................................................7-23 Practice 7: Overview ......................................................................................................................................7-24. ble a r Other Optimizer Operators ..............................................................................................................................8-1 fe s n Other Optimizer Operators .............................................................................................................................8-2 tra Objectives ......................................................................................................................................................8-3 n no Clusters ..........................................................................................................................................................8-4 a When Are Clusters Useful? ............................................................................................................................8-6 has uideฺ Cluster Access Path: Examples .....................................................................................................................8-8 ) om nt G Sorting Operators ...........................................................................................................................................8-9 c ฺ l Buffer Sort Operator .......................................................................................................................................8-11 ai tude m g sS Inlist Iterator ...................................................................................................................................................8-12 @ 1 View Operator ................................................................................................................................................8-13 hi 2 t y e Count Stop Key Operator s ija...............................................................................................................................8-14 u v u o Min/Max and First Row Operators ..................................................................................................................8-15 t h aOperations s Other N-Array ..............................................................................................................................8-16 ( hu Operations ........................................................................................................................................8-17 FILTER a S ai Concatenation Operation ...............................................................................................................................8-18. Vij. e. ns e c li. UNION [ALL], INTERSECT, MINUS ..............................................................................................................8-19 Result Cache Operator ..................................................................................................................................8-20 Quiz................................................................................................................................................................8-21 Summary ........................................................................................................................................................8-25 Practice 8: Overview ......................................................................................................................................8-26. Case Study: Star Transformation ...................................................................................................................9-1 Case Study: Star Transformation ...................................................................................................................9-2 Objectives ......................................................................................................................................................9-3 The Star Schema Model ................................................................................................................................9-4 The Snowflake Schema Model.......................................................................................................................9-5 Star Query: Example ......................................................................................................................................9-6 Execution Plan Without Star Transformation .................................................................................................9-7 Star Transformation .......................................................................................................................................9-8 Star Transformation: Considerations ..............................................................................................................9-10 Star Transformation: Rewrite Example ..........................................................................................................9-11 Retrieving Fact Rows from One Dimension ...................................................................................................9-12 Retrieving Fact Rows from All Dimensions ....................................................................................................9-13 Copyright © 2010, Oracle and/or its affiliates. All rights reserved.. Oracle Database 11g: SQL Tuning Workshop Table of Contents v.

(8) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2010, Oracle and/or its affiliatesฺ. i a j i V. Joining the Intermediate Result Set with Dimensions ...................................................................................9-14 Star Transformation Plan: Example 1 ............................................................................................................9-15 Star Transformation: Further Optimization .....................................................................................................9-16 Using Bitmap Join Indexes .............................................................................................................................9-17 Star Transformation Plan: Example 2 ............................................................................................................9-18 Star Transformation Hints ..............................................................................................................................9-19 Bitmap Join Indexes: Join Model 1.................................................................................................................9-20 Bitmap Join Indexes: Join Model 2.................................................................................................................9-21 Bitmap Join Indexes: Join Model 3.................................................................................................................9-22 Bitmap Join Indexes: Join Model 4.................................................................................................................9-23 Quiz................................................................................................................................................................9-24 Summary ........................................................................................................................................................9-27 Practice 9: Overview ......................................................................................................................................9-28 Optimizer Statistics..........................................................................................................................................10-1 Optimizer Statistics ........................................................................................................................................10-2 Objectives ......................................................................................................................................................10-3 Optimizer Statistics ........................................................................................................................................10-4 Types of Optimizer Statistics ..........................................................................................................................10-5 Table Statistics (DBA_TAB_STATISTICS) ....................................................................................................10-6 Index Statistics (DBA_IND_STATISTICS) .....................................................................................................10-7 Index Clustering Factor ..................................................................................................................................10-9 Column Statistics (DBA_TAB_COL_STATISTICS) ........................................................................................10-11 Histograms .....................................................................................................................................................10-12 Frequency Histograms ...................................................................................................................................10-13 Viewing Frequency Histograms......................................................................................................................10-14 Height-Balanced Histograms .........................................................................................................................10-15 Viewing Height-Balanced Histograms ............................................................................................................10-16 Histogram Considerations ..............................................................................................................................10-17 Multicolumn Statistics: Overview ....................................................................................................................10-18 Expression Statistics: Overview .....................................................................................................................10-20 Gathering System Statistics ...........................................................................................................................10-21 Gathering System Statistics: Example ...........................................................................................................10-23 Mechanisms for Gathering Statistics ..............................................................................................................10-25 Statistic Preferences: Overview .....................................................................................................................10-26 When to Gather Statistics Manually ...............................................................................................................10-28 Manual Statistics Gathering ...........................................................................................................................10-29 Manual Statistics Collection: Factors .............................................................................................................10-30 Managing Statistics Collection: Example .......................................................................................................10-31 Optimizer Dynamic Sampling: Overview ........................................................................................................10-32 Optimizer Dynamic Sampling at Work............................................................................................................10-33 OPTIMIZER_DYNAMIC_SAMPLING.............................................................................................................10-34 Locking Statistics ...........................................................................................................................................10-36 Restoring Statistics ........................................................................................................................................10-37 Export and Import Statistics ...........................................................................................................................10-38 Quiz................................................................................................................................................................10-39 Summary ........................................................................................................................................................10-42 Practice 10: Overview ....................................................................................................................................10-43. le. b fera. s. n a r t n-. ahu. no a has uideฺ ) om nt G c ฺ l ai tude m g sS @ 1 hi 2 t y e ija us v u to sah. (. S. Using Bind Variables .......................................................................................................................................11-1 Using Bind Variables ......................................................................................................................................11-2 Copyright © 2010, Oracle and/or its affiliates. All rights reserved.. Oracle Database 11g: SQL Tuning Workshop Table of Contents vi. e. ns e c li.

(9) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2010, Oracle and/or its affiliatesฺ. i a j i V. Objectives ......................................................................................................................................................11-3 Cursor Sharing and Different Literal Values ...................................................................................................11-4 Cursor Sharing and Bind Variables ................................................................................................................11-6 Bind Variables in SQL*Plus ............................................................................................................................11-7 Bind Variables in Enterprise Manager ............................................................................................................11-8 Bind Variables in SQL Developer ...................................................................................................................11-9 Bind Variable Peeking ....................................................................................................................................11-10 Cursor Sharing Enhancements ......................................................................................................................11-12 The CURSOR_SHARING Parameter ............................................................................................................11-14 Forcing Cursor Sharing: Example ..................................................................................................................11-15 Adaptive Cursor Sharing: Overview ...............................................................................................................11-16 Adaptive Cursor Sharing: Architecture ...........................................................................................................11-17 Adaptive Cursor Sharing: Views.....................................................................................................................11-19 Adaptive Cursor Sharing: Example ................................................................................................................11-21 Interacting with Adaptive Cursor Sharing .......................................................................................................11-22 Quiz................................................................................................................................................................11-23 Summary ........................................................................................................................................................11-26 Practice 11: Overview ....................................................................................................................................11-27. le. b fera. s. n a r t n-. SQL Tuning Advisor ........................................................................................................................................12-1 SQL Tuning Advisor .......................................................................................................................................12-2 Objectives ......................................................................................................................................................12-3 Tuning SQL Statements Automatically...........................................................................................................12-4 Application Tuning Challenges .......................................................................................................................12-5 SQL Tuning Advisor: Overview ......................................................................................................................12-6 Stale or Missing Object Statistics ...................................................................................................................12-7 SQL Statement Profiling .................................................................................................................................12-8 Plan Tuning Flow and SQL Profile Creation ...................................................................................................12-9 SQL Tuning Loop ...........................................................................................................................................12-10 Access Path Analysis .....................................................................................................................................12-11 SQL Structure Analysis ..................................................................................................................................12-12 SQL Tuning Advisor: Usage Model ................................................................................................................12-13 Database Control and SQL Tuning Advisor ...................................................................................................12-14 Running SQL Tuning Advisor: Example .........................................................................................................12-15 Schedule SQL Tuning Advisor .......................................................................................................................12-16 Implementing Recommendations ...................................................................................................................12-17 Compare Explain Plan ...................................................................................................................................12-18 Quiz................................................................................................................................................................12-19 Summary ........................................................................................................................................................12-21 Practice 12: Overview ....................................................................................................................................12-22. ahu. no a has uideฺ ) om nt G c ฺ l ai tude m g sS @ 1 hi 2 t y e ija us v u to sah. (. S. Using SQL Access Advisor .............................................................................................................................13-1 Using SQL Access Advisor ............................................................................................................................13-2 Objectives ......................................................................................................................................................13-3 SQL Access Advisor: Overview......................................................................................................................13-4 SQL Access Advisor: Usage Model ...............................................................................................................13-6 Possible Recommendations ...........................................................................................................................13-8 SQL Access Advisor Session: Initial Options .................................................................................................13-10 SQL Access Advisor: Workload Source .........................................................................................................13-12 SQL Access Advisor: Recommendation Options ...........................................................................................13-13 SQL Access Advisor: Schedule and Review ..................................................................................................13-14 Copyright © 2010, Oracle and/or its affiliates. All rights reserved.. Oracle Database 11g: SQL Tuning Workshop Table of Contents vii. e. ns e c li.

(10) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2010, Oracle and/or its affiliatesฺ. SQL Access Advisor: Results ........................................................................................................................13-15 SQL Access Advisor: Results and Implementation ........................................................................................13-16 Quiz................................................................................................................................................................13-18 Summary ........................................................................................................................................................13-20 Practice 13: Overview ....................................................................................................................................13-21 Automating SQL Tuning ..................................................................................................................................14-1 Automating SQL Tuning .................................................................................................................................14-2 Objectives ......................................................................................................................................................14-3 SQL Tuning Loop ...........................................................................................................................................14-4 Automatic SQL Tuning ...................................................................................................................................14-5 Automatic Tuning Process .............................................................................................................................14-6 Automatic SQL Tuning Controls .....................................................................................................................14-8 Automatic SQL Tuning Task ..........................................................................................................................14-9 Configuring Automatic SQL Tuning ................................................................................................................14-10 Automatic SQL Tuning: Result Summary.......................................................................................................14-11 Automatic SQL Tuning: Result Details ...........................................................................................................14-12 Automatic SQL Tuning Result Details: Drilldown ...........................................................................................14-13 Automatic SQL Tuning Considerations ..........................................................................................................14-14 Quiz................................................................................................................................................................14-15 Summary ........................................................................................................................................................14-16 Practice 14: Overview ....................................................................................................................................14-17. le. b fera. s. n a r t n-. no a SQL Plan Management ....................................................................................................................................15-1 as ideฺ h ) SQL Plan Management ..................................................................................................................................15-2 m t Gu o c Objectives ......................................................................................................................................................15-3 ilฺ den a Maintaining SQL Performance .......................................................................................................................15-4 m Stu g SQL Plan Management: Overview .................................................................................................................15-5 @ this 1....................................................................................................................15-7 SQL Plan Baseline: Architecture 2 y ja .........................................................................................................................15-9 se i Loading SQL Plan Baselines u v to huBaselines.........................................................................................................................15-11 Evolving SQLa Plan s Important(Baseline SQL Plan Attributes .........................................................................................................15-12 u Selection ........................................................................................................................................15-14 hPlan SQL a S i. Vija. Possible SQL Plan Manageability Scenarios .................................................................................................15-16 SQL Performance Analyzer and SQL Plan Baseline Scenario .....................................................................15-17 Loading a SQL Plan Baseline Automatically ..................................................................................................15-18 Purging SQL Management Base Policy .........................................................................................................15-19 Enterprise Manager and SQL Plan Baselines ................................................................................................15-20 Quiz................................................................................................................................................................15-21 Summary ........................................................................................................................................................15-22 Practice 15: Overview Using SQL Plan Management ....................................................................................15-23. Using Optimizer Hints ......................................................................................................................................16-1 Using Optimizer Hints ....................................................................................................................................16-2 Objectives ......................................................................................................................................................16-3 Optimizer Hints: Overview ..............................................................................................................................16-4 Types of Hints ................................................................................................................................................16-5 Specifying Hints .............................................................................................................................................16-6 Rules for Hints................................................................................................................................................16-7 Hint Recommendations ..................................................................................................................................16-8 Optimizer Hint Syntax: Example.....................................................................................................................16-9 Hint Categories ..............................................................................................................................................16-10 Copyright © 2010, Oracle and/or its affiliates. All rights reserved.. Oracle Database 11g: SQL Tuning Workshop Table of Contents viii. e. ns e c li.

(11) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2010, Oracle and/or its affiliatesฺ. i a j i V. Optimization Goals and Approaches ..............................................................................................................16-11 Hints for Access Paths ...................................................................................................................................16-13 The INDEX_COMBINE Hint: Example ...........................................................................................................16-17 Hints for Query Transformation ......................................................................................................................16-19 Hints for Join Orders ......................................................................................................................................16-22 Hints for Join Operations ................................................................................................................................16-23 Additional Hints ..............................................................................................................................................16-25 Hints and Views .............................................................................................................................................16-28 Global Table Hints..........................................................................................................................................16-30 Specifying a Query Block in a Hint .................................................................................................................16-31 Specifying a Full Set of Hints .........................................................................................................................16-32 Summary ........................................................................................................................................................16-33 Practice Appendix B: Overview ......................................................................................................................16-34 Using SQL Developer ......................................................................................................................................17-1 Using SQL Developer ....................................................................................................................................17-2 Objectives ......................................................................................................................................................17-3 What Is Oracle SQL Developer? ....................................................................................................................17-4 Specifications of SQL Developer....................................................................................................................17-5 SQL Developer 2.1 Interface ..........................................................................................................................17-6 Creating a Database Connection ...................................................................................................................17-8 Browsing Database Objects ...........................................................................................................................17-11 Displaying the Table Structure .......................................................................................................................17-12 Browsing Files ................................................................................................................................................17-13 Creating a Schema Object .............................................................................................................................17-14 Creating a New Table: Example.....................................................................................................................17-15 Using the SQL Worksheet ..............................................................................................................................17-16 Executing SQL Statements ............................................................................................................................17-20 Saving SQL Scripts ........................................................................................................................................17-21 Executing Saved Script Files: Method 1.........................................................................................................17-22 Executing Saved Script Files: Method 2.........................................................................................................17-23 Formatting the SQL Code ..............................................................................................................................17-24 Using Snippets ...............................................................................................................................................17-25 Using Snippets: Example ...............................................................................................................................17-26 Debugging Procedures and Functions ...........................................................................................................17-27 Database Reporting .......................................................................................................................................17-28 Creating a User-Defined Report .....................................................................................................................17-30 External Tools ................................................................................................................................................17-31 Setting Preferences........................................................................................................................................17-32 Resetting the SQL Developer Layout .............................................................................................................17-33 Summary ........................................................................................................................................................17-34. le. s. b fera. n a r t n-. ahu. no a has uideฺ ) om nt G c ฺ l ai tude m g sS @ 1 hi 2 t y e ija us v u to sah. (. S. Copyright © 2010, Oracle and/or its affiliates. All rights reserved.. Oracle Database 11g: SQL Tuning Workshop Table of Contents ix. e. ns e c li.

(12) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2010, Oracle and/or its affiliatesฺ. i a j i V. e. le. n a r t ns. S ahu ( no a has uideฺ ) om nt G c ฺ l ai tude m g sS @ 1 hi 2 t y e ija us v u to sah b fera ns e c li.

(13) Preface. Unauthorized reproduction or distribution prohibitedฺ Copyright© 2010, Oracle and/or its affiliatesฺ. Profile Before You Begin This Course Before you begin this course, you should be familiar with SQL Language statements, and have taken the Oracle Database 11g: Introduction to SQL course or have equivalent experience. It is also recommended that you have taken the Oracle Database 11g: SQL Fundamentals I course. How This Course Is Organized Oracle Database 11g: SQL Tuning Workshop is an instructor-led course featuring lectures and hands-on exercises. Online demonstrations and written practice sessions reinforce the concepts and skills that are introduced.. e. Related Publications Oracle Publications Title. ble a r Part Number fe s n a. tr n no Oracle Database Performance Tuning Guide 11g Release 2 (11.2)E10821-05 a ฺ Oracle SQL Developer User's Guide Release 2.1 has uideE15222-02 ) om nt G c ฺ l ai tude m g sS @ 1 hi 2 t y e ija us v u to h a s ( u h a S i a Oracle Database SQL Reference 11g Release 2 (11.2). E10592-04. Vij. Copyright © 2010, Oracle and/or its affiliates. All rights reserved.. Oracle Database 11g: SQL Tuning Workshop Table of Contents xi. ns e c li.

(14) Typographic Conventions The following two lists explain Oracle University typographical conventions for words that appear within regular text or within code samples.. Unauthorized reproduction or distribution prohibitedฺ Copyright© 2010, Oracle and/or its affiliatesฺ. 1. Typographic Conventions for words within regular text Convention Courier new,. Initial cap. Italic. Object or Term User input; commands; column, table, and schema names; functions; PL/SQL objects; paths Triggers; user interface object names, such as button names Titles of courses and manuals; emphasized words or phrases; placeholders or variables Lesson or module title referenced within a course. Example Use the SELECT command to view information stored in the LAST_NAME column of the EMPLOYEES table. Enter 300.. e. Log in as scott Assign a When-Validate-Item trigger to the ORD block. Click the Cancel button. For more information on the subject see Oracle SQL Reference Manual. le. b fera. s. n a r t n-. Vija. no a Do not save changes toathe eฺ h s database. d i ) u m where G o Enter hostname, hostname is the t c n is to be changed lฺ depassword host a oni which the m Stisucovered in Lesson 3, “Working with g Quotation This subject is 1@ Objects.” marks h 2 t y ja use i v to for words within code samples huConventions a 2. Typographic s ( u h Object or term Example Sa i Convention Uppercase. Lowercase italic Initial cap. Commands, functions Syntax variables. SELECT employee_id FROM employees CREATE ROLE role. Forms triggers. Form module: ORD Trigger level: S_ITEM.QUANTITY item Trigger name: When-Validate-Item . . . . . . OG_ACTIVATE_LAYER (OG_GET_LAYER ('prod_pie_layer')) . . . SELECT last_name FROM employees; CREATE USER scott IDENTIFIED BY tiger;. Lowercase. Column names, table names Filenames, PL/SQL objects. Bold. Text that must be entered by a user. Copyright © 2010, Oracle and/or its affiliates. All rights reserved.. Oracle Database 11g: SQL Tuning Workshop Table of Contents xii. ns e c li.

(15) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2010, Oracle and/or its affiliatesฺ. 3. Typographic Conventions for Oracle Application Navigation Paths This course uses simplified navigation paths, such as the following example, to direct you through Oracle Applications. (N) Invoice > Entry > Invoice Batches Summary (M) Query > Find (B) Approve This simplified path translates to the following: 1. (N) From the Navigator window, select Invoice then Entry then Invoice Batches Summary. 2. (M) From the menu, select Query then Find. 3. (B) Click the Approve button. Notations: (N) = Navigator (M) = Menu (T) = Tab (B) = Button (I) = Icon (H) = Hyperlink (ST) = Sub Tab. e. le. b fera. s. n a r t n-. Vija. no a 4. Typographic Conventions for Oracle Application Paths s Help eSystem ฺ a h d i you perform to find ) Guactions This course uses a “navigation path” convention to represent m o pertinent information in the Oracle Applications ฺc HelpeSystem. nt l i a d The following help navigation path, for mexample— tu g S (Help) General Ledger > Journals > Enter Journals is 1@ ofthactions: 2 —represents the following sequence y e ja of utheshelp i v 1. In the navigation frame system window, expand the General Ledger entry. u o t h a 2. Under(s the General Ledger entry, expand Journals. u h Journals, select Enter Journals. 3. Under i Sa 4.. Review the Enter Journals topic that appears in the document frame of the help system window.. Copyright © 2010, Oracle and/or its affiliates. All rights reserved.. Oracle Database 11g: SQL Tuning Workshop Table of Contents xiii. ns e c li.

(16) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2010, Oracle and/or its affiliatesฺ. i a j i V. e. le. s. b fera. n a r t n-. ahu. no a has uideฺ ) om nt G c ฺ l ai tude m g sS @ 1 hi 2 t y e ija us v u to sah. (. S. Copyright © 2010, Oracle and/or its affiliates. All rights reserved.. Oracle Database 11g: SQL Tuning Workshop Table of Contents xiv. ns e c li.

(17) Unauthorized reproduction or distribution prohibitedฺ Copyright© 2010, Oracle and/or its affiliatesฺ. i a j i V. e. le. b fera. s. n a r t n-. Exploring the no Oracle a Database eฺ has Architecture. d. ahu. ) Gui m o Chapter c en1t ฺ l i ma Stud g 1@ this 2 y ja use i v u to sah. (. S. Copyright © 2010, Oracle and/or its affiliates. All rights reserved.. Exploring the Oracle Database Architecture Chapter 1 - Page 1. ns e c li.

Figure

Updating...

References