IBM Support

How to fix continuous increase issue of CPU consumption of instance hosting repository database

Troubleshooting


Problem

To fix the continuous increase issue of CPU consumption of instance hosting repository database, you may need to add following indexes to repository tables.

Resolving The Problem

Add following indexes to repository tables:


CREATE INDEX "IBMOTS"."IDX1904030910320" ON "IBMOTS"."QUERY_STATS" ( "SNAPSHOT_BATCH_ID" ASC) ALLOW REVERSE SCANS COLLECT STATISTICS;
CREATE INDEX "IBMOTS"."IDX1904030910321" ON "IBMOTS"."LOCK_WAITTIME_STATS" ( "SNAPSHOT_BATCH_ID" ASC) ALLOW REVERSE SCANS COLLECT STATISTICS;
CREATE INDEX "IBMOTS"."IDX1904030910322" ON "IBMOTS"."LOCK_IDLETIME_STATS" ( "SNAPSHOT_BATCH_ID" ASC) ALLOW REVERSE SCANS COLLECT STATISTICS;
CREATE INDEX "IBMOTS"."IDX1904030910323" ON "IBMOTS"."REAL_MEMORY_STATS" ( "SNAPSHOT_BATCH_ID" ASC) ALLOW REVERSE SCANS COLLECT STATISTICS;
CREATE INDEX "IBMOTS"."IDX1904030910324" ON "IBMOTS"."VIRTUAL_MEMORY_STATS" ( "SNAPSHOT_BATCH_ID" ASC) ALLOW REVERSE SCANS COLLECT STATISTICS;
CREATE INDEX "IBMOTS"."IDX1904030910325" ON "IBMOTS"."WORKLOAD_STATS" ( "SNAPSHOT_BATCH_ID" ASC) ALLOW REVERSE SCANS COLLECT STATISTICS;
CREATE INDEX "IBMOTS"."IDX1904030910326" ON "IBMOTS"."SORTING_STATS" ( "SNAPSHOT_BATCH_ID" ASC) ALLOW REVERSE SCANS COLLECT STATISTICS;
CREATE INDEX "IBMOTS"."IDX1904030910327" ON "IBMOTS"."IO_STATS" ( "SNAPSHOT_BATCH_ID" ASC) ALLOW REVERSE SCANS COLLECT STATISTICS;
CREATE INDEX "IBMOTS"."IDX1904030910328" ON "IBMOTS"."BUFFERPOOL_STATS" ( "SNAPSHOT_BATCH_ID" ASC) ALLOW REVERSE SCANS COLLECT STATISTICS;
CREATE INDEX "IBMOTS"."IDX1904030910329" ON "IBMOTS"."LOG_SPACE_USED_STATS" ( "SNAPSHOT_BATCH_ID" ASC) ALLOW REVERSE SCANS COLLECT STATISTICS;
CREATE INDEX "IBMOTS"."IDX1904030910330" ON "IBMOTS"."LOG_SNDLOG_SPACE_ALLOCATED_STATS" ( "SNAPSHOT_BATCH_ID" ASC) ALLOW REVERSE SCANS COLLECT STATISTICS;
CREATE INDEX "IBMOTS"."IDX1904030910331" ON "IBMOTS"."LOG_DRTY_PAGES_LOG_STATS" ( "SNAPSHOT_BATCH_ID" ASC) ALLOW REVERSE SCANS COLLECT STATISTICS;
CREATE INDEX "IBMOTS"."IDX1904030910332" ON "IBMOTS"."LOG_HADR" ( "SNAPSHOT_BATCH_ID" ASC) ALLOW REVERSE SCANS COLLECT STATISTICS;
CREATE INDEX "IBMOTS"."IDX1904030910333" ON "IBMOTS"."PAGE_RECLAIM_CF_STATS" ( "SNAPSHOT_BATCH_ID" ASC) ALLOW REVERSE SCANS COLLECT STATISTICS;

