Day 2 of UKOUG Tech 14

Day 2 of the UKOUG Tech 14 conference for me started with the second session (I spent the first session typing up the blog post on Day 1 as there wasn’t a presentation I was interested in). Tammy Bednar was presenting on “Creating a Test / Dev Database in Minutes Using the Oracle Database Appliance”. Tammy is another old timer at Oracle. Like myself and Maria Colgan, she has been an Oracle employee for 20 years. I’d been discussing with Maria on day 1 that people either seem to move on from Oracle within a couple of years, or they stick around for a long time like us. For me, that’s because I’ve been able to move around and do different things that have kept my interest levels fresh.

But I digress. :) I wasn’t aware of what was happening in the ODA space with this topic. As is often the case at Oracle, there are multiple groups developing similar sorts of functionality. So we have the ODA snapshotting capability, Snap Clone on the EM side, and the ability to create a snapshot copy of a pluggable database in Oracle Database 12c. As I’ve been working on the Snap Clone functionality for some time, I was quite interested to see what the ODA folks had been up to as well. Tammy’s quite an accomplished presenter, so I really enjoyed her presentation.

Next up I went to Tim Gorman and Ron Ekin’s presentation title “Dueling Duplications – Physical vs Virtual Data Cloning”. Tim did most of the presenting, and of course, he’s a great presenter as well. The only issue I really had with the presentation was they were comparing apples with oranges, as they were comparing the times taken to duplicate an entire database versus snapshotting a thin clone, so of course the snapshotting was faster. :) Still, it was interesting to see the interface they were using, which I hadn’t seen in action before.

The next session was my first UKOUG presentation, which was on Snap Clone. I had heard from some other presenters that there had been some issues with getting the slides presenting properly, so I made sure that the room had one of the technicians ready. Unfortunately we had major dramas. Eventually even two technicians and myself had to admit defeat. Every time we tried to put PowerPoint into presenting mode, it would present on my laptop screen, rather than the big screen the attendees could see. The strange thing was when I connected to the projector it would display my desktop background but even when I tried to force the display onto the second monitor it would only come up on my laptop. The technicians finally got the slides onto the main display, but only in edit mode – if I tried to present from that configuration, PowerPoint would restart. So I presented the entire presentation in edit mode, losing all my build slides and so on, so it was incredibly frustrating. Adding to that frustration, both my slots were on in the rooms off the exhibition hall, which meant there was a lot of noise both from the hall itself and the adjoining rooms as I mentioned in yesterday’s blog. As I mentioned in that blog, I would certainly recommend those rooms NOT be used for presenting from again!

While the next session was on, I spent some time with the organizers of the conference and they borrowed a different laptop from the conference centre, so my second presentation worked very smoothly. A huge thanks to Faye Wood for managing to get that organized. My second session, titled “Best Practices for Deployment in a Private Cloud – Fast Track to DBaaS”, was the session I was asked to do just last Thursday, so it wasn’t listed in the printed agenda. It was on the agenda boards in the conference centre and the mobile app for the conference, so a few people realized it was on and turned up thankfully (I have heard of two presenters so far that had zero attendees, which must be incredibly disheartening for the presenters!)

That was the end of the day as far as sessions were concerned. There were drinks on in the exhibition hall again, and I spent some time catching up with Eric van der Sluis and Chris Lawless from DBvisit. It’s funny that I’d seen both of them just a couple of weeks ago on the other side of the world at the NZOUG Conference. :)

The conference finished on Tuesday with the Tech14 Social, but I took the opportunity instead to catch up with quite a number of my fellow Oak Table members over a few drinks and then dinner at the Smugglers Cove, a bar / restaurant just next door to the conference. A very enjoyable way to end the day!

Parse Time

Here’s a little query I wrote some time ago to see where my time went while running a query. It’s nothing sophisticated, just one of those simple things you can do with v$active_session_history (or dba_hist_active_sess_history, if you don’t get to the crime scene in time).

