Re: Oracle Performance Issue

From: Holger Baer <holger.baer_at_science-computing.de>
Date: Wed, 07 Sep 2005 14:10:01 +0200
Message-ID: <dfmlap$j58$1@news.BelWue.DE>

Roland Lohse wrote:
> Robert Klemme schrieb:
>

>>>
>>> SELECT   user_group_id
>>>     FROM user_group_ou
>>> GROUP BY user_group_id
>>>   HAVING COUNT (*) = :b1 - 1
>>
>>
>>
>> Did you look at the execution plan?
>>

>
> Yes; Oracle performs a full table scan.
>

>>
>> What's that?  A boolean value stored in a VARCHAR?
>>

>
> It's a VARCHAR2(1) field containing the same value for all rows.
>

>>
>> An index on user_group_id might help - depending on the record size.  In
>> that case Oracle can do an index scan instead of a table scan (which I
>> assume it's doing right now).
>>

>
> We'll try that. Seems reasonable.
>
>

>> The of course you have all the other options that improve IO performance
>> such as distributing data on several disks, adjusting cache sizes etc.
>> Difficult to tell with the info provided so far.

>
>
> IO is not a big deal - as we should have lots of that (an EMC Symmetrix
> is direct attached). We do not have performance issues with other SQL -
> that is well written. Querieng our biggest table - which contains over
> 1.000.000.000 records - are really fast - when those queries use proper
> indices.
>
> Thanks Roland
>

The index will only help if user_group_id is declared not null. From what you've written sofar I think that shouldn't be a problem though.

Following a small demonstration ( on 10g but the principle holds true on 8i):

SQL> create table user_group_ou (user_group_id integer, dummy integer, flag varchar2(8));

Table created.

SQL> create table user_group_ou_nn (user_group_id integer not null, dummy integer, flag varchar2(8));

Table created.

SQL> insert into user_group_ou select row_number () over (partition by owner order by object_name), 2 object_id, substr (object_type,1,8) from all_objects SQL> / 47166 rows created.
SQL> insert into user_group_ou select * from user_group_ou;

47166 rows created.

SQL> / 94332 rows created.

SQL> / 188664 rows created.

SQL> /
commit;

377328 rows created.

SQL>
Commit complete.

SQL> insert into user_group_ou_nn select * from user_group_ou;

754656 rows created.

SQL> commit;

Commit complete.

SQL> create index id_ou on user_group_ou (user_group_id);

Index created.

SQL> create index id_ou_nn on user_group_ou_nn (user_group_id);

Index created.

SQL> select user_group_id from user_group_ou

   2 group by user_group_id
   3 having count(*) = 150;

no rows selected

SQL> set autotrace on
SQL> / no rows selected

Execution Plan

---

    0 SELECT STATEMENT Optimizer=CHOOSE     1 0 FILTER

    2    1     SORT (GROUP BY)
    3    2       TABLE ACCESS (FULL) OF 'USER_GROUP_OU' (TABLE)

==> No statistics, user_group_id is nullable

SQL> exec dbms_stats.gather_schema_stats (user, cascade=> true);

PL/SQL procedure successfully completed.

SQL> / no rows selected

Execution Plan

---

    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2624 Card=201 Bytes=
           1005)

    1    0   FILTER
    2    1     SORT (GROUP BY) (Cost=2624 Card=201 Bytes=1005)
    3    2       TABLE ACCESS (FULL) OF 'USER_GROUP_OU' (TABLE) (Cost=6
           33 Card=753990 Bytes=3769950)

==> Statistics, but still table full scan

SQL> edit
Wrote file afiedt.buf

   1 select user_group_id from user_group_ou_nn    2 group by user_group_id
   3* having count(*) = 150
SQL> /
/

no rows selected

Execution Plan

---

    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2249 Card=199 Bytes=
           995)

    1    0   FILTER
    2    1     SORT (GROUP BY) (Cost=2249 Card=199 Bytes=995)
    3    2       INDEX (FAST FULL SCAN) OF 'ID_OU_NN' (INDEX) (Cost=243
            Card=759349 Bytes=3796745)

==> Statistics and user_group_id not null, Bingo!

Regards,
Holger Received on Wed Sep 07 2005 - 07:10:01 CDT