Wednesday, October 28, 2009

Three new "hints"

In version 11.2.0.1, Oracle introduced three new hints: IGNORE_ROW_ON_DUPKEY_INDEX, CHANGE_DUPKEY_ERROR_INDEX and RETRY_ON_ROW_CHANGE. These are not normal hints like we know them. Oracle writes:

Note:
The CHANGE_DUPKEY_ERROR_INDEX, IGNORE_ROW_ON_DUPKEY_INDEX, and RETRY_ON_ROW_CHANGE hints are unlike other hints in that they have a semantic effect. The general philosophy explained in "Hints" does not apply for these three hints.

That's strange. Why does Oracle mix up the concept of a hint by introducing three new hints that have a semantic effect? Now you have two kind of hints. You have optimizer hints and you have semantic hints. Both are silently ignored when you make a syntax error. But there is no way to tell with which kind you are dealing, unless you know them by heart. And it becomes harder to explain. It means you cannot advice to just gather statistics and remove all hints anymore. Couldn't this be solved by adding an extra keyword? Or were they reluctant to introduce another deviation from the ANSI standard? On the other hand, we now have some extra options available, and that's good. Let's explore them.


IGNORE_ROW_ON_DUPKEY_INDEX

From the documentation: "When a unique key violation is encountered, a row-level rollback occurs and execution resumes with the next input row."

An example:

rwijk@ORA11GR2> create table mytable
2 ( id number(2) primary key
3 , name varchar2(3) not null
4 )
5 /

Table created.

rwijk@ORA11GR2> insert into mytable values (4,'old')
2 /

1 row created.

rwijk@ORA11GR2> insert into mytable values (9,'old')
2 /

1 row created.

rwijk@ORA11GR2> select * from mytable
2 /

ID NAM
---------- ---
4 old
9 old

2 rows selected.

rwijk@ORA11GR2> insert /*+ ignore_row_on_dupkey_index(mytable(id)) */ into mytable
2 ( id
3 , name
4 )
5 select level
6 , 'new'
7 from dual
8 connect by level <= 10
9 /

8 rows created.

rwijk@ORA11GR2> select *
2 from mytable
3 order by id
4 /

ID NAM
---------- ---
1 new
2 new
3 new
4 old
5 new
6 new
7 new
8 new
9 old
10 new

10 rows selected.

So the insert statement with the ignore_row_on_dupkey_index was specified to insert 10 rows, but due to the current contents of the table, only 8 rows were actually inserted, since 4 and 9 already exist. In 10g Release 2 you could achieve a similar effect by using the DBMS_ERRLOG package. Using the DBMS_ERRLOG package is a bit more cumbersome and it is different since it logs ALL errors, not just the unique key violations.


CHANGE_DUPKEY_ERROR_INDEX

From the documentation: "The CHANGE_DUPKEY_ERROR_INDEX hint provides a mechanism to unambiguously identify a unique key violation for a specified set of columns or for a specified index. When a unique key violation occurs for the specified index, an ORA-38911 error is reported instead of an ORA-001.". An example with a table containing a primary key constraint and a unique key constraint:

rwijk@ORA11GR2> create table t
2 ( id number(2)
3 , name varchar2(3) not null
4 , constraint t_pk primary key (id)
5 , constraint t_uk unique(name)
6 )
7 /

Table created.

rwijk@ORA11GR2> insert into t values (1,'bla')
2 /

1 row created.


Whether the primary key constraint or the unique key constraint is violated, you'll always get an ORA-00001 error message:

rwijk@ORA11GR2> insert into t values (1,'abc')
2 /
insert into t values (1,'abc')
*
ERROR at line 1:
ORA-00001: unique constraint (RWIJK.T_PK) violated


rwijk@ORA11GR2> insert into t values (2,'bla')
2 /
insert into t values (2,'bla')
*
ERROR at line 1:
ORA-00001: unique constraint (RWIJK.T_UK) violated



With the change_dupkey_error_index hint you can reroute one of the ORA-00001 error messages to an ORA-38911 error. Which unique key violation is rerouted, depends on the columns or unique index name specified after the table name in the hint.

