RE: v$session_longops hash join operation oddity
- From: "Alvaro Jose Fernandez" <alvaro.fernandez@xxxxxxxxx>
- To: <knecht.stefan@xxxxxxxxx>, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 13 Sep 2007 13:56:26 +0200
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:
