data_engine probe : Data Maintenance and Index Maintenance job recommendations

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

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:

  

RDBMS	Data Maintenance	Index Maintenance
SQL Server	Run Daily as per  scheduled time.	Run Daily along with Data Maintenance if auto_index=yes
Oracle	Run Daily as per  scheduled time.	Run Daily along with Data Maintenance if auto_index=yes
MySQL	Run Daily as per  scheduled time.	No Index Maintenance

 

Environment

UIM 8.51, 9.0.2, 9.1

Resolution

RDBMS	Data maintenance	Index maintenance	Comments
SQL Server	Runs 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.
Oracle	Runs 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
MySQL	Runs 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.