Query cost high in oracle

For 1st one.. here is the explain plan
Plan
SELECT STATEMENT ALL_ROWS Cost: 66,710 Bytes: 27,984 Cardinality: 528
       1 TABLE ACCESS FULL TABLE ORD.ORD_COMPONENT Cost: 66,710 Bytes: 27,984 Cardinality: 528

for 2nd
SELECT STATEMENT ALL_ROWS Cost: 7,552 Bytes: 6,138 Cardinality: 99
       2 COUNT STOPKEY
              1 TABLE ACCESS FULL TABLE ORD.ORD_COMPONENT Cost: 7,552 Bytes: 6,200 Cardinality: 100

How large is the table?  It is collecting 528 rows out of how much?

you need to create indexes for columns
type and status;
as you are doing FTS that is why they are expensive.

are your statistics up to date?  do you have any indexes as itrec suggested?  are the index stats up to date?

is status numeric?  if so,  you should use

status=1  

NOT  

status = '1'

only use rownum < 100 if you want to restrict your results to 99 random rows.

I cannot add indexes to the table since I dont own them .. I can bring down cost only by using hints or tuning queries.. is it feasible.. ?

first, don't tune costs.   lower costs do NOT indicate a better plan, higher costs do NOT indicate a worse plan
tune resource consumption and response time

how long does the query take and how long do you want it to take?
if you trace the execution, what are the wait events?
if your main waits are scattered reads then you most likely will need an index.  either create it your self or have the owner do it.


second, you don't need to be the owner of a table to create an index for it.  You can create an index in your own schema  you just need the "create any index" privilege
if you don't have that privilege then ask your dba to do it for you.

This is just to correct 800 records.. after that I will not use this query .. so just a simple query with some clause should work. with rownum I got it down to 7k from 70K. is there anything I can add to improve it by a little more?

of course,
you changed the query to do a fraction of the work  and the cost was reduced to a fraction.  Your query was going to modify 800 rows, now it will only modify 99  so the work goes down and the cost reflected that,

don't tune the cost,  it's a made up number.    Note the cost did NOT go down proportionately with the reduction of rows.  

don't tune the cost

small cost != good query
big cost != bad query

if this is a one time effort, then don't bother tuning it.
you are spending more time, effort and money for something that will never be recouped by any efficiency you put into the query.

my main focus here is the response time .. does small cost mean better response time?
I dont want to use up resources when retrieveing this query.

small cost does NOT mean better response time...

compare these two queries...

on my system

The first has a cost of about 18 quintillion   (1.8x10^19)
the second has a cost of 2

both of these queries return in about 110-120 milliseconds

WITH x AS
    (SELECT 1 FROM
        (SELECT 1 FROM dual UNION ALL SELECT 1 FROM dual UNION ALL SELECT 1 FROM dual UNION ALL SELECT 1 FROM dual),
        (SELECT 1 FROM dual UNION ALL SELECT 1 FROM dual UNION ALL SELECT 1 FROM dual UNION ALL SELECT 1 FROM dual),
        (SELECT 1 FROM dual UNION ALL SELECT 1 FROM dual UNION ALL SELECT 1 FROM dual UNION ALL SELECT 1 FROM dual),
        (SELECT 1 FROM dual UNION ALL SELECT 1 FROM dual UNION ALL SELECT 1 FROM dual UNION ALL SELECT 1 FROM dual)
    )
SELECT ROWNUM FROM x,x,x,x,x,x,x,x,x,x
where rownum < 1000;
 
vs
 
select level from dual connect by level < 1000
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:

so to get a good response time , is indexing the only way?

for something as simple as "select * from table1 where type='abc' and status='1'"
you're not going to get a lot of tuning opportunities.

you "might" be able to get better performance with PARALLEL hint.

I see in a post above you said you will be correcting 800 rows.
Does that mean you are iterating over the result set of this select to issue updates?

if so, the performance problem isn't in this query, but the iteration.
try to consolidate the work into a single sql and skip the iteration.

also, just to be clear on my cost example above.

it's somewhat misleading because the second query is in fact better than the first,
but not 9 quintillion times better.

That's the problem with costs, if the value changes a little bit the change doesn't correspond to anything consistently measurable in time or resource consumption.

no .. getting result set is the issue..it is taking a long time because type or status in the table is not indexed..

also note, indexes won't necessarily help.

if you are querying 800 rows that are spread across most of the data blocks  of your table then an index won't help at all.

what are your wait events?  and how many of them are there?

How can I find that out?

trace your session  (dbms_monitor)  and then look at your trace file.

or   run this before and after your select and compare the results

SELECT n.name, s.VALUE
FROM v$mystat s, v$statname n
WHERE s.statistic# = n.statistic#

I am not a dba and I am not even running the other query in prod due to response time ..I am just getting the explain plan in prod

do you have a representative test environment?

what is response time in production?

we do have a test env that is not even size of the prod. nobody has tried it , they are scared looking at the cost itself to try

I tried the query in prod it took 3 min to bring up 800 rows .  I ran the same using java app.it took 43 sec for 10 records .. almost same for 1 record

is table1 actually a table or a view? how big, are your table statistics up to date?
what does the plan actually look like (use dbms_xplan.display)
did you capture your session statistics?