Just curious: why are you using PGA_AGGREGATE_TARGET? Are there any limits
on memory capacity that you are in danger of exceeding? You have two CPUs
with 4Gb of RAM; I imagine that you're not in any danger, but it pays to be
sure. I can't think of the HP-UX equivalent to the Solaris "swap" command,
but I imagine that the answer is "glance" or "glanceplus"...
There are hundreds of over-configured servers which have entire SIMMs and
DIMMs that have never been visited, have never had voltage applied to them.
One place I've worked has 8Gb of RAM onboard its database server, but
"swap -s" (i.e. Solaris) has never shown more than 2.5G of virtual memory
used (i.e. allocated plus reserved)! They are running 32-bit Oracle on
Solaris 2.8 (i.e. 64-bit capable), so they will probably never use all that
RAM anyway...
So, why use a feature like PGA_AGGREGATE_TARGET to place upper bounds on
memory utilization? The reason I ask is that this mechanism must play hell
on the cost-based optimizer. Imagine the CBO coming up with an execution
plan based on one memory model (i.e. this much memory to do in-memory sort,
that much memory to do in-memory hash join, etc) and then have subsequent
executions forced into unexpected memory models because automatic PGA memory
management decided to do so. Obviously the impact of automatic PGA memory
management is something that affects the CBO something fierce, and I don't
think anyone really has a handle on the "how" or "why".
My advice is twofold:
* re-evaluate the reasons for using automatic PGA memory management
in the first place
* pitch the automatic PGA memory management feature and then compare
how things behave.
Hope this helps...
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, January 22, 2003 4:13 AM
>
> 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).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Tim Gorman
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).
