RE: Table and index degree disparity

Only where Parallel Query is required :
I have
Parallel on Tables
And
NoParallel on Indexes

(Index Parallel Degree comes from CREATE or REBUILD ; I reset to 1 after the CREATE or REBUILD).

However, there are cases where you could use Index Fast Full Scan with Parallel, so you would use this sparingly.

Hemant K Chitale

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sandra Becker Sent: Tuesday, February 09, 2016 5:20 AM To: oracle-l
Subject: Table and index degree disparity

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.

This email and any attachments are confidential and may also be privileged. If you are not the intended recipient, please delete all copies and notify the sender immediately. You may wish to refer to the incorporation details of Standard Chartered PLC, Standard Chartered Bank and their subsidiaries at https://www.sc.com/en/incorporation-details.html
--

http://www.freelists.org/webpage/oracle-l Received on Wed Feb 10 2016 - 04:46:04 CET