Pin_bill_accts is Taking Longer Time to Execute (Doc ID 2157974.1)

Last updated on DECEMBER 10, 2022

Applies to:

Goal

One of the queries as part of pin_bill_accts is taking longer time to execute approximately around 60 seconds in 7.4 PS22. This query is going for full table scan on "event_billing_incentive_t" table and is impacting the billing performance in production. Details are as below.

SQL_ID: 3k8n5ba3thphj

SQL Query:

SELECT bill_obj_DB,

bill_obj_ID0,

bill_obj_TYPE,

bill_obj_REV,

channel_id,

current_total,

customer_segment,

pay_type,

previous_total,

trans_id

FROM event_billing_incentive_t

WHERE obj_id0 = :1

Here is the recommendation from DBA: 

  Introduce index on the “obj_id0” column of the table “event_billing_incentive_t” which will improve efficiency by 99.99%.
  Currently there is no index on any of the columns of the table.

Kindly suggest what can be done to improve the performance.

Solution

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