| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Oracle 9.2.0.2 performance problem
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?).
> 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 <ORACLE-L_at_fatcity.com>
> <hkchital_at_singn cc:
> et.com.sg> Subject: Re: Oracle 9.2.0.2
performance problem
> Sent by:
> root_at_fatcity.co
> 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: hkchital_at_singnet.com.sg 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:
> ListGuru_at_fatcity.com (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: j.miranda_at_sermatica.es 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: ListGuru_at_fatcity.com (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).Received on Wed Jan 22 2003 - 05:13:42 CST
 |
 |