File list from the server

I've been asked by one of my architects whether there is a quick way of see a list of file existing in the random folder on the server. The first response was - NO, because all file system communication should go via DIRECTORY object (starting 10g).

But I recalled that I've read sometimes about a slightly nontrivial usage of undocumented package DBMS_BACKUP_RECOVERY that could serve exactly the required purpose. And in a minute I found the article and the following code sample:

DECLARE
  pattern VARCHAR2(1024) := 'C:\temp\*'; 
  ns VARCHAR2(1024); 
BEGIN
  SYS.DBMS_BACKUP_RESTORE.searchFiles(pattern, ns);

  FOR each_file IN (SELECT FNAME_KRBMSFT AS name FROM X$KRBMSFT) LOOP
    DBMS_OUTPUT.PUT_LINE(each_file.name);
  END LOOP;
END;

Yes, you still need some fairly advanced privileges (like logging as SYS and having 'db_recovery_file_dest' parameter set), but still - I was surprised! Thanks a lot to Christopher Poole that I don't need to reinvent at least this bicycle :-)

P.s. More posts about DBMS_BACKUP_RESTORE are coming ;-) !

Amazon Top - PL/SQL for Dummies is #1

To my huge surprise the 5-year-old-book is currently #1 in the Amazon's top Oracle books... H-m-m-m, I have a feeling that with the current economy a lot of people went "back-to-school".

UTF8 vs AL32UTF8

To be fair, I am one of reasonably few people who like Oracle documentation - you indeed can find there everything needed. But... Sometimes it will be either in the unexpected place, or not explicit enough, or not very clear at all. Here is an illustration of such case. 

The beginning of this story is pretty simple - Friday, around 4 p.m. People are already day-dreaming about their weekend plans. And suddenly our main development database starts to behave really funky. Our senior web-architect eventually called me to show that in one of the newly created modules the whole session just crushes with ORA-600 after the call to XML Parser (we've been building XML document manually as a CLOB and than converting it into XMLDocument). Alternative via XMLType.CreateXML completely froze the whole database... 

After about an hour of pretty painful debugging the problem was found - and not where we've been expecting. The exact match was found in Oracle's Database Companion CD installation Guide:

-----------------------------------------------

AL32UTF8 is the Oracle Database character set that is appropriate for XMLType data. It is equivalent to the IANA registered standard UTF-8 encoding, which supports all valid XML characters.

Do not confuse the Oracle Database database character set UTF8 (no hyphen) with the database character set AL32UTF8 or with character encoding UTF-8. Database character set UTF8 has been superseded by AL32UTF8. Do not use UTF8 for XML data. UTF8 supports only Unicode version 3.1 and earlier; it does not support all valid XML characters. AL32UTF8 has no such limitation.

Using database character set UTF8 for XML data could potentially cause a fatal error or affect security negatively.

-----------------------------------------------

"Fatal errors" indeed! We immediately switched from:
 <?xml version="1.0" encoding="UTF-8"?>

to

 <?xml version="1.0" encoding="AL32UTF8"?>

And our problem was solved! Friday, 5 pm... Perfect timing!

Oracle OpenWorld 2011. Day 5

The closing day of the event started slowly. No wonder... Not that many people returned back to the hotel just after Sting concert (like I did) - the show went lo-o-o-o-ng way past midnight. Also, a lot of attendants had flights already on Thursday, so it was pretty common to see some of my old friends trying to reach a jet speed already in the hotel (running with their bags towards the cab stand :-) ).

My schedule was pretty simple:
- went to the talk about Edition-Based Redefinition from a couple of BankOfAmerica guys. We in Dulcian look at that feature for some years, so it was interesting to see what other people are doing in this area. Summary - it is a VERY powerful concept if you are starting a new project. But transforming existing one to be EBR-compliant is much more fun. Overall, I am planning to watch it closely, but still not sure whether I am ready to jump in the pool with unknown number of crocodiles.
- mine and Paul's last presentation "Achieving great performance for web-application by using only SQL and PL/SQL" got significantly more people than we ever hoped considering the last day of the conference. It seems like that the state of confusion, especially among small-to-medium size IT shops is huge. They do not have budgets/people to jump to the most recent bandwagons, but they still need to somehow support their end-users. After the presentation I've spent at least an hour more talking about the details of our FormSpider implementation - there was a lot of interest about this completely nontrivial way of building contemporary web applications.
- "It's the wrap" party at Yerba Buena - perfect sunny afternoon with "Blue Angels" flying above our heads. I didn't have any energy left even to talk to everybody. Picked up the book, and read for an hour or so.

