RE: v$session_longops hash join operation oddity

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



Other related posts: