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: Query takes 1min 15 secs and tables are indexed !

Re: Query takes 1min 15 secs and tables are indexed !

From: Brian Peasland <dba_at_nospam.peasland.net>
Date: Mon, 31 Jul 2006 16:24:07 GMT
Message-ID: <J3A08E.1Mx@igsrsparc2.er.usgs.gov>


Big Charles wrote:
> I make a query using three tables on Oracle 10g.
> The query lasts 1 min 15 sec !
>
> SELECT CA.cod_dni AS cod_dni,
> P.apepat AS apepat ,
> P.apemat AS apemat,
> P.nombre AS nombres,
> OP.DES_OP AS DES_OP,
> OP.cod_op AS cod_op,
> CA.FECHA AS FECHA,
> CA.flg_renuncia AS flg_renuncia,
> 'COMITES DE AFILIADOS' AS des_parte
> FROM PADRON P, TBL_COMITES_AFIL CA, TBL_OP OP
> WHERE CA.cod_op = OP.cod_op AND
> CA.cod_dni = P.numdle(+) AND
> CA.flg_renuncia <> 0
>
> I analyze the three tables:
>
> Table TBL_COMITES_AFIL:
> - Total Records: 510,647
> - SELECT COUNT('x') FROM TBL_COMITES_AFIL
> Time: 1 sec
>
> Table TBL_OP:
> - Total Records: 631
> - SELECT COUNT('x') FROM TBL_OP
> Time: immediately
>
> Table PADRON:
> - Total records: 16,494,906
> - SELECT COUNT('x') FROM PADRON
> Time: 45-50 sec
>
> It seems that the problem is table PADRON, because it has about 16
> millions records and a simple COUNT('x') on the table takes 45-50 secs,
> even that the table is indexed: Primary Key on numdle field. If I make
> a query like:
> SELECT * from PADRON where numdle='12345678', the results are
> immediately.
>
> I would like to know what is missing, because I think that Oracle 10g
> can manage tables of 16 millions records without taking almost 1 minute
> for a simple Record Count.
>

Oracle does not perform I/O using the row as the unit of I/O. It performs I/O one block at a time. In TBL_OP, you have 631 rows, which could all fit into a few blocks...fairly quick to read. Table PARDON has 16.5 million rows of data, but in how many blocks? My table with 16.5 mill rows may fit into fewer blocks that your table. It all depends on how long each row is. Additionally, you will want to find out if the rows of data are chained or migrated. You may have an incorrect block size for this table. Try the following:

ANALYZE TABLE pardon LIST CHAINED ROWS;

SELECT chain_cnt FROM user_tables WHERE table_name='PARDON';

If the number returned is sufficiently large, then you have chained or migrated rows which need fixing.

Additionally, is the table PARDON destined for the RECYCLE pool? If so, then it will age out of the recycle pool of the cache quickly. A poorly sized buffer cache can also be contributing to your problem.

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown
Received on Mon Jul 31 2006 - 11:24:07 CDT

Original text of this message

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