That's all, folks... Now I need to digest everything I learned. It was a really great conference, so check this blog from time to time - I will be posting more focused stories! Stay in touch!

Oracle OpenWorld 2011. Day 4

Sad day for the IT industry. There is not a lot of people who could think of something - and make the whole world change because of their ideas. So hard to lose such people so early...
Day 4. Oct 5 2011.
Finally, there was at least one day when I could just relax and wonder around between different presentations, demos etc. I think, I've spent it reasonably efficient:
- went to a couple of talks from Oracle tech leads (XMLDB and Database Vault). The first topic requires additional post, but here I will just mention briefly that Oracle finally started to change default behaviour of XMLType that I've been complaining for years:
    * in 11.2.0.3 XMLType will be created as Binary SecureFile storage mechanism with CACHE option automatically ON. That parameter was a performance issue for too many people - and not a lot of people know how to internally change it (if interested - read our article about LOB data types in the 3rd quarter of ODTUG  Tech Journal)
- spent couple hours at Oracle DemoGrounds(this time with SecureFile team, Java-in-the-database team, Oracle-on-Windows team). Got some useful information - sorry, also not for publishing :-)
- came to Larry's talk where he announced Oracle Cloud and Oracle Social Network... No comments on either of those topics, not my area of expertise.

Last part of the day - sure, you've guessed, PARTY! This time everybody was very worried about the possibility of storms, but we lucked out (storms are today...) The reason was pretty simple - STING! The guy just turned 60 years old, but I envied his level of energy on stage. It was unforgettable to be 20 meters from musicians (I came early enough) in the crowd of 10+ thousand people singing "Englishman in New York" with all accents existing on the planet Earth!

Oracle OpenWorld 2011. Days 2 and 3

This year my schedule here in SanFran is surprisingly busy. And not just because of social events :-) It is my 5th OpenWorld in a row - and for now it holds #1 spot for the technical content. Overall, from Oracle's side there is much less marketing people presenting their products / much more technical leads explaining what is going on under the hood. So, I apologize for a small gap in my story-telling and will try to catch up!

Day 2. Monday, Oct 3
For the first time in all of my conference years I decided to come to hands-on training. The topic was one of my favorites, Cost-Based-Optimizer, but still, I had some doubts about how well it could be illustrated as "hands-on". Big surprise - it worked out pretty seamlessly. Each problem case had a step-by-step analysis option and "do-it-yourself" option. Considering my limited experience with Oracle 11g (we only recently switched our main environment to 11.2.0.2 - so my performance tuning skills in that version are sub-optimal), I picked up a guided approach. Good for me - there was a lot of 11g-only concepts in the process (even so I correctly got one out of four tasks without any hints). Summary - learned a lot, need to spend more time with 11g.

Next presentation I came was done by people whom I know already for years - Bryn Llewellyn and Martin Buchi talked about modularization of PL/SQL code in large projects. That was definitely interesting for me, because looks like Martin's design patterns are very close to my "thick database approach".

After that talk I came to Paul Dorsey's "Building Systems in the Developing World" to provide DBA's support (if needed) for his primarily architectural talk. It was very educating to see how people coming from different backgrounds were reacting on our stories about building government systems in Ethiopia. A lot of confusion to be fair, and not a lot of understanding on real local problems...

As usual, final part of the day was much more optimistic! ODTUG had setup a cocktail party in a very nice French bistro.The combination of old friends+good vine+very creative snacks was well appreciated after so many hours wondering around the conference

Day 3. Tuesday, Oct 4
Yesterday was as busy as Monday. For the first time (thanks to Apress) I've been invited to Oracle Publisher's Seminar. An environment where you get a chance to communicate with such people as, for example, Andy Mendelsohn, was a completely new experience for me... It was a bit scary at the beginning, took me some time to relax a bit! Anyways, a lot of useful info (sorry, internal-usage-only :-) ).

After the seminar (around 2pm) I finally decided to wonder around the vendor hall. And as usual I got stuck at DemoGrounds... I just cannot miss the chance to harass developers/managers from Oracle with the problem I've found. And considering that usually I dig pretty deep, sometimes I surprise even Oracle specialists. This year my poor victims were two great guys developing DataPump engine - the game finished with 1:1 draw: they showed me a fix to one of my problems that I just missed in the documentation (next week I will have a post about it) / I showed them an issue with function-based indexes to be logged as an enhancement request. Jokes aside, such discussions for me is the most critical part of the OpenWorld at all. Thanks for the great job, people...

In the evening...Of course, yet another party! :-) This time one of the publishers was organizing a small meeting between authors/potential authors and their acquisition editors. A lot of insightful discussions about technical books. Definitely a well-spent time!

That's it folks, time to get back to the conference center! Hopefully, the weather today will be at least a bit better than forecast...

Oracle OpenWorld 2011. Day 1

As usual, for all government projects September is a pretty busy month (end of fiscal year), but nice thing about September - it always ends (plus/minus a week) with Oracle OpenWorld.

Even a lot of people complain that this conference became more and more marketing-oriented, for me it is a place to meet a lot of my old friends by Oracle community, get the "temperature" of the industry, talk to new people (there is always a ton of new faces from all over the world!). I even try not to submit abstracts for the next cycle of conferences (Collab, KScope) until I return from the OpenWorld - some of topics could just come up as a part of late-night discussion somewhere in the bar after N-th glass of beer. The key point - such topics will be 100% hit! Because if you remember something at that point - it usually means that it is really high in your priority queue :-) Anyways, for the next couple days I will try to post about my experiences here (sorry, not in the minute-by-minute structure!).

Day 1. Sunday, Oct 2.
Sunday is always a conference-in-the-conference, because this is the day when different user groups have a chance to organize its own symposiums. This year I participated in ODTUG Web Architecture Forum, playing a role of the person from the "other side of the wall" - my story was told from the point of view of a good DBA/Senior Database Developer that actively participates in the development of Web applications.

Of course, it was all about "thick database" approach, my favorite concept of pushing as much business logic into the database - and of course, a lot of people in the audience were not 100% comfortable with such extremes. But at least it made them thinking - which is always a good thing for you as a speaker. After my talk I've had about an hour-long conversation with a couple of developers from my native Ukraine - and I was really glad that their way of system development is reasonably close to my understanding, which means that my ideas are valid "in general", not only for the North America. :-) I would hate to sound "provincial" :-)

Another big part of the Forum was a talk of my guru Paul Dorsey about ways of building new Web-based system. The talk was very entertaining - but the summary was very grim... There is no coherency in the industry whatsoever! Take 10 system - get 12 architectures (not even 10!, sometimes architectures start to mutate inside of the life-cycle!). H-m-m... I more and more like my "thick database" approach!

But the evening ended up on the good note - a lot of thanks to Lilian Buziak for setting up an Oracle Ace Dinner (for me it was the very first one, since I got my Ace status only this summer). That was a perfect spot to talk with the "old-timers" (including my esteemed co-authors of "Expert PL/SQL Practices"). And food was great too :-)

I am a real DBA now!

Once I've been told is that to be a real DBA you need to do at least one restore a production system from RMAN backup...I think, I came close to that :-) - yesterday night I was able to create a duplicate instance from our production Level-0 (around 200 GB of data restored from tape over the weekend). Although, it was a very long night!

For all my sins (and because our real DBA is on vacations) this task landed on my laps last week - and since then I started to truly appreciate the work of honest-to-god administrators, non semi-architects/semi-developers like me.

