Giáo trình D52163GC20 | Môn thiết kế và quản trị cơ sở dữ liệu| Trường Đại học Bách Khoa Hà Nội

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.

11 6 lượt tải Tải xuống

Chia sẻ Báo cáo tài liệu

Oracle Database 11g: SQL

Tuning Workshop

Student Guide

D52163GC20

Edition 2.0

October 2010

D69160

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.

Trademark Notice

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective

owners.

Author

James Spiller, Tulika Srivastava

Technical Contributors and Reviewers

Abhinav Gupta, Branislav Valny, Clinton Shaffer, Donna Keesling, Ira Singer, Howard Bradley,

Sean Kim, Sue Harper, Teria Kidd

This book was published using:

Oracle Tutor

Oracle Database 11g: SQL Tuning Workshop Table of Contents

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

Introduction to SQL Tuning .............................................................................................................................2-1

Introduction to SQL Tuning ............................................................................................................................2-2

Objectives ......................................................................................................................................................2-3

Reasons for Inefficient SQL Performance ......................................................................................................2-4

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

Oracle Database 11g: SQL Tuning Workshop Table of Contents

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

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

Oracle Database 11g: SQL Tuning Workshop Table of Contents

iii

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

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

Oracle Database 11g: SQL Tuning Workshop Table of Contents

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

Optimizer: Join Operators ...............................................................................................................................7-1

Oracle Database 11g: SQL Tuning Workshop Table of Contents

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

Other Optimizer Operators ..............................................................................................................................8-1

Other Optimizer Operators .............................................................................................................................8-2

Objectives ......................................................................................................................................................8-3

Clusters ..........................................................................................................................................................8-4

When Are Clusters Useful? ............................................................................................................................8-6

Cluster Access Path: Examples .....................................................................................................................8-8

Sorting Operators ...........................................................................................................................................8-9

Buffer Sort Operator .......................................................................................................................................8-11

Inlist Iterator ...................................................................................................................................................8-12

View Operator ................................................................................................................................................8-13

Count Stop Key Operator ...............................................................................................................................8-14

Min/Max and First Row Operators ..................................................................................................................8-15

Other N-Array Operations ..............................................................................................................................8-16

FILTER Operations ........................................................................................................................................8-17

Concatenation Operation ...............................................................................................................................8-18

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

Oracle Database 11g: SQL Tuning Workshop Table of Contents

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

Using Bind Variables .......................................................................................................................................11-1

Using Bind Variables ......................................................................................................................................11-2

Oracle Database 11g: SQL Tuning Workshop Table of Contents

vii

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

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

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

Oracle Database 11g: SQL Tuning Workshop Table of Contents

viii

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

SQL Plan Management ....................................................................................................................................15-1

SQL Plan Management ..................................................................................................................................15-2

Objectives ......................................................................................................................................................15-3

Maintaining SQL Performance .......................................................................................................................15-4

SQL Plan Management: Overview .................................................................................................................15-5

SQL Plan Baseline: Architecture ....................................................................................................................15-7

Loading SQL Plan Baselines .........................................................................................................................15-9

Evolving SQL Plan Baselines .........................................................................................................................15-11

Important Baseline SQL Plan Attributes .........................................................................................................15-12

SQL Plan Selection ........................................................................................................................................15-14

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

Oracle Database 11g: SQL Tuning Workshop Table of Contents

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

Oracle Database 11g: SQL Tuning Workshop Table of Contents

Preface

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.

Related Publications

Oracle Publications

Title Part Number

Oracle Database SQL Reference 11g Release 2 (11.2) E10592-04

Oracle Database Performance Tuning Guide 11g Release 2 (11.2)E10821-05

Oracle SQL Developer User's Guide Release 2.1 E15222-02

Oracle Database 11g: SQL Tuning Workshop Table of Contents

xii

Typographic Conventions

The following two lists explain Oracle University typographical conventions for words that

appear within regular text or within code samples.

1. Typographic Conventions for words within regular text

Convention Object or Term Example

Courier new, User input;

commands;

column, table, and

schema names;

functions;

