Re: Slow select queries with joins, fast queries with materialized view: High PHYSICAL_READS value in V$SESS_IO
Date: Sat, 16 Feb 2008 11:25:26 +0100
On Thu, 14 Feb 2008 08:49:48 +0100, Alex Traud <alex.traud_at_gmx.de> wrote:
>we did not query for an executaion plan for every testcase
>we ran, however, for the queries we got an execution plan
>for we saw, that
>- the costs for the Joined query (involving LOCATION)
> were low (<100)
>- the costs for the MV_LOCATIONS based query were high
>As the tests use varying xmin,xmax and ymin,ymax parameters
>the execution plans will also return different costs, so its
>difficult to make a more specific statement on this.
>I think the problem is really the PHYSICAL_READ value. But
>until know, it's not clear to me what causes the high number
Cost is an irrelevant criterion.
The physical_read value is also not the problem. The problem is your statement is accessing only one of the m:n columns of the junction tabel, and indexing structure of MV_LOCATIONS is different from indexing structure of the underlying tables.
Post those explain plan results, using dbms_xplan
-- Sybrand Bakker Senior Oracle DBAReceived on Sat Feb 16 2008 - 04:25:26 CST