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!