Category: Oracle Database 12c New Features

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”

SQL Plan Management – Selective Automatic Plan Capture Now Available!

Over the years, Oracle has provided a number of techniques to help you control the execution plan for a SQL statement, such as Store Outlines and SQL Profiles but for me, the only feature to truly give you plan stability is SQL Plan Management (SPM). It’s this true plan stability that has made me a big fan of SPM ever since it was introduced in Oracle Database 11g.

With SPM only known or accepted execution plans are used. That doesn’t mean Oracle won’t parse your SQL statements, it will. But before the execution plan generated at parse is used, we will confirm it is an accepted plan by comparing the PLAN_HASH_VALUE to that of the accepted plan. If they match, we go ahead and use that plan.

Continue reading “SQL Plan Management – Selective Automatic Plan Capture Now Available!”

Controlling where objects are populated into memory on RAC

Last week I spent some time beta testing Oracle Database 12c RAC on docker (more on this in a future post) and decided to take a trip down memory lane and play around with Oracle Database In-Memory.

One of the things I looked at was the new FOR SERVICE sub-clause of the INMEMORY DISTRIBUTE clause.

ALTER TABLE customers INMEMORY PRIORITY HIGH DISTRIBUTE FOR SERVICE IM1;

This new sub-clause allows you to control exactly where an object (table or partition) is populated into memory in a RAC environment based on where a database service is allowed to run. If the service is stopped then the objects distributed for that service are automatically removed from the IM column store(s).

If you recall each RAC node has its own In-Memory column store (IM column store). By default, when a table is populated into memory in a RAC environment it will be automatically distributed across all of the IM column stores in the cluster. That is to say, a piece of the table will appear in each RAC node, effectively making the IM column store a shared-nothing architecture.

This is not always desirable, especially if you only run certain applications or workloads on a subset of RAC node.

The video below demonstrates how to use the new syntax and what gv$ performances views you should look at to monitor what is going on in the IM column store in a RAC environment. You’ll find a copy of the script I used in the video at the bottom of this post.

PLEASE NOTE: That the service names are case sensitive, so make sure you keep them consistent. If your service name is defined all in lower case and then you specify the name in upper case in the ALTER TABLE command, you will not get an error. Remember this command is only changing the INMEMORY attribute on the table. It’s only when we begin populating the table into memory will Oracle discover that the service you specified doesn’t exist, i.e. doesn’t match the case of the service defined.

Continue reading “Controlling where objects are populated into memory on RAC”

Overriding DBMS_STATS Parameter Settings

Since it’s introduction in Oracle Database 8i, the DBMS_STATS package is Oracle’s preferred method for gathering statistics. With each new database release the DBMS_STATS package is extended to accommodate new approaches to gather statistics and new types of statistics.

Over the years, application developers and DBAs have written hundreds of scripts using the DBMS_STATS package to help gather and manage optimizer statistics effectively. However, once written these scripts are rarely modified to take advantage of the improvements in the DBMS_STATS package, which can result in suboptimal statistics.

Oracle Database 12 Release 2 makes it a lot easier to be able to manage this vast collection of scripts by includes a new DBMS_STATS preference called PREFERENCE_OVERRIDES_PARAMETER. When this preference is set to TRUE, it allows preference settings to override the parameter values specifically set in a DBMS_STATS command.

Continue reading “Overriding DBMS_STATS Parameter Settings”

Oracle Database 12c and DevOps

Over the last month or so I’ve been on the road presenting at different user group conferences and meeting with customers. I’ve gotten a number of requests via blog comments to share the slides for the sessions I presented.

One of the most requested sessions is actually one I delivered, as a joint session with @GeraldVenzl, called Oracle Database 12c and DevOps.

In this session Gerald takes on the role of the lead developer on a project to deploy a RESTful web-based application, while I play the role of the DBA. Through the course of the session, we learn to work together to find a solution that will allow our (fictitious) company  to embrace a more agile development approach, as well as the latest technology trends without exposing the business to painful availability or security vulnerabilities.

Although the slides don’t do our witty repartee justice , hopefully they will inspire you to try out some of the technologies we describe in the session!

Below is the other session that I got a lot of requests for was the keynote session I gave at this year’s Georgia Oracle Users Group Tech Days 2017 , “What to expect from Oracle Database 12c”.

Extremely useful Partitioning enhancements in Oracle Database 12c

Back in January, I promised I would write a series of blogs on some of my favorite new 12c enhancements, designed to make your life easier. I’m finally getting around to keeping that promise with this weeks blog post on my favorite partitioning enhancements.

Imagine you have a large SALES table that contains information on all of the sales we have had in our chain of department stores.

DESC sales
 Name	          NULL?    TYPE
 ----------     -------- ------------------------
 PROD_ID        NOT NULL NUMBER(6)
 CUST_ID        NOT NULL NUMBER
 TIME_ID        NOT NULL DATE
 CHANNEL_ID     NOT NULL CHAR(1)
 PROMO_ID	NOT NULL NUMBER(6)
 QUANTITY_SOLD	NOT NULL NUMBER(3)
 AMOUNT_SOLD	NOT NULL NUMBER(10,2)
 REGION 	NOT NULL CHAR(3)

There are also 4 indexes on our SALES table to help with our analytic queries.

Continue reading “Extremely useful Partitioning enhancements in Oracle Database 12c”

Upcoming Event: Georgia Oracle Users Group Tech Days 2017

I’m delighted to have been given an opportunity to deliver the keynote session  at this year’s Georgia Oracle Users Group Tech Days 2017.

The conference takes place on  May 9-10, 2017, at the Loudermilk Conference Center in Atlanta, Georgia and is shaping up to be two great days of technical sessions delivered by some of  the best Oracle speakers I know.

Continue reading “Upcoming Event: Georgia Oracle Users Group Tech Days 2017”

Oracle Database 12c now available on Docker

Last week at Dockercon, Oracle announced that the Oracle Database is now available alongside other Oracle products the on Docker Store.

Given how much folks here in Silicon Valley (including my better half) rave about how easy and great Docker is, I thought I would try it out and share with you exactly how I did.

Since I was a Docker virgin, the first thing I had to do was download and install Docker. Then, a quick trip to the Docker Store followed by a double click, and I was up and running!

Next, I needed to get the new Oracle Database container. Again, you have two options here:
Continue reading “Oracle Database 12c now available on Docker”

New Top-N Queries and Pagination Syntax

At the RMOUG Training Days, a couple of weeks, ago @GeraldVenzl and I delivered a session demonstrating what it would take to get a REST enabled, web-based application up and running with Oracle Database 12c. During the session Gerald asked me to write a query to calculate the top 10 customers we had in terms of their total spend. Below is the query I came up with.

SELECT c.c_name, 
       c.c_custid loyaltyCardNo, 
       t.city, 
       t.state,
       SUM(salesAmount) total
FROM   customers c,
       t
WHERE  c.c_custid = t.loyaltyCardNo
GROUP BY c.c_name,
         c.c_custid,
         t.city,
         t.state
ORDER BY total DESC
FETCH FIRST 10 ROWS ONLY;

After the session I got a number of questions regarding the “FETCH FIRST 10 ROWS ONLY” syntax that I used, so I thought it would be worth explaining what it is and what happens under the covers when you execute it.

What is FETCH FIRST X ROWS ONLY?

Continue reading “New Top-N Queries and Pagination Syntax”

%d bloggers like this: