by | Released March 2015 | ISBN: 0134117050

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.DescriptionIn this video training, Dan Hotka starts out with a complete overview of the Oracle architecture so students can get an understanding how their SQL and applications can take advantage of the computing environment. This course then goes in-depth on understanding and controlling the Explain Plan, which is how and in what order Oracle retrieves data. The discussion includes considerable detail, with SQL examples, on how the optimizers—both rule-based and cost-based, but mostly cost-based—make their decisions. Students will work with a variety of SQL statements, reviewing Explain Plans and making changes to make these SQL statements perform better. Lectures include index design, using hints and coding style to control the Explain Plans, and how to use useful tools such as index monitoring, SQL Trace, and the PL/SQL profiler.This LiveLessons course takes a close look at indexes: how Oracle selects them, why they are sometimes not used, and how to tell if indexes are being used.This course includes Oracle10g, Oracle11g, and Oracle12c SQL tuning topics.The source code repository for this LiveLesson can be downloaded from www.informit.com/title/9780134117027.About the InstructorDan Hotka is a training specialist and an Oracle ACE director who has more than 36 years in the computer industry and more than 30 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 well over 200 published articles. 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. Dan can be reached at [email protected] LevelIntermediateLearn How ToRead and understand Explain Plan contentReview an Explain Plan and tell quickly if this is a good planUnderstand a good index column candidate from a not-so-good candidateQuickly tell the likelihood if your SQL will use an existing indexUse coding and a variety of Hints (directives) that can produce better performing SQLExecute and interpret SQL trace outputWho Should Take This CourseOracle programmersOracle database administrators who need additional training on SQL tuningCourse RequirementsWorking knowledge of the SQL query languageAbout LiveLessons Video TrainingThe 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. Show and hide more

1. Introduction
   • Oracle SQL Performance Tuning for Developers LiveLessons: Introduction 00:04:14
2. Lesson 1: Oracle Database Architecture
   • Learning Objectives 00:00:47
   • 1.1 Explore how Oracle processes SQL 00:16:11
   • 1.2 Understand Library Cache Internals 00:05:13
   • 1.3 Handle SQL with Bind Variables handling 00:04:27
   • 1.4 Explore the performance advantages of SQL using Functions 00:03:54
   • 1.5 See what‚Äôs new for Oracle12: SQL Directives 00:03:56
   • Put it all together 00:01:31
3. Lesson 2: Oracle Execution Plans/Explain Plans
   • Learning Objectives 00:00:53
   • 2.1 Understand Explain Plans 00:01:41
   • 2.2 Set up and use the PLAN_TABLE 00:04:24
   • 2.3 Use SQL*Plus 00:11:59
   • 2.4 Use TOAD 00:03:33
   • 2.5 Use SQL Developer 00:01:27
   • 2.6 Use Rapid SQL 00:01:47
   • 2.7 Use JS Tuner 00:03:02
   • 2.8 Use SQL Trace 00:15:37
   • 2.9 Get the correct information from Oracle to solve SQL performance issues 00:06:23
   • Put it all together 00:01:22
4. Lesson 3: Explain Plan Content
   • Learning Objectives 00:00:24
   • 3.1 Understand Explain Plan Anatomy 00:08:24
   • 3.2 Explore the Oracle SQL Parsing Process 00:07:05
   • 3.3 Understand the Cost-Based Optimizer steps 00:14:25
   • 3.4 Explore the Explain Plan Line-Item Content 00:13:01
   • 3.5 Understand Table Joins 00:10:14
   • 3.6 Explore the Optimizer Choices 00:15:46
   • 3.7 See common issues with poorly performing Partitioned queries 00:05:33
   • 3.8 Use Driving Table Hints 00:07:05
   • Put it all together 00:01:20
5. Lesson 4: Indexes
   • Learning Objectives 00:00:53
   • 4.1 Understand how Indexes work 00:06:20
   • 4.2 Explore the Complete Index syntax 00:19:38
   • 4.3 See how the CBO chooses which Index to use 00:02:14
   • 4.4 Utilize Index Monitoring 00:06:40
   • 4.5 Understand Index Clustering Factor 00:10:47
   • 4.6 Utilize Bitmap Indexes 00:05:41
   • 4.7 Understand Index-organized tables 00:03:15
   • 4.8 Explore Index tips and techniques 00:05:26
   • 4.9 Use some helpful Index Hints 00:05:39
   • Put it all together 00:01:22
6. Lesson 5: Where Clause Processing
   • Learning Objectives 00:00:31
   • 5.1 Understand how the CBO arrives at row estimations 00:03:16
   • 5.2 Explore where the math goes wrong 00:01:49
   • 5.3 Use the proper data types for better performance 00:07:21
   • 5.4 Look at the Where clause content 00:02:46
   • Put it all together 00:00:58
7. Lesson 6: Sorts/Views/Sub-Query Processing
   • Learning Objectives 00:00:31
   • 6.1 Look at SQL that has Sorts 00:04:08
   • 6.2 Explore how the CBO processes views 00:03:02
   • 6.3 Understand Sub-query processing 00:07:13
   • 6.4 Control the Explain Plan via Sub-queries 00:08:49
   • 6.5 Use Hints in Sub-queries 00:03:33
   • Put it all together 00:01:09
8. Lesson 7: Problem SQL Review
   • Learning Objectives 00:00:28
   • 7.1 Spot Problem SQL via Explain Plan 00:02:21
   • 7.2 Review Problem SQL 1 00:02:32
   • 7.3 Review Problem SQL 2 00:01:39
   • 7.4 Review Problem SQL 3 00:01:54
   • 7.5 Use Table Alias for code readability 00:05:05
   • 7.6 Review Hints 00:07:08
   • Put it all together 00:00:46
9. Lesson 8: Data Types and Statistics
   • Learning Objectives 00:00:27
   • 8.1 Use the proper data types 00:03:16
   • 8.2 Understand CBO Histograms 00:08:35
   • 8.3 Explore Oracle12 Adaptive Statistics 00:01:39
   • 8.4 Review Oracle Adaptive SQL Plans 00:06:29
   • Put it all together 00:01:05
10. Summary
    • Oracle SQL Performance Tuning for Developers LiveLessons: Summary 00:00:59
Show and hide more



9780134117058.oracle.sql.performance.OR.part1.rar

9780134117058.oracle.sql.performance.OR.part2.rar

9780134117058.oracle.sql.performance.OR.part3.rar