Re: DML too slow on some tables

On Mar 29, 3:05 pm, "Andrea" <netsecur..._at_tiscali.it> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

Amongst others
http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96533/optimops.htm

Don't tell you still don't know how to search tahiti.

Other than that buy the book of Jonathan Lewis on CBO

--
Sybrand Bakker
Senior Oracle DBA