set null CPU

        sql_exec_id, in_parse, in_hard_parse, event, count(*)
        sql_id = '{your choice here}'
group by
        sql_exec_id, in_parse, in_hard_parse, event
order by
        sql_exec_id, in_parse, in_hard_parse, count(*)

SQL_EXEC_ID I I EVENT                                            COUNT(*)
----------- - - ---------------------------------------------- ----------
   40341649 N N db file sequential read                                68
   40341649 N N CPU                                                    21
   40341649 N N db file scattered read                                  9
            Y Y CPU                                                     7

I had run the query that I was tracking exactly once, but my ASH query allows for, and separates, multiple executions of the same query by summing on sql_exec_id (the thing that the SQL Monitor also uses). The last row looks a little odd, though: it does’t have a value for sql_exec_id; that’s because those are ASH samples when the query is being optimized, not being executed – note that I’ve reported the columns in_parse and in_hard_parse – and both are set to “Y” for that row.

So  (statistically speaking) it’s probably taken about 7 CPU seconds for Oracle to optimise the statement, and from the rest of the results you can see that it’s taken about 21 CPU seconds to run, with 68 seconds spent on random I/Os and 9 seconds spent on multiblock reads for a total of 103 seconds elapsed.

Seven seconds sounds like quite a lot of time for parsing – but it was a fairly complex statement. However, the reason I’d been running the statement on a test system (a fairly good clone of production) was that I’d been seeing something stranger on production and I needed to get a baseline on the test system before I starting trying to fix the problem. Here’s the equivalent ASH reports for the same statement when it had run on production at a time that allowed me to capture its ASH samples.

SQL_EXEC_ID I I EVENT                                            COUNT(*)
----------- - - ---------------------------------------------- ----------
  514257929 N N CPU                                                    21
  514257929 Y Y latch: row cache objects                                1
            Y Y CPU                                                   119

Note the 119 CPU seconds spent parsing to run a 22 second query ! But that wasn’t the worst of it – sometimes the results looked more like this:

SQL_EXEC_ID I I EVENT                                            COUNT(*)
----------- - - ---------------------------------------------- ----------
  523748347 N N db file sequential read                                 1
  523748347 N N CPU                                                    32
            Y Y resmgr:cpu quantum                                     11
            Y Y latch: row cache objects                               38
            Y Y CPU                                                   415

That’s a pretty catastrophic optimsation time – especially since the statement can be optimised in seven seconds in another environment. You might note the resource manager kicking in there, the session is exceeding the CPU limit set for its resource group – though not very often given how infrequently it seems to be waiting on “resmgr:cpu quantum”. But there’s another important wrinkle to this report – which you can see when compare v$active_session_history with v$sql.

  1  select
  2     sql_id,
  3     round(cpu_time/1000000,2) cpu,
  4     round(elapsed_time/1000000,2) ela from v$sql
  5  where
  6     sql_text like '{some identifying text}'
  7* and        sql_text not like '%v$sql%'
SQL> /

SQL_ID               CPU        ELA
------------- ---------- ----------
2atyuc3vtpswy     285.81     516.13

The figures from v$sql don’t match very well with the summed results from ASH which has a total sample of 497 seconds and a CPU sample of 447 seconds. I think I can live with a statistical error of 4% ((516-497)/516) in a random sample for total time, but how do you explain the 36% error in the CPU time ?

The samples reporting “resmgr:cpu quantum” are a clue: the machine is overloaded; it’s trying to use far more CPU time than is available. As a result a process that gets pushed off the CPU by the operating system scheduler while it’s running can spend a long time in the run queue waiting to start running again. And if it’s an Oracle process that got pre-empted it doesn’t “know” that it’s not running, it didn’t put itself into a wait state so all it “knows” is that it’s not in a wait state.

