SQL tuning with Explain Plan
We are using a 2 node cluster runing Oracle 11R2. After an instance crash we see a significant decrease of performance of a querry.
The querry runs on a partitioned table with a global index. We used the awrsqrpt.sql script to get an execution plan.
Only on the crashed instance the script returns two execution plans and runs very slow.
The querry runs on a partitioned table with a global index. We used the awrsqrpt.sql script to get an execution plan.
Only on the crashed instance the script returns two execution plans and runs very slow.
How can it be that for one statement we have more then one execution plan at a time?
While the "fast" execution plan does a unique index scan, the "slow" execution plan does a fast full index scan.
We see a lot of block gets, most likely caused by the fast full index scan.
0