Download Oracle Database 11g SQL Tuning Workshop.pdf PDF

TitleOracle Database 11g SQL Tuning Workshop.pdf
File Size11.8 MB
Total Pages600
Table of Contents
                            Oracle Database 11g: SQLTuning Workshop Volume I (Student Guide)
Table of Contents
Preface
Chapter 1: Exploring the Oracle Database Architecture
	Exploring the Oracle Database Architecture
	Objectives
	Oracle Database Server Architecture: Overview
	Connecting to the Database Instance
	Oracle Database Memory Structures: Overview
	Database Buffer Cache
	Redo Log Buffer
	Shared Pool
	Processing a DML Statement: Example
	COMMIT Processing: Example
	Large Pool
	Java Pool and Streams Pool
	Program Global Area (PGA)
	Background Process
	Automatic Shared Memory Management
	Automated SQL Execution Memory Management
	Automatic Memory Management
	Database Storage Architecture
	Logical and Physical Database Structures
	Segments, Extents, and Blocks
	SYSTEM and SYSAUX Tablespaces
	Quiz
	Summary
	Practice 1: Overview
Chapter 2: Introduction to SQL Tuning
	Introduction to SQL Tuning
	Objectives
	Reasons for Inefficient SQL Performance
	Inefficient SQL: Examples
	Performance Monitoring Solutions
	Monitoring and Tuning Tools: Overview
	EM Performance Pages for Reactive Tuning
	Tuning Tools: Overview
	SQL Tuning Tasks: Overview
	CPU and Wait Time Tuning Dimensions
	Scalability with Application Design, Implementation, and Configuration
	Common Mistakes on Customer Systems
	Proactive Tuning Methodology
	Simplicity in Application Design
	Data Modeling
	Table Design
	Index Design
	Using Views
	SQL Execution Efficiency
	Writing SQL to Share Cursors
	Performance Checklist
	Development Environments: Overview
	What Is Oracle SQL Developer?
	Coding PL/SQL in SQL*Plus
	Quiz
	Summary
	Practice 2: Overview
Chapter 3: Introduction to the Optimizer
	Introduction to the Optimizer
	Objectives
	Structured Query Language
	SQL Statement Representation
	SQL Statement Implementation
	SQL Statement Processing: Overview
	SQL Statement Processing: Steps
	Step 1: Create a Cursor
	Step 2: Parse the Statement
	Steps 3 and 4: Describe and Define
	Steps 5 and 6: Bind and Parallelize
	Steps 7 Through 9
	SQL Statement Processing PL/SQL: Example
	SQL Statement Parsing: Overview
	Why Do You Need an Optimizer?
	Optimization During Hard Parse Operation
	Transformer: OR Expansion Example
	Transformer: Subquery Unnesting Example
	Transformer: View Merging Example
	Transformer: Predicate Pushing Example
	Transformer: Transitivity Example
	Cost-Based Optimizer
	Estimator: Selectivity
	Estimator: Cardinality
	Estimator: Cost
	Plan Generator
	Controlling the Behavior of the Optimizer
	Optimizer Features and Oracle Database Releases
	Quiz
	Summary
	Practice 3: Overview
Chapter 4: Interpreting Execution Plans
	Interpreting Execution Plans
	Objectives
	What Is an Execution Plan?
	Where to Find Execution Plans?
	Viewing Execution Plans
	The EXPLAIN PLAN Command
	The EXPLAIN PLAN Command: Example
	PLAN_TABLE
	Displaying from PLAN_TABLE: Typical
	Displaying from PLAN_TABLE: ALL
	The EXPLAIN PLAN Command
	Displaying from PLAN_TABLE: ADVANCED
	Explain Plan Using SQL Developer
	AUTOTRACE
	The AUTOTRACE Syntax
	AUTOTRACE: Examples
	AUTOTRACE: Statistics
	AUTOTRACE Using SQL Developer
	Using the V$SQL_PLAN View
	The V$SQL_PLAN Columns
	The V$SQL_PLAN_STATISTICS View
	Links Between Important Dynamic Performance Views
	Querying V$SQL_PLAN
	Automatic Workload Repository (AWR)
	Managing AWR with PL/SQL
	Important AWR Views
	Querying the AWR
	Generating SQL Reports from AWR Data
	SQL Monitoring: Overview
	SQL Monitoring Report: Example
	Interpreting an Execution Plan
	Execution Plan Interpretation: Example 1
	Execution Plan Interpretation: Example 2
	Execution Plan Interpretation: Example 3
	Reading More Complex Execution Plans
	Reviewing the Execution Plan
	Looking Beyond Execution Plans
	Quiz
	Summary
	Practice 4: Overview
