Oracloid Blog

It’s that time of the year again — Oracle OpenWorld time — and it’s my pleasure to announce our regular Oracle bloggers meetup again this year. We all know that Oracle community has grown this year so we expect to see folks from all the different technologies including MySQL, Java, Sun hardware folks in addition to the core Oracle database and apps crowd.

So… all of you Oracle bloggers attending Oracle Open World 2010…
… you are invited to attend this Oracle Bloggers Meetup during OOW 2010 — a chance to meet your online buddies face-to-face in relaxed and informal atmosphere.

When: Wed, 22-Sep-2010, 5:30pm

Where: Lower Dining Room, Jillian’s Billiards @ Metreon, 101 Fourth Street, San Francisco, CA 94103.


View Larger Map

Street view:

View Larger Map

See the “Lower Dining Room” on the floor plan below and ask where is the “Bloggers Meetup” booked under my name — Alex Gorbachev. These are the keywords to find us easily.

The plan is to gather at 5:30pm on Wednesday after three (or four for those of us starting on Sunday) days of intense learning. This year, you won’t need to find where to kill few hours in between of the OOW sessions and customer appreciation event at the Treasure Island — the best place to be this year is our bloggers meetup — the place where all the “cool kids” are.

As usual, thanks to Oracle Technology Network and Pythian for sponsoring the venue and drinks. HP is planning to establish a prize again this year for something fun… yes, we will again do something fun.

Last year, we were collecting signatures on our Bloggers Meetup T-Shirts so feel free to wear them this year to show your seniority at the event. ;-) This year’s activity is a surprise but if you have something cool in mind — let me know privately {last_name} at pythian.com.

For those of you who don’t know the history… The Bloggers Meetups during the Oracle Open World were started by Mark Rittman and continued by Eddie Awad and then I picked up the flag. They have been great success so let’s keep them this way! To give you an idea, here are the photos from the OOW08 Bloggers Meetup (courtesy of Eddie Awad) and last year’s meetup blog post update from myself.

If you are planning to attend, please comment here with the phrase “COUNT ME IN”. This will help us make sure we have the attendance numbers right. I will maintain the list here. Make sure you provide your blog URL with your comment — it’s a Bloggers Meetup in the end! Make sure you comment here if you are attending so that we have enough room, food and (most important) drinks.

Of course, do not under any circumstances forget to blog and tweet about this year’s bloggers meetup.

Looking forward to seeing all of you again this year!

Who is coming:
1. Alex Gorbachev
2. Vanessa Simmons
3. Marc Fielding
4. Arup Nanda
5. Kevin Closson
6. John Piwowar
7. Asif Momen
8. Arjen Visser
9. Kamran Agayev
10. Rob van Wijk
11. Karl Arao
12. Markus Eisele
13. Riyaj Shamsudeen
14. Mohan Dutt aka OCP Advisor
15. Jacco Landlust
16. Marco Gralike
17. Chet Justice
18. Eric Jenkinson
19. Fuad Arshad
20. Meg Bear
21. Jake Kuramoto
22. Rich (AppsLab)
23. Anthony (AppsLab)
24. Michael Aldrich
25. Sheeri K. Cabral
26. Iggy Fernandez
27. Martin Nash
28. Richard Foote
29. Espen Barroso-Gomez

Michigan OakTable Symposium (MOTS) is a unique event taking place just before Oracle OpenWorld — 16-17 September. Why unique? This is the first conference (is it not?) where all presenters are members of OakTable Network, a group that gathers number of like-minded IT professionals with scientific approach to Oracle database technology and to the life in general.

It happens in Ann Arbor, Michigan — a place I wanted to visit for a very long time. For those of you who are cost conscious and don’t have much in their education budget, this conference is a great value priced at a third of the Oracle OpenWorld pass and I can assure you that quality of presentations is on par with or higher then the top Oracle OpenWorld sessions. The conference is organized by volunteers from OakTable Network and few good friends as a non-commercial event so you will hear no marketing crap whatsoever.

Unlike, huge OpenWorld crowd, which has always intimidated me a little bit, MOTS is going to be a cozy event with strictly limited cap of only 300 participants – ideal size to support peer networking and opportunity to mingle with some of the folks you’ve been dying to talk to.

I especially enjoyed this video promotion:

My immediate reaction on the OakTable list was:

Am I hallucinating or I just saw the image of me right next to Cary, Mogens and Jonathan? As much as I’d be proud, I’d never qualify myself as deserving such comparison.

And Mogens clarified it:

The order in which we appear is based on TAOACDOEL (Total Amount Of Alcohol Consumed During Ones Entire Lifetime – pronounced “tao-AC-dole”). You’re still young, so you only made 4th position.

As to Cary’s and Jonathan’s TAOACDOEL, it is based on the premise that they have lived several prior lives in mining areas as gold diggers and drunkards. Otherwise they’d never make the list.

This is my first life, by the way.

I’m doing three presentation there:

• Battle Against Any Guess
• Oracle ASM 11g – the Evolution
• Under the Hood of Oracle Clusterware 2.0 – 11gR2 Grid Infrastructure

My colleague Chen Shapira does two sessions as well:

• Everything a DBA needs to know about TCP/IP Networks
• NoSQL Deep Dive

There is one more Pythian colleague that will be speaking at the conference but I’ll leave it for a surprise later. The full agenda is available but I believe it will be updated shortly.

Now that you checked the list of speakers and abstracts, I’m sure you’d be dying to get there for the two exciting days.

I hear lots of feedback on Exadata front asking for more and more technical information and I often refer them to some material online. I think I should reference couple credible resources for the readers of our blog in addition to our own Exadata content and Oracle’s own Exadata Technology section.

Kevin Closson — Oracle Exadata Performance Architect, fellow OakTable Network member and someone who knows loads about Oracle specifics on various hardware platforms and particularly storage — has a nice collection of Exadata blog posts. Some people say that Kevin is biased as he is working for Oracle on Exadata but I’d say that he’s rather passionate about this stuff and truly believes in what he writes.

Greg Rahn — lots of Exadata topics on Structured Data blog. Greg is a database performance engineer in the Real-World Performance group at Oracle and also a fellow member of OakTable Network. Even though Greg does work for Oracle, what you will read on his blog is the thoughts of engineer and not marketing.

Kerry Osborne’s Blog — Kerry covers some of the interesting Exadata stuff that our friends at Enkitec are working with.

Christian Antognini’s Blog – Christian provides interesting bits focused on how Oracle Optimizer deals with Exadata storage cells SQL offload as well as other performance-related areas.

Oracle Exadata Special Interest Group — Exadata SIG has been just recently created and this is the place to bounce your technical (and not only) questions. Some LinkedIn discussions are interesting.

I’ll keep this page updated as I come across of more web-sites with Exadata focused material that I think it worth to be added here.

Updated: 29-Jun-2010, 30-Jun-2010.

For me, ODTUG Kaleidoscope 2010 started on Friday with the ACE Directors briefing. Best practices topic was touched there slightly and I twitted about it. I decided that the feedback deserves a blog post so I’m simply quoting the conversation here. If you have anything to add, you know where to find the comment box.

alexgorbachev:
best practices should be forbidden or rather renamed to blue-prints #ACED

Fri Jun 25 16:26:26 +0000 2010

GregRahn: @alexgorbachev IMHO “Best Practices” are often sought after as an alternative to thinking and understanding. Two key components there!

Fri Jun 25 16:33:07 +0000 2010

CaryMillsap: Amen. RT @alexgorbachev: best practices should be forbidden or rather renamed to blue-prints #ACED

Fri Jun 25 16:33:43 +0000 2010

simon_haslam: Not sure but the term is overused RT @alexgorbachev best practices should be forbidden or rather renamed to blue-prints #ACED

Fri Jun 25 18:05:42 +0000 2010

debralilley: @alexgorbachev #bestpractice is WIP – easier to collect and publish initially but much harder to keep up but we should strive for it #ACED

Fri Jun 25 18:30:18 +0000 2010

CaryMillsap: @debralilley @myfear Designing “practices” is like designing library functions: generalizing means parameterizing. #aced

Fri Jun 25 19:16:49 +0000 2010

dannorris: @alexgorbachev re: best practices; what's in a name? Call them “stuff to do that avoids bad things” Note: I am not in marketing :)

Fri Jun 25 19:34:40 +0000 2010

CaryMillsap: @dannorris It's much easier to document the generally bad ideas than to document the generally good ones. #TenCommandments #aced

Fri Jun 25 19:37:52 +0000 2010

CaryMillsap: @dannorris s/easier/more practical/g

Fri Jun 25 20:16:02 +0000 2010

sheeri: “Best practices” should be thought of as “up for renewal” often. Often, “best practice + time = myth”. #ACED

Fri Jun 25 20:43:08 +0000 2010

oraclebase: To paraphrase Tom Kyte, the list of caveats is always longer than the best practice itself. #ACED

Fri Jun 25 20:49:15 +0000 2010

hyounpark_AG: Very true. Best != Permanent or immutable RT: @sheeri: “Best practices” should be thought of as “up for renewal” often. #ACED

Fri Jun 25 20:50:55 +0000 2010

CaryMillsap: “Best practice” the way to say “good idea” (or “MY idea”) when you want to stop people from thinking critically.

Sat Jun 26 14:51:14 +0000 2010

coins term “Minimally Sufficient Practice” (MSP) to combat #bestpractice epidemic. Yes.

CaryMillsap: On Oracle-L, @pudge1954 coins term “Minimally Sufficient Practice” (MSP) to combat #bestpractice epidemic. Yes.

Sat Jun 26 14:51:14 +0000 2010

It is time… Time for one more very special conference for me. Why special?

1. I have never been to ODTUG Kaleidoscope before. I always like new conferences — new experience.
2. Unlike Sheeri, I do not speak! This is one of those rare conferences where I come to slack off, meet old friends and make new ones, go to lots of sessions and actually learn stuff.
3. Coming from the DBA background, it’s not often that I come to development oriented conferences and I think I should do more of that.
4. It’s in Washington, DC. I lived there for some time and have number of good friends there. I’m really excited to see them again!

So what am I going to do there? I just arrived and right in time for the Oracle ACE Directors’ briefing that will run for the whole days of Friday. This is a super secret meeting where Oracle’s super secret plans are shared. Nobody can talk about that after this meeting or their tongues are cut off on the spot. For those of you who didn’t realize I’m joking, the ACE Director’s briefing is where Oracle shares the roadmap of its products — some of it is long term strategy and some is about the upcoming releases. There are few things that we are asked not to share in public but, frankly, there is nothing really sensitive. One of the most interesting parts of the briefing are the Q&A moments when all kind of questions get asked (sometimes tough ones) and, to the most parts, gets answered.

It took me the same time to get to the hotel from Dulles International Airport as flying from Ottawa. Oh well, the beauty of DC traffic. Now I’m sitting at the back of the room enjoying the demo of the new APEX 4.0 and quietly having a bite. I see quite a few familiar faces (or rather backs) that I’m about to say hello to (pending the next break).

So what’s ahead? Yesterday, we all received a surprise invitation (well, surprise to me at least) to ACE Directors welcome reception organized by the ODTUG board members. Looking forward to that.

Saturday is the ODTUG Community Service Day where the volunteers help to beautify Ronald H. Brown Middle School in Washington, D.C.

Sunday is ODTUG Kaleidoscope 2010 Symposia and I’m still not sure whether I should select APEX or Performance, Scalability, and Security track. Sunday night is the night of Oracle ACE dinner and I think I managed to miss few ACE program dinners at the past few conferences so I’m really looking forward to this Sunday.

