Normal 0 false false false EN-US ZH-CN X-NONEEnv:
DB:11.2.0.1.0
OS:SunOS 5.10 SPARC-Enterprise

What to do in "Merge" SQL:
When ORACLE execute "Merge" SQL, it will call an internal SQL which combine "select 1 from dual " and "where" and the on clause

For example:

Merge into a using b  on ( a.id=b.id ) when matched then update set a.name=b.name when not matched then insert values(b.id,b.name);

This Merge SQL will call an internal SQL "select 1 from dual where a.id=b.id", you can trace it with event 10053 or 10046.

Our requirement:
Every dynamic table include about thousand of messages, every message have it's own attribute and every message have different merge clause ,so we need use a merge SQL to change the attribute one by one, so every merge(message) will be hard parse .

Our problem:

We set a same alias for every dynamic table order to make dynamic SQL easy, so lead to the internal SQL are same for different dynamic table, and we hard parse "Merge" SQL every time, so make a very high version for the internal SQL, so when we have many sessions merge on different dynamic table in parallel ,it will contention(Cursor: mutex S) on the internal SQL "select 1 from dual where a.id=b.id".

How to work around this bug:

We use a different alias for different dynamic table so that they will not affect each other.

For example ,
for dynamic table a, we use alias joba

Merge into a joba using b  on ( joba.id=b.id ) when matched then update set joba.name=b.name when not matched then insert values(b.id,b.name);

the internal SQL will be :

select 1 from dual where joba.id=b.id

for dynamic table c, we use alias jobc

Merge into c jobc using b  on ( jobc.id=b.id ) when matched then update set jobc.name=b.name when not matched then insert values(b.id,b.name);

the internal SQL will be :

select 1 from dual where jobc.id=b.id

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/28815564/viewspace-758772/ ，如需转载，请注明出处，否则将追究法律责任。