SQL_ID For Active Sessions In V$Session

All posts relating to Oracle database administration.

Moderator: Tim...

SQL_ID For Active Sessions In V$Session

Postby ssadoglu » Sun May 12, 2013 2:22 pm

Hi Tim,

I've been using Oracle 11GR2 Enterprise Edition. Let's say , after using 100% of my CPU a SQL query for a long time I want to find sql_id of the session. But it is losing. How do I monitor sql_id column of the session? is there any method? if so , please clarify.

Thanks & Regards,
ssadoglu
Senior Member
 
Posts: 58
Joined: Wed Feb 02, 2011 10:59 am

Re: SQL_ID For Active Sessions In V$Session

Postby Tim... » Sun May 12, 2013 3:06 pm

Hi.

The V$SESSION view has the SQL_ID of the current session being run for each session.

http://docs.oracle.com/cd/B28359_01/ser ... s_3016.htm

There are a load of different ways to find the top SQL statement of an instance.

- "Top Activity" screen of Cloud Control, assuming you have a Diagnostics and Tuning Pack license.
- ASH Viewer (standard connection does not use ASH, so doesn't need Diagnostics and Tuning Pack license: http://sourceforge.net/projects/ashv/
- Top Sessions script: http://www.oracle-base.com/dba/script.p ... ssions.sql
- TOAD and SQL Developer have Top-SQL type screens.
- The ADDM and AWR reports display Top SQL information, assuming you have a Diagnostics and Tuning Pack license.
- Statspack.
- MOATS: http://blog.tanelpoder.com/2011/03/29/m ... g-scripts/

So find the session, then check the SQL_ID of that session.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17933
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: SQL_ID For Active Sessions In V$Session

Postby ssadoglu » Mon May 13, 2013 9:41 am

Thanks,
ssadoglu
Senior Member
 
Posts: 58
Joined: Wed Feb 02, 2011 10:59 am

Re: SQL_ID For Active Sessions In V$Session

Postby Tim... » Mon May 13, 2013 10:50 am

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17933
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle Database Administration

Who is online

Users browsing this forum: No registered users and 2 guests

cron