RAID is slower then normal disks. You're doing a full table scan. Is it file system, raw devices? Get the file response times from v$filestat and see what are the disk response times. Turn on the event 10046 and run tkprof with WAITS=YES and that will give you the events that your application is waiting on. Better yet, contact Cary Milsap from Hotsos and have him analyze your trace file. That will give you everything I mentioned above, and with additional clarifications. The address is http://www.hotsos.com
> -----Original Message----- > From: Juan Miranda [mailto:[EMAIL PROTECTED]] > Sent: Monday, January 20, 2003 7:35 AM > To: Multiple recipients of list ORACLE-L > Subject: RE: Oracle 9.2.0.2 performance problem > > > > Hello > > We execute the query in the servers, so there is no NET > problem (I think). > The data volume is exact (imported). > Execution path is the same, full-scan. > > This is a very strange problem and is very important for us > to solve it. > Thank�s > > This is the plan of the windows db: > Execution Plan > ---------------------------------------------------------- > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5952 Card=465110 Byt > es=32557700) > > 1 0 SORT (GROUP BY) (Cost=5952 Card=465110 Bytes=32557700) > 2 1 TABLE ACCESS (FULL) OF 'SUPUESTOS' (Cost=643 Card=465110 > Bytes=32557700) > Statistics > ---------------------------------------------------------- > 0 recursive calls > 4 db block gets > 6679 consistent gets > 12866 physical reads > 0 redo size > 26428556 bytes sent via SQL*Net to client > 3894740 bytes received via SQL*Net from client > 59454 SQL*Net roundtrips to/from client > 0 sorts (memory) > 1 sorts (disk) > 445919 rows processed > > > -----Mensaje original----- > De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de chao_ping > Enviado el: lunes, 20 de enero de 2003 12:19 > Para: Multiple recipients of list ORACLE-L > Asunto: Re: Oracle 9.2.0.2 performance problem > > > Juan Miranda, > It seems quite strange,there is little wait > event in the statspack report, > and you execution path should be the same on both platform, > right? And is > the data volumn the same in both platform?And does the time > spent on fetch > the result from server to your client different?Is the speed > of your pc to > linux and hp the same? > > > > > > > Regards > zhu chao > msn:[EMAIL PROTECTED] > www.happyit.net > www.cnoug.org(China Oracle User Group) > > ======= 2003-01-20 01:59:00 ,you wrote��======= > > >Hello > > > >We have an serious performance problem on a DSS db. > >We buy a new HP rp5405 (2x650Mhz, 4GB, ...) with HP UX 11.11 > > > >Oracle 9.2.0.2 tooks 30 min doing this query where an Intel > 2x1,4 Ghz > tooks > >9 min only. > > > >We have in the HP losts of buffers(1,5GB), sga(200MB), > pga(500MB), fast i/O > >(EMC Clariom CX600)... > >We try lost of parameters, but time is always the same. > > > >Is there some bug in this release - platform ????? > >How can I get more data about this problem?? > > > >Thanks. > > > >SELECT grupo,evpamb,evpdel,evpfor,evppr2,evppr3,evppr4, > > evpanc,evpgru,evpcli,evppai,evppro,evpume, > > to_date(evpano||'-'||evpmes||'-'||'01','YYYY-MM-DD') FECHA, > > sum(evppca) PPTO > >FROM DW.SUPUESTOS > >GROUP BY grupo,evpamb,evpdel,evpfor,evppr2,evppr3,evppr4, > > evpanc,evpgru,evpcli,evppai,evppro,evpume, > > evpano, evpmes > > > >call count cpu elapsed disk query current > >rows > >------- ------ -------- ---------- ---------- ---------- > ---------- ----- > - > >---- > >Parse 1 0.01 0.00 0 0 0 > >0 > >Execute 1 0.00 0.00 0 0 0 > >0 > >Fetch 445920 1748.65 1708.72 1554 1675 23 > >445919 > >------- ------ -------- ---------- ---------- ---------- > ---------- ----- > - > >---- > >total 445922 1748.66 1708.72 1554 1675 23 > >445919 > > > >Misses in library cache during parse: 1 > >Optimizer goal: CHOOSE > >Parsing user id: 90 (recursive depth: 1) > > > > > >Execution Plan > >---------------------------------------------------------- > > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4481 > Card=464215 Byt > > es=32495050) > > > > 1 0 SORT (GROUP BY) (Cost=4481 Card=464215 Bytes=32495050) > > 2 1 TABLE ACCESS (FULL) OF 'SUPUESTOS' (Cost=162 > Card=464215 > > Bytes=32495050) > > > >Statistics > >---------------------------------------------------------- > > 0 recursive calls > > 31 db block gets > > 1675 consistent gets > > 1577 physical reads > > 0 redo size > > 9012743 bytes sent via SQL*Net to client > > 208363 bytes received via SQL*Net from client > > 29729 SQL*Net roundtrips to/from client > > 0 sorts (memory) > > 1 sorts (disk) > > 445919 rows processed > > = = = = = = = = = = = = = = = = = = = = > > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: chao_ping > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Juan Miranda > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
