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 “Oracle RAC now available on Docker!”

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”

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 “Can GPUs speed up Database workloads?”