I am new to Toad and relatively new to Oracle. I have recently received access to use Session Browser in Toad. Can someone point me in the right direction on how to effectively use this tool. For instance, how to find long running queries, find bad explain plans etc.
Not sure the Session Browser is going to give you that. It will show you connected sessions and you could look at ACTIVE sessions to identify long running ones and see the explain plan but that’s a fairly basic approach. Look up things like AWR and ADDM which might prove better for achieving the sorts of things you are looking for.
I use, and have used, the session browser a lot, however my purpose is always to see what’s going on with a specific session at this very moment. So, I use it when something is slow, or to figure out what an application is currently trying to do in the database. That generally amounts to checking to see what SQL the session is currently executing and then tuning that, or checking for database locks. It is also helpful to see that the client/app is currently not waiting for the database to know that something else is going on.
if i want to check the resources taken to execute a query, how can i use session browser?
The Session Browser is really more about seeing what a session is doing than seeing what resources a single query will take. If your query has already been executed recently, you can find it in the “SGA Trace/Optimization” window and there you can find some information about it.
ooh nice! thank you for the reply!!!
Hi,
can someone please explain me the functionality of session browser as i cannot really use it effectively.
Im trying to monitor crm from internet explorer. In the session browser i can see JDBC thin client. But all the accounts seem to be inactive.
How exactly does this work? and how can you monitor a specific process?
I would appreciate if someone could answer my questions.
Morning lida_theo,
the session browser might not be your best hope of monitoring web based “stuff” as web based stuff is, I seem to remember, not necessarily going to always come back on the same connection. So you might be following one particular session in a given SID, but the next page request that hits the database might well come in on a different SID. WebLogic (other webby middle-ware is available) usually has connection pools, and they hold a connection to the database. The browser talks to the WebLogic pool and gets whatever connection is free/next in the queue and then goes to the database. This feature of middle-ware also makes life extremely difficult when trying to trace a particular session to diagnose a problem - the trace results either end up in multiple trace files or only bits of the trace are generated. Have I mentioned how much I really hate middle-ware systems? [;)]
Active sessions are those which are carrying out a database action “right this second”, or as Oracle’s reference manual states, "ACTIVE - Session currently executing SQL". Executing SQL usually means a parse, exec or fetch exercise is in progress. If the results have been sent back to the client, then the session is no longer active.
Monitoring a single session in the database is a simple case of selecting it, setting the refresh rate (minimum 5 seconds) and watching what happens. You can set a trace from this screen also. Monitoring a single session from the application is almost impossible I’m afraid, due to the above connection pooling. There might be a way that the middle-ware can be set to use a dedicated connection for some application features, but I don’t know of one, and in the past, what I’ve had to do to get a trace running was to use a test system where the fault could be reproduced, and get everyone off except me and the affected user, then trace everything so that if the user’s session flipped between database sessions, I’d still get something out of the mess! Sadly, the WebLogic’s keep alive feature of running a select count(*) from dual, or similar, really messes everything up!
If there were Oracle errors reported, then it made life a little easier as it is possible to grep the numerous trace files (thankfully, I was never running Windows database servers!) to find the error number, and investigate from there.
How does it work? As far as I’m aware, Toad interrogates various views in the database, beginning with V$SESSION to get the main tab’s information, then when you click on a different tab, say the locks tab, Toad interrogates whichever view it needs to to bring back that information. Database->Spool SQL will show you what views are being interrogated for each tab etc, but do this with refresh turned off!
Session browser is your friend when it comes to tracing or monitoring a single database session, but is unlikely to be of much use when you have to deal with a webby front end and some middle-ware between the user and the database. [:(]
HTH
Norm,
Thank you so much for the immediate response.
You gave me a lot of info and I appreciate it…I will investigate it further and see what I can do.
Thank you and have a lovely evening.
Lida
There are also several FREE training course right here on Toad World and one of them covers the Session Browser. The rest all cover various portions and features of Toad for Oracle so I recommend viewing them.
-Mark
Thank you 
Is there any link to them?
Hi Lida,
www.toadworld.com/…/media-library is the complete “boxed set” of Toad for Oracle videos. The session browser one mentioned by Mark is unfortunately, not there - as far as I can see.