Page 1 of 2
Oracle Update SQL Explain Plan
Posted: Wed Dec 02, 2009 4:51 am
by algfr
Hello people,
I have a simple job which retrieves 5000 update records out of a dataset and apply them to a target Oracle database using an update only mode.
It is extremely slow
I'm running the update statement on a single key which has a unique index on it. It has been also previously analyzed. I tried running it on one node and got the same result
What i find strange is that the update statement generated has a wrong explain plan. When i monitor the sessions when running, i can see the query and it performs a full scan on the table... If I capture the query and paste it on a sql view to see the explain plan, then I get a index rowscan.
It is strange. I tried using a /*+ RULE */ hint but got the same result.
Anybody had the same issue ?
Thanks for helping
Arnaud
Posted: Wed Dec 02, 2009 4:59 am
by ray.wurlod
Is it still slow if you take Oracle out of the picture, perhaps replacing the Oracle stage with a Copy stage for the purposes of testing?
Posted: Wed Dec 02, 2009 6:13 am
by algfr
Hi Ray,
No it's very fast.
The problem comes out of the Database for sure
it performs a full scan with a cost of 40,000 whereas it should do an index row scan (the cost would be only 4 !!!!)
Posted: Wed Dec 02, 2009 6:45 am
by algfr
Hi,
I've just found something interesting:
My update key is called INV_BK, hence I'm passing ORCHESTRATE.INV_BK in my where clause
If i edit the update statement by replacing this value by a constant, the explain plan becomes correct and it used the index !!
Posted: Wed Dec 02, 2009 9:21 am
by chulett
There is an INDEX hint to force Oracle to use the index you specify. Seems like worst case you could use that.
Posted: Wed Dec 02, 2009 11:03 am
by algfr
Will try...
Does this problem rings a bell to someone ?
Posted: Thu Dec 03, 2009 6:28 am
by algfr
Wow, I tried the same job with a server method and it ran so much faster.
I've read this issue here befor but it seems so crazy to me...
How can Server be faster than parallel ??
Posted: Thu Dec 03, 2009 7:31 am
by chulett
It really depends on what you're doing but you'd be amazed what Server can do in the right hands.
I'm assuming here Server is not confusing Oracle and the explain plan is correct, hence the proper speed. The question still remains why that ORCHESTRATE syntax is problematic. Have you checked with your official support provider? Asked if it is a known issue?
Posted: Thu Dec 03, 2009 10:17 am
by algfr
Hi Craig,
My official support is ... IBM. I don't know if they can help me in this case but I can try.
Do you think the difference could be cause by a misuse of the OCI stage (in EE we use Oracle Enterprise).
I wonder if there is a configuration file of some sort.
Posted: Thu Dec 03, 2009 10:29 am
by chulett
Official support doesn't get much more official than IBM.
Not sure what you mean by 'a misuse of OCI'.
Posted: Thu Dec 03, 2009 11:13 am
by algfr
Sorry mister
I meant the OCI Oracle Stage in Server against the Oracle Enterprise for Parallel
Posted: Thu Dec 03, 2009 11:32 am
by chulett
Still not following. You're wondering if Server is faster because you are 'misusing' the OCI stage there?
Me, I'd be asking IBM why the OE stage is not using the 'right' explain plan.
Posted: Thu Dec 03, 2009 11:37 am
by algfr
OK.
I don't know how they would be able to reproduce it. Guess there is no other option now.
I was just meaning that maybe there is an admin configuration to be done on the OE stage that was not done correctly.
Also, do I need to put some specific Oracle options on the table (PARALLEL, etc) ? Just to make sure it is not the issue.
Posted: Thu Dec 03, 2009 11:40 am
by chulett
You would send them the query, the explain plan and the table ddl and the Server query and explain plan as well I would imagine. Let them take it from there.
No, there's no configuration step or Oracle options you're missing here.
Posted: Fri Dec 04, 2009 7:30 am
by algfr
chulett wrote:You would send them the query, the explain plan and the table ddl and the Server query and explain plan as well I would imagine. Let them take it from there.
No, there's no configuration step or Oracle options you're missing here.
About to open the case...
I've made some little progress in the meanwhile. I've compared the parallel and the server job in Oracle (OSH against PHANTOM).
Big difference I see is:
With Phantom, it opens 2 cursors:
- SELECT GLOBAL_NAME FROM GLOBAL_NAME
- Update query
With OSH, it opens as many cursors as there are fields in my update query !!
For example:
If my query has 3 fields to update : update table 1 set field1=:field1, field2=:field2, field3=:field3 where inv_bk:=inv_bk
then it generates 3 statements in addition to the update statement:
select field1 from table1
select field2 from table1
select field3 from table1
Since this table has about 50 fields, I get 50 queries which of course full scan the 10 000 000 rows table...
Why does it need those queries ?