Monday evening, come for the Sundown Sessions which are open discussions moderated by Oracle ACE Directors. I’ll be hanging either in MySQL or Database Development halls. Immediately following that, on my agenda is Oracle ACE Directors Reception — this is your chance to mingle with the bunch of Oracle ACE Directors and other cool folks and abuse some complimentary food and drinks (thanks to OTN).

The error message I received this morning trying to login at oracle.com was:

Your account has been locked for the next 3 hours due to too many invalid login attempts. Please try again later.

This was odd — I think I used the right password and I only did it once. Oh well, SSO on oracle.com has been behaving strangely these days (and I’m not even talking about availability of oracle.com itself).

Fortunately, there was a simple solution for this thanks to Simon Haslam who provided solution on Twitter. All you need to do is to use “Forgot password” functionality — this will do both, reset your password and unblock the account. The latter is a surprising side-effect for me and that’s why I didn’t even think about resetting my password in the first place.

Now, why would I blog about such a seemingly irrelevant issue? Since My Oracle Support (formerly Metalink) migrated to Single Sign-On integrated with oracle.com, you can’t access your Oracle support without a functioning oracle.com account. I don’t need to tell you what it means for a production DBA to loose access to Oracle support in critical times.

Update: You won’t be asked to reset your temporary password so you should change it yourself right away — remember that your new password is potentially compromised as it’s been sent using email which is not a secure media. To reset your account password go to your Account and click on “Change Password” in the top-right corner.

I found I never published this post as it was sitting in my drafts few months now — it was written in 13th February, 2010. I’m publishing it without any changes.

I learn therefore I am!

I’ve just wrote few bits about learning a new technology and after skimming through my Google Reader, I noticed a great post by Chen Shapira — Deliberate Practice. That’s reminded me about another aspect of learning that I didn’t mention — learning is a continuous process.

There are two aspects…

• No matter how good I am and how much I know, my knowledge and expertize become outdated relatively quickly these days unless I keep up with the new stuff. Unfortunately, there is so much new technologies these days that I have to be very selective on what I want to follow which is a big challenge in itself. On the other hand, I’d rather be challenged than bored. As DBA’s we are luckier than Developers — their world changes much faster than ours.
• I forget things I don’t use/do/read about/think about regularly. Refreshing my “old” memory is a must do. This is why I think Chen’s idea of deliberate practice is so great.

Some people are better on keeping the existing tools sharp, while others succeed on the lookout for new ways and techniques. To truly excel, we need to master both.

This photo (it was taken 5 years ago in Australia in one of Sydney’s “wild” parks) reminds me of this continuous learning process — perhaps, it will do the job for you as well.

For one of our customers, I’ve recently reviewed the strategy of migration from single-byte encoding to variable length multi-byte UTF8 (AL32UTF8 encoding in Oracle naming standards). These type of projects are coming up again and again so I think it must be common for many of you. Thus, this bit might be useful. I’m also interested in your experience – perhaps you can run this simple simulation on your platforms and provide the results in the comments?

Back to the project… One area was estimation of the string manipulation overhead. Based on Pythian experience as well as the feedback from my network, I could conclude that nobody has observed any measurable performance degradation or significant CPU usage growth clearly attributed to UTF8 migration.

Thus, I decided to simulate at least some operations and measure them. One of the concerns was sub-string extraction — fixed length encoding sub-string is super easy to implement as the offset and length in bytes are known. Variable length character set would require scanning the whole string from the very beginning because byte offset and byte length are not known until the string is traversed from the beginning character by character.

This is the PL/SQL block I came up with:

declare
  s1 varchar2(4000 char);
  s2 varchar2(4000 char);
  i INTEGER;
begin
  s1 := rpad('a',3999,'a');
  for i in 1..10000000 loop
    null;
    --s2 := 'a' || 'b' || 'c' || 'd';
    --s2 := substr(s1,3000,1) || substr(s1,3001,1) || substr(s1,3002,1) || substr(s1,3003,1);
  end loop;
  dbms_output.put_line(s2);