OK, I will stop ranting... A couple of useful remarks:
- Arup/Sam/Darl, I owe you at least one round of good drinks! Your book "RMAN Recipes for Oracle Database 11G" helped me a lot!
- As usual, around midnight I've hit an brick wall. RMAN was complaining about a space "somewhere", I've spent another hour trying to figure what it was about. And eventually I discovered the solution in the bug-list (#8631293):

Description
 * RMAN Duplicate command signals RMAN-06561 (available space must be larger than NNN kb) when the recovery area parameter is set.
Workaround
* The workaround is not to set db_recovery_file_dest and db_recovery_file_dest_size parameters in the init.ora

Expert PL/SQL Practices on sale!

My (to be fair - our) new book is waiting for everybody! Sales started today - here is a link from Amazon:
"Expert PL/SQL Practices"

It was a very interesting project to participate - and huge thanks to Jonathan Gennick from Apress who was brave enough to take the challenge of herding together 15 different authors. To our own surprise the book came together even faster than anybody anticipated - by at least a month. Looks like PL/SQL authors are more disciplined comparing to everybody else :-)

Good reading to everyone!

I am an Oracle ACE now!

Some good news: I've been confirmed as Oracle ACE! Here is my profile published on the Oracle's main web-site: click to check yourself...

That was a long way to go, my friends... It started in the IT department of the factory where my dad was working - for the first time I've heard about a miraculous platform that would contain all the information about the whole factory. Later "the way" continued in the university. I was shocked by the depth of what it really meant to built contemporary IT systems, but with the help of my professors I learned how to swim in that sea of data. By the way, I still own my first Oracle-related book - it was Russian Edition of Paul Dorsey's Oracle Designer Handbook. Who would ever guess that couple year later Paul would become my (a) boss (b) mentor (c) friend for life! It is now more than 10 years of me working in Dulcian.... Time is flying!

Anyways, thanks everybody for shaping me as a professional - my teachers, my colleges, my listener, my friends! I hope, in this new status I could contribute even more to the Oracle community - one day at a time, one line of code at a time. Will keep you posted!

CLOB-related DataPump issue

Yesterday while building a demo-instance for the upcoming KScope'11 I've hit a strange Oracle bug. Here is the story:
- a single schema is transferred from 10.2.0.5 to 11.2.0.1 (both Windows 32 bit) via datapump
- importing of the data fails with fatal error (in the middle of data import)
- I took export log, found exact table it fails to import (by looking at the last successful load), and tried to import just one table. It also failed with the same fatal error:

Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Job "SCOTT"."SYS_IMPORT_TABLE_01" stopped due to fatal error at 10:38:53

- in the alert.log I found a bit more details

ORA-07445: exception encountered: core dump [kcblnb_dscn()+38] [ACCESS_VIOLATION] [ADDR:0x8] [PC:0xDD94FE] [UNABLE_TO_READ] []

Metalink pretty quickly gave me an answer: Doc ID 1313537.1:
- Using datapump to import a table that has mixed CACHE/NOCHACHE LOB columns results in an ORA-7445.
- Eventually there will be patch (11.2.0.3), but for now the workaround is to use regular EXP/IMP

I tested the workaround option - it does its job. So, beware!

P.s. Since I am talking for years about manual adjusting CLOB storage parameters, I decided that it is my duty to create this post! As usual, advanced features are causing side-effects...

P.p.s. Forgot to mention - just FYI about old EXP/IMP module: you cannot export data from 10g database using 11g client utilities. The export must be done using native 10g utilities - otherwise a pretty strange error is thrown:


EXP-00008: ORACLE error 904 encountered
ORA-00904: "MAXSIZE": invalid identifier

LONG to CLOB

Yes, I know that LONG datatype was considered "dead" already for Oracle 8i. But still I have cases when people are asking about the conversion to CLOB. Finally, I decided to build a "memo" on this topic.

Small remark: in this blog post I explicitly ignore 11g SecureFile LOB implementation, since the chances of people still using LONG datatypes to step up to the most recent feature are not really high.
--------------------

From the database point of view it is very simple to change LONG to CLOB datatype, since ALTER TABLE..MODIFY supports such change explicitly. But since CLOB columns have many more parameters to be configured, the process of conversion provides a good opportunity to make appropriate setups.

