| 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. SOLVED.
Hi
Thanks to all who help me.
We install Oracle 8.1.7 and the query tooks 50 seconds only !!! (30 min. in 9.2)
I will post more...
-----Mensaje original-----
De: root_at_fatcity.com [mailto:root_at_fatcity.com]En nombre de Tim Gorman
Enviado el: miércoles, 22 de enero de 2003 14:39
Para: Multiple recipients of list ORACLE-L
Asunto: Re: Oracle 9.2.0.2 performance problem
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:
Hope this helps...
>
> 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).
> >
> >
> >
> >
> >
> >
> >
>
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> > In such case, you should destroy this message and kindly notify the
> >>>>
> > 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.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> > also send the HELP command for other information (like subscribing).
> >>>>
> >
> > --
> > 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
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: Tim_at_SageLogix.com 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: Juan Miranda 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 Mon Jan 27 2003 - 02:38:38 CST
 |
 |