If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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:

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;

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...

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.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

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 ....

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.