Partition_utils Execution Caused Database Performance Issue (Doc ID 2711575.1)

Last updated on MARCH 31, 2023

Applies to:

Goal

Qn1: partition_utils is executed once a month. The user observed that after execution, all the front-end applications took a long time to process. One of the custom opcode, which is mostly doing searches in different storable classes, took more than one hour to execute. Indexes and execution plans were rebuilt to address the issue.

1. What exactly the partition_utils do in database?
2. Could partition_utils process cause slowness in the pcm_op_search process of Billing Revenue and Management (BRM)?
3. Could partition_utils change or erase the execution plan or indexes in database? If yes, what can be done to prevent or avoid this?
4. What could cause or impact on the pcm_op_search process slowness?
5. What is the logic that pcm_op_search uses to create the Structured Query Language (SQL) query and how data manager (DMO/dm_oracle) defines the arguments for the query?
 
Qn2: Is there documentation or explanation specifically to how the PCM_OP_SEARCH converts into Structured Query Language (SQL) query and defines the quantity of arguments used?
 

Solution

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