v$session_longops hash join operation oddity

From: Stefan Knecht <knecht.stefan_at_gmail.com>
Date: Thu, 13 Sep 2007 13:48:21 +0200
Message-ID: <486b2b610709130448i15151441ub42fc6739826b9cf@mail.gmail.com>

Hi all

This is oracle 10.1.0.4 on Sun8.

We're running a pretty intensive data mart query, that is at the moment doing a hash join.

v$session_longops shows the following:

SID SERIAL# OPNAME TARGET TARGET_DESC SOFAR TOTALWORK UNITS START_TIME LAST_UPDATE_TIME TIME_REMAINING 255.00 1'988.00 Table Scan MIS_DM.MD**_RISK NULL 80'162.00 80'162.00 Blocks 13.09.2007 13.09.2007 0.00

255.00    1'988.00    Hash Join    NULL    NULL    22'064.00    22'064.00
Blocks    13.09.2007    13.09.2007    0.00
255.00    1'988.00    Table Scan    MIS_DM.MF**_LIMIT    NULL    15'883.00

15'883.00 Blocks 13.09.2007 13.09.2007 0.00

<<... rest of partitions omitted for clarity -- all in all they sum up to about 1mio blocks ...>>

255.00 1'988.00 Table Scan MIS_DM.MF**_LIMIT NULL 29'183.00 29'183.00 Blocks 13.09.2007 13.09.2007 0.00 255.00 1'988.00 Hash Join NULL NULL 12'603'570.00 2'294'422'320.00 Blocks 13.09.2007 13.09.2007 1'444'380.00

There is no way we can get up to 2 billion blocks. Does anyone know what exactly this is supposed to mean ? Are we hitting a bug ?

Stefan

-- 


=========================


Stefan P Knecht
Consultant
Infrastructure Managed Services

Trivadis AG
Europa-Strasse 5
CH-8152 Glattbrugg

Phone +41-44-808 70 20
Fax +41-808 70 12
Mobile +41-79-571 36 27
stefan.knecht_at_trivadis.com
http://www.trivadis.com

OCP SCSA SCNA


=========================


--
http://www.freelists.org/webpage/oracle-l

Received on Thu Sep 13 2007 - 06:48:21 CDT