select prod_id, prod_name, unit_price, qty from product_mas where 
prod_id = '23';

1) CARDINALITY RULES OPTIMIZATION.  Check your query plan's cardinality estimates with actuals either by using gather_plan_statistics hint, or by running your own reconstruction queries.  When numbers are off my more than 5X there is clearly an issue that needs investigating.  Be especially warry of any cardinality estimate of 1 that does not come from a UNIQUE INDEX SCAN.

2) most problems come from these few mistakes.  Since these are the 99% contributors to bad performance, it stands to reason you should do something about them.

a) bad DATABASE design
     1. not relational in design
     2. constraints not defined (PK/UK/FK/NOT NULL/CHECK)
     3. FK constraints not indexed (consider OLTP vs. WAREHOUSE)
     4. poor datatype selection DATE=DATE not number, not varchar2; NUMBER=?; VARCHAR2=?
     5. dummy values used instead of null (31-DEC-4712,9999999999999) and the like (truthfully I do this too)
     6. poor indexing strategy (missing index, wrong columns in an index)

b) statistics collection errors
     1. missing
     2. stale
     3. bad collection practices
     4. improper histograms (too many or too few)
     5. no extended stats when useful
     6. forgot an index somewhere
     7. didn't do partitions and sub-partitions and global (granularity='ALL')

c) poor code
     1. row by row design
     2. ignorance of advanced features

d) no respect for the Full Table Scan (FTS IS THE FUTURE, LEARN THE 2% RULE).

e) laziness, developer stopped once he got the right answer and did not bother to think about how it could be written to go fast too.

3) My first step in query tuning is to determine the basic query type.  It is either an OLTP kind of query or a WAREHOUSE kind of query.  I'd like to say there is something in between but from an optimizer perspective there isn't really.  Either your query should be driving from a small number of rows and thus should be using NESTED LOOPS AND INDEX LOOKUPS, or it is looking at more than 2% of the data and thus should be using FULL TABLE SCANS AND HASH JOINS.  Figure out which it should be (SEE CARDINALITY ABOVE) then check to make sure your query plan is doing it.

4) DRIVING TABLE is key for an OLTP kind of query.  Are you starting with the right driving table?  Again (see CARDINALITY ABOVE).