Thanks for the question, Rohit.
Asked: January 16, 2017 - 1:18 pm UTC
Last updated: January 18, 2017 - 7:16 pm UTC
Version: 11.2.0.4.0
Viewed 1000+ times
You Asked
Hi Team,
As part of a daily housekeeping practice I executed the below commands for an index rebuilding activity :
ALTER INDEX DLM_RAMBO_ILQ_PK REBUILD ONLINE PARALLEL 12 NOLOGGING;
ALTER INDEX RAMBO_ILQ_IDX2 REBUILD ONLINE PARALLEL 12 NOLOGGING;
Now, I have a concern regarding the DOP of these indexes. The problem is these indexes are of tables which are involved in the below DML :
INSERT /*+ APPEND PARALLEL(DLM_RAMBO_SERVICE_STATUS_HIST,6) */
INTO DLM_RAMBO_ILQ (SRC_ACTIVITY_DATE,
SERVICE_ID,
DLM_ILQ,
UPDATE_SYSTEM,
CREATE_BY,
CREATE_DATE)
SELECT /*+ PARALLEL(RSD,6) */
RSD.SRC_ACTIVITY_DATE,
RSD.SERVICE_ID,
RSD.DLM_ILQ,
RSD.UPDATE_SYSTEM,
'XXX',
SYSDATE
FROM RAMBO_ILQ_DAILY RSD
WHERE NOT EXISTS
(SELECT 1
FROM DLM_RAMBO_ILQ ILQ
WHERE ILQ.SERVICE_ID = RSD.SERVICE_ID
AND RSD.SRC_ACTIVITY_DATE = ILQ.SRC_ACTIVITY_DATE)
AND RSD.DATA_DATE = '14-jan-2017';
Now this query , before I did the rebuild , took only 15-20 mins to load. I am sure the source data volume has not changed drastically.
The indexes are usable, there are no untoward wait events. Parallel Slaves are being taken up.
Has the runtime increased because I changed the DOP from original value ( I don't know what it was ) to 12.
If its a possibility I would like to restore the degree back to what it was.
Can you suggest a way forward ?
Thanks
Rohit
and we said...
Unless you have some ddl logging in place, either via AUDIT, or information captured in the alert.log, or via the 'enable_ddl_logging' initialization parameter, then its unlikely you can work out what the degree was *before* you made the change.
If you were really desperate you could look at restoring a old backup to another machine an looking there, but that seems a drastic option.
If you don't recall doing any particular maintenance on these indexes in the past, the most probable setting the indexes had was '1', so you could set them to that, and do some benchmarking to see if this restores your performance.
This sounds like a place where you could use a little PLSQL wrapper to avoid this in future, by doing something like (pseudo-code):
procedure REBUILD_INDEX(p_index varchar2, p_degree int) is
l_prev_setting varchar2(30);
begin
logger(p_index||' is being rebuilt by '||user);
select degree into l_prev_setting
from user_indexes
where index_name = p_index;
execute immediate 'alter index '||p_index||' rebuild parallel '||p_degree;
execute immediate 'alter index '||p_index||' parallel '||l_prev_setting ;
logger(p_index||' rebuild completed');
end;
or similar.