Oracle SQL Tuning with View Merging

In this tutorial you will learn – through demonstrations and proving theories with execution plans – how to tune Oracle SQL queries that include views and what query structures prevent View Merging (aka unnesting). You will learn to recognize View Merging – or the lack of it – in Oracle execution plans. You’ll learn what the related suspicious structures are – the “Red Flags”.

A vital optimization that your queries need! You’ll be amazed (or not 😉 at how much difference View Merging can make!

BACKGROUND
Oracle’s Cost Based Optimizer (CBO) is an incredible software artifact, possibly the most complex we will ever work with. But it is not infallible: the CBO needs our help. In this webinar you will learn how we can provide help related to View Merging (aka Unnesting) to the CBO and – in some cases – dramatically reduce response time with a simple rewrite of the query!

One case is CBO’s ability to merge view definitions and subqueries during optimization. If there are two views referenced in the query, the CBO may choose to build two results sets independently, then merge two results to create a single result set to pass the user OR merge the view definitions and create a single result set to be passed to the user. The latter plan – reducing the number of results sets to be merged – can often deliver astronomical performance benefits. But not if the syntax of the query prevents it. We need to learn how to not prevent view merging.

Prerequisite Knowledge:
Solid experience with Oracle SQL SELECT statement clauses and functions.

Basic experience reading Oracle execution plans. See SkillBuilders’ Free Tutorial How to Read an Execution Plan a Great review of reading execution plans – https://skillbuilders.com/course/how-to-read-an-execution-plan-in-oracle/

Some familiarity with Oracle sample schemas, including Scott, Human Resources (HR), Sales History (SH) and Order Entry (OE). If you haven’t used these demonstration schemas before, take a couple of minutes to get familiar with them. Refer to the Oracle Documentation for schema diagrams.

Presenter:
John Watson, Oracle Certified Master DBA, SkillBuilders Director of All Things Oracle Database