Search

Top 60 Oracle Blogs

Recent comments

November 2010

Different Performance from Standard Edition and Enterprise Edition? 4

November 24, 2010 (Back to the Previous Post in the Series) The previous articles in this series showed potential differences in performance between the Enterprise Edition of Oracle Database and the Standard Edition, as well as changes from release 10.2.0.5 to 11.2.0.1 for the same edition, even when the execution plans appeared to be the [...]

Belgrade, Serbia: Day -1

I usually wake up far too early before a trip, then spend ages waiting for a taxi. Today I was lying in the bath when the taxi came. This happened to me once before when I fell asleep in the bath. Today I didn’t fall asleep, but the time just disappeared. Maybe I was abducted by aliens for 30 minutes. I heard a knock on the door and panic ensued…

As I get older I enjoy taxi rides more and more. Taxi drivers are notorious for moaning and now when I get in a taxi I feel totally in my element. I can just moan and bitch about life, the Universe and everything. Throw in a couple of, “They’ll be first against the wall when the revolution comes”, and my morning is complete. :)

Today’s flight is via Paris, which for some reason means I go from the domestic terminal in Birmingham. I guess if nothing else it gives me a change of scenery. I’ve seen far too much of the international terminal recently. I have one hour to change in Paris and I don’t have my boarding pass for the second leg yet. I don’t think I’ve ever been through CDG before, so I’m not sure if this is a reasonable amount of time to transfer or not. Time will tell.

Update 1: I’m going through the Paris transfer now. The Air France guys are telling me to go to the boarding gate without a boarding pass. You can’t get to the terminal without a boarding pass, but I have to get to the terminal to have my boarding pass printed. Hmrgh… As much as I hate the world perception of the English, acting like an bumbling idiot (Hue Grant style) does help at times like this. If you look clueless long enough their superiority complex kicks in and they help you out of sympathy. While they are smugly congratulating themselves on how thick the English are I’m mentally putting my little finger to the corner of my mouth and saying “Muhahaha” in a DR Evil style. :)

Update 2: There was a little drama at the boarding gate. The Jat Airways people told me I needed to go back to the transfer desk and get a boarding pass. I told them what the Air France guys had said and slipped seamlessly into the stupid English man act for the second time today. Problem solved. Muhahaha… :)

Update 3: I’m now in Belgrade, but my bag isn’t. I’ve got everything I need to teach tomorrow, except clean clothes. Nothing makes a first impression like that stale aeroplane and airport smell. Nice… I’m going to see if I can pick up a couple of things tonight without hitting my credit cards too hard. I have no idea what proces are like over here.

Cheers

Tim…

Logica Guru4Pro – Amstelveen

Yesterday I returned from a trip to Amsterdam where I presented about Grid Infrastructure 11.2 as part of Logica’s Guru 4 Pro series. I have to say it has been a very pleasant experience! And it marked the first time I presented outside the UK as well.

Logica Holland runs a series of events where renowned experts present about the latest and greatest developments in their field. I was very pleased getting an invitation to the series, and gladly accepted. I opted to give the audience a “close look at Grid Infrastructure”. I think Oracle University would have termed it a “Deep Dive”, and a deep dive it was!

The flight from London Gatwick, my “home” airport to Amsterdam is very short indeed, I had the feeling I spent more time on taxiways than in the air. Upon arrival I was picked up by Dennis van Onselen, Logica’s Practice Manager. I really appreciate not having had to resort to a taxi to the venue!

Before the main event kicked off at 18:30 in the evening there was time for a session with Logica employees. For about two and a half we went through pros and cons of various technologies in the Oracle portfolio, and had a really good discussion along the way. I hope the attendees found it useful.

After a short break, I started my talk which was well received by the audience. I recognised some familiar faces in the audience, and was very pleased to also see Piet de Visser who I haven’t met all year and who introduced me to Anjo Kolk-I didn’t know he was part of the audience as well. After the presentation which was the longest I gave so far (around 90 minutes) we had a great discussion about the contents and high availability strategies in general. I felt a little strain on my voice, even though I was mike’d up after a cold I was suffering from at the weekend. I can fully understand singers now who can’t make it to their concert. But I’m back to normal now, hoping it won’t repeat itself during next week’s UKOUG conference.

One of the really good things that came out of this session was the prospect of returning to Holland for more presentations about RAC and all things around it. I would be delighted to return, anyone interested please drop me a line. And that of course includes Miracle!

