A fine Oracle man.
This is a bug encountered at a client site, and the test script was carefully prepared by Mark Hoxey (http://markhoxey.wordpress.com/) but it just shows how seemingly independent features in Oracle sometimes collide. Some of the elements of the script have been omitted for brevity, but if you’d like a copy, drop me a line.
We have a partitioned table with 4,000,000 rows in it.
SQL> CREATE TABLE block_test 2 (id NUMBER(10) NOT NULL 3 ,tstamp TIMESTAMP NOT NULL 4 ,val NUMBER(10) NOT NULL 5 ,padding CHAR(50) NOT NULL) 6 TABLESPACE users 7 PARTITION BY RANGE (tstamp) 8 (PARTITION block_test_201201 VALUES LESS THAN (TO_TIMESTAMP('01/02/2012','dd/mm/yyyy')) 9 ,PARTITION block_test_201202 VALUES LESS THAN (TO_TIMESTAMP('01/03/2012','dd/mm/yyyy')) 10 ,PARTITION block_test_201203 VALUES LESS THAN (TO_TIMESTAMP('01/04/2012','dd/mm/yyyy')) 11 ) 12 NOLOGGING 13 / Table created. SQL> INSERT /*+ APPEND */ INTO block_test 2 WITH generator AS 3 (SELECT -- materialize 4 ROWNUM AS n 5 FROM dual 6 CONNECT BY level <= 2000) 7 SELECT ROWNUM AS id 8 , TO_TIMESTAMP('01/01/2012','dd/mm/yyyy') + NUMTODSINTERVAL(ROWNUM/4000000*75*24*60*60,'SECOND') 9 , TRUNC(ROWNUM/10000) 10 , 'X' 11 FROM generator v1 12 , generator v2 13 / 4000000 rows created.
Now, working under the presumption that some of the data is now “old”, we opt to compress it to make it more efficient to query and consume less space and
SQL> ALTER TABLE block_test MOVE PARTITION block_test_201201 PCTFREE 0 COMPRESS UPDATE INDEXES 2 / Table altered. SQL> ALTER TABLE block_test MOVE PARTITION block_test_201202 PCTFREE 0 COMPRESS UPDATE INDEXES 2 / Table altered.
Now here is where things get interesting…Lets now resume inserting into the table in the normal way, ie, grab the next primary in sequence and start inserting. For each insert, we’ll record how many session IO’s were done to perform the insert. Typically, we’d expect very few – just a few on the primary key index and the table block itself.
SQL> DECLARE 2 TYPE t_stats_tab IS TABLE OF v$mystat%ROWTYPE; 3 4 c_iterations CONSTANT NUMBER(8) := 1000; 5 c_threshold_io CONSTANT NUMBER(4) := 100; 6 c_threshold_sec CONSTANT NUMBER(8) := 1; 7 8 l_start_tstamp TIMESTAMP; 9 l_end_tstamp TIMESTAMP; 10 l_start_io NUMBER(8); 11 l_end_io NUMBER(8); 12 13 l_start_stats t_stats_tab; 14 l_end_stats t_stats_tab; 15 16 l_min_duration INTERVAL DAY TO SECOND(6); 17 l_max_duration INTERVAL DAY TO SECOND(6); 18 19 l_min_io NUMBER(8); 20 l_max_io NUMBER(8); 21 22 l_id block_test.id%TYPE; 23 l_tstamp block_test.tstamp%TYPE; 24 [snip] 50 BEGIN 51 52 FOR i IN 1..c_iterations 53 LOOP 54 [snip] 59 SELECT SYSTIMESTAMP 60 , value 61 INTO l_start_tstamp 62 , l_start_io 63 FROM v$mystat 64 WHERE statistic# = 11; -- session logical reads 65 66 INSERT INTO block_test 67 (id, tstamp, val, padding) 68 VALUES 69 (block_test_id_seq.NEXTVAL, SYSTIMESTAMP, TRUNC(i/1000), 'X') 70 RETURNING id, tstamp 71 INTO l_id, l_tstamp; 72 73 SELECT SYSTIMESTAMP 74 , value 75 INTO l_end_tstamp 76 , l_end_io 77 FROM v$mystat 78 WHERE statistic# = 11; -- session logical reads 79 [snip] 84 COMMIT; 85 86 -- if we've exceeded what we've set as duration or IO thresholds then 87 -- log the details 88 IF (EXTRACT(SECOND FROM (l_end_tstamp-l_start_tstamp)) >= c_threshold_sec) 89 OR ((l_end_io - l_start_io) >= c_threshold_io) 90 THEN 91 dbms_ouput.put_line ('Entry ' || TO_CHAR(l_id) || ' started at ' || TO_CHAR(l_tstamp,'dd/mm/yyyy hh24:mi:ss') || 92 ', took ' || TO_CHAR(EXTRACT(SECOND FROM (l_end_tstamp-l_start_tstamp))) || ' seconds' || 93 ' and generated ' || TO_CHAR(l_end_io - l_start_io) || ' IOs'); 94 END IF; 95 [snip] 105 END LOOP; 106 107 END; 108 / Entry 4000170 started at 08/03/2012 20:52:31, took 3.431496 seconds and generated 19549 IOs
Bang ! One of the inserts took 3 seconds (an eternity in Oracle speak) and burned through nearly 20,000 logical IO’s ?
Setting a couple of events on the advice of Oracle Support
SQL> ALTER SESSION SET EVENTS '10612 trace name context forever,level 3' SQL> ALTER SESSION SET EVENTS '10224 trace name context forever,level 1'
shows some interesting diagnostics – it looks like a large chunk of blocks are being encountered as candidates for the insert, but are in fact full, and hence rejected from consideration – we see thousands of entries like:
ktspfsrch: Returns: BlockDBA:0x01029a2f kdisnew_bseg_srch_cbk reject block -mark full,dba 0x01029a2f,time 20:52:31.332 ktspfupdst: fdba: 0x01029a20 offset: 15 nblks: 1 newstate: 1 kdisnew_bseg_srch_cbk rejecting block ,dba 0x01029a2f,time 20:52:31.332 ktspfsrch:Cbk didnot like 0x01029a2f
This is on 184.108.40.206….Interestingly on 220.127.116.11, we see the same entries in the trace file, but you do not get the slow down….
I did three talks for the tasmanian user group. Its a small user group, but a credit to the committee that they keep it strong, and keep it going year after year. It was a pleasure to go there.
And if you’re reading this, I hope
a) you’re a member of your local user group
b) you attend and support any events that your local user group has
c) you will (perhaps one day in future) speak at your local user group.
User groups MAKE the Oracle community. We share, we learn, we strive to make our solutions better for our customers. I’m lucky enough to really enjoy getting up in front of an audience and talking about the Oracle things I’m passionate about. I know for others – getting up in front of a crowd sounds like being tortured slowly.
But invest in your user group, and it will pay your back two fold.
I’ll be doing a talk on Flashback on Feb 29. Details below – hope to see you there.
Where: Oracle Australia: Level 2, 66 Kings Park Road, West Perth
When: Wednesday, February 29, 2012. Breakfast starts 7:30am, and the talk from 8-9am
RSVP: By RSVP Sunday 26th February 5pm by emailing email@example.com
Cost: Free to all AUSOUG members. Ticket parking on Walker St, just off Kings Park Road, or catch the Red CAT, with a short walk from corner of Outram/Ord.
Abstract – Flashback:
We have all heard the term "adrenalin rush". It comes about 3 seconds after you press the Commit button and you realise that you probably needed to have a WHERE clause on that "delete all rows from the SALES table" SQL statement. Or it might come just after you run "drop table" on the Production database, when you were just so sure that you were logged onto the Test system. This session will discuss how you can use the FLASHBACK features in 10g and 11g to overcome this uniquely human condition
I’m in Hobart Tasmania for a half day on Monday (Feb 29). If you’re in town, please drop in and say Hi – should be a fun morning
Here is the agenda
09:00 – 09:15 am General introduction
09:15 – 10:15 am Presentation – Diary of an 11.2 upgrade
10:15 – 10:30 am Morning tea
10:30 – 11:30 am Presentation – Optimizer stats strategies – a fresh approach
11:30 – 12:30 pm Presentation – Miscellaneous SQL features
12:30 – 01:30 pm Lunch, general questions and networking
Morning tea and a light lunch will be provided so it would be very much appreciated if you could RSVP so we can correctly cater for the event by email to firstname.lastname@example.org
See you there !
Its been a long hard painful road….
But finally, we no longer have to ensure that Flash-based mistake that was unleased onto the world a few years back
I freely admit that I’ve been a scathing critic of the MOS flash interface, both on newsgroups and internally via feedback to the support team themselves, and to be honest, I thought that this would all be falling on deaf ears.
So I’m happy to say that somewhere inside the organisation, people were obviously listening to myself (and the many others), because we finally have an interface that looks promising and at first usage, seems functional and practical.
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…