Search

OakieTags

Who's online

There are currently 0 users and 20 guests online.

Recent comments

Oakies Blog Aggregator

Its not about the outage

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

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

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

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

I logged this SR:

Problem: Making index partition unusable does not free underlying segment

Test Case:

SQL> sho parameter deferred

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

SQL> drop table T purge;

Table dropped.

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

Table created.

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

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

Index created.

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

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

10 rows selected.

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

Index altered.

SQL> select segment_name, partition_name, bytes from user_segments;

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

10 rows selected.

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

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

But this post is about listening…

First response to the SR:

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

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

I point this out via an SR update.

Second response to the SR:

You can drop the partition to reclaim free space

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

Secondly, its not addressing the original request.

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

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

OUGN Norway Conference

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

The slides from my talks can be found here

Nightmares about exams…

It’s been over 20 years since I finished my finals for my first degree, but this time of year never fails to affect my already terrible sleeping patterns. I’ve started waking up in the morning panicking about an exam, which I’ve forgotten to revise for. It takes a few minutes for me to realize there is no exam, so I’ve got nothing to worry about… :)

Like I said, this happens every year, but I think the fact I’m currently working for University has reinforced the panic in me. I sense a few more weeks of exam panic induced insomnia, before I settle back into my normal insomnia.

Cheers

Tim…


Nightmares about exams… was first posted on April 29, 2013 at 10:25 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

fast refresh of outer-join-only materialized views – algorithm, part 2

In this post, we are going to complete part 1 illustrating the (considerably more complex) general case of a fast refresh from a master inner table without a unique constraint on the joined column(s).

To recap, now the outer slice can be composed of more than one row, for example:

ooo inn1
ooo inn2

and hence, both the DEL and INS step must consider (and read) the whole outer slice even if only a subset of the inner rows have been modified. This requires both more resources and a considerably more complex algorithm. Let's illustrate it (the mandatory test case is here).

The DEL macro step

This sub step (named DEL.del by me) is performed first:

/* MV_REFRESH (DEL) */
delete from test_mv where rowid in (
select rid
  from (
select test_mv.rowid rid,
       row_number() over (partition by test_outer_rowid order by rid$ nulls last) r,
       count(*)     over (partition by test_outer_rowid ) t_cnt,
       count(rid$)  over (partition by test_outer_rowid ) in_mvlog_cnt
  from test_mv, (select distinct rid$ from mlog$_test_inner) mvlog
 where /* read touched outer slices start */
       test_mv.test_outer_rowid in
          (
          select test_outer_rowid
            from test_mv
           where test_inner_rowid in (select rid$ from mlog$_test_inner)
          )
       /* read touched outer slices end */
   and test_mv.test_inner_rowid = mvlog.rid$(+)
       )
 /* victim selection start */
 where t_cnt > 1
   and ( (in_mvlog_cnt = t_cnt and r > 1)
          or
         (in_mvlog_cnt < t_cnt and r <= in_mvlog_cnt)
       )
 /* victim selection end */
)

followed by the DEL.upd one:

/* MV_REFRESH (UPD) */
update test_mv
   set jinner = null, xinner = null, pkinner = null, test_inner_rowid = null
 where test_inner_rowid in (select rid$ from mlog$_test_inner)

This two steps combined do change all the rows of the MV marked in the log (and only them, other rows are not modified at all); the first step deletes some of them, leaving all the others to the second one, that sets to null their columns coming from the inner table.

DEL.upd is straighforward. Let's illustrate the DEL.del algorithm:

a) the section "read touched outer slices" fetches all the MV outer slices that have at least one of their rows marked in the log;
b) the slices are outer joined with the "mvlog" in-line view, so that rid$ will be nonnull for all rows marked in the log;
c) the analytic functions, for each outer slice separately, compute the number of rows (column t_cnt), the number of rows marked (column in_mvlog_cnt), and then attach a label (column r) that orders the row (order is not important at all besides non-marked rows being ordered last)
d) the where-predicate "victim selection" dictates which rows to delete.

