| Pinning question [message #125452] |
Sat, 25 June 2005 16:19  |
james_briar
Messages: 39
Registered: April 2005
Location: Morden (South London)
|
Member |
|
|
Hello, just a quickie question. I've pinned a procedure ok by doing "exec dbms_shared_pool.keep('auditing.lbm_popup');" but the procedure calls a function, do i need to pin the function as well? The function always runs when the procedure is run.
Thanks.
|
|
|
|
|
|
|
|
|
|
| Re: Pinning question [message #126592 is a reply to message #125899] |
Tue, 05 July 2005 16:09  |
james_briar
Messages: 39
Registered: April 2005
Location: Morden (South London)
|
Member |
|
|
Hello Tom, just been reading your note on Pinning.
I've been playing around with pinning but it doesn't seem to fix my problem anyway (i'm using the large_pool). When a certain procedure is run for the first time it takes a long while to run but on subsequent runs its very fast (say on the 2nd run or the third run and so on). If i leave things for say a few hours and run the procedure it again takes a long while but soon as you issue the procedure again it runs very quick? The procedure is smallish as well (it calls a function). It would be nice if i could fix it but we can live with it ok.
|
|
|
|
|
|
| Re: Pinning question [message #126594 is a reply to message #126592] |
Tue, 05 July 2005 16:14 |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
|
It's most likely that the SQL and direct I/Os are the culprit. When you run it the first time, it's likely that the data is getting cached in the buffer cache (like it should). Because your buffer cache is almost always smaller than all the data you access on a daily basis, some of it gets aged out to buffer some other user's data. Run a sql trace (tkprof) on for the first execution when the data isn't cached and then opne a second session and run a second trace file. Compare the direct and logical IOs the the traces.
|
|
|
|
| Re: Pinning question [message #126738 is a reply to message #125452] |
Wed, 06 July 2005 07:41 |
james_briar
Messages: 39
Registered: April 2005
Location: Morden (South London)
|
Member |
|
|
Hello Andrew, i'll do what you said and run a tkprof, i'll let you know the outcome when i get around to doing it.
Thanks again.
|
|
|
|
| Re: Pinning question [message #126740 is a reply to message #126594] |
Wed, 06 July 2005 07:50 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
| andrew again wrote on Tue, 05 July 2005 16:14 |
It's most likely that the SQL...
|
That is a good answer...
|
|
|
|
| Re: Pinning question [message #126963 is a reply to message #125452] |
Thu, 07 July 2005 09:59 |
james_briar
Messages: 39
Registered: April 2005
Location: Morden (South London)
|
Member |
|
|
Hello Andrew, My problem is now fixed. The procedure was taking 15 seconds to run on its first run and on its subsequent runs it ran in an instant (about 1/5 of a second). I fixed the problem by increasing the value of DB_BLOCK_BUFFERS and LARGE_POOL_SIZE in the parameter file and amending the SQL that calls my procedure to use Bind variables :-
v_plsql := 'BEGIN auditing.lbm_popup(:v_osuser, :v_message);END;';
EXECUTE IMMEDIATE v_plsql USING v_osuser, v_message;
The first call is now only slightly slower than the first (the procedure is used to send a popup message to a user).
Thanks again.
|
|
|
|
|
|
| Re: Pinning question [message #127214 is a reply to message #125452] |
Sun, 10 July 2005 01:09 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
Just curious. Why do you use dynamic sql?
Why don't you issue the command
auditing.lbm_popup(v_osuser, v_message);
directly ?
hth
|
|
|
|
| Re: Pinning question [message #128039 is a reply to message #125452] |
Thu, 14 July 2005 15:31 |
james_briar
Messages: 39
Registered: April 2005
Location: Morden (South London)
|
Member |
|
|
Hello Frank, just read your reply, i did initially use auditing.lbm_popup(v_osuser, v_message); directly but i thought using the EXECUTE IMMEDIATE statement etc would be a touch quicker. I'll have another play around. I read somewhere that i could run a procedure at instance startup and then pin it. I'll have a fiddle with that to. The second calls to lbm_popup are very fast, it takes only around 1 to 2 seconds after issuing the lbm_popup procedure before a user receives a popup message on their PC. The first call is only a bit slower but is adequate.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
