Oracle Update SQL Explain Plan

algfr · Post by **algfr** » Wed Dec 02, 2009 4:51 am

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

ray.wurlod · Post by **ray.wurlod** » Wed Dec 02, 2009 4:59 am

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?

algfr · Post by **algfr** » Wed Dec 02, 2009 6:13 am

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 !!!!)

algfr · Post by **algfr** » Wed Dec 02, 2009 6:45 am

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 !!

chulett · Post by **chulett** » Wed Dec 02, 2009 9:21 am

There is an INDEX hint to force Oracle to use the index you specify. Seems like worst case you could use that.

algfr · Post by **algfr** » Wed Dec 02, 2009 11:03 am

Will try...

Does this problem rings a bell to someone ?

algfr · Post by **algfr** » Thu Dec 03, 2009 6:28 am

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 ??

chulett · Post by **chulett** » Thu Dec 03, 2009 7:31 am

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?

algfr · Post by **algfr** » Thu Dec 03, 2009 10:17 am

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.

chulett · Post by **chulett** » Thu Dec 03, 2009 10:29 am

Official support doesn't get much more official than IBM.

Not sure what you mean by 'a misuse of OCI'.

algfr · Post by **algfr** » Thu Dec 03, 2009 11:13 am

Sorry mister

I meant the OCI Oracle Stage in Server against the Oracle Enterprise for Parallel

chulett · Post by **chulett** » Thu Dec 03, 2009 11:32 am

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.

algfr · Post by **algfr** » Thu Dec 03, 2009 11:37 am

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.

chulett · Post by **chulett** » Thu Dec 03, 2009 11:40 am

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.

algfr · Post by **algfr** » Fri Dec 04, 2009 7:30 am

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 ?