I guess everybody who is working with Oracle databases and has been involved with Oracle Exadata in any way knows about smartscans. It is the smartscan who makes the magic happen of full segment scans with sometimes enormously reduced scan times. The Oracle database does smartscans which something that is referred to as ‘offloading’. This is all general known information.
But how does that work? I assume more people are like me, and are anxious to understand how that exactly works. But the information on smartscans is extremely scarce. Of course there is the Oracle public material, which looks technical, but is little/nothing more than marketing. On My Oracle Support, I can’t find anything on the inner working. Even in the ‘Expert Oracle Exadata’ book (which I still regard as the best source of Exadata related information) there is no material on the mechanics of smartscans.
Prompted by a pingback on this post, followed in very short order by a related question (with a most gratifying result) on Oracle-L, I decided to write up a note about another little optimizer enhancement that appeared in 12c. Here’s a query that differs slightly from the query in the original article:
Enkitec is the best consulting firm for hands on implementation, running and troubleshooting your Oracle based systems, especially the engineered systems like Exadata. We have a truly awesome group of people here; many are the best in their field (just look at the list!!!).
This is why I am here.
#444; font-family: 'Helvetica Neue',Helvetica,Arial,sans-serif; margin: 0 0 1em;">#000000;">I’m often asked what management pack is used by what feature and there is actually a very easy way to find out this information in the EM12c console.
Here’s a little tip that might be helpful occasionally when you’re trying to work out why the optimizer transformation you were expecting isn’t appearing
If you’ve ever checked the 10053 trace (and who wants to do that for a complex query) you may have noticed lines like:
SU: SU bypassed: Remote table referenced.
So now you know that SU – Subquery Unnesting – has limitations in distributed queries.
When I first saw a line like this, it crossed my mind that it would be useful to keep a reference list of features that could be reported as bypassed, which I do through a simple unix line:
strings -a oracle | grep -i bypassed > bypassed.txt
If you need a reference for the various short codes for transformations you can find it near the top of the 10053 trace, looking like this:
I’ve just filled in my Oracle ACE Director Self-Evaluation…
The way the tour dates worked this year, I got the LAOTN 2013, Nordic 2013 and Yathra 2014 tours in a single assessment year, plus all the one-off events. It looks crazy-busy…
Most of my events this year have been packed into the first half of the year, so I think next years assessment will look a little sparse by comparison…
I’ll be at the UKOUG Next Gen event at Birmingham City University on the 15th May.
My session is on “The Importance of Community”. I guess my blog readership is not exactly target audience for an event like this, but if you know any students or recent graduates who are looking to start a career in IT, or you are an employer looking for fresh new talent, you should come along to the event.
I’ve just returned from the Enterprise Manager Customer Advisory Board, aka EM CAB. This is a special invitation-only program, provided by Oracle, that offers power users and top implementers of Oracle Enterprise Manager to come onsite and discuss the latest new features and enhancements to the product. An NDA, (non-disclosure agreement) is required as part of the invitation due to the opportunity to revi
I received an email recently asking me if I knew how Oracle found specific rows and columns in a compression unit. This is a topic that I’ve spoken about a couple of times, and I’ve published several notes on the blog about it, including an image of a critical slide from one of my presentations, and I was expecting to find some notes somewhere about Oracle catalogues all the bits and pieces.