Oracle-L: Oracle 9.2.0.2 performance problem

From: Juan Miranda <j.miranda_at_sermatica.es>
Date: Mon, 20 Jan 2003 01:59:16 -0800
Message-ID: <F001.00534503.20030120015916@fatcity.com>

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

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



application/octet-stream attachment: stastpack_report1.sql

Received on Mon Jan 20 2003 - 03:59:16 CST