Re: Oracle 8i and poor query performance in some queries

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Wed, 22 Oct 2003 16:09:53 +0100
Message-ID: <3f969dc1$0$246$ed9e5944@reading.news.pipex.net>

"Heikki Siltala" <heikki.siltala_at_stakes.fi> wrote in message news:26e49b9f.0310212331.b261d7b_at_posting.google.com...
> "Noel" <tbal_at_go2.pl> wrote in message

news:<bn3dah$jso$1_at_inews.gazeta.pl>...
>
> > Does:
> > select id from a1
> > minus
> > select id from a2;
> > takes so much time, eigher??
>
> Tried this one:
>
> select count(*) from (
> select id from a1
> minus
> select id from a2
> );
>
> It executes in 0,08 seconds! And the row counts in plan seem more
> realistic now. The total cost is 560, compared to earlier 109, but the
> query executes 25212,5 times faster than the earlier one. Quite a
> peformance gain!
>
> Now the problem seems to be an optimizer problem.

I disagree.

SQL> create table a1 as select * from all_objects;

Table created.

SQL> create table a2 as select * from all_objects;

Table created.

SQL> insert into a2 select * from a2;

24766 rows created.

SQL> / 49532 rows created.

SQL> commit;

Commit complete.

SQL> analyze table a1 compute statistics;

Table analyzed.

SQL> analyze table a2 compute statistics;

Table analyzed.

SQL> select table_name,num_rows from user_tables;

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
A1                                  24765
A2                                  99064

so now we have two similar tables

SQL> set autotrace traceonly explain
SQL> select count(*) from a1 where id not in 2 (select id from a2)
3 .
SQL> ed
Wrote file afiedt.buf

1 select count(*) from a1 where object_id not in 2* (select object_id from a2)
SQL> / Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=1 Bytes=4) 1 0 SORT (AGGREGATE)

   2    1     FILTER
   3    2       TABLE ACCESS (FULL) OF 'A1' (Cost=26 Card=1239 Bytes=4
          956)

   4    2       TABLE ACCESS (FULL) OF 'A2' (Cost=103 Card=4 Bytes=16)

we get the same plan as you.

SQL> ed
Wrote file afiedt.buf

1 select /*+ RULE */ count(*) from a1 where object_id not in 2* (select object_id from a2)
SQL> / Execution Plan

0 SELECT STATEMENT Optimizer=HINT: RULE 1 0 SORT (AGGREGATE)

   2    1     FILTER
   3    2       TABLE ACCESS (FULL) OF 'A1'
   4    2       TABLE ACCESS (FULL) OF 'A2'

and we also get the same result with the old rule based optimizer. The problem is that (especially in the absence of indices) this is just a bad piece of sql to issue against these tables. Essentially the only option available to oracle is to filter the results of the first select against the subselect for each row. this means far too many tablescans. Use of MINUS is a much better option since you really want to compare two sets. Note that the idea of creating appropriate indexes will also help you greatly. I have modified the tables slightly by creating a primary key on a1(object_id) and then an index on a2(object_id). You can see the improvement below.

SQL> alter table a1 add constraint pk_a1 primary key(object_id);

Table altered.

SQL> analyze table a1 compute statistics;

Table analyzed.

SQL> select count(*) from a1 where object_id not in 2 (select object_id from a2);

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=4) 1 0 SORT (AGGREGATE)

   2    1     FILTER
   3    2       INDEX (FAST FULL SCAN) OF 'PK_A1' (UNIQUE) (Cost=4 Car
          d=1239 Bytes=4956)

   4    2       TABLE ACCESS (FULL) OF 'A2' (Cost=103 Card=4 Bytes=16)



SQL> set timing on

SQL> set autotrace off
SQL> / COUNT(*)

Elapsed: 00:05:33.07
SQL> create index idx_a2 on a2(object_id);

Index created.

Elapsed: 00:00:03.02
SQL> analyze table a2 compute statistics;

Table analyzed.

Elapsed: 00:00:10.08
SQL> select count(*) from a1 where object_id not in 2 (select object_id from a2);

COUNT(*)

Elapsed: 00:00:00.08
SQL> set autotrace on explain
SQL> / COUNT(*)

Elapsed: 00:00:00.08

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=4) 1 0 SORT (AGGREGATE)

   2    1     FILTER
   3    2       INDEX (FAST FULL SCAN) OF 'PK_A1' (UNIQUE) (Cost=4 Car
          d=1239 Bytes=4956)

   4    2       INDEX (RANGE SCAN) OF 'IDX_A2' (NON-UNIQUE) (Cost=1 Ca
          rd=4 Bytes=16)

Hopefully what this demonstrates is that by looking at the problem queries, tracing them, thinking about what they are doing it will almost always be possible to significantly improve performance. It should also demonstrate that it isn't the optimizer that has the problem but either the data model or the sql being issued.

-- 
Niall Litchfield
Oracle DBA
Audit Commission Uk


SQL>



-- 
Niall Litchfield
Oracle DBA
Audit Commission Uk

Received on Wed Oct 22 2003 - 10:09:53 CDT