195 lines
12 KiB
MySQL
195 lines
12 KiB
MySQL
--------------------------------------------------------------------------------
|
|
-- Author: Alberto Dell'Era
|
|
-- Copyright: (c) 2008-2013 Alberto Dell'Era http://www.adellera.it
|
|
--------------------------------------------------------------------------------
|
|
|
|
&COMM_IF_LT_10G. procedure ash_info_initialize( p_inst_id int )
|
|
&COMM_IF_LT_10G. is
|
|
&COMM_IF_LT_10G. l_code varchar2(100);
|
|
&COMM_IF_LT_10G. l_ash_info ash_info_t;
|
|
&COMM_IF_LT_10G. begin
|
|
&COMM_IF_LT_10G. if m_ash_over_thr_initialized then
|
|
&COMM_IF_LT_10G. return;
|
|
&COMM_IF_LT_10G. end if;
|
|
&COMM_IF_LT_10G. m_ash_over_thr_initialized := true;
|
|
|
|
&COMM_IF_LT_10G. for r in (select /*+ xplan_exec_marker */ sql_id, sql_child_number,
|
|
&COMM_IF_LT_10G. min(sample_time) as sample_time_min, max(sample_time) as sample_time_max, count(*) as cnt
|
|
&COMM_IF_LT_10G. from sys.gv_$active_session_history
|
|
&COMM_IF_LT_10G. where inst_id = p_inst_id
|
|
&COMM_IF_LT_10G. and sql_id is not null
|
|
&COMM_IF_LT_10G. and sql_child_number >= 0
|
|
&COMM_IF_LT_10G. -- optimizations for targeted searches
|
|
&COMM_IF_LT_10G. and (m_action_like is null or lower(action ) like lower(m_action_like) escape '\')
|
|
&COMM_IF_LT_10G. and (m_module_like is null or lower(module ) like lower(m_module_like) escape '\')
|
|
&COMM_IF_LT_10G. and (m_sql_id is null or sql_id = m_sql_id)
|
|
&COMM_IF_LT_10G. and (m_parsing_user_id is null or user_id = m_parsing_user_id)
|
|
&COMM_IF_LT_10G. and (m_child_number is null or sql_child_number = m_child_number)
|
|
&COMM_IF_LT_10G. -- end of optimizations for targeted searches
|
|
&COMM_IF_LT_10G. group by sql_id, sql_child_number
|
|
&COMM_IF_LT_10G. having count(*) >= m_ash_cnt_thr
|
|
&COMM_IF_LT_10G. order by sql_id, sql_child_number)
|
|
&COMM_IF_LT_10G. loop
|
|
&COMM_IF_LT_10G. l_code := r.sql_id||'.'||r.sql_child_number;
|
|
&COMM_IF_LT_10G. l_ash_info.sample_time_min := r.sample_time_min;
|
|
&COMM_IF_LT_10G. l_ash_info.sample_time_max := r.sample_time_max;
|
|
&COMM_IF_LT_10G. l_ash_info.cnt := r.cnt;
|
|
&COMM_IF_LT_10G. m_ash_over_thr(l_code) := l_ash_info;
|
|
&COMM_IF_LT_10G. end loop;
|
|
|
|
&COMM_IF_LT_10G. -- -- debug print
|
|
&COMM_IF_LT_10G. -- l_code := m_ash_over_thr.first;
|
|
&COMM_IF_LT_10G. -- while l_code is not null loop
|
|
&COMM_IF_LT_10G. -- print( ':: '||l_code||' '||m_ash_over_thr(l_code).cnt||' '||m_ash_over_thr(l_code).sample_time_min||' - '||m_ash_over_thr(l_code).sample_time_max );
|
|
&COMM_IF_LT_10G. -- l_code := m_ash_over_thr.next(l_code);
|
|
&COMM_IF_LT_10G. -- end loop;
|
|
&COMM_IF_LT_10G. end ash_info_initialize;
|
|
|
|
procedure ash_print_stmt_profile (
|
|
p_inst_id sys.gv_$sql.inst_id%type,
|
|
p_sql_id varchar2,
|
|
p_child_number sys.gv_$sql.child_number%type,
|
|
p_first_load_time date,
|
|
p_last_load_time date,
|
|
p_last_active_time date
|
|
)
|
|
is
|
|
&COMM_IF_LT_10G. l_sample_time_min timestamp(3);
|
|
&COMM_IF_LT_10G. l_sample_time_max timestamp(3);
|
|
&COMM_IF_LT_10G. l_prof scf_state_t;
|
|
&COMM_IF_LT_11G. l_prof2 scf_state_t;
|
|
&COMM_IF_LT_10G. l_code varchar2(100);
|
|
&COMM_IF_LT_10G. l_ash_info ash_info_t;
|
|
&COMM_IF_LT_10G. l_prev_event sys.gv_$active_session_history.event%type;
|
|
&COMM_IF_LT_11G. l_prev_line int;
|
|
begin
|
|
if :OPT_ASH_PROFILE_MINS = 0 then
|
|
return;
|
|
end if;
|
|
|
|
&COMM_IF_GT_9I. print ('gv$active_session_history does not exist before 10g.');
|
|
|
|
&COMM_IF_LT_10G. l_sample_time_min := greatest (p_first_load_time, nvl(p_last_active_time,systimestamp) - (:OPT_ASH_PROFILE_MINS / 1440));
|
|
&COMM_IF_LT_10G. l_sample_time_max := nvl(p_last_active_time,systimestamp);
|
|
&COMM_IF_LT_10G. print( l_sample_time_min||' '||l_sample_time_max);
|
|
|
|
&COMM_IF_LT_10G. ash_info_initialize(p_inst_id);
|
|
|
|
-- return if no enough samples exist in child cursor activity interval
|
|
&COMM_IF_LT_10G. l_code := p_sql_id||'.'||p_child_number;
|
|
&COMM_IF_LT_10G. if m_ash_over_thr.exists( l_code ) then
|
|
&COMM_IF_LT_10G. l_ash_info := m_ash_over_thr ( l_code );
|
|
&COMM_IF_LT_10G. if not (l_sample_time_min between l_ash_info.sample_time_min and l_ash_info.sample_time_max or
|
|
&COMM_IF_LT_10G. l_sample_time_max between l_ash_info.sample_time_min and l_ash_info.sample_time_max) then
|
|
&COMM_IF_LT_10G. print ('no sample found in v$ash for activity interval');
|
|
&COMM_IF_LT_10G. return;
|
|
&COMM_IF_LT_10G. end if;
|
|
&COMM_IF_LT_10G. else
|
|
&COMM_IF_LT_10G. print ('sample count zero or too low ( < '||m_ash_cnt_thr||' ) in v$ash');
|
|
&COMM_IF_LT_10G. return;
|
|
&COMM_IF_LT_10G. end if;
|
|
|
|
-- display ASH profile (event,object)
|
|
&COMM_IF_LT_10G. l_prev_event := 'x';
|
|
&COMM_IF_LT_10G. for p in (with ewb as (
|
|
&COMM_IF_LT_10G. select name
|
|
&COMM_IF_LT_10G. from sys.v_$event_name e
|
|
&COMM_IF_LT_10G. where e.wait_class in ('Application', 'Cluster', 'Concurrency', 'User I/O')
|
|
&COMM_IF_LT_10G. ), bas as (
|
|
&COMM_IF_LT_10G. select /*+ ordered use_hash(ewb o) */ /* xplan_exec_marker */
|
|
&COMM_IF_LT_10G. -- keep aligned with other profiles using event, current_obj#
|
|
&COMM_IF_LT_10G. decode(a.session_state, 'WAITING', a.event, 'ON CPU', 'cpu/runqueue', '**error**') as event,
|
|
&COMM_IF_LT_10G. decode(a.session_state, 'WAITING', decode(ewb.name, null, null, nvl(o.object_name, '#'||a.current_obj#) ), null) as object_name
|
|
&COMM_IF_LT_10G. from sys.gv_$active_session_history a, ewb, sys.dba_objects o
|
|
&COMM_IF_LT_10G. where a.inst_id = p_inst_id
|
|
&COMM_IF_LT_10G. and a.sql_id = p_sql_id
|
|
&COMM_IF_LT_10G. and a.sql_child_number = p_child_number
|
|
&COMM_IF_LT_10G. and a.sample_time between l_sample_time_min and l_sample_time_max
|
|
&COMM_IF_LT_10G. and a.event = ewb.name(+)
|
|
&COMM_IF_LT_10G. -- it costs too much: and (o.object_type not like 'JAVA%' and o.object_type not in('SYNONYM', 'TYPE', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY'))
|
|
&COMM_IF_LT_10G. and o.object_id(+) is not null
|
|
&COMM_IF_LT_10G. and a.current_obj# = o.object_id(+) -- checked this mapping, even for partitioned objects
|
|
&COMM_IF_LT_10G. ), gby as (
|
|
&COMM_IF_LT_10G. select event,
|
|
&COMM_IF_LT_10G. object_name,
|
|
&COMM_IF_LT_10G. count(*) as cnt
|
|
&COMM_IF_LT_10G. from bas
|
|
&COMM_IF_LT_10G. group by event, object_name
|
|
&COMM_IF_LT_10G. )
|
|
&COMM_IF_LT_10G. select event, object_name,
|
|
&COMM_IF_LT_10G. sum(cnt) over(partition by event) as cnt_event,
|
|
&COMM_IF_LT_10G. 100 * sum(cnt) over(partition by event) / sum(cnt) over() as perc_event,
|
|
&COMM_IF_LT_10G. cnt,
|
|
&COMM_IF_LT_10G. 100 * cnt / sum(cnt) over(partition by event) as perc_in_event
|
|
&COMM_IF_LT_10G. from gby
|
|
&COMM_IF_LT_10G. order by cnt_event desc, event, cnt desc
|
|
&COMM_IF_LT_10G. )
|
|
&COMM_IF_LT_10G. loop
|
|
&COMM_IF_LT_10G. scf_add_elem (l_prof, 'ash event', case when p.event != l_prev_event then p.event end);
|
|
&COMM_IF_LT_10G. scf_add_elem (l_prof, 'cnt' , case when p.event != l_prev_event then p.cnt_event end);
|
|
&COMM_IF_LT_10G. scf_add_elem (l_prof, '%' , case when p.event != l_prev_event then p.perc_event end);
|
|
&COMM_IF_LT_10G. scf_add_elem (l_prof, 'object' , p.object_name );
|
|
&COMM_IF_LT_10G. scf_add_elem (l_prof, 'cnt2' , p.cnt);
|
|
&COMM_IF_LT_10G. scf_add_elem (l_prof, '%/event' , p.perc_in_event);
|
|
&COMM_IF_LT_10G. l_prev_event := p.event;
|
|
&COMM_IF_LT_10G. end loop;
|
|
|
|
-- display ASH profile (plan line, event, object)
|
|
&COMM_IF_LT_11G. l_prev_event := 'x';
|
|
&COMM_IF_LT_11G. l_prev_line := -11;
|
|
&COMM_IF_LT_11G. for p in (with ewb as (
|
|
&COMM_IF_LT_11G. select name
|
|
&COMM_IF_LT_11G. from sys.v_$event_name e
|
|
&COMM_IF_LT_11G. where e.wait_class in ('Application', 'Cluster', 'Concurrency', 'User I/O')
|
|
&COMM_IF_LT_11G. ), bas as (
|
|
&COMM_IF_LT_11G. select /*+ ordered use_hash(ewb o) */ /* xplan_exec_marker */
|
|
&COMM_IF_LT_11G. -- keep aligned with other profiles using event, current_obj#
|
|
&COMM_IF_LT_11G. decode(a.session_state, 'WAITING', a.event, 'ON CPU', 'cpu/runqueue', '**error**') as event,
|
|
&COMM_IF_LT_11G. decode(a.session_state, 'WAITING', decode(ewb.name, null, null, nvl(o.object_name, '#'||a.current_obj#) ), null) as object_name,
|
|
&COMM_IF_LT_11G. sql_plan_line_id
|
|
&COMM_IF_LT_11G. from sys.gv_$active_session_history a, ewb, sys.dba_objects o
|
|
&COMM_IF_LT_11G. where a.inst_id = p_inst_id
|
|
&COMM_IF_LT_11G. and a.sql_id = p_sql_id
|
|
&COMM_IF_LT_11G. and a.sql_child_number = p_child_number
|
|
&COMM_IF_LT_11G. and a.sample_time between l_sample_time_min and l_sample_time_max
|
|
&COMM_IF_LT_11G and a.event = ewb.name(+)
|
|
&COMM_IF_LT_11G -- it costs too much: and (o.object_type not like 'JAVA%' and o.object_type not in('SYNONYM', 'TYPE', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY'))
|
|
&COMM_IF_LT_11G and o.object_id(+) is not null
|
|
&COMM_IF_LT_11G. and a.current_obj# = o.object_id(+) -- checked this mapping, even for partitioned objects
|
|
&COMM_IF_LT_11G. ), gby as (
|
|
&COMM_IF_LT_11G. select sql_plan_line_id,
|
|
&COMM_IF_LT_11G. event,
|
|
&COMM_IF_LT_11G. object_name,
|
|
&COMM_IF_LT_11G. count(*) as cnt
|
|
&COMM_IF_LT_11G. from bas
|
|
&COMM_IF_LT_11G. group by sql_plan_line_id, event, object_name
|
|
&COMM_IF_LT_11G. )
|
|
&COMM_IF_LT_11G. select sql_plan_line_id, event, object_name,
|
|
&COMM_IF_LT_11G. sum(cnt) over(partition by sql_plan_line_id) as cnt_line,
|
|
&COMM_IF_LT_11G. 100 * sum(cnt) over(partition by sql_plan_line_id) / sum(cnt) over() as perc_line,
|
|
&COMM_IF_LT_11G. sum(cnt) over(partition by sql_plan_line_id, event) as cnt_event,
|
|
&COMM_IF_LT_11G. 100 * sum(cnt) over(partition by sql_plan_line_id, event) / sum(cnt) over(partition by sql_plan_line_id) as perc_event_in_line,
|
|
&COMM_IF_LT_11G. cnt,
|
|
&COMM_IF_LT_11G. 100 * cnt / sum(cnt) over(partition by sql_plan_line_id, event) as perc_in_event
|
|
&COMM_IF_LT_11G. from gby
|
|
&COMM_IF_LT_11G. order by cnt_line desc, sql_plan_line_id, cnt_event desc, event, cnt desc
|
|
&COMM_IF_LT_11G. )
|
|
&COMM_IF_LT_11G. loop
|
|
&COMM_IF_LT_11G. --print(p.sql_plan_line_id||' '||p.cnt_line||' '||lpad(p.event,22)||' '||p.cnt_event||' '||lpad(nvl(get_cache_obj_name(p.current_obj#),' '),5)||' '||p.cnt);
|
|
&COMM_IF_LT_11G. scf_add_elem (l_prof2, 'ash plan line', case when p.sql_plan_line_id != l_prev_line then p.sql_plan_line_id end);
|
|
&COMM_IF_LT_11G. scf_add_elem (l_prof2, 'cnt' , case when p.sql_plan_line_id != l_prev_line then p.cnt_line end);
|
|
&COMM_IF_LT_11G. scf_add_elem (l_prof2, '%' , case when p.sql_plan_line_id != l_prev_line then p.perc_line end);
|
|
&COMM_IF_LT_11G. scf_add_elem (l_prof2, 'event' , case when p.sql_plan_line_id != l_prev_line or p.event != l_prev_event then p.event end);
|
|
&COMM_IF_LT_11G. scf_add_elem (l_prof2, 'cnt2' , case when p.sql_plan_line_id != l_prev_line or p.event != l_prev_event then p.cnt_event end);
|
|
&COMM_IF_LT_11G. scf_add_elem (l_prof2, '%/line' , case when p.sql_plan_line_id != l_prev_line or p.event != l_prev_event then p.perc_event_in_line end);
|
|
&COMM_IF_LT_11G. scf_add_elem (l_prof2, 'object' , p.object_name );
|
|
&COMM_IF_LT_11G. scf_add_elem (l_prof2, 'cnt3' , p.cnt);
|
|
&COMM_IF_LT_11G. scf_add_elem (l_prof2, '%/event' , p.perc_in_event);
|
|
&COMM_IF_LT_11G. l_prev_event := p.event;
|
|
&COMM_IF_LT_11G. l_prev_line := p.sql_plan_line_id;
|
|
&COMM_IF_LT_11G. end loop;
|
|
|
|
&COMM_IF_LT_10G. scf_print_output (l_prof , 'no profile info found in v$ash.', 'no profile info found in v$ash.');
|
|
&COMM_IF_LT_11G. scf_print_output (l_prof2, 'no profile info found in v$ash.', 'no profile info found in v$ash.');
|
|
end ash_print_stmt_profile;
|
|
|