Who's online

There are currently 0 users and 23 guests online.

Recent comments

July 2011

Cursor variable and global cursors security issues

I noticed a few days ago that David Litchfield had posted two new short papers on Oracle security; one is related to global cursors declared in PL/SQL packages, the other about cursor variable type SYS_REFCURSOR being passed out of functions/procedures....[Read More]

Posted by Pete On 06/07/11 At 01:20 PM

What is the purpose of segment level checkpoint before DROP/TRUNCATE of a table?

There was a very good question asked in Oracle-L list today, which has bothered me too in past.
The question was:
What is the purpose of a segment level checkpoint before DROP/TRUNCATE of a table?
In other words, why do we have to wait for the enq: RO – fast object reuse wait event (and in 11.2 the enq: CR – block range reuse ckpt wait) when dropping & truncating segments?
I’m not fully confident that I know all the real reasons behind this, but it could be related to the need to get rid of segment’s dirty buffers in buffer cache, before dropping the object.
Imagine this:
  • You have a large buffer cache and you drop table A without checkpointing the dirty buffers.  
  • Immediately after the drop succeeds (some buffers are still dirty in cache) some other segment (table B) reuses that space for itself and writes stuff into it.
  • A few seconds later, DBWR wakes up to find & write some dirty buffers to disk (anything it finds from its lists). As there are some old & dirty blocks of table A still in the cache, they get written to disk too, overwriting some of the new table B blocks!


So, this is one reason why you should checkpoint the blocks to disk before dropping (or truncating) a segment. Of course you might ask that why doesn’t DBWR just check whether the dirty buffer is part of an existing object or a dropped one when it walks through its dirty list? It could just discard the dirty buffers of dropped objects it finds. It would be doable – but I also think it would get quite complex. DBWR is a low level background proces, understanding the cache layer and dealing with physical datablocks in a file# = X block offset = Y. It doesn’t really know anything about the segments/objects which use these blocks. If it should start checking for logical existence of an object, it would have to start running code to access (a much higher level concept) data dictionary cache – and possibly query data dictionary tables via recursive calls, etc, so making it much more complicated.
So, this logic may just be matter of implementation, it’d be too complex to implement such selective discarding of dirty buffers, based on a higher-level concept of existence of a segment or object. Dropping and truncating tables so frequently, that these waits become a serious problem (consuming significant % of response time) indicate a design problem anyway. For example, former SQL server developers creating multiple temporary tables in Oracle – for breaking a complex query down into smaller parts, just like they had been doing it in SQL Server.
Anyway, here’s what I think about this – I’d love to hear other opinions, if you think otherwise!

The Dresden Files: Side Jobs

Side Jobs is a collection of short stories about characters from The Dresden Files series. Many of the stories have been published previously on the internet or in anthologies, so it’s not really new.

It’s kinda hard to describe the book because it contains a mix of stories of varying length and quality. Some are very weak and could be removed from the book at no loss. A couple are very good and show the characters they reference in a new light. One of the things with The Dresden Files series is it is all from the perspective of Harry, so you only know as much about other characters as Harry does. Some of these stories are told from other characters perspectives, so that quite literally changes your view point considerably.

If you are a Dresden fan I’m sure you’ll love it. If not, you will probably do well to avoid it.

BTW. The last story in the book takes place between the end of the Changes and the soon to be released Ghost Story. It kind of revved me up and got me exited about the new release again.



Wow !

How do you fancy a fantastic new feature that should have been introduced about 10 years ago, but only gets into 11.2 with a hidden enhancment patch ?

Iggy Fernandez reads the latest TPC-C report and is the first to announce range-partitioned clusters in Oracle 11.1.

(This is a temporary note, and will be deleted in a few days.)

Bitmap Indexes and Not Equal (Holy Holy)

Way back, I previously discussed how the CBO will simply ignore any possible indexes when determining the best execution plan involving a NOT EQUAL(<>) condition, even if an index might in theory provide the most efficient access path. Oracle just assumes that the vast majority of rows are likely to be returned and so doesn’t even [...]

WordPress 3.2 Released…

WordPress 3.2 has been released.

This is a big change and it is worth doing some testing before launching straight in. The minimum supported versions of mySQL and PHP have changed, so check your service can handle it using this plugin (Health Check).

