Skip to Main Content
  • Questions

  • Tracing for multiple sessions for single user

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Karen.

Asked: October 27, 2002 - 5:43 pm UTC

Last updated: July 04, 2005 - 10:40 am UTC

Version: 8.1.6

Viewed 1000+ times

You Asked

I was asked to review performance issues with a Microsoft Visual C++/Delphi 7 application written to access an Oracle 8.1.6 database running on AIX (sorry don't know the version). I've got two related issues.

Once the user is logged in, OCX and various controls on the screens make database connections to retrieve/process data. In other words, one process ends up with multiple sessions. So, here's the problem... since there are multiple sessions that are created by the various controls, if I want to trace the activity of a single user I'd have to trace multiple sessions. But when I start a trace, all of the sessions aren't there yet (they open as various controls make database connections/requests). Each session has the same process but the number of sessions related to a single process can range from 1-n (usually 5-6 max). I was wondering if there's a way to trace all activity against a process instead of tracing by session? I can't recall seeing anything like that, but it's quite maddening to try and trace sessions that come and go!

The second related issue is that one of the major performance killers is the lack of use of bind variables. I've never used their development tools and so I'm unable to give them concrete examples of how to use bind variables in their development lingo. They also could benefit by writing procedures to return resultsets in many cases. Can you point me to a link or two where I can find some related code examples to show them how to 1) use bind variables and 2) call procedures which return resultsets in Microsoft Visual C++ syntax.

I've been able to find "snippets" but not enough good full examples to really help. I've done alot of looking and hoped you might just have some resources close at hand so I could curtail this tedious search!

Many thanks in advance!

and Tom said...

What a terrible application design -- Oracle is not sqlserver, we do not have their inherit limitations. My first goal would be to try and figure out how to fix the application so as to NOT open a session per little button or widget on a screen. Talk about wasting resources!

There is no concept of a "process" here. We have a client opening umpteen sessions and they are (as far as we can tell) 100% unrelated to eachother.

You could use a logon trigger perhaps, something like:

create trigger sql_trace after logon on database
begin
if ( user = 'USER YOU WANT TO TRACE' )
then
execute immediate 'alter session set sql_trace=true';
end if;
end;
/

make sure the OWNER of that trigger has "ALTER SESSION" granted directly to them (not via a role). I've used this "trick" many times in the past.

See
</code> http://asktom.oracle.com/~tkyte/ResultSets/index.html <code>
for all of the examples I have with ref cursors (using binds)...

MS Visual C++ is a language -- I assume they are using ODBC -- if so, they could jsut read the documentation, ODBC fully supports the concept of binding.


(they have a HUGE performance killer in the form of all of those sessions -- thats a terrible, horrible -- but very "ms'ish" for whatever reason -- approach)

