JEFF Talks From Kscope18

The first day of the ODTUG Kscope conference is always symposium Sunday. This year’s Database symposium, organized by @ThatJeffSmith, consisted of multiple, short, rapid  sessions, covering a wide variety of database and database tool topics, similar to Ted Talks but we called then JEFF Talks!

I was lucky enough to present 3 of this year’s JEFF Talks that I thought I would share on my blog since there wasn’t a way to uploaded to the conference site.

In the first session I covered  5 useful tips for getting the most out of your Indexes, including topics like reverse key indexes, partial indexes, and invisible indexes.

Next up was my session on JSON and the Oracle Database. In this session, I covered topics like what data type you should use to store JSON documents (varchar2, clob or blob) the pros and cons of using an IS JSON check constraint, and how to load, index, and query JSON documents.

In my finally JEFF talk I covered some of the useful PL/SQL packages that are automatically supplied with the Oracle Database. Since the talk was only 15 minutes I only touched on 4 of the 300 supplied packages you get with Oracle Database 18c but hopefully it will give you enough of a taste to get you interested in investigating some of the others!

 

 

Posted in Beginners, Events, JSON, Syntax, Top_Tip | Tagged , , , , | Leave a comment

Avoiding reparsing SQL queries due to partition level DDLs – Part 2

In my pervious post, I promised to provide an alternative solution to avoiding repasing SQL queries due to partition level DDLs.

So, what is it?

In Oracle Database 12 Release 2 we implementing a fine-grained cursor invalidation mechanism, so that cursors can remain valid if they access partitions that are not the target of an EXCHANGE PARTITION, TRUNCATE PARTITION or MOVE PARTITION command.

As I said in my previous post, this enhancements can’t help in the case of a DROP PARTITION command due to the partition number changing but hopefully you can change the DROP to either an EXCHANGE PARTITION or a TRUNCATE PARTITION command to avoid the hard parse, as I have done in the example below. 

If you recall, we have a METER_READINGS table that is partitioned by time, with each hour being stored in a separate partition. Once an hour we will now TRUNCATE the oldest partition in the table as a new partition is added. We also had two versions of the same SQL statement, one that explicitly specifies the partition name in the FROM clause and one that uses a selective WHERE clause predicate to prune the data set down to just 1 partition.

Let’s begin by executing both queries and checking their execution plans.
Continue reading

Posted in IoT, Partititoning, Top_Tip | Tagged , , | 1 Comment

Avoiding reparsing SQL queries due to partition level DDLs – Part 1

A couple of weeks ago, I published a blog post that said specifying a partition name in the FROM clause of a query would prevent an existing cursor from being hard parsed when a partition is dropped from the same table. This was not correct.

It’s actually impossible for us not to re-parse the existing queries executing against the partitioned table when you drop a partition, because all of the partition numbers change during a drop operation. Since we display the partition numbers in the execution plan,  we need the re-parse each statement to generate a new version of the plan with the right partition information.

What actually happened in my example was the SQL statement with the partition name specified in the FROM clause reused child cursor 0 when it was hard parsed after the partition drop, while the SQL statement that just specified the table name in theFROM clause got a new child cursor 0.

But it’s not all bad news. I do have a solution that will reduce hard parses when executing DDL operations on partitioned tables that you can check out in part 2 of this blog post. But before you click over to read the alternative solution, let me explain in detail what was really happening in the original example I posted.

If you recall, we have a METER_READINGS table that is partitioned by time, with each hour being stored in a separate partition. Once an hour we drop the oldest partition in the table as a new partition is added. We also had two versions of the same SQL statement, one that explicitly specifies the partition name in the FROM clause and one that uses a selective WHERE clause predicate to prune the data set down to just 1 partition.

Continue reading

Posted in IoT, Partititoning, Top_Tip | Tagged , , | Leave a comment

Automatic Collection of Fixed Objects Statistics in 12c

In my previous life as the Optimizer Lady, I wrote a blog on the importance of gathering fixed object statistics, since they were not originally gathered as part of the automatic statistics gather task.

Starting with Oracle Database 12c Release 1, Oracle will automatically gather fixed object statistics as part of automated statistics gathering task, if they have not been previously collected.Does that mean we are off the hook then?

The answer (as always) is it depends!

Let me begin by explaining what we mean by I the term “fixed objects”.

Continue reading

Posted in Optimizer, Statistics | Tagged , , | Leave a comment

Should you gather System Statistics?

While at the HotSOS Symposium, last month, I caused quite a stir when I recommended that folks should never gather system statistics.

Why such a stir?

It turns out this goes against what we recommend in the Oracle SQL Tuning Guide, which says “Oracle recommends that you gather system statistics when a physical change occurs in the environment”.

So, who right?

Well in order to figure that out, I spoke with Mohamed Zait, the head of the optimizer development team and Nigel Bayliss, the product manager for the optimizer, upon my return to the office.

After our discussions, Nigel very kindly agreed to write a detailed blog post that explains exactly what system statistics are, how they influence the Optimizer, and provides clear guidance on when, if ever, you should gather system statistics!

What did I learn from all this?

Don’t gather system statistics unless you are in a pure data warehouse environment, with a good IO subsystem (e.g. Exadata) and you want to encourage the Optimizer to pick more full table scans and never says never!

Posted in Optimizer, Statistics | Tagged , , | Leave a comment

Oracle RAC now available on Docker!

I began my love affair with Docker a year ago when @GeraldVenzl got me started with my very first 12.2 Multitenant container database on Docker and I have to say I absolutely love the convenience of having an Oracle Database directly on my MAC for demos and building test cases to help answer AskTOM questions.

Then about six months ago I got an opportunity to beta test RAC on Docker when I needed a two node RAC cluster for a blog post on controlling where data is populated into In-Memory on a RAC cluster.

Now you have an opportunity to try RAC on Docker for yourself, as Oracle has just released Docker build files to create an Oracle RAC Database Docker image on Github.

Continue reading

Posted in Docker, RAC | Tagged , , | Leave a comment

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

Posted in Beginners, New Features, Oracle Database 12c New Features, SQLDev | Tagged , , , | 5 Comments

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 , , , | 1 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 12.2.0.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