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@xxxxxxxxx> 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@xxxxxxxxxxxx http://www.trivadis.com OCP SCSA SCNA =========================