Home » SQL & PL/SQL » SQL & PL/SQL » how can I get bind values for another session? There are conditions.. (10.2.0.3 Solaris 5.8)
how can I get bind values for another session? There are conditions.. [message #313246] Fri, 11 April 2008 03:59 Go to next message
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 #313248 is a reply to message #313246] Fri, 11 April 2008 04:09 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
I didn't read this thread , but maybe you'll find the information you need in it.
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 Go to previous messageGo to next message
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 #313251 is a reply to message #313250] Fri, 11 April 2008 04:20 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
If you would have read further down, you would stumble on a query involving GV$SQL_BIND_DATA

And your session - or the session you're interested - doesn't need to have the tracing enabled.
Re: how can I get bind values for another session? There are conditions.. [message #313252 is a reply to message #313251] Fri, 11 April 2008 04:28 Go to previous messageGo to next message
salkawari
Messages: 16
Registered: April 2008
Junior Member
Thanks alot for answering my post.

I really appreciate that.

The view GV$SQL_BIND_DATA will only sample data every 15 minutes which is too slow a window. We cannot afford to wait 15 minutes and let this bad session block rows in critical tables. The statistics_level cannot be any higher than typical.

Can you think of anything else?
Re: how can I get bind values for another session? There are conditions.. [message #313253 is a reply to message #313252] Fri, 11 April 2008 04:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The view GV$SQL_BIND_DATA will only sample data every 15 minutes

Where did you see that?

Regards
Michel
Re: how can I get bind values for another session? There are conditions.. [message #313257 is a reply to message #313253] Fri, 11 April 2008 04:45 Go to previous messageGo to next message
salkawari
Messages: 16
Registered: April 2008
Junior Member
I know that from researching on the internet and through first hand testing of this dynamic view. I am guessing the solution is based around querying the pga or uga. The tricky thing is knowing which x$ tables or which oradebug commands to use. The problem sounds easy to solve, but turns out to be quite tricky.

Hopefully an answer will be posted soon.
Re: how can I get bind values for another session? There are conditions.. [message #313269 is a reply to message #313257] Fri, 11 April 2008 05:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post the link where you found it.

Regards
Michel
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 Go to previous messageGo to next message
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 #313298 is a reply to message #313292] Fri, 11 April 2008 06:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I rather think you may have misunderstood what gv$_sql_bind_data does.

As I understand it, it will show you the bind variables in use by each cursor owned by the session querying the view

Link

I don't know of a way to get at another sessions bind variables without tracing it.
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 Go to previous messageGo to next message
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 #313305 is a reply to message #313298] Fri, 11 April 2008 06:19 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Can you change the stored procedure in question?

You could create a table to store the bind variables, and then write the bind variables + the session id into a new table with an autonomous transaction that you commit inside the procedure.

(and then delete them again at the end of the procedure without using an autonomous transaction, which would then be committed by the commit from Java.)
Re: how can I get bind values for another session? There are conditions.. [message #313307 is a reply to message #313303] Fri, 11 April 2008 06:21 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If I were you, I'd post this up to the Pl/Sql Experts forum.

In fact, I'll move it up now.
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
ThomasG
Messages: 3189
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? Wink

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 Go to previous messageGo to next message
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
Re: how can I get bind values for another session? There are conditions.. [message #313566 is a reply to message #313246] Sun, 13 April 2008 14:52 Go to previous message
salkawari
Messages: 16
Registered: April 2008
Junior Member

I would just like to thank EVERYONE for your help and support on this topic!

Its nice to know that there are so many helpful senior DBAs out there, to help young-sprouts!

The answers were well thought out and helpful.

gracias
danke vel
muito obrigado
vielen dank

Smile Smile Smile Smile Smile Smile
Previous Topic: Accessing the plan table
Next Topic: Need INSERT Query
Goto Forum:
  


Current Time: Thu Dec 08 20:08:06 CST 2016

Total time taken to generate the page: 0.16544 seconds