Oracle Scratchpad

October 4, 2013

Quiz Night

Filed under: Bugs,Oracle — Jonathan Lewis @ 3:08 pm BST Oct 4,2013

Okay, it’s a little early in the day (for me at least) to say “night” – but here’s a fun little detail I picked up in Prague yesterday. What do you think will happen when you try to execute the following two queries:


select 0/0 from dual;

select count(*) from (select 0/0 from dual);

I’ve only tried it on 11.2.0.4 and 12.1.0.1 – I could imagine the results might be different if you’re still running 8i or 9i.

If those are too easy, you might want to think about an example that Julian Dontcheff produced at OpenWorld:


select power(0,0) from dual;

What SHOULD the answer be, and what do you think Oracle will supply ?

October 2, 2013

FBI decode

Filed under: Bugs,CBO,Function based indexes,Indexing,Oracle — Jonathan Lewis @ 8:26 am BST Oct 2,2013

It probably won’t surprise many people to hear me say that the decode() function can be a bit of a nuisance; and I’ll bet that quite a lot of people have had trouble occasionally trying to get function-based indexes that use this function to behave properly. So (to put it all together and support the general directives that case is probably a better choice than decode() and that the cast() operator is an important thing to learn) here’s an example of how function-based indexes don’t always allow you to work around bad design/code. (Note: this is a model of a problem I picked up at a client site, stripped to a minimum – you have to pretend that I’m not allowed to fix the problem by changing code).

(more…)

August 28, 2013

Sorted Hash Clusters RIP

Filed under: Bugs,Infrastructure,Oracle — Jonathan Lewis @ 8:22 am BST Aug 28,2013

Sorted Hash Clusters have been around for several years, but I’ve not yet seen them being used, or even investigated in detail. This is a bit of a shame, really, because they seem to be engineered to address a couple of interesting performance patterns.

(more…)

August 15, 2013

MV Refresh

Filed under: Bugs,CBO,Infrastructure,Oracle,Statistics — Jonathan Lewis @ 6:12 pm BST Aug 15,2013

Here’s a funny little problem I came across some time ago when setting up some materialized views. I have two tables, orders and order_lines, and I’ve set up materialized view logs for them that allow a join materialized view (called orders_join) to be fast refreshable. Watch what happens if I refresh this view just before gathering stats on the order_lines table.

(more…)

August 6, 2013

12c subquery factoring

Filed under: 12c,Bugs,Oracle,Subquery Factoring,Tuning — Jonathan Lewis @ 8:08 am BST Aug 6,2013

I’ve written a few notes about anomalies in subquery factoring (with subquery) in the past, principally making a fuss about the fact that moving an inline view into a “with subquery” can cause a plan to change even when the internal code moves the subquery back in line. With the arrival of 12c one of my first sets of tests was to rerun all the examples to see how many of them had been addressed. I hadn’t written about as many examples as I had thought, and some of them had been fixed before 12c, but here are few references to a couple of outstanding items that I thought worth a mention:

[Further reading on "subquery factoring"]

June 28, 2013

12c Debug

Filed under: 12c,Bugs,Oracle — Jonathan Lewis @ 8:45 am BST Jun 28,2013

Now that 12c is out, here’s an idea that might save you some time even if you have no intention of migrating to, or even testing, the product for a couple of years. Download the “List of bugs fixed in 12c”: you may find that it’s the best starting point when you’re trying to solve a problem in your current version of Oracle.

A slightly more sophisticated version of the same thing – download and install the product, then take a dump of v$system_fix_control – that may also give you some insight into anomalies (that are not necessarily declared as bugs) in the way Oracle – and the optimizer in particular – behave. I updated the referenced note to add in a couple of figures for 12.1 – but one figure that’s not there is the number of database parameters: now at 368 in the v$ and 3,333 in the x$ (in my Beta 3 release).

June 21, 2013

Invisible ?

Filed under: Bugs,Hints,Indexing,Oracle — Jonathan Lewis @ 7:14 am BST Jun 21,2013

I’ll probably have to file this one under “Optimizer ignoring hints” – except that it should also go under “bugs”, and that’s one of the get-out clauses I use in my “hints are not hints” argument.

Sometimes an invisible index isn’t completely invisible.

(more…)

June 12, 2013

Not In Nasty

Filed under: Bugs,CBO,Oracle — Jonathan Lewis @ 5:31 pm BST Jun 12,2013

Actually it’s probably not the NOT IN that’s nasty, it’s the thing you get if you don’t use NOT IN that’s more likely to be nasty. Just another odd little quirk of the optimizer, which I’ll demonstrate with a simple example (running under 11.2.0.3 in this case):

(more…)

June 10, 2013

Metadata bug

Filed under: Bugs,Oracle — Jonathan Lewis @ 6:42 pm BST Jun 10,2013

Here’s a funny little bug – which probably won’t cause any damage – that may remind you that (most of) the people who work for Oracle are just ordinary people like you and me who make ordinary little mistakes in their programming. It’s a bug I discovered by accident because I just wanted to check something about how a particular undo tablespace had been defined, and I called dbms_metadata instead of querying dba_tablespaces. Here’s the cut-n-paste from an SQL*Plus session on 11.2.0.2:

(more…)

June 4, 2013

ROWID

Filed under: ANSI Standard,Bugs,lateral view,Oracle — Jonathan Lewis @ 9:09 am BST Jun 4,2013

Here’s a suggestion to help you avoid wasting time. If you ever include the rowid in a query – not that that should happen very commonly – make sure you give it an alias, especially if you’re using ANSI SQL. If you don’t, you may find yourself struggling to work out why you’re getting an irrational error message. Here’s an example that appeared recently on the OTN forum, with the output cut-n-pasted from a system running 11.1.0.7:

