Joining Oracle

Last month I joined Oracle after nearly 20 years of working with their technology.  Some people congratulated me on my new role, others told me that going from working solo to working for a massive organisation would be disaster.  In the latter case, this was often associated with an impassioned “But why?”

To be honest, I found that a little discourteous – its an assumption that to work for a multinational is to become “part of the problem”.  In my career, I’ve worked for several large organisations (Fujitsu and BHP to name a couple).  I’ve always been proud of the accomplishments with those companies, never regretful.

But the best way to answer the question I figured, was to speak directly to it.

So here is why I joined Oracle …  I hope you enjoy and subscribe, because I’ll be publishing a lot more content (of a more technical nature) on my new channel.

https://youtu.be/PtGDlSKmsBw

Things that are there but you cannot use

I did a "desc" command on the STANDARD package today, the package that helps define PL/SQL, and saw the XOR function!

SQL> declare
  2    x boolean;
  3  begin
  4    x := XOR(true,true);
  5    dbms_output.put_line(case when x then 'TRUE' else 'FALSE' end);
  6
  7    x := XOR(true,false);
  8    dbms_output.put_line(case when x then 'TRUE' else 'FALSE' end);
  9
 10    x := XOR(false,false);
 11    dbms_output.put_line(case when x then 'TRUE' else 'FALSE' end);
 12
 13  end;
 14  /
FALSE
TRUE
FALSE

PL/SQL procedure successfully completed.

I cant find any reference to it in the Oracle PL/SQL documentation anywhere, so its definitely not supported so using it is probably off limits until you see officially in the documentation.

Continuous Delivery – Moving to SECUREFILE

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…

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

Partial uniqueness

I had an interesting request recently from a developer.

“ I have a table created as per below

create table C_TEST (
  col_1 varchar2(3),
  col_2 varchar2(3),
  col_3 number
  );

The rows defined by col_1, col_2, col_3 must be unique but only when col_3 is present.  If col_3 is not present, then we allow anything.  Hence if the table is populated like this:

begin
insert into c_test values (‘a’,’b’,null);
insert into c_test values (‘a’,’b’,1);
insert into c_test values (‘a’,’b’,2);
insert into c_test values (‘a’,’c’,1);
insert into c_test values (‘b’,null,1);
insert into c_test values (‘c’,null,null);
insert into c_test values (null,null,null);
insert into c_test values (null,null,1);
insert into c_test values (null,null,2);
end;
/

— then all of the following should fail

insert into c_test values (‘a’,’b’,1);
insert into c_test values (‘b’,null,1);
insert into c_test values (null,null,1);

 

My first thought there is something perhaps out of whack with the design, but who am I to say.

Anyway, we can take advantage of the fact that if entirity of an index key is null, then no value is stored in the index (for a B-tree index).  Hence the following definition should satisfy the need:

create unique index c_test_ix on c_test
 (case when col_3 is not null then col_1 end,
  case when col_3 is not null then col_2 end,
  case when col_3 is not null then col_3 end
  );

The CASE statements effectively only bring columns col_1, col_2 and col_3 into play when col_3 is provided.

Oracle Developer awards…chosen by Developers

I was reading the following post today http://stevenfeuersteinonplsql.blogspot.com.au/2015/06/the-oracle-database-developer-choice.html

Oracle are planning on rewarding developers in the following areas:

  • SQL
  • PL/SQL
  • Oracle REST Data Services
  • Oracle Application Express
  • Database Design

 

At first glance I had a bit of a cynical view…it could easily be one of those things where if you come from a company that has massive investment in Oracle, then surprise surprise you float to the top of the heap. 

But this part of the post caught my eye:

Nominations come from the community (you).
Panels of ACEs decide on a set of finalists.
Winners are determined by popular vote (that is, by you).

I think that appeals much more to the developer community.  We all know those developers in our midst who are either standouts with their knowledge, or just standouts because they’re so keen to help us when we hit a problem.  This is a nice way to recognise them, especially those that fall into the latter category – those developers that contribute to our community without necessarily being “the best of the best”.

So check it out here and hopefully it will be the start of a nice tradition.

The technology community

I remember on a flight to the UKOUG, I was doing what all presenters typically do on a plane.  They enter the cabin with the thought of "OK, I’ll spend most of the flight getting those slides just right".  Then…a set of broadcast advertisements, safety messages, hot face towels, exit row briefings, beverage services, coffee services, and before you know it you’ve burned 2 hours without touching the laptop…and then the meal service starts :-)

Anyway, I digress.  I eventually got the laptop fired up and started flicking through my slides for the 800th time – I suffer from that silliness where if I’m thinking "Hmmm, if replace with ‘database’ with ’12c database’", then this somehow will make a significant improvement for the attendees.  After a while the laptop gives me a little ‘beep’ telling me that battery is low.

I close the lid, and reach into the laptop bag for the power supply….and then the realisation hits me. 

No….power….cable.

Uh oh….I’m heading to UKOUG, with all my content on laptop which is running at 10% battery and I’ve got nothing to charge it with.  Whilst everyone around me are in that state of languor associated with long haul flights, my heart rate has hit 200, the adrenalin has gone from a trickle to a flood, and the sweat beads are forming on my brow.

A hostess stops as she passes by… "It’s OK sir, just a couple of little bumps.  No need to be nervous.  Flying is the safest form of air travel", and gives me a pat on the hand.  It very nearly wasn’t a safe form of travel for her, as at that point I wanted to swat her with my laptop…but I figured that might reduce its charge even further.

6 hours later we land in Birmingham, and even on sleep mode, the laptop has ceased to be.  So I take a taxi to an internet cafe and send out a call for help on some Oracle discussion forums:

 

"In UK, with a 6 year old Dell laptop, no power cable…HELP!"

 

And what happened next changed a nightmare start to a conference, to being an incredibly uplifting one.

Emails came firing back, all being incredible keenness to assist:

 

(from a fellow presenter who I knew)
"I have a universal adapter, and I’m not presenting on Sunday."

(from a fellow presenter who I’d never met)
"I might have one that fits.  What hotel you in, I’ll drop it over to you."

(from a local attendee)
"I’ve called PC-World, they have one that should work – here’s the address"

 

Isn’t that just amazing.

Yes, we all share a technology (Oracle). And we all love it some days, and other days we hate it.  But by and large, its still a group of relative strangers being happy to reach out and assist.  In the end, I got a taxi and PC-world and got an adapter that did the job.  But the importance of community in our technology arena was the lasting lesson from this experience.  Whether it be user groups, conferences, your working departments, communal activities such as the PL/SQL Challenge website, or OTN Community forums, just keep remembering the mantra…

We’re all in this together

 

I’ve recently joined Oracle and a number of friends and colleagues phrased the transition to me as “being on the other side of the fence” or “wrong side of the train track” etc etc.  I find that a little sad – the theory that who you work for dictates the amount that you can contribute to a technology community.  I’m aiming to contribute more rather than less.  And another cool thing with a community, is that if I’m not contributing – they’ll call me out on it.  That way, we all develop.