Oracle recently announced a new free base level for the Oracle Database In-Memory Option, their real-time analytics capability. With the base level, you can allocate up to 16GB of memory to the In-Memory column store, per instance without having to have an addition In-Memory license. Larger column stores will still need the additional license. Continue reading “How to use Oracle Database In-Memory for free!”
Over the last couple of months, the wonderful folks from Oracle Australia have been running a FREE virtual training event on what to expect from Oracle Database 19c and how to prepare to upgrade to the latest long-term support release of the Oracle Database.
The first 5 sessions covered everything you would want to know about how to upgrade and what you can expect from Oracle Database 19c including an in-depth look at the new Multitenant architecture. The sessions so far have been presented by some of the best Oracle speakers around including Mike Dietrich, Julian Dontcheff, Martin Bach, Richard Agnew, and Alex Blyth. All of the sessions so far have been recorded and are available for replay.
Under the Hood of an Autonomous Database
The final session in series is scheduled for August 11th at 10am AEST ( August 10th at 5 pm PST) and will be presented by Alex Blyth and myself. In our session, we plan to give you a peek under the hood of the Oracle Autonomous Database and provide you with a clear understanding of how this unique autonomous database works. We’ll share details on the exclusive combination of Oracle Database 19c features, best practices, and machine-learning algorithms used to deliver this family of cloud services. We’ll use live demos to show you how it can simplify your approach to data management with Oracle Database 19c and accelerate your transition to the cloud.
You can registers for this session here. Hope you can join us!
At the moment, we hear a lot about how businesses need to become data-driven to remain competitive, how business need to understand their customer’s needs and quickly deliver value to those customers.
But how do you do that?
You take advantage of data-driven apps that allow users to create value or insights from data in real-time.
What are Data-Driven Apps?
Data-driven apps operate on a diverse set of data (spatial, documents, sensor, transactional, etc.) pulled from multiple different sources, often in real-time and create value from that data in very different ways to traditional applications. For example, they may use Machine Learning to make real-time recommendations to customers or detect fraudulent transactions. Or use Graph analytics to identify influencers in a community and target them with specific promotions or perhaps use spatial data to keep track of deliveries.
These apps are also frequently deployed on multiple platforms, including mobile devices as well as standard web browsers, which means they need a flexible, scalable and reliability deployment platform. Given the demands on these apps, they need to be continuously developed to adapt to new use cases or user needs, and all updates must happen online as they have to be available 24×7.
When building data-driven apps, developers need to leverage an ever-increasing set of data processing and machine learning algorithms to meet these requirements.
So how should you go about developing and deploying data-driven apps quickly, efficiently, and more importantly, in a maintainable way?
Data-Driven Techniques and Technology
In the video below, I explain how to take advantage of the built-in features and functionality of the Oracle Database to develop and deploy data-driven apps efficiently. I also share some easy to follow code examples to demonstrate how much simpler your application code can be if you use this approach! Continue reading “Data-Driven Apps – What are they and the easiest way to develop them”
There is an on-going debate in our community about the best approach for developing cloud-native or data-driven apps. On one side, you have folks who say use a single-purpose “best-of-breed” database for each data type or workload you have. While the other half say, you should use a single converged database. So, which approach is right for you and your projects?
Let’s examine some of the pros and cons of each approach.
Single-purpose databases or purpose-built databases as they are often as known, are engineered to help solve a single or small number of problems. Given their narrow focus, they can ignore the tradeoffs usually required when trying to accommodate multiple data types or workloads. It also allows them to use a convenient data model that fits the purpose and to adopt APIs that seem natural for that data model. They offer less functionality than converged databases, and therefore, fewer APIs, making it easier to start developing against them. Their simplicity means they do a few things very well, but other things not at all. For example, a lot of single-purpose databases scale well, because they offer no strong consistency guarantees.
At first glance, single-purpose databases appear to be a good option. Developers are happy because they get exactly what they need to begin a project. However, when you look at the bigger picture, single-purpose databases can cause a lot of pain and end up costing more in the long run.
Oracle Autonomous Database automates the lifecycle management of a database, everything from provisioning, scaling, backups and patching, but what it doesn’t do yet is fully tune your application.
You are still on the hook to make sure your app doesn’t have any concurrency bottlenecks or poorly written SQL that Auto Indexing can’t address.
So, what can you do to monitor your app while it’s running on an Autonomous Database?
The first place you can start is the Performance Hub tab on the cloud console. Here you’ll find both real-time and historical performance data in the form of Active Session History (ASH) information for the last seven days, and SQL Monitor reports for the high-load SQL. You can aggregate the ASH data several different ways including by wait class, database service, resource group or SQL ID. The same information is also available in Oracle Management Cloud (OMC), and SQL Developer Web.
But if you are trying to get a holistic view of how your app is behaving on a database, nothing beats an Automatic Workload Repository report or AWR report.
I got a lot of follow-up questions on what Query Block names are and how to find them, after my recent post about using SQL Patches to influence execution plans. Rather than burying my responses in the comment section under that post, I thought it would be more useful to do a quick post on it.
What are query blocks?
A query block is a basic unit of SQL. For example, any inline view or subquery of a SQL statement are considered separate query blocks to the outer query.
The simple query below has just one sub-query, but it has two Query Blocks—one for the outer SELECT and one for the subquery SELECT.
Oracle automatically names each query block in a SQL statement based on the keyword using the following sort of name; sel$1, ins$2, upd$3, del$4, cri$5, mrg$6, set$7, misc$8, etc.
Given there are two SELECT statements in our query, the query block names will begin with SEL. The outer query will be SEL$1 and the inner query SEL$2.
How do I find the name of a query block?
To find the Query Block name, you can set the FORMAT parameter to ‘+alias’ in the DBMS_XPLAN.DISPLAY_CURSOR command. This will display the contents of the OBJECT_ALIAS column in the PLAN_TABLE, as a new section under the execution plan.
The new section will list the Query Block name for each of the lines in the plan.
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format=\>'+alias')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------- SQL_ID 4c8bfsduxhyht, child NUMBER 0 ------------------------------------- SELECT e.ename, e.deptno FROM emp e WHERE e.deptno IN (SELECT d.deptno FROM dept d WHERE d.loc='DALLAS') Plan hash VALUE: 2484013818 --------------------------------------------------------------------------- | Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| TIME | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 5 (100)| | |* 1 | HASH JOIN SEMI | | 5 | 205 | 5 (20)| 00:00:01 | | 2 | TABLE ACCESS FULL| EMP | 14 | 280 | 2 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| DEPT | 1 | 21 | 2 (0)| 00:00:01 | --------------------------------------------------------------------------- Query Block Name / Object Alias (IDENTIFIED BY operation id): ------------------------------------------------------------- 1 - SEL$5DA710D3 2 - SEL$5DA710D3 / E@SEL$1 3 - SEL$5DA710D3 / D@SEL$2 Predicate Information (IDENTIFIED BY operation id): --------------------------------------------------- 1 - access("E"."DEPTNO"="D"."DEPTNO") 3 - FILTER("D"."LOC"='DALLAS')
As you can see, @SEL1 is the Query Block name for the outer query, where the EMP table is used, and @SEL2 is the Query Block name for the sub-query, where the DEPT tables is used.
At the recent OOW European conference there was a lot talk about Converged Databases and how they can greatly simplify data-driven app development.
But if you missed the conference, you might find yourself wondering what exactly is a Converged Database and what is the difference between a Converged Database and an Autonomous Database?
So, I thought it would be a good idea to write a short blog post explaining what a Converged Database is and how it relates to the Oracle Autonomous Database.
What is a Converged Database?
A Converged Database is a database that has native support for all modern data types (JSON, Spatial, Graph, etc. as well as relational), multiple workloads (IoT, Blockchain, Machine Learning, etc.) and the latest development paradigms (Microservice, Events, REST, SaaS, CI/CD, etc.) built into one product.
By having support for each of these datatype, workloads, and paradigms as features within a converged database, you can support mixed workloads and data types in a much simpler way. You don’t need to manage and maintain multiple systems or worry about having to provide unified security across them.
You also get synergy across these capabilities. For example, by having support for Machine Learning algorithms and Spatial data in the same database, you can easily do predictive analytics on Spatial data. The Oracle Database is a great example of a Converged Database, as it provides support for Machine Learning, Blockchain, Graph, Spatial, JSON, REST, Events, Editions, and IoT Streaming as part of the core database at no additional cost.
A good analogy for a Converged Database is a smartphone. In the past, if you wanted to take a picture or video you would need a camera. If you wanted to navigate somewhere you would need a map or a navigation system. If you wanted to listen to music, you needed an iPod and if you wanted to make phone calls, you would also need a phone.
But with a smartphone, all of these products have been converged into one. Each of the original products is now a feature of the smartphone. Having all of these features converged into a single product inherently makes your life easier, as you can stream music over the phone’s data plan or upload pictures or videos directly to social media sites.
Continue reading “What is a Converged Database?”
In a recent chat with Connor McDonald, we discussed if it is realistic to have applications that don’t contain Optimizer hints. Ideally, the answer to this question is “yes”, you don’t need hints if you have a well-written application and you have supplied a representative set of statistics and all the possible constraint information (NOT NULL, Primary keys, Foreign Keys, etc.) to the Optimizer.
But in reality even with all of this in place, there can be cases where something goes wrong with the execution plan for a critical SQL statement and you get called in to fix.
During our chat, Connor used a very apt analogy to describe this situation. He said it was like having a patient arrive in the emergency room, who is bleeding profusely. Your first priority is to stop the patient from bleeding by slapping a band-aid on the wound.
The same is true for our poorly performing SQL statement. Our initial response is to add an optimizer hint to get the SQL statement’s execution plan back to a reasonable response time or acceptable performance.
But once a patient has been stabilized in the emergency room, medical professionals normally take that patient into surgery to make a permanent fix or at the very least stitch up the wound properly.
We need to make sure we do the same thing for our SQL statements.
Rather than leaving a band-aid in the application code in the form of an optimizer hint, we should either fix the root cause or at the very least, make a permanent fix that can be easily traced and ideally can evolve over time.
That’s why you often hear me say, “if you can hint it, you can baseline or patch it”.
What do I mean by that?
I mean we should capture the hinted plan as a SQL plan baseline or at the very least insert the hints via a SQL Patch so that we know that this statement is patched (the use of a SQL patch is visible in the note section of the plan).
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 key tools you need to exam executions 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 5 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. the majority 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:
- Subquery Unnesting
- Or Expansion
- Table Elimination
- Join Predicate Push down
- Table Expansion
- Star Transformation
- View Merging
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”
Traditionally database deployments have been designed and provisioned for the peak possible workload. And in reality, a substantial margin of safety was also provisioned on top of that in order to make sure the system could cope with any unforeseen demands.
But peak workloads tend to occur infrequently, leaving most of this costly capacity idle the majority of the time.
In order to enable customers to pay for only the resources they need, Oracle Autonomous Database allow customers to elastically adjust their compute and storage resources when necessary.
An Autonomous Database can be scaled through the UI as shown in the video below or via our cloud APIs or CLI commands. In the video, you see how I can scale a 2 CPU configuration to an 8 CPU configuration in under a minute to accommodate 48 concurrent users running a JSON workload.