Impact On The Performance Of ASAP, If Indexes Are Created On ASAP Tables. (Doc ID 1502008.1)

Last updated on MARCH 01, 2024

Applies to:

Goal

In ASAP production environment, dashboard processes are used which collect Work Order / ASDL / CSDL processing data/statistics like failed/completed ASDL per NE etc from SARM tables like (TBL_ASDL_LOG, TBL_WRK_ORD, TBL_SRQ etc) at an interval of every 15 minutes. Business is looking to change the frequency to 5 minutes. This Query is taking around 10-12 minutes to execute every time. However, this process is consuming lot of I/O's on database side and is impacting performance of database as average I/O% is almost 100% always.

DBAs at our end, suggest 2 approaches that can improve these I/O's and time taken by the queries every time:

1. Creating Indexes on ASAP Tables. Following Index need to be created.

TBL_ASDL_LOG - Index on START_DTS and ASDL_STAT

2. Creating materialized views on ASAP Tables.

Questions are which of the above would be a suggested approach without impacting performance of ASAP? If we create Indexes on SARM database table, would there be any implication on provisioning performance of ASAP? The same questions go for creating materialized views too.

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.