The victim selection predicate has three sub-components, each implementing a different case (again, considering each slice separately):

"t_cnt > 1": do not delete anything if the slice contains only one row (since it is for sure marked and hence will be nulled by DEL.upd)

             rid$ t_cnt in_mvlog_cnt r  action
ooo inn1 not-null     1            1 1  updated by DEL.upd   



"in_mvlog_cnt = t_cnt and r > 1": all rows are marked, delete all but one (that will be nulled by DEL.upd)

             rid$ t_cnt in_mvlog_cnt r  action
ooo inn1 not-null     3            3 1  updated by DEL.upd
ooo inn2 not-null     3            3 2  deleted by DEL.del
ooo inn3 not-null     3            3 3  deleted by DEL.del



"in_mvlog_cnt < t_cnt and r <= in_mvlog_cnt": only some rows are marked; delete all marked rows, keep all the others.

             rid$ t_cnt in_mvlog_cnt r  action
ooo inn1 not-null     3            2 1  deleted by DEL.del
ooo inn2 not-null     3            2 2  deleted by DEL.del
ooo inn3     null     3            2 3  nothing



The INS macro step

The first sub-step is INS.ins:

/* MV_REFRESH (INS) */
insert into test_mv
select  o.jouter,  o.xouter,  o.pkouter, o.rowid,
       jv.jinner, jv.xinner, jv.pkinner, jv.rid
  from ( select test_inner.rowid rid,
                test_inner.*
           from test_inner
          where rowid in (select rid$ from mlog_test_inner)
       ) jv, test_outer o
 where jv.jinner = o.jouter

this sub-step simply find matches in the outer table for the marked inner table rows (note that it is an inner join, not an outer join), and inserts them in the MV.

Then, INS.del:

/* MV_REFRESH (DEL) */
delete from test_mv sna$ where rowid in (
select rid
 from (
select test_mv.rowid rid,
       row_number()            over (partition by test_outer_rowid order by test_inner_rowid nulls first) r,
       count(*)                over (partition by test_outer_rowid ) t_cnt,
       count(test_inner_rowid) over (partition by test_outer_rowid ) nonnull_cnt
  from test_mv
 where /* read touched outer slices start */
       test_mv.test_outer_rowid in
          (
          select o.rowid
            from ( select test_inner.rowid rid$,
                          test_inner.*
                     from test_inner
                    where rowid in (select rid$ from mlog$_test_inner)
                 ) jv, test_outer o
           where jv.jinner = o.jouter
          )
      /* read touched outer slices end */
      )
 /* victim selection start */
 where t_cnt > 1
   and ( (nonnull_cnt = 0 and r > 1)
          or
         (nonnull_cnt > 0 and r <= t_cnt - nonnull_cnt)
       )
 /* victim selection end */
)

this substep has a SQL structure very similar to DEL.upd, hence I will simply outline the algorith: first, the statement identifies (in the "read touched outer slices" section) all the outer slices that had at least one rows inserted by INS.ins, by replaying its join; then, for each slice, it deletes any row, if it exists, that has column "test_inner_rowid" set to null (check the "victim selection predicate").

Side note: I cannot understand how nonnull_cnt could be = 0 - possibly that is for robustness only or because it can handle variants of the DEL step I haven't observed.

speeding up

These are the indexes that the CBO might enjoy using to optimize the steps of the propagation from the inner table:
- DEL.del: test_mv(test_inner_rowid, test_outer_rowid)
- DEL.upd: test_mv(test_inner_rowid)
- INS.ins: test_outer(jouter)
- INS.del: test_outer(jouter) and test_mv(test_outer_rowid , test_inner_rowid)

And hence, to optimize all steps:
- test_outer(jouter)
- test_mv(test_inner_rowid, test_outer_rowid)
- test_mv(test_outer_rowid , test_inner_rowid)

And of course we need the usual index on test_inner(jinner) to optimize the propagation from the outer table (not shown in this post), unless we positively know that the outer table is never modified.

