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@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@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 Tue Jan 21 2003 - 07:49:08 CST
