SQL & PL/SQL

Hello Gurus,

I am trying to find a root cause for the problem that we were experiencing th eother day in our 11.1.0.7 database.

We have 2 environments (Lets say A and B) and all the database parameters are same for each one of them. Checked from v$parameter. OS level also both are same as we have been told by SYS Admins.

We tested one UPDATE statement in environment A and it updates the record in 10 mins and when we run the same UPDATE statement in the environment B then it ran for Hrs. Explain Plan was looking same between Environment A and B for the same UPDATE statement and there was nothing runing on Env. A and B at the time of running this UPDATE in either environment.

Looking at V$SESSION_LONGOPS tells me that -
1. It was trying to look through 60K of blocks
2. First 40K of blocks Oracle was able to churn though very quickly around 3 mins or so
3. But after that looking at the speed of the UPDATE and TIME_REMAINING column from V$SESSION_LONGOPS, it looked like with that kind of speed that UPDATE might have taken around 100 Hrs.

Now the question is - Why this behavior of the same UPDATE statement between two identical environments?

I saw that there was moe fragmentation in the table that we were updating in Env B.

So, to prove the theory that the fragmentation caused this huge slowness, we tested same dataset after restoring (expdp/impdp) to the Env. A and B. Now the data nicely defragmented (re-orged) on both Env. A abd B.

After doing this we ran that UPDATE statement again on both the environments and found that it ran almost in same amount of time in both the environments.

I would really like to know the logic and the reasoning behind why such kind of behavior by an UPDATE in identical two environments?

Your inputs will be appreciated. Thanks!