Re: DML too slow on some tables

From: Scott <nospam_at_nomail.com>
Date: Fri, 30 Mar 2007 10:15:48 -0400
Message-ID: <vQ9Ph.27276$VN1.220655@wagner.videotron.net>

"sybrandb" <sybrandb_at_gmail.com> wrote in message news:1175168993.914726.35240_at_e65g2000hsc.googlegroups.com...

> On Mar 29, 1:42 pm, "Andrea" <netsecur..._at_tiscali.it> wrote:

>> On 29 Mar, 12:51, "gazzag" <gar..._at_jamms.org> wrote:
>>
>>
>>
>>
>>
>> > On 29 Mar, 11:25, "Andrea" <netsecur..._at_tiscali.it> wrote:
>>
>> > > Hi,
>> > > i've a 9i DB for OLTP database type. Some tables is quiet large
>> > > (about
>> > > 3/4 million of rows, size 500/600Mb) and when i launch query or DML
>> > > transactions it is too slow.
>>
>> > > I've already analyzed schema with dbms_stats.schema.. , check
>> > > chained
>> > > row, tuning OS, but is all ok.
>> > > I would like to know if there is a method for improving performance
>> > > of
>> > > big table, some tips or advise :)
>>
>> > > thanks very much.
>> > > bye
>> > > --
>> > > Andrea
>>
>> > To get help, provide the following information:
>>
>> > 1. Oracle and operating system versions.
>> > 2. Details of the structure of the table(s) involved.
>> > 3. Examples of the DML causing you problems.
>> > 4. An explain plan if the above DML.
>>
>> 1. Oracle 9.0.2 SE, Windows 2000 Srv
>>
>> 2.
>> PCT_FREE INITIAL_EXTENT NEXT_EXTENT NUM_ROWS AVG_ROW_LEN
>> -------- -------------- -----------
>> ---------- -----------
>> 10 356678656 77528320
>> 1253021 193
>>
>> 3.
>> SELECT SBI_ID FROM TRANSIT WHERE CARD_NUMBER LIKE '%193';
>>
>> 1102 rows selected.
>>
>> Elapsed: 00:00:12.47
>>
>> 4.
>> Execution Plan
>> ----------------------------------------------------------
>> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3170 Card=46788 Byte
>> s=655032)
>>
>> 1 0 TABLE ACCESS (FULL) OF 'TRANSIT' (Cost=3170 Card=4
>> 6788 Bytes=655032)
>>
>> Statistics
>> ----------------------------------------------------------
>> 29 recursive calls
>> 4 db block gets
>> 20957 consistent gets
>> 10496 physical reads
>> 0 redo size
>> 44994 bytes sent via SQL*Net to client
>> 8528 bytes received via SQL*Net from client
>> 150 SQL*Net roundtrips to/from client
>> 1 sorts (memory)
>> 0 sorts (disk)
>> 1102 rows processed- Hide quoted text -
>>
>> - Show quoted text -

>
> 1 There is no such thing as 9.0.2. There is 9.2.0.x
> 2 I'm not sure why you think you won't get a full table scan for LIKE
> '%<literal>'
> 3 Are you running on
> a) pre-historic hardware
> b) everything on 1 disk
> c) Everything on the C-drive?
> If either of those, get proper hardware, as 12 secs for the shallow
> number of records is outrageous.
>
> Win 2k is desupped. 9i will be soon.
>
> --
> Sybrand Bakker
> Senior Oracle DBA
>

10496 physical reads in 12 seconds. That seems pretty good to me unless my math is wrong that is about 1ms per read. I would say that is pretty good hardware.

There is a function "reverse" that has been around for quite sometime however, I am not sure if this is still one of those undocumented functions. However, I did try this on my 10G instance and it worked as I don't have a 9i instance to try this on.

What I did was

create index f_ix on transit ( reverse( card_number)); analyze index f_ix compute statistics;

Now run your select like this
SELECT SBI_ID FROM TRANSIT WHERE reverse(CARD_NUMBER) LIKE '391%';

Also in 9i you need the following parameters set for your session

    QUERY_REWITE_ENABLED=true
and QUERY_REWRITE_INTEGRITY=TRUSTED

Again I am not sure if this function is supported so you may have to develop your own reverse function to stay on the up and up. ( If in doubt check with support ).

HTH
Scott Received on Fri Mar 30 2007 - 09:15:48 CDT