PL/SQL objects;

paths

Use the SELECT command to view

information stored in the LAST_NAME

column of the EMPLOYEES table.

Enter 300.

Initial cap Triggers;

user interface object

names, such as

button names

Assign a When-Validate-Item trigger to

the ORD block.

Click the Cancel button.

Italic Titles of

courses and

manuals;

emphasized

words or phrases;

placeholders or

variables

For more information on the subject see

Oracle SQL Reference Manual

Do not save changes to the database.

Enter hostname, where hostname is the

host on which the password is to be changed

Quotation

marks

Lesson or module

title referenced

within a course

This subject is covered in Lesson 3, “Working with

Objects.”

2. Typographic Conventions for words within code samples

Convention Object or term Example

Uppercase Co mmands,

functions

SELECT employee_id

FROM employees

Lowercase

italic

Syntax variables

CREATE ROLE role

Initial cap Forms triggers

Form module: ORD

Trigger level: S_ITEM.QUANTITY

item

Trigger name: When-Validate-Item

. . .

Lowercase Colum n names,

table names

Filenames,

PL/SQL objects

. . .

OG_ACTIVATE_LAYER

(OG_GET_LAYER ('prod_pie_layer'))

. . .

SELECT last_name

FROM employees;

Bold Text that must be

entered by a user

CREATE USER scott

IDENTIFIED BY tiger;

Oracle Database 11g: SQL Tuning Workshop Table of Contents

xiii

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

4. Typographic Conventions for Oracle Application Help System Paths

This course uses a “navigation path” convention to represent actions you perform to find

pertinent information in the Oracle Applications Help System.

The following help navigation path, for example—

(Help) General Ledger > Journals > Enter Journals

—represents the following sequence of actions:

1. In the navigation frame of the help system window, expand the General Ledger entry.

2. Under the General Ledger entry, expand Journals.

3. Under Journals, select Enter Journals.

4. Review the Enter Journals topic that appears in the document frame of the help system

window.

Oracle Database 11g: SQL Tuning Workshop Table of Contents

xiv

Exploring the Oracle Database Architecture

Chapter 1 - Page 1

Exploring the Oracle

Database Architecture

Chapter 1

Exploring the Oracle Database Architecture

Chapter 1 - Page 2

Exploring the Oracle Database Architecture

Chapter 1 - Page 3

Objectives

This lesson provides an overview of the Oracle Database server architecture. You learn about

physical and logical structures and about the various components.

Objectives

After completing this lesson, you should be able to:

• List the major architectural components of Oracle

Database server

• Explain memory structures

• Describe background processes

• Correlate logical and physical storage structures

Exploring the Oracle Database Architecture

Chapter 1 - Page 4

Oracle Database Server Architecture: Overview

An Oracle Database server consists of an Oracle Database and one or more Oracle Database

instances. An instance consists of memory structures and background processes. Every time

an instance is started, a shared memory area called the System Global Area (SGA) is

allocated and the background processes are started.

The SGA contains data and control information for one Oracle Database instance.

The background processes consolidate functions that would otherwise be handled by multiple

Oracle Database server programs running for each user process. They may asynchronously

perform input/output (I/O) and monitor other Oracle Database processes to provide increased

parallelism for better performance and reliability.

The database consists of physical files and logical structures discussed later in this lesson.

Because the physical and logical structures are separate, the physical storage of data can be

managed without affecting access to the logical storage structures.

Note: Oracle Real Application Clusters (Oracle RAC) comprises two or more Oracle Database

instances running on multiple clustered computers that communicates with each other by

means of an interconnect and access the same Oracle Database.

Database

Oracle Database Server Architecture: Overview

Instance

SGA

BGP2BGP1

BGPn

BGP3

Bấm Tải xuống để xem toàn bộ.

| 1/600

| | Tải xuống

Preview text:

