Re: Why is this query slow?
From: ddf <oratune_at_msn.com>
Date: Fri, 12 Mar 2010 05:46:14 -0800 (PST)
Message-ID: <72b7918b-113a-40af-aabd-fd1b992008a2_at_e19g2000prn.googlegroups.com>
On Mar 12, 5:34 am, vsevolod afanassiev
<vsevolod.afanass..._at_gmail.com> wrote:
> I have been doing SQL query tuning for a while and in most cases this
> is relatively straightforward excercise. For example: if a query
> selects only a few rows from large table then it should be accessed
> through index, if a query selects more than 10% of the rows the table
> should be accessed through full table scan. In many cases I was able
> to reduce number of buffer gets from something like 200,000,000 to
> less than 1000 per execution by creating indexes and adding a few
> hints.
>
> The difficulty I have now is that the query is very simple and as far
> as I can see there is nothing wrong
> with plan. Still it is slow.
>
> The query is
>
> select * from transactions
> where tx_date between (date1 and date2) and
> status = 'AAA' and
> ID not in
> (select ID from transaction where tx_date between (date3 and date4)
> and status = 'AAA')
>
> So we have a query and a subquery. Subquery is not correlated. Table
> has 11 million rows.
> Nothing fancy: no LOBS, LONG, etc, all columns are either NUMBER or
> VARCHAR2.
> Column ID has type VARCHAR2(32)
>
> Executed separately both query and subquery run in less than a
> second,
> use index on tx_date. Main query returns 250 records while subquery
> returns 2500.
>
> I am running it on lightly loaded UNIX server with plenty of free
> memory, SGA is 4 GB, PGA is 500 MB, there are no other heavy queries.
> Still it takes 2 minutes.
>
> Autotrace shows 2.2 million buffer gets and no disk reads.
>
> There is nothing in wait events: no latch waits, no buffer busy, etc.
>
> My main problem is: there is nothing wrong with execution plan for
> complete query.
> It shows use of the same index on tx_date by both main query and
> subquery and FILTER.
>
> If I save results of the subquery in a temporary table then such
> modified query
> completes in less than a second:
>
> select * from transactions
> where tx_date between (date1 and date2) and
> status = 'AAA'
> ID not in
> (select ID from temp_table)
>
> Surely Oracle should be able to hold 2,500 records from the subquery
> in
> memory and access it more efficiently than my temporary table? But it
> doesn't do it.
>
> I can re-write the query using MINUS and it will run much faster. I am
> just puzzled why is it so slow.
>
> This is plan from AUTOTRACE (9.2.0.8 on AIX):
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=45545 Card=81
> Bytes=3888)
> 1 0 FILTER
> 2 1 FILTER
> 3 2 ABLE ACCESS (BY INDEX ROWID) OF
> 'TRANSACTIONS' (Cost=1076 Card=81 Bytes=3888)
> 4 3 NDEX (RANGE SCAN) OF 'TRANSACTIONS_IND01' (NON-
> UNIQUE) (Cost=821 Card=105815)
> 5 1 FILTER
> 6 5 TABLE ACCESS (BY INDEX ROWID) OF
> 'TRANSACTIONS' (Cost=549 Card=4 Bytes=192)
> 7 6 INDEX (RANGE SCAN) OF 'TRANSACTIONS_IND01' (NON-
> UNIQUE) (Cost=419Card=53853)
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 2326009 consistent gets
> 0 physical reads
> 0 redo size
> 7609 bytes sent via SQL*Net to client
> 832 bytes received via SQL*Net from client
> 18 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 253 rows processed
)
select * from transactions
where tx_date between (date1 and date2) and status = 'AAA' and
ID not in
(select ID from tlimit);
Date: Fri, 12 Mar 2010 05:46:14 -0800 (PST)
Message-ID: <72b7918b-113a-40af-aabd-fd1b992008a2_at_e19g2000prn.googlegroups.com>
On Mar 12, 5:34 am, vsevolod afanassiev
<vsevolod.afanass..._at_gmail.com> wrote:
> I have been doing SQL query tuning for a while and in most cases this
> is relatively straightforward excercise. For example: if a query
> selects only a few rows from large table then it should be accessed
> through index, if a query selects more than 10% of the rows the table
> should be accessed through full table scan. In many cases I was able
> to reduce number of buffer gets from something like 200,000,000 to
> less than 1000 per execution by creating indexes and adding a few
> hints.
>
> The difficulty I have now is that the query is very simple and as far
> as I can see there is nothing wrong
> with plan. Still it is slow.
>
> The query is
>
> select * from transactions
> where tx_date between (date1 and date2) and
> status = 'AAA' and
> ID not in
> (select ID from transaction where tx_date between (date3 and date4)
> and status = 'AAA')
>
> So we have a query and a subquery. Subquery is not correlated. Table
> has 11 million rows.
> Nothing fancy: no LOBS, LONG, etc, all columns are either NUMBER or
> VARCHAR2.
> Column ID has type VARCHAR2(32)
>
> Executed separately both query and subquery run in less than a
> second,
> use index on tx_date. Main query returns 250 records while subquery
> returns 2500.
>
> I am running it on lightly loaded UNIX server with plenty of free
> memory, SGA is 4 GB, PGA is 500 MB, there are no other heavy queries.
> Still it takes 2 minutes.
>
> Autotrace shows 2.2 million buffer gets and no disk reads.
>
> There is nothing in wait events: no latch waits, no buffer busy, etc.
>
> My main problem is: there is nothing wrong with execution plan for
> complete query.
> It shows use of the same index on tx_date by both main query and
> subquery and FILTER.
>
> If I save results of the subquery in a temporary table then such
> modified query
> completes in less than a second:
>
> select * from transactions
> where tx_date between (date1 and date2) and
> status = 'AAA'
> ID not in
> (select ID from temp_table)
>
> Surely Oracle should be able to hold 2,500 records from the subquery
> in
> memory and access it more efficiently than my temporary table? But it
> doesn't do it.
>
> I can re-write the query using MINUS and it will run much faster. I am
> just puzzled why is it so slow.
>
> This is plan from AUTOTRACE (9.2.0.8 on AIX):
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=45545 Card=81
> Bytes=3888)
> 1 0 FILTER
> 2 1 FILTER
> 3 2 ABLE ACCESS (BY INDEX ROWID) OF
> 'TRANSACTIONS' (Cost=1076 Card=81 Bytes=3888)
> 4 3 NDEX (RANGE SCAN) OF 'TRANSACTIONS_IND01' (NON-
> UNIQUE) (Cost=821 Card=105815)
> 5 1 FILTER
> 6 5 TABLE ACCESS (BY INDEX ROWID) OF
> 'TRANSACTIONS' (Cost=549 Card=4 Bytes=192)
> 7 6 INDEX (RANGE SCAN) OF 'TRANSACTIONS_IND01' (NON-
> UNIQUE) (Cost=419Card=53853)
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 2326009 consistent gets
> 0 physical reads
> 0 redo size
> 7609 bytes sent via SQL*Net to client
> 832 bytes received via SQL*Net from client
> 18 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 253 rows processed
How does this rewrite perform:
with tlimit as (
select ID
from transaction
where tx_date between (date3 and date4)
and status = 'AAA'
)
select * from transactions
where tx_date between (date1 and date2) and status = 'AAA' and
ID not in
(select ID from tlimit);
Yes, it's similar to your temp table version except this uses only one permanant object. You did not mention how many rows were in this transaction table nor did you post an event 10046 trace -- possibly you should set event 10046 and see what the trace file reports. You also failed to post autotrace results from your temp table query; how do those differ from the autotrace statistics you reported here? Look especially at the consistent gets returned as I expect they will be far fewer in the factored query (and in your temp table version).
David Fitzjarrell Received on Fri Mar 12 2010 - 07:46:14 CST