The most critical
*Tablespace - CLOBs could use a different tablespace comparing to the table itself, which could be useful for the purposes of data storage management.
* Retention - configures the LOB column to store old versions of LOB data for a period of time, rather than using a percentage of the tablespace (PCTVERSION, which is used by default for columns converted from LONG). The RETENTION parameter is designed for be used with Undo features of the database, such as Flashback Versions Query, and recommended for all DB configurations using Automatic Undo Management.
* Caching – defines whether DML operations against LOB columns would use BUFFER CACHE or Direct IO operations. There are 3 possible settings of this parameter – CACHE / CACHE READS / NOCACHE (default). Using the CACHE option results in improved performance when reading and writing data from the LOB column. However, it can potentially age other non-LOB pages out of the buffer cache prematurely.

Overall, the migration will take a form of running the following statements to each LONG column (as you can see, column names is not changed – only its datatype and extra storage options)

alter table <TableName> modify <ColumnName> CLOB
lob(<ColumnName>) store as 
(cache 
 tablespace <TablespaceName>
 retention
 )

From the application point of view the following issues/remarks are detected:
* There are no known areas where LONG datatype is doing anything better  (performance/functionality) than CLOB
* There is no easy technique to modify CLOB column back to LONG without using some kind of staging solution.
* PL/SQL supports explicit casting of CLOB columns to LONG variables as long as the length of retrieved data meets LONG restrictions. The next example should read/change/write:

declare
    v_long long;
begin
    select clob_column
    into v_long
    from t_table;
    
    v_long:= v_long||'ABCDEFG';
    
    update t_table
    set clob_column = v_long
    where pk_id = 1;
end;

It is recommended to do a full code review and remove variables of type LONG, especially in performance-critical modules (because DBMS_LOB APIs are much more efficient than, for example, concatenation)

--------------------

Hope, it helps!

Oracle OpenWorld 2011 => Oracle Mix

As many of you have noticed, at the end of the last week Oracle sent around accept/reject letters for OpenWorld 2011. Mixed feelings...From one side, I already got in with one of "performance" talks that we often do together with Dr. Paul Dorsey (he covers "why"/I cover "how"). From the other side, I didn't get through the abstract I really hoped.

But this year there is an alternative,“Oracle Mix”, where people could re-submit their abstracts to the “popular vote”.  What surprises me is that majority of MIX topics are either about development or about core DBA’s issues (manageability/ scalability/ availability). Middleware/SOA/other products are well-behind. Two possible conclusions – either DBA/Dev specialists are more active in sharing of their ideas or the balance of presentations in OpenWorld is slightly off. Opinions?

Anyways, if anybody would like to vote for my additional abstract via Oracle Mix, here is a link. 

https://mix.oracle.com/events/oow11/proposals/11032

It is the same talk I will be doing at KScope’11, “Managing unstructured Data: Lobs, BasicFiles, SecureFiles” (Tue June 28, 3 p.m.). Please, increase my chances of increasing the real development content at OpenWorld 2011. 

P.s. A bit more of advertising - Yalim's architectural talk on how to appropriately use PL/SQL in XXI century is strongly suggested to everybody:

https://mix.oracle.com/events/oow11/proposals/10961

Base64 Encoding in PL/SQL

Just before the holiday weekend it is always nice to tell a good story to cheer everybody up. Here is mine! You know, I've had a couple of good news for you :-) And no, they are not about car insurance - they are about Base64 encoding in PL/SQL:

- with some manual tweaking or parameters it is possible to get a really good performance out of UTL_ENCODE package in both 10g and 11g. In my case - up to 50x performance gain!

And now let's get to details. In the environment I am working right now it just historically happened that we had two different Base64 encoding/decoding procedures (you can google for either of them - there will be  tons of hits on OTN):
- Java-based (classes Base64 and UTLBase64)
     * encodes into 76-char per line Base64 encoding with CHR(10) at the end of the line ~ 77 char/line
- PL/SQL-based (UTL_ENCODE.base64_encode inside).
     * encodes to 64-char per line Base64 encoding with CHR(10)||CHR(13) ~ 66 char/line

