Just a short note to say thank you to anyone that took the time to score, rate and comment on my presentations at the UKOUG this year. Whether you loved my talks, or hated my talks, I’m always flattered by anyone that takes the time to give the feedback that all speakers desire in order to better cater toward the preferences of the attendees at conferences. After all, its the attendees that are paying the money – they deserve the best that we can give.
I recently came across an example where someone wanted to update primary keys with an INSTEAD-OF trigger (on a view). It can be done, but I wanted to demonstrate that you need to take extreme care, because you can get some problems you might not have forseen.
If you choose to allow primary key updates, then you’ve pretty much got two options:
1) “simple update”
The incoming SQL is say:
update ... set col7 = :new.col7 where ...
and the instead-of trigger takes a “global” approach, ie, all columns
update ... set pk = :new.pk, col1 = :new.col1, col2 = :new.col2 where ...
where the column list includes the primary keys…
The problem here is that even an update of a primary to the same value, is still an update to the primary key – which means increased locking for any child tables. It means a pretty careful review of any foreign keys, and associated foreign key indexes.
2) “conditional update”
The instead-of trigger checks to see if the PK columns are being altered, and only includes them if appropriate
if :old.pk != :new.pk then update ... set pk = :new.pk, col1 = :new.col1, col2 = :new.col2 where ... else update ... set col1 = :new.col1, col2 = :new.col2 where ... end if;
Still challenges here – because a set operation has now become a row-by-row operation….Consider the following example:
SQL> create table T ( x number primary key, y date ); Table created. SQL> insert into T values (1,sysdate); 1 row created. SQL> insert into T values (2,sysdate); 1 row created. SQL> insert into T values (3,sysdate); 1 row created. SQL> insert into T values (4,sysdate); 1 row created. SQL> create or replace 2 view V as select * from T; View created. SQL> create or replace 2 trigger TRIG instead of update on V 3 for each row 4 begin 5 update T 6 set x = :new.x 7 where x = :old.x; 8 end; 9 / Trigger created.
That all seems straightforward enough – but now we’ll compare updates on the base table and view…
SQL> update T set x = x + 1; 4 rows updated. SQL> roll; Rollback complete.
No problems there – each primary key was incremented by 1, and then oracle ensured statement level integrity, ie, no duplicates
Now lets try the exact same operation on our view
SQL> update V set x = x + 1; update V set x = x + 1 * ERROR at line 1: ORA-00001: unique constraint (SYS_C00177683) violated ORA-06512: at "TRIG", line 2 ORA-04088: error during execution of trigger TRIG'
Uh oh….ker splat !
That’s actually a best case scenario – because at least the thing crashed. Things can get really gnarly once you start doing primary key updates across tables. For example:
SQL> create table PARENT ( p number primary key ); Table created. SQL> create table CHILD ( f_p number constraint FK references PARENT ); Table created. SQL> insert into PARENT values (1); 1 row created. SQL> insert into PARENT values (2); 1 row created. SQL> insert into PARENT values (3); 1 row created. SQL> insert into CHILD values (1); 1 row created. SQL> insert into CHILD values (2); 1 row created. SQL> insert into CHILD values (3); 1 row created. SQL> create or replace 2 trigger TRIG after update on PARENT 3 for each row 4 begin 5 update CHILD set f_p = :new.p where f_p = :old.p; 6 end; 7 / Trigger created. SQL> UPDATE PARENT SET p = p+1; 3 rows updated. SQL> select * from parent; P ---------- 2 3 4 SQL> select * from child; F_P ---------- 4 4 4
Nothing crashed – but we totally corrupted the child table. This last example is actually also contained in the standard Oracle docs.
And you also have to really ask the question – who or what is updating a primary key ? That’s just a bad idea, triggers or not.
Today was a bumper day for content.
Day 3 started with Doug Burns taking the p*ss out my presentation style…The cheek of the man ! All in good fun, although Doug is such a sensitive soul, the temptation to mess with him and claim offense was very very high. He gave a great talk on statistics on partitioned objects, which was very relevant given my client’s recent embarkation on a new data warehouse.
Then I ran back and forth between presentations on indexes and memory – its a tough call when you want to see two presenters, but you also dont want to be one of those rude people that leaves halfway through. I opted for the “sneak out when presenter turns their back” option, and seemed to get away with it.
Harald van Breederrode (http://prutser.wordpress.com/) gave a great talk on smart flash cache in Oracle. Unfortunately we are AIX, so its of limited use for us currently, but still a cool piece of technology. For those who don’t know, he’s legally blind, but still knows more about Oracle than just about anyone on the planet…astounding.
My final talk for the conference was on Optimizer stats, which is really a light hearted way of telling people to take more care and more time with their stats, and they’ll be amazed at the benefits they will get. I did this talk on Sunday as well – in front of optimizer boss Maria Colgan – so hopefully any of my gentle digs at the optimizer were well received. Of course, if not, then tough…I’ll always be a ranter
And that was about the size of it….next stop was Birmingham airport, and then a glorious 20 hours on a plane back to Perth.
If anyone cannot locate the slides for my talks, please drop me a line and I’lll send you them.
Firstly, I hope everyone had a great Christmas…I took a few weeks off in December which explains the blog hiatus…but back to the grind stone now
Well, day 2 started much like day 1….4am the internal body clock told me to get up, and thus I did some work on my slides, and then promptly fell back to sleep and missed the first session on the morning.
I went to Alex’s (http://nuijten.blogspot.com/) presentation on Analytics, a personal favourite topic of mine, since I’ve done talks myself on it, and I always like seeing someone else’s take on it. I also liked the clean format of his slides – in fact, one of the cool things I’ve seen at all conferences around the world in the past year, is the abandonment of “corporate templates” in favour of the just high quality content. I’d like to think I’ve had a small influence in that, but I like the trend.
Next up was my own talk on the saga of upgrades I had in 2011. Its a funny talk to give, because there’s not a lot of technical content but because of the stress and pain those upgrades had, I still get a physiological response when I’m doing the talk – I get sweaty and adrenalin – all memories of a time when we nearly lost millions of dollars with a down system. But the talk went well and I felt like a rock star because my ugly mug was on the big screen behind the stage
I was pretty much spent after that – I tried to catch John B’s talk on ASH and Jonathan Lewis’s talk on the optimizer, but as I felt myself falling to sleep in each, I had to give up and head back to the hotel for a sleep. A hour of recovery sleep, and I was back Luca’s talk on storage options at CERN and Ray Wang’s keynote.
Then it was off to the masquerade ball. I bombed out about 11pm, but apparently many Oakies partied hard into the night!
Well, as per normal when attending a conference in another country, it started very early….very early indeed. 4am the body clock decided it was time to get up, so eventually just gave up on trying to get back to sleep and started the day
First up was Kyle Hailey’s talk on SQL Tuning. Kyle is very passionate about his stuff, and his Visual SQL Tuning is all about graphing rather than “reading” the execution plan. He was in Hall 1, which this year is projecting the speaker up on the big screen next to the slides…Should be fun for my talk in that room tomorrow !
Next up was my partitioning talk, which went OK, although its one of those talks that just bludgeons people with streams and streams of content and examples. So by the end of the 60 minutes, you can tell when people are “full” and can’t take much more
One thing that’s interesting this year is the size of the exhibition hall. There seems to be a lot less exhibitors here – I wonder if they this is a reflection of the fact that generally conferences seem to be attracting technical people rather than middle management. (And technical people don’t have an operating budget, so the exhibitors are not seeing it as a worthwhile investment…who knows).
Here’s a picture of the hall in case anyone hasn’t seen how these things are mapped out:
The afternoon sessions included Julian Dyke and Martin Bach’s talk about clusterware upgrades – both of which tend to mirror the content I’ll be doing tomorrow, namely …. its painful.
(Day 2 and Day 3 will be coming in a couple of weeks….I’m off to Rottnest (http://www.rottnestisland.com/) for a couple of weeks – no laptop, no internet…
UKOUG talks for me if you’re keen
Monday 11:05, Hall 9
A look at the partitioning options at your disposal in Enterprise Edition from its inception in Oracle 8 up to array of improvements that have arrived in 11g. We’ll look at the various types of partitioning, how to decide whether you should be using it, how to get the best out of it, and where it might trip you up.
Tuesday 11:15, Hall 1
Clusterware upgrade diary
In 2010, a performance issue in the version of the database that a client was using, marked the commencement of a year long struggle to get the desired results, including numerous patches and upgrades. Come for a ride on the journey we’ve just completed – hear about this client’s experiences, and get some advice on how to diagnose performance problems, tackle upgrades, deal effectively with support, how to select the right platform, whilst keeping your sanity at the time
Wednesday 12:30, Hall 5
Optimizer Stats – a fresh approach
The optimizer must try to be all things to all people, and similarly, the collection of optimizer statistics must try to satisfy the needs of all. And many DBA’s just leave it at that. But with a little more effort and discipline, we can achieve much more than a "one-size-fits-all" policy. We’ll look at the tools now available under DBMS_STATS in Oracle 11g to get more stability and better performance with optimizer statistics.
There is nothing better than
a) bumpy flight from Perth to Dubai
b) bumpy flight from Dubai to Birmingham
then to open the laptop bag and find
a) a laptop
b) NO laptop power supply
So this morning was mad panic, getting taxis to PC-World to buy a universal power supply, which beeps when the PC is started with “Warning Warning – power supply is not up to par”…..
But power is power….at least the week can only get better than this !
OakTable sunday about to come to a close – some great talks as always. Its almost beer o’clock
Well, its off to the UK for one of the best conferences going…the UKOUG conference in Birmingham. If you’re coming, please join me at my talks – I’m giving one every single day, including Sunday, so you’re spoilt for choice 🙂
(Disclaimer: I’ve published titles through Apress, but they are not paying me to post this …)
Just noticed on the Apress site that you can buy ebooks today for $15.
I’ve dragged this little demo up from the past, because friend Toon (http://thehelsinkideclaration.blogspot.com) has been blogging about triggers. This demo shows the overhead of triggers in terms of doing things that can be easily done without triggers. But I do stress – I’m with Toon on this one – this is not the same as having a blanket ban on triggers.
Someone asked why having triggers to populate primary keys with a sequence was a bad idea…so a simple demo is called for.
SQL> create table T ( x number ); Table created. SQL> create sequence S cache 1000; Sequence created. SQL> create or replace 2 trigger TRG before insert on T 3 for each row 4 begin 5 :new.x := s.nextval; 6 end; 7 / Trigger created. SQL> set timing on SQL> insert into T 2 select rownum from dual 3 connect by level <= 100000; 100000 rows created. Elapsed: 00:00:08.79
So it takes 9 seconds to load 100,000 rows – and everyone starts asking for more CPU, more RAM, faster servers, etc etc etc….
But lets try it without the trigger, and just code the insert using the sequence directly
SQL> insert into T 2 select s.nextval from dual 3 connect by level <= 100000; 100000 rows created. Elapsed: 00:00:01.81
Bottom line – if you want a 400% performance penalty on insert, then triggers for sequences are just what you are looking for 🙂
If you’re in Birmingham in the first week of December, (and you’re reading this blog), chances are you are coming to the UKOUG conference.
This is one of my favourite conferences – the organisers treat the speakers brilliantly, the venue is perfect (even if the weather is not), and the quality of the presentations is always amazing.
This year, the Oak Table are having some fun – you can come play Ping Pong (table tennis), but instead of bats, you’ll be using an Oracle technical reference of your choice
Check out the agenda here:
Sometime things just leap out at you when you least expect it.
Let’s say I’ve got a table that I’d like to shrink the space on. No problems there.
SQL> drop table T purge; Table dropped. SQL> create table T as 2 select rownum x from dual 3 connect by level create index IX on T ( x ); Index created. SQL> alter table T enable row movement; Table altered. SQL> alter table T shrink space; Table altered.
So far so good. But then I decide that I’d like to scan this table in descending order through the index column. So I recreate my index as DESCENDING.
SQL> drop index IX; Index dropped. SQL> create index IX on T ( x desc); Index created. SQL> alter table T shrink space; alter table T shrink space * ERROR at line 1: ORA-10631: SHRINK clause should not be specified for this object
So what happened ? Well, a descending index is implemented as function based index, and shrink space on tables that have function based indexes is prohibited.
As with all things, prudent testing to find the boundary cases in Oracle is important.