Learning Path: Professional Oracle Developer

Learning Path: Professional Oracle Developer

by Dan Hotka
Released June 2016
Publisher(s): Pearson
ISBN: 0134664027

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Video description

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

Adv_PLSQL_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.

Table of contents

  1. Introduction
    1. Oracle SQL Performance Tuning for Developers LiveLessons: Introduction
  2. Lesson 1: Oracle Database Architecture
    1. Learning Objectives
    2. 1.1 Explore how Oracle processes SQL
    3. 1.2 Understand Library Cache Internals
    4. 1.3 Handle SQL with Bind Variables handling
    5. 1.4 Explore the performance advantages of SQL using Functions
    6. 1.5 See what's new for Oracle12: SQL Directives
    7. Put it all together
  3. Lesson 2: Oracle Execution Plans/Explain Plans
    1. Learning Objectives
    2. 2.1 Understand Explain Plans
    3. 2.2 Set up and use the PLAN_TABLE
    4. 2.3 Use SQL*Plus
    5. 2.4 Use TOAD
    6. 2.5 Use SQL Developer
    7. 2.6 Use Rapid SQL
    8. 2.7 Use JS Tuner
    9. 2.8 Use SQL Trace
    10. 2.9 Get the correct information from Oracle to solve SQL performance issues
    11. Put it all together
  4. Lesson 3: Explain Plan Content
    1. Learning Objectives
    2. 3.1 Understand Explain Plan Anatomy
    3. 3.2 Explore the Oracle SQL Parsing Process
    4. 3.3 Understand the Cost-Based Optimizer steps
    5. 3.4 Explore the Explain Plan Line-Item Content
    6. 3.5 Understand Table Joins
    7. 3.6 Explore the Optimizer Choices
    8. 3.7 See common issues with poorly performing Partitioned queries
    9. 3.8 Use Driving Table Hints
    10. Put it all together
  5. Lesson 4: Indexes
    1. Learning Objectives
    2. 4.1 Understand how Indexes work
    3. 4.2 Explore the Complete Index syntax
    4. 4.3 See how the CBO chooses which Index to use
    5. 4.4 Utilize Index Monitoring
    6. 4.5 Understand Index Clustering Factor
    7. 4.6 Utilize Bitmap Indexes
    8. 4.7 Understand Index-organized tables
    9. 4.8 Explore Index tips and techniques
    10. 4.9 Use some helpful Index Hints
    11. Put it all together
  6. Lesson 5: Where Clause Processing
    1. Learning Objectives
    2. 5.1 Understand how the CBO arrives at row estimations
    3. 5.2 Explore where the math goes wrong
    4. 5.3 Use the proper data types for better performance
    5. 5.4 Look at the Where clause content
    6. Put it all together
  7. Lesson 6: Sorts/Views/Sub-Query Processing
    1. Learning Objectives
    2. 6.1 Look at SQL that has Sorts
    3. 6.2 Explore how the CBO processes views
    4. 6.3 Understand Sub-query processing
    5. 6.4 Control the Explain Plan via Sub-queries
    6. 6.5 Use Hints in Sub-queries
    7. Put it all together
  8. Lesson 7: Problem SQL Review
    1. Learning Objectives
    2. 7.1 Spot Problem SQL via Explain Plan
    3. 7.2 Review Problem SQL 1
    4. 7.3 Review Problem SQL 2
    5. 7.4 Review Problem SQL 3
    6. 7.5 Use Table Alias for code readability
    7. 7.6 Review Hints
    8. Put it all together
  9. Lesson 8: Data Types and Statistics
    1. Learning Objectives
    2. 8.1 Use the proper data types
    3. 8.2 Understand CBO Histograms
    4. 8.3 Explore Oracle12 Adaptive Statistics
    5. 8.4 Review Oracle Adaptive SQL Plans
    6. Put it all together
  10. Summary
    1. Oracle SQL Performance Tuning for Developers LiveLessons: Summary
  11. Introduction
    1. Toad for Oracle: Introduction
  12. Lesson 1: Toad for Oracle Course Overview
    1. Learning objectives
    2. 1.1 Understand the course pre-requisites
    3. 1.2 Understand the course database objects and the course download
    4. 1.3 Introduce Toad for Oracle Unleashed book
    5. Put it all together
  13. Lesson 2: Toad Configuration and Setup Option
    1. Learning objectives
    2. 2.1 Install Toad
    3. 2.2 Understand login panel options and configuration
    4. 2.3 Explore useful Toad options panels
    5. 2.4 Customize the Tool bar
    6. Put it all together
  14. Lesson 3: SQL and the Editor Window
    1. Learning objectives
    2. 3.1 Use the editor window
    3. 3.2 Work with the describe panel
    4. 3.3 Discover useful SQL code
    5. 3.4 Work with the Toad SQL history feature
    6. 3.5 Discover useful user enhancements
    7. 3.6 Introduce Toad's Code Review
    8. 3.7 Hands-on Lab
    9. Put it all together
  15. Lesson 4: The Schema Browser
    1. Learning objectives
    2. 4.1 Use the Toad schema browser panel to configure the interface LHS and RHS options
    3. 4.2 Manage vast numbers of objects
    4. 4.3 Create object wizards from navigator tree
    5. 4.4 Explore additional data grid features
    6. 4.5 Export data from the data grid
    7. 4.6 Create reports from data grids using the Fast Reports interface
    8. 4.7 Hands-on Lab
    9. Put it all together
  16. Lesson 5: PL/SQL Tips and Techniques
    1. Learning objectives
    2. 5.1 Explore PL/SQL
    3. 5.2 Work with code templates
    4. 5.3 Use the PL/SQL symbolic debugger
    5. 5.4 Hands-on Lab
    6. Put it all together
  17. Lesson 6: Toad as a SQL Tuning Tool
    1. Learning objectives
    2. 6.1 Utilize Toad Explain Plan features
    3. 6.2 Use the query viewer
    4. 6.3 Work with auto trace
    5. 6.4 Use Auto Optimize SQL/SQL Tuning Advisor
    6. 6.5 Utilize Session Browser to show SQL, wait events and locking
    7. 6.6 Work with SQL Trace (10046 trace)
    8. 6.7 Work with the PL/SQL profiler
    9. 6.8 Hands-on Lab
    10. Put it all together
  18. Lesson 7: Toad for the Database Administrator
    1. Learning objectives
    2. 7.1 Utilize Toad monitoring features for the DBA
    3. 7.2 Review Toad's tablespace management options
    4. 7.3 Work with statistics and useful monitoring assistance
    5. 7.4 Review additional DBA topics in Toad
    6. Put it all together
  19. Lesson 8: Other Useful Features of Toad
    1. Learning objectives
    2. 8.1 Explore Automation Designer
    3. 8.2 Utilize Finding Objects
    4. 8.3 Work with HTML doc generator
    5. 8.4 Use Toad's compare features
    6. 8.5 Create objects and scripts using Toad
    7. 8.6 Explore various Toad reports
    8. 8.7 Review useful keystrokes in Toad
    9. 8.8 Hands-on Lab
    10. Put it all together
  20. Lesson 9: Object and Data Relationship Features, Query Builder Features
    1. Learning objectives
    2. 9.1 Discover master/detail data browsing
    3. 9.2 Use Entity Relationship Diagram
    4. 9.3 Use Query Builder
    5. 9.4 Hands-on Lab
    6. Put it all together
  21. Summary
    1. Toad for Oracle: Summary
  22. Introduction
    1. Oracle PL/SQL Programming: Fundamentals to Advanced: Introduction
  23. Introduction
    1. Oracle PL/SQL Programming Fundamentals LiveLessons: Introduction
  24. Lesson 1: PL/SQL Course Overview
    1. Learning Objectives
    2. 1.1 Understand the course pre-requisites
    3. 1.2 Learn the Oracle database objects to be used in this course
    4. 1.3 Review the command-line SQL*Plus tool
    5. 1.4 Review SQL Developer tool
    6. 1.5 Review TOAD tool
    7. Put it all together
  25. Lesson 2: Oracle PL/SQL Overview
    1. Learning Objectives
    2. 2.1 Review Oracle architecture
    3. 2.2 Review PL/SQL overview
    4. Put it all together
  26. Lesson 3: PL/SQL Basics and Variables
    1. Learning Objectives
    2. 3.1 Explore PL/SQL basics
    3. 3.2 Use PL/SQL variables
    4. Put it all together
  27. Lesson 4: Logic Flow
    1. Learning Objectives
    2. 4.1 Understand IF/THEN/ELSE logic
    3. 4.2 Explore the CASE statement
    4. 4.3 Utilize looping syntax
    5. Put it all together
  28. Lesson 5: PL/SQL Cursors
    1. Learning Objectives
    2. 5.1 Introduce PL/SQL cursors and the implicit cursor
    3. 5.2 Explore explicit cursors
    4. Put it all together
  29. Lesson 6: PL/SQL Exception Handling
    1. Learning Objectives
    2. 6.1 Introduce PL/SQL exceptions
    3. 6.2 Explore PL/SQL exception basic syntax
    4. 6.3 Discover implicit vs explicit exceptions
    5. 6.4 Work with SQLCODE and SQLERRM features
    6. 6.5 Understand user-defined exceptions
    7. 6.6 Use application-defined exceptions
    8. Put it all together
  30. Lesson 7: PL/SQL Procedures and Functions
    1. Learning Objectives
    2. 7.1 Introduce PL/SQL procedures and functions
    3. 7.2 Explore PL/SQL procedures
    4. 7.3 Understand PL/SQL functions
    5. 7.4 Work with parameter passing
    6. Put it all together
  31. Lesson 8: PL/SQL Packages
    1. Learning Objectives
    2. 8.1 Introduce PL/SQL packages
    3. 8.2 Explore the package syntax
    4. 8.3 Review executing code in packages
    5. Put it all together
  32. Lesson 9: PL/SQL Triggers
    1. Learning Objectives
    2. 9.1 Introduce PL/SQL triggers
    3. 9.2 Explore the trigger syntax
    4. 9.3 Review the order of trigger processing and other restrictions
    5. 9.4 Work with useful examples
    6. Put it all together
  33. Lesson 10: PL/SQL New Features
    1. Learning Objectives
    2. 10.1 Introduce PL/SQL new features
    3. 10.2 Explore the new features with tips and techniques
    4. Put it all together
  34. Lesson 11: PL/SQL Collections
    1. Learning Objectives
    2. 11.1 Introduce PL/SQL collections
    3. 11.2 Explore the collection syntax
    4. 11.3 Review code examples
    5. 11.4 Explore bulk binding techniques
    6. Put it all together
  35. Lesson 12: PL/SQL Debugger, PL/SQL Profiler, PL/SQL Tuning Tips
    1. Learning Objectives
    2. 12.1 Introduce PL/SQL debugging, PL/SQL profiling, PL/SQL coding tips
    3. 12.2 Explore the TOAD debugger
    4. 12.3 Explore the SQL developer debugger
    5. 12.4 Review the PL/SQL profiler
    6. 12.5 Use the PL/SQL profiler with SQL*Plus
    7. 12.6 Use the TOAD PL/SQL profiler
    8. 12.7 Review PL/SQL coding tips
    9. Put it all together
  36. Oracle PL/SQL Programming Fundamentals LiveLessons: Summary
    1. Summary
  37. Introduction
    1. Oracle PL/SQL Advanced Programming: Introduction
  38. Lesson 1: Oracle PL/SQL Advanced Course Overview
    1. Learning objectives
    2. 1.1 Review LiveLessons course objectives and pre-requisites
    3. 1.2 Understand the course database objects and the course download
    4. 1.3 Introduce the course tools
    5. 1.4 Review the Oracle Architecture
    6. 1.5 Explore the PL/SQL Architecture
    7. Put it all together
  39. Lesson 2: PL/SQL Compiler Options: Part I
    1. Learning objectives
    2. 2.1 Introduce function/procedure/package advanced syntax
    3. 2.2 Review Oracle11 result cache
    4. 2.3 Explore PL/SQL limits including Oracle12 new limits
    5. 2.4 Hands-on Lab
    6. 2.5 Work with identity columns
    7. 2.6 Utilize sequences
    8. 2.7 Understand date fields
    9. 2.8 Utilize definer rights
    10. 2.9 Discover Oracle12 select lists
    11. 2.10 Explore Oracle12 new privilege features
    12. 2.11 Work with Autonomous Transactions
    13. 2.12 Explore compiling with Purity features
    14. 2.13 Hands-on Lab
    15. Put it all together
  40. Lesson 3: PL/SQL Compiler Options: Part II
    1. Learning objectives
    2. 3.1 Invoke the PL/SQL compiler
    3. 3.2 Utilize compiler warnings
    4. 3.3 Work with inlining
    5. 3.4 Use the conditional compilation
    6. 3.5 Review Oracle12 compiler directives
    7. 3.6 Introduce native compilation
    8. 3.7 Hands-on Lab
    9. Put it all together
  41. Lesson 4: PL/SQL Compiler Options and Packages
    1. Learning objectives
    2. 4.1 Review PL/SQL packages syntax
    3. 4.2 Work with PL/SQL packages Pragma declarations
    4. 4.3 Introduce PL/SQL packages optional block
    5. 4.4 Discover PL/SQL packages forward declaration
    6. 4.5 Hands-on Lab
    7. Put it all together
  42. Lesson 5: PL/SQL Coding Style and New Features
    1. Learning objectives
    2. 5.1 View PL/SQL source code
    3. 5.2 Work with data dictionary information
    4. 5.3 Use source code encryption
    5. 5.4 Work with overloading
    6. 5.5 Review bodiless package
    7. 5.6 Discover code dependencies
    8. 5.7 Explore new PL/SQL syntax
    9. 5.8 Hands-on Lab
    10. Put it all together
  43. Lesson 6: Oracle Database Triggers
    1. Learning objectives
    2. 6.1 Introduce Oracle triggers
    3. 6.2 Explore trigger types and syntax
    4. 6.3 Review trigger restrictions
    5. 6.4 Work with trigger management
    6. 6.5 Understand trigger new features: updateable views
    7. 6.6 Hands-on Lab
    8. 6.7 Review triggers that audit
    9. 6.8 Explore event triggers
    10. 6.9 Hands-on Lab
    11. Put it all together
  44. Lesson 7: Cursors
    1. Learning objectives
    2. 7.1 Introduce reference cursors (ref cursors)
    3. 7.2 Explore ref cursor syntax
    4. 7.3 Work with implementation of ref cursors
    5. 7.4 Review working examples
    6. 7.5 Discover cursor sharing
    7. 7.6 Utilize cursor parameters
    8. 7.7 Hands-on Lab
    9. Put it all together
  45. Lesson 8: PL/SQL Collections
    1. Learning objectives
    2. 8.1 Introduce user-defined types
    3. 8.2 Understand collection types and methods
    4. 8.3 Explore Associative Arrays description and syntax
    5. 8.4 Discover Nested Tables description and syntax
    6. 8.5 Work with Varrays description and syntax
    7. 8.6 Hands-on Lab
    8. 8.7 Introduce bulk binding
    9. 8.8 Use BULK COLLECT
    10. 8.9 Work with FORALL
    11. 8.10 Explore collection exception processing
    12. 8.11 Hands-on Lab
    13. 8.12 Review array timings and comparisons
    14. 8.13 Discover collections tips and techniques
    15. 8.14 Hands-on Lab
    16. Put it all together
  46. Lesson 9: Oracle Built-in Packages
    1. Learning objectives
    2. 9.1 Discover built-in packages
    3. 9.2 Understand DBMS_OUTPUT package
    4. 9.3 Work with UTL_FILE package
    5. 9.4 Use the DBMS_UTILITY package
    6. 9.5 Utilize DBMS_APPLICATION_INFO package
    7. 9.6 Review Oracle12 new features
    8. 9.7 Hands-on Lab
    9. 9.8 Discover DBMS_ALERT package
    10. 9.9 Utilize DBMS_PIPE technology
    11. 9.10 Review UTL_MAIL
    12. 9.11 Hands-on Lab
    13. 9.12 Discover DBMS_JOB and DBMS_SCHEDULER
    14. 9.13 Explore DBMS_LOCK package
    15. 9.14 Utilize DBMS_RANDOM number generators
    16. 9.15 Hands-on Lab
    17. 9.16 Review DBMS_STATS package with collections
    18. Put it all together
  47. Lesson 10: Dynamic SQL
    1. Learning objectives
    2. 10.1 Introduce dynamic SQL in the Oracle RDBMS
    3. 10.2 Explore DBMS_SQL package
    4. 10.3 Hands-on Lab
    5. 10.4 Work with Native Dynamic SQL (Execute Immediate)
    6. 10.5 Review SQL injection and other malicious attacks
    7. 10.6 Discover SQL creating SQL tips and techniques
    8. 10.7 Hands-on Lab
    9. Put it all together
  48. Lesson 11: PL/SQL Performance
    1. Learning objectives
    2. 11.1 Introduce PL/SQL table functions
    3. 11.2 Work with Pipelined Table functions
    4. 11.3 Explore Cursors and Pipelined Table functions
    5. 11.4 Discover Chaining Pipelined Table functions
    6. 11.5 Review Compilers Options (function performance perspective)
    7. 11.6 Hands-on Lab
    8. 11.7 Introduce updating large tables in parallel
    9. 11.8 Utilize Parallel Execute Chunk Processing
    10. 11.9 Explore Parallel Execute Chunk Management Code Examples
    11. Put it all together
  49. Lesson 12: Large Objects
    1. Learning objectives
    2. 12.1 Understand large object processing
    3. 12.2 Review syntax and working examples
    4. 12.3 Hands-on Lab
    5. 12.4 Work with clobs and BFILES
    6. 12.5 Explore additional code resources
    7. 12.6 Utilize code examples
    8. 12.7 Hands-on Lab
    9. Put it all together
  50. Lesson 13: PL/SQL Debugger, PL/SQL Profiler, PL/SQL Tuning Tips
    1. Learning objectives
    2. 13.1 Introduce PL/SQL debugging, PL/SQL profiling, PL/SQL coding tips
    3. 13.2 Work with the PL/SQL Trace
    4. 13.3 Explore the Toad debugger with live demonstration
    5. 13.4 Explore the SQL Developer debugger with live demonstration
    6. 13.5 Use the PL/SQL profiler with SQL*Plus
    7. 13.6 Use the Toad PL/SQL profiler
    8. 13.7 Review the Hierarchical profiler
    9. 13.8 Watch the live profiling demonstration
    10. 13.9 Review PL/SQL coding tips
    11. Put it all together
  51. Summary
    1. Oracle PL/SQL Advanced Programming: Summary
  52. Summary
    1. Oracle PL/SQL Programming: Fundamentals to Advanced: Summary

Product information

  • Title: Learning Path: Professional Oracle Developer
  • Author(s): Dan Hotka
  • Release date: June 2016
  • Publisher(s): Pearson
  • ISBN: 0134664027