Recently we started to use encoded data much more for a number of different reasons - and my developers started to complain about the performance, especially for large data volumes (just FYI - my development server is Dell 6850 4 2-core CPUs, MS Windows 2008, Oracle 10.2.0.5 64 bit) processed by Java-based module.

Eventually I decided to take a step back, and since my area of knowledge is mostly PL/SQL to take a look whether I can make PL/SQL version run faster. Of course, the first suspect in any string processing was the size of buffer - it just seemed strange to me that people (in majority of examples I found online) use single-line size to both operations. It definitely didn't seem optimized. Also a lot of examples use concatenation to generate an output CLOB, which is also a major slow-down. Here are my final version of encode/decode for BLOB:

---------------------------------------------------------------------
function base64EncodeBlob_plsql (i_bl BLOB) 
return clob is
  v_out_cl     CLOB;
  file_len     PLS_INTEGER;
  modulo       PLS_INTEGER;
  pieces       PLS_INTEGER;
  amt          BINARY_INTEGER      := 23808;
  buf          RAW (32767);
  buf_tx       varchar2(32767);
  pos          PLS_INTEGER         := 1;
  filepos      PLS_INTEGER         := 1;
  counter      PLS_INTEGER         := 1;
BEGIN
  DBMS_LOB.createtemporary 
     (v_out_cl, TRUE, dbms_lob.call);
  file_len := DBMS_LOB.getlength (i_bl);
  modulo := MOD (file_len, amt);
  pieces := TRUNC (file_len / amt);

  WHILE (counter <= pieces) LOOP
    DBMS_LOB.READ (i_bl, amt, filepos, buf);
    buf_tx:=utl_raw.cast_to_varchar2
       (UTL_ENCODE.base64_encode (buf));
    dbms_lob.writeappend
       (v_out_cl,length(buf_tx),buf_tx);
    filepos := counter * amt + 1;
    counter := counter + 1;
  END LOOP;

  IF (modulo <> 0) THEN
    DBMS_LOB.READ
      (i_bl, modulo, filepos, buf);
    buf_tx:=utl_raw.cast_to_varchar2
      (UTL_ENCODE.base64_encode (buf));
    dbms_lob.writeappend
      (v_out_cl,length(buf_tx),buf_tx);
  END IF;
  return v_out_cl;
END;

function base64DecodeClobAsBlob_plsql(i_data_cl CLOB) 
return blob is

  v_out_bl blob;

  clob_size number;

  pos number;

  charBuff varchar2(32767);

  dBuffer RAW(32767);

  v_readSize_nr number;

  v_line_nr number;

begin

  dbms_lob.createTemporary
    (v_out_bl, true, dbms_lob.call);

  v_line_nr:=greatest(65, 

                 instr(i_data_cl,chr(10)),

                 instr(i_data_cl,chr(13)));

  v_readSize_nr:=
      floor(32767/v_line_nr)*v_line_nr;

  clob_size := dbms_lob.getLength(i_data_cl);

  pos := 1;

  WHILE (pos < clob_size) LOOP

    dbms_lob.read
      (i_data_cl, v_readSize_nr, pos, charBuff);

    dBuffer := UTL_ENCODE.base64_decode

      (utl_raw.cast_to_raw(charBuff));

    dbms_lob.writeAppend

     (v_out_bl,utl_raw.length(dBuffer),dBuffer);

    pos := pos + v_readSize_nr;

  end loop;

  return v_out_bl;

end;

----------------------------------------------------------------

Performance improvement was just enormous! Let's take the process of encoding of 1 MB BLOB:

- Java version - 5 sec

- unoptimized PL/SQL (48 chars per read) - 11 sec

- optimized PL/SQL (23808 char per read) - 0.1 (50x gain and 110x gain!)

Decoding on 1 MB BLOB:

- Java version - 5 sec

- unoptimized PL/SQL (66 chars per read) - 2 sec

- optimized PL/SQL (32736 char per read) - 0.1  sec (50x and 20x gain).

Now let me explain where I found these magic numbers. For encode the logic is as follows:

- Known:

   * the length of the line is 66 or 64 data chars + 2 end-of-line.
   * 64 Base64 encoded data chars represent 64*(3/4)=48 original data bytes.

   * the result cannot exceed 32K for each processing (stay in the scope of VARCHAR2 datatype).
