Home » RDBMS Server » Performance Tuning » regarding sql_id value in awr report (oracle 10.2.0.3 solaris 10 64bit)
regarding sql_id value in awr report [message #326840] Thu, 12 June 2008 16:36 Go to next message
skumar.dba
Messages: 82
Registered: October 2007
Member
Experts,

one of my database is facing performance problem. i ran awr report. and i found that 5 sql statement are taking high resource like cpu, and user I/O. i found only sql statements as well as sql_id.

now i want to find which user is running this sql statements?
i want to dig more details of each sql statements based on sql_id.
i am trying to enable session trace using dbms_system.set_sql_trace_to_session(sid,serial,true). for this i need to know what is session id of corresponding sql statement.

can any one tell how do get session_id?


regards,
jyothy.
Re: regarding sql_id value in awr report [message #326843 is a reply to message #326840] Thu, 12 June 2008 16:44 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>can any one tell how do get session_id?
from V$SESSION
Re: regarding sql_id value in awr report [message #326844 is a reply to message #326843] Thu, 12 June 2008 17:14 Go to previous messageGo to next message
skumar.dba
Messages: 82
Registered: October 2007
Member
Thanks for quick response.

i have one more question. can one sql_id have multiple session_id values?

i am getting 16 sid's for one sql_id. i am bit confusing. can any one explain me.
Re: regarding sql_id value in awr report [message #326845 is a reply to message #326840] Thu, 12 June 2008 17:22 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>i am getting 16 sid's for one sql_id
Typical & expected.
You have an application being utilized by multiple users concurrently.
A given SQL resides in a package being run by 16 different folks.

WHY do you think it matters which user invoked the SQL.
The SQL statement does not know or care who invoked it!
It will not behave differently if USER_A or USER_B is running it.

SELECT SID FROM V$SESSION WHERE SQL_ID = '8ma2t4fpan9kz'
Will statement above behave differently if you run it as opposed to me running it?

[Updated on: Thu, 12 June 2008 17:22] by Moderator

Report message to a moderator

Re: regarding sql_id value in awr report [message #327531 is a reply to message #326845] Mon, 16 June 2008 15:35 Go to previous messageGo to next message
JackyShu
Messages: 25
Registered: May 2008
Junior Member
anacedent wrote on Thu, 12 June 2008 18:22
>i am

WHY do you think it matters which user invoked the SQL.
The SQL statement does not know or care who invoked it!
It will not behave differently if USER_A or USER_B is running it.



it is true in most case. but need to be careful when you have view or synonym, because the same name in different schema might refer to different things.
Re: regarding sql_id value in awr report [message #327605 is a reply to message #327531] Tue, 17 June 2008 00:53 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is true but the child is different:
Session 1 : MICHEL
---------
SQL> col sid format a10
SQL> select user, sys_context('userenv','sid') sid from dual;
USER                           SID
------------------------------ ----------
MICHEL                         140

1 row selected.

SQL> declare
  2    cursor c is select ename from emp;
  3  begin
  4     open c;
  5     dbms_lock.sleep (30);
  6  end;
  7  /

Session 2 : SCOTT
---------
SQL> col sid format a10
SQL> select user, sys_context('userenv','sid') sid from dual;
USER                           SID
------------------------------ ----------
SCOTT                          144

1 row selected.

SQL> declare
  2    cursor c is select ename from emp;
  3  begin
  4     open c;
  5     dbms_lock.sleep (30);
  6  end;
  7  /

Session 3 : DBA
---------
SQL> select sid, sql_id, sql_child_number from v$session where sid in (140,144);
       SID SQL_ID        SQL_CHILD_NUMBER
---------- ------------- ----------------
       140 21kxjy8u53g89                0
       144 21kxjy8u53g89                1

2 rows selected.

Regards
Michel
Previous Topic: Partitioning a table in Source
Next Topic: Extents and blocks
Goto Forum:
  


Current Time: Fri Dec 02 20:22:07 CST 2016

Total time taken to generate the page: 0.13221 seconds