Re: Performance problems after moving to new hardware
Date: Wed, 4 Mar 2015 08:50:59 -0700
Message-ID: <CAJzM94BLeFdK7C=Bqpg60-nsJJqLApCRVHjpj_RNN64ZD5KJAQ_at_mail.gmail.com>
Good feedback, thanks. Unless the old statistics are automatically saved somewhere, I don't have them to revert back to.
I checked the init.ora parameters. db_cache_size is 1Gb smaller, everything else is the same. I will continue working through the suggestions.
They mentioned that the development environment is having the same problem. The only change in development was the server. The storage was simply detached from the old and attached to the new.
The old system is currently our standby. They are hesitant to allow us to do anything on the standby right now for fear that we will screw it up. The statement in question is only a select on a view, but I don't get to decide what to do with the standby DB, only recommend.
I've been told the different databases are segregated in some fashion on the storage.
Sandy
On Wed, Mar 4, 2015 at 8:21 AM, Stefan Koehler <contact_at_soocs.de> wrote:
> Hi Sandra,
> well i gonna be very generic to your specific points here as you are not
> able to provide any details due to company policy.
>
> 1) Yes, it also changes the CBO behavior, but in a different way as your
> development team would expect it. Randolf Geist called it "new cost is time
> model". For details please check his blog post (
> http://tinyurl.com/nxdksp9 ). However I/O calibration is used in case of
> Auto DOP and as you
> mentioned in point 4 that your query runs in parallel it might be an
> influencing factor.
>
> 2) Script awrsqrpt.sql provides you more information about history
> execution plan, but with a lot of missing important details (e.g. predicate
> &
> outline section). So nothing really helpful, if it gets really tricky.
> However if it is about parallel query, i would go for Randolf Geist's tool
> called "XPLAN_ASH" (really great stuff!): http://tinyurl.com/nuso58w and
> check the PX details.
>
> 3) This can not be answered without any detailed information, but as it
> ran fast without these indexes before the switch i would start analyzing the
> root cause first.
>
> 4) SQL Monitoring report, especially in case of PX.
>
> However the following statement ("On Saturday I completed gathering stats
> on the application schema tables as requested by the product manager") makes
> me sit-up and take notice. Why not just restoring the old statistics for
> the corresponding objects (as a possible quick work-around and for
> verification) and check the runtime afterwards. If the runtime is fine
> afterwards, you can dig into the details and check what went wrong without
> any
> business pressure.
>
> Best Regards
> Stefan Koehler
>
> Freelance Oracle performance consultant and researcher
> Homepage: http://www.soocs.de
> Twitter: _at_OracleSK
>
> > Sandra Becker <sbecker6925_at_gmail.com> hat am 4. März 2015 um 14:25
> geschrieben:
> >
> > 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
>
-- Sandy GHX -- http://www.freelists.org/webpage/oracle-lReceived on Wed Mar 04 2015 - 16:50:59 CET