Oracle Database 11g: SQL Tuning

Course ID

31060

Course Description

This course will equip database administrators and application developers to build efficient SQL statements and to tune database applications. When this effort is complemented by database server and PL/SQL application tuning, then a highly efficient application execution environment is created. One will learn about the internals of SQL statement execution, how to monitor the performance of such execution, and how one can influence the behavior of the database to achieve performance gains. This textbook is a mandatory reference for any database administrator or SQL database application developer.

Prerequisites

An understanding of computer concepts and experience with a graphical user interface (GUI) is essential.

Audience

The primary target audiences for this course are:
  • Senior application designers and database developers
  • PL/SQL developer
  • Database administrators
  • Web server administrators
  • System administrators
  • Implementation specialists
  • Data center support engineers

Course Content

    Tuning & the oracle database advisory framework
  • the challenges of tuning
  • performance metrics
  • management & advisory framework
  • ADDM & AWR
  • SQL tuning privileges

    Viewing & monitoring the execution plan
  • about the execution plan
  • collecting performance statistics
  • viewing the execution plan
  • real-time SQL monitoring

    Understanding the optimizer
  • optimization methods
  • optimization goals
  • optimizer_mode
  • optimizer_features_enable
  • optimizer components
  • execution plan operations

    Execution plan methods & operations
  • table access methods
  • Join methods
  • index operations
  • data operations

    Managing optimizer statistics
  • more about optimizer statistics
  • automatic maintenance tasks
  • manually gathering statistics
  • gather_table_stats()
  • gather_index_stats()
  • gather_schema_stats()
  • gather_database_stats()
  • gather_sys parameter
  • gather_dictionary_stats()
  • gather_fixed_objects_stats()
  • using historical statistics
  • dynamic sampling
  • locking statistics

    Enhanced optimizer statistics
  • about optimizer system statistics
  • manage system statistics
  • create_stat_table(), drop_stat_table()
  • create_stat_table()
  • drop_stat_table()
  • gather_system_stats()
  • get_system_stats()
  • set_system_stats()
  • import_system_stats(),
  • Export_system_stats()
  • import_system_stats()
  • export_system_stats()
  • delete_system_stats()
  • pending & published statistics

    Histograms & extended statistics
  • why are histograms needed?
  • histograms internal structure
  • manually managing histograms
  • expression statistics

    Application tracing
  • application tracing packages
  • set_SQL_trace()
  • trace_enable()
  • set_identifier()
  • em application monitoring & tuning
  • real-time monitoring
  • using trcsess & tkprof

    ADDM & the SQL tuning advisor
  • ADDM performance analysis
  • using the SQL tuning advisor
  • automatic SQL tuning
  • configuring automatic SQL tuning

    The SQL access advisor
  • using the SQL access advisor
  • SQL access advisor templates
  • performing a quick tune task
  • index database parameters
  • optimizer_index_cost_adj
  • optimizer_index_caching
  • skip_unusable_indexes
  • optimizer_use_invisible_indexes

    Plan management
  • SQL management base architecture
  • load_plans_from_SQLset()
  • load_plans_from_cursor_cache()
  • using plan baselines
  • plan management using em managing cursor sharing
  • about cursor sharing
  • bind variables & cursor sharing
  • the cursor_sharing parameter
  • finding candidates for rewrite

    Optimizer hints
  • what are hints?
  • hint explanations

For More Information

For training inquiries, call 850-308-1376

or email us at eramos@gbsi.com

Course Details

Duration - 5 days
Price - $2995.00 USD


(Discounts may apply. Call for more information.)

Course Actions

Schedule

Acceletrain Collaborative Learning Environment (formerly know as VILT) places industry certified and expert instructors, peers, learners and multi-media components into a "borderless classroom", and interactive learning environment that can span multiple physical locations. VILT combines the benefits of the traditional brick-and-mortar classroom with innovative learning techniques and the cost savings of internet-based training.