1Z0-117: Oracle Database 11g Release 2 - SQL Tuning

Exam Number: 1Z0-117

Exam Title: Oracle Database 11g Release 2 - SQL Tuning

Associated Certification Paths

Oracle 1Z0-117 Certification Details:

Exam Name Oracle Database 11g Release 2: SQL Tuning
Exam Code 1Z0-117
Exam Product Version Oracle Database 11g
Exam Price USD $245
Duration 150 minutes
Number of Questions 75
Passing Score 61% 
Validated Against The exam has been validated against Oracle® Database 11g
Release 2 version and Oracle® Database 12c version
Format Multiple Choice
Recommended Practice 1Z0-117 Online Practice Exam
Recommended Training Oracle Database 11g: SQL Tuning Workshop 
Oracle SQL Tuning for Developers Workshop
Oracle Database 11g: Administer a Data Warehouse
Parallel Processing in Oracle Database 11g

Oracle 1Z0-117 Certification Topics:

SQL Statement Tuning

Exploring the Oracle Database Architecture
  • Describe the major architectural components of Oracle Database server
  • Explain memory structures
  • Describe background processes
  • Correlate logical and physical storage structures
Introduction to SQL Tuning
  • Describe what attributes of a SQL statement can make it perform poorly
  • Describe the Oracle tools that can be used to tune SQL
  • Explain the tuning tasks
Introduction to the Optimizer
  • Describe the execution steps of a SQL statement
  • Explain the need for an optimizer
  • Explain the various phases of optimization
  • Control the behavior of the optimizer
Interpreting Execution Plans
  • Gather execution plans
  • Display execution plans, display xplan
  • Interpret execution plans
Application Tracing
  • Configure the SQL Trace facility to collect session statistics
  • Use the trcsess utility to consolidate SQL trace files
  • Format trace files using the tkprof utility
  • Interpret the output of the tkprof command
Optimizer Operations
  • Describe the SQL operations for tables and indexes
  • Describe the possible access paths for tables and indexes
Optimizer: Join Operations 
  • Describe the SQL operations for joins
  • Describe the possible access paths for joins
Other Optimizer Operations
  • Describe Clusters, In-List, Sorts, Filters and Set Operations
  • Use Result Cache operations
Case Study: Star Transformation
  • Define a star schema, a star query plan without transformation and a star query plan after transformation
Optimizer Statistics
  • Gather optimizer statistics
  • Gather system statistics
  • Set statistic preferences
  • Use dynamic sampling
  • Manipulate optimizer statistics
Using Bind Variables
  • Explain the benefits of using bind variables
  • Use bind peeking
  • Use adaptive cursor sharing
SQL Tuning Advisor
  • Describe statement profiling
  • Use SQL Tuning Advisor
Using SQL Access Advisor
  • Use SQL Access Advisor
Automating SQL Tuning
  • Automating SQL Tuning
SQL Plan Management
  • Manage SQL performance through changes
  • Set up SQL Plan Management
  • Set up various SQL Plan Management scenarios
Using Optimizer Hints
  • Use hints when appropriate
  • Specify hints for Optimizer mode, Query transformation, Access path, Join orders, Join methods and Views
Parallel Queries
Parallel Processing Concepts
  • Explain what parallel processing is and why is it useful
Basics of Parallel Execution
  • Describe operations that can be parallelized
  • Explain parallel execution theory
  • Understand impact of initialization parameter on parallel execution
Manual DOP Management 
  • Understand an explain plan of a parallel query
  • Understand an explain plan of parallel DML and DDL
Simplified Auto DOP 
  • Understand the new parameters of Auto DOP
  • Explain when to use Auto DOP 
  • Use Auto DOP
Statement Queuing 
  • Explain statement queuing, concurrency and DBRM
In-Memory Parallel execution 
  • Use in-memory parallel execution
Data Warehouse Administration
Partitioning Concepts
  • Explain the available partitioning strategies
  • Explain partition pruning 
  • Implement partition enhancements in star query optimization
Materialized Views
  • Use summaries to improve performance
  • Differentiate materialized view types

Oracle 1Z0-117 - Oracle Database 11g Release 2 - SQL Tuning Exam Preparation: