Re: Full table scan performance differences - why?
Date: Fri, 19 Sep 2008 04:40:30 -0700 (PDT)
Message-ID: <b33e264a-57e6-40fb-a29b-526a1b8555fd@s50g2000hsb.googlegroups.com>
On 18 Sep., 21:29, "fitzjarr..._at_cox.net" <orat..._at_msn.com> wrote:
> Comments embedded.
> On Sep 18, 11:50 am, steph <stepha..._at_yahoo.de> wrote:
>
>
>
> > Hello group,
>
> > I've got a table with roughly 7 million rows in a 10G database.
>
> > When I query it like that it returns results in about a second:
>
> > SELECT *
> > FROM VERM
> > WHERE 1=1
> > AND VER_DAT >= to_date('20.07.2008','dd.mm.yyyy');
>
> > The plan looks like this:
> > SELECT STATEMENT ALL_ROWSCost: 11,111 Bytes: 210,924 Cardinality:
> > 5,022
> > 1 TABLE ACCESS FULL TABLE VERM Cost: 11,111 Bytes: 210,924
> > Cardinality: 5,022
>
> > When I change the query so that the date value (i.e. the SAME date
> > value) is returned from a package function (which in turn just returns
> > a global package variable) all is different:
>
> > SELECT *
> > FROM VERM
> > WHERE 1=1
> > AND VER_DAT >= ret_pkg.get_date;
>
> > It takes one minute to return results and the plan looks like this:
> > SELECT STATEMENT ALL_ROWSCost: 12,667 Bytes: 14.839.356
> > Cardinality: 353,318
> > 1 TABLE ACCESS FULL TABLE VERM Cost: 12,667 Bytes: 14.839.356
> > Cardinality: 353,318
>
> > I wonder why.
>
> You're calling a function for each and every row you process. Have
> you taken the time to trace what that function is doing? Probably
> not ...
>
> > Probably the reason is that the date-value is a constant
> > in the first query but not in the second.
>
> Partly.
>
> > But in reality the second
> > value is also a constant - it does not change.
>
> But you're now going through any number of additional 'gyrations' to
> get that 'constant'.
>
> > I've tried to improve
> > performance of the second query by using a restrict_references pragma
> > for the package function - but this didn't help.
>
> Why should it?
My idea was, that if I tell the database that this value isn't changing, i.e. not reading any data, it does not need to be evaluated for every row. This was not very clever probably.
>
> > Somehow I need to
> > communicate to Oracle that the first and second queries are to be
> > executed the same way - through an optimizer hint maybe. How can I
> > solve this?
>
> Discover what, exactly, this function is doing and make THAT more
> efficient. Or re-write your query to call the function ONCE for the
> duration of the query:
>
> WITH PKGDT as (
> SELECT ret_pkg.get_date proc_dt
> from dual
> )
> SELECT *
> FROM VERM, pkgdt
> WHERE 1=1
> AND VER_DAT >= proc_dt;
>
Tried this, but it didn't improve things a bit. Besides the function is only returning a global package variable, nothing else, something like:
function get_date return date is
begin
return g_date;
end;
And here the statistics:
SELECT *
FROM VERM
WHERE 1=1
AND VER_DAT >= to_date('20.07.2008','dd.mm.yyyy')
call count cpu elapsed disk query
current rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 1.09 1.08 49912 49921 0 501
------- ------ -------- ---------- ---------- ---------- ----------
total 4 1.09 1.09 49912 49921 0 501
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 43
Rows Row Source Operation
------- ---------------------------------------------------
501 TABLE ACCESS FULL VERM (cr=49921 pr=49912 pw=0 time=1079561 us)
SELECT *
FROM VERM
WHERE 1=1
AND VER_DAT >= ret_pkg.get_date
call count cpu elapsed disk query
current rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 60.37 58.98 49912 49921 0 501
------- ------ -------- ---------- ---------- ---------- ----------
total 4 60.37 58.98 49912 49921 0 501
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 43
Rows Row Source Operation
------- ---------------------------------------------------
501 TABLE ACCESS FULL VERM (cr=49921 pr=49912 pw=0 time=58984517 us)
So there's a lot of cpu-time being used. I know, this is due to function calls. So probably I can't change much about this situation ...
regards,
stephan
Received on Fri Sep 19 2008 - 06:40:30 CDT