|
package body dbms_addm
is
procedure validate_task(task_name_in in out varchar2)
is
tid number;
status_t varchar2(11);
begin
select max(status) into status_t
from user_advisor_tasks
where advisor_name = 'addm' and
task_name = task_name_in;
if status_t is null then
dbms_advisor.create_task('addm', tid, task_name_in);
return;
elsif status_t = 'completed' or status_t = 'executing' then
return;
elsif status_t <> 'initial' then
dbms_advisor.reset_task(task_name_in);
end if;
end;
procedure analyze_db ( task_name in out varchar2,
begin_snapshot in number,
end_snapshot in number,
db_id in number := null)
is
begin
dbms_management_packs.check_pack_enabled('diagnostic');
validate_task(task_name);
dbms_advisor.set_task_parameter(task_name, 'start_snapshot', begin_snapshot);
dbms_advisor.set_task_parameter(task_name, 'end_snapshot', end_snapshot);
if db_id is not null then
dbms_advisor.set_task_parameter(task_name, 'db_id', db_id);
end if;
dbms_advisor.execute_task(task_name);
end;
procedure analyze_inst ( task_name in out varchar2,
begin_snapshot in number,
end_snapshot in number,
instance_number in number := null,
db_id in number := null)
is
inst_id number;
dbid number;
begin
dbms_management_packs.check_pack_enabled('diagnostic');
validate_task(task_name);
dbms_advisor.set_task_parameter(task_name, 'start_snapshot', begin_snapshot);
dbms_advisor.set_task_parameter(task_name, 'end_snapshot', end_snapshot);
inst_id := instance_number;
dbid := db_id;
if db_id is not null then
dbms_advisor.set_task_parameter(task_name, 'db_id', dbid);
end if;
if inst_id is null then
select dbms_utility.current_instance into inst_id from dual;
end if;
dbms_advisor.set_task_parameter(task_name, 'instance', inst_id);
dbms_advisor.execute_task(task_name);
end;
procedure analyze_partial ( task_name in out varchar2,
instance_numbers in varchar2,
begin_snapshot in number,
end_snapshot in number,
db_id in number := null)
is
begin
dbms_management_packs.check_pack_enabled('diagnostic');
validate_task(task_name);
dbms_advisor.set_task_parameter(task_name, 'instances', instance_numbers);
dbms_advisor.set_task_parameter(task_name, 'start_snapshot', begin_snapshot);
dbms_advisor.set_task_parameter(task_name, 'end_snapshot', end_snapshot);
if db_id is not null then
dbms_advisor.set_task_parameter(task_name, 'db_id', db_id);
end if;
dbms_advisor.execute_task(task_name);
end;
procedure insert_finding_directive ( task_name in varchar2,
dir_name in varchar2,
finding_name in varchar2,
min_active_sessions in number := 0,
min_perc_impact in number := 0)
is
dir_id number;
dir_text varchar2(2000);
cnt number;
task_name_cpy varchar2(100) := task_name;
begin
dbms_management_packs.check_pack_enabled('diagnostic');
if min_active_sessions is null or min_active_sessions < 0 then
dbms_sys_error.raise_system_error(-13704,
nvl(to_char(min_active_sessions),'null'),
'min_active_session');
end if;
if min_perc_impact is null or min_perc_impact < 0 or min_perc_impact >100 then
dbms_sys_error.raise_system_error(-13704,
nvl(to_char(min_perc_impact),'null'),
'min_perc_impact');
end if;
if not prvt_advisor.finding_name_exists('addm',finding_name) then
dbms_sys_error.raise_system_error(-13713, finding_name);
end if;
if task_name is not null then
validate_task(task_name_cpy);
end if;
dir_id := prvt_advisor.get_directive_id('addm', 'finding filter');
dir_text :=
' ' ||
' ' ||
' ' || finding_name || ' ' ||
' ' ||
' ' ||
' ' || min_active_sessions || ' ' ||
' ' ||
' ' ||
' ' || min_perc_impact || ' ' ||
' ' ||
'';
dbms_advisor.insert_directive(dir_id, dir_name, task_name, dir_text);
end;
procedure insert_sql_directive ( task_name in varchar2,
dir_name in varchar2,
sql_id in varchar2,
min_active_sessions in number := 0,
min_response_time in number := 0)
is
dir_id number;
dir_text varchar2(2000);
i number;
c varchar2(1);
task_name_cpy varchar2(100) := task_name;
begin
dbms_management_packs.check_pack_enabled('diagnostic');
if sql_id is null then
dbms_sys_error.raise_system_error(-13704, 'null',
'sql_id');
end if;
if min_active_sessions is null or min_active_sessions < 0 then
dbms_sys_error.raise_system_error(-13704,
nvl(to_char(min_active_sessions),'null'),
'min_active_session');
end if;
if min_response_time is null or min_response_time < 0 then
dbms_sys_error.raise_system_error(-13704,
nvl(to_char(min_response_time),'null'),
'min_response_time');
end if;
if length(sql_id) <> 13 then
dbms_sys_error.raise_system_error(-13714, sql_id);
end if;
for i in 1..13 loop
c := substr(sql_id, i, 1);
if not ((c >= '0' and c <= '9') or (c >= 'a' and c <= 'z')) then
dbms_sys_error.raise_system_error(-13714, sql_id);
end if;
end loop;
if task_name is not null then
validate_task(task_name_cpy);
end if;
dir_id := prvt_advisor.get_directive_id('addm', 'sql filter');
dir_text :=
' ' ||
' ' ||
' ' || sql_id || ' ' ||
' ' ||
' ' ||
' ' || min_active_sessions || ' ' ||
' ' ||
' ' ||
' ' || min_response_time || ' ' ||
' ' ||
'';
dbms_advisor.insert_directive(dir_id, dir_name, task_name, dir_text);
end;
procedure insert_segment_directive ( task_name in varchar2,
dir_name in varchar2,
owner_name in varchar2,
object_name in varchar2 := null,
sub_object_name in varchar2 := null)
is
dir_id number;
dir_text varchar2(2000);
name1 varchar2(100);
name2 varchar2(100);
name3 varchar2(100);
task_name_cpy varchar2(100) := task_name;
begin
dbms_management_packs.check_pack_enabled('diagnostic');
if owner_name is null then
name1 := '%';
else
name1 := lower(owner_name);
end if;
if object_name is null then
name2 := '%';
else
name2 := lower(object_name);
end if;
if sub_object_name is null then
name3 := '%';
else
name3 := lower(sub_object_name);
end if;
if task_name is not null then
validate_task(task_name_cpy);
end if;
dir_id := prvt_advisor.get_directive_id('addm', 'segment filter');
dir_text :=
' ' ||
' ' ||
' ' || name1 || ' ' ||
' ' ||
' ' ||
' ' || name2 || ' ' ||
' ' ||
' ' ||
' ' || name3 || ' ' ||
' ' ||
' ' ||
' 0 ' ||
' ' ||
'';
dbms_advisor.insert_directive(dir_id, dir_name, task_name, dir_text);
end;
procedure insert_segment_directive ( task_name in varchar2,
dir_name in varchar2,
object_number in number)
is
dir_id number;
dir_text varchar2(2000);
task_name_cpy varchar2(100) := task_name;
begin
dbms_management_packs.check_pack_enabled('diagnostic');
if task_name is not null then
validate_task(task_name_cpy);
end if;
dir_id := prvt_advisor.get_directive_id('addm', 'segment filter');
dir_text :=
' ' ||
' ' ||
' 0 ' ||
' ' ||
' ' ||
' 0 ' ||
' ' ||
' ' ||
' 0 ' ||
' ' ||
' ' ||
' ' || object_number || ' ' ||
' ' ||
'';
dbms_advisor.insert_directive(dir_id, dir_name, task_name, dir_text);
end;
procedure insert_parameter_directive ( task_name in varchar2,
dir_name in varchar2,
parameter_name in varchar2)
is
dir_id number;
dir_text varchar2(2000);
cnt number;
pn varchar2(300) := lower(parameter_name);
task_name_cpy varchar2(100) := task_name;
begin
dbms_management_packs.check_pack_enabled('diagnostic');
if task_name is not null then
validate_task(task_name_cpy);
end if;
dir_id := prvt_advisor.get_directive_id('addm', 'parameter filter');
if not prvt_hdm.parameter_exists(pn) then
dbms_sys_error.raise_system_error(-13715, nvl(parameter_name, 'null'));
end if;
dir_text :=
' ' ||
' ' ||
' ' || pn || ' ' ||
' ' ||
'';
dbms_advisor.insert_directive(dir_id, dir_name, task_name, dir_text);
end;
procedure delete_finding_directive ( task_name in varchar2,
dir_name in varchar2)
is
dir_id number;
begin
dbms_management_packs.check_pack_enabled('diagnostic');
dir_id := prvt_advisor.get_directive_id('addm', 'finding filter');
dbms_advisor.delete_directive(dir_id, dir_name, task_name);
end;
procedure delete_sql_directive ( task_name in varchar2,
dir_name in varchar2)
is
dir_id number;
begin
dbms_management_packs.check_pack_enabled('diagnostic');
dir_id := prvt_advisor.get_directive_id('addm', 'sql filter');
dbms_advisor.delete_directive(dir_id, dir_name, task_name);
end;
procedure delete_segment_directive ( task_name in varchar2,
dir_name in varchar2)
is
dir_id number;
begin
dbms_management_packs.check_pack_enabled('diagnostic');
dir_id := prvt_advisor.get_directive_id('addm', 'segment filter');
dbms_advisor.delete_directive(dir_id, dir_name, task_name);
end;
procedure delete_parameter_directive ( task_name in varchar2,
dir_name in varchar2)
is
dir_id number;
begin
dbms_management_packs.check_pack_enabled('diagnostic');
dir_id := prvt_advisor.get_directive_id('addm', 'parameter filter');
dbms_advisor.delete_directive(dir_id, dir_name, task_name);
end;
procedure delete ( task_name in varchar2)
is
cursor tnames(tn varchar2) is
select task_name
from user_advisor_tasks
where task_name = tn
and advisor_name = 'addm';
begin
dbms_management_packs.check_pack_enabled('diagnostic');
for t in tnames(task_name) loop
dbms_advisor.delete_task(t.task_name);
end loop;
end;
function get_report ( task_name in varchar2)
return clob
is
begin
dbms_management_packs.check_pack_enabled('diagnostic');
return dbms_advisor.get_task_report(task_name);
end;
function get_ash_query ( task_name in varchar2, finding_id in number)
return varchar2
is
begin
dbms_management_packs.check_pack_enabled('diagnostic');
dbms_advisor.check_privs;
prvt_advisor.check_task_enabled(task_name,
dbms_advisor.adv_name_addm,
false);
return prvt_hdm.get_ash_query(task_name, finding_id);
end;
end dbms_addm;
|