select 
	'1' 
from 
	dual a
left join 
	(
	select	c.dummy, b.rowid
	from	dual b
	join	dual c
	on b.dummy = c.dummy
	) d
on 	a.dummy = d.dummy
;

select
*
ERROR at line 1:
ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table

The error doesn’t really seem to fit the query, does it?
If you want to bypass the problem all you have to do is give b.rowid (line 7) an alias like rid.

As far as I can tell, the problem arises from a defect in the code that Oracle uses to transform the query before optimising it. Given the nature of the transformation it’s possible that you’re only going to see the problem manifest like this if the SQL uses ANSI forms; in this particular case changing from the ANSI left outer join syntax to Oracle’s standard (+) syntax also bypasses the problem. To narrow down the cause, I simply enabled event 10053 (the optimizer trace) and got lucky. The trace file showed an interesting “unparsed SQL” statement which, stripped of double-quote marks and re-formatted, was as follows (in 11.1.0.7 – this may vary with version of Oracle):

SELECT 
	A.DUMMY QCSJ_C000000000600000,
	from$_subquery$_007.DUMMY_0 QCSJ_C000000000600001,
	from$_subquery$_007.ROWID_1 ROWID 
FROM 
	SYS.DUAL A, 
	LATERAL( 
		(
		SELECT 
			D.DUMMY DUMMY_0,
			D.ROWID ROWID_1 
		FROM	(
			SELECT 
				from$_subquery$_005.QCSJ_C000000000500001_1 DUMMY,
				from$_subquery$_005.QCSJ_C000000000500003_2 ROWID 
			FROM	(
				SELECT 
					B.DUMMY QCSJ_C000000000500000,
					C.DUMMY QCSJ_C000000000500001_1,
					B.ROWID QCSJ_C000000000500003_2 
				FROM 
					SYS.DUAL B,
					SYS.DUAL C 
				WHERE 
					B.DUMMY=C.DUMMY
				) from$_subquery$_005
			) D 
		WHERE
			A.DUMMY=D.DUMMY
		)
	)(+) from$_subquery$_007

Note the alias of ROWID appearing in lines 4 and 15. If you tried to run this SQL from the command line (after fiddling the event to enable lateral() views), or even just the simple select running from lines 13 to 26, you would get error ORA-00923: FROM keyword not found where expected. My guess is that the context in which the optimisation takes place means that this error is re-raised as the error ORA-01445 that we ultimately see.

April 16, 2013

systimestamp

Filed under: Bugs,Oracle,Troubleshooting — Jonathan Lewis @ 12:46 am BST Apr 16,2013

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>

(more…)

March 29, 2013

Missing SQL

Filed under: Bugs,Oracle,Statspack,Troubleshooting — Jonathan Lewis @ 9:35 am BST Mar 29,2013

From time to time I’ve looked at an AWR report and pointed out to the owner the difference in work load visible in the “SQL ordered by” sections of the report when they compare the summary figure with the sum of the work done by the individual statements. Often the summary will state that the captured SQL in the interval represents some percentage of the total workload  in the high 80s to mid 90s – sometimes you might see a statement that the capture represents a really low percentage, perhaps in the 30s or 40s.

You have to be a little sensible about interpreting these figures, of course – at one extreme it’s easy to double-count the cost of SQL inside PL/SQL, at the other you may notice that every single statement reported does about the same amount of work so you can’t extrapolate from a pattern to decide how significant a low percentage might be. Nevertheless I have seen examples of AWR reports where I’ve felt justified in suggesting that at some point in the interval some SQL has appeared, worked very hard, and disappeared from the library cache before the AWR managed to capture it.

Now, from Nigel Noble, comes another explanation for why the AWR report might be hiding expensive SQL – a bug, which doesn’t get fixed until 12.2 (although there are backports in hand).

March 20, 2013

Lock Bug

Filed under: Bugs,deadlocks,Locks,Oracle — Jonathan Lewis @ 12:08 pm BST Mar 20,2013

Here’s an oddity that I ran into a little while ago while trying to prepare a sample trace file showing a particular locking pattern; it was something that I’d done before, but trace files can change with different versions of Oracle so I decided to use a copy of 11.2.0.2 that happened to be handy at the time to check if anything had changed since the previous (11gR1) release. I never managed to finish the test; here are the steps I got through:
(more…)

January 17, 2013

dbms_xplan bug

Filed under: Bugs,dbms_xplan,Oracle — Jonathan Lewis @ 6:22 pm BST Jan 17,2013

Here’s a very long post (which is mainly an example) demonstrating a little bug in the “explain plan” functionality. It’s a variation of a bug which I thought had been fixed in 11g, but it still appears in some cases. Take a look at this execution plan, which comes from explaining “select * from dba_tab_cols” – the bit I want to emphasise is in lines 1 to 10:

(more…)

January 6, 2013

Blog advert

Filed under: Bugs,CBO,Function based indexes,Indexing,Oracle — Jonathan Lewis @ 9:10 pm BST Jan 6,2013

Just a quick note to say that I found a blog over the weekend with a number of interesting posts, so I thought I’d pass it on: http://www.bobbydurrettdba.com/

There’s a really cute example (complete with test case) of an optimizer bug (possibly only in 11.1)  in the December archive: http://www.bobbydurrettdba.com/2012/12/04/index-causes-poor-performance-in-query-that-doesnt-use-it/

« Previous PageNext Page »

The Rubric Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,507 other followers