Search

Top 60 Oracle Blogs

Recent comments

October 2018

OpenWorld is done!

The OpenWorld conference is over for another year. It’s always a thrill, albeit a hard-working thrill, to visit San Francisco and be a part of such a huge event.

I’ll have a full wrap up post and video soon, but I wanted to get quick blog post out there so people could get a link to see the slides from my sessions.

See my OpenWorld 2018 content here

To all those people that attended my sessions, thanks for giving up your time. I hope you got lots of benefit out of the content. And as always, if you have any feedback on what you liked, or how I can make the content better for you, please drop me a comment below.

If you’d like me to speak at your local event, please reach out to me on Twitter. I can’t make any promises – these things all depend on scheduling and budgeting, but I’ll always try my best.

What’s new in Oracle Database 19c and other #OOW18 feedback

Back from Oracle Open World #OOW18 and here are quick notes I’ve taken about what’s new. I focus on what was publicly announced, but with the ‘Safe Harbor’ statement: all this can be true, wrong or different (three-valued logic;) when 19c will be in General Availability. Rushing to publish those notes before entering the beta program as it will be hard to remember what was public or not.

Disclaimer: this was written before getting details on 19c, from content under ‘safe harbor statement’. This does not describe what will be actually implemented when 19c will be in general availability.

Upgrades – again

I’ve got a data set which I’ve recreated in 11.2.0.4 and 12.2.0.1.

I’ve generated stats on the data set, and the stats are identical.

I don’t have any indexes or extended stats, or SQL Plan directives or SQL Plan Profiles, or SQL Plan Baselines, or SQL Patches to worry about.

I’m joining two tables, and the join column on one table has a frequency histogram while the join column on the other table has a height-balanced histogram.  The histograms were created with estimate_percent => 100%. (which explains why I’ve got a height-balanced histogram in 12c rather than a hybrid histogram.)

Here are the two execution plans, 11.2.0.4 first, pulled from memory by dbms_xplan.display_cursor():

Oak Table World 2018

Oak Table World 2018 (OTW) just completed at the Children’s Creativity Museum in San Francisco.  The event website is “https://otw18.org“.

This year, it was my turn to organize this remarkable little un-conference on behalf of the Oak Table Network, which is often described as a drinking society with an Oracle problem.

What is an “un-conference”?  The dictionary definition is…

Join Cardinality – 4

In previous installments of this series I’ve been describing how Oracle estimates the join cardinality for single column joins with equality where the columns have histograms defined. So far I’ve  covered two options for the types of histogram involved: frequency to frequency, and frequency to top-frequency. Today it’s time to examine frequency to hybrid.

My first thought about this combination was that it was likely to be very similar to frequency to top-frequency because a hybrid histogram has a list of values with “repeat counts” (which is rather like a simple frequency histogram), and a set of buckets with variable sizes that could allow us to work out an “average selectivity” of the rest of the data.

“Hidden” Efficiencies of Non-Partitioned Indexes on Partitioned Tables Part III” (Ricochet)

In Part I and Part II of this series, we looked at how Global Indexes can effectively perform “Partition Pruning” when the partition keys are specified in SQL predicates, by only using those index entries that have a Data Object of interest stored within the index Rowids. In this piece, I’ll cover the key performance […]

OTW18 Sessions: Data Engineering and Latch Contentions

Thank you all those who came to my sessions at the OakTable World. Here are the presentations materials

Think Data; not Database
Data Engineering for Data Science
Latch Contentions Presentation Scripts

Running orachk as part of TFA with support tools bundle

I have previously written a number of posts about OSWatcher integration in Tracefile Analyzer (TFA) w/support tools bundle (available from My Oracle Support Document ID 1513912.1). Thus far I have neglected another useful tool available to administrators in the same package: orachk.

Summary of the environment

My lab system used for this post uses Oracle Restart 12.1.0.2 on top of Oracle Linux 7.4. I installed TFA 18.3 to /opt/tfa. If memory serves me right, TFA isn’t automatically deployed with Oracle Restart 12.1 as it now is with 12.2 and later.

[oracle@server1 ~]$ /opt/tfa/bin/tfactl print version
TFA Version : 18.3.3.0.0

A quick check using tfactl toolstatus reveals that orachk is indeed present:

Upgrade threat

Here’s one I’ve just discovered while trying to build a reproducible test case – that didn’t reproduce because an internal algorithm has changed.

If you upgrade from 12c to 18c and have a number of hybrid histograms in place you may find that some execution plans change because of a change in the algorithm for producing hybrid histograms (and that’s not just if you happen to get the patch that fixes the top-frequency/hybrid bug relating to high values).

Here’s a little test to demonstrate how I wasted a couple of hours trying to solve the wrong problem – first a simple data set:

OOW18 Session: Jumpstarting Docker

Thank you all for honoring me with a standing room only session, even at towards the end of the day. Much appreciated. Here is the presentation materials I used in the session. Please feel free to contact me if you need additional information.

Presentation