Re: DML too slow on some tables

On 29 Mar, 14:02, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> On Mar 29, 6:42 am, "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 -
>
> You need a lesson in how the optimizer operates; *ANY* conditional
> starting with a wildcard will *not* use an index. This is precisely
> what you have, a condition where you're looking for specific data at
> the end of a string. Oracle cannot use an index to speed access with
> such a condition. One possibility would be to store that column data
> in a new column in reverse order, index that column and change your
> condition:
>
> SELECT SBI_ID FROM TRANSIT WHERE REV_CARD_NUMBER LIKE '391%';
>

thanks.

which means Cost=3170 in Execution Plan? there is some link for explain CBO?

thanks Received on Thu Mar 29 2007 - 08:05:39 CDT