HowTo Pin Stored Procedures in Oracle 10g?

[TheExcession]

Not even sure this is relevent or doable... can you tell I don't know what I'm doing yet? ;)

But in - in previous versions of Oracle we used to Pin our stored procedures with something like:

execute SYS.DBMS_SHARED_POOL.KEEP('HHMEFEP.BGAN_createInitialCallRecord');

Is there anything similar in 10g?

tried google but not gettingvery far.

many thanks
Ex

[BliarOut]

You're not the first person I've seen posting that question today...... I still don't know the answer though

[TheExcession]

TBH Honest - I'm just being a bit greedy -

We had some software delivered by our Indian Bretheran many years ago. All it does is answer RADIUS messages - bit like Steel Belted RADIUS Server, however it's been developed to service Satellite calls so there are a lot of non standard RADIUS AVPs in the messages.

I've just upgraded all the code to support 3GPP, as this is the standard that Inmarsat have chosen for their latest and greatest, known as BGAN - Broadband Global Area Network.

Now, at best I would class this Indian code as a pile of smelly poo - we were seeing response times of arround 1200 ms

So, I just spent two weeks doing maintenance to try and get the response times down - at the moment (after my efforts and the move to an Oracle systen that doesn't use Replication) we are running at arround 30-60 ms.

I was just wondering if I could get it any lower by pinning Tables and Stored Procs in Oracle.

cheers
Ex

[rebelstar]

Well, I'm no oracle expert, but a google search came up with a page which seems to indicate that it should still work:

www.stanford.edu/dept/itss/docs/oracle/10g/appdev.101/b10802/d_shpool.htm#999223

[3200ac]

have you achieved the required performance? what other tuning have you performed, eg explain plan, tkprof, plsql profiler? 10g has an advisor that will get stats data and make recommendations (see Oracle magazine for sept/oct for articles, oracle.com/oraclemagazine)