Oracle Update SQL Explain Plan
Moderators: chulett, rschirm, roy
Oracle Update SQL Explain Plan
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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?
![Wink :wink:](./images/smilies/icon_wink.gif)
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?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
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.
About to open the case...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.
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 ?