Search

Top 60 Oracle Blogs

Recent comments

March 2012

Editing Hints in Stored Outlines

Introduction 
This note describes changing a hint in a SQL Outline by updating the OUTLN.OL$ and OUTLN.OL$HINTS tables directly. It shows that in the limited test case that it does affect the resulting execution plan.  

CAVEAT: This is a hack, so this is more of an oddity that something that I would recommend for use in production. I am only interested in it for the limited purpose of adding a NO_EXPAND hint to an outline, that would not otherwise be included if the outline was generated in the conventional manner.

Why Outlines? 

Add Constraint

Here’s a quirky little detail that may make you think carefully about how you define and load large tables.
I have a large table which I load with data and then apply the following:

alter table t_15400 modify (id not null, small_vc not null);

Would you really expect to find Oracle doing two tablescans on the table to enable these constraints ? This is what I found in a trace file (with a lot of db file scattered read waits and other stuff in between) when I ran the test recently on 11.2.0.3:

select /*+ all_rows ordered */ A.rowid, :1, :2, :3 from "SYS"."T_15400" A where( "ID" is null)
select /*+ all_rows ordered */ A.rowid, :1, :2, :3 from "SYS"."T_15400" A where( "SMALL_VC" is null)

It’s just a little difficult to come up with a good reason for this approach, rather than a single statement that validates both constaints at once.

Recent Oracle 8-Socket Xeon E7 TPC-C Result. Big NUMA Box, No NUMA Parameters.

I’ve read through the full disclosure report from Oracle’s January 2012 TPC-C. I’ve found that the result was obtained without using any NUMA init.ora parameters (e.g., enable_NUMA_support). The storage was a collection of Sun x64 servers running COMSTAR to serve up F5100 flash storage. The storage connectivity was 8GFC fibre channel. This was a non-RAC result with 8s80c160t Xeon E7. The only things that stand out to me are:

Vote Peter Robson!

The UKOUG Council elections are in progress and I think User Groups are a critical part of the community. Peter Robson is both a friend and the type of person I admire and want to represent me within the UKOUG but, sadly, the grumpy old sod is possibly the least Web 2.0 chap I know so, concerned that his campaigning efforts might be hindered, I asked him if he'd like to make his pitch here. Over to you, Peter ....


Thanks to Doug for giving me this impromptu platform for a bit more shouting about my candidacy for the Council of the UK Oracle Users Group. It’s only right, as it was he who first persuaded me to stand for the Board (as it then was) all those years ago.

Join Views, ROWIDs And Query Transformations

Here is an odd little bug that was discussed a couple of weeks ago on the OTN forums.

It's about queries on join views by ROWID that fail with "ORA-01410: invalid ROWID" under certain circumstances. The bug can only be reproduced when using the 11.2 code base. In fact the same setup will cause an internal error in 11.1, but 10.2 will return correct results.

It's probably not a very common scenario but it is an interesting example of how features that work fine by themselves can cause problems when used together.

First of all (hopefully) some of you may ask: How is it possible to query from a join view by ROWID, since the view is based on multiple objects and hence doesn't have a simple one-to-one mapping to a ROWID of a single table?

Oracle XML Training With Marco Gralike

Oracle XML Training With Marco Gralike
Event date: 
Tue, 2012-03-27 - Wed, 2012-03-28

With great pleasure we announce a very interesting 2-day seminar with Marco Gralike about XML titled "Oracle XML Database Training". The course will deal in depth with performance problems, design, set-up, architecture and troubleshooting.
Marco Gralike, member of OakTable and Oracle ACE Director si know as the top expert for XML in Oracle database.
The seminar will take place on March 27-28 2012 in Ljubljana, Slovenia, at Poslovna hiša Unija, Tržaška cesta 515, 1251 Brezovica pri Ljubljani.   The seminar is organized by DbProf d.o.o. in cooperation with SIOUG, Slovenian Oracle User Group.

Advert: Oracle XML Training With Marco Gralike

I was asked by Jože Senegačnik, if I would be would be interested in doing a Masterclass/Seminar in Slovenia and, yes of course, I really liked the idea. So after having a quick look in my agenda, regarding my free time, we started to set things up. This 2 day seminar will take place the

Read More…