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%';

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

Posted in Beginners, New Features, Oracle Database 12c New Features, SQLDev | Tagged , , , | Leave a comment

Can GPUs speed up Database workloads?

Recently there has been a lot of interest or hope that Graphics processing units or GPUs would be able to transparently accelerate database workloads. So, I thought it was worth investigating what Oracle is up to regarding getting transparent performance gains from both CPUs and GPUs, as the Oracle Database has a long history of adopting new technologies as they become available.

Let’s start with GPUs.

It is important to understand the basic architectural benefits and tradeoffs of GPUs in order to determine whether they will provide  value for database workloads.

GPUs are dedicated highly parallel hardware accelerators that sit on the PCI bus. The huge number of parallel computation engines provided by these devices accelerate tasks that require large numbers of computations on small amounts of data.  For example, GPUs are extremely effective for Blockchain applications because these require billions of computations on a few megabytes of data.  GPUs are also good for deep learning algorithms since these perform repeated computational  loops on megabytes to gigabytes of data and of course GPUs are great for graphics because three-dimensional imaging requires millions of computations on every image.  The workload patterns here are all the same – lots of computation on modest amounts of data.

So, can GPUs improve database workloads?

Continue reading

Posted in GPUs | Tagged , , , | Leave a comment

Oracle OpenWorld 2018 Call for papers is open!

Believe it or not, it’s time to start thinking about Oracle OpenWorld 2018!

The Oracle OpenWorld 2018 call for papers is now opens! Oracle customers and partners are encouraged to submit proposals to present at this year’s Oracle OpenWorld conference, which will be held October 22-25, 2018 at the Moscone Center in San Francisco.

Details and submission guidelines are available on the Oracle OpenWorld Call for Papers web site. The deadline for submissions is Thursday, March 22, 11:59 p.m. PDT.

We look forward to checking out your sessions on the Oracle Database and how it has changed the way you do business!

Posted in Events, OOW | Tagged | Leave a comment

Oracle Database 18c Released

Today Oracle officially released Oracle Database 18c on the Oracle Public Cloud and Oracle Engineered Systems. This is the first version of the database to follow the new yearly release model and you can find more details on the release model change in the Oracle Support Document 2285040.1 .

Before you freak out about the fact you haven’t even upgraded 12.2, so how on earth are you ever going to get to 18c – Don’t Panic!

Oracle Database 18c is in fact “Oracle Database 12c Release 2”, the name has simply been changed to reflect the year in which the product is released.

So, what can you expect?

As you’d imagine a patchset doesn’t contain any seismic changes in functionality but there are lots of small but extremely useful incremental improvements, most of which focus on the three key marquee features in Oracle Database 12c Release2:

More details on what has changed in each of these areas and other improvements can be found in the Oracle Database blog post published by Dominic Giles this morning or in the video below with Penny Avril.

You can also read all about the new features in the 18c documentation and you can try out Oracle Database 18c on LiveSQL.

So, when will you be able to get your hands on 18c on-premises for non-engineered systems?

It will be some time later this calendar year. You can check the Oracle Support document 742060.1 for more details!


Posted in Oracle Database 18c, Whitepapers | Tagged , | Leave a comment

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 execute 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

Posted in Optimizer, Oracle Database 12c New Features, SQL Plan Management, Uncategorized | Tagged , , , , | Leave a comment

Does the Explain Plan command really show the execution plan that will be used?

When it comes to SQL tuning we often need to look at the execution plan for a SQL statement to determine where the majority of the time is spent. But how we generate that execution plan can have a big impact on whether or not the plan we are looking at is really the plan that is used.

The two most common methods used to generate the execution plan for a SQL statement are:

EXPLAIN PLAN command – This displays an execution plan for a SQL statement without actually executing the statement.

V$SQL_PLAN A dynamic performance view introduced in Oracle 9i that shows the execution plan for a SQL statement that has been compiled into a cursor and stored in the cursor cache.

My preferred method is always to use V$SQL_PLAN (even though it requires the statement to at least begin executing) because under certain conditions the plan shown by the EXPLAIN PLAN command can be different from the plan that will actually be used when the query is executed.

