Presuming that PGA_AGGREGATE_TARGET of 500MB is sufficient for your environment .... ... it does look as if your PA-RISC processors are slower than the Intel ones but ... I still wonder.. your statistics show that the number of block-gets for the FTS isn't very high and cpu time is high.
If you can, try taking it out of automatic PGA management and set an explicit SORT_AREA_SIZE [run the query with different SORT_AREA_SIZES]. Also watch the NEXT_EXTENT size of your Temporary Tablespace. Hemant At 03:13 AM 22-01-03 -0800, you wrote:
Hello 650 Mhz processors are HP pa RISC processors. We use automatic PGA management. There are 500MB PGA. sort_area_size have the dafault value because under automatic PGA there is not used (or not?). ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, January 21, 2003 10:59 PM > Hemant raises a valid point... > > You could just do a "select blah from dw.supuestos" in SQL*Plus (or some > other client that retrieves all results without pausing) to get an idea of > how much effort the group by is taking. > > My other question relates to the CPU's. If I'm reading correctly the old > server had 2 * 1.4GHz CPU's and the new server has 2 * 650MHz CPU's. I > realise the CPU's most likely aren't the same architecture (sorry - I know > nothing about HP's range of machines) but you aren't comparing apples to > apples are you? > > It sounds like you have changed Oracle version, CPU architecture, IO > subsystem and OS all at the same time. There is a multitude of > possibilities. Have you compared the init.ora files (Hemant's > SORT_AREA_SIZE recommendation is a good one). > > > > > Hemant K > Chitale To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > <hkchital@singn cc: > et.com.sg> Subject: Re: Oracle 9.2.0.2 performance problem > Sent by: > [EMAIL PROTECTED] > m > > > 22/01/2003 > 00:49 > Please respond > to ORACLE-L > > > > > > > > Wonder if it is the SORT (for the GroupBy) taking time ? > What is the SORT_AREA_SIZE and what are the INITIAL and NEXT > extents of the user's temporary tablespace ? > Are the tablespaces Locally-Managed and the temporary tablespace > a TEMPORARY TABLESPACE with a TEMPFILE ? > > Hemant > > At 01:59 AM 20-01-03 -0800, 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 > > > > > > Hemant K Chitale > My web site page is : http://hkchital.tripod.com > > > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: > Hemant K Chitale 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). > > > > > > > <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> >>>> > Privileged/Confidential information may be contained in this message. > If you are not the addressee indicated in this message > (or responsible for delivery of the message to such person), > you may not copy or deliver this message to anyone. > In such case, you should destroy this message and kindly notify the sender > by reply e-mail or by telephone on (61 3) 9612-6999. > Please advise immediately if you or your employer does not consent to > Internet e-mail for messages of this kind. > Opinions, conclusions and other information in this message > that do not relate to the official business of > Transurban City Link Ltd > shall be understood as neither given nor endorsed by it. > <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> >>>> > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Mark Richard > 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: jmiranda 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).
Hemant K Chitale My web site page is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale 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).
