Search

OakieTags

Who's online

There are currently 0 users and 42 guests online.

Recent comments

Affiliations

November 2010

A Year Older, Any Wiser?

November 30, 2010 (Updated December 1, 2010) It feels a bit strange, but this blog went live a year ago today.  What was the motivation for creating the blog?  At the time I was comtemplating creating a publically accessible Oracle Database related blog, I already had a restricted-access Oracle blog, and if I was lucky [...]

VPD / FGAC / RLS

That’s “Virtual Private Database”, “Fine Grained Access Control”, or “Row-level Security” – three different names for the same feature. (Four if you include the separately licensed Oracle Label Security (OLS) which is a product built on top of RLS).

I’ve just seen a nice presentation from John Batchelor of Sopra hitting the key points of intent, implementation and threat from this (free) product and wondered how many sites used it. So I’ve put up a little poll about usage, with a follow-up for those who do use it about the impact on their system.

If you have any problems, insights, or comments relating to the product please feel free to add them as comments to the post. I can’t promise to offer any response – but other readers might have some relevant contribution to make.





Best Practice

This came up in one of today’s presentations at the UKOUG annual conference: http://dilbert.com/strips/comic/2008-09-03/

It’s like the line from the movie The Incredibles: “when everyone’s super, no-one’s super.”

How NOT to present

I’m at the UKOUOG this week and, as ever, the presentations vary in quality. Most are excellent {or even better than that}, some are not. I was in one first thing this morning and, I have to say, it was rushed, garbled, unclear and there was a definite air of unease and panic. I’m not even sure the guy got to his big point and I could think of at least three major things he did not mention at all.

