I have been hanging around at OTN’s grid computing forums for quite a while now. I enjoy reading about other people’s problems with RAC technology, learn from the advice of other smart DBAs and draw some satifcation from helping out others in those areas I feel confident about.
And while the grid computing part of the OTN message board is rather small (compared to ‘Database/general’ for example) it is still enough to keep you occupied for a while when reading all threads. That’s why I came up with the idea of compiling a list with my favourite thread(s) each month. Please bear in mind that I do not claim any authority, a fair rating system or even having read all threads. I am not affiliated with OTN, just another fellow member. These are just my favourites, nothing more. There is no reward or benefit in being listed here. The only thing I can offer for those that contribute to my monthly favourites is to buy you a beer if you manage to track me down at a conference or SIG meeting.
I thought this would be easy and fun until I got to actually pick a single great thread. Anyway, this is what I came up with for the month of October:

ASM benefit in SAN environment is quite a general discussion about why you might prefer ASM over raw partitions or filesystems and scratches on the surface of recommendations for a good stripe size. I also liked the fact that quite a few people participated, a lot of the other threads are really mostly two-way discussions.

Another great answer was given in “Possible Splitbrain scenario” where Syed Zaheer links to an excellent series of articles on OCR mirroring by Geert De Paep.

I was tuning a query today that involved a couple of nested loops. I wanted to see the effect of different values for my bind variable(s) on the actual work being performed, so naturally autotrace was going to be the weapon of choice, especially since it is so convenient to use from SQLDeveloper. So I was stuffing my query with different variables, checked that the execution plan stayed the same (of course it did) and observed autotrace’s value for ‘consistent gets’. But something was odd. When I used a value that yielded no (or just very few) results for the driving part of the nested loop, the consistent gets were higher than with a value that yielded many results. After all, the work being done or blocks needed to be read should be dependant on how often I execute that loop. For an easy (and similar) example, look at this query with bind values of either ‘%x%’ or ‘%@%’:

SELECT *
FROM oe.orders o,
oe.customers c
WHERE c.customer_id = o.customer_id
AND c.cust_email LIKE :1

If I look for order by customers with an ‘x’ in their email address, I get 2 results and it takes 27 consistent gets. When looking for customers with an ‘@’ (all of them) I get 105 results but only 19 consistent gets are needed. I spent the next 60 minutes trying find an explanation for this behavior and really started to doubt my knowledge about Oracle. So I double-checked with autotrace from sqlplus which yielded the expected results: more rows – more CGs. So the problem had to be with SQLDeveloper and it was a rather easy find from there on. SQLDeveloper has a pagination with the default page size being set to 50. This basically means that it will only fetch the first 50 rows and wait for the user to scroll down before going on with the query. Which is a very good idea for most general purpose database work because you might now want to wait for all results to be fetched and transferred over the net.
But this poses a problem in combination with the built-in autotrace function because SQLDeveloper will stop (or rather pause) executing the query after the first 50 results. And autotrace can only output the work being done until that point. In the example above, this meant that with the ‘%x%’ predicate the outer or driving loop had to go through the whole full scan of the customers table. When given the other predicate, it had to execute the inner loop 50 times (a simple index lookup) and stopped after that, not even halfway ‘done’. The workaround for me was to increase the pagination size. You will find this at Tools->Preferences->Database->Advanced under ‘SQL Array Fetch Size’ but there seems to be an upper limit and I would also wish that they would either not display the wrong autotrace results or introduce an option that disables pagination when using autotrace.