Rem Rem $Header: rdbms/admin/dbmsaddm.sql /main/6 2008/09/15 14:07:49 ushaft Exp $ Rem Rem dbmsaddm.sql Rem Rem Copyright (c) 2006, 2008, Oracle. All rights reserved. Rem Rem NAME Rem dbmsaddm.sql - Declaration of the DBMS_ADDM package Rem Rem DESCRIPTION Rem Utilities for using ADDM Rem Rem NOTES Rem Rem MODIFIED (MM/DD/YY) Rem ushaft 08/08/08 - ADd ASH query for ADDM findings Rem ushaft 11/29/06 - moved describe_directive to prvt_hdm Rem ushaft 08/07/06 - added SQL/Segment/Parameter directives Rem ushaft 05/10/06 - Created Rem CREATE OR REPLACE PACKAGE dbms_addm authid current_user IS -- PROCEDURE DBMS_ADDM.analyze_db -- PURPOSE: Create and execute an ADDM task to analyze all instances -- of a database for a rango of snapshots. -- PARAMETERS: -- TASK_NAME -- An optional task name. Task names must be unique to the user. -- If not specified, a name will be generated by the system. -- BEGIN_SNAPSHOT -- The snapshot id for the beginning of the analysis period -- END_SNAPSHOT -- The snapshot id for the end of the analysis period -- DB_ID -- The databse to analyze, defaults to current database. procedure analyze_db ( task_name in out varchar2, begin_snapshot in number, end_snapshot in number, db_id in number := NULL); -- PROCEDURE DBMS_ADDM.analyze_inst -- PURPOSE: Create and execute an ADDM task to analyze a specific instance -- of a database for a rango of snapshots. -- PARAMETERS: -- TASK_NAME -- An optional task name. Task names must be unique to the user. -- If not specified, a name will be generated by the system. -- BEGIN_SNAPSHOT -- The snapshot id for the beginning of the analysis period -- END_SNAPSHOT -- The snapshot id for the end of the analysis period -- INSTANCE_NUMBER -- The number of the instance to analyze, defaults to current -- DB_ID -- The databse to analyze, defaults to current database. 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); -- PROCEDURE DBMS_ADDM.analyze_partial -- PURPOSE: Create and execute an ADDM task to analyze a partial list -- of instances for a rango of snapshots. -- PARAMETERS: -- TASK_NAME -- An optional task name. Task names must be unique to the user. -- If not specified, a name will be generated by the system. -- INSTANCE_NUMBERS -- A comma separated list of instance numbers that participate -- in the analysis -- BEGIN_SNAPSHOT -- The snapshot id for the beginning of the analysis period -- END_SNAPSHOT -- The snapshot id for the end of the analysis period -- DB_ID -- The databse to analyze, defaults to current database. 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); -- PROCEDURE DBMS_ADDM.insert_finding_directive -- PURPOSE: insert a directive filtering a finding, either for a task or -- system wide. -- PARAMETERS: -- TASK_NAME -- An optional task name. If NULL, this is a system directive. -- DIR_NAME -- A unique name identifying the new directive -- FINDING_NAME -- A name for the finding, will appear in the "FINDING_NAME" -- column of dba_advisor_findings. -- MIN_ACTIVE_SESSIONS -- Minimal value for active sessions. A finding is filtered out -- if it has less active sessions than this value. -- MIN_PERC_IMPACT -- Minimal value for percentage of impact (0-100). A finding -- is filtered out if its impact is less than this percentage of -- the database time of the analysis period. 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); -- PROCEDURE DBMS_ADDM.insert_finding_directive -- PURPOSE: insert a directive filtering sql recommendations, -- either for a task or system wide. -- PARAMETERS: -- TASK_NAME -- An optional task name. If NULL, this is a system directive. -- DIR_NAME -- A unique name identifying the new directive -- SQL_ID -- A string identifying the SQL (13 characters). -- MIN_ACTIVE_SESSIONS -- Minimal value for active sessions. Ain action is filtered out -- if the SQL's total db time is less than this value. -- MIN_PERC_IMPACT -- Minimal value for response time (microseconds). -- A SQL action is filtered out if the response time is less than -- this value. 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); -- PROCEDURE DBMS_ADDM.insert_segment_directive -- PURPOSE: insert a directive filtering recommendations to run segment -- advisor. -- PARAMETERS: -- TASK_NAME -- An optional task name. If NULL, this is a system directive. -- DIR_NAME -- A unique name identifying the new directive -- OWNER_NAME -- The owner of the segment. -- OBJECT_NAME -- The name of the main object. If NULL, then all objects for the -- specified owner are filtered. -- SUB_OBJECT_NAME -- The name of a partition of the object. If null, than all -- partitions of the object are filtered. Also, if the object is -- not partitioned, leave the value as NULL. -- 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); -- PROCEDURE DBMS_ADDM.insert_segment_directive -- PURPOSE: insert a directive filtering recommendations to run segment -- advisor. -- PARAMETERS: -- TASK_NAME -- An optional task name. If NULL, this is a system directive. -- DIR_NAME -- A unique name identifying the new directive -- OBJECT_NUMBER -- The data object number (as appears in OBJ$) -- procedure insert_segment_directive ( task_name in varchar2, dir_name in varchar2, object_number in number); -- PROCEDURE DBMS_ADDM.insert_parameter_directive -- PURPOSE: insert a directive filtering a recommendations to change -- a system parameter. -- PARAMETERS: -- TASK_NAME -- An optional task name. If NULL, this is a system directive. -- DIR_NAME -- A unique name identifying the new directive -- PARAMETER_NAME -- The name of the system parameter. All actions to change this -- parameter are filtered out. procedure insert_parameter_directive ( task_name in varchar2, dir_name in varchar2, parameter_name in varchar2); -- PROCEDURE DBMS_ADDM.delete_finding_directive -- PURPOSE: delete an addm directive -- PARAMETERS: -- TASK_NAME -- An optional task name. If NULL, this is a system directive. -- DIR_NAME -- A unique name identifying the directive procedure delete_finding_directive ( task_name in varchar2, dir_name in varchar2); -- PROCEDURE DBMS_ADDM.delete_sql_directive -- PURPOSE: delete an addm directive -- PARAMETERS: -- TASK_NAME -- An optional task name. If NULL, this is a system directive. -- DIR_NAME -- A unique name identifying the directive procedure delete_sql_directive ( task_name in varchar2, dir_name in varchar2); -- PROCEDURE DBMS_ADDM.delete_segment_directive -- PURPOSE: delete an addm directive -- PARAMETERS: -- TASK_NAME -- An optional task name. If NULL, this is a system directive. -- DIR_NAME -- A unique name identifying the directive procedure delete_segment_directive ( task_name in varchar2, dir_name in varchar2); -- PROCEDURE DBMS_ADDM.delete_parameter_directive -- PURPOSE: delete an addm directive -- PARAMETERS: -- TASK_NAME -- An optional task name. If NULL, this is a system directive. -- DIR_NAME -- A unique name identifying the directive procedure delete_parameter_directive ( task_name in varchar2, dir_name in varchar2); -- PROCEDURE DBMS_ADDM.delete -- PURPOSE: delete an ADDM task -- PARAMETERS: -- TASK_NAME -- The name of the task to be deleted. procedure delete ( task_name in varchar2); -- FUNCTION DBMS_ADDM.get_report -- RETURN: a CLOB containing the report. -- PURPOSE: get the report of an ADDM task. -- PARAMETERS: -- TASK_NAME -- The name of the task to report on. function get_report ( task_name in varchar2) return clob; -- FUNCTION DBMS_ADDM.get_ash_query -- RETURN: a varchar containing an ASH query identifying the rows -- in ASH with impact for the finding. -- PURPOSE: get a SQL text of an ASH query for a finding -- PARAMETERS: -- TASK_NAME -- The name of the task. -- FINDING_ID -- The ID of the finding within the task function get_ash_query ( task_name in varchar2, finding_id in number) return varchar2; END dbms_addm; / GRANT EXECUTE ON dbms_addm TO PUBLIC; CREATE OR REPLACE PUBLIC SYNONYM dbms_addm FOR dbms_addm;