Re: DML too slow on some tables

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