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