rwijk@ORA11GR2> insert /*+ change_dupkey_error_index (t(name)) */ into t
2 values (2,'bla')
3 /
insert /*+ change_dupkey_error_index (t(name)) */ into t
*
ERROR at line 1:
ORA-38911: unique constraint (RWIJK.T_UK) violated


rwijk@ORA11GR2> insert /*+ change_dupkey_error_index (t(id)) */ into t
2 values (1, 'abc')
3 /
insert /*+ change_dupkey_error_index (t(id)) */ into t
*
ERROR at line 1:
ORA-38911: unique constraint (RWIJK.T_PK) violated


This can be handy in PL/SQL to distinguish one unique key violation from the other, without having to parse the error message for the constraint name:

rwijk@ORA11GR2> declare
2 e_name_uk_violated exception;
3 pragma exception_init(e_name_uk_violated,-38911);
4 begin
5 insert /*+ change_dupkey_error_index (t,t_uk) */ into t values (1,'abc');
6 exception
7 when e_name_uk_violated then
8 dbms_output.put_line('Name should be unique, and this name already exists.');
9 when dup_val_on_index then
10 dbms_output.put_line('Some other unique constraint was violated');
11 end;
12 /
Some other unique constraint was violated

PL/SQL procedure successfully completed.

rwijk@ORA11GR2> declare
2 e_id_pk_violated exception;
3 pragma exception_init(e_id_pk_violated,-38911);
4 begin
5 insert /*+ change_dupkey_error_index (t,t_pk) */ into t values (1,'abc');
6 exception
7 when e_id_pk_violated then
8 dbms_output.put_line('Id should be unique, and this Id already exists.');
9 when dup_val_on_index then
10 dbms_output.put_line('Some other unique constraint was violated');
11 end;
12 /
Id should be unique, and this Id already exists.

PL/SQL procedure successfully completed.



RETRY_ON_ROW_CHANGE

This one puzzles me. The documentation says: "When you specify this hint, the operation is retried when the ORA_ROWSCN for one or more rows in the set has changed from the time the set of rows to be modified is determined to the time the block is actually modified.".

Ok, I can reproduce that. First create a table t with 50,000 rows and add a row level trigger that logs autonomously into a table called logtable. This way we can monitor the progress of the update statement in a separate session. And it slows down the update considerably, which is a good thing for the test.

rwijk@ORA11GR2> create table t
2 ( id number(9) primary key
3 , col number(9) not null
4 )
5 /

Table created.

rwijk@ORA11GR2> insert /*+ append */ into t
2 select level
3 , level
4 from dual
5 connect by level <= 50000
6 /

50000 rows created.

rwijk@ORA11GR2> create table logtable
2 ( last_id number(9)
3 , number_of_processed_ids number(9)
4 )
5 /

Table created.

rwijk@ORA11GR2> insert into logtable values (null,0)
2 /

1 row created.

rwijk@ORA11GR2> create procedure p (p_id in t.id%type)
2 is
3 pragma autonomous_transaction;
4 begin
5 update logtable
6 set last_id = p_id
7 , number_of_processed_ids = number_of_processed_ids + 1
8 ;
9 commit;
10 end;
11 /

Procedure created.

rwijk@ORA11GR2> create trigger t_aru
2 after update on t
3 for each row
4 begin
5 p(:new.id);
6 end;
7 /

Trigger created.


And now we do an update with the retry_on_row_change hint:

rwijk@ORA11GR2> update /*+ retry_on_row_change */ t
2 set col = col * -1
3 /

And while this statement is running, open a second session and run this:

rwijk@ORA11GR2> select *
2 from logtable
3 /

LAST_ID NUMBER_OF_PROCESSED_IDS
---------- -----------------------
14754 14754

1 row selected.

rwijk@ORA11GR2> update t
2 set col = 888888
3 where id = 40000
4 /

1 row updated.

rwijk@ORA11GR2> commit
2 /

Commit complete.

rwijk@ORA11GR2> select *
2 from logtable
3 /

LAST_ID NUMBER_OF_PROCESSED_IDS
---------- -----------------------
17507 17508

1 row selected.

So the row with id 40000 is updated and committed, while our statement from session 1 has not reached this row yet.

After session 1 completed the update it says:

50000 rows updated.


But here is what happened (from session 1):

rwijk@ORA11GR2> select *
2 from t
3 where id between 39999 and 40001
4 /

ID COL
---------- ----------
39999 -39999
40000 -888888
40001 -40001

3 rows selected.

Session 2 has set COL to 888888, and session 1 has set this value to -888888.

rwijk@ORA11GR2> select *
2 from logtable
3 /

LAST_ID NUMBER_OF_PROCESSED_IDS
---------- -----------------------
50000 89922

1 row selected.

By the number_of_processed_ids column you can deduce that the statement was restarted once it has fetched the block with ID 40000 in it. I also checked it in session 2 by reexecuting the last statement several times. The last_id column dropped back to 1 and the entire statement was re-executed. This leads to a total of 89,922 (50,000 + almost 40,000) number of processed ID's. So the statement was retried on detecting a row change. And the table doesn't need the ROWDEPENDENCIES clause to make this happen.

But what's the point? Oracle has its write consistency mechanism that makes sure a statement gets restarted under the covers if during a DML a row has been changed that has not been locked yet. For example, if we add a where clause to the update statement saying "id = col", then the statement sets out to update 50,000 rows. Now, if another session update the col value of ID 40000 to 888888, the update statement of session 1 will detect that automatically and restarts the statement. Without the need for a RETRY_ON_ROW_CHANGE hint:

From session 1 with the same DDL as above:

rwijk@ORA11GR2> update t
2 set col = col * -1
3 where id = col
4 /

And while this statement runs, update the ID 40000 record in session 2:

rwijk@ORA11GR2> select *
2 from logtable
3 /

LAST_ID NUMBER_OF_PROCESSED_IDS
---------- -----------------------
10783 10783

1 row selected.

rwijk@ORA11GR2> update t
2 set col = 888888
3 where id = 40000
4 /

1 row updated.

rwijk@ORA11GR2> commit
2 /

Commit complete.

rwijk@ORA11GR2> select *
2 from logtable
3 /

LAST_ID NUMBER_OF_PROCESSED_IDS
---------- -----------------------
12822 12823

1 row selected.


And then from session 1:

49999 rows updated.

rwijk@ORA11GR2> commit
2 /

Commit complete.

rwijk@ORA11GR2> select *
2 from t
3 where id between 39999 and 40001
4 /

ID COL
---------- ----------
39999 -39999
40000 888888
40001 -40001

3 rows selected.

rwijk@ORA11GR2> select *
2 from logtable
3 /

LAST_ID NUMBER_OF_PROCESSED_IDS
---------- -----------------------
50000 89999

1 row selected.

So here Oracle's write consistency mechanism made the statement rollback and re-execute. Which, by the way, is a good reason to not use autonomous transactions in database triggers: they might execute twice for a single row.

If someone can enlighten me with a use case for the RETRY_ON_ROW_CHANGE hint, I would be happy.

Sunday, October 18, 2009

All About Grouping at Oracle OpenWorld

On Thursday 10AM I gave a presentation at the Unconference called "All About Grouping". I really need to think more about titles of presentations, as two people came in with the question "What kind of grouping will you be talking about?". Fortunately, when I mentioned it was about grouping in SQL and especially the extensions ROLLUP, CUBE and GROUPING SETS, they stayed.

I had given the presentation two times before, but this was the first time I did it in English. In fact, it was the first time I've presented anything in English. Things went well I guess: it looked like everybody understood what I was saying. And there was some nice interaction as well. So it was a good warm-up for the UKOUG conference.

Thanks to all who attended. You can download the presentation and the scripts below.

Powerpoint of All About Grouping

Demonstration scripts of All About Grouping

Friday, October 16, 2009

Oracle OpenWorld 2009, the last three days

Tuesday was a very rainy day. I decided to take the shuttle bus to the Moscone and was therefore a little late for the first session. The sessions I visited this day were:

Dan Morgan - What's New in Eleven ... Dot Two (that Oracle won't be talking about)
In every release there are like two types of features. On the one side there are features that are picked up by marketing and which are given catchy names. On the other side are the features that are not picked up by marketing. Dan talked about those. Some I recognized because they are mentioned in the New Features Guide, but he also mentioned a few others. Dan is involved in the beta program, so he has already worked with 11g Release 2 for a year now.

Richard Foote - Oracle Indexes: Q & A Discussion
Richard had only two slides which he showed in the first minutes, and then it was time to throw questions at him. About indexes of course, and David Bowie. Jonathan Lewis was also in the room, so they kind of answered the questions together. One of the things that was nice to hear, is that neither Richard nor Jonathan had seen a useful use for the skip scan. Which was something I already suspected.

Steven Feuerstein - Coding Therapy for Software Developers
Another great presentation by Steven. His presentation skills are simply awesome. The subject of the presentation helps of course. However, there were a few statements here that I strongly disagree with. Statements like "SQL statements with 3 table joins, or 7; that's crazy!" or "It doesn't make sense for developers to have to learn the ins and outs of tuning SQL!". Then who is going to write that transactional API or that PL/SQL interface or batch program? I suspect that Steven had some other context in mind when making those statements.

Raj Mattamal - Giving Your Oracle Application Express a Web 2.0 Face-Lift
Raj talked about how to make your APEX application cooler for the end user. With so much jquery code out there, it's easy to do more than just the standard APEX stuff. It makes a big difference in how you interact with the application. Raj did a live demonstration, which I always like. And, man, does Raj talk fast!

Andrew Holdsworth - Current Trends in Real-World Database Performance
This seems to be an annual session, but it was the first one I attended. It's nice to hear from the people who do performance stuff only, what they encounter most. It seems bad SQL is still number one.

Blogger Meetup
Alex Gorbachev organized the annual bloggers meeting this year. And he made it a special one with t-shirts saying "I met more bloggers than you", where everybody was writing their names on the t-shirts. The wearer of the shirt with the most names won a HP Netbook. This is a great event for meeting the people behind the blogs you read (or don't read yet).


Wednesday I really tried to get some more sleep. Again I was awake at 4 AM, about the same time as every morning here in San Francisco. But unfortunately just staying in bed until late in the morning did not help. The sessions I followed were:

Arup Nanda - Upgrade Case Study: Database Replay, Snapshot Standby, and Plan Baselines
This session mainly outlined the use of new technologies you can use for upgrading your database to 11g. Unfortunately for me, the technologies themselves were not explained here; it was about when to apply which technology.

Rory McLean - Using Row-Level Security with Oracle Application Express
Last year I swore not to enter sessions anymore when I don't know the speaker, but this session was only 30 minutes and there wasn't anything more interesting to choose from at that time, so I decided to give this one a shot. And I wasn't disappointed. Rory McLean gave a nice and well-prepared presentation, including a demonstration of VPD within APEX. I didn't learn much new though, but that's because I'm already familiar with the subject.

S. Gopalakrishnan - Keynote: Seven Game Changing Trends: How Prepared Are You?
I realized too late that, to have a good press/analysts/blogger seat for Larry's keynnote, you'd have to be in the room before this one started. When Andrew Clarke and I arrived at Moscone North's hall D, the room was already full, except for some seats at the side. So we ended up behind a giant pillar just watching the big screens. This first keynote was aimed at managers to give some high-level overview about the strategic challenges they are awaiting. Nothing much of interest to me.

Larry Ellison and others - Keynote
Larry's keynote did not have a big new introduction, as expected. He first mentioned the success of Oracle Enterprise Linux. Then he went on to talk about Exadata V2 for OLTP databases. Some impressive numbers were there. I think my current client should definitely check this one out. After they have migrated to 11g Release 2 of course ... And he repeated the $10M challenge and the attack on IBM, which he also did at the Sunday keynote. The third part was about introducing pro-active Oracle support. If you give Oracle all the data about your configuration, then Oracle will inform you about which patches you should run to avoid running into the same problems as other customers did. The fourth part was about the new Fusion Applications. This software is still not ready to ship, but it looked they are well on their way. Fusion Applications will be the successor of E-Business Suite, Siebel, Peoplesoft, JD Edwards and the like. Oracle will support those applications for a long long time, but they just offer an alternative based on modern technologies like SOA.

The highlight of the keynote was of course the appearance of the governor of California, Arnold Schwarzenegger. He did a very funny talk about the importance of technology. How it had helped him in his career and how important it is to reduce global warming.


The keynote was a long show that should have ended at 16:30, but ended at 17:20. I therefore missed "SQL gone wild: Taming Bad SQL the Easy Way (or the Hard Way)" by Benoit Dageville about tuning SQL with some automated tools.

Appreciation Event
Andrew invited me to join him and Marco Gralike, Jacco Landlust, Anjo Kolk, Tim Hall and Doug Burns to the Appreciation Event on Treasure Island. An evening with music performances by Aerosmith, Roger Daltrey and the Wailers and two other bands I did not see. Normally I don't fancy Aerosmith's music that much (with the exception of "Dream On"), but I thought they gave a great show. Roger Daltrey played a couple of songs I did not recognize, but when he played some of The Who classics, the enthusiasm of the crowd -and mine- was greatly increased. After losing everyone of the original group and having a peek at the Wailers, I called it a day.


I was back at the hotel at 1AM. And again awake at 4AM. My own Unconference session started at 10AM (more about that in a separate post). After my session I returned my laptop to my hotel and went back to see "Real-World Database Performance Roundtable" by five people, among whom Greg Rahn, Graham Wood and Andrew Holdsworth. It was a really nice Q&A session with some interesting questions from the audience. But I was so tired that when I closed my eyes for a moment, I actually started sleeping. Some internal mechanism woke me up instantly and I decided I had to leave before I started disrupting the session with some loud snoring ... I grabbed a lunch, had a quick talk with H.Tonguç Yılmaz and Husnu Sensoy (DBA of the year!), and took some photographs to include in a presentation about Oracle OpenWorld for my department. I went back to my hotel and slept from 2PM to 7PM. Finally ...

Tuesday, October 13, 2009

Oracle OpenWorld 2009, the first two days

This year I'm visiting Oracle OpenWorld for the second time. Everything is now familiar, except for two differences. First one is the location of Oracle Develop, which has moved back to the Hilton Hotel, instead of the nearby Marriot Hotel. It is a nice walk, but sometimes you cannot chat after a session if you have a next session at the Moscone Center. Second difference is that it seems to me less people are attending the conference than previous year. But it's still a lot of people. One thing hasn't changed: my excitement about the conference.

The first two days were very busy. I'll quickly describe the sessions I went to. Sunday started early at 8:30 AM.

Alex Gorbachev - Demystifying Oracle Real Application Clusters Workload
A topic that with which I am not too familiar: my shop doesn't use RAC and I'm not a DBA. So I thought to use this one to get to know a little bit more. I had some trouble getting the context right in the beginning of the session, but when Alex started demonstrating the stuff on his laptop, everything fell into place. The demos were simply great. The sometimes complex load balancing algorithms were made perfectly clear by showing exactly the right things. A good start of the conference.

Steven Feuerstein - Why you should care about PL/SQL in Oracle Database 11g Now!
This was the first time I attended a session by Steven Feuerstein. Although I heard nothing new about PL/SQL, it was great to see how he presented. Very interactive and with a lot of humour.

Dimitri Gielis - Mastering an Oracle Application Express Page
In this session we learnt about what happens when APEX shows or processes a page. The session really helped to get some of my thoughts straight. One of the new things I heard was a simple button on the page design screen called "View" where you can see the order of the events that will happen. I saw that page lots of times now, but never bothered to use that.

Andrew Clarke - Designing PL/SQL with Intent
This session was totally different than I expected. It was a rather philosophical one comparing design patterns in PL/SQL with design patterns in town planning. At the end a few PL/SQL examples were shown that I liked a lot, since I preach them myself often as well, although not as well as Andrew did.

Michael Hichwa & David Peake - Oracle Application Express 4.0
A lot of nice new features were presented from the APEX development team themselves. The ones I liked most were Dynamic Actions, with which you can create client side validations declaratively, and the APEX listener. I'm not entirely sure, but it looks like the APEX listener opens up easier and supported ways to have proxy database authentication. Other new features include integrated Team Development for feedback and bug reports and the like, improved charts, websheets applications and improved tabular form handling.

Bryn Llewellyn - Online Application Upgrade
This session was renamed to "Edition Based Redefinition". Bryn very concisely talked about the 11g Release 2's killer feature: editions. Without using a demo he managed to make it perfectly clear what the feature is about and what it is not about. A few random things I wrote down: from now on an object is not defined by owner and name but by owner, name and edition. Only synonyms, views and PL/SQL of all kinds are editionable. You need to rename the table, create an editioning view with the same name as the table's former name. Then move all triggers, all privileges and all VPD policies to the editioning view. And last but not least: in 11gR2 it's now possible to add an extra column while the table is in use, whereas previously you would get an ORA-04021 lock timeout message.

Scott McNealy - Extreme innovation
This keynote was mainly aimed at assuring all Sun customers that Oracle is not trying to kill Sun's products but instead Oracle is increasing the investments in Sun's products. Larry Ellison did a good job at depicting IBM's range of hardware as inferior, slow, and not very green. He even offers $10M if your Oracle application doesn't run at least twice as fast. Now that's confidence!

ACE dinner
An evening at the Sens Restaurant for all Oracle ACEs and ACE Directors, where I've met a lot of old and new friends. Thanks to Lillian and all others at OTN for the perfect organization and a nice evening!


On Monday I attended these sessions:

Jonathan Lewis - How to become an expert
A very entertaining story about what you should do to become an expert. My rather free interpretation of the answer to this question is, to learn and really understand the basics first. If you understand the architecture of version 6, when you could still carry the documentation with you, then everything afterwards is really just a variation on the same theme. Understand exactly what Oracle will have to do when you send it a SQL statement and you'll be in a good position to know where the root cause of your problem is.

Tom Kyte - All About Metadata: Why Telling the Database About Your Schema Matters
A good story about why should have constraints, even in data warehouses. They may slow down DML a little, but it's extra information for the cost based optimizer to make smarter decisions. Extra access paths will become available. A new thing for me was to see the effect of creating a dimension object in combination with materialized view query rewrites. I have to see that one for myself one time.

Greg Rahn - Chalk & Talk: The Core Performance Fundamentals Of Oracle Data Warehousing
A story about Exadata and data warehouses and benchmarks. A different world than my usual one, which was nice. For example, this exadata machine is so fast, Greg recommends to not even use indexes anymore in most data warehouses. Of course, it all depends. I must say I was impressed by all numbers he mentioned. Those are not numbers I usually see in my clients OLTP database.

Jonathan Lewis - How to Hint
Again a very good presentation in which I heard quite a number of new things. To name a few: use_hash is only half a hint; it's better to combine this hint with the swap_join_inputs hint to explicitly state which query block should act as the in-memory table and which one as the probe table. Jonathan also had a great example of the no_merge hint, which finally became clear to me. And the parallel hint was not what I thought it would be. It's not instructing to always go parallel, but rather: if you are full scanning the table AND it's cost is lower than the serial plan, only then go parallel. Which means there might be a day your query will run serial even if it ran in parallel before, or vice versa. In this session I also learnt how an alien looks like ...

Chen Shapira - Visualization Session - Make your charts more effective for troubleshooting and presenting
This one definitely categorizes as "something completely different". Although the running example was about duration of exports, the session was about how to use graphs effectively and how they can effectively support your message, or not. Luckily I knew just enough about statistics to understand all graphs. I especially liked the Q-Q one.

Lucas Jellema - Continuous Application Application Evolution with Oracle Database 11g Release 2
Exactly the same topic as Bryn Llewellyn's session yesterday. Lucas also had a clear story and even included a few live demos that really helped. A very well explained introduction. The only unfortunate thing about both sessions is that no downsides were mentioned. And there must be several. Like test efforts being multiplied or worse performance because crossedition triggers are row level triggers. And we know what that means ... But still I liked the session for being very clear and the nice metaphores.

OTN Night
Here I just grabbed some food, but I could not find any familiar faces so I decided to leave early and catch some sleep. Which doesn't work by the way: I'm still awake each morning well before 4AM. Which is not uncommon for Europeans, it seems ...

More to come later.