Chapter 5: Application Tracing
	Application Tracing
	Objectives
	End-to-End Application Tracing Challenge
	End-to-End Application Tracing
	Location for Diagnostic Traces
	What Is a Service?
	Using Services with Client Applications
	Tracing Services
	Use Enterprise Manager to Trace Services
	Service Tracing: Example
	Session Level Tracing: Example
	Trace Your Own Session
	The trcsess Utility
	Invoking the trcsess Utility
	The trcsess Utility: Example
	SQL Trace File Contents
	SQL Trace File Contents: Example
	Formatting SQL Trace Files: Overview
	Invoking the tkprof Utility
	tkprof Sorting Options
	Output of the tkprof Command
	tkprof Output with No Index: Example
	tkprof Output with Index: Example
	Quiz
	Summary
	Practice 5: Overview
Chapter 6: Optimizer Operators
	Optimizer Operators
	Objectives
	Row Source Operations
	Main Structures and Access Paths
	Full Table Scan
	Full Table Scans: Use Cases
	ROWID Scan
	Sample Table Scans
	Indexes: Overview
	Normal B*-tree Indexes
	Index Scans
	Index Unique Scan
	Index Range Scan
	Index Range Scan: Descending
	Descending Index Range Scan
	Index Range Scan: Function-Based
	Index Full Scan
	Index Fast Full Scan
	Index Skip Scan
	Index Skip Scan: Example
	Index Join Scan
	B*-tree Indexes and Nulls
	Using Indexes: Considering Nullable Columns
	Index-Organized Tables
	Index-Organized Table Scans
	Bitmap Indexes
	Bitmap Index Access: Examples
	Combining Bitmap Indexes: Examples
	Combining Bitmap Index Access Paths
	Bitmap Operations
	Bitmap Join Index
	Composite Indexes
	Invisible Index: Overview
	Invisible Indexes: Examples
	Guidelines for Managing Indexes
	Investigating Index Usage
	Quiz
	Summary
	Practice 6: Overview
Chapter 7: Optimizer: Join Operators
	Optimizer: Join Operators
	Objectives
	Join Methods
	Nested Loops Join
	Nested Loops Join: Prefetching
	Nested Loops Join: 11g Implementation
	Sort Merge Join
	Hash Join
	Cartesian Join
	Join Types
	Equijoins and Nonequijoins
	Outer Joins
	Semijoins
	Antijoins
	Quiz
	Summary
	Practice 7: Overview
Chapter 8: Other Optimizer Operators
	Other Optimizer Operators
	Objectives
	Clusters
	When Are Clusters Useful?
	Cluster Access Path: Examples
	Sorting Operators
	Buffer Sort Operator
	Inlist Iterator
	View Operator
	Count Stop Key Operator
	Min/Max and First Row Operators
	Other N-Array Operations
	FILTER Operations
	Concatenation Operation
	UNION
	Result Cache Operator
	Quiz
	Summary
	Practice 8: Overview
Chapter 9: Case Study: Star Transformation
	Case Study: Star Transformation
	Objectives
	The Star Schema Model
	The Snowflake Schema Model
	Star Query: Example
	Execution Plan Without Star Transformation
	Star Transformation
	Star Transformation: Considerations
	Star Transformation: Rewrite Example
	Retrieving Fact Rows from One Dimension
	Retrieving Fact Rows from All Dimensions
	Joining the Intermediate Result Set  with Dimensions
	Star Transformation Plan: Example 1
	Star Transformation: Further Optimization
	Using Bitmap Join Indexes
	Star Transformation Plan: Example 2
	Star Transformation Hints
	Bitmap Join Indexes: Join Model 1
	Bitmap Join Indexes: Join Model 2
	Bitmap Join Indexes: Join Model 3
	Bitmap Join Indexes: Join Model 4
	Quiz
	Summary
	Practice 9: Overview
