Tom Kyte in Moscow

February, 24th. Renaissance Moscow Hotel. Over 300 of people in hall and Tom Kyte on stage – that is “AskTom in Moscow” for the first time.

I heard that Tom is a great speaker. I can confirm – he is. I’d say live Tom’s presentations are fascinating: nice voice, appropriate talking speed, simple examples and very accurate language (just like in a book!). I was amazed by his language – I didn’t know that a man can speak all that things such smoothly, using only meaningful words, and make the points so clear.
Tom did five presentations: “How do you know…”, “The Best Way”, “Worst Practices”, “Top 10, No 11 new features of Oracle Database 11g Release 2” and “Why Why is probably the right answer”. All are already available for download. I don’t want to retell them, but I do want to write down couple of quotes (in my interpretation) alongside with my notes.

• Things Change (slides 22-26 of ThingsYouKnow_OOW.ppt). There were several examples how things changed between releases thus making old suggestions no longer valid.
  • What is better – “IN or EXISTS”. Tom said “In modern releases, you should probably use whatever you like since CBO costs all possibilities and uses the best one”. It’s not always true since CBO can’t cost all variants of query transformation and can limit the search scope.
  • Array Fetching. In Oracle 10g PL/SQL engine has been improved to automatically use bulk fetch of size 100 in FOR cursor loops. When I heard that a recent thread on the SQL.ru came to my mind with a case showing that such optimization could be silently turned off due to DEBUG compilation mode – and this seems to be not documented behavior.
• Bind Variables (OOW_WorstPractices.ppt, slides 6-13) Of course you should better use them, to get advantages of shared SQL. However very-very rarely you might want to achieve something quite opposite – how not to share SQL

There were several interesting questions in the panel session. Here are two of them which I liked:

• The following code throws exceptions with different codes. Why not ORA-00060 always?

  drop table t cascade constraints purge;
  create table t (id) as select 1 from dual;
  
  update t set id = 1;
  
  declare
      pragma autonomous_transaction;
      l_tmp int;
  begin
      select id into l_tmp from t where id = 1 for update;
  end;
  /
  
  declare
      pragma autonomous_transaction;
      l_tmp int;
  begin
      select id into l_tmp from t where id = 1 for update wait 5;
  end;
  /
  
  declare
      pragma autonomous_transaction;
      l_tmp int;
  begin
      select id into l_tmp from t where id = 1 for update wait 6;
  end;
  /
  

  Relevant output:

  ERROR at line 1:
  ORA-00060: deadlock detected while waiting for resource
  ORA-06512: at line 5
  
  ERROR at line 1:
  ORA-30006: resource busy; acquire with WAIT timeout expired
  ORA-06512: at line 5
  
  ERROR at line 1:
  ORA-00060: deadlock detected while waiting for resource
  ORA-06512: at line 5

  Tom said it’s probably works as designed (i.e. it’s not a bug, it’s a feature ;)). Two separate timers are working on similar tasks and exception depends on which one will fire first

• The question related to system statistics. Modern storage systems may contain both very fast disks (SSD and alike) and relatively slow (usual SCSI). But system stats applies to the whole database, and can’t represent such a difference in a storage system. How Oracle overcomes that situation? There were no answer to that question. From what I know, you can’t fine tune system stats and can only adjust averages it represents

All in all I enjoyed that delightful day with Tom Kyte. Thanks to all who organized this event and special thanks to Tom for coming to Moscow.

Update:
Thanks to a colleague of mine, here are recorded “Worst Practices”, “Top 10 Oracle 11gR2 Features” and “Panel session” (100 MB in total):
Worst practices
Top 10 Features Pt1
Top 10 Features Pt2
Panel session – if you don’t understand russian you probably don’t want to listen to this one (Tom speaks here too, but not all the time)

Update #2
Alternative download link for audio recordings.

PS. Photos by Dmitry Volkov: