How to "alter session" when I can't actually "alter session"?

I've got a continuing problem, and could use some ideas on how to solve it. I don't think it's all that uncommon.

I have this packaged application. (It's Peoplesoft, if it matters.) I have set my default optimizer mode to "choose", because we can see an overall performance advantage to doing so. (We're running Oracle 7.3.4 on AIX, if you care.) However, a few of my processes run much slower under cost-based optimization (CBO), than under rules-bases (RBO). For example, we have one process that runs maybe 2 hours under RBO, but 24 hours under CBO. There isn't a single SQL statement that eats up most of that time; there are several. Rather than tune the SQL, I'd like to simply run this process under RBO. The catch is: I can't get my hands far enough into the application to issue an "alter session ..." at the start of the job to set RBO.

My question is: How would you solve this problem?

One idea I've come up with is: I CAN fiddle with the SQL enough to, say, insert a row into a dummy table. Could I create a trigger on that table which would fire when the insert is done, and do an "alter session ..." statement from within the trigger? It sounds so simple, that there is probably a good reason why it can't be done. I welcome intelligent opinions on this solution, or another solution.

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Wed Sep 02 1998 - 14:45:29 CDT