end;

There are 3 cases and only one of them needs to be un-commented when running the block. null; is there to just capture the baseline and measure time required to run an empty loop. Concatenation s2 := 'a' || 'b' || 'c' || 'd'; is one scenario and, finally, substring extraction s2 := substr(s1,3000,1) || ... is the second one.

I’ve run it in the VMs on my laptop as well as customer’s own physical servers to confirm the timing. Below is the result from my virtual machines but the percentage was pretty much the same. Platform is Linux. Oracle version 10.2 and 11.1. Note that 11.2 is smarter and optimizes the block with NULL loop so you would need to trick PL/SQL optimizer better.

The time in the table is seconds that PL/SQL block was executing. I ran if few times and averaged the result but it was very consistent anyway. Also, skip the very first measurement to avoid the impact of block parsing.

To interpret the results, the string manipulation/concatenation (excluding 0.5s of looping itself) accounts 1.3s vs 1.7s – 30% UTF8 overhead. SUBSTR function overhead (excluding loop and concat itself) accounts for 6.2s vs 7.7s which is only 25% overhead.

I found substring overhead rather small – I expected order of magnitude difference to be honest. However, 30% of concatenation overhead seemed to be little too much and I don’t see why it should be that high.

Now, while overhead is rather noticeable, I didn’t see databases that were doing mostly string manipulations in SQL and PL/SQL. I think that’s why in the big picture, we generally don’t see much performance impact moving to variable-length character set.

If you have access to the databases with fixed-length and variable-length character sets on the similar hardware, feel free to post your results below.

I was recently installing one APEX application and needed to upload a bunch of images. APEX was configured to use EGP (Embedded PL/SQL Gateway) so traditional options were to configure FTP or WebDAV but I’d rather not open these services on production environment.

After searching for the solution on the Internet, I surprisingly realized that there is none. At least, nothing I could find easily. Our resident APEX expert, Alex Fatkulin, pointed me to the installation process and suggested that there is a simple way to do that using a single PL/SQL call.

It turned out that it was more than a single PL/SQL call involved but nothing too difficult.

What you need is to create the hierarchy of files and directories that you want to upload (images or not – doesn’t matter). Then you create an XML file imagelist.xml listing required directories and files to upload.

Here is the example:

imagelist.xml
image1.png
logo/pythian.png

The content of imagelist.xml:

<upload>
    <directories>
        <directory>logo</directory>
    </directories>
    <files>
        <file>/image1.png</file>
        <file>/logo/pythian.png</file>
    </files>
</upload>

This folder should be on the database server and should be readable by the database server processes. The script accept the path to that directory as a parameter and creates a temporary directory object in the database. It then reads the XML file, creates directories inside XML DB repository and upload image objects. Finally, temporary directory database object is dropped.

The script is below. If a directory in XML DB repository already exists, then the script skips its creation (there is an exception handler). If uploaded file already exists then exception is thrown. Feel free to implement other behavior if you want to.

timing start "Load Images"

begin
    execute immediate 'drop directory APEX_IMAGES_UPLOAD';
exception when others then
    null;
end;
/

create directory APEX_IMAGES_UPLOAD as '&1';

set serveroutput on

declare
    file_list               varchar2(30) default 'imagelist.xml';
    upload_directory_name   varchar2(30) default 'APEX_IMAGES_UPLOAD';
    repository_folder_path  varchar2(30);
    pathseperator varchar2(1) := '/';

    directory_path      varchar2(256);

    target_folder_path  varchar2(256);
    target_file_path    varchar2(256);
    target_file_name    varchar2(256);

    resource_path       varchar2(256);

    filelist_xml        xmltype := xmltype(bfilename(upload_directory_name,file_list),nls_charset_id('AL32UTF8'));
    content_bfile       bfile;

    result              boolean;

    filelist_dom    dbms_xmldom.domdocument;
    files_nl        dbms_xmldom.domnodelist;
    directory_nl    dbms_xmldom.domnodelist;
    filename_nl     dbms_xmldom.domnodelist;
    files_node      dbms_xmldom.domnode;
    directory_node  dbms_xmldom.domnode;
    file_node       dbms_xmldom.domnode;
    text_node       dbms_xmldom.domnode;
    l_mv_folder     varchar2(30);

    DIR_EXISTS EXCEPTION;
    PRAGMA EXCEPTION_INIT(DIR_EXISTS, -31003);

