Link: http://www.pythian.com/news/6891/more-fun-with-perl-and-inline-ifs/

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 . . . 

my $price_dollars = '0';
my $price_cents = '00';
if ($mode eq 'Edit') {
$price_dollars = $line_item[$count]->price_dollars();
$price_cents = $line_item[$count]->price_cents();
}

 . . . and then use them later on:

CGI::textfield({name => 'price_dollars',
width => '5',
value => $price_dollars}).
CGI::textfield({name => 'price_cents',
value => $price_cents });

Well at least this cuts down on the if statements, but now I have a whole bunch of single-use scalars hanging about–not very neat.

I could go for an Array or a Hash for my values, but as you might have already guessed, $line_item[$count] is an object of some form. So why not just use that?

So. At each iteration I check to see if I have a invalid line item . . . 

if (!$line_item[$count]) {
$line_item[$count] = Foo::Bar:LineItem->new();
}

 . . . and if I do, I simply create a new empty one, and then simply do this on my form:

CGI::textfield({name => 'price_dollars',
width => '5',
value => $line_item[$count]->price_dollars() });
CGI::textfield({name => 'price_cents',
value => $line_item[$count]->price_cents()});

Much simpler, and easier to read. Cheers!

Link: http://jonathanlewis.wordpress.com/2009/12/31/dbms_metadata/

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.

select dbms_metadata.get_ddl('TABLE','T1','TEST_USER') from dual;

As a consequence of [...]

Link: http://ba6.us/node/150

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.

read more

Link: http://rwijk.blogspot.com/2009/12/paper-about-grouping.html

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.

Link: http://orastory.wordpress.com/2009/12/31/sql_plan-other_xml/

Just a quick follow-up to my previous post on investigating bad plans using AWR & ASH.

In that article, I mentioned

1. that 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) and
2. that I was using DBA_HIST_SQLBIND to investigate various peeked binds for that particular SQL based on the LAST_CAPTURED column

However, 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.

From the documentation:

OTHER_XML: “Provides extra information specific to an execution step of the execution plan. The content of this column is structured using XML because it allows multiple pieces of information to be stored, including the following:

• Name of the schema against which the query was parsed.
• Release number of the Oracle Database that produced the explain plan.
• Hash value associated with the execution plan.
• Name (if any) of the outline or the SQL profile used to build the execution plan.
• Indication of whether or not dynamic sampling was used to produce the plan.
• The outline data, a set of optimizer hints that can be used to regenerate the same plan.

“

Here is a sample of the peeked bind section from my sql with the problem plan:

  <peeked_binds>
    <bind nam=":B3" pos="3" dty="2" pre="0" scl="0" mxl="22">c3023102</bind>
    <bind nam=":B2" pos="4" dty="1" csi="873" frm="1" mxl="32">524d46</bind>
    <bind nam=":B1" pos="5" dty="12" mxl="7">786d0a1f010101</bind>
    <bind nam=":B3" pos="13" ppo="3" dty="2" pre="0" scl="0" mxl="22"/>
    <bind nam=":B2" pos="14" ppo="4" dty="1" csi="873" frm="1" mxl="32"/>
    <bind nam=":B1" pos="15" ppo="5" dty="12" mxl="7"/>
    <bind nam=":B3" pos="16" ppo="3" dty="2" pre="0" scl="0" mxl="22"/>
    <bind nam=":B1" pos="19" ppo="5" dty="12" mxl="7"/>
    <bind nam=":B3" pos="20" ppo="3" dty="2" pre="0" scl="0" mxl="22"/>
    <bind nam=":B5" pos="21" ppo="1" dty="2" pre="0" scl="0" mxl="22"/>
    <bind nam=":B3" pos="22" ppo="3" dty="2" pre="0" scl="0" mxl="22"/>
    <bind nam=":B6" pos="23" dty="2" pre="0" scl="0" mxl="22">c10d</bind>
    <bind nam=":B1" pos="24" ppo="5" dty="12" mxl="7"/>
    <bind nam=":B6" pos="26" ppo="23" dty="2" pre="0" scl="0" mxl="22"/>
    <bind nam=":B1" pos="27" ppo="5" dty="12" mxl="7"/>
    <bind nam=":B1" pos="29" ppo="5" dty="12" mxl="7"/>
    <bind nam=":B3" pos="31" ppo="3" dty="2" pre="0" scl="0" mxl="22"/>
    <bind nam=":B6" pos="32" ppo="23" dty="2" pre="0" scl="0" mxl="22"/>
    <bind nam=":B1" pos="33" ppo="5" dty="12" mxl="7"/>
  </peeked_binds>

I’m not sure if the repetition of bind names and positions but not values is normal – one for further investigation.

But bind variable 1 is what I was talking about previously as my date bind with the data skew and we can convert the values back out of their raw value using, for example, the overloaded DBMS_STATS.CONVERT_RAW_VALUE.

So, from above, taking my B1 bind of “786d0a1f010101″:

declare
l_date date;
begin
dbms_stats.convert_raw_value(rawval => '786d0a1f010101', resval => l_date);
dbms_output.put_line('dt: '||to_char(l_date,'DD/MM/YYYY HH24:MI:SS'));
end;
/

dt: 31/10/2009 00:00:00

Some other posts regarding OTHER_XML:
Kerry Osborne using OTHER_XML to create test scripts with bind variables
Randolf Geist using OTHER_XML to get outlines / create profiles

Link: http://karlarao.wordpress.com/2009/12/31/50-sql-performance-optimization-scenarios/

Link: http://hoopercharles.wordpress.com/2009/12/31/hard-parses-when-using-bind-variables/

December 31, 2009

Assume that the following tables are created and then statistics are gathered:

CREATE TABLE T3 AS
SELECT
  ROWNUM C1,
  LPAD('A',100,'A') C2
FROM
  DUAL
CONNECT BY
  LEVEL<=10000;

CREATE TABLE T4 AS
SELECT
  ROWNUM C1,
  LPAD('A',100,'A') C2
FROM
  DUAL
CONNECT BY
  LEVEL<=10000;

CREATE INDEX IND_T4 ON T4(C1);

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T3',CASCADE=>TRUE)
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T4',CASCADE=>TRUE)

If you then set up SQL*Plus with the following commands:

VARIABLE N1 NUMBER
VARIABLE N2 NUMBER
SET AUTOTRACE TRACEONLY STATISTICS
SET ARRAYSIZE 1000

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, if you do the following in SQL*Plus:

EXEC :N1 := 1
EXEC :N2 := 1
SELECT T3.C1, T4.C2 FROM T3, T4 WHERE T3.C1 BETWEEN :N1 AND :N2 AND T3.C1=T4.C1;
SELECT T3.C1, T4.C2 FROM T3, T4 WHERE T3.C1 BETWEEN :N1 AND :N2 AND T3.C1=T4.C1;
SELECT T3.C1, T4.C2 FROM T3, T4 WHERE T3.C1 BETWEEN :N1 AND :N2 AND T3.C1=T4.C1;
SELECT T3.C1, T4.C2 FROM T3, T4 WHERE T3.C1 BETWEEN :N1 AND :N2 AND T3.C1=T4.C1;

Now, repeat the above 9,999 times in the same session, specifying random values for N1 and N2 such that:

• N1 >= 1
• N2 <= 10,000
• N1 <= N2

Does it matter if you test with Oracle 8.0.5 (assuming that you use the ANALYZE command rather than DBMS_STATS), 11.2.0.1, or something in between?

Link: http://jonathanlewis.wordpress.com/2009/12/30/simple-scripts/

Most recent addition (30th Dec 2009): Segment Scans.
Since many of my customers are fairly hot on security I’m often banned from plugging USB drives into local PCs, or getting my laptop onto the local network, or downloading from the Internet, so I’ve often had to type up ad-hoc queries whenever I’ve had a thought about [...]

Link: http://blog.tanelpoder.com/2009/12/30/null-is-not-zero/

Some time ago I wrote a post about how COUNT(*) and COUNT(column) are semantically different things (link). Such queries may return different results if the column counted has NULLs in it. And the difference comes from that NULL is not a value, it’s rather a state which says “value unknown” or “no value entered”.

So, you better understand how NULLs interact with your SQL constructs if you call yourself a DBA or a database developer ;-)

Here’s another example about how misunderstanding NULLs may cause your application to return different results than what was intended.

I will create a little table with TWO rows in it:

SQL> create table t(a int);

Table created.

SQL> insert into t values(1);

1 row created.

SQL> insert into t values(null);

1 row created.

SQL> select avg(a) from t;

AVG(A)
----------
1

When I take an average of the 2 values in these rows I get average of 1.

Now lets update the NULL (no value) to 0 (an actual value of zero).

SQL> update t set a=0 where a is null;

1 row updated.

SQL> select avg(a) from t;

AVG(A)
----------
.5

As you see, as we now have an actual value in the other row (as opposed to “no value&#8221, the AVG function takes that zero into account.

Hopefully this illustrates once more that NULL does not mean zero or any other value, it means NO value. If you do aggregation functions (count,avg) over NULLs then you must understand that Oracle treats NULLs as no value and doesn’t account these “no values”, thus your queries may behave differently than what your intuition might say (and yes its always good to read documentation about what exactly a given SQL construction/function does in the given database engine instead of relying on “common sense&#8221.

Link: http://kevinclosson.wordpress.com/2009/12/30/hot-oracle-related-search-engine-terms-for-2009-carry-on-wayward-googler/

2009 is drawing to a close so I just took a gander at the year-end search term statistics for my blog. It’s interesting to see the search terms that send readers to this blog. Some are surprising, others not so surprising. So, for trivial pursuit sake, the following list shows the top 50 search terms [...]

Link: http://jonathanlewis.wordpress.com/2009/12/28/short-sorts/

I posted a little holiday quiz – timed to appear just before midnight (GMT) on 24th December – that asked about the number of rows sorted and the memory used for queries like:

select sortcode
from
(
  select sortcode
  from t1
order by
  [...]

Link: http://jonathanlewis.wordpress.com/2009/12/24/holiday-quiz/

I have a table with one million rows, there are no indexes on the table. The table has a column called sortcode which has no nulls, and has been generated in a highly random way so that no value appears more than four times. Consider the following queries:

select
[...]

Link: http://jonathanlewis.wordpress.com/2009/12/23/btree-bitmap/

In a recent ‘philosophy’ post I focused on the critical mental image that should be adopted when comparing B-tree and bitmap indexes. I was a little surprised, however, to discover that the idea I proposed needed further explanation. So here’s a note that expands on the original comment.
The point that caused confusion was simple: when [...]

Link: http://jonathanlewis.wordpress.com/2009/12/22/new-links/

I’ve just added Charles Hooper and Joze Senegacnik to my list of useful blogs – so this is a temporary note to draw attention to the change; and if you hadn’t noticed it, Kyle Hailey is a fairly recent addition.
Updated: The lastest additions:  Dion Cho and Timur Akhmadeev.

Link: http://jonathanlewis.wordpress.com/2009/12/22/optimizer-features/

Each time you upgrade the Oracle server (even with a patch release), you may find that some strange things happen to a few execution paths. Every release carries some changes to the optimizer code – sometimes enhancements, sometimes bug fixes – and every change might be one that just happens to do something nasty with your [...]

Link: http://jonathanlewis.wordpress.com/2009/12/19/advert-odtug/

Right now I’m desperately struggling to catch up on my email, blogging and posting from the last two weeks – so I’ve just got time to let you know that I’ve just had confirmation from ODTUG that they’ve accepted all three of my abstracts for Kaleidoscope 2010. See Public Appearances for a list of the [...]

Link: http://jonathanlewis.wordpress.com/2009/12/17/partition-indexing/

From time to time I get asked if it’s possible to index a partitioned table so that recent partitions have different (local) indexes from older partitions. The answer is “not really, but there’s a couple of dirty tricks which aren’t very nice and aren’t very stable“. (You can always play around – dangerously – with [...]

Link: http://jonathanlewis.wordpress.com/2009/12/16/adaptive-optimisation/

Here’s an interesting post and test case from Gregory Guillou (WeDoStreams blog). It features an SQL statement that is re-optimised the second time you run it.
Since it’s running on 11.2.0.1 your first thought is likely to be “SQL Plan Management”, or “Adaptive Cursor Sharing” – except the first feature wasn’t enabled, and the statement doesn’t [...]