Home » RDBMS Server » Performance Tuning » Execution plan for every SQL, executed by user (Oracle10g)
Execution plan for every SQL, executed by user [message #426242] Wed, 14 October 2009 09:46 Go to next message
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 #426245 is a reply to message #426242] Wed, 14 October 2009 09:58 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
ALTER SESSION SET SQL_TRACE=TRUE
Re: Execution plan for every SQL, executed by user [message #426252 is a reply to message #426245] Wed, 14 October 2009 10:21 Go to previous messageGo to next message
McLaud
Messages: 15
Registered: August 2008
Junior Member
It is not possible in my case for same reason
The question is: how to retrieve from Active Session History the sql's executed by specified user and get execution plan for those statements?
Re: Execution plan for every SQL, executed by user [message #426254 is a reply to message #426242] Wed, 14 October 2009 10:29 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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;
Re: Execution plan for every SQL, executed by user [message #426260 is a reply to message #426252] Wed, 14 October 2009 10:40 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
>It is not possible in my case for same reason
"same reason" as what?

I have made custom LOGON trigger to ensure SQL_TRACE only gets enabled for the single session I desire to trace.
Re: Execution plan for every SQL, executed by user [message #426265 is a reply to message #426242] Wed, 14 October 2009 11:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
NOTE. My script is executed under SYSTEM user account

As an aside, this isn't good practice. SYS/SYSTEM aren't for doing development things in.
Re: Execution plan for every SQL, executed by user [message #426509 is a reply to message #426242] Fri, 16 October 2009 01:20 Go to previous messageGo to next message
McLaud
Messages: 15
Registered: August 2008
Junior Member
I believe, I know that the problem is.
I add additional parameter in my script - module name, seems now it working more correct.

[Updated on: Fri, 16 October 2009 01:22]

Report message to a moderator

Re: Execution plan for every SQL, executed by user [message #426711 is a reply to message #426242] Mon, 19 October 2009 02:04 Go to previous messageGo to next message
McLaud
Messages: 15
Registered: August 2008
Junior Member
Hello All,
I have attached the script, designed for
1. Extracting execution plan for user statements, executed by specified module.
2. Extract Oracle SQL Tuning adviser report for every user statement.
May be you will find this script useful.
Any comments are welcome.

[Updated on: Mon, 19 October 2009 02:05]

Report message to a moderator

Re: Execution plan for every SQL, executed by user [message #426737 is a reply to message #426711] Mon, 19 October 2009 04:21 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Thank you kindly for that.
Previous Topic: Analyze table hangs for hours
Next Topic: query tuning
Goto Forum:
  


Current Time: Fri Sep 30 22:45:13 CDT 2016

Total time taken to generate the page: 0.11188 seconds