begin

  if wwv_flow_utilities.db_version_is_at_least('11') then
    repository_folder_path := '/images/';
  else
    repository_folder_path := '/i/';
  end if;

  -- create the set of folders in the xdb repository

  filelist_dom := dbms_xmldom.newdomdocument(filelist_xml);

  directory_nl := dbms_xmldom.getelementsbytagname(filelist_dom,'directory');

  for i in 0 .. (dbms_xmldom.getlength(directory_nl) - 1) loop
    directory_node := dbms_xmldom.item(directory_nl,i);
    text_node      := dbms_xmldom.getfirstchild(directory_node);
    directory_path := dbms_xmldom.getnodevalue(text_node);
    directory_path := repository_folder_path || directory_path;
    begin
      result          := dbms_xdb.createfolder(directory_path);
    exception
      when DIR_EXISTS then
        null;
      when OTHERS then
        raise;
    end;
  end loop;

    -- load the resources into the xml db repository
  files_nl           := dbms_xmldom.getelementsbytagname(filelist_dom,'files');
  files_node         := dbms_xmldom.item(files_nl,0);

  filename_nl := dbms_xmldom.getelementsbytagname(filelist_dom,'file');

  for i in 0 .. (dbms_xmldom.getlength(filename_nl) - 1) loop
    file_node          := dbms_xmldom.item(filename_nl,i);

    text_node          := dbms_xmldom.getfirstchild(file_node);

    target_file_path   := dbms_xmldom.getnodevalue(text_node);
    target_file_name   := substr(target_file_path,instr(target_file_path,pathseperator,-1)+1);
    target_folder_path := substr(target_file_path,1,instr(target_file_path,pathseperator,-1));
    target_folder_path := substr(target_folder_path,instr(target_folder_path,pathseperator));
    target_folder_path := substr(target_folder_path,1,length(target_folder_path)-1);
    resource_path := repository_folder_path || target_folder_path || '/' || target_file_name;

    begin
    content_bfile := bfilename(upload_directory_name,target_file_path);
    result := dbms_xdb.createresource(resource_path,content_bfile,nls_charset_id('AL32UTF8'));
    exception when others then
        dbms_output.put_line('file not found: '||target_file_path);
    end;

  end loop;

end;
/

commit;

drop directory APEX_IMAGES_UPLOAD;

timing stop

There are some special conditions for 11g database but I warn you – I only tested it on 10g so far. Let me know how it works for you.

This is just a short blog post to spread the word about the upcoming webinar organized by Embarcadero with fellow member of OakTable Network — Jonathan Lewis and Kyle Hailey — Ultimate SQL Tune-off.

Kyle and Jonathan are on my list of the most respected Oracle performance experts and I could only imagine what they can deliver joining the forces. I’ll allow myself to quote the opening of the webinar as it sounds really interesting:

According to Jonathan Lewis, there is a basic strategy to writing efficient SQL, but it requires knowledge of the data, the metadata, and the intent of the query.

Jonathan will start with the basic principles of efficient data access and then show different ways of analyzing the data. He’ll then describe a graphical approach to finding the most efficient access path for a query and then apply the method to a problem he recently saw with one of his clients.

But wait… is there more than one way to skin a cat? Kyle Hailey thinks so, and he’s not afraid to let Jonathan know it!

Kyle’s fancy footwork will include his own approach to Visual SQL Tuning (VST) with some ring side support from DB Optimizer XE. Hitting below the belt? You be the judge.

The webinar is on June 10, 2010 at 11AM Pacific / 2PM Eastern.