- Highest data read is calculated in the following way: floor(32767/66)*48 = 23808.

For decode:

- Known:

  * The routine may get different kinds of Base64 encoded strings.

  * for now I decided to support 64-char and 76-char formats (plus end-of-line)

  * these specified formats could generate 65,66,77 or 78 char/line

  * length of the line is identified by the highest of first occurances of CHR(10) / CHR(13) 

  * in when the original volume of data was less then 48 - default 65 char/line is established

  * the result cannot exceed 32K for each processing (stay in the scope of VARCHAR2 datatype). 

  * read should always happen in complete lines

- Highest data read is calculated in the following way: floor(32767/<lineSize>)*<lineSize>

P.s I also run a number of test in 11g (11.2.0.1). Unfortunately, I the hardware on that box is completely different from what I have in my 10g instance, so at the current point I will not post a lot of real numbers (it will be like comparing apples to oranges), but the overall findings are the following:
- Java-version - 0.5 sec

- Optimized PL/SQL - 0.1

So, additional good news - 11g version of JVM is significantly faster than 10g, but my PL/SQL code still outruns it.

P.p.s. I am wondering if anybody from Java-side would like to come up with some optimized Java-code to be loaded into the database. It would be interesting to compare!

P.p.p.s. Yes, I know that JVM is not coming with XE-edition. That's one of the reasons I decided to optimize my PL/SQL version (since Dulcian's BRIM products are compatible with all editions)

Small thoughts about hierarchical queries

I am just wondering, how many times the question of how exactly does Oracle execute CONNECT BY has been raised? I think, all known database experts had at least something on this :-) I didn't expect this thing to be so popular! Although, it could say something about either the quality of existing books/docs or about the fact very few newcomer ever read aforementioned books/docs.

Knowing a number of currently published authors my guess would be the latter has higher probability. H-m-m-m-m (if anybody reads this blog) - here is a survey: if you are planning to use a new feature would you rather read docs first and try later - or you would first try and check the doc only is something doesn't work as expected? :-)

P.s. I should not break the trend.  Here are my 2c on CONNECT BY.

Hierarchical queries

For the last couple of days I am working on the new paper of ODTUG KScope'11 on how to appropriately work with hierarchical data. The reason I decided to open this topic was Oracle's introduction in 11g R2 an extended version of CTE (common table expressions) - or to call it simpler a recursive WITH-clause (here is a set of examples: link, another link). For years it was used in a number of RDBMS implementations (SQL Server, PostgreSQL etc), while Oracle people were using Oracle's own CONNECT-BY clause.

