Home » RDBMS Server » Performance Tuning » Pinning question
Pinning question [message #125452] Sat, 25 June 2005 16:19 Go to next message
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 #125590 is a reply to message #125452] Mon, 27 June 2005 18:50 Go to previous messageGo to next message
vadlamani
Messages: 39
Registered: November 2003
Member
I would keep that function in shared pool
Thanks
Satish
Re: Pinning question [message #125651 is a reply to message #125452] Tue, 28 June 2005 08:17 Go to previous messageGo to next message
james_briar
Messages: 39
Registered: April 2005
Location: Morden (South London)
Member
Hello Vadlamani, thanks for your reply. I've pinned the function as well.
Re: Pinning question [message #125899 is a reply to message #125651] Wed, 29 June 2005 16:24 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Are you really sure that you want to pin the code - it's a bit of a dated approach...
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:7037830724934
Re: Pinning question [message #126592 is a reply to message #125899] Tue, 05 July 2005 16:09 Go to previous messageGo to next message
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 #126593 is a reply to message #125899] Tue, 05 July 2005 16:10 Go to previous messageGo to next message
james_briar
Messages: 39
Registered: April 2005
Location: Morden (South London)
Member
Sorry, i meant to say Hello Andrew not Tom.
Re: Pinning question [message #126594 is a reply to message #126592] Tue, 05 July 2005 16:14 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #126964 is a reply to message #125452] Thu, 07 July 2005 10:01 Go to previous messageGo to next message
james_briar
Messages: 39
Registered: April 2005
Location: Morden (South London)
Member

I meant to say - The first call is now only slightly slower than the second
Re: Pinning question [message #127214 is a reply to message #125452] Sun, 10 July 2005 01:09 Go to previous messageGo to next message
Frank
Messages: 7897
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 Go to previous message
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.
Previous Topic: Extracting CPU data from statspack tables
Next Topic: reversed indexes
Goto Forum:
  


Current Time: Sun Jan 29 03:24:17 CST 2023