다른 명령
SQL & Perfomance Tuning Course
- SQL & Performance Tuning Course Overview
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?
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
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
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
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
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
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
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
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
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
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)
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