Chapter 10: Optimizer Statistics
	Optimizer Statistics
	Objectives
	Optimizer Statistics
	Types of Optimizer Statistics
	Table Statistics (DBA_TAB_STATISTICS)
	Index Statistics (DBA_IND_STATISTICS)
	Index Clustering Factor
	Column Statistics (DBA_TAB_COL_STATISTICS)
	Histograms
	Frequency Histograms
	Viewing Frequency Histograms
	Height-Balanced Histograms
	Viewing Height-Balanced Histograms
	Histogram Considerations
	Multicolumn Statistics: Overview
	Expression Statistics: Overview
	Gathering System Statistics
	Gathering System Statistics: Example
	Mechanisms for Gathering Statistics
	Statistic Preferences: Overview
	When to Gather Statistics Manually
	Manual Statistics Gathering
	Manual Statistics Collection: Factors
	Managing Statistics Collection: Example
	Optimizer Dynamic Sampling: Overview
	Optimizer Dynamic Sampling at Work
	OPTIMIZER_DYNAMIC_SAMPLING
	Locking Statistics
	Restoring Statistics
	Export and Import Statistics
	Quiz
	Summary
	Practice 10: Overview
Chapter 11: Using Bind Variables
	Using Bind Variables
	Objectives
	Cursor Sharing and Different Literal Values
	Cursor Sharing and Bind Variables
	Bind Variables in SQL*Plus
	Bind Variables in Enterprise Manager
	Bind Variables in SQL Developer
	Bind Variable Peeking
	Cursor Sharing Enhancements
	The CURSOR_SHARING Parameter
	Forcing Cursor Sharing: Example
	Adaptive Cursor Sharing: Overview
	Adaptive Cursor Sharing: Architecture
	Adaptive Cursor Sharing: Views
	Adaptive Cursor Sharing: Example
	Interacting with Adaptive Cursor Sharing
	Quiz
	Summary
	Practice 11: Overview
Chapter 12: SQL Tuning Advisor
	SQL Tuning Advisor
	Objectives
	Tuning SQL Statements Automatically
	Application Tuning Challenges
	SQL Tuning Advisor: Overview
	Stale or Missing Object Statistics
	SQL Statement Profiling
	Plan Tuning Flow and SQL Profile Creation
	SQL Tuning Loop
	Access Path Analysis
	SQL Structure Analysis
	SQL Tuning Advisor: Usage Model
	Database Control and SQL Tuning Advisor
	Running SQL Tuning Advisor: Example
	Schedule SQL Tuning Advisor
	Implementing Recommendations
	Compare Explain Plan
	Quiz
	Summary
	Practice 12: Overview
Chapter 13: Using SQL Access Advisor
	Using SQL Access Advisor
	Objectives
	SQL Access Advisor: Overview
	SQL Access Advisor: Usage Model
	Possible Recommendations
	SQL Access Advisor Session: Initial Options
	SQL Access Advisor: Workload Source
	SQL Access Advisor: Recommendation Options
	SQL Access Advisor: Schedule and Review
	SQL Access Advisor: Results
	SQL Access Advisor: Results and Implementation
	Quiz
	Summary
	Practice 13: Overview
Chapter 14: Automating SQL Tuning
	Automating SQL Tuning
	Objectives
	SQL Tuning Loop
	Automatic SQL Tuning
	Automatic Tuning Process
	Automatic SQL Tuning Controls
	Automatic SQL Tuning Task
	Configuring Automatic SQL Tuning
	Automatic SQL Tuning: Result Summary
	Automatic SQL Tuning: Result Details
	Automatic SQL Tuning Result Details: Drilldown
	Automatic SQL Tuning Considerations
	Quiz
	Summary
	Practice 14: Overview
Chapter 15: SQL Plan Management
	SQL Plan Management
	Objectives
	Maintaining SQL Performance
	SQL Plan Management: Overview
	SQL Plan Baseline: Architecture
	Loading SQL Plan Baselines
	Evolving SQL Plan Baselines
	Important Baseline SQL Plan Attributes
	SQL Plan Selection
	Possible SQL Plan Manageability Scenarios
	SQL Performance Analyzer and  SQL Plan Baseline Scenario
	Loading a SQL Plan Baseline Automatically
	Purging SQL Management Base Policy
	Enterprise Manager and SQL Plan Baselines
	Quiz
	Summary
	Practice 15: Overview Using SQL Plan Management