So how do ASH and v$sql differ ? The code that derives the cpu_time for v$sql issues a call to the O/S asking “how much CPU have I used”. The code that takes an ASH sample says: “is this session active, if so is it in a wait state and if it’s not in a wait state then it’s either on the CPU or in the run queue waiting for the CPU”. So when we compare v$sql with ASH the difference in CPU is (statistically speaking) time spent in the run queue. So of our 447 seconds of CPU recorded by ASH, we spent 161 seconds in the CPU run queue waiting for CPU.

We still have to account for the difference between the 7 CPU seconds on a test system and the variation between 119 CPU seconds and 415 CPU seconds in optimisation on production. In a word – concurrency. Apart from everything else going on at the time there were, in the worst case, 16 slightly different versions of the same statement being called at the same time (on a machine with 24 cores) – all 16 statement were competing violently for the same resources at the same time, and the escalating conflict as more session joined in produced an exponential growth in time spent competing for resources rather than doing the job. (I managed to demonstrate the effect quite nicely during the evening by limiting the batch to 4 concurrent executions – and got a typical parse time of 40 CPU seconds).

I’ve often warned people about the problems of concurrency and encouraged them to think about how much time is being spent in competition rather then doing the job; I think this is the most extreme case I’ve seen in a production system. Given how dramatic the variation is, I can’t help wondering if the problem has been exaggerated by some corner case of sessions spinning for mutexes or latches; perhaps even an error in the code that allows resource management to put a session into “resmgr:cpu quantum” while it’s holding a latch or mutex. (I wasn’t able to emulate such an extreme display of the problem on a slightly newer version of Oracle, but I was able to construct a test that demonstrated the effect with a much smaller wastage of CPU.)

The solution (almost certainly): the statements are extremely similar, varying in just one predicate that is using a literal constant. It ought to be a relatively safe and simple change to make the query use a bind variable in that predicate. If the solution is adopted I’d expect to see the (once only) parse time on production drop back to about 7 seconds. Of course, if any other session tries to call the same statement at the same time it ought to end up reporting 7 seconds waiting on “cursor: pin S wait on X” before it starts executing – but that 7 seconds wait is a lot better than an extra 493 CPU seconds trying to optimise the “same” statement at the same time.


Running a minor variation on my ASH query to report the sql_plan_hash_value along with the sql_exec_id, I found that the “fix-up” code that updates older ASH rows with information that only becomes available later (e.g. plan_hash_value when optimising, or long time_waited values for a single wait event) only goes back 255 rows – so when I queried ASH for the statements that took 500 seconds to optimizer only 255 of the in_parse rows showed the final sql_plan_hash_value.


Riga Dev Day 2015

Over the weekend I booked my flights and hotel for the Riga Dev Day on January 22nd.

It’s my first time in Latvia, so I look forward to seeing everyone out there!



Riga Dev Day 2015 was first posted on December 9, 2014 at 2:09 pm.
©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.

Oracle ACE Program : Thanks Folks!

As you will know from my previous post, I picked up the “UK Speaker Award” at UKOUG Tech 14. I’ve already done a bunch of thank you messages, but I’d just like to take a minute and thank the Oracle ACE Program for the part they have played in this.

Many of you will know, I was invited to the ACE program because of my online contributions to the Oracle community. Prior to joining the program I had not done any public speaking and had not been part of any user groups. I hadn’t even attended a SIG or a conference before. The first conference I ever attended was Oracle OpenWorld in 2006, where I picked up the “Oracle ACE of the Year” award. It was only from that point on that I started to present.

If I had not been invited to the program, I doubt I would have ever tried to present at an Oracle event. Even if I had taken the plunge, I doubt I would have done enough presenting to come away with a speaker award like this. The amount of people I’ve met over the years and the amount of advice they have given me on presenting has been invaluable.

I know I’m not the best presenter out there and I know I’ve still got a long way to go, but I honestly don’t believe I would have come this far without the help of the Oracle ACE Program, so many thanks to everyone involved in the program, both organisers and ACEs alike, for all your help over the last  8 years. It is very much appreciated!



Oracle ACE Program : Thanks Folks! was first posted on December 9, 2014 at 12:22 pm.
©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.

UKOUG Tech 14 : Monday

The day started with a taxi ride into New Street station to get the 06:01 train to Liverpool Lime Street. I was a little dissapointed that the train was a sprint train, so there were no tables and no power sockets. When the ticket guy came through I asked about an upgrade to first class and it was only £25 for the return journey, so I paid the extra to get a table and a power socket. Two 90 minute journeys are so much more bearable when you can actually do something useful on them. From Lime Street, it was a quick taxi ride to the ACC where I picked up my pass and headed straight to the room for my first session.

I was up in the first block of speakers at 09:00 for my virtualisation session. I expected a very small crowd for a virtualisation session in the first slot of an Oracle tech conference. I got into double figures, so I was happy. I got some questions at the end also, which is always nice.

After chatting to Peter Scott and Richard Foote about Richard’s fake tan and botox, I went to the speaker lounge and spent some time chatting to a variety of people. I also got my Christmas present from my dad. I was kind-of surprised and the size of the cheque, but I promised not to tell the wife or daughter how much he gave me! They are jealous types.

Next up was Robyn Sands speaking about “Why Solid SQL Still Delivers the Best ROI”. Being a database guy I guess it’s pretty obvious I’m going to agree entirely with what Robyn says. :) A few of the key points included:

  • Good database and application design pays off many times over.
  • Set-based processing in the database will always out pace row-by-row processing, either in the database or in the middle tier, for large data sets.
  • If you can’t use set-based processing, array processing typically beats row-by-row processing.
  • Threading to fake parallelism is not a great replacement for true database parallelism, but it can give advantages in some circumstances.

There are always exceptions to the rules, but bad programming gives bad performance. The test harness used for the demos is really neat. It really does hammer home the point in a very clear way!

SpeakerAwardSmallI chatted through lunch and didn’t actually make it to the food. :) After lunch it was off to the keynotes, where the UKOUG gave out some awards. I picked up a “UK Speaker Award” for last years session on “Improving the Performance of PL/SQL Function Calls from SQL”. The award was based on the speaker evaluations, which makes it feel kind-of special. I wasn’t too happy with my performance during that particular session last year, but obviously some of the folks in the audience felt different. Just goes to show you can’t be objective about your own performance. I’ll no doubt act all blasé about it, like it does’t matter, but I’ll secretly get a t-shirt printed to wear around the house. :) Thanks to everyone who filled in the evaluations. It is very nice to get a pat on the back like this from time to time…

The keynote overran and I was sitting near the front, so I was a bit nervous about leaving early. As a result, I was the last person to arrive at my session. My second session was an introduction to Analytic Functions, which seemed popular. The room was full and Tom claimed he was turned away. I think he was on the wind-up! It’s always better to have a smaller room that’s full, that a large room that feels empty. :) I was a bit “giddy” during this session. I think it was a combination of things including lack of food, adrenalin, excitement over the award, having a few friends in the audience and it being my last presentation of the year. I remember it feeling like a fun session for me. Not totally sure how that translates to the audience reaction though. As an example of my “giddiness”, about 3/4 of the way through the session I noticed Dawn on the front row, right in front of me, stopped and said something like, “Oh. You’re here!”. I must have looked like I was on crack! :)

During the Analytics session I try to break things down to the simplest possible terms, so this is more like a teaching session than a conference presentation. Analytics is something you just have to get your hands dirty with. No amount conference presentations will actually make you feel comfortable with them, so rather than trying to get heavy, I’m trying to get people over the first hurdle and get them started. The response from this session is usually pretty good, but I’m always worried someone will miss the word “introduction” mentioned in the abstract and turn up expecting some deep dive type session. It’s always good when you learn stuff during sessions you present and I learnt a couple of things thanks to Stew Ashton. Happy days!

