Learning is not a spectator sport

July 3, 2015

Continuous Delivery – Moving to SECUREFILE

Filed under: Uncategorized — connormcdonald @ 3:11 pm

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…

Filed under: Uncategorized — connormcdonald @ 10:54 am

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

July 2, 2015

Partial uniqueness

Filed under: Uncategorized — connormcdonald @ 5:14 pm

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

Filed under: Uncategorized — connormcdonald @ 4:56 pm

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.

June 27, 2015

The technology community

Filed under: Uncategorized — connormcdonald @ 10:55 am

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.

June 24, 2015

Upgraded and can’t see the tables you could before ?

Filed under: Uncategorized — connormcdonald @ 9:41 pm

If you take a look at the "alter user" command in the old 9i documentation, you’ll see this:

DEFAULT ROLE Clause

Specify the roles granted by default to the user at logon. This clause can contain only roles that have been granted directly to the user with a GRANT statement. You cannot use the DEFAULT ROLE clause to enable:

  • Roles not granted to the user
  • Roles granted through other roles
  • Roles managed by an external service (such as the operating system), or by the Oracle Internet Directory

Oracle enables default roles at logon without requiring the user to specify their passwords.

And if you do a similar examination of the 10g documentation, its pretty much the same, with some more elaboration.

DEFAULT ROLE Clause

Specify the roles granted by default to the user at logon. This clause can contain only roles that have been granted directly to the user with a GRANT statement. You cannot use the DEFAULT ROLE clause to enable:

  • Roles not granted to the user

  • Roles granted through other roles

  • Roles managed by an external service (such as the operating system), or by the Oracle Internet Directory

Oracle Database enables default roles at logon without requiring the user to specify their passwords or otherwise be authenticated. If you have granted an application role to the user, you should use the DEFAULT ROLE ALL EXCEPT role clause to ensure that, in subsequent logons by the user, the role will not be enabled except by applications using the authorized package.

So lets take a look at the 11.2 documentation for the same section

DEFAULT ROLE Clause

Specify the roles granted by default to the user at logon. This clause can contain only roles that have been granted directly to the user with a GRANT statement, or roles created by the user with the CREATE ROLE privilege. You cannot use the DEFAULT ROLE clause to specify:

  • Roles not granted to the user

  • Roles granted through other roles

  • Roles managed by an external service (such as the operating system), or by the Oracle Internet Directory

  • Roles that are enabled by the SET ROLE statement, such as password-authenticated roles and secure application roles

Notice the new 4th bullet point, which has replaced the handling of default roles from previous versions.  If a role is password protected, then even if it is set as a default role, then it will not be enabled automatically.  We can see this with a simple example:

SQL> conn / as sysdba

Connected.

SQL> create user NO_PRIVS identified by blah;

User created.

SQL> grant create session to NO_PRIVS;

Grant succeeded.

SQL> create role SUPER_DOOPER identified by SECRET_PASSWORD;

Role created.

SQL> create role DOES_NOTHING;

Role created.

SQL> grant delete any table, select any table to SUPER_DOOPER;

Grant succeeded.

SQL> grant SUPER_DOOPER  to NO_PRIVS;

Grant succeeded.

SQL> grant DOES_NOTHING to NO_PRIVS;

Grant succeeded.

SQL> alter user NO_PRIVS default role all;

User altered.

SQL> conn NO_PRIVS/blah

Connected.

SQL> select count(*) from hr.employees;

select count(*) from hr.employees

                        *

ERROR at line 1:

ORA-00942: table or view does not exist

SQL>

SQL> set role SUPER_DOOPER identified by SECRET_PASSWORD;

Role set.

SQL> select count(*) from hr.employees;

  COUNT(*)

----------

       107

SQL>

June 23, 2015

You never stop learning

Filed under: Uncategorized — connormcdonald @ 11:10 am

My mate Scott Wesley, whose specialty is Apex, is currently at Kscope having the time of his life (well, I hope so Smile).  He tweeted this picture last night of his conference badge, mainly about the “I love Apex” buttons, but something else struck me.

scott_CIHbrDpWIAA1-Cc

 

Take a look at the last two ribbons attached to his badge.  I’ve known Scott for many years, and was even fortunate enough be a  mentor of his for awhile.  He’s a sharp dude, and is probably one of the best Apex resources locally in Australia and abroad.  This is why you see the deserved Oracle Ace designation.  But its his first Kscope, so he also gets the “newbie” ribbon. That reinforces the fact that no matter how much experience you have, no matter how much knowledge you have, there are always new things to learn, new things to explore, to continually expand your horizons as an Oracle professional.

So … try to keep a virtual badge on a virtual lanyard around you’re neck as you go about your working day.  It should always contain a “newbie” ribbon – so you’re always finding new challenges.

June 15, 2015

A new challenge…

Filed under: Uncategorized — connormcdonald @ 1:26 pm

After the last 16 years in the contracting and consulting world, for dozens of clients in Australia and other countries around the world, its time for a change of direction.  Whilst doing work with Oracle systems has always been (and continues to be) rewarding, I realised that a lot of the motivation for being at client sites was the sharing of information with developers and DBA’s at those clients so they could get the most out of their Oracle investment.  Similarly, its really cool to speak at conferences to let people explore the Oracle technology in perhaps way they had not thought of, and similarly, blogging about features and idiosyncracies that we stumble upon.  Whatever the medium (and even whatever the technology, whether it be Oracle or otherwise), the concept of an “IT community”, ie, people being keen to share, debate, and discuss has always been one of the things I like about my profession.

So with that in mind…I’ve taken a new role with Oracle Corporation which hopefully will extend this even further.  I’ll be part of the Developer Advocate group, with the aim of helping people get the most out of their Oracle investment, in particular, showing how resilient and powerful the SQL language continues to be.  And hopefully we’ll also attract people unfamiliar with SQL to embrace it rather than fear it Smile

June 13, 2015

Optimizer curiosity in 12.1.0.2

Filed under: Uncategorized — connormcdonald @ 2:19 pm

For almost as long as I can remember, the optimizer has had a nifty little trick when you (in effect) try to combine two different usage models within a single SQL.  To explain that, I’m referring to the common scenario of: "If a bind value is provided by the caller, then use it, otherwise it shouldn’t limit the result set.  So we commonly see queries like:

select *
from MY_TABLE
where COL1 = NVL(:mybindvar, COL1)

[For ease of discussion, we’ll assume COL1 is not nullable]

Anyway, the nice little optimizer trick was to optimize the query to handle the two separate use cases, so you see a CONCATENATION step in the execution plan, and two FILTER’s, one to handle the case when the bind variable is null, and one to handle the case where it is provided. 

-----------------------------------------------
| Id  | Operation                             |
-----------------------------------------------
|   0 | SELECT STATEMENT                      |
|   1 |  CONCATENATION                        |
|*  2 |   FILTER                              |
            <path1>
|*  5 |   FILTER                              |
            <path2>
-----------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(:B1 IS NULL)
   5 - filter(:B1 IS NOT NULL)

In effect, the optimizer has taken care of the old SQL tuning advice we used to give to the developers to rewrite the SQL as a UNION ALL, or even split it into two distinct SQL statements to cater for each case.  With that in mind, I picked up a change to this behaviour in 12.1.0.2 (and some additional work by Jonathan Lewis suggests 11.2.0.4 as well), where bind peeking seems to create some confusion. 

Let’s look at an example.  I’ll create a table, populate it with approx 4million rows, where two cols are of interest:

  • SEQ, 2m distinct values and hence highly selective
  • PSEQ, only 2 distinct values, and hence not a great option for an index path

The primary key is the composite of these two columns, and a secondary index on PSEQ

SQL> create table T (
  2    seq int, pseq int, blah char(30),
  3    constraint T_PK primary key (seq,pseq)
  4  )
  5  /

Table created.

SQL> insert into T
  2  select trunc(rownum/2) seq,
  3         mod(rownum,2) pseq,
  4         'x' blah
  5  from
  6    ( select 1 from dual connect by level < 1000 ),
  7    ( select 1 from dual connect by level < 4000 )
  8  /

3995001 rows created.

SQL> create index T_IX2 on T ( pseq );

Index created.

SQL> exec dbms_stats.gather_table_stats('','T',no_invalidate=>false);

PL/SQL procedure successfully completed.

Now we’ll execute an SQL in the form previously mentioned, and take the case where the bind variable in the NVL is null.

SQL> variable b1 number
SQL> variable b2 number
SQL> exec :b1 := null;
SQL> exec :b2 := 1706496;

SQL> select /*+ gather_plan_statistics */ *
  2  from T
  3  WHERE seq = :B2
  4  AND pseq = NVL(:B1 ,pseq)
  5  /

       SEQ       PSEQ BLAH
---------- ---------- ------------------------------
   1706496          0 x
   1706496          1 x

SQL>
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  0caq50rvfkub5, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from T WHERE seq = :B2 AND pseq
= NVL(:B1 ,pseq)

Plan hash value: 3837764478

---------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |       |      1 |        |      2 |00:00:00.01 |       6 |
|   1 |  CONCATENATION                        |       |      1 |        |      2 |00:00:00.01 |       6 |
|*  2 |   FILTER                              |       |      1 |        |      2 |00:00:00.01 |       6 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T     |      1 |      2 |      2 |00:00:00.01 |       6 |
|*  4 |     INDEX RANGE SCAN                  | T_PK  |      1 |      2 |      2 |00:00:00.01 |       4 |
|*  5 |   FILTER                              |       |      1 |        |      0 |00:00:00.01 |       0 |
|*  6 |    TABLE ACCESS BY INDEX ROWID BATCHED| T     |      0 |      1 |      0 |00:00:00.01 |       0 |
|*  7 |     INDEX RANGE SCAN                  | T_IX2 |      0 |      1 |      0 |00:00:00.01 |       0 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(:B1 IS NULL)
   4 - access("SEQ"=:B2)
       filter("PSEQ" IS NOT NULL)
   5 - filter(:B1 IS NOT NULL)
   6 - filter("SEQ"=:B2)
   7 - access("PSEQ"=:B1)


30 rows selected.

Because :B1 is null, you can see from the Predicate Information, and from the Actual Rows information, that we took the execution path in lines 3-4.  But take a peek (no pun intended) at lines 6-7.  That path, whilst not used, is proposing the use of index T_IX2, which as we know is 2 distinct keys across 4 million rows.  The problem is … we’ve now loaded that execution plan into our library cache.  So let’s see what happens when we exercise that part of the plan when we this time specify both bind variables

SQL> exec :b1 := 1;

SQL> select /*+ gather_plan_statistics */ *
  2  from T
  3  WHERE seq = :B2
  4  AND pseq = NVL(:B1 ,pseq)
  5  /

       SEQ       PSEQ BLAH
---------- ---------- ------------------------------
   1706496          1 x

SQL>
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  0caq50rvfkub5, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from T WHERE seq = :B2 AND pseq
= NVL(:B1 ,pseq)

Plan hash value: 3837764478

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |       |      1 |        |      1 |00:00:00.70 |   28124 |    178 |
|   1 |  CONCATENATION                        |       |      1 |        |      1 |00:00:00.70 |   28124 |    178 |
|*  2 |   FILTER                              |       |      1 |        |      0 |00:00:00.01 |       0 |      0 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T     |      0 |      2 |      0 |00:00:00.01 |       0 |      0 |
|*  4 |     INDEX RANGE SCAN                  | T_PK  |      0 |      2 |      0 |00:00:00.01 |       0 |      0 |
|*  5 |   FILTER                              |       |      1 |        |      1 |00:00:00.70 |   28124 |    178 |
|*  6 |    TABLE ACCESS BY INDEX ROWID BATCHED| T     |      1 |      1 |      1 |00:00:00.70 |   28124 |    178 |
|*  7 |     INDEX RANGE SCAN                  | T_IX2 |      1 |      1 |   1997K|00:00:00.63 |    3898 |    178 |
------------------------------------------------------------------------------------------------------------------

Ouch…2000 million rows scanned in the index.  Since we provided both bind variables, the obvious access path should have been a simple primary key lookup.  But the path derived from our first execution (with :B1 being null) has left behind a "nasty legacy".

The troubling thing about this (in terms of application stability) is that the order in which we run queries now impacts the performance of how they run.  Lets clear out the cursor by recalculating stats and then reverse the order of execution.

SQL> exec dbms_stats.gather_table_stats('','T',no_invalidate=>false);

PL/SQL procedure successfully completed.

SQL> exec :b1 := 1;
SQL> exec :b2 := 1706496;

PL/SQL procedure successfully completed.

SQL> select /*+ gather_plan_statistics */ *
  2  from T
  3  WHERE seq = :B2
  4  AND pseq = NVL(:B1 ,pseq)
  5  /

       SEQ       PSEQ BLAH
---------- ---------- ------------------------------
   1706496          1 x

SQL>
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  0caq50rvfkub5, child number 1
-------------------------------------
select /*+ gather_plan_statistics */ * from T WHERE seq = :B2 AND pseq
= NVL(:B1 ,pseq)

Plan hash value: 933468988

--------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |      |      1 |        |      1 |00:00:00.01 |       4 |
|   1 |  CONCATENATION                        |      |      1 |        |      1 |00:00:00.01 |       4 |
|*  2 |   FILTER                              |      |      1 |        |      0 |00:00:00.01 |       0 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T    |      0 |      2 |      0 |00:00:00.01 |       0 |
|*  4 |     INDEX RANGE SCAN                  | T_PK |      0 |      2 |      0 |00:00:00.01 |       0 |
|*  5 |   FILTER                              |      |      1 |        |      1 |00:00:00.01 |       4 |
|   6 |    TABLE ACCESS BY INDEX ROWID        | T    |      1 |      1 |      1 |00:00:00.01 |       4 |
|*  7 |     INDEX UNIQUE SCAN                 | T_PK |      1 |      1 |      1 |00:00:00.01 |       3 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(:B1 IS NULL)
   4 - access("SEQ"=:B2)
       filter("PSEQ" IS NOT NULL)
   5 - filter(:B1 IS NOT NULL)
   7 - access("SEQ"=:B2 AND "PSEQ"=:B1)

When we optimize the query for the initial case of both bind variables provided, you can see that both sides of the CONCATENATION have yielded a sensible path.

June 6, 2015

Cross-platform database migration

Filed under: Uncategorized — connormcdonald @ 5:55 pm

Last weekend, we faced one of the larger challenges that we’ve faced in my time at my current client.  Migrate multiple database systems, across multiples sites, all to new hardware (with new endian format), new database version (12.1.0.2)…and of course, try to do it with as small a disruption to the service as possible.

We are not a Goldengate customer, so pursuing a zero-downtime migration was not considered, and to be honest, even we had such facilities at our disposal, I think we would still have taken a small outage just to allow a "quiet time" to do verification checks etc.

I had done a similar exercise many years ago, with a client moving from 9i to 10g.  In those days, migration with low downtime was a massively complex affair.  I remember countless SQL and shell scripts, all designed to do as much work concurrently as possible (multiple physical networks, multiple concurrent jobs pulling tables over db links, concurrent index builds, etc etc etc), all designed to keep that outage time to a minimum.  High risk days indeed.

In contrast, using the cross-platform transportable tablespaces, this current migration ran very smoothly indeed.  Below is a rough guide as to what is needed to perform such a migration – I didnt want to list precisely our steps, because people will mistakenly treat that as the definitive prescription for how to do it.  This is more of an overview, which will get you started, but hopefully you’ll be doing your own thorough planning and testing when the time comes !

Firstly, you create a fresh new database on your target system.  It will just have the usual tablespaces (SYSTEM, SYSAUX, etc).  It will be the recipient of all of the non-default tablespaces that will be be transporting over from our source system.

On the source system, first you’ll need a datapump of all the object definitions, without unloading any data.

expdp ... dumpfile=metadata_full.dmp full=y content=metadata_only exclude=user,role,role_grant,profile exclude=table_statistics exclude=index_statistics

and for each datafile, you need an imagecopy using RMAN, for example:

backup as copy tag 'my_tablespace' datafile 50,60,61 format '/backup_dest/file%f.bkp; 

And I do this for all of the user tablespaces, ie, not those that would be created as part of a fresh db install.  Note: Ensure compression is turned off for all RMAN parts of this process.

Notice I excluded statistics in the datapump.  Whilst datapump can also unload the optimizer stats for you, we found impdp to very slow for bringing those stats back into the target system, so we took control of that ourselves

exec dbms_stats.create_stat_table(user,'STATS'); 
exec dbms_stats.export_database_stats('ST'ATS,'WHOLE_DB',user); 

Now – so far, our source system is still running.  There has been no outage or disruption to service.  Those datafile copy backups we took, now need to be converted to the endian format of the target system, once again using RMAN, with a convert command for each file.

convert from platform 'AIX-Based Systems (64-bit)' datafile '/backup_dest/file50.bkp'  format '/target_dest/file50.dbf'; 

So now we have a converted set of files, but of course, they are a "fuzzy" copy of the data since the copies were taken with the source system still active.  This is where the nice part of cross-platform migration comes in.  I can regularly take incrementals from my source system, and convert/apply them to the freshly converted datafile copies.  Thus I can keep my target system up to date with my source system, without taking my source system offline.

So I can find the base checkpoint# from all files, and take an incremental of each tablespace since that point in time

select MIN(checkpoint_change#) from v$datafile; 

backup incremental from scn <mincheckpoint> tablespace 'MY_TSPACE' format  '/backup_dest/%U'; 

The incrementals can then be converted/applied to the target system using a little bit of PLSQL

DECLARE 
  d  varchar2(512); 
  h  varchar2(512) ; 
  t  varchar2(30) ; 
  b1 boolean ; 
  b2 boolean ;

BEGIN 
  d := sys.dbms_backup_restore.deviceAllocate; 
  sys.dbms_backup_restore.applysetdatafile(   check_logical=>false, cleanup=>false) ;

  sys.dbms_backup_restore.applyDatafileTo( 
     dfnumber=>50, 
     toname =>'/target_dest/file50.dbf, 
     fuzziness_hint=>0, 
     max_corrupt =>0, 
     islevel0=>0, 
     recid=>0, 
     stamp=>0);

  sys.dbms_backup_restore.restoreSetPiece( 
     handle=>'/backup_dest/incr12345', 
     tag=>null, 
     fromdisk=>true, 
     recid=>0, 
     stamp=>0) ;

  sys.dbms_backup_restore.restoreBackupPiece( 
    done=>d, 
    params=>null, 
    outhandle=>h, 
    outtag=>t, 
    failover=>failover);

  sys.dbms_backup_restore.restoreCancel(TRUE); 
  sys.dbms_backup_restore.deviceDeallocate;

END; 
/

 

This process can be repeated indefinitely until you are ready to cutover to the new system.  When that time will comes, the process is similar

Your source system now must go read-only (and hence this most likely is the commencement of your outage)

begin 
for i in ( 
select tablespace_name from dba_tablespaces 
where tablespace_name not in ('SYSTEM','SYSAUX') and contents = 'PERMANENT' 
) 
loop 
  execute immediate 'alter tablespace '||i.tablespace_name||' read only'; 
end loop; 
end; 
/

You now take a datapump of the tablespaces you are going to transport over.  We are omitting the statistics, because we already have them.

expdp ... exclude=table_statistics exclude=index_statistics dumpfile=transport.dmp transport_full_check=no transport_tablespaces=tspace1,tspace2,... 

And you take a final incremental backup, and apply it to the target system.  Now all is in readiness.

Create all the required users by datapump-ing across the network

impdp ... network_link=source_db_link full=y include=user,role,role_grant,profile 

and then import the transportable tablespace definitions

impdp dumpfile=transport.dmp transport_datafiles='/target_dest/file10.dbf','/target_dest/file11.dbf','/target_dest/file12.dbf',... 

And then set your tablespaces to read-write

begin 
for i in ( 
select tablespace_name from dba_tablespaces 
where tablespace_name not in ('SYSTEM','SYSAUX') and contents = 'PERMANENT' and status = 'READ ONLY' 
) 
loop 
  execute immediate 'alter tablespace '||i.tablespace_name||' read write'; 
end loop; 
end; 
/

And bring in all of the other bits and pieces (views, PL/SQL, etc etc) using the metadata dump you took earlier

impdp ... dumpfile=full.dmp full=y exclude=table_statistics exclude=index_statistics

and voila…you will have a migrated database with minimal downtime.  In our case, the transportable datapump export/import, plus the last incremental (thanks to block change tracking), was around 30mins, and the import of all of our PL/SQL etc around 30mins as well.  Not too bad for multiple multi-terabyte databases.

We then upgraded our stats table, and used dbms_stats to import the statistics, and started verification testing.

That covers the broad steps you’ll be taking.  There’s plenty of information on MOS and on OTN about the nitty gritty, but all in all, we were very happy with outcome.

Older Posts »

The WordPress Classic Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 94 other followers