This is the third and final post on follow up questions from the Redgate webinar I did on 12c Adaptive Optimization (the link goes to a recording of the webcast by the way).
Also, here are links to the 2 earlier posts:
So here are the last set of questions along with my responses:
Q: Is this feature on by default or you have to set a parameter to make sure of it?
A: It’s on by default but can be turned off by the methods listed in the presentation.
This is the second post on follow up questions from the Redgate webinar I did on 12c Adaptive Optimization. The first post is here: 12c Adaptive Optimization – Part 1. Since there were several comments and questions about hints and how they interact with Adaptive Plans, I decided to limit this 2nd post to that topic.
Q: Regarding turning off the adaptive optimization (particularly adaptive joins), will there also be a hint to disable it for a particular SQL?
Q: can we pick and choose SQL’s not to run this collector for
A: There are no specific hints to enable or disable Adaptive Plans as of 22.214.171.124. However, the OPT_PARAM hint does work with both the OPTIMIZER_ADAPTIVE_FEATURES parameter and the “_optimizer_adaptive_plans” parameter.
When you upgrade you often find that some little detail (of the optimizer) that didn’t receive a lot of attention in the “New Features” manuals introduces a few dramatic changes in execution plans. Here’s one example of a detail that is likely to catch a few unlucky people. We start with a very simple table which is just and id column with some padding, and then show the effect of a change in the handling of “constant subqueries”. Here’s my data set:
Last week I did a webinar on 12c Adaptive Optimization. The talk was recorded. The slides are here: 12c Adaptive Optimization V2 PDF. The recording can be found here: 12c Adaptive Optimization Recording. There were a number of follow up questions and emails so I thought I’d summarize here. Since there were so many questions, (I guess I must not have done that good of a job of explaining how it works) I will break them up into 2 or 3 posts. So for this first one I will just cut and paste from a couple of email follow ups.
Here’s the first question(s):
Oracle 12c introduced the ability to make user specified columns “Invisible” such that they’re invisible by default and have to be explicitly referenced to be accessed. So a simple SELECT * won’t display columns defined as invisible, but they will be displayed if explicitly referenced in the SELECT column list. From an indexing perspective, columns […]
Oracle 12c has increased the maximum length of character-based columns to 32K bytes – don’t get too excited, they’re stored out of lines (so similar in cost to LOBs) and need some modification to the parameter file and data dictionary (starting the database in upgrade mode) before you can use them.
Richard Foote has a pair of articles on indexing such columns:
Finally, at long long last, I have a spare 30 minutes in my life to complete this blog entry !! As discussed previously, Oracle has extended the maximum length of varchar2, nvarchar and raw columns to 32K, but this comes with some challenges when it comes to indexing such columns due to restrictions on the […]
I wrote a note about the 12c “In-Memory” option some time ago on the OTN Database forum and thought I’d posted a link to it from the blog. If I have I can’t find it now so, to avoid losing it, here’s a copy of the comments I made:
Juan Loaiza’s presentation is probably available on the Oracle site by now, but in outline: the in-memory component duplicates data (specified tables – perhaps with a restriction to a subset of columns) in columnar format in a dedicated area of the SGA. The data is kept up to date in real time, but Oracle doesn’t use undo or redo to maintain this copy of the data because it’s never persisted to disc in this form, it’s recreated in-memory (by a background process) if the instance restarts. The optimizer can then decide whether it would be faster to use a columnar or row-based approach to address a query.
I’ve been playing about with the ACCESSIBLE BY clause to create PL/SQL white lists in Oracle 12c. Here’s the article I wrote about it.
There seem to be some discrepancies in the documentation*, which I’ve highlighted in the article. Not sure if they are documentation errors, functionality that has been pulled and will reappear in 12cR2, or just misunderstandings on my part.
* I’ve posted comments on the docs, so if they are documentation errors they may get fixed.
This note is a quick summary of an oddity that came to light after a twitter conversation with Christian Antognini yesterday. First a little test script to get things going: