Asynchronous Global Index Maintenance, also called Deferred Global Index Maintenance, means that Oracle will maintain global indexes during the nightly maintenance window, when you perform operations on a partitioned tables that use a global index. Oracle has introduced this in an effort to make online operations less disruptive.
Examples of such operations are
* DROP PARTITION
* TRUNCATE PARTITION
* MOVE PARTITION (although this operation is not listed in the
Oracle 12.1 documentation)
The execution of the index maintenance is done through the PL/SQL program PMO_DEFERRED_GIDX_MAINT, which purpose is to clear orphan data from global indexes generated during partition maintenance operations.
The program can be verified through DBA_SCHEDULER_PROGRAMS:
SELECT OWNER,PROGRAM_NAME,PROGRAM_TYPE,PROGRAM_ACTION,ENABLED
FROM DBA_SCHEDULER_PROGRAMS
WHERE PROGRAM_NAME='PMO_DEFERRED_GIDX_MAINT';
| OWNER | PROGRAM_NAME | PROGRAM_TYPE | PROGRAM_ACTION | ENABLED |
| SYS | PMO_DEFERRED_GIDX_MAINT | PLSQL_BLOCK | dbms_part.cleanup_gidx_internal(
noop_okay_in => 1); | TRUE |
It is executed trough the scheduler every night:
select schedule_name,repeat_interval
from DBA_SCHEDULER_SCHEDULES
where schedule_name='PMO_DEFERRED_GIDX_MAINT_SCHED';
| SCHEDULE_NAME | REPEAT_INTERVAL |
| PMO_DEFERRED_GIDX_MAINT_SCHED | FREQ=DAILY; BYHOUR=02; BYMINUTE=0; BYSECOND=0 |
It is executed through the job PMO_DEFERRED_GIDX_MAINT_JOB:
SELECT owner,job_name,program_name,schedule_name,enabled,state,run_count,to_char(last_start_date,'dd.mm.yyyy hh24:mi') "start",stop_on_window_close
FROM DBA_SCHEDULER_JOBS
WHERE PROGRAM_NAME='PMO_DEFERRED_GIDX_MAINT';
| OWNER | JOB_NAME | PROGRAM_NAME | SCHEDULE_NAME | ENABLED | STATE | RUN_COUNT | start | STOP_ON_WINDOW_CLOSE |
| SYS | PMO_DEFERRED_GIDX_MAINT_JOB | PMO_DEFERRED_GIDX_MAINT | PMO_DEFERRED_GIDX_MAINT_SCHED | TRUE | RUNNING | 1328 | 10.12.2019 02:00 | FALSE |
If the job is not finished within a day, is it possible to run PMO_DEFERRED_GIDX_MAINT with parallelism, as specified in the Oracle Support Note "Is It Possible To Execute The Job PMO_DEFERRED_GIDX_MAINT_JOB With Parallel Degree? (Doc ID
2126370.1)".
An example from my own experience is that the following command
ALTER TABLE SALES
MOVE PARTITION P_2019_02 ONLINE
TABLESPACE DATA2
ROW STORE COMPRESS ADVANCED
PARALLEL 4;
Triggered the statement:
ALTER INDEX "SH"."SALES_IDX1" COALESCE CLEANUP;
I have found that I sometime need to stop an job that's running through the maintenance window, and into office hours.
In that case, I've used the procedure dbms_scheduler.stop_job, like this:
BEGIN
DBMS_SCHEDULER.STOP_JOB('SYS.PMO_DEFERRED_GIDX_MAINT_JOB', 'TRUE');
END;
/
If you need to find out which indexes that are due for coalescing, use this query:
SELECT owner,index_name,orphaned_entries
FROM dba_indexes
where owner ='SCOTT'
and orphaned_entries='YES'
ORDER BY 1;
