This Learning Path includes Oracle SQL Performance Tuning for Developers LiveLessons, Oracle PL/SQL Advanced Programming LiveLessons, and Toad for Oracle LiveLessons.
Prerequisites:
Working knowledge of the SQL query language
Overview:
The focus of Oracle SQL Performance Tuning for Developers LiveLessons is to illustrate
coding techniques that ensure a consistent response time between instances and releases
of the Oracle database. …
This Learning Path includes Oracle SQL Performance Tuning for Developers LiveLessons, Oracle PL/SQL Advanced Programming LiveLessons, and Toad for Oracle LiveLessons.
Prerequisites:
Working knowledge of the SQL query language
Overview:
The focus of Oracle SQL Performance Tuning for Developers LiveLessons is to illustrate
coding techniques that ensure a consistent response time between instances and releases
of the Oracle database. This course works closely with performance tuning of actual
SQL statements. Oracle PL/SQL Advanced Programming LiveLessons teaches developers
with PL/SQL experience the new and advanced features of the PL/SQL language along
with performance tuning techniques. Toad for Oracle LiveLessons video training covers
the latest features of TOAD necessary to view, extract, and manipulate data within
the Oracle database. This course is designed for both the developer using Toad to
aid in programming Oracle objects, as well as for the business analyst using Toad
as a query tool for data extraction and analysis. There is even a complete lesson
for the database administrator.
Skill Level:
Beginner-to-Intermediate
Downloads:
Oracle_SQL_LiveLessons_File_Download.zip
PLSQL_Fundamentals_LiveLessons.zip
Toad_Live_Lessons_Download.zip
About the Instructor
Dan Hotka is a training specialist and an Oracle ACE director with more than 37 years in the computer industry and more than 31 years of experience with Oracle products. His experience with the Oracle RDBMS dates back to the Oracle V4.0 days. Dan enjoys sharing his knowledge of the Oracle RDBMS. Dan is well-published with 12 Oracle books and over 200 published articles. He is also the video author for Oracle SQL Performance Tuning for Developers LiveLessons, Oracle SQL, and Oracle PL/SQL Programming Fundamentals. He is frequently published in Oracle trade journals, regularly blogs, and speaks at Oracle conferences and user groups around the world. Visit his website at www.DanHotka.com.
About LiveLessons
The LiveLessons Video Training series publishes hundreds of hands-on, expert-led video tutorials covering a wide selection of technology topics designed to teach you the skills you need to succeed. This professional and personal technology video series features world-leading author instructors published by your trusted technology brands: Addison-Wesley, Cisco Press, IBM Press, Pearson IT Certification, Prentice Hall, Sams, and Que. Topics include: IT Certification, Programming, Web Development, Mobile Development, Home and Office Technologies, Business and Management, and more. View all LiveLessons on InformIT at: http://www.informit.com/livelessons.
Introduction
Oracle SQL Performance Tuning for Developers LiveLessons: Introduction
Lesson 1: Oracle Database Architecture
Learning Objectives
1.1 Explore how Oracle processes SQL
1.2 Understand Library Cache Internals
1.3 Handle SQL with Bind Variables handling
1.4 Explore the performance advantages of SQL using Functions
1.5 See what’s new for Oracle12: SQL Directives
Put it all together
Lesson 2: Oracle Execution Plans/Explain Plans
Learning Objectives
2.1 Understand Explain Plans
2.2 Set up and use the PLAN_TABLE
2.3 Use SQL*Plus
2.4 Use TOAD
2.5 Use SQL Developer
2.6 Use Rapid SQL
2.7 Use JS Tuner
2.8 Use SQL Trace
2.9 Get the correct information from Oracle to solve SQL performance issues
Put it all together
Lesson 3: Explain Plan Content
Learning Objectives
3.1 Understand Explain Plan Anatomy
3.2 Explore the Oracle SQL Parsing Process
3.3 Understand the Cost-Based Optimizer steps
3.4 Explore the Explain Plan Line-Item Content
3.5 Understand Table Joins
3.6 Explore the Optimizer Choices
3.7 See common issues with poorly performing Partitioned queries
3.8 Use Driving Table Hints
Put it all together
Lesson 4: Indexes
Learning Objectives
4.1 Understand how Indexes work
4.2 Explore the Complete Index syntax
4.3 See how the CBO chooses which Index to use
4.4 Utilize Index Monitoring
4.5 Understand Index Clustering Factor
4.6 Utilize Bitmap Indexes
4.7 Understand Index-organized tables
4.8 Explore Index tips and techniques
4.9 Use some helpful Index Hints
Put it all together
Lesson 5: Where Clause Processing
Learning Objectives
5.1 Understand how the CBO arrives at row estimations
5.2 Explore where the math goes wrong
5.3 Use the proper data types for better performance
5.4 Look at the Where clause content
Put it all together
Lesson 6: Sorts/Views/Sub-Query Processing
Learning Objectives
6.1 Look at SQL that has Sorts
6.2 Explore how the CBO processes views
6.3 Understand Sub-query processing
6.4 Control the Explain Plan via Sub-queries
6.5 Use Hints in Sub-queries
Put it all together
Lesson 7: Problem SQL Review
Learning Objectives
7.1 Spot Problem SQL via Explain Plan
7.2 Review Problem SQL 1
7.3 Review Problem SQL 2
7.4 Review Problem SQL 3
7.5 Use Table Alias for code readability
7.6 Review Hints
Put it all together
Lesson 8: Data Types and Statistics
Learning Objectives
8.1 Use the proper data types
8.2 Understand CBO Histograms
8.3 Explore Oracle12 Adaptive Statistics
8.4 Review Oracle Adaptive SQL Plans
Put it all together
Summary
Oracle SQL Performance Tuning for Developers LiveLessons: Summary
Introduction
Toad for Oracle: Introduction
Lesson 1: Toad for Oracle Course Overview
Learning objectives
1.1 Understand the course pre-requisites
1.2 Understand the course database objects and the course download
1.3 Introduce Toad for Oracle Unleashed book
Put it all together
Lesson 2: Toad Configuration and Setup Option
Learning objectives
2.1 Install Toad
2.2 Understand login panel options and configuration
2.3 Explore useful Toad options panels
2.4 Customize the Tool bar
Put it all together
Lesson 3: SQL and the Editor Window
Learning objectives
3.1 Use the editor window
3.2 Work with the describe panel
3.3 Discover useful SQL code
3.4 Work with the Toad SQL history feature
3.5 Discover useful user enhancements
3.6 Introduce Toad’s Code Review
3.7 Hands-on Lab
Put it all together
Lesson 4: The Schema Browser
Learning objectives
4.1 Use the Toad schema browser panel to configure the interface LHS and RHS options
4.2 Manage vast numbers of objects
4.3 Create object wizards from navigator tree
4.4 Explore additional data grid features
4.5 Export data from the data grid
4.6 Create reports from data grids using the Fast Reports interface
4.7 Hands-on Lab
Put it all together
Lesson 5: PL/SQL Tips and Techniques
Learning objectives
5.1 Explore PL/SQL
5.2 Work with code templates
5.3 Use the PL/SQL symbolic debugger
5.4 Hands-on Lab
Put it all together
Lesson 6: Toad as a SQL Tuning Tool
Learning objectives
6.1 Utilize Toad Explain Plan features
6.2 Use the query viewer
6.3 Work with auto trace
6.4 Use Auto Optimize SQL/SQL Tuning Advisor
6.5 Utilize Session Browser to show SQL, wait events and locking
6.6 Work with SQL Trace (10046 trace)
6.7 Work with the PL/SQL profiler
6.8 Hands-on Lab
Put it all together
Lesson 7: Toad for the Database Administrator
Learning objectives
7.1 Utilize Toad monitoring features for the DBA
7.2 Review Toad’s tablespace management options
7.3 Work with statistics and useful monitoring assistance
7.4 Review additional DBA topics in Toad
Put it all together
Lesson 8: Other Useful Features of Toad
Learning objectives
8.1 Explore Automation Designer
8.2 Utilize Finding Objects
8.3 Work with HTML doc generator
8.4 Use Toad’s compare features
8.5 Create objects and scripts using Toad
8.6 Explore various Toad reports
8.7 Review useful keystrokes in Toad
8.8 Hands-on Lab
Put it all together
Lesson 9: Object and Data Relationship Features, Query Builder Features
Learning objectives
9.1 Discover master/detail data browsing
9.2 Use Entity Relationship Diagram
9.3 Use Query Builder
9.4 Hands-on Lab
Put it all together
Summary
Toad for Oracle: Summary
Introduction
Oracle PL/SQL Programming: Fundamentals to Advanced: Introduction
Introduction
Oracle PL/SQL Programming Fundamentals LiveLessons: Introduction
Lesson 1: PL/SQL Course Overview
Learning Objectives
1.1 Understand the course pre-requisites
1.2 Learn the Oracle database objects to be used in this course
1.3 Review the command-line SQL*Plus tool
1.4 Review SQL Developer tool
1.5 Review TOAD tool
Put it all together
Lesson 2: Oracle PL/SQL Overview
Learning Objectives
2.1 Review Oracle architecture
2.2 Review PL/SQL overview
Put it all together
Lesson 3: PL/SQL Basics and Variables
Learning Objectives
3.1 Explore PL/SQL basics
3.2 Use PL/SQL variables
Put it all together
Lesson 4: Logic Flow
Learning Objectives
4.1 Understand IF/THEN/ELSE logic
4.2 Explore the CASE statement
4.3 Utilize looping syntax
Put it all together
Lesson 5: PL/SQL Cursors
Learning Objectives
5.1 Introduce PL/SQL cursors and the implicit cursor
5.2 Explore explicit cursors
Put it all together
Lesson 6: PL/SQL Exception Handling
Learning Objectives
6.1 Introduce PL/SQL exceptions
6.2 Explore PL/SQL exception basic syntax
6.3 Discover implicit vs explicit exceptions
6.4 Work with SQLCODE and SQLERRM features
6.5 Understand user-defined exceptions
6.6 Use application-defined exceptions
Put it all together
Lesson 7: PL/SQL Procedures and Functions
Learning Objectives
7.1 Introduce PL/SQL procedures and functions
7.2 Explore PL/SQL procedures
7.3 Understand PL/SQL functions
7.4 Work with parameter passing
Put it all together
Lesson 8: PL/SQL Packages
Learning Objectives
8.1 Introduce PL/SQL packages
8.2 Explore the package syntax
8.3 Review executing code in packages
Put it all together
Lesson 9: PL/SQL Triggers
Learning Objectives
9.1 Introduce PL/SQL triggers
9.2 Explore the trigger syntax
9.3 Review the order of trigger processing and other restrictions
9.4 Work with useful examples
Put it all together
Lesson 10: PL/SQL New Features
Learning Objectives
10.1 Introduce PL/SQL new features
10.2 Explore the new features with tips and techniques
Put it all together
Lesson 11: PL/SQL Collections
Learning Objectives
11.1 Introduce PL/SQL collections
11.2 Explore the collection syntax
11.3 Review code examples
11.4 Explore bulk binding techniques
Put it all together
Lesson 12: PL/SQL Debugger, PL/SQL Profiler, PL/SQL Tuning Tips
Learning Objectives
12.1 Introduce PL/SQL debugging, PL/SQL profiling, PL/SQL coding tips
12.2 Explore the TOAD debugger
12.3 Explore the SQL developer debugger
12.4 Review the PL/SQL profiler
12.5 Use the PL/SQL profiler with SQL*Plus
12.6 Use the TOAD PL/SQL profiler
12.7 Review PL/SQL coding tips
Put it all together
Oracle PL/SQL Programming Fundamentals LiveLessons: Summary
Summary
Introduction
Oracle PL/SQL Advanced Programming: Introduction
Lesson 1: Oracle PL/SQL Advanced Course Overview
Learning objectives
1.1 Review LiveLessons course objectives and pre-requisites
1.2 Understand the course database objects and the course download
1.3 Introduce the course tools
1.4 Review the Oracle Architecture
1.5 Explore the PL/SQL Architecture
Put it all together
Lesson 2: PL/SQL Compiler Options: Part I
Learning objectives
2.1 Introduce function/procedure/package advanced syntax
2.2 Review Oracle11 result cache
2.3 Explore PL/SQL limits including Oracle12 new limits
2.4 Hands-on Lab
2.5 Work with identity columns
2.6 Utilize sequences
2.7 Understand date fields
2.8 Utilize definer rights
2.9 Discover Oracle12 select lists
2.10 Explore Oracle12 new privilege features
2.11 Work with Autonomous Transactions
2.12 Explore compiling with Purity features
2.13 Hands-on Lab
Put it all together
Lesson 3: PL/SQL Compiler Options: Part II
Learning objectives
3.1 Invoke the PL/SQL compiler
3.2 Utilize compiler warnings
3.3 Work with inlining
3.4 Use the conditional compilation
3.5 Review Oracle12 compiler directives
3.6 Introduce native compilation
3.7 Hands-on Lab
Put it all together
Lesson 4: PL/SQL Compiler Options and Packages
Learning objectives
4.1 Review PL/SQL packages syntax
4.2 Work with PL/SQL packages Pragma declarations
4.3 Introduce PL/SQL packages optional block
4.4 Discover PL/SQL packages forward declaration
4.5 Hands-on Lab
Put it all together
Lesson 5: PL/SQL Coding Style and New Features
Learning objectives
5.1 View PL/SQL source code
5.2 Work with data dictionary information
5.3 Use source code encryption
5.4 Work with overloading
5.5 Review bodiless package
5.6 Discover code dependencies
5.7 Explore new PL/SQL syntax
5.8 Hands-on Lab
Put it all together
Lesson 6: Oracle Database Triggers
Learning objectives
6.1 Introduce Oracle triggers
6.2 Explore trigger types and syntax
6.3 Review trigger restrictions
6.4 Work with trigger management
6.5 Understand trigger new features: updateable views
6.6 Hands-on Lab
6.7 Review triggers that audit
6.8 Explore event triggers
6.9 Hands-on Lab
Put it all together
Lesson 7: Cursors
Learning objectives
7.1 Introduce reference cursors (ref cursors)
7.2 Explore ref cursor syntax
7.3 Work with implementation of ref cursors
7.4 Review working examples
7.5 Discover cursor sharing
7.6 Utilize cursor parameters
7.7 Hands-on Lab
Put it all together
Lesson 8: PL/SQL Collections
Learning objectives
8.1 Introduce user-defined types
8.2 Understand collection types and methods
8.3 Explore Associative Arrays description and syntax
8.4 Discover Nested Tables description and syntax
8.5 Work with Varrays description and syntax
8.6 Hands-on Lab
8.7 Introduce bulk binding
8.8 Use BULK COLLECT
8.9 Work with FORALL
8.10 Explore collection exception processing
8.11 Hands-on Lab
8.12 Review array timings and comparisons
8.13 Discover collections tips and techniques
8.14 Hands-on Lab
Put it all together
Lesson 9: Oracle Built-in Packages
Learning objectives
9.1 Discover built-in packages
9.2 Understand DBMS_OUTPUT package
9.3 Work with UTL_FILE package
9.4 Use the DBMS_UTILITY package
9.5 Utilize DBMS_APPLICATION_INFO package
9.6 Review Oracle12 new features
9.7 Hands-on Lab
9.8 Discover DBMS_ALERT package
9.9 Utilize DBMS_PIPE technology
9.10 Review UTL_MAIL
9.11 Hands-on Lab
9.12 Discover DBMS_JOB and DBMS_SCHEDULER
9.13 Explore DBMS_LOCK package
9.14 Utilize DBMS_RANDOM number generators
9.15 Hands-on Lab
9.16 Review DBMS_STATS package with collections
Put it all together
Lesson 10: Dynamic SQL
Learning objectives
10.1 Introduce dynamic SQL in the Oracle RDBMS
10.2 Explore DBMS_SQL package
10.3 Hands-on Lab
10.4 Work with Native Dynamic SQL (Execute Immediate)
10.5 Review SQL injection and other malicious attacks
10.6 Discover SQL creating SQL tips and techniques
10.7 Hands-on Lab
Put it all together
Lesson 11: PL/SQL Performance
Learning objectives
11.1 Introduce PL/SQL table functions
11.2 Work with Pipelined Table functions
11.3 Explore Cursors and Pipelined Table functions
11.4 Discover Chaining Pipelined Table functions
11.5 Review Compilers Options (function performance perspective)
11.6 Hands-on Lab
11.7 Introduce updating large tables in parallel
11.8 Utilize Parallel Execute Chunk Processing
11.9 Explore Parallel Execute Chunk Management Code Examples
Put it all together
Lesson 12: Large Objects
Learning objectives
12.1 Understand large object processing
12.2 Review syntax and working examples
12.3 Hands-on Lab
12.4 Work with clobs and BFILES
12.5 Explore additional code resources
12.6 Utilize code examples
12.7 Hands-on Lab
Put it all together
Lesson 13: PL/SQL Debugger, PL/SQL Profiler, PL/SQL Tuning Tips
Learning objectives
13.1 Introduce PL/SQL debugging, PL/SQL profiling, PL/SQL coding tips
13.2 Work with the PL/SQL Trace
13.3 Explore the Toad debugger with live demonstration
13.4 Explore the SQL Developer debugger with live demonstration
13.5 Use the PL/SQL profiler with SQL*Plus
13.6 Use the Toad PL/SQL profiler
13.7 Review the Hierarchical profiler
13.8 Watch the live profiling demonstration
13.9 Review PL/SQL coding tips
Put it all together
Summary
Oracle PL/SQL Advanced Programming: Summary
Summary
Oracle PL/SQL Programming: Fundamentals to Advanced: Summary