Rating

  (10 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

All users log in with same ID!!

Karen Morton, October 27, 2002 - 7:04 pm UTC

Ooops....forgot to mention that all users of the app log in using the same Oracle ID....I know...another "bad" idea, huh? That's part of my problem....I can't figure out "who" to trace. If I put a trigger to trace USER, I trace everybody. But...how can I tell the trigger who they are? Any ideas? I can usually figure out who they are by looking at v$session OS user and machine columns...maybe use that info?

Also, I'd agree that the multiple sessions approach is very "ms'-ish" but am being told there's no way around multiple sessions. Do you suppose that's really true?

Tom Kyte
October 27, 2002 - 8:36 pm UTC

I do not believe that is true (the multiple sessions). I believe it is configurable, but not being an MS developer at all (prefering the ability to put my code on any operating system) - I cannot answer completely.

You can try the discussion groups on otn.oracle.com -- there are ones that center around things like this.

Without the application code helping you out (eg: being able to set a property somewhere that says -- hey guys, after you connect, please turn on tracing), I don't know what to tell you. Given the design of the application -- i doubt they gave any consideration to things like this and won't be setup to do it at all.

(and yes, that "same oracle id" thing, gotta love that -- not)


No choice = stubborn

Jim, October 27, 2002 - 8:37 pm UTC

You are experiencing the "tyranny of the specification". Clearly this app has an MS Sqlserver bent to it. 1 user with multiple concurrent connections and probably no transactions - "autocommit mode". This is to get around the locking limitations of sqlserver (server side cursors bad). If one used transactions multiple connections per user would drive a developer batty and end up probably locking yourself up. Abomination.

The developers need to spend a little time learning what a database is for and why autcommit is not a good concept.

Thoughts confirmed

Karen Morton, October 27, 2002 - 9:10 pm UTC

Oracle did come along after SQL Server had already been used as the primary database environment in this case. I knew that there were differences, but I'm admittedly surprised at how basically devastating following one development path for one database can be so utterly performance killing when you try to implement that design on another. It's just hard to tell someone that they need to re-design an entire app to accommodate their database choice! Oh well.... This has just helped me to confirm some re-design suggestions I thought needed to be made. Thanks

Partial solution - IP address

andrew, October 28, 2002 - 12:56 pm UTC

If you have the luxury of having a know IP to do the testing from, then you can test for that IP in the logon trigger.
if SYS_CONTEXT ('USERENV','IP_ADDRESS') = '1.2.3.4' then...

Tom Kyte
October 28, 2002 - 7:15 pm UTC

Excellent idea! Variation on the "if user = the user"

You could use IP address to enable tracing

msc, October 28, 2002 - 12:59 pm UTC

---
Ooops....forgot to mention that all users of the app log in using the same Oracle ID....I know...another "bad" idea, huh? That's part of my problem....I can't figure out "who" to trace. If I put a trigger to trace USER, I trace
everybody.
---

You could use SYS_CONTEXT ('USERENV', 'IP_ADDRESS') to enable SQL tracing in ON LOGON trigger when client with specified IP address connects.



Sagi, October 29, 2002 - 6:12 am UTC

Hi All there,

A few of you gave a solution that is:

if SYS_CONTEXT ('USERENV','IP_ADDRESS') = '1.2.3.4' then...

But the problem is this works in Windows Environment. If you are on Unix normally you to a telnet session. So the IP_ADDRESS would be same...

Regards,
Sagi

Tom Kyte
October 29, 2002 - 6:47 am UTC

did you even read the question? its all about microsoft -- the cause of all of all of the heartburn is because its all about microsoft. If this was a unix based application -- we would not even be having this discussion!

Sagi, October 29, 2002 - 7:31 am UTC

Hi Tom and Others,

I hope none is angry with me. I got lost in the middle of the question while giving my comment. Sorry about that.

Yeap, SYS_CONTEXT ('USERENV', 'IP_ADDRESS') Would work since its MS.

Sorry for that once again.

Regards,
Sagi

Delphi specific comment got lost?

Holger Baer, October 29, 2002 - 9:27 am UTC

Tom,

I read this thread earlier today and there was a Delphi specific comment on how to turn on monitoring there. Now I can not see it any more - what happened?


Tom Kyte
October 29, 2002 - 1:48 pm UTC

i think you mean this:

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:4254158186083 <code>

it was about delphi and tracing and recently updated

OK

Siva, April 18, 2005 - 12:03 pm UTC 

Hi Tom,
When I do the following,
I am getting error as mentioned below.

How to correct this??

SQL> desc dbms_oracle_trace_user
SP2-0749: Cannot resolve circular path of synonym 

"dbms_oracle_trace_user"

Tom Kyte
April 18, 2005 - 12:16 pm UTC

looks like it was not installed properly (it is really old stuff too)

sys@ORA8IW> @dbmsotrc

Package created.


Grant succeeded.


Package created.


Synonym created.


Grant succeeded.


Package body created.


Package body created.

sys@ORA8IW> desc dbms_oracle_trace_user
PROCEDURE SET_ORACLE_TRACE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ON_OFF BOOLEAN IN DEFAULT
COLLECTION_NAME VARCHAR2 IN DEFAULT
FACILITY_NAME VARCHAR2 IN DEFAULT


resolve circular path of synonym

A reader, July 04, 2005 - 5:39 am UTC

Hi

We recently migrated a DB from Solaris to HPUX, after importing we faced many errors like this

SP2-0749: Cannot resolve circular path of synonym

We had to recreate them? What can be the cause of this?

Tom Kyte
July 04, 2005 - 10:40 am UTC

I've never heard of that happening before. Not knowing the precise steps you took, I'm not going to guess.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library