Re: Table and index degree disparity

From: Timur Akhmadeev <timur.akhmadeev_at_gmail.com>
Date: Tue, 9 Feb 2016 10:06:52 +0300
Message-ID: <CACGsLC+4YsTBaQYyDQnAbAwxtoia3qyRYYymDCp-tCgFUvYyTQ_at_mail.gmail.com>



Hi Sandra

most common reason to have indexes DOP greater than zero is a parallel rebuild/create. In my opinion for most applications it should be set back to noparallel immediately after rebuild is done. When I write rebuild/create indexes statements I always include alter index X noparallel at the end.

> The DBA who initially set up the Exadata databases sees no value in making
> changes since we are using HCC compression.

I see no direct connection of indexes DOP and HCC being used so I'd ask for more details.

On Tue, Feb 9, 2016 at 12:20 AM, Sandra Becker <sbecker6925_at_gmail.com> wrote:

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

-- 
Regards
Timur Akhmadeev

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Feb 09 2016 - 08:06:52 CET

Original text of this message