I know for sure that such rogue SQL(s) are issued by Business Objects user. In the database all we see is a generic Id (say, BUSOBJ_USER). This is similar to Oracle Apps, where we do not know the real User who connected thru the BusObj app. So, unless we know who executed the SQL, there's no way for us to tune the SQL or train the user. But identifying the BO user is a different topic altogether. We have already looked
One CAN identify an Oracle Apps user if the right profile options are set. But I do agree that identifying a BO user is an issue. The nearest you can get to it what my BO Admin calls the "Governor log" which when configured, lists all the users who executed some particular report.
As for this problem, you need to observe what type of segment it is from V$SORT_USAGE.SEGTYPE. If you see a HASH join that consume TEMP quickly, you might to investigate if a BO "relation" as missed or incorrectly defined. BO uses point-click and sometimes this occurs when the "point" is not accompanied by a "click" :)
-- John Kanagaraj <>< DB Soft Inc Phone: 408-970-7002 (W)
Disappointment is always inevitable; Discouragement is invariably optional
** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -- http://www.freelists.org/webpage/oracle-l