I understand, that compatibility with other platforms is a good thing - but I am still struggling to figure out why anybody in Oracle would care. Me and my co-worker Grigoriy Novikov are running a set of tests and will keep you posted whether there are any performance reasons (doesn't look like - yet), but even architecturally I am not 100% sure that I see the "25-words-or-less" case that would prove the existence of this feature.

To be fair, I know that I am biased - if the problem cannot be solved with simple SQL statement, I immediately build function that would return a result collection as an output and do all my processing in PL/SQL. Maybe this approach is limited - but this code I can give to my junior developers to maintain. Unfortunately, proposed solutions involving CTE (as far as I can see) are above the comprehension point of mere mortals... I appreciate the level of the knowledge of people providing such solutions - but I am still looking for a good "PL/SQL for Dummies"-level example.

To be continued!

ASSOCIATE STATISTICS

Oracle RDBMS environment is too too big to know everything - as a result from time to time I just stumble at   some functionality that existed for years but I never heard of it! My today's finding is 'ASSOCIATE STATISTICS' command (more details here).

Briefly, it allows to hard-code some statistics (like cost or cardinalities) to different Oracle objects - columns, functions, packages, indexes etc. And this statistics will directly impact CBO's decisions - as any other statistics! Since in Dulcian we use a lot of PL/SQL, this feature could significantly simplify my tuning efforts in cases when we have functions inside of SQL queries.

Minor issues:
1. It looks like statistics cannot be associated with a separate function in the package - only standalone function. Too bad - will have to have logical wrappers!
2. Statistics can be associated with a package. Hmmm... Not sure that I understand that logic (and I was not able to find meaningful examples).

Will keep you posted on further discoveries - but it seems to be a nice add-on to my list of tricks (available in both 10g and 11g!)

Danger of explicit conversions

At the last conference (Collab'11) I've had a discussion with one of the speakers about the danger of explicit conversions in Oracle (like TO_CHAR(SYSDATE) without a format mask or direct concatenations). Strange enough, from his experience still too many people are trying to save a couple of extra key-strokes - and eventually get themselves into major troubles.

IMHO, in the environment where the process of code review is reasonably common, any code with such conversions should not pass evaluation at all! There is a number of good reasons (and here I could've typed 2-3 pages)... My favorite one is that explicit conversions open an additional hole for SQL injections. Here is a sample:

SQL> alter session set  NLS_Date_Format = '"'''' or 1=1--';

Session altered.

SQL> declare

  2      v_dt date:=sysdate;

  3      v_count_nr number;

  4

  5      v_sql_tx varchar2(4000);

  6  begin

  7      v_sql_tx:='select count(*) from scott.emp where hiredate>'||v_dt;

  8

  9      dbms_output.put_line('Query:'||v_sql_tx);

 10      execute immediate v_sql_tx into v_count_nr;

 11

 12      dbms_output.put_line('Count:'||v_count_nr);

 13  end;

 14  /

Query:select count(*) from scott.emp where hiredate>'' or 1=1--

Count:14

PL/SQL procedure successfully completed.

SQL>

You may notice a very strange date format - and that's exactly how the SQL injection happens! Concatenation of a DATE attribute (line 7) to a string under the hood fires TO_CHAR(V_DT) with the default format mask that was just overridden by ALTER SESSION. As a result, the query shows the total count of rows in the EMP table (14) when it should return 0! 

Good enough reason? I would think so.

Oracle Bug

One of my developers just noticed a strange behavior in all versions of Oracle 10g that we could test (10.2.0.3 on Win32, 10.2.0.5 on Win64).

Here is a simplified case:

REPLACE command is fired against a CLOB variable where we are trying to change 'T_' to 'ZZ'. In the CLOB there is a part of text that looks like 'T('.

To my own surprise, Oracle replaces both 'T_' and 'T(' with 'ZZ': it looks like underscore is being interpreted as a wild-card! By the way, this behavior is completely gone in 11gR2.

Code sample:

declare

    v1_cl CLOB := 'T_T(a number)';

begin

    v1_cl := replace (v1_cl, 'T_', 'ZZ');

    dbms_output.put_line(v1_cl);

end;

Received result: ZZZZa number)

Expected result: ZZT(a number)

Update (thanks to Grigoriy Novikov): It is not a feature - it is a real Oracle bug (Metalink Bug ID:4598943), first detected in 9.2.0.6 and fixed in 11g. So, please, be aware of it!

New book!

After a couple of years of staying outside of publishing business I decided to make something new "in paper". Although, getting a whole new book was too much for me to survive right now, but Jonathan Gennick from APress came with an idea of "Expert PL/SQL practices" - one chapter per author.

And looks like the chances are very high that around OpenWorld'11 you could have such book printed! It even got a link on APress web-site and a preliminary cover:

http://www.apress.com/databases/oracle/9781430234852

P.s.  Hmm... My first book ("PL/SQL for Dummies", Wiley, 2006) is still getting enough people interested: today in the morning it was in Top-10 Oracle books on Amazon!
http://www.amazon.com/Oracle-SQL-Dummies-Michael-Rosenblum/dp/0764599577/ref=zg_bs_4092_10

Crossing the barrier...

Finally, I decided that it is really time for me to start blogging in English (as a lot of people may know, my native languages are Russian and Ukrainian). There is just too much information around me worthwhile sharing!

Yes, it took me a number years plus a number of conversations with people I trust to step up and get over the language barrier. You see, for all of these years in Dulcian I've had a wonderful editor behind my back (who could translate from mine to normal English), so, please, be patient and forgiving :-), while I will try my best not to break too many rules at the same post!