Home » RDBMS Server » Performance Tuning » Explain plan Cost vs time elapsed
Explain plan Cost vs time elapsed [message #236971] Fri, 11 May 2007 08:13 Go to next message
beetel
Messages: 96
Registered: April 2007
Member
Initially, I had the following query in my program:

        select a.acct_id, b.cust_id, c.org_id
          from tableA a,
               tableB b,
               tableC c
         where a.acct_id  = b.acct_id
           and a.invoice_date between :v_date_range
           and  a.uom_id   = c.uom_id(+);


where :v_date_range can be something like

to_date('01-NOV-2006','dd-MON-yyyy') and to_date('30-NOV-2006','dd-MON-yyyy')

TableA is partitioned by invoice_date. Then I had to put an extra condition: and a.pk_id <= :max_pk_limit
where :max_pk_limit can be any integer. and PK_ID is the primary key of TableA which has 300M records. So the query is now:

            select a.acct_id, b.cust_id, c.org_id
              from tableA a,
                   tableB b,
                   tableC c
             where a.acct_id  = b.acct_id
               and a.invoice_date between :v_date_range
               and  a.uom_id   = c.uom_id(+) 
               and  a.pk_id    <= :max_pk_limit;


The old query costs more than the new query as per explain plan. BUT in the actual execution, the new query executes 5x more than the old query. Why is that so??? I put hints so that the second query has the same explain plan as the first. But still the costs are different. And the new query still runs slower than the first.

Does it mean that the extra condition/filter makes Oracle do an extra step -- even though the explain plan, or rather execution path are the same (but the costs are still different)? I think so, that's why the costs are different, though paths are the same.. Am I right???
Re: Explain plan Cost vs time elapsed [message #237103 is a reply to message #236971] Sat, 12 May 2007 02:30 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
There is a quite low correspondance between computed cost and actual exec time.

Michael
Re: Explain plan Cost vs time elapsed [message #237108 is a reply to message #236971] Sat, 12 May 2007 02:41 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Given that:
- you don't post your Oracle version
- you don't post your real queries (the ones you posted are syntaxically wrong)
- you don't post your table definitions
- you don't post indexes
- you don't post statistics
- you don't post your explain plan
- you don't post...

How can you hope we help you? YOYO!

Regards
Michel
Previous Topic: sql query too slow -maybe needs indexes?
Next Topic: SGA size
Goto Forum:
  


Current Time: Sun Dec 04 04:16:50 CST 2016

Total time taken to generate the page: 0.05248 seconds