Unfortunately, upgrading is not an option for another 2-3 months, if not longer. Sandy On Wed, Mar 4, 2015 at 11:42 AM, Mladen Gogala <dmarc-noreply@xxxxxxxxxxxxx> wrote: > On 03/04/2015 08:25 AM, Sandra Becker wrote: > >> OS: Solaris Sparc 10 (64-bit) >> Oracle: EE 11.2.0.2 >> >> The OS and Oracle versions are identical on both the old and new >> servers. Storage attached to the new server is a new EMC disk array. >> Sorry I don't have any more details on the storage and the only additional >> information I have on the server is that it is a T5. >> >> We created a standby on the new hardware and did a switchover last Friday >> night. On Saturday I completed gathering stats on the application schema >> tables as requested by the product manager. As usual, very little activity >> on this database over the weekend. Yesterday morning we were contacted by >> internal users that performance was much worse than on the old hardware for >> a specific query on a really ugly view. A look at the execution plan shows >> multiple full table scans on some partitioned tables, some very large. >> There are about 15 tables joined to create the view, some more than once. >> They claim the view is no longer doing partition pruning, as it did before >> the switchover. I can't prove that it was/wasn't exhibiting this behavior >> before the switchover. They are insisting we run I/O calibration. I'm not >> familiar with it so I went to the docs. This database shares storage with >> quite a few production databases so I want to be very careful how I go >> about this. >> >> Questions: >> >> 1. What will running the I/O calibration do? Does it only provide >> information on the I/O subsystem, or does it change the way the optimizer >> behaves? The development team insists it will improve performance. >> 2. I've looked at AWR reports before/after the switchover and see that >> the query in question was doing a similar amount of I/O in both reports. >> Is there any way for me to get more detail on the before execution plan? >> 3. One of the large partitioned tables has no indexes. Would creating an >> index be of any benefit? I understand that it's possible to negatively >> affect other queries, so it should be considered with caution. Development >> insists that indexing would be a waste of time and definitely cause >> problems, although they have never tested it. >> 4. I want to trace the query, but it runs in parallel and produces more >> trace data that I have available disk to handle. Is there anything I can >> do on that front to get a trace I can feed into my Method-R tool and supply >> to oracle support? >> >> As I reviewed how the view, I recall them having issues with it before >> and me suggesting it should be optimized. I was told no and here we are >> again. The obvious concern is that the results would be different and >> changes require a lot of testing they don't have time to do. Any other >> recommendations would be appreciated. >> >> -- >> Sandy >> GHX >> > > Hi Sandra, > Calibrate only determines automatic degree of parallelism. I would gather > new system statistics first. However, there was a bug in 11.2.0.2 with > system statistics values being off chart. Can you upgrade to 11.2.0.4? You > are using an old and unsupported version. > > > -- > Mladen Gogala > Oracle DBA > http://mgogala.freehostia.com > > -- > //www.freelists.org/webpage/oracle-l > > > -- Sandy GHX