Learn IT with Examples
Free tutorials with examples : learn IT & improve your IT skills. |
Site created by Paul Catalin Tomoiu Enterprise / Solution Architect |
This tutorial explains you how to find sessions with high rollback generated in an Oracle database.
Finding Sessions with High Rollback generated in an Oracle database For finding sessions with High Rollback generated you can write the following code and run it (under TOAD for instance):declare -- put here the declarations below !!! -- put here the PROCEDURE below !!! begin tab_session_stat.delete; vnu_ind1 := 0; select name into vva_database_name from v$database; DBMS_OUTPUT.PUT_LINE ( 'This report is done for '||vva_database_name||' database.'); DBMS_OUTPUT.PUT_LINE ( 'The analyze is done for '''||vva_resource_analysed||''' statistic. '); DBMS_OUTPUT.PUT_LINE ( 'Start Date: ' ||to_char(sysdate, 'Mon dd, yyyy hh:mm am') ); DBMS_OUTPUT.PUT_LINE ( 'Period of time analysed= '||n||' seconds.'); DBMS_OUTPUT.PUT_LINE ('-----------------------------------------------------------------------------------------'); for i in gather_statistics loop vnu_ind1 := vnu_ind1 +1; tab_session_stat(vnu_ind1).sess# := i.sid; tab_session_stat(vnu_ind1).SERIAL# := i.SERIAL#; tab_session_stat(vnu_ind1).ress_value_T0 := i.value; tab_session_stat(vnu_ind1).USERNAME := i.USERNAME; tab_session_stat(vnu_ind1).MACHINE := i.MACHINE; tab_session_stat(vnu_ind1).LOGON_TIME := i.LOGON_TIME; tab_session_stat(vnu_ind1).OSUSER := i.OSUSER; tab_session_stat(vnu_ind1).PROGRAM := i.PROGRAM; end loop; dbms_lock.sleep(n); -- sleep(n) n = number of sec. vnu_count_stats := 0; for i in gather_statistics loop for vnu_ind2 in tab_session_stat.first .. tab_session_stat.last loop if tab_session_stat(vnu_ind2).sess# = i.sid and tab_session_stat(vnu_ind2).LOGON_TIME = i.LOGON_TIME then tab_session_stat(vnu_ind2).ress_value_T1 := i.value; tab_session_stat(vnu_ind2).diff_ress_value := i.value - tab_session_stat(vnu_ind2).ress_value_T0; if tab_session_stat(vnu_ind2).diff_ress_value >= 0 then vnu_count_stats := vnu_count_stats + tab_session_stat(vnu_ind2).diff_ress_value; end if; end if; end loop; end loop; for vnu_ind2 in tab_session_stat.first .. tab_session_stat.last loop if vnu_count_stats <> 0 then tab_session_stat(vnu_ind2).ress_util_percent := (tab_session_stat(vnu_ind2).diff_ress_value/vnu_count_stats)*100; end if; end loop; order_tab_session_stat; vnu_ind1 := 0; if vnu_count_stats > 0 then for vnu_ind2 in tab_session_stat.first .. tab_session_stat.last loop if tab_session_stat(vnu_ind2).diff_ress_value >= 0 then vnu_ind1 := vnu_ind1 +1; dbms_output.put_line('SID= '||tab_session_stat(vnu_ind2).sess#||', SERIAL#= '|| tab_session_stat(vnu_ind2).SERIAL#||', USERNAME= '|| nvl(tab_session_stat(vnu_ind2).USERNAME,'BACKGROUND PROCESS')|| ', Utilisation for that periode= '||tab_session_stat(vnu_ind2).diff_ress_value|| '('||tab_session_stat(vnu_ind2).ress_util_percent||' %) MACHINE='|| tab_session_stat(vnu_ind2).MACHINE ); end if; if vnu_ind1 >= vnu_top_session_nr then exit; end if; end loop; else dbms_output.put_line('No activities !'); end if; end; procedure order_tab_session_stat is rec_temp_record session_stat_type; vnu_ind_change number; -- 0 if there are no row switches begin if tab_session_stat.count <> 0 then vnu_ind_change := 1; while vnu_ind_change <> 0 loop vnu_ind_change := 0; for i in tab_session_stat.first .. tab_session_stat.last-1 loop if nvl(tab_session_stat(i).ress_util_percent,-1) < nvl(tab_session_stat(i+1).ress_util_percent,-1) and nvl(tab_session_stat(i).ress_util_percent,0) >= 0 and tab_session_stat(i+1).ress_util_percent >= 0 then -- Here is a row switch SERIAL# rec_temp_record.sess# := tab_session_stat(i+1).sess#; rec_temp_record.SERIAL# := tab_session_stat(i+1).SERIAL#; rec_temp_record.ress_value_T0 := tab_session_stat(i+1).ress_value_T0; rec_temp_record.ress_value_T1 := tab_session_stat(i+1).ress_value_T1; rec_temp_record.diff_ress_value := tab_session_stat(i+1).diff_ress_value; rec_temp_record.ress_util_percent := tab_session_stat(i+1).ress_util_percent; rec_temp_record.USERNAME := tab_session_stat(i+1).USERNAME; rec_temp_record.MACHINE := tab_session_stat(i+1).MACHINE; rec_temp_record.LOGON_TIME := tab_session_stat(i+1).LOGON_TIME; rec_temp_record.OSUSER := tab_session_stat(i+1).OSUSER; rec_temp_record.PROGRAM := tab_session_stat(i+1).PROGRAM; tab_session_stat(i+1).sess# := tab_session_stat(i).sess#; tab_session_stat(i+1).SERIAL# := tab_session_stat(i).SERIAL#; tab_session_stat(i+1).ress_value_T0 := tab_session_stat(i).ress_value_T0; tab_session_stat(i+1).ress_value_T1 := tab_session_stat(i).ress_value_T1; tab_session_stat(i+1).diff_ress_value := tab_session_stat(i).diff_ress_value; tab_session_stat(i+1).ress_util_percent := tab_session_stat(i).ress_util_percent; tab_session_stat(i+1).USERNAME := tab_session_stat(i).USERNAME; tab_session_stat(i+1).MACHINE := tab_session_stat(i).MACHINE; tab_session_stat(i+1).LOGON_TIME := tab_session_stat(i).LOGON_TIME; tab_session_stat(i+1).OSUSER := tab_session_stat(i).OSUSER; tab_session_stat(i+1).PROGRAM := tab_session_stat(i).PROGRAM; tab_session_stat(i).sess# := rec_temp_record.sess#; tab_session_stat(i).SERIAL# := rec_temp_record.SERIAL#; tab_session_stat(i).ress_value_T0 := rec_temp_record.ress_value_T0; tab_session_stat(i).ress_value_T1 := rec_temp_record.ress_value_T1; tab_session_stat(i).diff_ress_value := rec_temp_record.diff_ress_value; tab_session_stat(i).ress_util_percent := rec_temp_record.ress_util_percent; tab_session_stat(i).USERNAME := rec_temp_record.USERNAME; tab_session_stat(i).MACHINE := rec_temp_record.MACHINE; tab_session_stat(i).LOGON_TIME := rec_temp_record.LOGON_TIME; tab_session_stat(i).OSUSER := rec_temp_record.OSUSER; tab_session_stat(i).PROGRAM := rec_temp_record.PROGRAM; vnu_ind_change := 1; end if; end loop; end loop; end if; end order_tab_session_stat; -------------------------------------------------------- vnu_top_session_nr number := 10; -- To find the top 10 resource consumer sessions (DEFAULT value) vva_resource_analysed varchar2(60) :='transaction rollbacks' ; n number := 60; -- sleep(n) n = number of sec. between statistics -------------------------------------------------------- TYPE session_stat_type IS RECORD (sess# number, SERIAL# number, ress_value_T0 number, -- statistics at time T(0) ress_value_T1 number, -- statistics at time T(1) diff_ress_value number, -- diff = stat. value at T(1) - stat. value at T(0) ress_util_percent number(6,3), USERNAME varchar2(32), MACHINE varchar2(200), LOGON_TIME varchar2(200), OSUSER varchar2(200), PROGRAM varchar2(200) ); type tab_session_stat_type is table of session_stat_type index by binary_integer; tab_session_stat tab_session_stat_type; cursor gather_statistics is select b.sid, c.SERIAL#, a.name, b.value, c.USERNAME, c.MACHINE, to_char(c.LOGON_TIME, 'hh:mi:ss') LOGON_TIME, c.OSUSER, c.PROGRAM from v$statname a, v$sesstat b, v$session c where a.statistic# = b.statistic# and b.SID = c.SID and a.name=vva_resource_analysed; vnu_ind1 number; vnu_ind2 number; vnu_count_stats number; vva_database_name varchar2(50); |
|
||
Copyright 2017-2021 © LEARN-IT-WITH-EXAMPLES.com All rights reserved. This website does not represent any corporation (Oracle, Microsoft, etc) in any way. This site is not using cookies, but it use some services that might use cookies. All information is supposed to be accurate, but it is not guaranteed to be correct. The usage of the information from this website is strictly at your own risk. If you don't like these policies, you have to stop using the website. |