| Execution plan for every SQL, executed by user [message #426242] |
Wed, 14 October 2009 09:46  |
 |
McLaud
Messages: 15
Registered: August 2008
|
Junior Member |
|
|
Hello All
Recently I ask myself how to go get to know all sql statements, executed by specified user in database and extract execution plan for every statement, in order to analyze it and may be tune it.
I extract all users' sqls from two time dimensions:
1. v$active_session_history keeps recent executed sqls
2. Dba_Hist_Active_Sess_History keep the history of sql
And for every sql_id and child number try to retrieve the execution plan by using DBMS_XPLAN built-in package:
procedure process_plan is
begin
select tf.*
bulk collect into varchar_table
FROM
(
select distinct sqlid, childn
from
(
select distinct sql_id as sqlid, sql_child_number as childn from v$active_session_history s where s.user_id = 82
UNION
select distinct sql_id as sqlid, sql_child_number as childn from Dba_Hist_Active_Sess_History s where s.user_id = 82
)
) ht,
table (DBMS_XPLAN.display_cursor(ht.sqlid, ht.childn, 'SERIAL' )) tf;
for i in 1..varchar_table.last
loop
to_file(varchar_table(i));
end loop;
end;
Where
varchar_table dbms_sql.varchar2_table;
to_file procedure print line to file.
BUT, my problem is than in output file I see a LOT of sql's statement, executed not by my user (user_id=82) but a number of other users in the database. I do not waste my time just browsing execution plan for sql's from SYSTEM, SYS and other background users in database.
So, the general question is:
HOW to get sql_id for only those sql statements, executed by only specified user and get executions plan for those statements.
NOTE. My script is executed under SYSTEM user account
|
|
|
|
|
|
|
|
| Re: Execution plan for every SQL, executed by user [message #426254 is a reply to message #426242] |
Wed, 14 October 2009 10:29  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
How are you identifying that the queries were run by users other than yourself?
If you're using a gui interface, then it will generate a suprising number of queries by itself, and many things that you do in the Db generate queries that you don't get to know about.
|
|
|
|
| Re: Execution plan for every SQL, executed by user [message #426258 is a reply to message #426242] |
Wed, 14 October 2009 10:35 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I'd use something like this:
To get the plans:select * from v$sql_plan
where sql_id in (select sql_id from Dba_Hist_Active_Sess_History where user_id = 61
union all
select sql_id from v$active_Session_history where user_id = 61)
order by sql_id,id;
To get the Actual SQL: select * from v$sqltext
where sql_id in (select sql_id from Dba_Hist_Active_Sess_History where user_id = 61
union all
select sql_id from v$active_Session_history where user_id = 61)
order by sql_id,piece; |
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
