Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: DML too slow on some tables

Re: DML too slow on some tables

From: sybrandb <sybrandb_at_gmail.com>
Date: 29 Mar 2007 04:49:53 -0700
Message-ID: <1175168993.914726.35240@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
Received on Thu Mar 29 2007 - 06:49:53 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US