Oracle

A couple of weeks ago I started a competition to win 4 copies of Managing Multimedia and Unstructured Data in the Oracle Database byMarcelle Kratochvil. Thanks to Packt for donating the prizes. The competition closed today and the lucky winners are:

• Kim Berg Hansen
  
• Stuart Uren
• Steve (dnunknown)
• Bayu Satria Setiadi

Once I’ve published this post I’ll be sending your email address to my contact at Packt, who will contact you to deliver your e-book.

One of the developers was performing a delete using a nested subquery, which was throwing out ORA-00600 errors. The ORA-00600 Lookup Tool on MOS suggested it was caused by this bug:

• Bug 14347248  ORA-600 [kkqjpdGetContext:1] parsing SQL with subquery

The workaround in the bug suggested running this ALTER SESSION command.

alter session set "_optimizer_unnest_disjunctive_subq"= FALSE;

Rather than alter the whole session, we used the OPT_PARAM hint, making the subquery look something like this.

This might be something very obvious for the reader but I had an interesting revelation recently when implementing parallel_degree_limit_p1 in a resource consumer group. My aim was to prevent users mapped to a resource consumer group from executing any query in parallel. The environment is fictional, but let’s assume that it is possible that maintenance operations for example leave indexes and tables decorated with a parallel x attribute. Another common case is the restriction of PQ resource to users to prevent them from using all the machine’s resources.

This can happen when you perform an index rebuild for example in parallel to speed the operation up. However the DOP will stay the same with the index after the maintenance operation, and you have to explicitly set it back:

#38761d;">[Updated on 4/20/2013 after feedback from #38761d;">Charles Hooper#38761d;">, #38761d;">Jonathan Lewis#38761d;">, #6aa84f;">Lau#38761d;">rent Schneider and #38761d;">Mohamed Houri#38761d;"> and with some minor cosmetic enhancements of outputs]

Here's part two!

I just got permission from The UK Oracle Users Group to reproduce my article series on optimising scans in Oracle. Part One is available here, Part Two will follow shortly after, and then Part Three will be a few weeks away, following its publication in the magazine. Enjoy!

For your entertainment – there’s nothing up my sleeves, this was a simple cut-n-paste after real-time typing with no tricks:

20:39:51 SQL> create table t1 (t1 timestamp);

Table created.

20:39:55 SQL> insert into t1 values(systimestamp);

1 row created.

20:39:59 SQL> select t1 - systimestamp  from t1;

T1-SYSTIMESTAMP
---------------------------------------------------------------------------
+000000000 04:59:50.680620

1 row selected.

20:40:08 SQL>

My laptop runs Oracle so quickly that it took only 4 seconds for 5 hours to elapse !
11.2.0.3 on 64-bit Linux – the client is running with TZ=EST5EDT, while the server is running UK Time (currently BST (GMT+1))

Check out the extensive slide deck (over 500 slides) about upgrading techniques to Oracle 11.2, by Oracle Corp (Roy Swonger and Mike Dietrich):

• https://blogs.oracle.com/UPGRADE/entry/new_version_of_the_huge

It has lots of examples (from real customer upgrade cases) in it.

Thanks to Randolf Geist for telling me about this.

VirtualBox 4.2.12 has just been released. The downloads and changelog are in the usual places.

Happy upgrading!

Cheers

Tim…

---

VirtualBox 4.2.12… was first posted on April 13, 2013 at 2:32 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.

Here’s a deadlock graph that might cause a little confusion: