In this product, the details table had 100M records and we will get 10 txns every second and we will pull the corresponding details from the below table. The query plan looks good, The cost of the query is showing 5% and CPU is showing to100%.
We had an index on prod, card type, and STAT column as well and there is no primary key on this table,.
SELECT * FROM
product WHERE prod = :B3 AND cardtype = :B2 AND
STAT = 'N' AND ROWNUM <= :B1 FOR UPDATE
Can someone guide me what is the best way to reduce the CPU %.