Re: Oracle 9.2.0.2 performance problem

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" <ORACLE-L_at_fatcity.com>
>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 <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).
> >
> >
> >
> >
> >
> >
> >
><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
> >>>>
> > 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: mrichard_at_transurban.com.au
> >
> > 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).

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).