Impact On DB Tables And Indexes Given Data Volume Increase In Network Query Transactions (Doc ID 1502696.1)

Last updated on AUGUST 03, 2022

Applies to:

Goal

ASAP Performance And Tuning issue.

We are expecting greater utilization of DB tables and indexes with increase in the transaction of network query.

With new functionality going live as part of a release, there is an increase in the transaction of network query. As a result of this, we are expecting few tables to be highly utilized and this would impact on not just the DB tablespace utilization but also any potential performance degradation.

A cartridge is modified to get large response from new network element. Current network query returns less data in response while the new modified query returns large data in response. This modification and the resulting transaction, populates the SARM tables tbl_info_parm and tbl_srq_parm with a few hundred more rows for each work order transaction. The percentage increase is projected to be 150% for tbl_info_parm and 50% for tbl_srq_parm (which translate to daily increase of 25 million rows from present 10 million rows for tbl_info_parm and increase of 43 million rows from present 28 million rows for tbl_srq_parm). With current 90 days retention policy, the projected number of rows will be around 2.5 billion in tbl_info_parm and 4 billion in tbl_srq_parm). Also, with increase in table rows, indexes are increased.

The requirement is to know if this scenario affects ASAP order processing and DB queries and also the OCA client application queries. Also, whether the retention period of transaction data need to be modified from present 90 days.

Solution

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