Optimizing Oracle SQL, Intensive - Denver (5/22/17-5/26/17)

Whatever your role, performance analyst, database administrator or application developer, our mission at Hotsos is to provide you with courses that will radically enhance your on-the-job efficiency in delivering peak-performing applications that are suited exactly to the needs of your business. You can use the methods we teach at the moment you leave our classroom to provide spectacular value to your business.Our courses focus the student's attention not upon rote learning and superficial understanding of Oracle rules of thumb, but upon a thorough understanding of how systems work. Hands-on problem-solving strategies are presented that can be carried forward directly to common problems and customized for solving less common ones. We believe that theory is essential, but without the relevant practical application, the investment you make in both time and money remains a mere academic exercise instead of valuable intellectual assets that can be applied to your day-to-day activities.

Hotsos

<H3>Synopsis</H3>
<P>This course is intended to teach students a repeatable and reliable method for optimizing SQL and to assimilate the new habit of testing optimization hypotheses rapidly during the performance improvement process. Instead of relying on “rules of thumb” and hit-and-miss guessing, this course helps the student understand the inner workings of the Oracle optimizer and how SQL execution paths are determined. Particular emphasis is placed on how to create and read the 10046 and 10053 trace files. This allows the students to know exactly what happened when the SQL ran (10046) and how the optimizer developed the plan it used (10053). The student will also learn how to capture and read the execution plan. The student's attention is focused on a thorough understanding of exactly <I>how the Oracle Database works</I>.</P>
<P>The course is not just about learning how to write SQL. The course is about arming the student with the skills to be able to scientifically determine the efficiency of any SQL statement. While the course does offer alternative ways to write SQL in many cases, the primary objective is to equip the student to determine whether a statement is optimal and to achieve a deeper understanding of the cost-based optimizer. This course presents problem-solving strategies that can be carried forward directly to common problems and customized for solving less common ones. The result is a fortified foundation for optimizing SQL in ever more challenging situations.</P>
<H3>Objective</H3>
<P>This class is a 5-day intensive combination of material that would typically be covered in 7 days. Focus is directed towards the optimizer and understanding how it behaves when developing query execution plans. The importance of optimizing SQL at the resource usage level is learned and the Hotsos SQL Test Harness and Tools Pack scripts are used to facilitate the comparison of multiple approaches to writing SQL statements to discover the “performance optimal” choice among several approaches. Different approaches to writing high performance SQL are demonstrated as well as review of database design elements that can affect performance.</P>
<H3>Target Audience</H3>
<P>The course is designed for application developers and database administrators who want to respond faster and more permanently to Oracle system performance problems that are known to be caused by inefficient application SQL code. Whether you are wanting to learn how to write new code that performs optimally from the start or are looking to update and maintain previously written code, this course teaches you the skills you need. We take you 'under the covers' to see how and why your SQL performs as it does and lead you to understand how to most effectively provide the Oracle optimizer with the information it needs to produce optimal execution plans.</P>
<P>Simply put, if you write SQL, this class is for you!</P>
<P>The course is most effective for students who will be motivated to solve real Oracle system performance problems immediately upon returning to work after the course.</P>
<H3>Prerequisites</H3>
<P>Students are expected to have a working knowledge of Oracle SQL through experience either as a database administrator or application developer.</P>
<H3>Syllabus</H3>
<P>This course is organized into the following segments:<BR></P>
<OL>
<LI>Introduction<BR>
<UL>
<LI>Course welcome, instructor and student introductions</LI>
<LI>"False Truths"</LI>
<LI>Classroom Environment</LI>
</UL>
</LI>
<LI>Fundamentals<BR>
<UL>
<LI>Fundamentals of Testing<BR>
<UL>
<LI>Understanding and measuring statement resource consumption</LI>
<LI>Performance Is The Study Of <I>Time</I></LI>
<LI>Scalability Threats</LI>
</UL>
</LI>
<LI>Fundamentals of Database Architecture<BR>
<UL>
<LI>Shared Pool</LI>
<LI>Buffer Cache</LI>
<LI>Latching</LI>
</UL>
</LI>
<LI>Fundamentals of Statement Execution<BR>
<UL>
<LI>PARSE, EXECUTE, BIND, FETCH, arraysize issues, etc.</LI>
<LI>Identical Statements</LI>
<LI>Stages of SQL Processing</LI>
<LI>Response Time and Resource Consumption</LI>
</UL>
</LI>
<LI>Terms and definitions:<BR>
<UL>
<LI>LIO, PIO, latching, buffer cache, library cache, etc.</LI>
</UL>
</LI>
</UL>
</LI>
<LI>Tools of the Trade<BR>
<UL>
<LI>Tools for SQL statement problem diagnosis and repair:<BR>
<UL>
<LI>Statistics report (including table and index descriptions)</LI>
<LI>Block- and row-selectivity report</LI>
<LI>Extended SQL trace (Event 10046) data</LI>
<LI>A good profiler (TKPROF)</LI>
</UL>
</LI>
<LI>The Hotsos SQL Test Harness<BR>
<UL>
<LI>How to install and use to measure and store test results</LI>
</UL>
</LI>
</UL>
</LI>
<LI>The Optimizer<BR>
<UL>
<LI>Oracle query optimizer technologies<BR>
<UL>
<LI>Rule Based (RBO)</LI>
<LI>Cost Based (CBO)</LI>
</UL>
</LI>
<LI>Adaptive Query Optimization (12c)<BR>
<UL>
<LI>Adaptive Plans</LI>
<LI>Adaptive Statistics</LI>
</UL>
</LI>
</UL>
</LI>
<LI>Hints and Selectivity<BR>
<UL>
<LI>Optimizer hints</LI>
<LI>Statistics collection<BR>
<UL>
<LI>DBMS_STATS</LI>
<LI>CLUSTERING_FACTOR</LI>
<LI>System Statistics</LI>
<LI>Selectivity Estimates</LI>
<LI>Extended Stats</LI>
<LI>Block And Row Selectivity</LI>
<LI>Data Skew</LI>
</UL>
</LI>
<LI>Histograms<BR>
<UL>
<LI>Frequency, Height Based, Top Frequency and Hybrid</LI>
<LI>Histogram and bind variables</LI>
<LI>Adaptive Cursor Sharing</LI>
</UL>
</LI>
</UL>
</LI>
<LI>Access and Join Methods<BR>
<UL>
<LI>Compare and contrast access methods:<BR>
<UL>
<LI>Table Access Full</LI>
<LI>Index Access By Rowid</LI>
</UL>
</LI>
<LI>Index scan type overview:<BR>
<UL>
<LI>Index unique scan, index skip scan, index fast scan, index full scan, index joins</LI>
<LI>Batched access (12c)</LI>
</UL>
</LI>
<LI>Compare and contrast join methods:<BR>
<UL>
<LI>Nested loop, hash join, sort/merge join, and Cartesian</LI>
</UL>
</LI>
</UL>
</LI>
<LI>Understanding Execution plans<BR>
<UL>
<LI>Reading Execution Plans<BR>
<UL>
<LI>Explain plan vs Execution plan</LI>
<LI>Capturing and displaying execution plans</LI>
</UL>
</LI>
<LI>Analyzing Execution Plans<BR>
<UL>
<LI>Plan Table, V$SQL_PLAN, V$SQL_PLAN_STATISTICS, etc.</LI>
</UL>
</LI>
<LI>Query Transformation<BR>
<UL>
<LI>View merging</LI>
<LI>Subquery unnesting</LI>
<LI>Predicate pushing</LI>
</UL>
</LI>
</UL>
</LI>
<LI>Indexes and Performance<BR>
<UL>
<LI>Coding techniques that may prevent index use:<BR>
<UL>
<LI>Column expressions</LI>
<LI>Implicit datatype conversion</LI>
</UL>
</LI>
<LI>Indexing null values</LI>
<LI>Impact of composite key order</LI>
<LI>Invisible indexes</LI>
<LI>Function-based indexes</LI>
<LI>Bitmap indexes</LI>
</UL>
</LI>
<LI>Getting with it<BR>
<UL>
<LI>Defining Subquery Factoring</LI>
<LI>The MATERIALIZE hint</LI>
<LI>Recursive Subquery Factoring</LI>
<LI>WITH functions</LI>
</UL>
</LI>
<LI>Writing SQL "right"<BR>
<UL>
<LI>Analytic functions and SQL modeling</LI>
<LI>Predicate order and filter application</LI>
<LI>SEMI and ANTI joins</LI>
<LI>Scalar subqueries</LI>
<LI>INLIST ITERATORs</LI>
</UL>
</LI>
</OL>
<H3>Class Forum</H3>
<P>For questions and discussion about the course, please visit our <A HREF="http://support.hotsos.com/forums/viewforum.php?f=61" TARGET="_blank" DATA-MSYS-CLICKTRACK="0" REL="nofollow noopener noreferrer">forum</A>.</P>
<H3>Instructional Format</H3>
<P>The five-day course is approximately 60% instructor-led lecture and discussion, 40% in-class exercises.</P>
<P>The instructors place a special emphasis on teaching students to think and test for themselves instead of relying on widely believed but unfortunately unreliable rules of thumb.</P>
<P>The course is conducted in English.</P>
<H3>Materials</H3>
<P>Each student receives the following materials:</P>
<UL>
<LI>Course Notes<BR> A PDF copy of the presentation is provided prior to the beginning of training. Please down load this prior to start of training.</LI>
<LI>Tools<BR> Access to course software tools and supplemental documentation at hotsos.com.</LI>
</UL>

Sorry, registration for this course is now closed.  For a list of upcoming courses, please visit the Hotsos website (www.hotsos.com) and click the Calendar in the upper right.

Registrations are closed

Optimizing Oracle SQL, Intensive - Denver (5/22/17-5/26/17)

More events from Hotsos

Follow organizers to get events picked for you

Registrations are closed

Optimizing Oracle SQL, Intensive - Denver (5/22/17-5/26/17)

More events from Hotsos

Follow organizers to get events picked for you

You might also like...

Browse more events with different dates, prices, and formats to find your next great experience.