Chapter 16: (Appendix B) Using Optimizer Hints
	Using Optimizer Hints
	Objectives
	Optimizer Hints: Overview
	Types of Hints
	Specifying Hints
	Rules for Hints
	Hint Recommendations
	Optimizer Hint Syntax: Example
	Hint Categories
	Optimization Goals and Approaches
	Hints for Access Paths
	The INDEX_COMBINE Hint: Example
	Hints for Query Transformation
	Hints for Join Orders
	Hints for Join Operations
	Additional Hints
	Hints and Views
	Global Table Hints
	Specifying a Query Block in a Hint
	Specifying a Full Set of Hints
	Summary
	Practice Appendix B: Overview
Chapter 17: (Appendix C) Using SQL Developer
	Using SQL Developer
	Objectives
	What Is Oracle SQL Developer?
	Specifications of SQL Developer
	SQL Developer 2.1 Interface
	Creating a Database Connection
	Browsing Database Objects
	Displaying the Table Structure
	Browsing Files
	Creating a Schema Object
	Creating a New Table: Example
	Using the SQL Worksheet
	Executing SQL Statements
	Saving SQL Scripts
	Executing Saved Script Files: Method 1
	Executing Saved Script Files: Method 2
	Formatting the SQL Code
	Using Snippets
	Using Snippets: Example
	Debugging Procedures and Functions
	Database Reporting
	Creating a User-Defined Report
	External Tools
	Setting Preferences
	Resetting the SQL Developer Layout
	Summary
                        
Document Text Contents
Page 1

Oracle Database 11g: SQL
Tuning Workshop

Student Guide

D52163GC20

Edition 2.0

October 2010

D69160



Vija
i Sa

hu (
sah

uvij
ay2

[email protected]
gma

ilฺco
m)

has
a n

on-
tran

sfer
able

lice
nse

to u
se t

his
Stu

den
t Gu

ideฺ

U
na

ut
ho

riz
ed

r
ep

ro
du

ct
io

n
or

d
is

tr
ib

ut
io

n
pr

oh
ib

ite
dฺ

C
op

yr
ig

ht
©

2
01

0,
O

ra
cl

e
an

d/
or

it
s

af
fil

ia
te

sฺ

Page 2

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



Vija
i Sa

hu (
sah

uvij
ay2

[email protected]
gma

ilฺco
m)

has
a n

on-
tran

sfer
able

lice
nse

to u
se t

his
Stu

den
t Gu

ideฺ

U
na

ut
ho

riz
ed

r
ep

ro
du

ct
io

n
or

d
is

tr
ib

ut
io

n
pr

oh
ib

ite
dฺ

C
op

yr
ig

ht
©

2
01

0,
O

ra
cl

e
an

d/
or

it
s

af
fil

ia
te

sฺ

Page 300

Copyright © 2010, Oracle and/or its affiliates. All rights reserved.

Optimizer: Join Operators

Chapter 7 - Page 4

Join Methods



Join Methods

A row source is a set of data that can be accessed in a query. It can be a table, an index, a
nonmergeable view, or even the result set of a join tree consisting of many different objects.

A join predicate is a predicate in the WHERE clause that combines the columns of two of the
tables in the join.

A nonjoin predicate is a predicate in the WHERE clause that references only one table.

A join operation combines the output from two row sources (such as tables or views) and
returns one resulting row source (data set). The optimizer supports different join methods
such as the following:

• Nested loop join: Useful when small subsets of data are being joined and if the join
condition is an efficient way of accessing the second table

• Sort-merge join: Can be used to join rows from two independent sources. Hash joins
generally perform better than sort-merge joins. On the other hand, sort-merge joins can
perform better than hash joins if one or two row sources are already sorted.

• Hash join: Used for joining large data sets. The optimizer uses the smaller of two tables
or data sources to build a hash table on the join key in memory. It then scans the larger
table, probing the hash table to find the joined rows. This method is best used when the

Join Methods

A join:

• Defines the relationship between two row sources
• Is a method of combining data from two data sources
• Is controlled by join predicates, which define how the