By the way, I have converted the presentation to a PDF, and it can be downloaded here.

11g Virtual Columns and Fast Refreshable Materialized Views (What In The World)

Previous to Oracle 11g Rel 2, two very common and useful features previously worked well together, they being fast refreshable materialized views and the introduction of virtual columns due to the creation of function-based indexes.   To illustrate, we create and populate a little demo table:    We now create a simple little function-based index:     [...]

Oracle Database 11g Interactive Quick Reference

Just a quick note about the Oracle Database 11g Interactive Quick Reference That was released today to the public. This Interactive Quick Reference, created by Oracle University and the Server Technology Curriculum group, is your Essential Guide to Oracle Database 11g Release 2. This interactive quick reference includes an architecture diagram, a comprehensive list of […]

Distributed Queries – 2

I have often said that the optimizer “forgets” that it is dealing with a distributed query once it has collected the stats that it can about the objects in the query, and that as a consequence the driving site for a distributed query will be the local database unless you use the /*+ driving_site */ hint to change it.

While investigating an oddity with a distributed query between two 11.1.0.7 databases a few days, I noticed something in the 10053 trace file that made me change my mind, and go back to look at earlier versions of Oracle.

Here are two sections extracted from a 10053 trace file running under 10.2.0.3 with CPU costing (system statistics) enabled:

SINGLE TABLE ACCESS PATH
  Table: T1  Alias: AWAY
    Card: Original: 3240  Rounded: 41  Computed: 40.50  Non Adjusted: 40.50
  Access Path: TableScan
    Cost:  53.22  Resp: 53.22  Degree: 0
      Cost_io: 53.00  Cost_cpu: 2073815
      Resp_io: 53.00  Resp_cpu: 2073815
  Access Path: index (AllEqRange)
    Index: 0
    resc_io: 4.00  resc_cpu: 29536
    ix_sel: 0.0125  ix_sel_with_filters: 0.0125
    Cost: 4.00  Resp: 4.00  Degree: 1
  Remote table cost added, new values: cost 4.00  resc 4.00  resp .2f  Best:: AccessPath: IndexRange  Index: 0        <<===
         Cost: 4.00  Degree: 1  Resp: 4.00  Card: 40.50  Bytes: 0

...

  HA cost: 50.54
     resc: 50.54 resc_io: 50.00 resc_cpu: 5187262
     resp: 50.54 resp_io: 50.00 resp_cpu: 5187262
  Cost adjustment for NL join with remote table: 0.72             <<===
Join order aborted: cost > best plan cost
***********************

Note the two lines with the reference to “remote” (I’d highlight them properly, but you can’t do highlighing and code in the same text). Notice, also that one of the programmers made a bit of a mistake with their printf() call in the first of the lines – a bug that is still there in 11.1.0.6

Clearly Oracle is doing some arithmetic relating to the costs of accessing distributed data from at least 10.2.0.3 (there was nothing similar in the equivalent trace file for 9.2.0.8, and I don’t have a 10.1 available for testing). Unfortunately I have yet to see a single distributed execution plan where it does the right thing – but that might be a problem related to histograms (and the failure to use them) rather than a defect in the algorithms for distributed cost.

I’ll have to spend some time looking at what it does before I can write any more about it – but given the number of times I’ve said the optimizer doesn’t do any arithmetic I thought it was important to point out that I was wrong as soon as I discovered the change.

Footnote: I have added a category “distributed” to my list of categories – and added a link to it at the bottom of every article I’ve written about distributed SQL. That’s a pattern that I may copy across other articles in the future – especially if I can find out how to order the articles by date (ascending).

[Further reading on distributed databases]

Asynch descriptor resize wait event in Oracle

A lot of people have started seeing “asynch descriptor resize” wait event in Oracle 11gR2. Here’s my understanding of what it is. Note that I didn’t spend too much time researching it, so some details may be not completely accurate, but my explanation will at least give you an idea of why the heck you suddenly see this event in your database.

FYI, there’s a short, but incomplete explanation of this wait event also documented in MOS Note 1081977.1



The “direct path loader” (KCBL) module is used for performing direct path IO in Oracle, such as direct path segment scans and reading/writing spilled over workareas in temporary tablespace. Direct path IO is used whenever you see “direct path read/write*” wait events reported in your session. This means that IOs aren’t done from/to buffer cache, but from/to PGA directly, bypassing the buffer cache.



This KCBL module tries to dynamically scale up the number of asynch IO descriptors (AIO descriptors are the OS kernel structures, which keep track of asynch IO requests) to match the number of direct path IO slots a process uses. In other words, if the PGA workarea and/or spilled-over hash area in temp tablespace gets larger, Oracle also scales up the number of direct IO slots. Direct IO slots are PGA memory structures helping to do direct IO between files and PGA.



In order to be able to perform this direct IO asynchronously, Oracle also dynamically scales up the number of OS asynch IO descriptors, one for each slot (up to 4096 descriptors per process). When Oracle doesn’t need the direct IO slots anymore (when the direct path table scan has ended or a workarea/tempseg gets cancelled) then it scales down the number of direct IO slots and asynch IO descriptors. Scaling asynch IO descriptors up/down requires issuing syscalls to OS (as the AIO descriptors are OS kernel structures).



I guess this is supposed to be an optimization, to avoid running out of OS AIO descriptors, by releasing them when not they’re not needed, but as that Metalink note mentioned, the resize apparently sucks on Linux. Perhaps that’s why other ports also suffer and have seen the same wait event.



The “asynch descriptor resize” event itself is really an IO wait event (recorded in the wait class Other though), waiting for reaping outstanding IOs. Once this wait is over, then the OS call to change the amount of asynch IO descriptors (allocated to that process) is made. There’s no wait event recorded for the actual “resize” OS call as it shouldn’t block.



So, the more direct IO you do, especially when sorting/hashing to temp with frequent workarea closing/opening, the more of this event you’ll see (and it’s probably the same for regular tablespace direct path IO too).



This problem wouldn’t be noticeable if Oracle kept async io descriptors cached and wouldn’t constantly allocated/free them. Of course then you may end up running out of aio descriptors in the whole server easier. Also I don’t know whether there would be some OS issues with reusing cached aio descriptors, perhaps there is a good reason why such caching isn’t done.



Nevertheless, what’s causing this wait event is too frequent aio descriptor resize due to changes in direct IO slot count (due to changes in PGA workarea/temp segment and perhaps when doing frequent direct path scans through lots of tables/partitions too).



So, the obvious question here is what to do about this wait event? Well, first you should check how big part of your total response time this event takes at all?



  1. If it’s someting like 1% of your response time, then this is not your problem anyway and troubleshooting this further would be not practical – it’s just how Oracle works :)
  2. If it’s something like 20% or more of your response time, then it’s clearly a problem and you’d need to talk to Oracle Support to sort out the bug
  3. If it’s anything in between, make sure you don’t have an IO problem first, before telling that this is a bug. In one recent example I saw direct path reads take over a second on average when this problem popped up. The asynch descriptor resize wait event may well disappear from the radar once you fix the root cause – slow IO (or SQL doing too much IO). Remember, the asynch descriptor resize wait event, at least on Linux, is actually an IO wait event, the process is waiting for outstanding IO completion before the descriptor count increase/decrease can take place.

Share

Asynch descriptor resize wait event in Oracle

A lot of people have started seeing “asynch descriptor resize” wait event in Oracle 11gR2. Here’s my understanding of what it is. Note that I didn’t spend too much time researching it, so some details may be not completely accurate, but my explanation will at least give you an idea of why the heck you suddenly see this event in your database.
FYI, there’s a short, but incomplete explanation of this wait event also documented in MOS Note 1081977.

Asynch descriptor resize wait event in Oracle

A lot of people have started seeing “asynch descriptor resize” wait event in Oracle 11gR2. Here’s my understanding of what it is. Note that I didn’t spend too much time researching it, so some details may be not completely accurate, but my explanation will at least give you an idea of why the heck you suddenly see this event in your database.
FYI, there’s a short, but incomplete explanation of this wait event also documented in MOS Note 1081977.

NFS and IO testing : Bonnie++

Been spending time lately testing out and trying to tune NFS mounts.
For testing IO, I've been using
  • dd
  • iozone
  • bonnie++
  • orion
This first option, dd, is fine for file creation tests and/or sequential reads but less flexible for random reads or random writes
To test random reads I thought I'd use iozone but don't see a solely random read test.
Then I thought I'd use the Oracle supplied tool, orion, to do random read test on an NFS mounted file system, but this doesn't work, at least on AIX 6.1, with my mount settings.