Performance degradation after upgrading from 10gR2 to 11gR2

Hi guys,

We upgraded our DWH DB from version 10.2.0.4 to 11.2.0.3 three months ago.
Since then, we have experienced a performance degradation of 30% in Reports' queries.
After a deep investigation, we've found out that since the upgrading there is a huge increase of SORT MERGE joins on expense of HASH joins:

Code:

---

  1  select count(1),trunc(timestamp,'MM')
  2  from DBA_HIST_SQL_PLAN
  3  where OPERATION like '%SORT%'
  4  and options like '%JOIN%'
  5  and timestamp >= to_date('01/01/2013','dd/mm/yyyy')
  6  group by trunc(timestamp,'MM')
  7* order by trunc(timestamp,'MM')
SQL> /
 
  COUNT(1) TRUNC(TIM
---------- ---------
      736 01-JAN-13
      672 01-FEB-13
      647 01-MAR-13
      2362 01-APR-13
      3960 01-MAY-13
      2460 01-JUN-13

---

Since the upgrading we've treated some reports with the hint USE_HASH in order to improve reports but there are too many reports which were screwed up by SMJ.

What can be the reason that the optimizer chooses by mistake SMJ instead of HJ?

Our DB is hosted by the following server:
Server type and version - SunOS 5.10 Generic_144488-08 (64-bit)
CPUs - 32 X 2 (3Gb)
Memory Size (MB) - 524288


Thanks in advance,
Nir

There is a known issue with 11.2.0.3 and direct reads. Essentially Oracle takes what it considered to be a full table scan on a large table and serializes it reading directly from disk. Since the read is directly from disk it needs to flush the dirty blocks first. The reason for this behavior is so that you don't have one large query flush everything out of the db_cache. I turned off this feature on a few databases and turned on parallel_automatic_tuning. This seemed to help. You can also change what Oracle considers to be a small table by making that larger, hence fewer queries will be direct read queries.

alter system set parallel_automatic_tuning=TRUE scope=spfile;
alter system set "_serial_direct_read" =FALSE;

http://www.pythian.com/blog/upgraded...reads-trouble/

Thanks a lot!
Very interesting and makse a lot of sence because we have also a dramatic increasing in "direact read/write path" wait events.
For testing , I've changed in session level the parameter _serial_direct_read to FALSE and I checked a problematic report which its execution plan was screwed with SMJ.
The execution plan has been changed to the good one with HJ!
I assume I'll need to open a SR to Oracle to get their approval for this changing...

Best regards,
Nir

I don't think that you need approval for that change. But it could still help if you want to create an SR. Maybe Oracle will see that this change was ill advised.

To make 11g work without performance degradation on RH Linux using OS file system, we had to set:

Code:

---

FILESYSTEMIO_OPTIONS = SETALL

---

Perhaps it will help for you also :confused:

Thanks again gandolf989. I'll open SR and let's see what Oracle will recommend.
LKBrwn_DBA - thanks , but we work on Sun Solaris , so I don't know if changing this parameter helps...

Quote:

---

Originally Posted by LKBrwn_DBA

To make 11g work without performance degradation on RH Linux using OS file system, we had to set:

Code:

---

FILESYSTEMIO_OPTIONS = SETALL

---

Perhaps it will help for you also :confused:

---

Is that like pressing the easy button? It looks like your parameter does not help if you already use ASM.

We actually bumped into a similar issue while testing upgrading a DWH to 11gR2.
In our case the solution was to gather SYSTEM statistics on the new hardware as well as fully refresh table/index statistics.

Hi PAVB,

Thanks!
The Db size is ~ 50Tb , so I don't see a case to refresh statistcs all over the tables/indexes.
In addition , we don't have any change in hardware , so I don't think that taking SYSTEM stats will help ....

Quote:

---

Originally Posted by nir_s

... we don't have any change in hardware , so I don't think that taking SYSTEM stats will help ....

---

You are correct, different scenario. Please disregard my previous post.

No hard feelings :)

Quote:

---

Originally Posted by gandolf989

Is that like pressing the easy button? It looks like your parameter does not help if you already use ASM.

---

Only works with "cooked" disk (not ASM).
The main problem was occurring due to Linux disk "cache" and the parameter allowed Oracle to bypass the OS file manager.
;)

Hi guys,

I wanted to update that after a long session with Oracle Support via SR , they decided that this behavior is a bug in 11.2.0.3 and they are developing now a fix.
Bug 17397506 : SORT MERGE INCORRECTLY CHOSEN OVER HJ IN 11.2.0.3
The workaround that I've got from them:
Setting the parameter _optimizer_filter_pushdown to FALSE.
For testing this parameter , I've applied it in session level (by a trigger) on a group of customers.
Most of the screwed reports were improved but there were several reports that their good explain plan was screwed because of this parameter.We tread them with hints.

Regards,
Nir