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