Datatype discipline

(This is based on some ‘issues’ a client was having, so the example is architected to make a particular point, but is relevant nonetheless)

In the distributed database world, we often have different names for the same piece of information.

Consider two tables which hold customer information.  In one table, they called the customer key "REF_NUM", and in the other table "CUST_ID".

That’s not ideal, but it’s not a big drama because at least the data types align.

        SQL> desc T1

                   Name                                        Null?    Type
                   ------------------------------------------- -------- ----------------------
                   REF_NUM                                     NOT NULL NUMBER(10)
                   T1_DATA                                              VARCHAR2(1000)

        SQL> desc T2

                   Name                                        Null?    Type
                   ------------------------------------------- -------- ----------------------
                   CUST_ID                                     NOT NULL NUMBER(10)
                   T2_DATA                                              VARCHAR2(1000)

When I do a join between these two tables, it costs me about 9400 I/O’s

        SQL> select *
          2  from  t1, t2
          3  where t1.ref_num = t2.cust_id
          4  and   t1.t1_data like '100%';

        Statistics
        ----------------------------------------------------------
                  0  recursive calls
                  1  db block gets
               9408  consistent gets
                  0  physical reads

Consider now the same two tables, with exactly the same data, but the data types are not aligned.  In one system, they used a numeric, and in the other system they used a string

        SQL> desc T1

                   Name                                        Null?    Type
                   ------------------------------------------- -------- ----------------------
                   REF_NUM                                     NOT NULL NUMBER(10)
                   T1_DATA                                              VARCHAR2(1000)

        SQL> desc T2

                   Name                                        Null?    Type
                   ------------------------------------------- -------- ----------------------
                   CUST_ID                                     NOT NULL VARCHAR2(10)
                   T2_DATA                                              VARCHAR2(1000)

Now look what happens to my SQL

        SQL> select *
          2  from  t1, t2
          3  where t1.ref_num = t2.cust_id
          4  and   t1.t1_data like '100%';

        Statistics
        ----------------------------------------------------------
                  0  recursive calls
                  2  db block gets
              18428  consistent gets
                  0  physical reads

It costs DOUBLE the amount of I/O’s – it runs twice as slowly.

Moreover, it’s not just performance that is at risk…You’re whole application might just start to crash randomly

Using the same tables above (with the conflicting data types) here’s a SQL that works fine "today"…

        SQL> select count(*)
          2  from  t1, t2
          3  where t1.ref_num = t2.cust_id
          4  and   t2.t2_data like '100%';

          COUNT(*)
        ----------
                71

Then a pesky user comes along and does what users do…adds data to the one of the tables.

        SQL> insert into T2 values ('X123','100some data');

        1 row created.

And now let’s re-run that same query again

        SQL> select count(*)
          2  from  t1, t2
          3  where t1.ref_num = t2.cust_id
          4  and   t2.t2_data like '100%';

        ERROR: ORA-01722: invalid number

Ker-splat….your application is toast…

Not being disciplined with data type selection causes pain later.

Default null for collection parameter

I’ve got an existing package called DEMO as below

SQL> create or replace package demo is
  2     -- used to pass list of numbers
  3     TYPE t_num_list IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  4
  5     PROCEDURE p(
  6       p_list1               IN     t_num_list
  7     );
  8  end;
  9  /

Package created.

but what I’d like to do is add another collection parameter to it, whilst keeping backward compatibility

I could use a overloaded version, or I can default that second parameter to null.  Lets explore the second option:

SQL> create or replace package demo is
  2     -- used to pass list of numbers
  3     TYPE t_num_list IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  4
  5     PROCEDURE p(
  6       p_list1               IN     t_num_list,
  7       p_list2               IN     t_num_list default null
  8     );
  9  end;
 10  /

Warning: Package created with compilation errors.

SQL> sho err
Errors for PACKAGE DEMO:

LINE/COL ERROR
-------- ---------------------------------------------------------------
5/4      PL/SQL: Declaration ignored
7/54     PLS-00382: expression is of wrong type

Well that’s no good. What we need to do is ensure that the types are consistent, like this:

SQL> create or replace package demo is
  2     -- used to pass list of numbers
  3     TYPE t_num_list IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  4
  5     PROCEDURE p(
  6       p_list1               IN     t_num_list,
  7       p_list2               IN     t_num_list default cast(null as t_num_list)
  8     );
  9  end;
 10  /

Package created.

But just remember, in doing so, when you reference the parameter inside your package body, it is no longer null in the ‘conventional’ sense, as the following little example demonstrates

SQL> create or replace package body  demo is
  2
  3     PROCEDURE p(
  4       p_list1               IN     t_num_list,
  5       p_list2               IN     t_num_list default cast(null as t_num_list)
  6     ) is
  7     begin
  8       if p_list2 is null then
  9          dbms_output.put_line('I am null');
 10       end if;
 11
 12       if p_list2.count = 0 then
 13          dbms_output.put_line('I am empty');
 14       end if;
 15    end;
 16  end;
 17  /

Package body created.


SQL> set serverout on

SQL> declare
  2     x demo.t_num_list;
  3  begin
  4    demo.p(x,x);
  5  end;
  6  /
I am empty

PL/SQL procedure successfully completed.

Long overdue…

The OTN forums, which should be a plethora of knowledge and learning, were always hampered by the hideous interface.

But, changes are coming.  It will be interesting to see what transpires

https://wikis.oracle.com/display/Forums/OTN+Forums+Migration+and+Upgrade

Its not about the outage

My Oracle Support had a fairly lengthy outage today right in the middle of the Australian business day.

But I’m not going to blog about that.  One thing I’ve learnt from many client sites is that people will understand and forgive things like outages, or errors, or crashes, or just plain wrong software, as long its evident that you are passionately working for the benefit of the user, that you were not lazy or flippant or learning from mistakes…

But one thing, perhaps the biggest thing, that customers will NOT tolerate, is when you don’t listen to what they’re trying to tell you

And that’s where MOS is suffering – not from outages, not from errors, but from not listening….

I logged this SR:

Problem: Making index partition unusable does not free underlying segment

Test Case:

SQL> sho parameter deferred

NAME TYPE VALUE
------------------------------------ ----------- ----------------------------------
deferred_segment_creation boolean TRUE

SQL> drop table T purge;

Table dropped.

SQL> create table T ( x timestamp, y int)
  2  PARTITION BY RANGE (x)
  3  INTERVAL( NUMTODSINTERVAL(1,'DAY'))
  4  (
  5  PARTITION ARCH_P0 VALUES LESS THAN (TIMESTAMP' 2009-01-01 00:00:00')
  6  )
  7  /

Table created.

SQL> insert into T values ('01-APR-13',1);
SQL> insert into T values ('02-APR-13',1);
SQL> insert into T values ('03-APR-13',1);
SQL> insert into T values ('04-APR-13',1);

SQL> create index IX on T ( x ) local;

Index created.

SQL> col partition_name new_value d
SQL> select segment_name, partition_name, bytes from user_segments;

SEGMENT_NAME PARTITION_NAME BYTES
---------------------------------------- ------------------------------ ----------
T SYS_P42997 8388608
T SYS_P42998 8388608
T SYS_P42999 8388608
T ARCH_P0 8388608
T SYS_P42996 8388608
IX ARCH_P0 65536
IX SYS_P43000 65536
IX SYS_P43001 65536
IX SYS_P43002 65536
IX SYS_P43003 65536

10 rows selected.

SQL> alter index IX modify partition &&d unusable;

Index altered.

SQL> select segment_name, partition_name, bytes from user_segments;

SEGMENT_NAME PARTITION_NAME BYTES
---------------------------------------- ------------------------------ ----------
T SYS_P42997 8388608
T SYS_P42998 8388608
T SYS_P42999 8388608
T ARCH_P0 8388608
T SYS_P42996 8388608
IX ARCH_P0 65536
IX SYS_P43000 65536
IX SYS_P43001 65536
IX SYS_P43002 65536
IX SYS_P43003 65536

10 rows selected.

Now before people criticise that I didn’t show version and platform, remember that in logging an SR, these things are provided as the SR is logged.  In this case, the platform is AIX and the version is 11.2.0.2.  I also posted a case into the SR showing the under 11.2.0.3 on Linux, the segment is indeed correctly dropped when the index partition is set to unusable.

Its a trivial test case, and my inquiry was simple – is it platform or version or both that is the issue.

But this post is about listening…

First response to the SR:

Your problem is this: "Unusable Index Segment Still Exists in DBA_SEGMENTS for Alter Table Move"

Well…I dont think so. Probably because the test case makes no mention of alter-table-move. 

I point this out via an SR update.

Second response to the SR:

You can drop the partition to reclaim free space

For starters, the ambiguity is risky advice.  Do they mean drop the table partition or the index partition ?  Might my next SR be "how can I recover the data I just dropped ?"

Secondly, its not addressing the original request.

So I’m more than happy to accept that an MOS outage happened….these things do in the IT world.  We try to avoid them, but they happen.

But its sad when the NON-technical components of IT, that of simple good listening skills also suffer regular "outages".

OUGN Norway Conference

The OUGN conference (held on a cruise ship) was an awesome experience and so professionally run by the committee.  I recommend it to anyone.

The slides from my talks can be found here

Norway bound

The OUGN user conference (on a cruise ship!) starts next week and I’m presenting there.

From all reports, this is an awesome conference.

Full agenda is here: http://www.ougn.no/vrseminar-2013

ORA_HASH and LOBs …. not nice partners

Consider the following simple setup

SQL> create table t ( x int, b blob );

Table created.

SQL> declare
  2    bin blob;
  3  begin
  4    insert into t values (1, empty_blob())
  5    returning b into bin;
  6
  7    dbms_lob.writeappend(bin,10000,utl_raw.cast_to_raw(rpad('x',10000,'x')));
  8    commit;
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> select ora_hash(b) from t;

ORA_HASH(B)
-----------
 3856858313

SQL> select ora_hash(b) from t;

ORA_HASH(B)
-----------
 3856858313

SQL> select ora_hash(b) from t;

ORA_HASH(B)
-----------
 3856858313

That all seems fine…but thats because I ran the ORA_HASH commands quickly one after the other… Now that 30 seconds has passed (as I type this into the blog)…lets run the same ORA_HASH again

SQL> select ora_hash(b) from t;

ORA_HASH(B)
-----------
 1078420585

It changes ! In fact, one most platforms it appears to change every 3 seconds, so presumably somewhere in there its related to one the internal Oracle timers in some way.

Bottom line – you can’t use ORA_HASH on LOB’s. (There is also some information in Metalink note 427803.1)

MERGE – concise syntax but not always fastest

A long time ago … a long long time ago (http://www.oracledba.co.uk/tips/merge_speed.htm) I wrote that MERGE works best over update and insert.

This still holds true, but its also apparent that MERGE seems to be optimized for larger sets of rows.  When you take it down to single row operations, then don’t be SO hasty to recast your updates-and-inserts into merge commands.

SQL> create table t1
  2   ( x int primary key,
  3     y int );

Table created.

SQL>
SQL> -- all inserts
SQL>
SQL> set timing on
SQL> begin
  2  for i in 1 .. 50000 loop
  3    merge into t1
  4    using ( select i x, i y
  5            from dual ) m
  6    on ( t1.x = m.x )
  7    when matched then update
  8    set t1.y = m.y
  9    when not matched then
 10    insert values (m.x, m.y );
 11  end loop;
 12  end;
 13  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.79
SQL>
SQL> -- 50/50
SQL>
SQL> set timing on
SQL> begin
  2  for i in 25000 .. 75000 loop
  3    merge into t1
  4    using ( select i x, i y
  5            from dual ) m
  6    on ( t1.x = m.x )
  7    when matched then update
  8    set t1.y = m.y
  9    when not matched then
 10    insert values (m.x, m.y );
 11  end loop;
 12  end;
 13  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.31
SQL>
SQL> -- all updates
SQL>
SQL> set timing on
SQL> begin
  2  for i in 25000 .. 75000 loop
  3    merge into t1
  4    using ( select i x, i+1 y
  5            from dual ) m
  6    on ( t1.x = m.x )
  7    when matched then update
  8    set t1.y = m.y
  9    when not matched then
 10    insert values (m.x, m.y );
 11  end loop;
 12  end;
 13  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.14
SQL>
SQL>
SQL> drop table t1 purge;

Table dropped.

Elapsed: 00:00:00.35
SQL>
SQL> create table t1
  2   ( x int primary key,
  3     y int );

Table created.

Elapsed: 00:00:00.03
SQL>
SQL> -- all inserts
SQL> set timing on
SQL> begin
  2  for i in 1 .. 50000 loop
  3    update t1 set y = i where x = i;
  4    if sql%notfound then insert into t1 values (i,i); end if;
  5  end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:11.65
SQL>
SQL> -- 50/50
SQL>
SQL> set timing on
SQL> begin
  2  for i in 25000 .. 75000 loop
  3    update t1 set y = i where x = i;
  4    if sql%notfound then insert into t1 values (i,i); end if;
  5  end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:09.20
SQL>
SQL> -- all updates
SQL>
SQL> set timing on
SQL> begin
  2  for i in 25000 .. 75000 loop
  3    update t1 set y = i+1 where x = i;
  4    if sql%notfound then insert into t1 values (i,i+1); end if;
  5  end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.94
SQL>

AUSOUG session February 2013

Thanks to everyone that came along.

The slides can be found here

Lob CACHE vs NOCACHE – impact on flashback logging

 

I was doing some work for a client, and LOB processing seemed incredibly slow.  After some poking around, it appears that LOB processing is quite sensitive when it comes to flashback mode in the database. Test bench is a simple table with three clobs. 4kb will be insert into each clob in 5000 individual inserts. The "wait" option is applied to the commit to ensure that the standard PLSQL commit optimizations do not come into play.

Database is 11.2.0.1 running in flashback mode.

SQL> create table t_nocache ( x1 clob, x2 clob, x3 clob )
  2    lob ( x1 ) store as ( nocache )
  3    lob ( x2 ) store as ( nocache )
  4    lob ( x3 ) store as ( nocache )
  5    ;

Table created.

SQL> set timing on
SQL> begin
  2  for i in 1 .. 5000 loop
  3    insert into t_nocache values (rpad('x',4000,'x'),rpad('x',4000,'x'),rpad('x',4000,'x'));
  4    commit WRITE WAIT IMMEDIATE;
  5  end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:13.21

So for NOCACHE, the elapsed time is 13 seconds, and then we can inspect where the time was lost:

SQL> @sessev
Sid required: 17

EVENT                                        TOTAL_WAITS TOTAL_TIMEOUTS       SECS PCT        MAX_WAIT
-------------------------------------------- ----------- -------------- ---------- -------- ----------
Data file init write                                   2              0          0 00.00%            0
events in waitclass Other                              2              0          0 00.00%            0
db file single write                                   2              0          0 00.00%            0
control file parallel write                           14              0          0 00.00%            0
CPU                                                                              0 00.00%            0
log file switch (checkpoint incomplete)                2              0        .01 00.11%            1
log file switch completion                             3              0        .01 00.11%            0
control file sequential read                          70              0        .01 00.11%            0
Disk file operations I/O                              11              0        .04 00.44%            1
db file sequential read                              563              0        .13 01.44%            2
buffer busy waits                                      1              0        .25 02.77%           25
direct path write                                  14996              0       1.19 13.16%            2
log file sync                                       4999              0       1.82 20.13%            7
direct path read                                   14865              0       2.24 24.78%            4
flashback log file sync                            14999              0       3.34 36.95%            9

15 rows selected.

SQL> select * from V$FLASHBACK_DATABASE_STAT;

BEGIN_TIME           END_TIME             FLASHBACK_DATA    DB_DATA  REDO_DATA ESTIMATED_FLASHBACK_SIZE
-------------------- -------------------- -------------- ---------- ---------- ------------------------
05-feb-2013 15:37:02 05-feb-2013 15:42:06      125911040  126820352  136450048                        0

Test 1 – CACHE lob

SQL> create table t_cache ( x1 clob, x2 clob, x3 clob )
  2    lob ( x1 ) store as ( cache )
  3    lob ( x2 ) store as ( cache )
  4    lob ( x3 ) store as ( cache )
  5    ;

SQL> set timing on
SQL> begin
  2  for i in 1 .. 5000 loop
  3    insert into t_cache values (rpad('x',4000,'x'),rpad('x',4000,'x'),rpad('x',4000,'x'));
  4    commit WRITE WAIT IMMEDIATE;
  5  end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:07.89

So for CACHE, the elapsed time is 8 seconds, and then we can inspect where the time was lost:

SQL> @sessev
Sid required: 17

EVENT                                        TOTAL_WAITS TOTAL_TIMEOUTS       SECS PCT        MAX_WAIT
-------------------------------------------- ----------- -------------- ---------- -------- ----------
Data file init write                                   1              0          0 00.00%            0
control file sequential read                          35              0          0 00.00%            0
control file parallel write                            7              0          0 00.00%            0
events in waitclass Other                              1              0          0 00.00%            0
flashback log file sync                                1              0          0 00.00%            0
CPU                                                                              0 00.00%            0
db file single write                                   1              0          0 00.00%            0
Disk file operations I/O                               8              0        .02 00.38%            1
log file switch completion                             1              0         .1 01.88%           10
buffer busy waits                                      1              0        .25 04.71%           25
log file switch (checkpoint incomplete)                2              0        .45 08.47%           45
log file sync                                       4998              0       1.24 23.35%            6
db file sequential read                             7136              0       1.24 23.35%            4
db file scattered read                              8415              0       2.01 37.85%            2

14 rows selected.

There is an increase in scattered read (ie, bringing blocks into the cache) but the flashback log sync wait has vanished.


SQL> select * from V$FLASHBACK_DATABASE_STAT;

BEGIN_TIME           END_TIME             FLASHBACK_DATA    DB_DATA  REDO_DATA ESTIMATED_FLASHBACK_SIZE
-------------------- -------------------- -------------- ---------- ---------- ------------------------
05-feb-2013 15:37:02 05-feb-2013 15:44:18      128581632  129851392  208777728                        0

Flashback log size growth is minimal (ie it was already 125MB from the previous test, and had grown to 128M). Presumably as the flashback process kicks in from time to time in background, this would be expected to grow, but it is no longer synchronous to the foreground process.