Note that the two indexes test_mv(test_inner_rowid, test_outer_rowid) and test_mv(test_outer_rowid , test_inner_rowid) allow to skip visiting the MV altogether (except for deleting rows, obviously) and hence might reduce the number of consistent gets dramatically (the indexes are both "covering" indexes for the SQL statements we observed in the DEL.del and INS.del) .

For example, in my test case (check ojoin_mv_test_case_indexed.sql), the plan for the DEL.del step is:

--------------------------------------------------------------
| 0|DELETE STATEMENT                |                        |
| 1| DELETE                         |TEST_MV                 |
| 2|  NESTED LOOPS                  |                        |
| 3|   VIEW                         |VW_NSO_1                |
| 4|    SORT UNIQUE                 |                        |
| 5|     VIEW                       |                        |
| 6|      WINDOW SORT               |                        |
| 7|       HASH JOIN OUTER          |                        |
| 8|        HASH JOIN SEMI          |                        |
| 9|         INDEX FULL SCAN        |TEST_MV_TEST_INNER_ROWID|
|10|         VIEW                   |VW_NSO_2                |
|11|          NESTED LOOPS          |                        |
|12|           TABLE ACCESS FULL    |MLOG$_TEST_INNER        |
|13|           INDEX RANGE SCAN     |TEST_MV_TEST_INNER_ROWID|
|14|        VIEW                    |                        |
|15|         SORT UNIQUE            |                        |
|16|          TABLE ACCESS FULL     |MLOG$_TEST_INNER        |
|17|   MAT_VIEW ACCESS BY USER ROWID|TEST_MV                 |
--------------------------------------------------------------
5 - filter[ (T_CNT>1 AND ((IN_MVLOG_CNT=T_CNT AND R>1)
OR (IN_MVLOG_CNT

Note the absence of any access to the MV to identify the rows to be deleted (row source operation 5 and its progeny; note the filter operation, which is the final "victim selection predicate"); the MV is only accessed to physically delete the rows.

Ditto for the INS.del step:

-------------------------------------------------------------------
| 0|DELETE STATEMENT                     |                        |
| 1| DELETE                              |TEST_MV                 |
| 2|  NESTED LOOPS                       |                        |
| 3|   VIEW                              |VW_NSO_1                |
| 4|    SORT UNIQUE                      |                        |
| 5|     VIEW                            |                        |
| 6|      WINDOW SORT                    |                        |
| 7|       HASH JOIN SEMI                |                        |
| 8|        INDEX FULL SCAN              |TEST_MV_TEST_INNER_ROWID|
| 9|        VIEW                         |VW_NSO_2                |
|10|         NESTED LOOPS                |                        |
|11|          NESTED LOOPS               |                        |
|12|           TABLE ACCESS FULL         |MLOG$_TEST_INNER        |
|13|           TABLE ACCESS BY USER ROWID|TEST_INNER              |
|14|          INDEX RANGE SCAN           |TEST_OUTER_JOUTER_IDX   |
|15|   MAT_VIEW ACCESS BY USER ROWID     |TEST_MV                 |
-------------------------------------------------------------------
5 - filter[ (T_CNT>1 AND ((NONNULL_CNT=0 AND R>1)
OR (NONNULL_CNT>0 AND R<=T_CNT-NONNULL_CNT))) ]
...



You might anyway create just the two "standard" single-column indexes on test_mv(test_inner_rowid) and test_mv(test_outer_rowid) and be happy with the resulting performance, even if you now will access the MV to get the "other" rowid - it all depends, of course, on your data (how many rows you have in each slice, and how many slices are touched by the marked rows) and how you modify the master tables.

Hey Mum, I’m Famous!!!

I got a mail this week from Richard Harrison:

“Hi Martin
See you made it in to oracle magazine this month.That’s the pinnacle of any oracle professionals career – all downhill from here on in :-)”

I was not aware of my sudden raise to fame, but Richard is right – I’m in this month’s “peer to peer” section, which just gives some details about recent Oracle Ace’s I think. I’d forgotten that I had done a form they sent me before Christmas, answering a set of questions. It is interesting to see what they picked out of all my answers to include.

I think most of us would feel it is nice to see something about ourselves in print (so long as it is not derogatory or critical, of course!), though when I come to think of it, I don’t really know why it is nice – other than the rather self-serving feeling of having our egos polished. And as my friends I drink with would (and probably will) comment, my ego certainly does not need much polishing :-). I’ve of course made it worse by blogging about how famous I now am. Polish, polish, polish.

Don’t worry, my wife stepped in to put me back in my place. “You could tell your mum when you next ring her – not that she’ll be impressed at all!”. Thanks wife. She’s right. My mum will just say “that’s nice” in a tone that in no way convinces me she means it, and will then proceed to talk at me about her new cats, what’s on TV and all the terrible things going on in the world, according to the “Daily Mail” (An utterly horrible and vacuous daily tabloid paper her in the UK).

So thank you for the heads-up Richard. I’m looking forward to the rapid decline of my career as you predict… :-)

Announcing EMC WORLD 2013 Flash Related Sessions

Interested In EMC Flash Products Division Technology?
This is just a quick blog entry to announce sessions at EMC WORLD offered by speakers from EMC’s Flash Products Division.  The sessions I’m speaking at is the one about accelerating SQL Server and Oracle with EMC XtremSW Cache.

flash-sessions-EMCWORLD

EMCWORLD2013

Filed under: oracle

The Three Tenners- OUGN 2013

Seeing as I did a blog post about looking forward to my second Norwegian Oracle User Group spring meeting, I suppose I should say how it went.

Given the circumstances, it went well and I enjoyed the conference far more than I would have enjoyed being home for those days. Part of the enjoyment was the reformation of the Martin Cluster as mentioned in my last blog. A small part of that was the slightly childish humour I found in those situations when people introduce themselves when they meet at such meetings. I’d be chatting to Mr Nash, Mr Bach and usually one or two others and when a new person came along and asked names, the three of us would take it in turns to say “hi, I’m Martin” – “I’m Martin” – “I’m Martin too”. It was like a poor take on the “I’m Brian” pastiche of the Spartacus movie. Most people smiled.

At the Speakers Meal on the first night Bryn Llewelyn suddenly said something like “Well, let’s take a picture of the Three Martins – it’s almost like the Three Tennors!” It had to be done, I dug out three ten pound notes so we could have a picture of the Three Tenners. If only the idiot on the right had held his the same way around as the other two…

The Martins doing a terrible

The Martins doing a terrible “Three Tenners” joke

Thanks to Bryn for the picture.

So, why did I say the event went well despite the circumstances? Because I was ill thoughout the event :-(.

I met up with some friends in London on Tuesday night before the conference, as I could not get into London and across to Heathrow in time for my flight in the morning. So I had to stay over. I know, you are all thinking I drank waaaay too many beers and was hung over the next day! I could not argue that I did not have several beers that night but it was more the 4 hours sleep and long walk that I blamed for how I felt. At the end of the evening I went out to Heathrow and I got directed to the wrong Premier Inn there {I was very specific about it being T5, I knew there were two hotels} and it took a while to get to this wrong hotel. Or rather half a mile past it. I new what side of the road the hotel was on (online maps with street view are so helpful) but it did not appear {as I am in the wrong place} and the bus driver who was going to give me the nod did so a stop or two too late. So I walked waaay back – and get told “Other Premier Inn sir”. OK, can you get me there “No, get a bus”. It’s midnight, the last bus was not so helpful. Shuttle bus? “No sir” Taxi? “Get the bus sir”. Sigh, not helpful. Well, that is what you should expect when you use budget hotels I guess. How far is it? “About 2 miles Sir”. I walked. It turned out to be over 3.

So when I met Martin Bach at the airport next day, I blamed the beer, long walks and lack of sleep. But as the day progressed I felt worse and worse and worse. Tuesday night might not have helped but this was more. We got to the venue and after lunch I did my talk on Row Level Security and masking data (which was packed, to my amazement) then checked into the hotel and went to bed. Thus I missed all other sessions. I managed the Speakers Meal (and it was jolly good, thank you OUGN) and then had a good night’s sleep. It did not help. The next day was a blur, half of it I was asleep but I did manage my second presentation, on Disasters oddly enough. Afterwards I went back to my cabin and only got up briefly to see about trying to eat some food, but the rough sea quickly made me decide not to bother. I managed two other sessions other than my own. One was a Martin talking (a very good one on making practical use of virtualisation) and the other was the Keynote by Cary Millsap, who did a talk that was more about life than Oracle. I had missed this talk at the UKOUG conference but had heard about it, so I was keen to see it. It was the highlight of the conference for me. I’ll probably do a Friday Philosophy on it “soon” but the main message was that Cary feels life always changes and it seems best when you are doing something to progress from a bad place to a better one, rather than ‘enjoying’ the better place. It’s a philosophy I have a lot of time for.

The last day was similar except that I seemed to perk up a bit in the afternoon and I finally got to see one or two more sessions. I did my final one, on “an introduction to tuning” and it was awful. I had a lot of material to cover and I knew I could pile though it on a good day, but this was a bad day and I simply could not keep my thread or progress through the material rapidly. *sigh*. At least there were not too many people there to witness the car crash. I’ll be interested to see how poor the ratings on my talks are.

So all in all I should count the event as a bit of a disaster. But I managed to honour my presenting duties and, despite spending half the time laying down in the dark, I had some fantastic conversations with people in the two evenings I managed to be awake. Oh, and thank you to the people who organised for them to play Happy Birthday to me in the Piano Bar at midnight on Friday (45 now), I might have been a bit quiet about it but I did appreciate that. It sums up half of what I like about the OUGN spring conference. I missed the first half, the excellent talks you get from what both years has been a very good speaker line-up, but the other is that everyone is around in the lunchtimes and evenings and you get to chat, find out other stuff, make new friends and just spend time with lots of people who are interested in some of the things you are, ie Oracle.

After the conference I did stay on in Oslo for a few day, my wife had come out to join me for my birthday weekend. We enjoyed the weekend but she kept on saying “you look dreadful” or “are you sure you are up to going out?” and “stop coughing you annoying bloody bas….” :-) I’m still coughing now and feel pretty rough, a week on.

I’ll be sure to try and be there next year. I hope I feel better by then!

Prize Winners : Managing Multimedia and Unstructured Data in the Oracle Database e-book

A couple of weeks ago I started a competition to win 4 copies of Managing Multimedia and Unstructured Data in the Oracle Database byMarcelle Kratochvil. Thanks to Packt for donating the prizes. The competition closed today and the lucky winners are:

  • Kim Berg Hansen
  • Stuart Uren
  • Steve (dnunknown)
  • Bayu Satria Setiadi

Once I’ve published this post I’ll be sending your email address to my contact at Packt, who will contact you to deliver your e-book.

I was severely tempted to award one of the books to Connor McDonald for his ingenious use of death threats in an attempt to sway the judging process. :)

Cheers

Tim…


Prize Winners : Managing Multimedia and Unstructured Data in the Oracle Database e-book was first posted on April 26, 2013 at 10:37 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Analysis Challenges

April 25, 2013 Roughly 12 years ago I was attempting to analyze customer order changes that were received through electronic document interchange (EDI), specifically X12 830 documents that show order forecasted demand for specific part numbers.  At the time, the EDI data was partially transformed and inserted into an Oracle 8.0.5 database, while that data […]

Analysis Challenges

April 25, 2013 Roughly 12 years ago I was attempting to analyze customer order changes that were received through electronic document interchange (EDI), specifically X12 830 documents that show order forecasted demand for specific part numbers.  At the time, the EDI data was partially transformed and inserted into an Oracle 8.0.5 database, while that data […]