Oracle Database 11g: SQL Tuning Workshop Student Guide D52163GC20 Edition 2.0 October 2010 D69160
Copyright © 2010, Oracle and/or its affiliates. All rights reserved. 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. Trademark Notice
Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners. Author
James Spiller, Tulika Srivastava
Technical Contributors and Reviewers
Abhinav Gupta, Branislav Valny, Clinton Shaffer, Donna Keesling, Ira Singer, Howard Bradley,
Sean Kim, Sue Harper, Teria Kidd
This book was published using: Oracle Tutor 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
Introduction to SQL Tuning .............................................................................................................................2-1
Introduction to SQL Tuning ............................................................................................................................2-2
Objectives ......................................................................................................................................................2-3
Reasons for Inefficient SQL Performance ......................................................................................................2-4
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
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
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
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
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
Copyright © 2010, Oracle and/or its affiliates. All rights reserved.
Oracle Database 11g: SQL Tuning Workshop Table of Contents iii
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
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
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
Other Optimizer Operators ..............................................................................................................................8-1
Other Optimizer Operators .............................................................................................................................8-2
Objectives ......................................................................................................................................................8-3
Clusters ..........................................................................................................................................................8-4
When Are Clusters Useful? ............................................................................................................................8-6
Cluster Access Path: Examples .....................................................................................................................8-8
Sorting Operators ...........................................................................................................................................8-9
Buffer Sort Operator .......................................................................................................................................8-11
Inlist Iterator ...................................................................................................................................................8-12
View Operator ................................................................................................................................................8-13
Count Stop Key Operator ...............................................................................................................................8-14
Min/Max and First Row Operators ..................................................................................................................8-15
Other N-Array Operations ..............................................................................................................................8-16
FILTER Operations ........................................................................................................................................8-17
Concatenation Operation ...............................................................................................................................8-18
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
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
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
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
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
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
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
SQL Plan Management ....................................................................................................................................15-1
SQL Plan Management ..................................................................................................................................15-2
Objectives ......................................................................................................................................................15-3
Maintaining SQL Performance .......................................................................................................................15-4
SQL Plan Management: Overview .................................................................................................................15-5
SQL Plan Baseline: Architecture ....................................................................................................................15-7
Loading SQL Plan Baselines .........................................................................................................................15-9
Evolving SQL Plan Baselines .........................................................................................................................15-11
Important Baseline SQL Plan Attributes .........................................................................................................15-12
SQL Plan Selection ........................................................................................................................................15-14
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
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
Copyright © 2010, Oracle and/or its affiliates. All rights reserved.
Oracle Database 11g: SQL Tuning Workshop Table of Contents ix Preface 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. Related Publications Oracle Publications Title Part Number
Oracle Database SQL Reference 11g Release 2 (11.2) E10592-04
Oracle Database Performance Tuning Guide 11g Release 2 (11.2)E10821-05
Oracle SQL Developer User's Guide Release 2.1 E15222-02
Copyright © 2010, Oracle and/or its affiliates. All rights reserved.
Oracle Database 11g: SQL Tuning Workshop Table of Contents xi Typographic Conventions
The following two lists explain Oracle University typographical conventions for words that
appear within regular text or within code samples.
1. Typographic Conventions for words within regular text Convention Object or Term Example
Courier new, User input;
Use the SELECT command to view commands;
information stored in the LAST_NAME column, table, and
column of the EMPLOYEES table. schema names; functions; Enter 300. PL/SQL objects; paths Log in as scott Initial cap Triggers;
Assign a When-Validate-Item trigger to
user interface object the ORD block. names, such as button names
Click the Cancel button. Italic Titles of
For more information on the subject see courses and
Oracle SQL Reference Manual manuals; emphasized
Do not save changes to the database. words or phrases; placeholders or
Enter hostname, where hostname is the variables
host on which the password is to be changed Quotation Lesson or module
This subject is covered in Lesson 3, “Working with marks title referenced Objects.” within a course
2. Typographic Conventions for words within code samples
Convention Object or term Example Uppercase Co mmands, SELECT employee_id functions FROM employees Lowercase Syntax variables CREATE ROLE role italic Initial cap Forms triggers Form module: ORD
Trigger level: S_ITEM.QUANTITY item
Trigger name: When-Validate-Item . . . Lowercase Colum n names, . . . table names OG_ACTIVATE_LAYER Filenames,
(OG_GET_LAYER ('prod_pie_layer')) PL/SQL objects . . . SELECT last_name FROM employees; Bold Text that must be CREATE USER scott entered by a user IDENTIFIED BY tiger;
Copyright © 2010, Oracle and/or its affiliates. All rights reserved.
Oracle Database 11g: SQL Tuning Workshop Table of Contents xii
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
4. Typographic Conventions for Oracle Application Help System Paths
This course uses a “navigation path” convention to represent actions you perform to find
pertinent information in the Oracle Applications Help System.
The following help navigation path, for example—
(Help) General Ledger > Journals > Enter Journals
—represents the following sequence of actions:
1. In the navigation frame of the help system window, expand the General Ledger entry.
2. Under the General Ledger entry, expand Journals.
3. Under Journals, select Enter Journals.
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
Copyright © 2010, Oracle and/or its affiliates. All rights reserved.
Oracle Database 11g: SQL Tuning Workshop Table of Contents xiv Exploring the Oracle Database Architecture Chapter 1
Copyright © 2010, Oracle and/or its affiliates. All rights reserved.
Exploring the Oracle Database Architecture Chapter 1 - Page 1
Exploring the Oracle Database Architecture
Exploring the Oracle Database Architecture
Copyright © 2010, Oracle and/or its affiliates. All rights reserved.
Exploring the Oracle Database Architecture Chapter 1 - Page 2 Objectives Objectives
After completing this lesson, you should be able to: •
List the major architectural components of Oracle Database server • Explain memory structures • Describe background processes •
Correlate logical and physical storage structures Objectives
This lesson provides an overview of the Oracle Database server architecture. You learn about
physical and logical structures and about the various components.
Copyright © 2010, Oracle and/or its affiliates. All rights reserved.
Exploring the Oracle Database Architecture Chapter 1 - Page 3
Oracle Database Server Architecture: Overview
Oracle Database Server Architecture: Overview Instance SGA BGP1 BGP2 BGP3 BGPn Database
Oracle Database Server Architecture: Overview
An Oracle Database server consists of an Oracle Database and one or more Oracle Database
instances. An instance consists of memory structures and background processes. Every time
an instance is started, a shared memory area called the System Global Area (SGA) is
allocated and the background processes are started.
The SGA contains data and control information for one Oracle Database instance.
The background processes consolidate functions that would otherwise be handled by multiple
Oracle Database server programs running for each user process. They may asynchronously
perform input/output (I/O) and monitor other Oracle Database processes to provide increased
parallelism for better performance and reliability.
The database consists of physical files and logical structures discussed later in this lesson.
Because the physical and logical structures are separate, the physical storage of data can be
managed without affecting access to the logical storage structures.
Note: Oracle Real Application Clusters (Oracle RAC) comprises two or more Oracle Database
instances running on multiple clustered computers that communicates with each other by
means of an interconnect and access the same Oracle Database.
Copyright © 2010, Oracle and/or its affiliates. All rights reserved.
Exploring the Oracle Database Architecture Chapter 1 - Page 4

Giáo trình D52163GC20 | Môn thiết kế và quản trị cơ sở dữ liệu| Trường Đại học Bách Khoa Hà Nội

Thông tin :

Tài liệu liên quan:

Xây dựng mô hình cho cơ sở dữ liệu | Đại học Bách Khoa Hà Nội

Tutorial: Hbase| Giáo trình môn thiết kế và quản trị cơ sở dữ liệu| Trường Đại học Bách Khoa Hà Nội

Using EXPLAIN to Write Better MySQL Queries| Giáo trình môn thiết kế và quản trị cơ sở dữ liệu| Trường Đại học Bách Khoa Hà Nội

SQL Performance Explained| Giáo trình môn thiết kế và quản trị cơ sở dữ liệu| Trường Đại học Bách Khoa Hà Nội

SQL Performance Explained Vietnamese| Giáo trình môn thiết kế và quản trị cơ sở dữ liệu| Trường Đại học Bách Khoa Hà Nội