| regarding sql_id value in awr report [message #326840] |
Thu, 12 June 2008 16:36  |
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 #326845 is a reply to message #326840] |
Thu, 12 June 2008 17:22  |
 |
BlackSwan
Messages: 26766
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 |
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 |
 |
Michel Cadot
Messages: 68665
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