I think his main problem was just starting off in a rush and never settling down. You see, I was stuck on the top floor of my Hotel and had to run to the venue. Yes, the poor presentation was by me :-( .

I usually present well {modesty forbids me from saying I am a very good presenter – but modesty can take a hike, my ego knows I am capable of giving great presentations}. I am one of those lucky people for whom presenting has never been particularly frightening and, in fact, I find it easier to present to a group of people than talk with them.

But not today. I was already worried about the session, have been for weeks, as I was doing interactive demos. But last night I ran through it, wrote down the names of the scripts and the slide numbers so I could just bang through them and timed it all. 50 mins, I would skip one unneeded section. Calm. I got a reasonable night’s sleep, got up early and ran through it all one more time, making sure my Big Point demo worked. And it did. Yes.

Went down to breakfast, had breakfast and back to the room to pick up my stuff. And realised I was late. Less than 10 minutes to do the 5 minutes over to the venue. So I fled the room, stuffing my laptop in my bag. But not my notes. Or my conference pass. I did not think of this as I stood on the top floor of the hotel, I just thought “where are the lifts?”. They were all below me, ferrying hungry people to and from breakfast. After what seemed like an hour and was only 4 or 5 minutes I decided 16 flights of stairs was OK to go down and, to give me credit, I managed those stairs and the few hundred yards to the venue in pretty good time. I did pause for a few seconds at floor 7, I think, when I remembered my notes. Too late.

But I was now panicked and arrived as a dash. I had to mess about with the Audio Visual guy to get going and started 2 mins past my slot start – and then did the 5 minutes of non-relevant stuff I had decided to drop. It was game over from there, I was failing to find the correct scripts, I was skipping relevant sections and I was blathering instead of just taking a few seconds to calm down and concentrate.

Oh well, my first time in a large room at the UKOUG and I messed up. At least I had the key lesson drummed into me. TURN UP EARLY!!!!

Collection Costs

Here’s an extract from an execution plan I found on a client site recently. I’ve collapsed lines 5 to 42 into a single line representing the rowsource produced by a fairly messy execution plan, leaving just the last three stages of execution on view. Each of three operations joins the same collection variable (using the table() operator) to the row source – once through a hash join, then twice more (joining to two other columns) through nested loop outer joins:

The resulting estimates of row counts and costs are quite entertaining and, fortunately, not very accurate:


-----------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |        |       |       |  5865M(100)|          |
|   1 |  NESTED LOOPS OUTER                        |        |   478G|   207T|  5865M  (1)|999:59:59 |
|   2 |   NESTED LOOPS OUTER                       |        |  5830M|  1895G|    18M  (1)| 50:38:00 |
|*  3 |    HASH JOIN                               |        |    71M|    14G|   266K  (2)| 00:44:49 |
|   4 |     COLLECTION ITERATOR PICKLER FETCH      |        |       |       |            |          |
|   5 |     {join-based row source}                |        | 87049 |    18M|   266K  (2)| 00:44:49 |
|  43 |   VIEW                                     |        |    82 | 10578 |            |          |
|* 44 |     COLLECTION ITERATOR PICKLER FETCH      |        |       |       |            |          |
|  45 |   VIEW                                     |        |    82 | 10578 |            |          |
|* 46 |    COLLECTION ITERATOR PICKLER FETCH       |        |       |       |            |          |
-----------------------------------------------------------------------------------------------------

The system was running 11.1.0.7, and the optimizer clearly has some problems (still) with the arithmetic of collection types.

Here’s a suggestion for use with the table() operator, by the way. The optimizer assumes that the number of rows produced by the table() operator will be roughly the same as the number of bytes in the default block size – and this can lead to some very poor execution plans (watch out, by the way, if someone tells you to rebuild your database with a new default block size – there may be some unexpected side effects). As a general principle I advise people that if they have a reasonable idea of the number of rows that they will be passing into a query of this type that they tell the optimizer what that number. The simplest way of doing this is to change your SQL from something like this:


from
        tableX                        ty,
        table(collection_variable)    tc,
        tableY                        ty,
...

to something like this – where you introduce an inline view with a /*+ cardinality */ hint:

from
        tableX                        tx,
        (
        select  /*+ cardinality(t 20) */
                *
        from    table(collection_variable)  t
        )                              tc,
        tableY                        ty,
...

It’s possible to use a “global” hint in the main query with a query block name referencing the inline view, of course – but it can be very hard to make this work correctly in more complex cases – especially if you are using ANSI SQL – so a simple inline approach with a hint in the view is probably a much safer bet.

I ORDERED a Hint – Why Won’t You Listen to Me?

November 29, 2010 (Updated November 30, 2010) I recently read a blog article that forced me to Stop, Think, … Understand (which happens to be the subtitle of this blog, so it is worth mentioning).  The blog article discussed an error found in the Oracle Database documentation regarding how to read an execution plan – more [...]

Pythian at UKOUG Technology and E-Business Suite Conference 2010

Hello Birmingham!

It’s past Sunday midnight and I’m stuck in my room in the last couple hours finishing my slides for my masterclass tomorrow. Turns out that I’m presenting the very first session of the conference at 9am. I wish there is a keynote instead so that I could grab one more hour of sleep (it’s going to be deep into the night back home in Canada). Strange that the keynote was moved to Wednesday — I hope UKOUG has really good reason for that!

My two hours masterclass will start at the same time as Tom Kyte’s a-la keynote session — what a competition. On the other hand, there is no other sessions in server technology so I expect that folks without interest of database development will automatically end up in my session. I’m in Hall 5 – quite large room. Is it the second biggest room after the Hall 1?

I will need to work hard to keep the audience… maybe I shouldn’t plan for any breaks to make sure I don’t let folks slip out to the next sessions like James Morles’ Sane SAN 2010 or Jeremy Schneider’s Large Scale ASM.

My masterclass is based on the slides that I presented at the Oracle OpenWorld few months ago which, in turn is reworked session on Oracle Clusterware internals that I’ve done number of times as long session with demos. I thought updating this material to 11gR2 would be easy… Boy was I wrong!

11gR2 Grid Infrastructure has changes so much that it took me much much longer to get something sensible ready. I also had to limit the scope a bit as Grid Infrastructure has become so much more complex than older pre-11gR2 Clusterware. (stop complaining Alex!)

Anyway, everything is ready now and demos look reasonable. It will be a bit rough doing it first time – I’m sure I’ll stumble few times but fingers crossed we get to the end timely. I actually hope to finish early and allocate a bit more time for Q&A and potential ad-hoc demos at the end. But enough about me…

Who from Pythian are at the UKOUG conference this year? In additional to myself, it’s Christo Kutrovsky, Daniel Fink, Paul Vallee and Andrew Poodle. Christo, Dan and myself are presenting, Andrew is helping organization of MySQL track as a MySQL SIG Chair and Paul… well, I’d say Paul is a slacker so he is covering the beer tap to pay up! :)

