Re: Table and index degree disparity
Date: Wed, 10 Feb 2016 11:28:58 -0700
Message-ID: <CAJzM94AM9DLV4OmkArHtSM-oMd87fVkeKZ08uxAwMhHWGjXK5w_at_mail.gmail.com>
I definitely did not word my original post very well. We appear to have SLAs only on the extremely critical databases, so that didn't give me much in the way of guidelines for the two databases I'm currently looking at. I frequently hear, "...but the customers aren't complaining.." as an excuse not to do anything or we'll look at it later. Seems like later never comes though.
I am more interested in reducing resource consumption. I have heard from a couple of people, though not through our ticketing system, that some of their queries always run long. Rather subjective, so I started monitoring SQL statements, primarily through OEM to get a better feel for duration of queries. Part of familiarizing myself with the databases, was noting a "typical" load, what queries ran most frequently, which ran the longest, what looked like it could benefit from tuning, etc. That's when I noticed the disparity.
I have used Method-R tools before, but they don't seem interested in using it here. Thank you for your comments.
Sandy
On Wed, Feb 10, 2016 at 11:10 AM, Stefan Koehler <contact_at_soocs.de> wrote:
> Hi Sandy,
>
> > Q1: No one is complaining about performance right now, although I have
> seem some queries that could be tuned. Maybe users are just used to that
> > level of performance? Not sure. Is it worth being proactive in this
> instance?
>
> What are your SLAs for the corresponding business processes? Do you
> actually measure the response time of these? If you just look at query or
> SQL
> level without focus on the business you may tune forever (e.g. "Compulsive
> Tuning Disorder") without any possible benefit to business / end users. I
> just can highly recommend (once again) Method R for that, e.g. here on
> page 20:
> http://www.oracle.com/technetwork/testcontent/millsap-ch01-131133.pdf
> or here:
> http://assets.red-gate.com/products/oracle-development/education/webinars/Assets/cary-millsap-performance-is-a-feature-webinar.pdf
>
> P.S.: Please don't confuse this with reducing the resource consumption on
> database servers, if this is your goal, but you talked about end users and
> performance.
>
> 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 8. Februar 2016 um 22:20
> geschrieben:
> >
> > Oracle EE 11g and 12c
> > Both Exadata and non-Exadata environments
> >
> > While familiarizing myself with all the databases at my new job (still
> feels new after 4 months), I noticed that several tables and indexes in our
> > production databases have a degree of parallelism disparity. I remember
> encountering some serious issues at my last employer that required getting
> > Oracle developers involved. One of the items of concern that they
> raised was the degree disparity between tables and indexes, i.e. we had
> degree of
> > 32 on in indexes and degree of 1 on the tables. We were told to make
> them the same to avoid bottlenecks. After resetting both sides to an
> > appropriate value for our environment, performance significantly
> improved. We had more tweaks to do (unfortunately, no code tweaks which
> would have
> > helped even more) before performance was acceptable.
> >
> > Question 1: No one is complaining about performance right now,
> although I have seem some queries that could be tuned. Maybe users are
> just used
> > to that level of performance? Not sure. Is it worth being proactive in
> this instance? Our team is split on the value and I wanted other opinions.
> >
> > Question 2: Same issue on our Exadata databases. The DBA who
> initially set up the Exadata databases sees no value in making changes
> since we are
> > using HCC compression. I am a newbie to Exadata and have been studying,
> but I haven't seen anything to indicate that we shouldn't still follow best
> > practices or be proactive. Opinions? Does the compression really
> remove the "bottleneck" situation from degree disparity?
> >
> > I appreciate your opinions and comments. Thank you.
> >
> > --
> > Sandy B.
>
-- Sandy B. -- http://www.freelists.org/webpage/oracle-lReceived on Wed Feb 10 2016 - 19:28:58 CET