Re: v$session_longops hash join operation oddity

Hi Alvaro

The plan looks OK -- it's what we'd expect it to be ( I can't show it unless I edit all the table names out first, and it's 140 lines long)

Stefan

On 9/13/07, Alvaro Jose Fernandez <alvaro.fernandez_at_sivsa.com> wrote:
>
> May be a bug, or bad constructed query which fires a bug.
>
>
>
> Or simply a bad written query – or very bad filters. Check the current
> plan for this hash value, now , if you can .
>
>
>
> alvaro
>
>
>
>
>
>
>
> 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