how can I get bind values for another session? There are conditions.. [message #313246] |
Fri, 11 April 2008 03:59  |
salkawari
Messages: 16 Registered: April 2008
|
Junior Member |
|
|
Hello,
I have a question about oracle10g. How can I get the bind variables?
Here is a description of the situation:
1. A java process starts a stored procedure via jdbc.
2. The procedure has a number of bind variables as input.
3. The java process does NOT commit but just sleeps.
4. I can't change the statistics_level (it must be typical).
5. I cannot predict when this happens and I cannot trace all sessions from that user due to performance requirements.
Can I use oradebug somehow?
Thanks for your help!
|
|
|
|
Re: how can I get bind values for another session? There are conditions.. [message #313250 is a reply to message #313248] |
Fri, 11 April 2008 04:15   |
salkawari
Messages: 16 Registered: April 2008
|
Junior Member |
|
|
Thanks for taking the time to reply.
Unfortunately that technique involves tracing all sessions.
I cant afford to do that as its a production system.
I cannot predict when it happens either.
When it happens someone will call me, so I need some way of getting the data from existing sessions, in a live manner.
I do have a development env so I can try anything out there.
Thanks for the link - unluckily it wont solve the problem.
|
|
|
|
|
|
|
|
Re: how can I get bind values for another session? There are conditions.. [message #313292 is a reply to message #313269] |
Fri, 11 April 2008 05:57   |
salkawari
Messages: 16 Registered: April 2008
|
Junior Member |
|
|
Hiya Michel,
I dont have the link to hand. But here is an example of how to re-produce the problem. Please note I am calling the procedure from sqlplus in this example. This is done to keep things simple.
In the production problem, we call this via a jdbc java call to the procedure (so it appears as "begin test_proc(?,?); end;" in v$sql).
Okay here is the syntax of the simplified example:
----------------------------------------------
----------------------------------------------
--(please open an sqlplus window and log in as some test user)
drop table test1;
create table test1(Order_Number number,ITEM_ORDER number ,ENTRYDATE date);
ALTER TABLE test1 ADD ( CONSTRAINT test1_PK PRIMARY KEY (Order_Number,item_order) );
create or replace procedure test_proc(pOrder_Number IN number, pItem_Order IN number) as
begin
insert into test1(order_Number, Item_Order, EntryDate)
values(pOrder_Number, pItem_Order,sysdate);
end;
/
var OrdNum number
var ItemOrd number
exec :OrdNum := 1;
exec :ItemOrd := 2;
exec test_proc(:OrdNum,:ItemOrd);
----------------------------------------------
-- please open another session.
select count(*) from GV$SQL_BIND_DATA where value is not null;
-- no rows are returned.
----------------------------------------------
----------------------------------------------
Hopefully you can tell me that I am using this view wrongly and that another select is required.
Thanks in advance for your help!
|
|
|
|
Re: how can I get bind values for another session? There are conditions.. [message #313303 is a reply to message #313298] |
Fri, 11 April 2008 06:18   |
salkawari
Messages: 16 Registered: April 2008
|
Junior Member |
|
|
Hiya,
That is the problem, I can't trace it as it happens sporadically and I cant trace everything as its production. Tracing after the call has been made wont show me the bind values unfortunately.
Since these connections are made by the application, I can't call that select from that session (or is there a way to somehow connect as that different session? Probably not for security reasons, right?).
A friend of mine things it should be possible to get the information about the bind variables by directly looking at the memory (via oradebug with dumpvar / peek). The problem is, I dont know how to go about using this tool.
I am new to this forum, do you think if nobody answers the question within a week, would it make sense to post it in another section of the forum?
Or do all senior members look at this newbie section?
|
|
|
|
|
Re: how can I get bind values for another session? There are conditions.. [message #313311 is a reply to message #313305] |
Fri, 11 April 2008 06:32   |
salkawari
Messages: 16 Registered: April 2008
|
Junior Member |
|
|
Hiya Thomas,
That is a good idea.
The only thing is the procedure is called hundreds of times every minute, it is also called by a number of differnt java processes and other plsql batch jobs, so any increase in the calling time would have a negative effect on performance. There are certain batch jobs which run for a long time currently so any further delay would be frained upon.
Does anyone know how to use oradebug to get this information? Or is there a way of solving this without changing the plsql code?
Thanks to everyone for helping with this.
@JRowbottom: sorry, I havent understood, are you posting it for me in there, or recommending me to do this?
@Everyone: Your input is very valued.
|
|
|
Re: how can I get bind values for another session? There are conditions.. [message #313317 is a reply to message #313311] |
Fri, 11 April 2008 06:41   |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
Have you tried figuring out the source of the problem?
When the Java process starts that procedure and then goes to "sleep" is the procedure finished at that point or is it still running? If it is called hundreds of time per minute it can't be something that is usually long running I guess.
Is there anything else the Java process has to do before committing after the procedure is finished? If no, is the commit in the Java code directly after the procedure call or is something between the two that could cause the Java process to hang?
|
|
|
Re: how can I get bind values for another session? There are conditions.. [message #313321 is a reply to message #313317] |
Fri, 11 April 2008 06:48   |
salkawari
Messages: 16 Registered: April 2008
|
Junior Member |
|
|
Yep, the java caller is the one who doesnt do the commit.
The java caller does a long list of things over a number of different database sources and commits it all only at the end. The problem also causes blocks as 2 different java callers use the procedure for different purposes but sometimes with the same input.
All the our team is filled with java developers, I am the only oracle person. They have told me that without the order number they cant find the cause of the problem. They say that this is my job as the oracle dba.
At the moment, whenever it occurs (which is once every couple of months) we all have approx 5 - 10 minutes to run lots of queries against the database before it gets killed (so that the production system can keep on running).
It is a difficult problem.
|
|
|
Re: how can I get bind values for another session? There are conditions.. [message #313335 is a reply to message #313321] |
Fri, 11 April 2008 07:11   |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
It smells a little like a design problem where both processes run into trying to change the same row at some point.
Do you use the application info in v$session for anything yet?
Maybe you could use DBMS_APPLICATION_INFO to set the action and store the order number that is processed at the moment at the start of the procedure if it isn't used for anything else yet. Then you could see what the session is doing in the v$session view.
|
|
|
Re: how can I get bind values for another session? There are conditions.. [message #313340 is a reply to message #313335] |
Fri, 11 April 2008 07:24   |
salkawari
Messages: 16 Registered: April 2008
|
Junior Member |
|
|
Yep I agree with you, it definitely is a design problem.
They say that cant solve it unless the know which order.
I like your idea about using dbms_application_info for instrumentation.
1. Would there be any performance loss using this?
2. If I save the sid and order number in the action via a trigger, would this lead to an explosion of entries in v$sql? I guess this would happen, so that for every call of this procedure, a different entry would be made (due to different orders) thus causing lots of entries in v$sql.
Do you agree?
|
|
|
Re: how can I get bind values for another session? There are conditions.. [message #313341 is a reply to message #313340] |
Fri, 11 April 2008 07:31   |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
1) Maybe a little. But since the information is only in memory probably not noticeable.
2) Huh? 
I don't think a trigger is needed. I basically though about doing a
DBMS_APPLICATION_INFO.SET_ACTION(
action_name => 'Last Order :' || v_order_id_or_whatever );
at the start of the procedure, which then shows up in the ACTION column in v$session. Since that uses a bind parameter there should only be one entry in v$sql, but I'm not 100% sure about that.
PS:
I did a quick performance check. Setting the ACTION one million times with
DECLARE
v_i NUMBER;
BEGIN
FOR v_i IN 1 .. 1000000 LOOP
DBMS_APPLICATION_INFO.SET_ACTION(
action_name => 'Last Order : 12dgd');
END LOOP;
END;
took 1.12 seconds on my system.
[Updated on: Fri, 11 April 2008 07:41] Report message to a moderator
|
|
|
Re: how can I get bind values for another session? There are conditions.. [message #313344 is a reply to message #313340] |
Fri, 11 April 2008 07:50   |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
We use dbms_application package quite a bit in our application. It is a very useful piece of package for tracing when used correctly to know what's happening within your application (i.e) which step is getting executed. But you have to remember to change the action value with the correct information before executing the next set of sql statement. We didn't had any issues in terms of performances. Code snippet will be something like this.
Package body test
is
Proc A
is
Begin
dbms_application_info.set_module('A','Start');
...
dbms_application_info.set_action('Truncating');
...
dbms_application_info.set_action('Inserting');
....
dbms_application_info.set_action('Transforming');
....
dbms_application_info.set_module(NULL,NULL);
end;
....
End Test
Regards
Raj
|
|
|
|