After another chatting session in the speaker lounge, it was off to see dad speak about “AWR: Looking Beyond the Wait Events and Top SQL”. This meant missing Tom’s “AskTom” session, but family comes first! :) I really liked this session. The main point dad was making was people often jump into the top-5 wait events and try to fix the problem, without doing the necessary prep work first. Guilty! Instead, people should look at the information in the AWR report and try to build a picture of the system and application architecture, before trying to fix the problem. In many cases, this process will actually highlight the problems. This is not an exhaustive list, but the sort of things he highlighted were:

  • Check the spec of the hardware at the top of the report. Specifically the core count.
  • Check the number of sessions. Is it excessive compared to the core count. Ideally it should be 1-10 times the core count. More than that may represent a problem.
  • Is the number of sessions increasing during the sample time (start compared to end)? Is this normal or a session leak?
  • Is the number of open cursors excessive? Is it growing? Is this a cursor leak, which suggests bad application code?
  • On the load profile, is the DB CPUs per second is higher than the number of cores, you are CPU bound. This will probably make other waits “over-report” as they are really CPU bound. Maybe the contents of the top 5 waits is an artefact of being CPU bound. Can you trust them?
  • If there are a high number of rollbacks compared to transactions (commits), the application is doing lots of work only to throw it away. Bad application design.
  • Check out the init.ora settings for non-default and hidden parameters that look suspicious. Are these necessary or legacy?

Only after you’ve built up this picture, should you jump to the top 5 waits and the top SQL sections. Do they confirm your picture of the system built up from the previous investigation?

I’m pretty sure I will end up at the same conclusions when looking at an AWR report, but I think this approach is much better than the way I do things, so I will try to adapt and see how it goes. If you didn’t manage to see the session, you should download the slides and check them out. I think there is a lot of value there. Well done dad!

After the last session we headed off to the exhibition hall to socialise for a bit. I talked to a whole bunch of people, which is the best bit about conferences. I also got my first food of the day, a chocolate fish from the Dbvisit stand. :) I spent quite a bit of time talking to Jacco Landlust, who is always a great source of info. His advice has been incredibly valuable in plugging up the gaping holes in my middle tier knowledge.

At about 19:40 I shot off to the station and got on the 20:04 train home. A table and a power socket again. Awesome. I also managed to scrounge and extra strong mint of someone, which was the second meal of the day. :) I got to Birmingham at about 21:45 and stuffed some food down my face before getting a taxi home.

So that’s the last conference of the year. Let’s see what 2015 brings…

Thanks to my company for paying for the train ticket and letting me have the day off work as a “training day”, rather than holiday, especially having just got back from a three week “holiday” doing the OTN APAC tour. :) Thanks to the UKOUG for inviting me again and making sure everything ran smoothly. Thanks to all the attendees this year and to everyone who filled in their evaluations from last year. Cheques are in the mail. :) I’ll do a separate post about the ACE Program



UKOUG Tech 14 : Monday was first posted on December 9, 2014 at 11:56 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.

UKOUG Tech14 slides – Exadata Security Best Practices

I think 2 years is long enough to wait between posts!

Today I delivered a session about Oracle Exadata Database Machine Best Practices and promised to post the slides for it (though no one asked about them :). I’ve also posted them to the Tech14 agenda as well.

Direct download: UKOUG Tech14 Exadata Security slides

Day 1 of UKOUG Tech 14

Monday was the first day of the main part of the UKOUG Tech 14 conference, after SuperSunday’s additional content. I had a great night’s sleep and woke at 7 am, much to the disgust of Richard Foote who had been up for four hours already and didn’t seem to appreciate my #NoJetLagHere hashtag replying to his lack of sleep tweet. :) I managed to get to the conference relatively dry, but realized yet again that you don’t come to Liverpool in December for the weather. :)

The conference itself is at the Arena and Convention Centre in Liverpool. The conference and exhibition are spread over three floors of the conference centre, with the exhibition on floor 0 (it sounds better than the basement, I guess!) and most of the presentations and the speaker’s lounge on the 2nd floor. Some of the presentations are also held in rooms off the exhibition hall. The first session timeslot didn’t hold a lot to interest me specifically, so I went to the speaker lounge and caught up on a bit of email and the like. The second session included a presentation by Joze Senegacnik, a fellow Oak Table member that I had finally met in person the previous night at the Oracle ACE dinner. His presentation was titled “The Evolvement of Oracle Performance Troubleshooting”. When we met at the ACE dinner, he described his paper as a tour of performance since Oracle 2, so that sounded pretty interesting. Not many people have been around to use a lot of those early versions, so it’s always interesting to hear from someone who experienced just how limited some of that early stuff was!


Unfortunately, Joze’s presentation was in one of the rooms off the exhibition hall and was separated from another one of those rooms by a wall that didn’t reach the roof. The end result is that neither presenter could use a microphone (as they would have drowned out the presenter in the other room) and it was quite difficult to hear, particularly as my hearing isn’t the best anyway. Still, it gave me a heads-up of what it will be like for my two papers today, as both of them are in that same room. I think I’ll have a bit of an advantage over Joze as he is a fairly quietly spoken guy, whereas I’m much more of a “basso profundo”! :)

After Joze’s presentation I finally got to catch up with my lovely colleague Tania Le Voi. Tania and I had worked together in the group I was with in the Enterprise Manager product management team before I moved to the DBaaS team, so it was great to actually spend some together before sharing lunch with her, James Kao and Jeff Barber (also fellow product managers in the EM team. I also caught up with a customer who has been a reader of my blog, so that’s always fun! Thanks for the positive feedback, Jon! :)

Straight after lunch we had the EM Roundtable slot, which is particularly valuable to us as product managers as these sessions are where we get to hear directly from customers about what they’re doing with the EM product suite, what pain points they might still have and so on. Again, this session was in one of the rooms off the exhibition hall, and if there’s one piece of feedback I would offer the UKOUG Tech 14 organizers, it would be to definitely NOT use these rooms for roundtables, as I had a lot of difficulty hearing some of the issues customers were raising. In fact, it would be better to not use these rooms at all. Those of us who have a hearing loss will realize just how difficult it can be to hear in rooms with competing presenters! The other time when I had problems hearing were in sessions that were on the second floor. From time to time during the day, there were quite heavy periods of rainfall and even hail. I don’t know what sort of material the roof of the convention centre is made from, but the noise level when it was hailing was horrendous!

Unfortunately, I had to spend most of the rest of the afternoon working on my day job, which is one of the downsides of being at a conference. I managed to be finished in time for the exhibition drinks slot, which was held in the exhibition hall. Once again, I enjoyed catching up with a few people over drinks that I hadn’t had the chance to meet in person before. I had to leave after that, so I missed the community drinks session while I went off for dinner with some customers from Barclays, an event organized by Ian Carney, a colleague from my days back in the late 90’s / early 2000’s with Oracle in the US.

All in all, a fairly enjoyable day at UKOUG Tech 14. Looking forward to tomorrow when I have two presentations to do myself, as well as more time catching up with good friends old and new!

Oracle Security Training, 12c, PFCLScan, Magazines, UKOUG, Oracle Security Books and Much More

It has been a few weeks since my last blog post but don't worry I am still interested to blog about Oracle 12c database security and indeed have nearly 700 pages of notes in MS Word related to 12c security....[Read More]

Posted by Pete On 28/08/13 At 05:04 PM

Twitter Oracle Security Open Chat Thursday 6th March

I will be co-chairing/hosting a twitter chat on Thursday 6th March at 7pm UK time with Confio. The details are here . The chat is done over twitter so it is a little like the Oracle security round table sessions....[Read More]

Posted by Pete On 05/03/14 At 10:17 AM

PFCLScan Version 1.3 Released

We released version 1.3 of PFCLScan our enterprise database security scanner for Oracle a week ago. I have just posted a blog entry on the PFCLScan product site blog that describes some of the highlights of the over 220 new....[Read More]

Posted by Pete On 18/10/13 At 02:36 PM