You can read more about the changes in 3.2 here.

While writing this post in 3.2 I can see straight away that everything is much more responsive. No more waiting for the link dialog to appear etc. :)



Video – 2

This is just a temporary note to point you in a useful direction.

Oracle Corp has been launching videos onto YouTube over the last few months – and nobody is watching them !

Boris Dali (Oak Table colleague) has just sent me a link to a lovely little presentation (just 8 minutes 39 seconds) by Andy Holdsworth called “OLTP Performance, the Trouble with Parsing” – so I thought I’d pass the link on: here’s the URL for the whole channel, tag it, save it, a keep an eye on it.

Advanced RAC Training

I will be delivering an intense, advanced 2-week RAC training seminar in Aug 22-26 and Sep 19-23 with numerous demos, dumps and scripts. We will meet 4 hours per day, in a virtual world, 8AM-12 Noon Pacific time for those two weeks. Tanel has written has a great blog post about this seminar series Advanced RAC training.

If you like my presentations about RAC and performance tuning, you will love my seminar series. You will gain better understanding about RAC internals, and you will be able to advance your debugging and performance tuning skills attending my seminar series.

Join me and Let’s explore RAC further.

The First Exadata Virtual Conference in the World!

We have been secretly planning something with Kerry Osborne – and now it’s official – we will host The First Exadata Virtual Conference in the World, on 3-4 August 2011.

This conference takes place a couple of weeks after our Expert Oracle Exadata book is published (on 18. July – check out the awesome new cover design). So, we thought it’d be a good idea to run this conference, where we can explain some things in a different format, do live demos and answer questions that attendees have.

On the first day Kerry and Randy will talk about some serious fundamentals of Exadata, like how Exadata Smart Scan Offloading works and how to make the IO resource manager work for you (especially important in mixed workload consolidated environments).

And on the second day we’ll dig even deeper, with Andy Colvin talking about how to survive Exadata patching (he has patched more Exadatas than anyone else I know) and me following up with some complex performance troubleshooting stories I’ve encountered recently (trust me – there’s a LOT of issues to troubleshoot ;-)

About the Conference:

Since its release, Oracle Exadata quickly became a hit. Due to the relative “youth” of Exadata technology and internal behavior changes introduced with frequent patch-sets, there’s not much up-to-date quality technical information and know-how available to public. This virtual conference brings you a chance to learn from the leading Exadata experts, from their experience of working with real Exadata environments, from Exadata V1 to the latest X2-8. Additionally, there is plenty of Q&A time scheduled, so you can also get answers to your Exadata-related questions.

The speakers are probably some of the most experienced Exadata consultants in the world, in the field of Exadata deployment, migration, performance, and troubleshooting. Also, Kerry, Randy and Tanel are the authors of the #2970a6; text-decoration: none; padding: 0px; margin: 0px;\" href="">Expert Oracle Exadata book published by Apress in July 2011.


  • 3-4 August 2011


  • Online (or should I say “the Cloud” ;-)


  • 8am – 12pm (PST) on both days – 2 x 1.5h sessions on each day, with Q&A sessions and a break in between


  • Kerry Osborne, Randy Johnson, Andy Colvin from Enkitec
  • Tanel Poder from right here :-)

All of the speakers are hard-core hands-on professionals, having worked on many different real-life (production) Exadata environments of their clients. Enkitec dudes didn’t stop there, they bought a half rack for themselves, just for playing around with it. Yeah (+1 from me), some people buy a red hot Ferrari, some buy a red hot computer rack with an X on it :-)


  • 375 USD (early bird until 22. July), 475 regular price

More information, abstracts and registration:

I don’t think you’ll find an Exadata learning opportunity like this from anywhere else (and any time soon), especially considering the price!

This conference is so hot, that one of the attendees managed to sign up to it even before I had published this page to the world! :-)

The CURSOR_SHARING Parameter is a Silver Bullet – What is Wrong with this Quote?

July 3, 2011 (Modified July 5, 2011) Define Yah-but: Almost like yeah but, but with one missing letter. While reading the alpha edition of the “Oracle Database 11g Performance Tuning Recipes” book, specifically recipe 5-19, I found a couple of interesting comments about the CURSOR_SHARING parameter, specifically the FORCE and SIMILAR parameter values.  The quotes (as [...]