Thanks to all who attended my session at NYOUG. As usual, it was a great experience!
I am excited to present at the NYOUG Fall General Meeting (2013). I’ll be speaking about volatile tables in Oracle. They are not common, yet they can wreak havoc on the database performance when present.
The presentation has some improvements over the one I gave at Hotsos a couple years back. It covers relevant Oracle 12c features as well.
I’ll be happy to see you there!
Adaptive execution plans is one of the most exciting new features in Oracle 12c. Now, in Oracle 12c, the optimizer can pick the join method based on the actual amount of data processed, rather than the estimates it had at compile time. Does that mean that the estimated cardinalities/set sizes and the table/index statistics they are based upon no longer matter?
Not so! Here is why:
Even though Oracle uses actual information from the STATISTICS COLLECTOR step, it also uses lots of estimated information to decide the join strategy. I am not aware of the specific formula used to select the join type, but I think it is reasonable to assume that Oracle uses the estimated cost of a Nested Loop and the estimated cost of a Hash Join for that formula. Those estimated costs are based on collected data dictionary statistics (DBA_TABLES, DBA_INDEXES,..).
Here is an example:
Table TEST1 has 20 million records. The data dictionary statistics (DBA_TABLES, etc ) for TEST1 represent the data in the table.
Table TEST2 has 2 million records. The data dictionary statistics (DBA_TABLES, etc ) for TEST2 represent the data in the table.
This sample query
FROM TEST1 ,
TEST1.ID = TEST2.ID
generates an adaptive plan, whoch picks Hash Join, as expected.
Let’s see at how much data we need to have in TEST2 in order to switch to from Nested Loops to Hash Join.
To do that, we need to truncate TEST2 without re-gathering stats. Also, we need to create a loop that inserts data in TEST2 (without stats gathering) and saves the execution plan.
For my case, using this setup, we can see that the switch from NL to HJ happens when TEST2 has approx. 11500 cords.
The switch point depends on the TEST1 stats, not the actual data in TEST1.
We can double the data in TEST1 and we’ll get the same result as long as the stats are not re-gathered.
Conversly, we can delete 70% of the data in TEST1 and we’ll get the same result as long as the stats are not re-gathered.
In short, the cardinality estimates and the stats they are based upon are still very important.
If the actual cardinality of the second set in a join is higher than the estimated cardinality , then for some values Oracle would be using HJ, even though NL would produce better results.
Conversely, if the actual cardinality of the second set in a join is lower than the estimated cardinality, then for some values Oracle would be using NL, even though HJ would produce better results.
I was not able to get the Adaptive Execution Plan to work properly from a PL/SQL procedure. I had to write a shell script…
As mentioned in a previous post, MGMT_ECM_HW contains a wealth of information about hosts in OEM.
The problem is that CLOCK_FREQ_IN_MHZ column in MGMT_ECM_HW is not properly populated. All hosts had the value of 100 there – an obvious mistake.
That prompted me to do a search through the OEM repository objects. This is what I found:
MGMT_ECM_HW_CPU is similar to MGMT_ECM_HW, but FREQ_IN_MHZ column is properly populated. No need to parse descriptions to get the CPU’s speed
MGMT_ECM_HW_NIC – lots of information about network cards
MGMT_ECM_OS – OS related information
MGMT_ECM_HW_IOCARD – information about IO peripheral devices
Database virtualization and thin provisioning are new and powerful ways to share/reuse storage among non-production database copies.
Solutions for reducing the disk footprint of non-production Oracle databases range from complete database virtualization (Delphix, etc), to solutions build around storage vendor’s features (EMC,NetApps, etc), to purely software solutions (cloneDB, etc).
Sharing read-only tablespaces – a “poor man’s” database virtualization – is less known approach for saving disk space and improving refresh times in non-production environments. Here is a link to the presentation I did at NYOUG (2012) about this topic.
What are we measuring and why?
In general, most solutions are quite effective, both in terms of storage and refresh time, in delivering a copy of the production database.
Once the copy is delivered, it is open for users and batch jobs, so it starts changing. Those changes belong to the new database and therefore cannot be shared with the other non-production databases. That means that they consume “real” disk space. The more DB changes since refresh, the lower the benefits of the DB virtualization/thin provisioning solution.
Measuring the amount of change after a refresh is important in understanding how much disk space would be needed for the non-production Oracle environments after DB virtualization/thin provisioning is in place. The number is essential in computing ROI of the project.
How are we measuring it?
One way to measure the change is to run an incremental RMAN backup and see how big the resulting backup file(s) is. In many cases, however, that is not doable.
The method described here works only on 11gR2. It only requires the SCN of the DB at the time of refresh.
The information can be find in V$DATABASE (RESETLOG_CHANGE#) for databases created by RMAN DUPLICATE.
If the data was transferred with DataPump, or other similar method, a suitable SCN can be found in V$ARCHIVED_LOG (FIRST_CHANGE#, NEXT_CHANGE#).
The new DBVerify feature allows us to utilize HIGH_SCN parameter to find out how many blocks where changes since HIGH_SCN.
Let’s see how many blocks were modified after SCN 69732272706.
It is really easy:
dbv userid=sys/?????????? file=/tmp/test.dbf HIGH_SCN=69732272706
Page 64635 SCN 1012797077 (16.1012797077) exceeds highest scn to check 1012795970 (16.1012795970)
Page 66065 SCN 1012796687 (16.1012796687) exceeds highest scn to check 1012795970 (16.1012795970)
Page 66187 SCN 1012796687 (16.1012796687) exceeds highest scn to check 1012795970 (16.1012795970)
Page 87759 SCN 1012796692 (16.1012796692) exceeds highest scn to check 1012795970 (16.1012795970)
DBVERIFY - Verification complete
Total Pages Examined : 93440
Total Pages Processed (Data) : 62512
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 9399
Total Pages Failing (Index): 0
Total Pages Processed (Other): 6119
Total Pages Processed (Seg) : 1705
Total Pages Failing (Seg) : 0
Total Pages Empty : 13705
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Total Pages Exceeding Highest Block SCN Specified: 39
Highest block SCN : 1012797131 (16.1012797131)
In this case, only 39 blocks (out of 100K) were modified after SCN 69732272706.
Please note that even though DBVerify (dbv) can work with open DB files, it will not account for changed blocks that were not yet written to the file.
Also, as per MOS document 985284.1 – ” Why HIGH_SCN Option Doesn’t Work While Running DBV Against ASM Files?” – the HIGH_SCN flag works only for files that are not stored in ASM. Hope they fix that problem soon.
During my presentation at Hotsos 2013, an attendee questioned my statement that each filter/selection predicate contributes to the overall cardinality error. She believed that only the predicate with the maximum error mattered.
I believe the following paper supports my position:
I got the Hotsos 2013 “Speaker scoring and verbatim comments” information a couple of weeks ago.
I am happy that the presentation was very well received!!!
Thanks for your feedback. I appreciate all your comments. They would not only making this presentation better, but also improve my skills as a speaker.
Here are some of the comments:
This was an intriguing session, but I left feeling like there was still much more to be said on the subject.
There is indeed much more to be said. The problem with digging deeper in the subject is that it gets quite academic and might be less interesting for the audience. The article I mentioned earlier in the post, as well as Ioannidis Y. And S. Christodoulakis (1991) paper cited in the reference can be a good starting point for further research.
is this accurate, did Maria review this info?
I am not sure who reviewed the paper before the presentation, but I believe that Maria Colgan was in audience. I too will be very interested in her feedback…
He spent a lot of time talking about Terradata, The package he showed seemed way to simplistic to be of any real practical use. He did show some creative ideas on solving some issues, however there wasn’t a real example to show how to do what he purposed.
The package is quite limited for most practical uses. It was intended to be for “demonstration purposes only”.
Fair point about examples though. I’ll try to publish some examples in this blog in the coming months. Stay tuned…
It was a great pleasure presenting at Hotsos.
Thank you for attending my presentation – your comments and questions were quite insightful. I owe a bit more explanation to the lady who asked about the mechanism/formula for passing filter errors into the resulting set. I’ll get more information about that soon – stay tuned.
Working with Confidence: How Sure Is the Oracle CBO about its Cardinality Estimates, and Why Does It Matter?
The Oracle CBO is tasked to provide an execution plan for every syntactically correct statement. To tackle this very broad requirement, the CBO has to come up with selectivity coefficients for every possible type of predicate. In some cases, the selectivity coefficients are deeply rooted in math and common sense — we know for a fact that lookup by primary key would result in one or zero records.
In other cases, however, the CBO has to take a guess, sometimes even a wild guess, on how much a particular predicate would affect the cardinality of the resulting set. A simple LIKE clause with a leading wildcard would force the CBO to default the selectivity coefficient to around 5%, a guess that could be very far from reality.
To measure the level of guesswork employed by the CBO, the notion of “confidence” level of a cardinality estimate is introduced. Estimates of the confidence level the Oracle CBO should be getting for common SQL predicates are presented. The effects of joins on the confidence levels are reviewed as well as methods to reduce the level of guesswork the optimizer has to employ.
The practical implications of considering CBO’s confidence in its cardinality estimates are discussed in detail. Guidelines on how to vet new SQL and PL/SQL features, designs, and coding standards are given to aid the CBO in avoiding unnecessary guesses about predicate selectivity. A technique for analysis and resolution of performance issues based on dealing with constructs that force the CBO to make guesses is presented.
The presentation is here.
The white paper is here. It is quite comprehensive, with lots of details and references.
Use XPLAN_CONFIDENCE package entirely at your own risk: