Yesterday I was caught up in an interesting SNAFU at my local Supermarket.  All of the checkout registers shut down, thus making it impossible to pay for groceries.  Later on Twitter, the company apologized as we discovered it was actually a nationwide outage!

News of the outage spread like wildfire through the media:

http://www.news.com.au/finance/business/retail/woolworths-checkouts-hit-by-national-outage/news-story/5611943156249d4ecc6427ef0b447c18

https://www.smh.com.au/business/consumer-affairs/woolworths-meltdown-closes-stores-across-australia-20180416-p4z9y4.html

http://www.abc.net.au/news/2018-04-16/woolworths-checkouts-across-australia-down/9663904

https://www.sbs.com.au/news/woolworths-hit-by-nationwide-technical-outage

https://www.9news.com.au/national/2018/04/16/16/29/woolworths-outage-stops-trading-across-australia

https://www.lifehacker.com.au/2018/04/the-woolworths-outage-is-a-lesson-in-business-continuity-planning/

TL;DR – people were forced to abandon their shopping trolleys and had to leave the stores. 

Needless to say, consumers vented loudly and forcefully at Woolworths, and the negative press naturally struck a chord with the general public, because we all feel empathy for the parent in front of the television cameras lamenting their inability to feed their family that night.  (For the record, my boys had meat balls and salad last night that we made with the leftovers we had in the fridge )

In a perfect world, IT system updates should never cause pain for the users of those IT systems, but no matter how careful the testing and planning, I think it is reasonable to assert that we can never eliminate totally the chances of a major problem during an upgrade, our aim is always to shrink the probability to a close to zero as possible.

That brings me to the point of this post – and this perhaps slightly controversial stance.  I don’t think IT outages really matter that much from the perspective of the customer. For example, a while back Amazon had a huge outage here in Australia due to storms in Sydney.  Delta Airlines had a big outage in late 2016.  But last time I checked, people are still flying Delta and still buying stuff they didn’t need from Amazon . Customers will forgive an outage but only if you prioritize their needs over yours during the crisis.  People are still ripping into Woolworths today because a Twitter apology doesn’t really get consumers any closer to taking groceries home. 

So this is what I would have done if I was Woolworths…. Make an announcement in each store that the store needs to close unexpectedly and customers to take your trolley to the nearest checkout (even though I know that the checkout’s are not working).  At that point, simply let people take what they have accumulated so far in their trolleys for no charge.  The news articles above already mentioned that the stores had security staff on hand to assist with closing the stores – so there is protection against a “looting mentality” being created.  Yes, there will be still be some negative press for those customers that could not get into the stores once they closed, but I contend that ultimately this would have turned into a positive result for Woolworths.  Yes you take a hit on the bottom line for yesterdays revenue, but the media attention becomes the mums and dads walking out of the stores smiling about the free shop they just got, rather than swearing they’ll never shop at Woolworths again.

Outages don’t matter.  Meeting the customer need is what matters.

Don’t get me wrong – I’m not claiming that any and every company I have ever worked for, or worked with, has a glowing record of understanding how to meet customer needs during times of crisis.  My point is that it should be something to always strive for – when you inflict pain on your customers due to the information technology solutions you build, then do your best to own the problem, and bust a gut trying to make the experience as bearable as possible for your customers, or even a win for them. 

Whether you turn bad into good, or bad into worse, rest assured your customers will remember you for it.

Some members of the Oracle community got well and truly into the April Fools Day spirit this year.

There were plenty of very earnest looking blog posts about a new 18c function – “TO_DOG_YEAR”.  You can read their posts here

http://www.oralytics.com/2018/04/predicting-ibs-in-dogs-using-oracle-18c.html 
https://blog.dbi-services.com/after-iot-iop-makes-its-way-to-the-database/
http://berxblog.blogspot.ie/2018/04/more-fun-with-ages.html
http://vanpupi.stepi.net/2018/04/01/exploring-18c-exadata-functions/

They even enlisted the help of the team here at AskTOM where they posed a question looking for more details here.

But naturally, it was important to get as many puns and hints into our answer as possible – did you spot them all ?

Here are the blog posts that you hit on most this year.  Thanks for supporting the blog, and always, there will be more content next year !

• ORA-14758: Last partition … cannot be dropped
• EXCHANGE PARTITION those pesky columns
• Pluggable database and restricted sessions
• Active and Inactive Sessions
• 12c install on Windows
• Problematic SQL ? PL/SQL is your friend.
• Compressed partitions are not compressed tables
• Cross-platform database migration
• BULK COLLECT into nested table
• Sharing a tablespace between 2 databases

I was perusing some old content during a hard drive “spring clean” the other day, and I found an old gem from way back in 2001.  A time when the database community were trying to dispel the myth that all database performance issues could be tracked back to,  and solved via, the database buffer cache hit ratio.  Thankfully, much of that folklore has now passed into the realm of fiction, but I remember at the time, as a means of showing how silly some of the claims were, I published a routine that would generate any buffer cache hit ratio you desired.  It just simply ran a query to burn through logical I/O’s (and burn a whole in your CPU!) until the required number of operations bumped up the buffer cache hit ratio to whatever number you liked  

Less performance, more work done…. all to get a nice summary number.

The kinds of statistics that the database collects, and what each one represents has changed over the years and versions of Oracle, but I figured I’d present the routine in original form as a nostalgic reminder that statistics without an understanding behind them are as good as no statistics at all.

Enjoy !

create or replace
procedure choose_a_hit_ratio(p_ratio number default 99,p_show_only boolean default false) is
  v_phy                number;
  v_db                 number;
  v_con                number;
  v_count              number;
  v_additional_congets number;
  v_hit number;
  
  procedure show_hit is
  begin
    select p.value, d.value, c.value
    into v_phy, v_db, v_con
    from 
      ( select value from v$sysstat where name = 'physical reads' ) p,
      ( select value from v$sysstat where name = 'db block gets' ) d,
      ( select value from v$sysstat where name = 'consistent gets' ) c;
    v_hit := 1-(v_phy/(v_db+v_con));
    dbms_output.put_line('Current ratio is: '||round(v_hit*100,5));
  end;
begin
--
-- First we work out the ratio in the normal fashion
--
  show_hit;

  if p_ratio/100 < v_hit or p_ratio > 99.9999999 then
    dbms_output.put_line('Sorry - I cannot help you');
    return;
  end if;
--
-- Flipping the formula we can work out how many more consistent gets
-- we need to increase the hit ratio
--
  v_additional_congets := trunc(v_phy/(1-p_ratio/100)-v_db - v_con);

  dbms_output.put_line('Another '||v_additional_congets||' consistent gets needed...');

  if p_show_only then return; end if;
--
-- Create a simple table to hold 200 rows in a single block
--
  begin
    execute immediate 'drop table dummy';
  exception 
    when others then null;
  end;

  execute immediate 'create table dummy (n primary key) organization index as '||
                    'select rownum n from all_objects where rownum <= 200';
--
-- Turn off any new 9i connect-by features to ensure we still do lots of 
-- logical IO
--
  begin
    execute immediate 'alter session set "_old_connect_by_enabled" = true';
  exception 
    when others then null;
  end;
--
-- Grind away until we do all those additional gets
--
  execute immediate '
    select count(*) 
    from (
      select n
      from dummy
      connect by n > prior n
      start with n = 1 )
    where rownum < :v_additional_congets' into v_count using v_additional_congets;

  show_hit;
end;
/

And some output to keep the hit ratio fanatics happy!


SQL> exec choose_a_hit_ratio(85,true);
Current ratio is: 82.30833
Another 29385 consistent gets needed...

PL/SQL procedure successfully completed.

SQL> exec choose_a_hit_ratio(85);
Current ratio is: 82.30833
Another 29385 consistent gets needed...
Current ratio is: 86.24548

PL/SQL procedure successfully completed.

SQL> exec choose_a_hit_ratio(90,true);
Current ratio is: 86.24731
Another 79053 consistent gets needed...

PL/SQL procedure successfully completed.

SQL> exec choose_a_hit_ratio(90);
Current ratio is: 86.24731
Another 79053 consistent gets needed...
Current ratio is: 90.5702

PL/SQL procedure successfully completed.

SQL> exec choose_a_hit_ratio(98,true);
Current ratio is: 90.5709
Another 1141299 consistent gets needed...

PL/SQL procedure successfully completed.

SQL> exec choose_a_hit_ratio(98);
Current ratio is: 90.5709
Another 1141299 consistent gets needed...
Current ratio is: 98.02386

PL/SQL procedure successfully completed.