online

Hyper-partitioned index avoidance thingamajig

Connor McDonald , , , ,

As you can tell, I have no idea on a name for what I am about to describe. So let me start from the beginning, and set the scene for an idea I have to utilize a cool new 18c feature.

Often in a transactional-style system the busiest table (let us call it SALES for the sake of this discussion) is also

  • the biggest table, after all, it has all of our sales in it,
  • the most demanded for table, in that, almost every query in our application wants to access it in some way shape or form.

This is in effect the database version of the Pareto Principle. Everyone wants a slice of that SALES “pie”, and the piece of that pie that is in most demand is typically the most recent data. Your application may have pages that will be showing:

  • sales in the past hour,
  • sales in the past day,
  • products sold in the past hour,
  • largest purchase amount for sales in the past few hours,
  • suspicious activity for today,
  • A customer will want their sales for the last few days

The list goes on and on, the common theme being that the data being requested is bound by a range of time in the recent past. Our SALES table already will have a primary key, probably being some sort of unique transaction ID for each purchase, but to the satisfy the style of requests in the list above, we will probably need:

  • an index on the time(stamp) of the sale,
  • an index on the product ID that was sold,
  • an index on the customer ID who made the purchase,
  • potentially even an index on the sale amount

You can see the troubled waters into which we are sailing here. It is our biggest and busiest table, and here we are, adding index after index after index to improve query performance, whilst at the same time:

  1. adding overhead/contention to DML operations on the SALES table,
  2. increasing the size of the database,
  3. increasing the duration of the backups,
  4. increasing time for maintenance and copies to Development and Test

None of this is looking great but we might be thinking “What choice do we have?”

Here is perhaps an alternate strategy: Let’s not create any indexes.  The challenges (1) through (4) above evaporate to non-existence. But of course, now we have a remaining challenge in getting those queries to run efficiently.

I’ll tackle that in a different way – I will partition the SALES table very “aggressively”, hence my blog post title “hyper-partitioning”. Considering the typical query requirements I listed above, I will partition my SALES down to as small as a partition for every hour.


SQL> create table sales
  2    ( ts   timestamp,
  3      id   int,
  4      amt  number,
  5      product int,
  6      customer int,
  7      item_cnt int,
  8      terminal int,
  9      operator int,
 10      credit_card int,
         ...
         ...
 17    )
 18  partition by range ( ts )
 19  interval ( numtodsinterval(1,'HOUR') )
 20  (
 21    partition p1 values less than ( timestamp '2018-07-01 00:00:00' )
 22  );

Table created.

SQL>
SQL> insert /*+ APPEND */ into sales (ts,id,amt,product,customer)
  2  select date '2018-07-01' + rownum / 240, rownum, dbms_random.value(1,100),
  3        dbms_random.value(1,100),dbms_random.value(1,100)
  4  from dual
  5  connect by level 
SQL> set serverout on
SQL> declare
  2    h varchar2(1000);
  3  begin
  4  for i in (
  5    select partition_name, high_value
  6    from user_tab_partitions
  7    where table_name = 'SALES'
  8    and   interval = 'YES'
  9    and   partition_name like 'SYS_P%'
 10    order by partition_position
 11  ) loop
 12    h := i.high_value;
 13    execute immediate 'select to_char('||h||'-0.01,''yyyymmdd_hh24'') from dual' into h;
 14    execute immediate 'alter table sales rename partition '||i.partition_name||' to p'||h;
 15  end loop;
 16  end;
 17  /

PL/SQL procedure successfully completed.

SQL>
SQL> col high_value format a60
SQL> select partition_name, high_value
  2  from user_tab_partitions
  3  where table_name = 'SALES'
  4  order by partition_position;

PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------------------------------------
P1                             TIMESTAMP' 2018-07-01 00:00:00'
P20180701_00                   TIMESTAMP' 2018-07-01 01:00:00'
P20180701_01                   TIMESTAMP' 2018-07-01 02:00:00'
P20180701_02                   TIMESTAMP' 2018-07-01 03:00:00'
P20180701_03                   TIMESTAMP' 2018-07-01 04:00:00'
P20180701_04                   TIMESTAMP' 2018-07-01 05:00:00'
P20180701_05                   TIMESTAMP' 2018-07-01 06:00:00'
P20180701_06                   TIMESTAMP' 2018-07-01 07:00:00'
P20180701_07                   TIMESTAMP' 2018-07-01 08:00:00'
P20180701_08                   TIMESTAMP' 2018-07-01 09:00:00'
P20180701_09                   TIMESTAMP' 2018-07-01 10:00:00'
P20180701_10                   TIMESTAMP' 2018-07-01 11:00:00'
P20180701_11                   TIMESTAMP' 2018-07-01 12:00:00'
P20180701_12                   TIMESTAMP' 2018-07-01 13:00:00'
P20180701_13                   TIMESTAMP' 2018-07-01 14:00:00'
P20180701_14                   TIMESTAMP' 2018-07-01 15:00:00'
P20180701_15                   TIMESTAMP' 2018-07-01 16:00:00'
P20180701_16                   TIMESTAMP' 2018-07-01 17:00:00'
P20180701_17                   TIMESTAMP' 2018-07-01 18:00:00'
P20180701_18                   TIMESTAMP' 2018-07-01 19:00:00'
P20180701_19                   TIMESTAMP' 2018-07-01 20:00:00'
P20180701_20                   TIMESTAMP' 2018-07-01 21:00:00'
P20180701_21                   TIMESTAMP' 2018-07-01 22:00:00'
P20180701_22                   TIMESTAMP' 2018-07-01 23:00:00'
P20180701_23                   TIMESTAMP' 2018-07-02 00:00:00'
P20180702_00                   TIMESTAMP' 2018-07-02 01:00:00'
P20180702_01                   TIMESTAMP' 2018-07-02 02:00:00'
P20180702_02                   TIMESTAMP' 2018-07-02 03:00:00'
P20180702_03                   TIMESTAMP' 2018-07-02 04:00:00'
P20180702_04                   TIMESTAMP' 2018-07-02 05:00:00'
P20180702_05                   TIMESTAMP' 2018-07-02 06:00:00'
P20180702_06                   TIMESTAMP' 2018-07-02 07:00:00'
P20180702_07                   TIMESTAMP' 2018-07-02 08:00:00'
P20180702_08                   TIMESTAMP' 2018-07-02 09:00:00'
P20180702_09                   TIMESTAMP' 2018-07-02 10:00:00'
P20180702_10                   TIMESTAMP' 2018-07-02 11:00:00'
P20180702_11                   TIMESTAMP' 2018-07-02 12:00:00'
P20180702_12                   TIMESTAMP' 2018-07-02 13:00:00'
P20180702_13                   TIMESTAMP' 2018-07-02 14:00:00'
P20180702_14                   TIMESTAMP' 2018-07-02 15:00:00'
P20180702_15                   TIMESTAMP' 2018-07-02 16:00:00'

41 rows selected.

I’ve run a small anonymous block to rename the (system-named) interval partitions into some sensible names to reflect the date range the partition covers. Let’s now look at the typical queries we will now be performing on the SALES table:


SQL> set autotrace traceonly explain
SQL> select * from sales
  2  where ts > timestamp '2018-07-02 15:00:00';

--------------------------------------------------------------------------------------------------
| Id  | Operation                | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |       |     9 |   387 |     7   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ITERATOR|       |     9 |   387 |     7   (0)| 00:00:01 |    41 |1048575|
|*  2 |   TABLE ACCESS FULL      | SALES |     9 |   387 |     7   (0)| 00:00:01 |    41 |1048575|
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("TS">TIMESTAMP' 2018-07-02 15:00:00.000000000')

SQL> select * from sales
  2  where ts > timestamp '2018-07-02 15:00:00'
  3  and product = 12;

--------------------------------------------------------------------------------------------------
| Id  | Operation                | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |       |     1 |    43 |     7   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ITERATOR|       |     1 |    43 |     7   (0)| 00:00:01 |    41 |1048575|
|*  2 |   TABLE ACCESS FULL      | SALES |     1 |    43 |     7   (0)| 00:00:01 |    41 |1048575|
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("PRODUCT"=12 AND "TS">TIMESTAMP' 2018-07-02 15:00:00.000000000')

SQL>
SQL> select * from sales
  2  where ts > timestamp '2018-07-02 15:00:00'
  3  and customer = 25;

--------------------------------------------------------------------------------------------------
| Id  | Operation                | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |       |     1 |    43 |     7   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ITERATOR|       |     1 |    43 |     7   (0)| 00:00:01 |    41 |1048575|
|*  2 |   TABLE ACCESS FULL      | SALES |     1 |    43 |     7   (0)| 00:00:01 |    41 |1048575|
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("CUSTOMER"=25 AND "TS">TIMESTAMP' 2018-07-02 15:00:00.000000000')

SQL>
SQL> select max(amt) from sales
  2  where ts > timestamp '2018-07-02 15:00:00';

---------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |       |     1 |    33 |     7   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE           |       |     1 |    33 |            |          |       |       |
|   2 |   PARTITION RANGE ITERATOR|       |     9 |   297 |     7   (0)| 00:00:01 |    41 |1048575|
|*  3 |    TABLE ACCESS FULL      | SALES |     9 |   297 |     7   (0)| 00:00:01 |    41 |1048575|
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("TS">TIMESTAMP' 2018-07-02 15:00:00.000000000')

SQL> set autotrace off

All of them scan a tiny portion of the data, namely, just the hours of sales data relevant to the query, and the query response times will be relatively consistent for all cases no matter which customer, product or other predicate will be passed because the data to be scanned is a fixed number of hours.

But there’s a problem here. If I am partitioning to the hour, or even to the minute…then it won’t be long before I have a lot of partitions. In the latter case (minutes) I will be up to over 500,000 partitions in the first year of SALES alone! That is a lot of database metadata to store. There is the partitions themselves, plus optimizer statistics on them, plus historical optimizer statistics, plus potentially histograms on every column.  A lot of optimizer data might lead to expensive parse times because there is just so much information to wade through when optimizing queries.

But we only need the extreme granularity of partitions for the SALES table for today. Once today ticks over and becomes “yesterday”, then we might only need a partition for the entire day.  And once “yesterday” ticks over to “last week”, then maybe only weekly partitions are needed and so forth.

One of the cool things in 18c is ability to do this style of maintenance with negligible disruption to service. Because I have named my partitions in a logical fashion, here is a simple routine to merge “yesterdays” hourly partitions into a single one for the day.


SQL> set serverout on
SQL> declare
  2    d date := date '2018-07-01';
  3    ddl varchar2(4000);
  4  begin
  5    select listagg(partition_name||chr(10),',') within group ( order by partition_position )
  6    into   ddl
  7    from   user_tab_partitions
  8    where  table_name = 'SALES'
  9    and    partition_name like 'P'||to_char(d,'yyyymmdd')||'%';
 10
 11    ddl := 'alter table sales merge partitions '||ddl||' into partition p'||to_char(d,'yyyymmdd')||' online';
 12
 13    dbms_output.put_line(ddl);
 14    execute immediate ddl;
 15  end;
 16  /
alter table sales merge partitions
 P20180701_00
,P20180701_01
,P20180701_02
,P20180701_03
,P20180701_04
,P20180701_05
,P20180701_06
,P20180701_07
,P20180701_08
,P20180701_09
,P20180701_10
,P20180701_11
,P20180701_12
,P20180701_13
,P20180701_14
,P20180701_15
,P20180701_16
,P20180701_17
,P20180701_18
,P20180701_19
,P20180701_20
,P20180701_21
,P20180701_22
,P20180701_23
 into partition p20180701 online

PL/SQL procedure successfully completed.

Now I have a single partition for yesterday’s data, and hourly partitions for today’s data.


SQL> col high_value format a60
SQL> select partition_name, high_value
  2  from user_tab_partitions
  3  where table_name = 'SALES'
  4  order by partition_position;

PARTITION_NAME                 HIGH_VALUE
------------------------------ -------------------------------
P1                             TIMESTAMP' 2018-07-01 00:00:00'
P20180701                      TIMESTAMP' 2018-07-02 00:00:00'
P20180702_00                   TIMESTAMP' 2018-07-02 01:00:00'
P20180702_01                   TIMESTAMP' 2018-07-02 02:00:00'
P20180702_02                   TIMESTAMP' 2018-07-02 03:00:00'
P20180702_03                   TIMESTAMP' 2018-07-02 04:00:00'
P20180702_04                   TIMESTAMP' 2018-07-02 05:00:00'
P20180702_05                   TIMESTAMP' 2018-07-02 06:00:00'
P20180702_06                   TIMESTAMP' 2018-07-02 07:00:00'
P20180702_07                   TIMESTAMP' 2018-07-02 08:00:00'
P20180702_08                   TIMESTAMP' 2018-07-02 09:00:00'
P20180702_09                   TIMESTAMP' 2018-07-02 10:00:00'
P20180702_10                   TIMESTAMP' 2018-07-02 11:00:00'
P20180702_11                   TIMESTAMP' 2018-07-02 12:00:00'
P20180702_12                   TIMESTAMP' 2018-07-02 13:00:00'
P20180702_13                   TIMESTAMP' 2018-07-02 14:00:00'
P20180702_14                   TIMESTAMP' 2018-07-02 15:00:00'
P20180702_15                   TIMESTAMP' 2018-07-02 16:00:00'

18 rows selected.

I stress – this is not my recommendation to race out and partition every transactional table you have, and drop all of the indexes Smile. But it with so many online partitioning operations in 18c, it raises some exciting new opportunities there were not available in previous releases. So start thinking about how you can exploit this to get advantages with the partitioning option that might sit “outside the box” of the standard usage.

12c Release 2 – Transparent Data Encryption online !

Connor McDonald , , , ,

It doesn’t take a rocket scientist to know that even if you have incredibly stringent controls on user authentication, user authorisation etc, that wont save you if your data on disk is not encrypted.  All you need is an errant tape, a missing disk, a misplaced flash stick…and kersplat, someone has a copy of your datafiles from your Oracle database.

Data at rest should be encrypted, but that often meant taking applications offline to do so.

I’ve put my Speed Racer hat on Smile and here’s a video on a new 12c Release 2 feature covered in 60 seconds !

A 12.2 treat for the festive season

Connor McDonald , , , ,

We’re being asked to store more and more data, yet keep backup windows, query performance and the like unchanged, no matter how much we store. As a result, more and more database shops are needing to partition their data. The problem is – partitioning data is a significant restructure of the data, which thus incurs a large outage and the accompanying planning and coordination.

Unless you’re on 12.2.

Here’s a demo where we can take an existing table and

  • convert it to partitioned on a range scheme, also using intervals so we don’t need to worry about ongoing range creation maintenance, and
  • take one of the existing indexes, and convert it to a local index to mirror the table partitioning scheme, and
  • take the other existing index, and globally partition it.

Oh…and of course, we will do the whole thing online without service disruption

Oh…and of course, we will do it with a single SQL command.

Ab…So….Lute….Ly awesome !



SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 22 09:53:37 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Mon Dec 19 2016 13:38:54 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.0.3 - 64bit Production


SQL> create table T as
  2  select d.*
  3  from dba_Objects d,
  4       ( select 1 from dual
  5         connect by level <= 20 )   6  where d.object_id is not null; Table created. SQL> create index IX on t ( object_id );

Index created.

SQL> create index IX2 on t ( created, object_name );

Index created.

SQL> alter table T modify
  2  partition by range (object_id) interval (10000)
  3  (
  4    partition p1 values less than (20000)
  5  ) online
  6  update indexes
  7  ( ix  local,
  8    ix2 global partition by range (created)
  9    (
 10     partition ix2_p1 values less than (date '2016-08-01'),
 11     partition ix2_p2 values less than (maxvalue)
 12   )
 13  );

Table altered.

SQL> select partition_name
  2  from   user_tab_partitions
  3  where  table_name = 'T';

PARTITION_NAME
--------------------------------------------------------------------------------------------------------------------------------
P1
SYS_P1145
SYS_P1146
SYS_P1147
SYS_P1148
SYS_P1149
SYS_P1150

7 rows selected.

SQL> select index_name, partition_name
  2  from   user_ind_partitions
  3  where  index_name like 'IX%';

INDEX_NAME                     PARTITION_NAME
------------------------------ ------------------------------
IX                             P1
IX                             SYS_P1145
IX                             SYS_P1146
IX                             SYS_P1147
IX                             SYS_P1148
IX                             SYS_P1149
IX                             SYS_P1150
IX2                            IX2_P1
IX2                            IX2_P2

9 rows selected.

SQL>

Continuous Delivery – Moving to SECUREFILE

Connor McDonald ,

You’ve been google-ing and you’ve seen articles (for example) like http://www.oracle.com/technetwork/articles/sql/11g-securefiles-084075.html and you’re pretty pumped about using SECUREFILE features. You’ve got lots of existing CLOB data and moving to SECUREFILE is going to make your life much easier. You’re also excited about the fact that none of your code has to change – you just change the existing CLOB columns to be stored as SECUREFILE and you’ll have set yourself up for all sorts of feature goodness !

But how do we do it in a continuous delivery (CD) model ? Because moving CLOB’s sounds like downtime doesn’t it ?

And by default, that’s exactly what it will be. Let’s explore that with a faux application that uses CLOB’s.

We’ll create an “application” which consists of a table, a sequence, a trigger* to populate the sequence, and a procedure which is our application interface.

(*- yes, in 12c we can skip the trigger and just use the new DEFAULT facilities available, but I’m keeping the trigger because that’s a very common occurrence in the industry as people move to 12c)

SQL> create table T1 ( x int, y clob, t timestamp default systimestamp, constraint T1_PK primary key (x) ) ;

Table created.

SQL> create sequence T1_SEQ;

Sequence created.

SQL> create or replace trigger T1_TRG
  2  before insert on T1
  3  for each row
  4  begin
  5    :new.x := t1_seq.nextval;
  6  end;
  7  /

Trigger created.

SQL> create or replace
  2  procedure T1_LOGGER is
  3    l_long varchar2(32767) := rpad('x',32000,'x');
  4  begin
  5    for i in 1 .. 1000 loop
  6      insert into t1 (y ) values (l_long);
  7      commit;
  8      dbms_lock.sleep(0.1);
  9    end loop;
 10  end;
 11  /

Procedure created.

And there we have our application. The T1_LOGGER application will insert approximately 10 rows per second, each one containing a 32kilobyte CLOB. We’ll start our application running:

SQL> --
SQL> -- This represents your app, busily inserting clobs
SQL> --
SQL> exec t1_logger;

And now here is where our continuous delivery comes in. I want to change to using SECUREFILES (and backdate the existing CLOBS) without interrupting user services. So I login to a second session and issue:

SQL> alter table T1 move lob ( y) store as securefile;

Table altered.
Elapsed: 00:00:46.11

Well that all looks fine, until I return to the session where my application is running

SQL> exec t1_logger;
BEGIN t1_logger; END;

*
ERROR at line 1:
ORA-01502: index 'SCOTT.T1_PK' or partition of such index is in unusable state
ORA-06512: at "SCOTT.T1_LOGGER", line 5
ORA-06512: at line 1

Ker-splat! I’ve broken my app, and its dead until I rebuild that index, which could be hours. Time to update mv CV 🙂 And what’s worse is, even if our application had not had a unique index, then we still created all sort of problems. Notice that our “alter table move” took 46 seconds. If we look at the insertion timestamps for our lobs, we see:

SQL> select max(t) from t1;

MAX(T)
---------------------------------------------------------------------------
03-JUL-15 10.26.04.644000 AM

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
03-JUL-15 10.26.51.748000 AM +08:00

We ended up with a “freeze” of 46 seconds in our application whilst the table was locked as it was moved. If this had been (say) a service-based interface, there’s a very good chance that our services would have reported timeout’s and all sort of other errors back to the calling environment.

So we take a squizz around the Oracle documentation and find this:

blog_dbms_redef

The “ONLINE” option looks promising. Let’s give that a whirl…

SQL> alter table T1 move lob ( y) store as securefile online;
alter table T1 move lob ( y) store as securefile online
                                                 *
ERROR at line 1:
ORA-00906: missing left parenthesis


SQL> alter table T1 move online lob ( y) store as securefile;
alter table T1 move online lob ( y) store as securefile
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option

We don’t get any joy there, since a closer look at the documentation tells us that the ONLINE option is for index-organised tables, so it doesn’t apply here.

So it seems like we’re stuck….Enter DBMS_REDEFINITION. This is a very cool utility for redefining tables without making them inaccessible to calling applications. Let’s go through the same process, this time using DBMS_REDEFINITION. We will build and run our application from scratch

SQL> create sequence T1_SEQ;

Sequence created.

SQL> create or replace trigger T1_TRG
  2  before insert on T1
  3  for each row
  4  begin
  5    :new.x := t1_seq.nextval;
  6  end;
  7  /

Trigger created.

SQL> create or replace
  2  procedure T1_logger is
  3    l_long varchar2(32767) := rpad('x',32000,'x');
  4  begin
  5    for i in 1 .. 1000 loop
  6      insert into t1 (y ) values (l_long);
  7      commit;
  8      dbms_lock.sleep(0.1);
  9    end loop;
 10  end;
 11  /

Procedure created.

SQL> --
SQL> -- This represents your app, busily inserting clobs
SQL> --
SQL> exec t1_logger;

Now we create a second session, whilst our application is running, and redefine our table online using a template tale to inform the database what structure we want.

SQL> create table T2 ( x int , y clob, t timestamp default systimestamp  ) lob ( y) store as securefile;

Table created.

SQL> EXEC DBMS_REDEFINITION.can_redef_table(user, 'T1');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_REDEFINITION.start_redef_table(user, 'T1', 'T2');

PL/SQL procedure successfully completed.

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2    l_num_errors PLS_INTEGER;
  3  BEGIN
  4    DBMS_REDEFINITION.copy_table_dependents(
  5      uname             => user,
  6      orig_table        => 'T1',
  7      int_table         => 'T2',
  8      copy_indexes      => DBMS_REDEFINITION.cons_orig_params,
  9      copy_triggers     => TRUE,  -- Default
 10      copy_constraints  => TRUE,  -- Default
 11      copy_privileges   => TRUE,  -- Default
 12      ignore_errors     => FALSE, -- Default
 13      num_errors        => l_num_errors);
 14    DBMS_OUTPUT.put_line('l_num_errors=' || l_num_errors);
 15  END;
 16  /
l_num_errors=0

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_REDEFINITION.sync_interim_table(user, 'T1', 'T2');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_REDEFINITION.finish_redef_table(user, 'T1', 'T2');

PL/SQL procedure successfully completed.

And we’done. We flick back to our original application session and check for errors:

SQL> exec t1_logger;

PL/SQL procedure successfully completed.

It ran to completion without any problems. We have moved to SECUREFILE without any disruption to service. Very cool!

But although nothing crashed, maybe there was a blockage of service to the database? We can check that by locking at the maximum time between insertions in our application.

SQL> select max(delta)
  2  from
  3   ( select t - lag(t) over ( order by t) as delta from t1 );

MAX(DELTA)
---------------------------------------------------------------------------
+000000000 00:00:00.250000

We were inserting rows every 0.10 seconds, so 0.25 represents a tiny overhead and unlikely to be noticed.

So when you need to make small structural changes to your database tables, have a think about DBMS_REDEFINITION. In a future blog post, I’ll talk about why you might want to redefine your tables even when you are not changing their structure at all !

Continuous delivery…

Connor McDonald ,

“Continuous Delivery (CD) is a software engineering approach in which teams keep producing valuable software in short cycles and ensure that the software can be reliably released at any time”

(Source: https://en.wikipedia.org/wiki/Continuous_delivery)

Perhaps a simpler definition is “CD is the currently the cool thing to do” Smile

Sarcasm aside, there’s a lot of common sense in being able to rapidly push out software changes in a safe manner.

Many years ago, I was a developer at a company that was drowning in bureaucracy, and I was tasked with trying to solve a suite of performance problems with part of the core business application.  The first thing I did (and I’d recommend this to anyone trying to assist in solving performance problems) was to visit the end-users who actually use the software.  (It’s too easy to jump in and start tracing SQL statements etc…but the pain points you are trying to solve are the customer’s pain points, not the servers)

She sat down and ran me through the litany Sad smile of performance problems she was having.  I tried to set some realistic expectations for her about when we could solve them, but I also asked:

“If there is one thing that is absolutely top of the list, what would it be, and I’ll focus on that”

Interestingly, when phrased that way, she pondered for a moment and told me it was not a performance issue.  Although the performance was terrible, she (embarrassingly for our IT dept) had re-structured her daily activities to accommodate the slow parts of the system.  (“I run the daily report whilst I’m at morning tea, and its usually done by time I get back”).  No, she had a much simpler request:

“We have smaller screens in the field office, so you have to scroll the screen every time to get to the ‘Submit’ button. Can you move it to the top of screen?”

“Leave it with me!” I exclaimed.  “This is simple, and we’ll get it to you asap”

So I was feeling pretty good about myself – I’d identified the important performance issues, bought myself some time to work on them, and had a simple fix to appease the customer in the interim.  I got back to the office, checked out the source code, move the button a few inches higher and voila! I’m done.

….Or so I thought.

I wont share the gory details, but it took over 6 months to get that change through all of the processes, environments, approvals, release cycles, etc and finally deliver it into Production. I was so ashamed that I’d let this customer down so badly.  And it strips away at your job satisfaction as a developer – nothing makes you feel more foolish than sitting in front of a “Change Approval Committee” meeting, and you’re justifying the business benefit of a change you coded 6 months ago, where a button was moved.  A total waste of everyone’s time.  But … after all that red tape, it had finally gone in.

My manager called me into the office after deployment:

“Well, your change is in!  You should phone the customer, tell her we’ve done her changes, and make sure she’s happy with it”

I refused.

Can you imagine it ? “Hi, remember me, its been six months…that button is 2 inches higher. Are you impressed ?”

Anyway…enough back story, this sets the scene for my next blog post…An simple example of CD in the database.