행위

SQL & Perfomance Tuning Course

DB CAFE

thumb_up 추천메뉴 바로가기

1 SQL & Perfomance Tuning Course[편집]

  • SQL & Performance Tuning Course Overview

1.1 What is SQL (Performance) Tuning and Why We Need?[편집]

  • 
What is SQL Tuning and Why to Do That?

  • 
What to Know Before Starting the Course?


1.2 Oracle Database Architecture[편집]

  • 
Do You Have a Running Database in Your PC?
  • 
Why to know the Oracle Database Architecture and how much to know?
  • 
Oracle Database Architecture Overview (Part 1)

  • 
Oracle Database Architecture Overview (Part 2)
  • 
Database Data Blocks in Detail
  • 
What is PGA?

  • 
What is Shared Pool?
  • 
What is Buffer Cache?
  • 
What is Redo Log Buffer?
  • 
What is Undo?
  • 
How a DML is processed and committed
  • 
Automatic Memory Management
  • 
Oracle Database Storage Architecture
  • 
Logical and Physical Database Structure
  • 
Quiz - Database Architecture

1.3 SQL Tuning Basics[편집]

  • 
When to Tune?
  • 
What is a Bad SQL?
  • 
Effective Schema Design
  • 
Table Partitioning
  • 
How an SQL Statement is Processed?
  • 
Why do we need the Optimizer?
  • 
Optimizer Overview
  • 
Query Transformer
  • 
Selectivity & Cardinality
  • 
What is "cost" in detail?
  • 
Plan Generator
  • 
Row Source Generator
  • 
SQL Tuning Principles and Strategies
  • 
Query Analysis Strategy
  • 
SQL Tuning Basics Assessment Test

1.4 Execution Plans & Statistics[편집]

  • 
Execution Plan and Explain Plan in Details
  • 
Generating Statistics (Part 1)
  • 
Generating Statistics (Part 2)
  • 
Generating Statistics (Part 3)
  • 
Generating Statistics (Code Samples)
  • 
Generating Execution Plan
  • 
Generating Execution Plan (Code Samples)
  • 
Autotrace

  • 
Autotrace (Code Samples)
  • 
V$SQL_PLAN View
  • 
V$SQL_PLAN (Code Samples)
  • 
Reading the Execution Plans (Part 1)
  • 
Reading the Execution Plans (Part 2)
  • 
Reading the Execution Plans (Code Samples)
  • 
Analyzing the Execution Plans
  • 
Analyzing the Execution Plans (Code Samples)
  • 
Execution Plans & Statistics

1.5 Table & Index Access Paths[편집]

  • 
What are Indexes and How They work in details?
  • 
Types of Table and Index Access Paths
  • 
Table Access Full
  • 
Table Access Full (Code Samples)
  • 
Table Access by ROWID
  • 
Table Access by ROWID (Code Samples)
  • 
Index Unique Scan
  • 
Index Range Scan
  • 
Index Range Scan (Code Samples)
  • 
Index Full Scan
  • 
Index Full Scan (Code Samples)
  • 
Index Fast Full Scan
  • 
Index Fast Full Scan (Code Samples)
  • 
Index Skip Scan
  • 
Index Skip Scan (Code Samples)
  • 
Index Join Scan
  • 
Index Join Scan (Code Samples)
  • 
Table & Index Access Paths

1.6 Optimizer Hints[편집]

  • 
What are Hints and Why to Use Them?
  • 
How to use Hints
  • 
How to use Hints (Code Samples)
  • 
List of Some Useful Hints
  • 
Using Hints

1.7 Join Operations[편집]

  • 
Join Methods Overview
  • 
Nested Loop Joins
  • 
Nested Loop Join (Code Samples)
  • 
Sort Merge Joins
  • 
Sort Merge Join (Code Samples)
  • 
Hash Joins
  • 
CODE: Hash Joins
  • 
Cartesian Joins
  • 
CODE: Cartesian Joins
  • 
Join Types Overview
  • 
Equijoins & Nonequijoins
  • 
CODE: Equijoins & Nonequijoins
  • 
Outer Joins
  • 
CODE: Outer Joins
  • 
Semijoins
  • 
CODE: Semijoins
  • 
Antijoins
  • 
CODE: Antijoins
  • 
Join Operations

1.8 Other Optimizer Operators[편집]

  • 
Result Cache Operator
  • 
CODE: Result Cache Operator
  • 
View Operator
  • 
CODE: View Operator
  • 
Clusters
  • 
CODE: Clusters
  • 
Sort Operators
  • 
CODE: Sort Operators
  • 
INLIST Operator
  • 
CODE: INLIST Operator
  • 
Count Stopkey Operator
  • 
CODE: Count Stopkey Operator
  • 
First Row Operator
  • 
CODE: First Row Operator
  • 
Filter Operator
  • 
CODE: Filter Operator
  • 
Concatenation Operator
  • 
CODE: Concatenation Operator
  • 
UNION Operators
  • 
CODE: Union Operators
  • 
Intersect Operator
  • 
CODE: Intersect Operator
  • 
Minus Operator
  • 
CODE: Minus Operator
  • 
Other Optimizer Operators

1.9 SQL Tuning Technique[편집]

  • 
How to find a performance problem and its tuning solution?
  • 
Ways of Getting the Execution Plan and the Statistics
  • 
Using the Real-Time SQL Monitoring Tool Part 1
  • 
Using the Real-Time SQL Monitoring Tool Part 2
  • 
Using the Real-Time SQL Monitoring Tool Part 3
  • 
CODE: Using the Real-Time SQL Monitoring Tool
  • 
