Re: Slow select queries with joins, fast queries with materialized view: High PHYSICAL_READS value in V$SESS_IO
From: Alex Traud <alex.traud_at_gmx.de>
Date: Thu, 14 Feb 2008 08:49:48 +0100
Message-ID: <fp0rqo$27b$1@aioe.org>
>> Hello Everybody,
>>
>> I am facing a hard problem with poor performing queries
>> that involves a join of two tables for quite a while now.
>> Here's the situatation:
>>
>> we have the tables
>>
>> - LOCATION: a table contained geo information for locations
>> with x/y coordinates, Primary key: LOCATION_ID,
>> columns X,Y and others
>>
>> - LOCATION_LAYER_ASSIGN: an assignement table that assigns
>> locations to layer (columns LOCATION_ID, LAYER_ID, m:n)
>>
>> - a materialzed view MV_LOCATIONS that is created using these
>> two tables. Columns LOCATION_ID, X, Y, LAYER_ID.
>>
>> fast query against MV_LOCATIONS:
>> ---
>> SELECT *
>> FROM MV_LOCATIONS WHERE
>> (X BETWEEN ? AND ?) AND (Y BETWEEN ? AND ?)
>> AND LAYER_ID IN (<layer_ids>)
>> ---
>>
>> slow querey against LOCATION/LOCATION_LAYER_ASSIGN:
>> ---
>> SELECT *
>> FROM
>> LOCATION INNER JOIN LOCATION_LAYER_ASSIGN
>> ON (LOCATION.LOCATION_ID = LOCATION_LAYER_ASSIGN.LOCATION_ID)
>> WHERE
>> (X BETWEEN ? AND ?) AND (Y BETWEEN ? AND ? )
>> AND LAYER_ID IN (<layer_ids>)
>> ---
>>
>> We ran some thousand test queries and anaylized the
>> table V$SESS_IO for a LOCATION and MV_LOCATIONS based
>> database session. The result was that the value
>> PHYSICAL_READS associated with the LOCATION based
>> session was 10 to 1000 times higher compared to the
>> value associated with the MV_LOCATION based session.
>>
>> Obviously, in the former case, we are doing a lot
>> more physical IO than in the later one, however, I
>> cannot figure out why.
>>
>> We also tried replacing the JOIN with a sub select
>> and other changes to the SQL statement like replacing
>> the BETWEEN statements with relational operator.
>> However, nothing improved.
>>
>> can anyone give me a hint what might be going wrong
>> here and what I can do?
>>
>> many thanks in advance.
>>
>> Alex
Date: Thu, 14 Feb 2008 08:49:48 +0100
Message-ID: <fp0rqo$27b$1@aioe.org>
fitzjarrell_at_cox.net schrieb:
> On Feb 13, 2:37 pm, Alex Traud <alex.tr..._at_gmx.de> wrote:
>> Hello Everybody,
>>
>> I am facing a hard problem with poor performing queries
>> that involves a join of two tables for quite a while now.
>> Here's the situatation:
>>
>> we have the tables
>>
>> - LOCATION: a table contained geo information for locations
>> with x/y coordinates, Primary key: LOCATION_ID,
>> columns X,Y and others
>>
>> - LOCATION_LAYER_ASSIGN: an assignement table that assigns
>> locations to layer (columns LOCATION_ID, LAYER_ID, m:n)
>>
>> - a materialzed view MV_LOCATIONS that is created using these
>> two tables. Columns LOCATION_ID, X, Y, LAYER_ID.
>>
>> fast query against MV_LOCATIONS:
>> ---
>> SELECT *
>> FROM MV_LOCATIONS WHERE
>> (X BETWEEN ? AND ?) AND (Y BETWEEN ? AND ?)
>> AND LAYER_ID IN (<layer_ids>)
>> ---
>>
>> slow querey against LOCATION/LOCATION_LAYER_ASSIGN:
>> ---
>> SELECT *
>> FROM
>> LOCATION INNER JOIN LOCATION_LAYER_ASSIGN
>> ON (LOCATION.LOCATION_ID = LOCATION_LAYER_ASSIGN.LOCATION_ID)
>> WHERE
>> (X BETWEEN ? AND ?) AND (Y BETWEEN ? AND ? )
>> AND LAYER_ID IN (<layer_ids>)
>> ---
>>
>> We ran some thousand test queries and anaylized the
>> table V$SESS_IO for a LOCATION and MV_LOCATIONS based
>> database session. The result was that the value
>> PHYSICAL_READS associated with the LOCATION based
>> session was 10 to 1000 times higher compared to the
>> value associated with the MV_LOCATION based session.
>>
>> Obviously, in the former case, we are doing a lot
>> more physical IO than in the later one, however, I
>> cannot figure out why.
>>
>> We also tried replacing the JOIN with a sub select
>> and other changes to the SQL statement like replacing
>> the BETWEEN statements with relational operator.
>> However, nothing improved.
>>
>> can anyone give me a hint what might be going wrong
>> here and what I can do?
>>
>> many thanks in advance.
>>
>> Alex
> > What did the respective execution plans reveal? > > > David Fitzjarrell
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.
regards,
Alex Received on Thu Feb 14 2008 - 01:49:48 CST