Category: SQLDev

SQL Tuning Workshop

Last week I had the pleasure of delivering a five-part SQL Tuning Workshop for my local Oracle User Group –  Northern California Oracle User Group. The workshop explains the fundamentals of the cost-based Optimizer, the statistics that feed it, the hints that influence it, and the key tools you need to examine execution plans.

The workshop also provides a methodology for diagnosing and resolving the most common SQL execution performance problems. Given the volume of interest in this content, I want to share all of the material from the workshop here and give you links to additional material on each of the five topics.

Part 1 Understanding the Oracle Optimizer

The first part of the workshop covers the history of the Oracle Optimizer and explains the first thing the Optimizer does when it begins to optimize a query – query transformation.

Query transformations or the rewriting of the SQL statement into a semantically equivalent statement allows the Optimizer to consider alternative methods of processing or executing that query, which are often more efficient than the original SQL statement would allow. Most of Oracle’s query transactions are now cost-based, which means the Optimizer will cost the plan with and with the query transformation and pick the plan with the lowest cost. With the help of the Optimizer development team, I’ve already blogged about a number of these transformations, including:

You can also download the slides here.

Part 2 Best Practices for Managing Optimizer Statistics

Part 2 of the workshop focuses on Optimizer Statistics and the best practices for managing them, including when and how to gather statistics, including fixed object statistics.
Continue reading “SQL Tuning Workshop”

How to determine which view to use

Often times DBAs or application architects create views to conceal complex joins or aggregations in order to help simplify the SQL queries developers need to write.  However, as an application evolves, and the number of views grow, it can often be difficult for a developer to know which view to use.

It also become easier for a developer to write an apparently simple query, that results in an extremely complex SQL statement being sent to the database, which may execute unnecessary joins or aggregations.

The DBMS_UTILITY.EXPAND_SQL_TEXT procedure, introduced in Oracle Database 12.1, allows developers to expand references to views, by turning them into subqueries in the original statement, so you can see just exactly what tables or views are being accessed and what aggregations are being used.

Let’s imagine we have been asked to determine the how many “Flat Whites” we sold in our coffeeshops this month. As a developer, I know I need to access the SALES table to retrieve the necessary sales data and the PRODUCTS table to limit it to just our “Flat Whites” sales but I also know that the DBA has setup a ton of views to make developers lives easier. In order to determine what views I have access to, I’m going to query the dictionary table USER_VIEWS.

SELECT  view_name 
FROM    user_views
WHERE   view_name LIKE '%SALES%';
 
VIEW_NAME
-------------------------------
SALES_REPORTING2_V
SALES_REPORTING_V

Based on the list of views available to me, I would likely pick the view called SALES_REPORTING_V or SALES_REPORTING2_V but which would be better?

Let’s use the DBMS_UTILITY.EXPAND_SQL_TEXT procedure to find out. In order to see the underlying query for each view, we can use is a simple “SELECT *” query from each view. First, we will try ‘SELECT * FROM sales_reporting_v‘.

Continue reading “How to determine which view to use”

%d bloggers like this: