Re: Slow select queries with joins, fast queries with materialized view: High PHYSICAL_READS value in V$SESS_IO

From: <>
Date: Sat, 16 Feb 2008 11:25:26 +0100
Message-ID: <>

On Thu, 14 Feb 2008 08:49:48 +0100, Alex Traud <> 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
> (>1000)
>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
>of reads.

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 DBA
Received on Sat Feb 16 2008 - 04:25:26 CST

Original text of this message