Using the Trace Files & TKPROF Utility - Part 1
  • 
Using the Trace Files & TKPROF Utility - Part 2
  • 
Using the Trace Files & TKPROF Utility - Part 3
  • 
CODE: Using the Trace Files & TKPROF Utility
  • 
Get What You Need Only
  • 
CODE: Get What You Need Only
  • 
Index Usage
  • 
CODE: Index Usage
  • 
Using Concatenation Operator
  • 
CODE: Using Concatenation Operator
  • 
Using Arithmetic Operators
  • 
CODE: Using Arithmetic Operators
  • 
Using Like Conditions
  • 
CODE: Using Like Conditions
  • 
Using Functions on the Indexed Columns
  • 
CODE: Using Functions on the Indexed Columns
  • 
Handling NULL-Based Performance Problems
  • 
CODE: Handling NULL-Based Performance Problems
  • 
Using EXISTS instead of IN Clause
  • 
Using TRUNCATE instead of DELETE command
  • 
CODE: Using TRUNCATE instead of DELETE command
  • 
Data Type Mismatch
  • 
CODE: Data Type Mismatch
  • 
Tuning Ordered Queries
  • 
CODE: Tuning Ordered Queries
  • 
Retrieving the MIN & MAX Values
  • 
CODE: Retrieving the MIN & MAX Values
  • 
UNION and UNION ALL Operators (Which one is faster?)
  • 
UNION and UNION ALL Operators (Which one is faster?)
  • 
Avoid Using the HAVING Clause!
  • 
CODE: Avoid Using the HAVING Clause!
  • 
Be Careful on Views!

  • 
CODE: Be Careful on Views!
  • 
Create Materialized Views
  • 
CODE: Create Materialized Views
  • 
Avoid Commit Too Much or Too Less!
  • 
Partition Pruning
  • 
CODE: Partition Pruning
  • 
Using BULK COLLECT
  • 
CODE: Using BULK COLLECT
  • 
Tuning the Join Order
  • 
CODE: Tuning the Join Order
  • 
Multitable DML Operations
  • 
CODE: Multitable DML Operations
  • 
Using Temporary Tables
  • 
CODE: Using Temporary Tables
  • 
Combining SQL Statements
  • 
CODE: Combining SQL Statements
  • 
Using "WITH" Clause
  • 
CODE: Using WITH Clause
  • 
Using Analytical Functions
  • 
CODE: Using Analytical Functions
  • 
SQL Tuning Techniques

1.10 Tuning with ADVANCED Indexing Techniques[편집]

  • 
Why Indexing is Important?

  • 
Index Selectivity & Cardinality
  • 
B-Tree Indexes in Details
  • 
CODE: B-Tree Indexes in Details
  • 
Bitmap Indexes in Details
  • 
CODE: Bitmap Indexes in Details
  • 
Bitmap Operations
  • 
Composite Indexes and Order of Indexed Columns
  • 
CODE: Composite Indexes and Order of Indexed Columns
  • 
Covering Indexes
  • 
CODE: Covering Indexes
  • 
Reverse Key Indexes
  • 
Bitmap Join Indexes
  • 
CODE: Bitmap Join Indexes
  • 
Combining Bitmap Indexes
  • 
CODE: Combining Bitmap Indexes
  • 
Function-Based Indexes
  • 
CODE: Function-Based Indexes
  • 
Index-Organized Tables
  • 
CODE: Index-Organized Tables
  • 
Cluster Indexes
  • 
CODE: Cluster Indexes
  • 
Invisible Indexes
  • 
CODE: Invisible Indexes
  • 
Index Key Compression- Part 1
  • 
Index Key Compression- Part 2
  • 
CODE: Index Key Compression
  • 
Full-Text Searches
  • 
CODE: Full-Text Search Indexes

1.11 Advanced SQL Tuning Techniques[편집]

  • 
Tuning Star Queries
  • 
CODE: Tuning Star Queries
  • 
Using Bind Variables
  • 
CODE: Using Bind Variables
  • 
Beware of Bind Variable Peeking
  • 
CODE: Beware of Bind Variable Peeking
  • 
Cursor Sharing
  • 
CODE: Cursor Sharing
  • 
Adaptive Cursor Sharing
  • 
CODE: Adaptive Cursor Sharing
  • 
Adaptive Plans
  • 
CODE: Adaptive Plans
  • 
Dynamic Statistics (Dynamic Sampling)
  • 
CODE: Dynamic Statistics (Dynamic Sampling)

1.12 Appendix: Software Installations[편집]

  • 
About the Database Installation
  • 
The Ways to Get a Database
  • 
Option 1: Installing VMware and VirtualBox on Windows
  • 
Option 1: How to Install the Virtual Box on Mac OS X?
  • 
Option 1: Downloading Virtual Machine Image
  • 
Option 1: Configuring the VMware Virtualization Software
  • 
Option 1: Configuring the Oracle VirtualBox Virtualization Software
  • 
Option 1: Configuring SQL Developer inside of the Virtual Machine
  • 
Option 2: What is Pluggable Database?
  • 
Option 2: Downloading and Installing the Oracle Database
  • 
Option 2: Unlocking the HR Schema
  • 
Option 2: Configuring and Using Oracle SQL Developer
  • 
Option 2: Installing Sample Schemas in Oracle Database
  • 
Extra: 12c Installation
  • 
Option 2: How to Unlock the HR Schema in the Oracle Database 12c?
  • 
Option 2: Oracle Database 12c Installation into Your Computer
  • 
Option 2: Configuring and Using Oracle SQL Developer for Oracle Database 12c
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=SQL_%26_Perfomance_Tuning_Course&oldid=11711"