So, what can cause the plans to differ?

Continue reading

Posted in Beginners, Optimizer | Tagged , , , | 1 Comment

Oracle Autonomous Database – What you need to know

At OpenWorld, Larry Ellison announced  an Autonomous Database cloud (service), which is self-driving, self-securing, and self-repairing.

Although there was a lot of excitement surrounding the announcement, there were also a lot of questions.

Is Oracle Database 18c the Autonomous Database?

How does it work exactly?

When and where will it become available?

How will it impact the role of the DBA?

So, I thought it would be a good idea to try and answer these questions and concerns.

Most of the answers can be found in a series of videos we shared in early November or in the Oracle Autonomous Database Strategy white paper. But before I introduce the videos let me address the first question, “Is Oracle Database 18c the Autonomous Database?”

The simple answer is NO. The Autonomous Database is a Cloud service running on top of Oracle Database 18c along with additional services to provide performance and availability SLAs. They are definitely not the same thing. Hopefully the formula below makes this clear.

Now that we have cleared that up, let’s move on to the videos I mentioned.

In the first video, Juan Loaiza gives you a peek behind the curtain of the Autonomous Database. He explains how it delivers full end-to-end automation for mission-critical workloads, including automation in provisioning, patching, securing, monitoring, optimizing, and more.

In the second video, Cetin Özbütün provides an overview of  the first Autonomous Database service to become available later this year, the Autonomous Data Warehouse. This service is extremely easy to use as you simply load data and begin running queries. Oracle will take care of everything else for you.

In the third video, George Lumpkin shares more details on the features of the Autonomous Data Warehouse Cloud and explains how customers can simply load their data and start running queries immediately.

In the fourth video, Vipin Samar outlines how the Oracle’s Autonomous Data Warehouse Cloud integrates automation to help deliver a self-securing data management platform.

And finally yours truly explains which aspect of the DBA role will be done automatically (mundane custodial tasks), allowing the DBAs to spend more time innovating and helping the business to better leverage their data.

As we get closer the official launch of new services we will share more information. So stay tuned!

Posted in Autonomous Database, OOW | Tagged , , | 3 Comments

Oracle Open World 2017 Wrap Up!

Thanks to all the customers, Oracle Aces, Development Champions and partners that made this year’s Open World an amazing event!

The conference was four, jam-packed days of great technical sessions and interesting discussions especially around the new Autonomous Database.

I was lucky enough to be present three sessions at this year’s event with two great co-speakers @dominic_giles and @GeraldVenzl . We’ve uploaded all of the slides, which are now publicly available on the Oracle Open World session catalog.

Simply click on the download arrow on the right hand side of each catalog entry to get a copy of the slides.

Don’t worry if you didn’t get a chance to collect a SQLMaria sticker. I’ll have more stickers at the East Coast Oracle Users Conference in November and at the UKOUG Technology Conference in December.


Posted in OOW | Tagged | Leave a comment

Day 4 of Oracle Open World 2017

It’s the last day of Oracle OpenWorld and we have saved the very best for last. So hopefully you are still awake and functioning at this stage!

You can start the day off at 12 noon in Moscone West, room 3020 with the Maria & Gerald Venzl show! In this session we will share the Oracle Database Features Every Developer Should Know About. Using clear, easy to follow examples we will explain how you can put these features to work in whatever project you are doing.

Continue reading

Posted in Events, OOW | Tagged | 2 Comments

Day 3 of Oracle OpenWorld 2017

Hopefully you enjoyed yesterday, the second full day of technical sessions at Oracle OpenWorld and are ready for more today!

At 11:30 am today I give my first technical session, with the wonder Dominic Giles of Swingbench fame!

Dom and I will be talking about the Oracle Database & the Internet of Things in Moscone West, room 3011. In this session we will provide step-by-step instructions for deploying a high-ingest, mission critical IoT workload on Oracle D atabase. There will be even some demos to proving the impact of each recommendations. So don’t missing it!

You can also get a SQLMaria laptop sticker at the session!

Continue reading

Posted in Events, OOW | Tagged , | Leave a comment