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