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.
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.
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‘.
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.
ALTERTABLE 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.
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.
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!
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.
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.
FROM customers c,
WHERE c.c_custid = t.loyaltyCardNo
ORDERBY total DESC
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.
My favorite marquee feature in Oracle Database 12c is Database In-Memory. With the introduction of Database In-Memory in 220.127.116.11, data can now be populated into memory both in a row format (the buffer cache) and a new in-memory optimized column format, simultaneously.
The database maintains full transactional consistency between the row and columnar formats, just as it maintains consistency between tables and indexes. The Oracle Optimizer is fully aware of what data exists in the column format and automatically routes analytic queries to the column format and OLTP operations to the row format, ensuring both outstanding performance and complete data consistency for all workloads without any application changes.
So, what can you expect from Database In-Memory in 12.2?
I know I promised more blog posts on the new features in Oracle Database 12c but I thought I would cheat on today’s post and point you to an article I wrote in the current issue of UKOUG’s Scene Magazine.
There you will find a 3 page article that outline what you can expect from Oracle Database 12c both in terms of marquee features as well as small but useful enhancements.
I’ll have more technical posts on these and other new features in the coming weeks.