Very slow UPSERT by oracle Enterprise Stage

[kamesh_sk]

HI ,

Iam trying to update a Oracle Table.
The table was analyzed and statistics computed before issuing update statement.
The update statement is something like this.

UPDATE
/*+ INDEX ACCOUNTS_TEST.ACC_PRIMEE*/
PBOND.ACCOUNTS_TEST
SET ACC_LAST_CHANGE_BY = ORCHESTRATE.ACC_LAST_CHANGE_BY ,
ACC_LAST_CHANGE_DATE = ORCHESTRATE.ACC_LAST_CHANGE_DATE and so on WHERE (ACC_SOC_SEQNO = ORCHESTRATE.ACC_SOC_SEQNO
AND ACC_ACCOUNT_NO = ORCHESTRATE.ACC_ACCOUNT_NO
AND ACC_SUBACC_NO = ORCHESTRATE.ACC_SUBACC_NO)

This statement almost runs as 1 row / sec . I could not find any solution in the forum . I have tried by forcing the Index HINT but that did not help either. IS there any patch or solution available for this.
Any Help is really much appreciated.

Thanks

Kamesh

[chulett]

Since your index names mean nothing to us, I have to ask... do you have an index over the three columns in your where clause? (ACC_SOC_SEQNO, ACC_ACCOUNT_NO, ACC_SUBACC_NO) Are they on the 'leading edge' of the index? If yes, have you or your DBA checked that it is actually being used while the job runs?

[kamesh_sk]

Thanks for your reply chulett

The DBA have monitored while the job was running.It looks like the index
not been used the job.This is because it does a full a table scan and to update 9000 records it hits the table for about 19 million read.

Aslo can you please let me know what the 'leading edge on the index' means?

Many thanks

[swarnkar]

UPDATE
/*+ INDEX ACCOUNTS_TEST.ACC_PRIMEE*/

Hi Kamesh,

Oracle hints needs to be given in exact syntax, and it requires a white space after the + sign at left side and before * at right side.
Here we can see there is no space between index name and comment sign. So try this syntax and see if it helps
UPDATE
/*+ INDEX ACCOUNTS_TEST.ACC_PRIMEE */

Thanks,
Nitin Swarnkar

[chulett]

Aslo can you please let me know what the 'leading edge on the index' means?

I just mean that for a composite index, those three fields would have to be first in the included fields for your update to use the index.

[qt_ky]

Continue working with your DBA. They should be able to look at your SQL, the update statement, and create an appropriate index. They may need to run an analyze command on the table as well. If those are done right, you should get reasonable performance and also not need to add a hint.

After that check how many nodes the job is running on, the array size, and related settings. Maybe you're running updates sequentially when they could be done in parallel. Just ensure correct index is there and used first, as that is the big issue.

[kamesh_sk]

THanks every one for the reply.

The problem i have identified was the update statement was issued based on 3 key columns which is part of the INdex as well. I have a column called Account_No which is Varchar but we receive only numeric data to it.
When i modify the update statement by adding a to_char around account_no i get a good run time. But unfortunately i cant use it because i am using RCP and the job is multi instance as well. Iam using the same update job to update 56 other tables. SO changing anythig in the design may not be possible Is there any way where i could force this either at Table_Index or any other approach.

Thanks very much
Kamesh

[kamesh_sk]

Thanks every one for Helping.

Using a To_Char was the final solution i had to adapt to over come this issue.
UPDATE
/*+ INDEX ACCOUNTS_TEST.ACC_PRIMEE*/
PBOND.ACCOUNTS_TEST
SET ACC_LAST_CHANGE_BY = ORCHESTRATE.ACC_LAST_CHANGE_BY ,
ACC_LAST_CHANGE_DATE = ORCHESTRATE.ACC_LAST_CHANGE_DATE and so on WHERE (ACC_SOC_SEQNO = ORCHESTRATE.ACC_SOC_SEQNO
AND ACC_ACCOUNT_NO = TO_CHAR(ORCHESTRATE.ACC_ACCOUNT_NO)
AND ACC_SUBACC_NO = ORCHESTRATE.ACC_SUBACC_NO

The data was all Numeric and even though the column is defined as Varchar/string , the update never uses the index and always goes for a full table scan , and hence had to add TO_CHAR in the SQL and it worked fine.

Thanks again for al the help

[chulett]

Did your DBA explain why that happened? When you join two columns of different data types, Oracle 'up converts' one to the other data type under the covers. In your case the string field was converted to a NUMBER before being joined to the incoming data and the moment that conversion happened it could no longer use the index the original value was part of.

Another option - define a function based index specifically for this join, one is which the ACC_ACCOUNT_NO is wrapped in a TO_NUMBER() function so the data type matches your source, then your job could automagically use it.