data_engine probe : Data Maintenance and Index Maintenance job recommendations

book

Article ID: 131511

calendar_today

Updated On:

Products

NIMSOFT PROBES DX Infrastructure Management

Issue/Introduction

What are the best practices for data_engine maintenance and Index Maintenance jobs?

Overview of Data maintenance and Index maintenance job as on today:
  
RDBMSData MaintenanceIndex Maintenance
SQL ServerRun Daily as per  scheduled time.Run Daily along with Data Maintenance if auto_index=yes
   
OracleRun Daily as per  scheduled time.Run Daily along with Data Maintenance if auto_index=yes
   
MySQLRun Daily as per  scheduled time.No Index Maintenance
 

Environment

UIM 8.51, 9.0.2, 9.1

Resolution

RDBMSData maintenanceIndex maintenanceComments
SQL ServerRuns Daily as per schedule.

Recommended to run Index Maintenance during maintenance window once in a month or conditionally when the database performance degrades( on need basis).

Datamaintenance job can be triggered directly from probe utility by selecting data_engine probe → Ctrl+P →

select  run_admin_now from drop down list → send command request.

Please make sure auto_reindex=yes,

table_maintenance_loglevel=5 and loglevel=5  in data_engine.cfg file before triggering from probe utility to validate the duration it took to complete.

 
OracleRuns Daily as per schedule.

Recommended to run Index Maintenance during maintenance window once in a month or conditionally when the database performance degrades( on need basis).

Datamaintenance job directly can be triggered from probe utility by selecting data_engine probe → Ctrl+P →

select  run_admin_now from drop down list → send command request.

Please make sure auto_reindex=yes,

table_maintenance_loglevel=5 and loglevel=5  in data_engine.cfg file before triggering from probe utility to validate the duration it took to complete.

Oracle recommended to perform Index Maintenace during Maintenance Windows to avoid blocking or locks as per Oracle Doc ID  1476083.1: Resolving Issues Where Lock Contention for 'enq: TM-contention' Wait Event (TM Enqueue Contention) Occurs during ANALYZE INDEX VALIDATE STRUCTURE

 

MySQLRuns Daily as per schedule.No Index maintenance 

Additional Information

Blocking Sessions in Oracle due to Index Maintenance Job of UIM
----------------------------------------------------------------------------------------
 
Query to Check Blocking sessions :
-----------------------------------------------
 
SELECT TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS')  || ' User '  || s1.username || '@' || s1.machine || ' (sqlid = ' || s1.sql_id || ' ) ( SID= ' || s1.sid  || s1.status
 || ')  with the statement: ' || sqlt2.sql_text ||'
is blocking the SQL statement on ' || ' User '  || s2.username || '@' || s2.machine || ' ( sqlid = ' || s1.sql_id || ' ) ( SID= ' || s2.sid  || s2.status || ')   blocke
d SQL -> ' ||sqlt1.sql_text AS blocking_status
FROM v$lock l1,
v$session s1 ,
v$lock l2 ,
v$session s2 ,
v$sql sqlt1 ,
v$sql sqlt2
WHERE s1.sid =l1.sid
AND s2.sid =l2.sid
AND sqlt1.sql_id= s2.sql_id
AND sqlt2.sql_id= s1.prev_sql_id
AND l1.BLOCK =1
AND l2.request > 0
AND l1.id1 = l2.id1
AND l2.id2 = l2.id2;
 
If there are Blocking Sessions existing in the Database and Blocking Session SQL Text is ANALYZE INDEX VALIDATE STRUCTURE indicates it belong to Index Maintenance job of UIM.
as per Oracle Doc ID  1476083.1: Resolving Issues Where Lock Contention for 'enq: TM-contention' Wait Event (TM Enqueue Contention) Occurs during ANALYZE INDEX VALIDATE STRUCTURE
to resolve Oracle recommended to run the ANALYZE INDEX command during a maintenance window or a quiet time when there is no contention likely at that time.

Please reference Oracle Doc ID 1476083.1.