My old arch-nemesis, the in-line if ($q = $q == $a ? $b : $c; ) reared its ugly little head again. This time, it was in context of an web page that displayed some form values, something like this:CGI::textfield({name => 'price_dollars', width => '5', value => ($mode eq 'Edit' ? $line_item[$count]->price_dollars() : '0') });CGI::textfield({name => 'price_cents', value => ($mode eq 'Edit' ? $line_item[$count]->price_cents(): '00' });This carried on for 15 other fields on the form. So, we have 17 if else statements all checking to see if the form is in ‘Edit’ mode. If there were, say, ten line items on the form . . .  well no need to go any further, other than to say that is a whole lot of if statements.While this does not take up much space, this multiplicity of ifs is not really necessary or even good, since to the compiler, an inline if and a bracketed one are the same. The inline is only a shorthand to make our code more readable.We could of course just declare 17 scalars at each iteration, and then a use single if statment to set these scalars, like this . . .  more »

The dbms_metadata package has a few convenient functions and procedures that allow you to generate the SQL to recreate parts of your database. The best-known function is probably dbms_metadata.get_ddl(), but every example of its use that I’ve seen on the Internet seems to use a “select from dual”, e.g. more »

If you are experimenting with gathering system statistics it might be helpful to archive your current settings and any intermediate settings you come up with along the way. All the system stats info is held in the sys.aux_stats$ table. Since the format is a little wacky, I came up with the following table to hold stats and the following insert statement to populate it after every gathering of system stats. more »

In 2009 I have presented "Alles Over Groeperen" twice in the Netherlands and the English version "All About Grouping" also twice, once at the Oracle OpenWorld Unconference and once at UKOUG. If you haven't been able to visit one of these presentations, and you are interested in the subject, then you can catch up by reading the paper that I just finished. more »

Just a quick follow-up to my previous post on investigating bad plans using AWR & ASH.In that article, I mentionedthat it was a shame that the ACCESS_PREDICATES and FILTER_PREDICATES columns were not populated (how this cannot be classified as a bug is beyond comprehension) andthat I was using DBA_HIST_SQLBIND to investigate various peeked binds for that particular SQL based on the LAST_CAPTURED columnHowever, I should also have mentioned that, from 10gR2 onwards, populated in both V$SQL_PLAN and DBA_HIST_SQL_PLAN, there is a very useful column called OTHER_XML. more »

Before the year ends I’d like to share some good stuff… I have never seen a huge compilation of SQL tuning tips or rewrite scenarios (with test cases) and got them only on OracleFans forum… ooops… so you can’t read Chinese? try this translated version, whew.. good thing Google has this translate service that I am able to read in Chinese.. more »

How many hard parses would you see for the session, and how many child cursors for the SQL statement will be in the library cache more »