Welcome! The Getting Started space is intended for discussions about the Oracle Community website, not for product-specific questions.
There are tips on how to find the correct space on the Getting Started page, which also has instructions on how to change your display name so you're not just a number.
In the ACTIONS box on the top right of your post, you should be able to click on "Move", and then specify the correct space, so the right experts can see your question. (A moderator here can move the post for you if you specify which Oracle product you're using.)
Thanks and good luck!
Confirm you have an index (prod, cardtype, STAT).
Just because the explain plan says the CPU is 100%, doesn't mean the CPU of the server is 100% utilised.
How long does the query take to execute?
*** Moderator note: I've now moved your question over to the SQL and PL/SQL space where you'll get better assistance for your question.
The CPU% in the explain plan just indicates that the plan thinks that pretty much all of the work done by that query will be done by the CPU. It doesn't mean that the CPU of the server will be 100% utilized by that query.
The "cost" of the explain plan is not in %, it's just a number that is used internally when the optimizer compares different plans for the same query so that it can pick out, what it thinks is, the best plan for that query. You can't even reliably compare that number to other queries to say that one would be 'better' than the other, because it doesn't work like that.
The main focus for looking at explain plans is to look at the expected amount of I/O that will be done.
On top of that you will want to look at execution traces to see where the work is actually done when the query executes, not just what the explain plan thought it may do.
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 %.
Please click on URLs below & respond accordingly
Better concentrate on the access path. Does that use indexes that help? If you have an index unique scan followed by table access by index rowid, then that's the best thing you may have even if it shows CPU 100%.
If you happen to have an index range scan, then it is not as well as an index unique scan, and that may perform better or worse than a full table scan: yet the optimizer is supposed to take the right decision between using an index or not - and, in most of the cases it does the right choice -, only that it needs statistics that are not stale (you may see if the statistics are stale or not in user_tab_statistics and user_ind_statistics).
Anyway, RTM first in order to be able to understand an execution plan.
A few things I got just by asking Google for "oracle execution plan"
And there are many others...