Most of you know I don’t like #ff00ff;">pink, but I also am not a fan of #ffff00;">yellow.
Here’s a convenient aid to trouble-shooting that appeared in 11g with its enhancements to setting events. It’s a feature that may help you to work out (among other things) why a given statement seems to have a highly variable performance profile. If you can find the SQL_ID for a parent cursor you can enable tracing for just that cursor whenever it executes, whoever executes it.
So back at the end of the EM CAB, I’d received an email stating I had an over-sized database and too many hits to continue on Go Daddy as the host for my website. I called into tech support to understand what options I had, but was never told that I had any option but leaving Go Daddy as my hosting service. I was told I had two weeks per the email, but asked for three and was told on the phone that wasn’t a problem. I then did some research a
I really enjoyed Oracle Midlands Event #3 last night. Christian Antognini spoke on “12c Adaptive Query Optimization” and “Row Chaining and Row Migration Internals”. I certainly learnt a lot, which is the whole point of this stuff!
After the event a few of us went across to a local pub and the geek talk continued. Cool++.
Thanks very much to Christian for coming all that way to speak to us. Thanks to those good people at Reg Gate for sponsoring the event. Thanks also to the Oracle ACE Program for letting Christian come to visit us.
The next event is on Monday 14th July. Hope to see you there!
Does Oracle ignore hints – not if you use them correctly, and sometimes it doesn’t ignore them even when you use them incorrectly!
Here’s an example that I’ve run on 184.108.40.206 and 220.127.116.11
create table t1 as with generator as ( select --+ materialize rownum id from dual connect by level <= 1e4 ) select rownum id, rownum n1, rpad('x',100) padding from generator v1 ; begin dbms_stats.gather_table_stats( ownname => user, tabname =>'T1', method_opt => 'for all columns size 1' ); end; / create index t1_i1 on t1(id); alter index t1_i1 unusable; select n1 from t1 where id = 15; select /*+ index(t1 (id)) */ n1 from t1 where id = 15;
Any guesses about the output from the last 4 statements ?
Here’s a little known feature of Exadata – you can use a Bloom filter computed from a join column of a table to skip disk I/Os against another table it is joined to. This not the same as the Bloom filtering of the datablock contents in Exadata storage cells, but rather avoiding reading in some storage regions from the disks completely.
So, you can use storage indexes to skip I/Os against your large fact table, based on a bloom filter calculated from a small dimension table!
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: