Yesterday I came back from my third Oracle OpenWorld. This year I was not on a bloggers pass, so I feel less obliged to cover the event. But I know there are people out there that like to read about my experiences and I still like it very much at San Francisco, so here is a small write-up about my experiences at Oracle OpenWorld 2010.
The event started at Sunday, but later than usual: 12:30 PM I think, instead of 8:30 AM in previous years. For the first time I had to wait in a queue to register and receive the conference pass. I also received an Oracle Develop t-shirt, a conference book and the Appreciation Event wristband, but to my surprise no bag. The guy behind the counter pointed at the screen that said what he was allowed to give, and the screen said nothing about a bag. It felt strange, because except for Frits Hoogland, everybody seemed to wear that bag. Not really a problem, since I have enough of them at home. But I did not want to carry the stuff with me all day, so I decided to go back to the hotel and drop the book and t-shirt.
The sessions I saw on Sunday differed much in quality. I especially liked Cary Millsap's lesson in how to prepare for and deliver a presentation. Very inspiring. Even though I knew all content about messed-up apps already, because they are on his blog as well. I also like Richard Foote's presentation about indexes and Chris Muir's one on load testing APEX applications. In the evening, all ACEs and ACE Directors were transported to a very nice boat for a trip on the bay, with nice food, conversations and magnificent views of San Francisco by night.
On Monday I visited four sessions, which were all pretty good. First a live PL/SQL Challenge by Steven Feuerstein, with some interesting questions. No prices here, but Steven had created a secondary challenge for the people present at Oracle OpenWorld, where I won a second place and a 100 dollar Amazon gift card. Thanks Steven! Another session I enjoyed was Lucas Jellema's and Alex Nuijten's Xenogenetics session, about some use cases for using Java in PL/SQL, among others. In the evening I visited OTN Night, mainly for some food :-).
Tuesday I went to Oracle Closed World together with Frits Hoogland, where I saw a very good presentation of Kyle Hayley about Visual SQL Tuning with his Embarcadero product. The steps I intuitively take to solve performance problems with my explain plans and tkprofs were nicely illustrated with graphs. Pick as a starting point the table with the best filter predicates, then join down (to details) and then join up (to master tables). When visualizing all that, it becomes obvious. At Closed World I received a t-shirt which I wore on Thursday. This generated quite some comments ("Is that from Mogens? He is a funny guy.") and even from strangers who liked the "Meat.Beer.Explode." phrase. I returned to my hotel pretty early to do some preparation for my own session and in the evening I went to Ruby Skye for the Oracle Benelux Party.
Wednesday was my own presentation called "Oracle Database 11g's Result Cache". At Hotel Nikko in the Bay View room. This room was on the 25th floor and had a magnificent view of the bay. The session went well, and I was exactly on time. Unfortunately, I heard later that not everybody could see the SQL*Plus screen well, even though I had specifically asked the guy at the back of the room if he could read it... I received some interesting questions at the end, which I will try to answer in some future blogpost. Next up was the Bloggers Meetup, which was fun. And afterwards, Roel Hartman, Jacco Landlust and his girlfriend Margot and me went to the appreciation event, where I saw Don Henley, the Black Eyed Peas (slow start, but great ending) and two songs of the Steve Miller Band.
On Thursday I only saw two sessions, of which I really liked one. It was Jože Senegačnik's session called Query Transformations about all kinds of transformations the cost based optimizer performs when hard parsing queries. These transformations are visible in the 10053 trace file, which I rarely use. The session was great because it fitted great with what I already knew, but there were lots of interesting little details I did not know. After gathering at the OTN Lounge with some fellow ACEs and skipping the wrap-up party, I headed back to my hotel.
Sunday, September 26, 2010
OOW10
Posted by
Rob van Wijk
at
5:34 PM
1 comments
Friday, September 17, 2010
Upcoming conferences
Tomorrow I'm off to Oracle OpenWorld for the third time in a row. I'm looking forward to a week attending great sessions, meeting friends, visiting the appreciation event, the Oracle ACE dinner and the Blogger's Meetup and presenting my session about the result cache. If you want to attend that session, the session details are:
ID#: S319106
Title: Oracle Database 11g’s Result Cache
Date: 22-SEP-10
Time: 13:00-14:00
Venue: Hotel Nikko
Room: Bay View
I will also be giving the result cache presentation at two other conferences closer to home.
At October 5, Planboard - known for their successful Oracle DBA Symposiums - organizes their first Oracle Developer Symposium. This is a conference you should not miss if you are a Dutch speaking Oracle developer. One day, with three tracks: Database, APEX and Fusion Middleware. More details can be found here.
At October 27 and 28, ODTUG OPP 2010 will be held in Brussels. They have combined two conferences: one about PL/SQL and one about APEX, with 5 tracks in total.
Last wednesday I rehearsed the presentation at a knowledge session at CIBER. I will be making some adjustments, but the Wednesday version of my presentation can be downloaded here:
The powerpoint and the scripts.
Posted by
Rob van Wijk
at
9:53 PM
1 comments
Thursday, July 15, 2010
European Summer Time
I needed a function to determine how many hours fit in a given day. In the Netherlands, a function that does "return 24;" is not good enough. In the northern hemisphere, Daylight Saving Time is quite common. And in Europe, except Iceland, we have European Summer Time which states that the clock moves one hour forward on the last Sunday in March and it moves one hour backwards on the last Sunday in October. Which means there is a day in March that consists of 23 hours and in October there is one with 25 hours.
So, for my function I needed two other functions: one that returns the date where European Summer Time starts and one that returns the date where European Summer Time ends. I found existing code which - if it had been modularized - looked like this:
create function european_summer_time_start1 (p_year in number)
return date
is
begin
execute immediate
'alter session set nls_date_language = "English"'
;
return
next_day(to_date('01-04-'||p_year,'dd-mm-rrrr')-1, 'sunday') - 7
;
end european_summer_time_start1;
and
create function european_summer_time_end1 (p_year in number)
return date
is
begin
execute immediate
'alter session set nls_date_language="English"'
;
return
next_day(to_date('01-11-'||p_year,'dd-mm-rrrr')-1, 'sunday') - 7
;
end european_summer_time_end1;
It uses the NEXT_DAY function to determine the next sunday after March 31 and October 31, and then subtracts a week to get the last Sunday of March and October. The second parameter ('sunday') is in the date language of the session. And to be sure the function runs correctly, the nls_date_language parameter is set. I don't like this at all, because it sets a session parameter without setting the value back after the function has finished. Code might run differently depending on whether the function has been run previously in the session or not. I want the code to be NLS independent and I came up with these two functions:
create function european_summer_time_start2 (p_year in number)
return date
is
begin
return
trunc
( to_date(to_char(p_year) || '0401','yyyymmdd')
, 'iw'
) - interval '1' day
;
end european_summer_time_start2;
and
create function european_summer_time_end2 (p_year in number)
return date
is
begin
return
trunc
( to_date(to_char(p_year) || '1101','yyyymmdd')
, 'iw'
) - interval '1' day
;
end european_summer_time_end2;
Here I use the TRUNC-function to set the date value back to the beginning of the ISO-week, which is always on Monday, regardless of NLS-settings. Then subtract one day and we have the last Sunday of March/October.
Then I saw the formula on Wikipedia in the same link I mentioned earlier:
Formula used to calculate the beginning of European Summer Time:
Sunday (31 − (5 * y ÷ 4 + 4) mod 7) March at 01:00 GMT
Formula used to calculate the end of European Summer Time:
Sunday (31 − (5 * y ÷ 4 + 1) mod 7) October at 01:00 GMT
Implementing these formulas lead to a third variant of the two functions:
create function european_summer_time_start3 (p_year in number)
return date
is
begin
return
to_date
( to_char(p_year) ||
'03' ||
to_char(31 - trunc(mod(5 * p_year / 4 + 4, 7)))
, 'yyyymmdd'
)
;
end european_summer_time_start3;
and
create function european_summer_time_end3 (p_year in number)
return date
is
begin
return
to_date
( to_char(p_year) ||
'10' ||
to_char(31 - trunc(mod(5 * p_year / 4 + 1, 7)))
, 'yyyymmdd'
)
;
end european_summer_time_end3;
Next, I compared the three variants. They return the right dates:
rwijk@TEST10> select european_summer_time_start1(year) ws1
2 , european_summer_time_start2(year) ws2
3 , european_summer_time_start3(year) ws3
4 , european_summer_time_end1(year) sw1
5 , european_summer_time_end2(year) sw2
6 , european_summer_time_end3(year) sw3
7 from ( select 1999 + level year
8 from dual
9 connect by level <= 11
10 )
11 /
WS1 WS2 WS3 SW1 SW2 SW3
---------- ---------- ---------- ---------- ---------- ----------
26-03-2000 26-03-2000 26-03-2000 29-10-2000 29-10-2000 29-10-2000
25-03-2001 25-03-2001 25-03-2001 28-10-2001 28-10-2001 28-10-2001
31-03-2002 31-03-2002 31-03-2002 27-10-2002 27-10-2002 27-10-2002
30-03-2003 30-03-2003 30-03-2003 26-10-2003 26-10-2003 26-10-2003
28-03-2004 28-03-2004 28-03-2004 31-10-2004 31-10-2004 31-10-2004
27-03-2005 27-03-2005 27-03-2005 30-10-2005 30-10-2005 30-10-2005
26-03-2006 26-03-2006 26-03-2006 29-10-2006 29-10-2006 29-10-2006
25-03-2007 25-03-2007 25-03-2007 28-10-2007 28-10-2007 28-10-2007
30-03-2008 30-03-2008 30-03-2008 26-10-2008 26-10-2008 26-10-2008
29-03-2009 29-03-2009 29-03-2009 25-10-2009 25-10-2009 25-10-2009
28-03-2010 28-03-2010 28-03-2010 31-10-2010 31-10-2010 31-10-2010
11 rows selected.
But as mentioned in the Wikipedia article, the third variant produces wrong results in 2100, which is not a leap year:
rwijk@TEST10> select european_summer_time_start1(year) ws1
2 , european_summer_time_start2(year) ws2
3 , european_summer_time_start3(year) ws3
4 , european_summer_time_end1(year) sw1
5 , european_summer_time_end2(year) sw2
6 , european_summer_time_end3(year) sw3
7 from ( select 2097 + level year
8 from dual
9 connect by level <= 5
10 )
11 /
WS1 WS2 WS3 SW1 SW2 SW3
---------- ---------- ---------- ---------- ---------- ----------
30-03-2098 30-03-2098 30-03-2098 26-10-2098 26-10-2098 26-10-2098
29-03-2099 29-03-2099 29-03-2099 25-10-2099 25-10-2099 25-10-2099
28-03-2100 28-03-2100 27-03-2100 31-10-2100 31-10-2100 30-10-2100
27-03-2101 27-03-2101 26-03-2101 30-10-2101 30-10-2101 29-10-2101
26-03-2102 26-03-2102 25-03-2102 29-10-2102 29-10-2102 28-10-2102
5 rows selected.
It probably is the faster function though, as it doesn't need date arithmetic and ISO week calculation, but I was curious in how much it differs. Here is the test that I used:
rwijk@TEST10> var N number
rwijk@TEST10> exec :N := 100000
PL/SQL procedure successfully completed.
rwijk@TEST10> set timing on
rwijk@TEST10> declare
2 l_date date;
3 begin
4 for i in 1..:N
5 loop
6 l_date := european_summer_time_start1(2010);
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:04.20
rwijk@TEST10> declare
2 l_date date;
3 begin
4 for i in 1..:N
5 loop
6 l_date := european_summer_time_start2(2010);
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.30
rwijk@TEST10> declare
2 l_date date;
3 begin
4 for i in 1..:N
5 loop
6 l_date := european_summer_time_start3(2010);
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.79
The test was executed several times with consistent timings. So now I have to decide whether I use the function that works fastest but breaks in 2100, or one that runs a little slower but keeps on working. Performance doesn't really matter here, so I'll stick with the second variant.
Posted by
Rob van Wijk
at
10:10 PM
0
comments
The PL/SQL Challenge effect
In Google Analytics I noticed a strange peak in my page visits. On a normal weekday when I haven't posted something new, approximately 200 people visit one or more blogposts here. But on Thursday July 8, there were 299. When zooming in on that day, I noticed that my blogpost about SAVE EXCEPTIONS was visited 101 times, where 5 or 10 per day is normal for that blogpost. When something like this happens, it is usually caused by someone posting a link to my blog, but that was not the case now. Then I looked at the searched keywords and I saw these lines (I filtered out lots of other rows):
save exceptions 8
oracle save exceptions 6
forall log errors save exceptions 2
forall save exceptions log errors 2
forall save exceptions oracle 2
log errors save exceptions 2
oracle forall save exceptions 2
save exception 2
"log errors" "save exceptions" 1
"log errors" "save exceptions" "dbms_errlog.create_error_log" 1
"log errors" "save exceptions" forall 1
"save exceptions" "log errors" 1
_http://rwijk.blogspot.com/2007/11/save-exceptions.html 1
both log errors and save exception 1
create_error_log "save exceptions" 1
forall 'log error' 'save exceptions' 1
forall log errors save exception both togheter 1
forall save exception 1
forall save exceptions 1
forall save exceptions and log errors together 1
forall save exceptions log errors sql%bulk_exceptions 1
forall statement "log errors" "save exceptions" "bulk_exceptions" 1
log eroors save exceptions forall 1
log error oracle save exceptions 1
log error save exceptions 1
log errors save exceptions forall oracle 1
log errors,save exception with for all oracle 1
oracle bulk "save exceptions" 1
oracle forall log errors save exceptions 1
oracle forall save exception 1
oracle forall save exceptions dbms_errlog bulk_exceptions 1
oracle pl sql log errors save exceptions 1
oracle save exceptions forall 1
oracle save exceptions log errors 1
oracle save exceptions log errors for all 1
oracle save exceptions vs log errors 1
pl/sql log errors save exception forall 1
pl/sql save exceptions 1
plsql using log errors and save exception 1
save exceprions 1
save exceptions example 1
save exceptions in oracle sql 1
save exceptions in pl sql 1
save exceptions log error oracle 1
save exeptions 1
use log errors and save exception in forall orcle 1
using save exceptions and log errors in the same forall statement 1
using save exceptions and log errors inside forall pl sql 1
when save exceptions are used in oracle 1
And then I remembered last week's PL/SQL Challenge. On July 8, it had a nice question about what happens when you combine the FORALL SAVE EXCEPTIONS with a LOG ERRORS clause...
And while I'm talking about the PL/SQL Challenge: if you haven't played it yet and you want to learn the language better, then you should give it a try. If you played the game and you haven't learned anything, then at least you'll have a high score :-). The questions are very diverse regarding the topics and regarding difficulty. So there will always be topics with which you are highly familiar and those with which you are not. For example, I use PL/SQL almost 15 years now, but somehow I almost never used the UTL_FILE package. It just seems counterintuitive to me to work with files when you have a database at your disposal. But by playing the quiz I learned the package inside out.
And for those of you who wished me luck and wanted to know how the playoff went: well, it went smooth. So compliments to the developers of the site. The 10 questions itself contained a lot of text and code to read and grasp in just 15 minutes. And I thought the questions were on average tougher than normal. I managed to answer all questions in time, but I had to rush. I'm not so sure about the correctness of all my answers, though. We'll see.
Posted by
Rob van Wijk
at
7:21 PM
1 comments
Sunday, June 20, 2010
One Oracle forum to rule them all
Previous year I already expressed some of my thoughts on the OTN Forums and Stack Overflow here. In short my story was: I absolutely love the Q&A engine of Stack Overflow, but the best answers can be found on OTN. However, with more knowledgeable people appearing on Stack Overflow, the gap is closing. In the comments section and from conversations I came to know that there is still some resistance with Stack Overflow to some people on two other points:
- Stack Overflow is primarily aimed at the developers community, so a DBA won't feel much at home as there are only a few real DBA questions. For Oracle this is a real pity, because there is a huge amount of knowledge about Oracle databases among the DBA's. If we could only welcome them as well...
- To categorize questions, Stack Overflow uses tags. People who answer questions use tags to see all potentially interesting questions. However, you'll see lots of not relevant questions as well this way. You can't ever filter out all questions that you don't want to see, but the signal-to-noise ratio should at least be above some personal threshold. And this can be hard to achieve on Stack Overflow depending on your interests.
I ended my blog post last year with the phrase "I wish that the best of both worlds can be combined somewhere in the future.". And this wish can come true, with Stack Exchange. It's now possible to use the brilliant Q&A engine of Stack Overflow and create a forum site for only Oracle related questions. This should be very interesting for DBA's as well, and because of the Oracle-focus, it becomes much easier to express your interests. The community can tag and re-tag all questions and direct them to the world's best experts and wannabe's in Oracle land on a certain field. I have a dream, of no more hardly readable questions with lousy titles, no more inaccessible forum sites, no more trolling and no more questions without reasonable answers. I dream of one Oracle forum that unites all other Oracle forums, so we can learn from ALL experts, not just the few that happen to be present in a particular forum.
So, I support Gary Myers' idea and hope that you do as well. And if you do, please click the FOLLOW button here. The technology is ready, the proposal is there, now we only need to express our wish as a community and commit ourselves to it.
Spread the word.
Posted by
Rob van Wijk
at
11:40 AM
0
comments
Tuesday, June 15, 2010
Oracle 11g's Result Cache at OOW10?
Oracle Mix has a Suggest-a-Session facility for Oracle OpenWorld 2010. Some 120+ sessions are already suggested and you can vote on the sessions you'd like to see on Oracle OpenWorld. The sessions with the most votes will be selected in the program.
My proposal is a session about Oracle 11g's Result Cache. Already 24 people have voted for my session (thank you!), but it looks like I need a couple more. So, if you'd like to see a technical session about the result cache at Oracle OpenWorld or if you just want to support me :-), please vote. Note that you have to vote for at least 3 sessions for your votes to count. The voting closes this Saturday, June 20.
UPDATE 10th July:
I've just received a speaker invitation to present my session at Oracle OpenWorld. Thank you all for the extra votes that made this possible!
Posted by
Rob van Wijk
at
11:30 PM
0
comments
Simulating LAG/LEAD IGNORE NULLS
From version 11 onwards, the analytic functions LAG and LEAD have the extra option to RESPECT or IGNORE NULLS. This comes in handy when -for example- you don't want a regular next value like this:
rwijk@ORA11GR2> select empno
2 , comm
3 , lead(comm) over (order by empno) next_comm
4 from emp
5 order by empno
6 /
EMPNO COMM NEXT_COMM
---------- ---------- ----------
7369 300
7499 300 500
7521 500
7566 1400
7654 1400
7698
7782
7788
7839 0
7844 0
7876
7900
7902
7934
14 rows selected.
but a next value which ignore nulls, like this:
rwijk@ORA11GR2> select empno
2 , comm
3 , lead(comm ignore nulls) over (order by empno) next_comm
4 from emp
5 order by empno
6 /
EMPNO COMM NEXT_COMM
---------- ---------- ----------
7369 300
7499 300 500
7521 500 1400
7566 1400
7654 1400 0
7698 0
7782 0
7788 0
7839 0
7844 0
7876
7900
7902
7934
14 rows selected.
As you can see the LEAD function is now looking for the next non null value, instead of just the next value. For example, the next_comm value for empno 7521 is not NULL (of empno 7566), but 1400 (of empno 7654).
Last week I attended the ODTUG preview sessions at AMIS, and one of the sessions was Alex Nuijten's session about analytic functions. When he mentioned the new IGNORE NULLS option, he also showed a query how you can simulate the same functionality in versions prior to 11g. That query was a rather lengthy one, so I decided I'd try to come up with a shorter one. And this is the result:
rwijk@ORA11GR2> select empno
2 , comm
3 , max(comm) over (order by grp range between 1 following and 1 following) next_comm
4 from ( select empno
5 , comm
6 , sum(nvl2(comm,1,0)) over (order by empno) grp
7 from emp
8 )
9 order by empno
10 /
EMPNO COMM NEXT_COMM
---------- ---------- ----------
7369 300
7499 300 500
7521 500 1400
7566 1400
7654 1400 0
7698 0
7782 0
7788 0
7839 0
7844 0
7876
7900
7902
7934
14 rows selected.
And posting it here on my blog will surely mean someone will show up and post an even shorter one :-).
For completeness, doing the same for a LAG ... IGNORE NULLS query:
rwijk@ORA11GR2> select empno
2 , comm
3 , lag(comm ignore nulls) over (order by empno) prev_comm
4 from emp
5 order by empno
6 /
EMPNO COMM PREV_COMM
---------- ---------- ----------
7369
7499 300
7521 500 300
7566 500
7654 1400 500
7698 1400
7782 1400
7788 1400
7839 1400
7844 0 1400
7876 0
7900 0
7902 0
7934 0
14 rows selected.
This one can be simulated in versions prior to 11g with the following query:
rwijk@ORA11GR2> select empno
2 , comm
3 , max(comm) over (order by grp range between 1 following and 1 following) prev_comm
4 from ( select empno
5 , comm
6 , sum(nvl2(comm,1,0)) over (order by empno desc) grp
7 from emp
8 )
9 order by empno
10 /
EMPNO COMM PREV_COMM
---------- ---------- ----------
7369
7499 300
7521 500 300
7566 500
7654 1400 500
7698 1400
7782 1400
7788 1400
7839 1400
7844 0 1400
7876 0
7900 0
7902 0
7934 0
14 rows selected.
Posted by
Rob van Wijk
at
11:09 PM
6
comments