objects are related
• Join methods:

– Nested loops
– Sort-merge join
– Hash join

SELECT e.ename,d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno AND

(e.job = 'ANALYST' OR e.empno = 9999);
Join predicate
Nonjoin predicate

SELECT e.ename, d.dname
FROM dept d JOIN emp e USING (deptno)
WHERE e.job = 'ANALYST' OR e.empno = 9999;

Join predicate
Nonjoin predicate

Vija
i Sa

hu (
sah

uvij
ay2

[email protected]
gma

ilฺco
m)

has
a n

on-
tran

sfer
able

lice
nse

to u
se t

his
Stu

den
t Gu

ideฺ

U
na

ut
ho

riz
ed

r
ep

ro
du

ct
io

n
or

d
is

tr
ib

ut
io

n
pr

oh
ib

ite
dฺ

C
op

yr
ig

ht
©

2
01

0,
O

ra
cl

e
an

d/
or

it
s

af
fil

ia
te

sฺ

Page 301

Copyright © 2010, Oracle and/or its affiliates. All rights reserved.

Optimizer: Join Operators

Chapter 7 - Page 5

smaller table fits in the available memory. The cost is then limited to a single read pass
over the data for the two tables.

Note: The slide shows you the same query using both the American National Standards
Institute (ANSI) and non-ANSI join syntax. The ANSI syntax is the first example.



Vija
i Sa

hu (
sah

uvij
ay2

[email protected]
gma

ilฺco
m)

has
a n

on-
tran

sfer
able

lice
nse

to u
se t

his
Stu

den
t Gu

ideฺ

U
na

ut
ho

riz
ed

r
ep

ro
du

ct
io

n
or

d
is

tr
ib

ut
io

n
pr

oh
ib

ite
dฺ

C
op

yr
ig

ht
©

2
01

0,
O

ra
cl

e
an

d/
or

it
s

af
fil

ia
te

sฺ

Page 599

Copyright © 2010, Oracle and/or its affiliates. All rights reserved.

Using SQL Developer

Chapter 17 - Page 33

Resetting the SQL Developer Layout



Resetting the SQL Developer Layout

While working with SQL Developer, if the Connections Navigator disappears or if you cannot
dock the Log window in its original place, perform the following steps to fix the problem:

1. Exit from SQL Developer.

2. Open a terminal window and use the locate command to find the location of
windowinglayout.xml.

3. Go to the directory which has windowinglayout.xml and delete it.

4. Restart SQL Developer.

Resetting the SQL Developer Layout

Vija
i Sa

hu (
sah

uvij
ay2

[email protected]
gma

ilฺco
m)

has
a n

on-
tran

sfer
able

lice
nse

to u
se t

his
Stu

den
t Gu

ideฺ

U
na

ut
ho

riz
ed

r
ep

ro
du

ct
io

n
or

d
is

tr
ib

ut
io

n
pr

oh
ib

ite
dฺ

C
op

yr
ig

ht
©

2
01

0,
O

ra
cl

e
an

d/
or

it
s

af
fil

ia
te

sฺ

Page 600

Copyright © 2010, Oracle and/or its affiliates. All rights reserved.

Using SQL Developer

Chapter 17 - Page 34

Summary



Summary

SQL Developer is a free graphical tool to simplify database development tasks. Using SQL
Developer, you can browse, create, and edit database objects. You can use SQL Worksheet
to run SQL statements and scripts. SQL Developer enables you to create and save your own
special set of reports for repeated use.

Summary

In this appendix, you should have learned how to use SQL
Developer to do the following:

• Browse, create, and edit database objects

• Execute SQL statements and scripts in SQL Worksheet

• Create and save custom reports

Vija
i Sa

hu (
sah

uvij
ay2

[email protected]
gma

ilฺco
m)

has
a n

on-
tran

sfer
able

lice
nse

to u
se t

his
Stu

den
t Gu

ideฺ

U
na

ut
ho

riz
ed

r
ep

ro
du

ct
io

n
or

d
is

tr
ib

ut
io

n
pr

oh
ib

ite
dฺ

C
op

yr
ig

ht
©

2
01

0,
O

ra
cl

e
an

d/
or

it
s

af
fil

ia
te

sฺ

Similer Documents