It’s close to 2am – gotta get some sleep before tomorrow. Few words against Jurys Inn Hotel this year. It’s the first year I’m having so much troubles here including no early check-ins, not working phones, no internet in two rooms (I had to switch twice!), and somewhat unfriendly stuff this year. Has hotel management change since last year or what? Will consider another hotel next time I think.

Oh… and it’s indeed bloody cold here! So cold that it seems to impact the amount of girls-who-forgot-their-skirts-at home at the Broad Street. This unusually cold weather does impact travel plans of other conference speakers and attendees. Doug Burn seems to have been delayed for like a day and barely made it to have a pint at Tap&Spile – I wish I could accompany the crowd there until late but thanks to the awesome schedule (and unfinished state of my presentation, to be fair) I had to miss some of the fun.

PS: I have another session on Tuesday — Analysis of Oracle ASM Failability (should be Fallibility I guess but I’ll keep it misspelled simply because I can!). If anybody wants to catch up for any reason (like buying me a beer) — text me at +1 613 219 7031. iPhone doesn’t work with data-plans here for unknown reason so no twitter/email on the go.

Life Update

I’m feeling quite burnt out now. I had a great time in Serbia, but I didn’t get much in the way of sleep. I figured the first night I was a bit stressed about the luggage situation, which stopped me from settling. On the second night I got two blocks of two hours sleep. Not sure why. Last night I lay awake for ages then finally fell asleep a couple of hours before my alarm went off. No reason I can think of.

I’ve already posted a rant about my flight home. The combination of sleep deprivation and the travel issues has really taken it’s toll. I’m not sure  I had properly recovered from the Asia Pacific tour either. I sound really pathetic, like a spoiled kid, but I just want to do something normal for a while.

Steve Feuerstein posted recently about ditching a lot of his travelling. I do a lot less than him and I haven’t been doing it for as long either, but I really know where he’s coming from. Some people are like robots and can do it week after week. I’m not one of them.

Well, I’ve got a day off before UKOUG starts. I know it sounds like I’m an ungrateful git, but I’m really not looking forward to it. The adrenaline will kick in and I will be all manic and chatty as always, but I’ve really got nothing left. At least I’m not presenting. I’ve agreed to help out Vikki with the OTN/ACE stuff. Not sure how much time that will take, so I’m not sure how many presentations I will get to see. If you are knocking around the OTN lounge and you see a fat old geezer who’s either sleeping or mumbling to himself, give him a prod and say, “You miserable old sod. You get all these fantastic opportunities and all you can do is bitch and moan!” I will then talk to you for 10 hours about Aardvarks, or something random like that. I have no off switch. You’ve been warned. :)

My sister had a mole taken off her face a few days before I left the UK. I got home today and found out the tests have come back positive for skin cancer (malignant melanoma). My Mom had malignant melanoma about 25 years ago and carcinoma more recently. My dad had skin cancer too. I think it was carcinoma, but I can’t remember. Mom and dad recovered fine (Dad died of something else, not cancer), so I’m sure my sister will be OK. We’re tough as old boots. :) Just to clarify, it was my sister-in-law, not my sister, that had ovarian cancer 2 years ago, she’s tough as old boots too. :)

Cheers

Tim…

Belgrade, Serbia: PL/SQL Masterclass -Day 2

Day 2 of the PL/SQL Masterclass went well. The feedback was very positive, which is always nice. As always I over ran on both days. I was about 25 minutes over on the first day and something like 40 minutes over on the second. I’ve got some more stuff I would like to include in the course, but that will mean taking something out or locking the doors and strapping people to their seats. :)

I’ve  had a really good time in Serbia. It would be good to go back again some time so I can see all it in daylight. :)

Cheers

Tin…

OT: Penultimate Movember Update

I have less than 48 hours to go. On the one hand, I feel slightly bad for finishing about 30 hours early but frankly, for those who know me, going from this ...

to this ....

Is *commitment*!

Besides, the opportunity to make even more of a fool of myself is on the horizon and the UKOUG 'Panto' will hopefully pull in more sponsorship. Because after the initial rush, the sponsorship flow has slowed down dramatically. I'm extremely grateful for all of those who have already contributed but also mindful of the promises made - "let's wait and see if you actually go through with it ...." - but still to be delivered. Ant and Dec - I'm talking to you! Whether the inevitable Panto video appears online is in the balance. If I
don't break £1000, I'd say it's pretty unlikely.

So, there isn't much time left. You've had your laugh, now time to pay up.