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 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:
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 provision 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 an 2 CPU configuration to an 8 CPU configuration in under a minute to accommodate 48 concurrent users running a JSON workload.
Continue reading “How do I scale an Autonomous Database?”
I can’t believe Oracle Open World is done for another year.
But what a great conference it was for those interested in the Oracle Database. We saw the introduction of Oracle Database 20c and of course the continuing evolution of the Oracle Autonomous Database with details on new deployment option (Dedicated) and new flavors (Document Store).
If you weren’t able to make it to OOW in person, don’t panic as a lot of the session presentations are now available for download in the OOW session catalog. You can download the slides by clicking the down arrow on the right-hand side of each session title as shown below.
Continue reading “OpenWorld 2019 Wrap Up”
Today was the first day of Oracle Open World and during Larry Ellison’s afternoon keynote he made a number of extremely exciting announcements around Oracle Autonomous Database including the launch of an Always Free Tier for Developers and students in the Oracle Cloud. Larry said, “as long as you use the service, it will be there, free forever.”
So what’s included in Oracle’s Always Free Tier?
Along with compute and storage services, you also get access to 2 Autonomous Database with 20GB of storage each absolutely free. So you can try out both an Autonomous Data Warehouse and an Autonomous Transaction Processing system.
Continue reading “FREE Oracle Autonomous Database”
With less than a week to go until Oracle Open World kicks off, I thought I would share with you what you can expect.
Of course the whole Database team will be there and you will have multiple opportunities to meet up with us, in one of our technical sessions, our hands-on-labs or at the Oracle demogrounds.
Below are just some of the session I thought might be interesting to help get you started:
Continue reading “Are you ready for Oracle OpenWorld 2019?”
Yes, Oracle Autonomous Database (ADB) is the ideal platform for new application development.
With this family of cloud services, developers no longer have to wait on others to provision hardware, install software, and create a database for them. With ADB, developers can easily and instantly deploy an Oracle database without worrying about having to manual tune it or capacity planning. This allows developers to start developing in minutes and concentrate on solving business problems without all of the usual distractions.
ADB has the most advanced SQL and PL/SQL support accelerating developer productivity by minimizing the amount of application code required to implement complex business logic. It also has a complete set of integrated Machine Learning algorithms, simplifying the development of applications that perform real-time predictions such as personalized shopping recommendations, customer churn rates, and fraud detection.
What Development Tools should I use with ATP?
Continue reading “Can I use an Autonomous Database to develop new applications?”
Often times development and testing teams need a copy of a production database in order to develop or test using a representative dataset. Up until now, creating these cloned environments has been a challenging and time consuming process for DBAs.
Oracle Autonomous Database makes it extremely easy for a DBA to clone a database in just a few mouse clicks, as I demonstrate in the video below.
When it comes to cloning an Autonomous Database you have two options:
You can create a new database that is a complete copy of the original database, which includes all metadata (table and view definitions etc.) and all of the actual data. This is referred to as a FULL CLONE.
Or you can create a new database that only contains the metadata from the original database. This is referred to as METADATA CLONE.
Either way the “cloned” database will have a completely new database name, admin password. It can also have different infrastructure criteria (CPU count and storage) from the original database.
The clone will also not have any of the AWR data or any of the ML Worksheets from original database.
Of course you don’t need to use the UI every time you want to create a clone. All of the tasks that can be done via the UI can be done using REST APIs or CLI commands.
Continue reading “Cloning an Oracle Autonomous Database”
Today, April 25th, Oracle Database 19c became available to downloaded from Oracle.com.
Oracle Database 19c is the final member of the 12.2 family a.k.a 188.8.131.52 and is therefore the ‘long term support’ release. This means it will come with 4 years of premium support and 3 years of extended support. Making this release the version of the database that most folks are going to upgrade to next.
So, what can you expect?
There are hundreds of useful enhancements in Oracle Database 19c as well as a several new features. Dom Giles‘s latest post on the Oracle Database Insider blog has all the details on the new release, while I’ve listed just a couple of my personal favorites below.
Continue reading “Oracle Database 19c is now available!”
Early this year, I wrote a blog to let you know that Oracle Database RAC was available on Docker. However, in that blog I described a number of manual steps that were required to rebuild the image before you could start using the RAC database.
I’m now delight to announce that you can simple download a Oracle Database RAC Docker image (under database) from the container registry, which has a pre-installed / pre-built image. Making it easier to have a RAC database for your demos, development or test environments.
Oracle Open World this year was a crazy busy week catching up with customers, Oracle Aces, Development Champions and partners as we all crammed into the Moscone center to hear the very latest on Oracle Technologies.
For the database, this year’s conferences saw the introduction of Oracle Database 19c and of course the continuing evolution of the Oracle Autonomous Database Cloud Services.
If you weren’t able to make it to OOW in person, don’t panic as a lot of the session presentations are now available for download in the OOW session catalog.
Continue reading “Oracle Open World Wrap Up”