Oracle Database 11g: Architecture, SQL Tuning, and Optimization

• recognize Oracle database structures
• recognize how to manage memory and database structures
• identify SQL statements that cause poor performance
• identify tools and tasks in SQL tuning
• recognize how SQL statement execution works
• recognize the basic features of the optimizer
• avoid common SQL mistakes
• recognize optimizer decisions in a given scenario

Oracle Database 11g: Indexes, Access Paths, and Join Operations

• identify the functions of table scans in the optimizer
• recognize types of indexes and how index scans work
• recognize the features of index-organized tables and bitmap and composite indexes
• use different acces paths
• recognize how clusters and sorting operators work
• recognize how joins and N-array operations work
• recognize how to cache query results in the SGA

Oracle Database 11g: Execution Plans and Star Transformations

• recognize how to gather execution plans
• recognize how to display execution plans
• recognize the views containing execution plan information
• recognize how to use the AWR
• recognize how to perform SQL monitoring
• interpret execution plans
• extract an execution plan used by the optimizer to execute a query
• recognize how to use star transformation
• use star transformation and access the benefits of using this optimizer technique

Oracle Database 11g: Optimizer Statistics, Hints, and Cursor Sharing

• recognize how to gather optimizer statistics
• recognize how to gather system statistics
• use dynamic sampling
• use system statistics
• identify the features of bind variables
• use cursor sharing
• use optimizer hints
• use hints and views
• recognize how adaptive cursor sharing, the CURSOR_SHARING initialization parameter, and hints
• are used

Oracle Database 11g: Application Tracing and Automatic SQL Tuning

• recognize how to configure the SQL trace facility
• recognize how to use the trcsess utility
• recognize how to use the tkprof utility
• perform application tracing
• recognize how statement profiling works
• recognize how to use the SQL Tuning Advisor
• recognize the features of the SQL Access Advisor
• recognize the features of Automatic SQL Tuning
• perform SQL tuning and use the SQL Access Advisor