CREATE INDEX "IBMOTS"."IDX1904030910344" ON "IBMOTS"."VIRTUAL_MEMORY_CF_STATS" ( "SNAPSHOT_BATCH_ID" ASC) ALLOW REVERSE SCANS COLLECT STATISTICS;
CREATE INDEX "HEALTHMETRICS"."IDX1904030910310" ON "HEALTHMETRICS"."HADR_STATS" ( "SNAPSHOT_BATCH_ID" ASC) ALLOW REVERSE SCANS COLLECT STATISTICS;
CREATE INDEX "HEALTHMETRICS"."IDX1904030910350" ON "HEALTHMETRICS"."DBSTATUS" ( "SNAPSHOT_BATCH_ID" ASC) ALLOW REVERSE SCANS COLLECT STATISTICS;
CREATE INDEX "HEALTHMETRICS"."IDX1904030910351" ON "HEALTHMETRICS"."DPFPARTITIONINFO" ( "SNAPSHOT_BATCH_ID" ASC) ALLOW REVERSE SCANS COLLECT STATISTICS;
CREATE INDEX "HEALTHMETRICS"."IDX1904030910352" ON "HEALTHMETRICS"."PURESCALE_MEMBER" ( "SNAPSHOT_BATCH_ID" ASC) ALLOW REVERSE SCANS COLLECT STATISTICS;
CREATE INDEX "HEALTHMETRICS"."IDX1904030910353" ON "HEALTHMETRICS"."PURESCALE_CF" ( "SNAPSHOT_BATCH_ID" ASC) ALLOW REVERSE SCANS COLLECT STATISTICS;
CREATE INDEX "HEALTHMETRICS"."IDX1904030910354" ON "HEALTHMETRICS"."CLUSTER_HOST" ( "SNAPSHOT_BATCH_ID" ASC) ALLOW REVERSE SCANS COLLECT STATISTICS;
CREATE INDEX "HEALTHMETRICS"."IDX1904030910355" ON "HEALTHMETRICS"."PURESCALE_INSTANCE" ( "SNAPSHOT_BATCH_ID" ASC) ALLOW REVERSE SCANS COLLECT STATISTICS;
CREATE INDEX "HEALTHMETRICS"."IDX1904030910356" ON "HEALTHMETRICS"."APPCONNECTIONS" ( "SNAPSHOT_BATCH_ID" ASC) ALLOW REVERSE SCANS COLLECT STATISTICS;
CREATE INDEX "HEALTHMETRICS"."IDX1904030910357" ON "HEALTHMETRICS"."TBSPACE_UTILIZATION" ( "SNAPSHOT_BATCH_ID" ASC) ALLOW REVERSE SCANS COLLECT STATISTICS;
CREATE INDEX "HEALTHMETRICS"."IDX1904030910358" ON "HEALTHMETRICS"."LOG_UTILIZATION" ( "SNAPSHOT_BATCH_ID" ASC) ALLOW REVERSE SCANS COLLECT STATISTICS;
CREATE INDEX "HEALTHMETRICS"."IDX1904030910359" ON "HEALTHMETRICS"."TABLE_AVAILABLE" ( "SNAPSHOT_BATCH_ID" ASC) ALLOW REVERSE SCANS COLLECT STATISTICS;
CREATE INDEX "HEALTHMETRICS"."IDX1904030910360" ON "HEALTHMETRICS"."TBSPACE_CONT_UTILIZATION" ( "SNAPSHOT_BATCH_ID" ASC) ALLOW REVERSE SCANS COLLECT STATISTICS;
CREATE INDEX "HEALTHMETRICS"."IDX1904030910361" ON "HEALTHMETRICS"."HADR_OPERATIONAL" ( "SNAPSHOT_BATCH_ID" ASC) ALLOW REVERSE SCANS COLLECT STATISTICS;
CREATE INDEX "HEALTHMETRICS"."IDX1904030910362" ON "HEALTHMETRICS"."STORAGE_PATH" ( "SNAPSHOT_BATCH_ID" ASC) ALLOW REVERSE SCANS COLLECT STATISTICS;
CREATE INDEX "HEALTHMETRICS"."IDX1904030910363" ON "HEALTHMETRICS"."BLOCKING_LOG_ALERTS" ( "SNAPSHOT_BATCH_ID" ASC) ALLOW REVERSE SCANS COLLECT STATISTICS;

CREATE INDEX "HEALTHMETRICS"."IDX1904030910364" ON "HEALTHMETRICS"."PURESCALE_HADR_STATS" ( "SNAPSHOT_BATCH_ID" ASC) ALLOW REVERSE SCANS COLLECT STATISTICS;

Document Location

Worldwide

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SS5Q8A","label":"IBM Data Server Manager"},"Component":"","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF016","label":"Linux"},{"code":"PF033","label":"Windows"}],"Version":"1.1,1.1.1,1.1.2, \n2.1,2.1.1,2.1.2,\n2.1.3,2.1.4,2.1.5,\n2.1.5.1,2.1.5.2,2.1.5.3","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
29 July 2019

UID

ibm10960974

Page Feedback