Home » RDBMS Server » Performance Tuning » TRACE SESSION USING DBMS_APPLICATION_INFO (ORACLE 10G AND JAVA ON WEBLOGIC AND JBOSS)
TRACE SESSION USING DBMS_APPLICATION_INFO [message #466236] Sun, 18 July 2010 07:38 Go to next message
prashant_ora
Messages: 196
Registered: July 2010
Senior Member
Hi ,

We have an java based application with Database Oracle 10g on RAC and 6 application instance. Some time due to performance issue, we have to trace the queries and once we got the query,we can not trace it in appliaction, means this query is the part of which transaction, because different transaction use the same query.Say payroll transaction, in this transaction, in side the application java opens the connection fires some query and close the connection, again it took the connection and fires the query and close the connection, so it would be very tough for us to corelate the query with transactions.So can i use the dbms_application_info package to trace all the query of a single transaction of one user,because many user use the payroll trnsaction and fires the same query and in parallel say finacial posting may use the same query with different parameter , so we need to trace end to end set of queries which are part of a single user and single transaction. Appriciate if any one help me, because we are facing lots of problem issue and some time deadlock problem, but not able to trace the transaction, so that we can go in the particular transaction and see the set of queries.

Thanks
Prashant

Report message to a moderator

Re: TRACE SESSION USING DBMS_APPLICATION_INFO [message #466241 is a reply to message #466236] Sun, 18 July 2010 08:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
What does "trace" mean for you?

Regards
Michel

Report message to a moderator

Re: TRACE SESSION USING DBMS_APPLICATION_INFO [message #466249 is a reply to message #466241] Sun, 18 July 2010 10:19 Go to previous messageGo to next message
prashant_ora
Messages: 196
Registered: July 2010
Senior Member
Trace means If I use v$session then I can get all the sid's which are used for one transaction , so that If I found any problematic query then i can easily identify that for which transaction this query is calling.

Report message to a moderator

Re: TRACE SESSION USING DBMS_APPLICATION_INFO [message #466251 is a reply to message #466236] Sun, 18 July 2010 10:22 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Is application a 3 tier application as described below?

user<=>browser<=>WebServer<=>ApplicationServer<=>DatabaseServer

Does application utilize connection pooling?

>because we are facing lots of problem issue and some time deadlock problem

Deadlock (ORA-00060) are automatically detected, resolved & logged by Oracle. Culprit SQL are contained in resultant trace file.
Deadlocks are caused by poor application implementation.

[Updated on: Sun, 18 July 2010 10:35]

Report message to a moderator

Re: TRACE SESSION USING DBMS_APPLICATION_INFO [message #466252 is a reply to message #466249] Sun, 18 July 2010 10:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
prashant_ora wrote on Sun, 18 July 2010 17:19
Trace means If I use v$session then I can get all the sid's which are used for one transaction , so that If I found any problematic query then i can easily identify that for which transaction this query is calling.

You can use dnms_application_info to set client_info or action field for this but you have to modify the application server code to record the current transaction in this field.

Regards
Michel

Report message to a moderator

Re: TRACE SESSION USING DBMS_APPLICATION_INFO [message #466256 is a reply to message #466252] Sun, 18 July 2010 10:54 Go to previous messageGo to next message
prashant_ora
Messages: 196
Registered: July 2010
Senior Member
See my problem is to identify all the sql which is part of transaction.. for example I have two transaction say payroll and Finacial Posting and in both the transaction we are using two update sql query with some bind variable and those two queries are written in xml file as our application in java. Now one user does the payroll transaction and both the query fires and in the same time another user does the financial transaction and the same query fires some different bind variable values, nowafter join gv$sql and gv$session table i found 4 query with 4 different sid, because in java we open the connection and get the connection pool and fire one update then close the connection and after some buisness logic again we open the connection and fire the next update, so as a result there would be 2 different sid , finally 4 query and 4 differnet sid , so it would be tough for me to identify which two sid is related to which transaction. So to make it possible I want to set the common module name of all the queries which are part of one transaction, i do not want to set the module name for every query , because it would be a very tedious job.

Report message to a moderator

Re: TRACE SESSION USING DBMS_APPLICATION_INFO [message #466257 is a reply to message #466256] Sun, 18 July 2010 11:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
This does not change what I said. It is a job to do in the application server and so you have to modify your application server code to set session fields as you want and if it is "transaction identifier" (anything you want for this") at least at connection time and after each commit or rollback.
Tedious or not this has to be done to achieve what you want.

Regards
Michel

Report message to a moderator

Re: TRACE SESSION USING DBMS_APPLICATION_INFO [message #466259 is a reply to message #466257] Sun, 18 July 2010 11:04 Go to previous messageGo to next message
prashant_ora
Messages: 196
Registered: July 2010
Senior Member
Thanks .But here in one transaction I have 1000 of queries and we have 1000 of transaction, so that would not be practically possible, there is no alternative way or something globally I can do?

Report message to a moderator

Re: TRACE SESSION USING DBMS_APPLICATION_INFO [message #466261 is a reply to message #466259] Sun, 18 July 2010 11:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
But here in one transaction I have 1000 of queries and we have 1000 of transaction, so that would not be practically possible, there is no alternative way or something globally I can do?

If you can imagine something then share with us.
For instance, you are talking about v$sessoion, then see all fields in v$session then how we can set them, then you have the solution, using dbms_application_info and so you know how to implement it.

Now if this is just for debugging you can put ALL sessions in trace mode for a while (hoping you have some quite free space on disks).

Regards
Michel

Report message to a moderator

Re: TRACE SESSION USING DBMS_APPLICATION_INFO [message #466264 is a reply to message #466261] Sun, 18 July 2010 11:52 Go to previous messageGo to next message
prashant_ora
Messages: 196
Registered: July 2010
Senior Member

http://stackoverflow.com/questions/53379/using-dbms-application-info-with-jboss

In this article I am getting something related to trace to sessions realted to connection pool, but i am not understanding whether it will solve my purpose or not?

Report message to a moderator

Re: TRACE SESSION USING DBMS_APPLICATION_INFO [message #466268 is a reply to message #466264] Sun, 18 July 2010 12:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
If you can't, we can't either!
It set the information at session level not at transaction one, maybe it is your case, maybe not, only you can answer.

Regards
Michel

Report message to a moderator

Re: TRACE SESSION USING DBMS_APPLICATION_INFO [message #466269 is a reply to message #466268] Sun, 18 July 2010 12:19 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>In this article I am getting something related to trace to sessions realted to connection pool
When connection pooling exists, a single transaction may span multiple sessions which greatly complicates single transaction tracing & the application must participate in any tracing solution.

Report message to a moderator

Previous Topic: auto execute of job_error_ORA-12012,ORA-04031
Next Topic: Oracle Fragmentation script
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Nov 25 05:43:43 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.