[Home] [Help]
76: --
77: REGULAR_PROFILE CONSTANT VARCHAR2(11) := 'SQL PROFILE';
78: PX_PROFILE CONSTANT VARCHAR2(10) := 'PX PROFILE';
79:
80: -- sqlset staging table constants
81: STS_STGTAB_10_2_VERSION CONSTANT NUMBER := 1;
82: STS_STGTAB_11_1_VERSION CONSTANT NUMBER := 2;
83: STS_STGTAB_11_2_VERSION CONSTANT NUMBER := 3;
84: STS_STGTAB_11_202_VERSION CONSTANT NUMBER := 4;
220: description IN VARCHAR2 := NULL,
221: con_name IN VARCHAR2 := NULL)
222: RETURN VARCHAR2;
223:
224: ---------------------- create_tuning_task - sqlset format -------------------
225: -- NAME:
226: -- create_tuning_task - sqlset format
227: --
228: -- DESCRIPTION:
222: RETURN VARCHAR2;
223:
224: ---------------------- create_tuning_task - sqlset format -------------------
225: -- NAME:
226: -- create_tuning_task - sqlset format
227: --
228: -- DESCRIPTION:
229: -- This function is called to prepare the tuning of a sqlset
230: -- The function mainly creates an advisor task and sets its parameters.
225: -- NAME:
226: -- create_tuning_task - sqlset format
227: --
228: -- DESCRIPTION:
229: -- This function is called to prepare the tuning of a sqlset
230: -- The function mainly creates an advisor task and sets its parameters.
231: --
232: -- PARAMETERS:
233: -- sqlset_name (IN) - sqlset name
229: -- This function is called to prepare the tuning of a sqlset
230: -- The function mainly creates an advisor task and sets its parameters.
231: --
232: -- PARAMETERS:
233: -- sqlset_name (IN) - sqlset name
234: -- basic_filter (IN) - SQL predicate to filter the SQL from the STS
235: -- object_filter (IN) - object filter
236: -- rank(i) (IN) - an order-by clause on the selected SQL
237: -- result_percentage (IN) - a percentage on the sum of a ranking measure
258: -- + MAX_DIRECT_WRITES: plan with max direct
259: -- writes
260: -- + MAX_OPTIMIZER_COST: plan with max opt. cost
261: --
262: -- sqlset_owner (IN) - the owner of the sqlset, or null for current
263: -- schema owner
264: --
265: -- RETURNS:
266: -- SQL tuning task unique name
268: -- EXCEPTIONS:
269: -- To be done
270: -----------------------------------------------------------------------------
271: FUNCTION create_tuning_task(
272: sqlset_name IN VARCHAR2,
273: basic_filter IN VARCHAR2 := NULL,
274: object_filter IN VARCHAR2 := NULL,
275: rank1 IN VARCHAR2 := NULL,
276: rank2 IN VARCHAR2 := NULL,
281: time_limit IN NUMBER := TIME_LIMIT_DEFAULT,
282: task_name IN VARCHAR2 := NULL,
283: description IN VARCHAR2 := NULL,
284: plan_filter IN VARCHAR2 := 'MAX_ELAPSED_TIME',
285: sqlset_owner IN VARCHAR2 := NULL)
286: RETURN VARCHAR2;
287:
288: ---------------------- create_tuning_task - SPA Task format -----------------
289: -- NAME:
340: -- MODE : tuning scope (comprehensive, limited)
341: -- USERNAME : username under which the statement will be parsed
342: -- BASIC_FILTER : basic filter for sql tuning set
343: -- OBJECT_FILTER : object filter for sql tuning set
344: -- PLAN_FILTER : plan filter for sql tuning set (see select_sqlset
345: -- for possible values)
346: -- RANK_MEASURE1 : first ranking measure for sql tuning set
347: -- RANK_MEASURE2 : second possible ranking measure for sql tuning set
348: -- RANK_MEASURE3 : third possible ranking measure for sql tuning set
920:
921: --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
922: --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
923: -- --------------------------- --
924: -- SQLSET PROCEDURES/FUNCTIONS --
925: -- --------------------------- --
926: --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
927: --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
928:
929: -----------------------------------------------------------------------------
930: -- Examples --
931: -----------------------------------------------------------------------------
932: -- In the following we give two examples that show how to use the package in
933: -- order to create, populate, manipulate and drop a sqlset.
934: -- The first example shows how to build a new sqlset by extracting
935: -- data from the Cursor cache, while the second one explains how to build a
936: -- sqlset from a USER defined workload.
937: --
930: -- Examples --
931: -----------------------------------------------------------------------------
932: -- In the following we give two examples that show how to use the package in
933: -- order to create, populate, manipulate and drop a sqlset.
934: -- The first example shows how to build a new sqlset by extracting
935: -- data from the Cursor cache, while the second one explains how to build a
936: -- sqlset from a USER defined workload.
937: --
938: --------------------------------------------
932: -- In the following we give two examples that show how to use the package in
933: -- order to create, populate, manipulate and drop a sqlset.
934: -- The first example shows how to build a new sqlset by extracting
935: -- data from the Cursor cache, while the second one explains how to build a
936: -- sqlset from a USER defined workload.
937: --
938: --------------------------------------------
939: -- EXAMPLE 1: select from the cursor cache --
940: --------------------------------------------
939: -- EXAMPLE 1: select from the cursor cache --
940: --------------------------------------------
941: --
942: -- DECLARE
943: -- sqlset_name VARCHAR2(30); /* sqlset name */
944: -- sqltset_cur dbms_sqltune.sqlset_cursor; /* a sqlset cursor variable */
945: -- ref_id NUMBER; /* a reference on a sqlset */
946: -- BEGIN
947: --
940: --------------------------------------------
941: --
942: -- DECLARE
943: -- sqlset_name VARCHAR2(30); /* sqlset name */
944: -- sqltset_cur dbms_sqltune.sqlset_cursor; /* a sqlset cursor variable */
945: -- ref_id NUMBER; /* a reference on a sqlset */
946: -- BEGIN
947: --
948: -- /* Choose an name for the sqlset to create */
941: --
942: -- DECLARE
943: -- sqlset_name VARCHAR2(30); /* sqlset name */
944: -- sqltset_cur dbms_sqltune.sqlset_cursor; /* a sqlset cursor variable */
945: -- ref_id NUMBER; /* a reference on a sqlset */
946: -- BEGIN
947: --
948: -- /* Choose an name for the sqlset to create */
949: -- sqlset_name := 'SQLSET_TEST_1';
944: -- sqltset_cur dbms_sqltune.sqlset_cursor; /* a sqlset cursor variable */
945: -- ref_id NUMBER; /* a reference on a sqlset */
946: -- BEGIN
947: --
948: -- /* Choose an name for the sqlset to create */
949: -- sqlset_name := 'SQLSET_TEST_1';
950: --
951: -- /* Create an empty sqlset. You automatically become the owner of
952: -- this sqlset */
945: -- ref_id NUMBER; /* a reference on a sqlset */
946: -- BEGIN
947: --
948: -- /* Choose an name for the sqlset to create */
949: -- sqlset_name := 'SQLSET_TEST_1';
950: --
951: -- /* Create an empty sqlset. You automatically become the owner of
952: -- this sqlset */
953: -- dbms_sqltune.create_sqlset(sqlset_name, 'test purpose');
947: --
948: -- /* Choose an name for the sqlset to create */
949: -- sqlset_name := 'SQLSET_TEST_1';
950: --
951: -- /* Create an empty sqlset. You automatically become the owner of
952: -- this sqlset */
953: -- dbms_sqltune.create_sqlset(sqlset_name, 'test purpose');
954: --
955: -- /***********************************************************************
948: -- /* Choose an name for the sqlset to create */
949: -- sqlset_name := 'SQLSET_TEST_1';
950: --
951: -- /* Create an empty sqlset. You automatically become the owner of
952: -- this sqlset */
953: -- dbms_sqltune.create_sqlset(sqlset_name, 'test purpose');
954: --
955: -- /***********************************************************************
956: -- * Call the select_cursor_cache table function to order the sql *
949: -- sqlset_name := 'SQLSET_TEST_1';
950: --
951: -- /* Create an empty sqlset. You automatically become the owner of
952: -- this sqlset */
953: -- dbms_sqltune.create_sqlset(sqlset_name, 'test purpose');
954: --
955: -- /***********************************************************************
956: -- * Call the select_cursor_cache table function to order the sql *
957: -- * statements in the cursor cache by cpu_time (ranking measure1) and *
960: -- * statements, i.e., top 100 which represents (result_limit). *
961: -- * Only the firts ranking measure is spefied and the content of *
962: -- * the cursor cache is not filtered. *
963: -- * *
964: -- * The OPEN-FOR statement associates the sqlset cursor variable *
965: -- * with the SELECT-FROM-TABLE dynamic query which is used to call the *
966: -- * table function and fetch its results. Notice that you need not to *
967: -- * close the cursor. When this cursor is used to populate a Sql Tuning *
968: -- * Set using the load_sqlset procedure, this later will close *
964: -- * The OPEN-FOR statement associates the sqlset cursor variable *
965: -- * with the SELECT-FROM-TABLE dynamic query which is used to call the *
966: -- * table function and fetch its results. Notice that you need not to *
967: -- * close the cursor. When this cursor is used to populate a Sql Tuning *
968: -- * Set using the load_sqlset procedure, this later will close *
969: -- * it for you. *
970: -- * *
971: -- * Notice the use of function VALUE(P) which takes as its argument, *
972: -- * the table alias for the table function and returns object instances *
970: -- * *
971: -- * Notice the use of function VALUE(P) which takes as its argument, *
972: -- * the table alias for the table function and returns object instances *
973: -- * corresponding to rows as retuned by the table function which are *
974: -- * instances of type SQLSET_ROW. *
975: -- * ********************************************************************/
976: -- OPEN sqlset_cur FOR
977: -- SELECT VALUE(P) /* use of function VALUE */
978: -- FROM TABLE(
972: -- * the table alias for the table function and returns object instances *
973: -- * corresponding to rows as retuned by the table function which are *
974: -- * instances of type SQLSET_ROW. *
975: -- * ********************************************************************/
976: -- OPEN sqlset_cur FOR
977: -- SELECT VALUE(P) /* use of function VALUE */
978: -- FROM TABLE(
979: -- dbms_sqltune.select_cursor_cache(NULL, /* basic filter */
980: -- NULL, /* object filter */
986: -- ) P; /* table instance */
987: --
988: --
989: -- /***********************************************************************
990: -- * Call the load_sqlset procedure to populated the created *
991: -- * sqlset by the results of the cursor cache table function *
992: -- **********************************************************************/
993: -- dbms_sqltune.load_sqlset(sqlset_name, sqlset_cur);
994: --
987: --
988: --
989: -- /***********************************************************************
990: -- * Call the load_sqlset procedure to populated the created *
991: -- * sqlset by the results of the cursor cache table function *
992: -- **********************************************************************/
993: -- dbms_sqltune.load_sqlset(sqlset_name, sqlset_cur);
994: --
995: -- /***********************************************************************
989: -- /***********************************************************************
990: -- * Call the load_sqlset procedure to populated the created *
991: -- * sqlset by the results of the cursor cache table function *
992: -- **********************************************************************/
993: -- dbms_sqltune.load_sqlset(sqlset_name, sqlset_cur);
994: --
995: -- /***********************************************************************
996: -- * Add a reference to the sqlset so that other users cannot *
997: -- * modified it, i.e., drop it, delete statement from it, update it or *
992: -- **********************************************************************/
993: -- dbms_sqltune.load_sqlset(sqlset_name, sqlset_cur);
994: --
995: -- /***********************************************************************
996: -- * Add a reference to the sqlset so that other users cannot *
997: -- * modified it, i.e., drop it, delete statement from it, update it or *
998: -- * load it. Like this, the sqlset is protected. User have only *
999: -- * a read-only access to the sqlset. *
1000: -- * The add_reference function returns a reference ID that will be used *
994: --
995: -- /***********************************************************************
996: -- * Add a reference to the sqlset so that other users cannot *
997: -- * modified it, i.e., drop it, delete statement from it, update it or *
998: -- * load it. Like this, the sqlset is protected. User have only *
999: -- * a read-only access to the sqlset. *
1000: -- * The add_reference function returns a reference ID that will be used *
1001: -- * later to deactivate the sqlset. *
1002: -- **********************************************************************/
995: -- /***********************************************************************
996: -- * Add a reference to the sqlset so that other users cannot *
997: -- * modified it, i.e., drop it, delete statement from it, update it or *
998: -- * load it. Like this, the sqlset is protected. User have only *
999: -- * a read-only access to the sqlset. *
1000: -- * The add_reference function returns a reference ID that will be used *
1001: -- * later to deactivate the sqlset. *
1002: -- **********************************************************************/
1003: -- ref_id :=
997: -- * modified it, i.e., drop it, delete statement from it, update it or *
998: -- * load it. Like this, the sqlset is protected. User have only *
999: -- * a read-only access to the sqlset. *
1000: -- * The add_reference function returns a reference ID that will be used *
1001: -- * later to deactivate the sqlset. *
1002: -- **********************************************************************/
1003: -- ref_id :=
1004: -- dbms_sqltune.add_sqlset_reference(sqlset_name,
1005: -- 'test sqlset: '|| sqlset_name);
1000: -- * The add_reference function returns a reference ID that will be used *
1001: -- * later to deactivate the sqlset. *
1002: -- **********************************************************************/
1003: -- ref_id :=
1004: -- dbms_sqltune.add_sqlset_reference(sqlset_name,
1005: -- 'test sqlset: '|| sqlset_name);
1006: --
1007: -- /* process your sqlset */
1008: -- ...
1001: -- * later to deactivate the sqlset. *
1002: -- **********************************************************************/
1003: -- ref_id :=
1004: -- dbms_sqltune.add_sqlset_reference(sqlset_name,
1005: -- 'test sqlset: '|| sqlset_name);
1006: --
1007: -- /* process your sqlset */
1008: -- ...
1009: -- ...
1003: -- ref_id :=
1004: -- dbms_sqltune.add_sqlset_reference(sqlset_name,
1005: -- 'test sqlset: '|| sqlset_name);
1006: --
1007: -- /* process your sqlset */
1008: -- ...
1009: -- ...
1010: -- ...
1011: --
1009: -- ...
1010: -- ...
1011: --
1012: -- /**********************************************************************
1013: -- * When your are done, remove the reference on the sqlset, so that it *
1014: -- * can be modified either by you (owner) or by another user who has a *
1015: -- * supper privilege ADMINISTER ANY SQLSET, etc. *
1016: -- *********************************************************************/
1017: -- dbms_sqltune.remove_sqlset_reference(sqlset_name, ref_id);
1011: --
1012: -- /**********************************************************************
1013: -- * When your are done, remove the reference on the sqlset, so that it *
1014: -- * can be modified either by you (owner) or by another user who has a *
1015: -- * supper privilege ADMINISTER ANY SQLSET, etc. *
1016: -- *********************************************************************/
1017: -- dbms_sqltune.remove_sqlset_reference(sqlset_name, ref_id);
1018: --
1019: --
1013: -- * When your are done, remove the reference on the sqlset, so that it *
1014: -- * can be modified either by you (owner) or by another user who has a *
1015: -- * supper privilege ADMINISTER ANY SQLSET, etc. *
1016: -- *********************************************************************/
1017: -- dbms_sqltune.remove_sqlset_reference(sqlset_name, ref_id);
1018: --
1019: --
1020: -- /* Call the drop procedure to drop the sqlset */
1021: -- dbms_sqltune.drop_sqlset(sqlset_name);
1016: -- *********************************************************************/
1017: -- dbms_sqltune.remove_sqlset_reference(sqlset_name, ref_id);
1018: --
1019: --
1020: -- /* Call the drop procedure to drop the sqlset */
1021: -- dbms_sqltune.drop_sqlset(sqlset_name);
1022: -- ...
1023: -- END
1024: --
1017: -- dbms_sqltune.remove_sqlset_reference(sqlset_name, ref_id);
1018: --
1019: --
1020: -- /* Call the drop procedure to drop the sqlset */
1021: -- dbms_sqltune.drop_sqlset(sqlset_name);
1022: -- ...
1023: -- END
1024: --
1025: -------------------------------------------
1026: -- EXAMPLE 2: select from a user workload --
1027: -------------------------------------------
1028: --
1029: -- DECLARE
1030: -- sqlset_name VARCHAR2(30); /* sqlset name */
1031: -- sqlset_cur dbms_sqltune.sqlset_cursor; /* a sqlset cursor variable */
1032: -- ref_id NUMBER; /* a reference on a sqlset */
1033: -- BEGIN
1034: --
1027: -------------------------------------------
1028: --
1029: -- DECLARE
1030: -- sqlset_name VARCHAR2(30); /* sqlset name */
1031: -- sqlset_cur dbms_sqltune.sqlset_cursor; /* a sqlset cursor variable */
1032: -- ref_id NUMBER; /* a reference on a sqlset */
1033: -- BEGIN
1034: --
1035: -- /* Choose an name for the sqlset to create */
1028: --
1029: -- DECLARE
1030: -- sqlset_name VARCHAR2(30); /* sqlset name */
1031: -- sqlset_cur dbms_sqltune.sqlset_cursor; /* a sqlset cursor variable */
1032: -- ref_id NUMBER; /* a reference on a sqlset */
1033: -- BEGIN
1034: --
1035: -- /* Choose an name for the sqlset to create */
1036: -- sqlset_name := 'SQLSET_TEST_2';
1031: -- sqlset_cur dbms_sqltune.sqlset_cursor; /* a sqlset cursor variable */
1032: -- ref_id NUMBER; /* a reference on a sqlset */
1033: -- BEGIN
1034: --
1035: -- /* Choose an name for the sqlset to create */
1036: -- sqlset_name := 'SQLSET_TEST_2';
1037: --
1038: -- /* Create an empty sqlset. You automatically become the owner of
1039: -- this SQLSET */
1032: -- ref_id NUMBER; /* a reference on a sqlset */
1033: -- BEGIN
1034: --
1035: -- /* Choose an name for the sqlset to create */
1036: -- sqlset_name := 'SQLSET_TEST_2';
1037: --
1038: -- /* Create an empty sqlset. You automatically become the owner of
1039: -- this SQLSET */
1040: -- dbms_sqltune.create_sqlset(sqlset_name, 'test purpose');
1034: --
1035: -- /* Choose an name for the sqlset to create */
1036: -- sqlset_name := 'SQLSET_TEST_2';
1037: --
1038: -- /* Create an empty sqlset. You automatically become the owner of
1039: -- this SQLSET */
1040: -- dbms_sqltune.create_sqlset(sqlset_name, 'test purpose');
1041: --
1042: -- /***********************************************************************
1035: -- /* Choose an name for the sqlset to create */
1036: -- sqlset_name := 'SQLSET_TEST_2';
1037: --
1038: -- /* Create an empty sqlset. You automatically become the owner of
1039: -- this SQLSET */
1040: -- dbms_sqltune.create_sqlset(sqlset_name, 'test purpose');
1041: --
1042: -- /***********************************************************************
1043: -- * In this example we suppose that the user workload is stored in *
1036: -- sqlset_name := 'SQLSET_TEST_2';
1037: --
1038: -- /* Create an empty sqlset. You automatically become the owner of
1039: -- this SQLSET */
1040: -- dbms_sqltune.create_sqlset(sqlset_name, 'test purpose');
1041: --
1042: -- /***********************************************************************
1043: -- * In this example we suppose that the user workload is stored in *
1044: -- * a single table USER_WORKLOAD_TABLE. We suppose that the table stores*
1043: -- * In this example we suppose that the user workload is stored in *
1044: -- * a single table USER_WORKLOAD_TABLE. We suppose that the table stores*
1045: -- * only the text of a set of SQL statements identified by their sql_id.*
1046: -- * Use the OPEN-FOR statement to associate the query that extracts the *
1047: -- * content of the user workload, with a sqlset cursor before loading it*
1048: -- * into the sqlset. *
1049: -- * Notice the use of the CONSTRUCTOR of the sqlset_row object type *
1050: -- * This is IMPORTANT because the cursor MUST contains instances *
1051: -- * of this type as required by the load_sql function. Otherwise an *
1044: -- * a single table USER_WORKLOAD_TABLE. We suppose that the table stores*
1045: -- * only the text of a set of SQL statements identified by their sql_id.*
1046: -- * Use the OPEN-FOR statement to associate the query that extracts the *
1047: -- * content of the user workload, with a sqlset cursor before loading it*
1048: -- * into the sqlset. *
1049: -- * Notice the use of the CONSTRUCTOR of the sqlset_row object type *
1050: -- * This is IMPORTANT because the cursor MUST contains instances *
1051: -- * of this type as required by the load_sql function. Otherwise an *
1052: -- * error will occur and the SQLSET will not be loaded. *
1045: -- * only the text of a set of SQL statements identified by their sql_id.*
1046: -- * Use the OPEN-FOR statement to associate the query that extracts the *
1047: -- * content of the user workload, with a sqlset cursor before loading it*
1048: -- * into the sqlset. *
1049: -- * Notice the use of the CONSTRUCTOR of the sqlset_row object type *
1050: -- * This is IMPORTANT because the cursor MUST contains instances *
1051: -- * of this type as required by the load_sql function. Otherwise an *
1052: -- * error will occur and the SQLSET will not be loaded. *
1053: -- **********************************************************************/
1048: -- * into the sqlset. *
1049: -- * Notice the use of the CONSTRUCTOR of the sqlset_row object type *
1050: -- * This is IMPORTANT because the cursor MUST contains instances *
1051: -- * of this type as required by the load_sql function. Otherwise an *
1052: -- * error will occur and the SQLSET will not be loaded. *
1053: -- **********************************************************************/
1054: -- OPEN sqlset_cur FOR
1055: -- SELECT
1056: -- SQLSET_ROW(sql_id, sql_text, null, null, null, null,
1050: -- * This is IMPORTANT because the cursor MUST contains instances *
1051: -- * of this type as required by the load_sql function. Otherwise an *
1052: -- * error will occur and the SQLSET will not be loaded. *
1053: -- **********************************************************************/
1054: -- OPEN sqlset_cur FOR
1055: -- SELECT
1056: -- SQLSET_ROW(sql_id, sql_text, null, null, null, null,
1057: -- null, 0, 0, 0, 0, 0, 0, 0, 0, 0, null, 0, 0, 0, 0
1058: -- ) AS row
1052: -- * error will occur and the SQLSET will not be loaded. *
1053: -- **********************************************************************/
1054: -- OPEN sqlset_cur FOR
1055: -- SELECT
1056: -- SQLSET_ROW(sql_id, sql_text, null, null, null, null,
1057: -- null, 0, 0, 0, 0, 0, 0, 0, 0, 0, null, 0, 0, 0, 0
1058: -- ) AS row
1059: -- FROM user_workload_table;
1060: --
1058: -- ) AS row
1059: -- FROM user_workload_table;
1060: --
1061: -- /***********************************************************************
1062: -- * Call the load_sql procedure to populated the created sqlset by the *
1063: -- * results of the cursor *
1064: -- **********************************************************************/
1065: -- dbms_sqltune.load_sqlset(sqlsetname, sqlsetcur);
1066: --
1061: -- /***********************************************************************
1062: -- * Call the load_sql procedure to populated the created sqlset by the *
1063: -- * results of the cursor *
1064: -- **********************************************************************/
1065: -- dbms_sqltune.load_sqlset(sqlsetname, sqlsetcur);
1066: --
1067: -- /* the rest of the steps are similar to those in example 1 */
1068: -- ...
1069: -- ...
1074:
1075: -----------------------------------------------------------------------------
1076: -- type declarations --
1077: -----------------------------------------------------------------------------
1078: ----------------------------------- sqlset_cursor ---------------------------
1079: -- NAME:
1080: -- sqlset_cursor
1081: --
1082: -- DESCRIPTION:
1076: -- type declarations --
1077: -----------------------------------------------------------------------------
1078: ----------------------------------- sqlset_cursor ---------------------------
1079: -- NAME:
1080: -- sqlset_cursor
1081: --
1082: -- DESCRIPTION:
1083: -- define a cursor type for SQL statements with their related data.
1084: -- This type is mainly used by the load_sqlset procedure
1080: -- sqlset_cursor
1081: --
1082: -- DESCRIPTION:
1083: -- define a cursor type for SQL statements with their related data.
1084: -- This type is mainly used by the load_sqlset procedure
1085: -- as an argument to populate a sqlset from a possible data
1086: -- source. See the load_sqlset description for more details.
1087: --
1088: -- NOTES:
1081: --
1082: -- DESCRIPTION:
1083: -- define a cursor type for SQL statements with their related data.
1084: -- This type is mainly used by the load_sqlset procedure
1085: -- as an argument to populate a sqlset from a possible data
1086: -- source. See the load_sqlset description for more details.
1087: --
1088: -- NOTES:
1089: -- It is important to keep in mind that this cursor is WEAKLY DEFINED.
1082: -- DESCRIPTION:
1083: -- define a cursor type for SQL statements with their related data.
1084: -- This type is mainly used by the load_sqlset procedure
1085: -- as an argument to populate a sqlset from a possible data
1086: -- source. See the load_sqlset description for more details.
1087: --
1088: -- NOTES:
1089: -- It is important to keep in mind that this cursor is WEAKLY DEFINED.
1090: -- A variable of type sqlStatCursor when it is used either as an input
1088: -- NOTES:
1089: -- It is important to keep in mind that this cursor is WEAKLY DEFINED.
1090: -- A variable of type sqlStatCursor when it is used either as an input
1091: -- by the load_sql procedure or returned by all table functions, it MUST
1092: -- contains rows of type sqlset_row.
1093: ----------------------------------------------------------------------------
1094: TYPE sqlset_cursor IS REF CURSOR;
1095:
1096:
1090: -- A variable of type sqlStatCursor when it is used either as an input
1091: -- by the load_sql procedure or returned by all table functions, it MUST
1092: -- contains rows of type sqlset_row.
1093: ----------------------------------------------------------------------------
1094: TYPE sqlset_cursor IS REF CURSOR;
1095:
1096:
1097: -----------------------------------------------------------------------------
1098: -- procedure/function declarations --
1096:
1097: -----------------------------------------------------------------------------
1098: -- procedure/function declarations --
1099: -----------------------------------------------------------------------------
1100: ---------------------------------- create_sqlset ----------------------------
1101: -- NAME:
1102: -- create_sqlset
1103: --
1104: -- DESCRIPTION:
1098: -- procedure/function declarations --
1099: -----------------------------------------------------------------------------
1100: ---------------------------------- create_sqlset ----------------------------
1101: -- NAME:
1102: -- create_sqlset
1103: --
1104: -- DESCRIPTION:
1105: -- This procedure creates a sqlset object in the database.
1106: --
1101: -- NAME:
1102: -- create_sqlset
1103: --
1104: -- DESCRIPTION:
1105: -- This procedure creates a sqlset object in the database.
1106: --
1107: -- PARAMETERS:
1108: -- sqlset_name (IN) - the sqlset name
1109: -- description (IN) - the description of the sqlset
1104: -- DESCRIPTION:
1105: -- This procedure creates a sqlset object in the database.
1106: --
1107: -- PARAMETERS:
1108: -- sqlset_name (IN) - the sqlset name
1109: -- description (IN) - the description of the sqlset
1110: -- sqlset_owner (IN) - the owner of the sqlset, or null for current schema
1111: -- owner
1112: -----------------------------------------------------------------------------
1105: -- This procedure creates a sqlset object in the database.
1106: --
1107: -- PARAMETERS:
1108: -- sqlset_name (IN) - the sqlset name
1109: -- description (IN) - the description of the sqlset
1110: -- sqlset_owner (IN) - the owner of the sqlset, or null for current schema
1111: -- owner
1112: -----------------------------------------------------------------------------
1113: PROCEDURE create_sqlset(
1106: --
1107: -- PARAMETERS:
1108: -- sqlset_name (IN) - the sqlset name
1109: -- description (IN) - the description of the sqlset
1110: -- sqlset_owner (IN) - the owner of the sqlset, or null for current schema
1111: -- owner
1112: -----------------------------------------------------------------------------
1113: PROCEDURE create_sqlset(
1114: sqlset_name IN VARCHAR2,
1109: -- description (IN) - the description of the sqlset
1110: -- sqlset_owner (IN) - the owner of the sqlset, or null for current schema
1111: -- owner
1112: -----------------------------------------------------------------------------
1113: PROCEDURE create_sqlset(
1114: sqlset_name IN VARCHAR2,
1115: description IN VARCHAR2 := NULL,
1116: sqlset_owner IN VARCHAR2 := NULL);
1117:
1110: -- sqlset_owner (IN) - the owner of the sqlset, or null for current schema
1111: -- owner
1112: -----------------------------------------------------------------------------
1113: PROCEDURE create_sqlset(
1114: sqlset_name IN VARCHAR2,
1115: description IN VARCHAR2 := NULL,
1116: sqlset_owner IN VARCHAR2 := NULL);
1117:
1118: ---------------------------------- create_sqlset ----------------------------
1112: -----------------------------------------------------------------------------
1113: PROCEDURE create_sqlset(
1114: sqlset_name IN VARCHAR2,
1115: description IN VARCHAR2 := NULL,
1116: sqlset_owner IN VARCHAR2 := NULL);
1117:
1118: ---------------------------------- create_sqlset ----------------------------
1119: -- NAME:
1120: -- create_sqlset
1114: sqlset_name IN VARCHAR2,
1115: description IN VARCHAR2 := NULL,
1116: sqlset_owner IN VARCHAR2 := NULL);
1117:
1118: ---------------------------------- create_sqlset ----------------------------
1119: -- NAME:
1120: -- create_sqlset
1121: --
1122: -- DESCRIPTION:
1116: sqlset_owner IN VARCHAR2 := NULL);
1117:
1118: ---------------------------------- create_sqlset ----------------------------
1119: -- NAME:
1120: -- create_sqlset
1121: --
1122: -- DESCRIPTION:
1123: -- This procedure creates a sqlset object in the database.
1124: --
1119: -- NAME:
1120: -- create_sqlset
1121: --
1122: -- DESCRIPTION:
1123: -- This procedure creates a sqlset object in the database.
1124: --
1125: -- PARAMETERS:
1126: -- sqlset_name (IN) - the sqlset name, can be NULL or omitted
1127: -- (in which case a name is generated automatically)
1122: -- DESCRIPTION:
1123: -- This procedure creates a sqlset object in the database.
1124: --
1125: -- PARAMETERS:
1126: -- sqlset_name (IN) - the sqlset name, can be NULL or omitted
1127: -- (in which case a name is generated automatically)
1128: -- description (IN) - the description of the sqlset
1129: -- sqlset_owner (IN) - the owner of the sqlset, or null for current schema
1130: -- owner
1124: --
1125: -- PARAMETERS:
1126: -- sqlset_name (IN) - the sqlset name, can be NULL or omitted
1127: -- (in which case a name is generated automatically)
1128: -- description (IN) - the description of the sqlset
1129: -- sqlset_owner (IN) - the owner of the sqlset, or null for current schema
1130: -- owner
1131: --
1132: -- RETURNS:
1125: -- PARAMETERS:
1126: -- sqlset_name (IN) - the sqlset name, can be NULL or omitted
1127: -- (in which case a name is generated automatically)
1128: -- description (IN) - the description of the sqlset
1129: -- sqlset_owner (IN) - the owner of the sqlset, or null for current schema
1130: -- owner
1131: --
1132: -- RETURNS:
1133: -- name of sqlset created. This will be the name passed in or, if a name
1129: -- sqlset_owner (IN) - the owner of the sqlset, or null for current schema
1130: -- owner
1131: --
1132: -- RETURNS:
1133: -- name of sqlset created. This will be the name passed in or, if a name
1134: -- is omitted (or NULL arg passed), the name we automatically create for
1135: -- the sqlset
1136: -----------------------------------------------------------------------------
1137: FUNCTION create_sqlset(
1131: --
1132: -- RETURNS:
1133: -- name of sqlset created. This will be the name passed in or, if a name
1134: -- is omitted (or NULL arg passed), the name we automatically create for
1135: -- the sqlset
1136: -----------------------------------------------------------------------------
1137: FUNCTION create_sqlset(
1138: sqlset_name IN VARCHAR2 := NULL,
1139: description IN VARCHAR2 := NULL,
1133: -- name of sqlset created. This will be the name passed in or, if a name
1134: -- is omitted (or NULL arg passed), the name we automatically create for
1135: -- the sqlset
1136: -----------------------------------------------------------------------------
1137: FUNCTION create_sqlset(
1138: sqlset_name IN VARCHAR2 := NULL,
1139: description IN VARCHAR2 := NULL,
1140: sqlset_owner IN VARCHAR2 := NULL)
1141: RETURN VARCHAR2;
1134: -- is omitted (or NULL arg passed), the name we automatically create for
1135: -- the sqlset
1136: -----------------------------------------------------------------------------
1137: FUNCTION create_sqlset(
1138: sqlset_name IN VARCHAR2 := NULL,
1139: description IN VARCHAR2 := NULL,
1140: sqlset_owner IN VARCHAR2 := NULL)
1141: RETURN VARCHAR2;
1142:
1136: -----------------------------------------------------------------------------
1137: FUNCTION create_sqlset(
1138: sqlset_name IN VARCHAR2 := NULL,
1139: description IN VARCHAR2 := NULL,
1140: sqlset_owner IN VARCHAR2 := NULL)
1141: RETURN VARCHAR2;
1142:
1143: ----------------------------------- drop_sqlset -----------------------------
1144: -- NAME:
1139: description IN VARCHAR2 := NULL,
1140: sqlset_owner IN VARCHAR2 := NULL)
1141: RETURN VARCHAR2;
1142:
1143: ----------------------------------- drop_sqlset -----------------------------
1144: -- NAME:
1145: -- drop_sqlset
1146: --
1147: -- DESCRIPTION:
1141: RETURN VARCHAR2;
1142:
1143: ----------------------------------- drop_sqlset -----------------------------
1144: -- NAME:
1145: -- drop_sqlset
1146: --
1147: -- DESCRIPTION:
1148: -- This procedure is used to drop a sqlset if it is not active.
1149: -- When a sqlset is referenced by one or more clients
1144: -- NAME:
1145: -- drop_sqlset
1146: --
1147: -- DESCRIPTION:
1148: -- This procedure is used to drop a sqlset if it is not active.
1149: -- When a sqlset is referenced by one or more clients
1150: -- (e.g. SQL tune advisor), it cannot be dropped.
1151: --
1152: -- PARAMETERS:
1145: -- drop_sqlset
1146: --
1147: -- DESCRIPTION:
1148: -- This procedure is used to drop a sqlset if it is not active.
1149: -- When a sqlset is referenced by one or more clients
1150: -- (e.g. SQL tune advisor), it cannot be dropped.
1151: --
1152: -- PARAMETERS:
1153: -- sqlset_name (IN) - the sqlset name.
1149: -- When a sqlset is referenced by one or more clients
1150: -- (e.g. SQL tune advisor), it cannot be dropped.
1151: --
1152: -- PARAMETERS:
1153: -- sqlset_name (IN) - the sqlset name.
1154: -- sqlset_owner (IN) - the owner of the sqlset, or null for current
1155: -- schema owner
1156: -----------------------------------------------------------------------------
1157: PROCEDURE drop_sqlset(
1150: -- (e.g. SQL tune advisor), it cannot be dropped.
1151: --
1152: -- PARAMETERS:
1153: -- sqlset_name (IN) - the sqlset name.
1154: -- sqlset_owner (IN) - the owner of the sqlset, or null for current
1155: -- schema owner
1156: -----------------------------------------------------------------------------
1157: PROCEDURE drop_sqlset(
1158: sqlset_name IN VARCHAR2,
1153: -- sqlset_name (IN) - the sqlset name.
1154: -- sqlset_owner (IN) - the owner of the sqlset, or null for current
1155: -- schema owner
1156: -----------------------------------------------------------------------------
1157: PROCEDURE drop_sqlset(
1158: sqlset_name IN VARCHAR2,
1159: sqlset_owner IN VARCHAR2 := NULL);
1160:
1161: -------------------------------- delete_sqlset ------------------------------
1154: -- sqlset_owner (IN) - the owner of the sqlset, or null for current
1155: -- schema owner
1156: -----------------------------------------------------------------------------
1157: PROCEDURE drop_sqlset(
1158: sqlset_name IN VARCHAR2,
1159: sqlset_owner IN VARCHAR2 := NULL);
1160:
1161: -------------------------------- delete_sqlset ------------------------------
1162: -- NAME:
1155: -- schema owner
1156: -----------------------------------------------------------------------------
1157: PROCEDURE drop_sqlset(
1158: sqlset_name IN VARCHAR2,
1159: sqlset_owner IN VARCHAR2 := NULL);
1160:
1161: -------------------------------- delete_sqlset ------------------------------
1162: -- NAME:
1163: -- delete_sqlset
1157: PROCEDURE drop_sqlset(
1158: sqlset_name IN VARCHAR2,
1159: sqlset_owner IN VARCHAR2 := NULL);
1160:
1161: -------------------------------- delete_sqlset ------------------------------
1162: -- NAME:
1163: -- delete_sqlset
1164: --
1165: -- DESCRIPTION:
1159: sqlset_owner IN VARCHAR2 := NULL);
1160:
1161: -------------------------------- delete_sqlset ------------------------------
1162: -- NAME:
1163: -- delete_sqlset
1164: --
1165: -- DESCRIPTION:
1166: -- Allows the deletion of a set of SQL statements from a sqlset.
1167: --
1162: -- NAME:
1163: -- delete_sqlset
1164: --
1165: -- DESCRIPTION:
1166: -- Allows the deletion of a set of SQL statements from a sqlset.
1167: --
1168: -- PARAMETERS:
1169: -- sqlset_name (IN) - the sqlset name
1170: -- basic_filter (IN) - SQL predicate to filter the SQL from the
1165: -- DESCRIPTION:
1166: -- Allows the deletion of a set of SQL statements from a sqlset.
1167: --
1168: -- PARAMETERS:
1169: -- sqlset_name (IN) - the sqlset name
1170: -- basic_filter (IN) - SQL predicate to filter the SQL from the
1171: -- sqlset. This basic filter is used as
1172: -- a where clause on the sqlset content to
1173: -- select a desired subset of Sql from the Tuning Set
1167: --
1168: -- PARAMETERS:
1169: -- sqlset_name (IN) - the sqlset name
1170: -- basic_filter (IN) - SQL predicate to filter the SQL from the
1171: -- sqlset. This basic filter is used as
1172: -- a where clause on the sqlset content to
1173: -- select a desired subset of Sql from the Tuning Set
1174: -- sqlset_owner (IN) - the owner of the sqlset, or null for current
1175: -- schema owner
1168: -- PARAMETERS:
1169: -- sqlset_name (IN) - the sqlset name
1170: -- basic_filter (IN) - SQL predicate to filter the SQL from the
1171: -- sqlset. This basic filter is used as
1172: -- a where clause on the sqlset content to
1173: -- select a desired subset of Sql from the Tuning Set
1174: -- sqlset_owner (IN) - the owner of the sqlset, or null for current
1175: -- schema owner
1176: -----------------------------------------------------------------------------
1170: -- basic_filter (IN) - SQL predicate to filter the SQL from the
1171: -- sqlset. This basic filter is used as
1172: -- a where clause on the sqlset content to
1173: -- select a desired subset of Sql from the Tuning Set
1174: -- sqlset_owner (IN) - the owner of the sqlset, or null for current
1175: -- schema owner
1176: -----------------------------------------------------------------------------
1177: PROCEDURE delete_sqlset(
1178: sqlset_name IN VARCHAR2,
1173: -- select a desired subset of Sql from the Tuning Set
1174: -- sqlset_owner (IN) - the owner of the sqlset, or null for current
1175: -- schema owner
1176: -----------------------------------------------------------------------------
1177: PROCEDURE delete_sqlset(
1178: sqlset_name IN VARCHAR2,
1179: basic_filter IN VARCHAR2 := NULL,
1180: sqlset_owner IN VARCHAR2 := NULL);
1181:
1174: -- sqlset_owner (IN) - the owner of the sqlset, or null for current
1175: -- schema owner
1176: -----------------------------------------------------------------------------
1177: PROCEDURE delete_sqlset(
1178: sqlset_name IN VARCHAR2,
1179: basic_filter IN VARCHAR2 := NULL,
1180: sqlset_owner IN VARCHAR2 := NULL);
1181:
1182: ---------------------------------- load_sqlset ------------------------------
1176: -----------------------------------------------------------------------------
1177: PROCEDURE delete_sqlset(
1178: sqlset_name IN VARCHAR2,
1179: basic_filter IN VARCHAR2 := NULL,
1180: sqlset_owner IN VARCHAR2 := NULL);
1181:
1182: ---------------------------------- load_sqlset ------------------------------
1183: -- NAME:
1184: -- load_sqlset
1178: sqlset_name IN VARCHAR2,
1179: basic_filter IN VARCHAR2 := NULL,
1180: sqlset_owner IN VARCHAR2 := NULL);
1181:
1182: ---------------------------------- load_sqlset ------------------------------
1183: -- NAME:
1184: -- load_sqlset
1185: --
1186: -- DESCRIPTION:
1180: sqlset_owner IN VARCHAR2 := NULL);
1181:
1182: ---------------------------------- load_sqlset ------------------------------
1183: -- NAME:
1184: -- load_sqlset
1185: --
1186: -- DESCRIPTION:
1187: -- This procedure populates the sqlset with a set of selected SQL.
1188: --
1183: -- NAME:
1184: -- load_sqlset
1185: --
1186: -- DESCRIPTION:
1187: -- This procedure populates the sqlset with a set of selected SQL.
1188: --
1189: -- PARAMETERS:
1190: -- sqlset_name (IN) - the name of sqlset to populate
1191: -- populate_cursor (IN) - the cursor reference to populate from
1186: -- DESCRIPTION:
1187: -- This procedure populates the sqlset with a set of selected SQL.
1188: --
1189: -- PARAMETERS:
1190: -- sqlset_name (IN) - the name of sqlset to populate
1191: -- populate_cursor (IN) - the cursor reference to populate from
1192: -- load_option (IN) - specifies how the statements will be loaded
1193: -- into the SQL tuning set.
1194: -- The possible values are:
1253: -- commit_rows (IN) - if a value is provided, the load will commit
1254: -- after each set of that many statements is
1255: -- inserted. If NULL is provided, the load will
1256: -- commit only once, at the end of the operation.
1257: -- sqlset_owner (IN) - the owner of the sqlset or null for current
1258: -- schema owner.
1259: -- Exceptions:
1260: -- This procedure returns an error when sqlset_name is invalid
1261: -- or a corresponding sqlset does not exist, the populate_cursor
1256: -- commit only once, at the end of the operation.
1257: -- sqlset_owner (IN) - the owner of the sqlset or null for current
1258: -- schema owner.
1259: -- Exceptions:
1260: -- This procedure returns an error when sqlset_name is invalid
1261: -- or a corresponding sqlset does not exist, the populate_cursor
1262: -- is incorrect and cannot be executed.
1263: -- FIXME: other exceptions are raised by this procedure. Need to update
1264: -- comments.
1257: -- sqlset_owner (IN) - the owner of the sqlset or null for current
1258: -- schema owner.
1259: -- Exceptions:
1260: -- This procedure returns an error when sqlset_name is invalid
1261: -- or a corresponding sqlset does not exist, the populate_cursor
1262: -- is incorrect and cannot be executed.
1263: -- FIXME: other exceptions are raised by this procedure. Need to update
1264: -- comments.
1265: -----------------------------------------------------------------------------
1262: -- is incorrect and cannot be executed.
1263: -- FIXME: other exceptions are raised by this procedure. Need to update
1264: -- comments.
1265: -----------------------------------------------------------------------------
1266: PROCEDURE load_sqlset(
1267: sqlset_name IN VARCHAR2,
1268: populate_cursor IN sqlset_cursor,
1269: load_option IN VARCHAR2 := 'INSERT',
1270: update_option IN VARCHAR2 := 'REPLACE',
1263: -- FIXME: other exceptions are raised by this procedure. Need to update
1264: -- comments.
1265: -----------------------------------------------------------------------------
1266: PROCEDURE load_sqlset(
1267: sqlset_name IN VARCHAR2,
1268: populate_cursor IN sqlset_cursor,
1269: load_option IN VARCHAR2 := 'INSERT',
1270: update_option IN VARCHAR2 := 'REPLACE',
1271: update_condition IN VARCHAR2 := NULL,
1264: -- comments.
1265: -----------------------------------------------------------------------------
1266: PROCEDURE load_sqlset(
1267: sqlset_name IN VARCHAR2,
1268: populate_cursor IN sqlset_cursor,
1269: load_option IN VARCHAR2 := 'INSERT',
1270: update_option IN VARCHAR2 := 'REPLACE',
1271: update_condition IN VARCHAR2 := NULL,
1272: update_attributes IN VARCHAR2 := NULL,
1271: update_condition IN VARCHAR2 := NULL,
1272: update_attributes IN VARCHAR2 := NULL,
1273: ignore_null IN BOOLEAN := TRUE,
1274: commit_rows IN POSITIVE := NULL,
1275: sqlset_owner IN VARCHAR2 := NULL);
1276:
1277: ---------------------------- capture_cursor_cache_sqlset --------------------
1278: -- NAME:
1279: -- capture_cursor_cache_sqlset
1273: ignore_null IN BOOLEAN := TRUE,
1274: commit_rows IN POSITIVE := NULL,
1275: sqlset_owner IN VARCHAR2 := NULL);
1276:
1277: ---------------------------- capture_cursor_cache_sqlset --------------------
1278: -- NAME:
1279: -- capture_cursor_cache_sqlset
1280: --
1281: -- DESCRIPTION:
1275: sqlset_owner IN VARCHAR2 := NULL);
1276:
1277: ---------------------------- capture_cursor_cache_sqlset --------------------
1278: -- NAME:
1279: -- capture_cursor_cache_sqlset
1280: --
1281: -- DESCRIPTION:
1282: -- This procedure captures a workload from the cursor cache into a SQL
1283: -- tuning set, polling the cache multiple times over a time period and
1284: -- updating the workload data stored there. It can execute over as long
1285: -- a period as required to capture an entire system workload.
1286: --
1287: -- Note that this procedure commits after each incremental capture of
1288: -- statements, so you can monitor its progress by looking at the sqlset
1289: -- views. This operation is much more efficient than
1290: -- select_cursor_cache/load_sqlset so it should be used whenever you need
1291: -- to repeatedly capture a workload from the cursor cache.
1292: --
1286: --
1287: -- Note that this procedure commits after each incremental capture of
1288: -- statements, so you can monitor its progress by looking at the sqlset
1289: -- views. This operation is much more efficient than
1290: -- select_cursor_cache/load_sqlset so it should be used whenever you need
1291: -- to repeatedly capture a workload from the cursor cache.
1292: --
1293: -- ** ALSO NOTE ** This function does not capture the SQL present
1294: -- in the cursor cache when it is invoked, but rather it collects those
1294: -- in the cursor cache when it is invoked, but rather it collects those
1295: -- SQL run over the 'time_limit' period in which it is executing.
1296: --
1297: -- PARAMETERS:
1298: -- sqlset_name (IN)- the SQLSET name
1299: -- time_limit (IN)- the total amount of time, in seconds, to execute
1300: -- repeat_interval (IN)- the amount of time, in seconds, to pause
1301: -- between sampling
1302: -- capture_option (IN)- during capture, either insert new statements,
1301: -- between sampling
1302: -- capture_option (IN)- during capture, either insert new statements,
1303: -- update existing ones, or both. 'INSERT',
1304: -- 'UPDATE', or 'MERGE' just like load_option in
1305: -- load_sqlset
1306: -- capture_mode (IN)- capture mode (UPDATE and MERGE capture options).
1307: -- Possible values:
1308: -- + MODE_REPLACE_OLD_STATS - Replace statistics
1309: -- when the number of executions seen is greater
1315: -- statistics will be the sum of the statistics
1316: -- of all cursors that statement existed under.
1317: -- basic_filter (IN)- filter to apply to cursor cache on each sampling
1318: -- (see select_xxx)
1319: -- sqlset_owner (IN)- the owner of the sqlset, or null for current
1320: -- schema owner
1321: -- recursive_sql (IN) - filter out the recursive SQL if NO_RECURSIVE_SQL
1322: -----------------------------------------------------------------------------
1323: PROCEDURE capture_cursor_cache_sqlset(
1319: -- sqlset_owner (IN)- the owner of the sqlset, or null for current
1320: -- schema owner
1321: -- recursive_sql (IN) - filter out the recursive SQL if NO_RECURSIVE_SQL
1322: -----------------------------------------------------------------------------
1323: PROCEDURE capture_cursor_cache_sqlset(
1324: sqlset_name IN VARCHAR2,
1325: time_limit IN POSITIVE := 1800,
1326: repeat_interval IN POSITIVE := 300,
1327: capture_option IN VARCHAR2 := 'MERGE',
1320: -- schema owner
1321: -- recursive_sql (IN) - filter out the recursive SQL if NO_RECURSIVE_SQL
1322: -----------------------------------------------------------------------------
1323: PROCEDURE capture_cursor_cache_sqlset(
1324: sqlset_name IN VARCHAR2,
1325: time_limit IN POSITIVE := 1800,
1326: repeat_interval IN POSITIVE := 300,
1327: capture_option IN VARCHAR2 := 'MERGE',
1328: capture_mode IN NUMBER := MODE_REPLACE_OLD_STATS,
1326: repeat_interval IN POSITIVE := 300,
1327: capture_option IN VARCHAR2 := 'MERGE',
1328: capture_mode IN NUMBER := MODE_REPLACE_OLD_STATS,
1329: basic_filter IN VARCHAR2 := NULL,
1330: sqlset_owner IN VARCHAR2 := NULL,
1331: recursive_sql IN VARCHAR2 := HAS_RECURSIVE_SQL);
1332:
1333: ----------------------------------- update_sqlset ---------------------------
1334: -- NAME:
1329: basic_filter IN VARCHAR2 := NULL,
1330: sqlset_owner IN VARCHAR2 := NULL,
1331: recursive_sql IN VARCHAR2 := HAS_RECURSIVE_SQL);
1332:
1333: ----------------------------------- update_sqlset ---------------------------
1334: -- NAME:
1335: -- update_sqlset
1336: --
1337: -- DESCRIPTION:
1331: recursive_sql IN VARCHAR2 := HAS_RECURSIVE_SQL);
1332:
1333: ----------------------------------- update_sqlset ---------------------------
1334: -- NAME:
1335: -- update_sqlset
1336: --
1337: -- DESCRIPTION:
1338: -- This procedure updates selected string fields for a SQL statement
1339: -- in a sqlset.
1335: -- update_sqlset
1336: --
1337: -- DESCRIPTION:
1338: -- This procedure updates selected string fields for a SQL statement
1339: -- in a sqlset.
1340: -- Fields that could be updated are MODULE, ACTION, PARSING_SCHEMA_NAME
1341: -- and OTHER.
1342: --
1343: -- PARAMETERS:
1340: -- Fields that could be updated are MODULE, ACTION, PARSING_SCHEMA_NAME
1341: -- and OTHER.
1342: --
1343: -- PARAMETERS:
1344: -- sqlset_name (IN) - the SQLSET name
1345: -- sql_id (IN) - identifier of the statement to update
1346: -- attribute_name (IN) - the name of the attribute to modify.
1347: -- attribute_value (IN) - the new value of the attribute
1348: -- sqlset_owner (IN) - the owner of the sqlset, or null for current
1344: -- sqlset_name (IN) - the SQLSET name
1345: -- sql_id (IN) - identifier of the statement to update
1346: -- attribute_name (IN) - the name of the attribute to modify.
1347: -- attribute_value (IN) - the new value of the attribute
1348: -- sqlset_owner (IN) - the owner of the sqlset, or null for current
1349: -- schema owner
1350: -----------------------------------------------------------------------------
1351: PROCEDURE update_sqlset(
1352: sqlset_name IN VARCHAR2,
1347: -- attribute_value (IN) - the new value of the attribute
1348: -- sqlset_owner (IN) - the owner of the sqlset, or null for current
1349: -- schema owner
1350: -----------------------------------------------------------------------------
1351: PROCEDURE update_sqlset(
1352: sqlset_name IN VARCHAR2,
1353: sql_id IN VARCHAR2,
1354: attribute_name IN VARCHAR2,
1355: attribute_value IN VARCHAR2 := NULL,
1348: -- sqlset_owner (IN) - the owner of the sqlset, or null for current
1349: -- schema owner
1350: -----------------------------------------------------------------------------
1351: PROCEDURE update_sqlset(
1352: sqlset_name IN VARCHAR2,
1353: sql_id IN VARCHAR2,
1354: attribute_name IN VARCHAR2,
1355: attribute_value IN VARCHAR2 := NULL,
1356: sqlset_owner IN VARCHAR2 := NULL);
1352: sqlset_name IN VARCHAR2,
1353: sql_id IN VARCHAR2,
1354: attribute_name IN VARCHAR2,
1355: attribute_value IN VARCHAR2 := NULL,
1356: sqlset_owner IN VARCHAR2 := NULL);
1357:
1358: ----------------------------------- update_sqlset ---------------------------
1359: PROCEDURE update_sqlset(
1360: sqlset_name IN VARCHAR2,
1354: attribute_name IN VARCHAR2,
1355: attribute_value IN VARCHAR2 := NULL,
1356: sqlset_owner IN VARCHAR2 := NULL);
1357:
1358: ----------------------------------- update_sqlset ---------------------------
1359: PROCEDURE update_sqlset(
1360: sqlset_name IN VARCHAR2,
1361: sql_id IN VARCHAR2,
1362: plan_hash_value IN NUMBER,
1355: attribute_value IN VARCHAR2 := NULL,
1356: sqlset_owner IN VARCHAR2 := NULL);
1357:
1358: ----------------------------------- update_sqlset ---------------------------
1359: PROCEDURE update_sqlset(
1360: sqlset_name IN VARCHAR2,
1361: sql_id IN VARCHAR2,
1362: plan_hash_value IN NUMBER,
1363: attribute_name IN VARCHAR2,
1356: sqlset_owner IN VARCHAR2 := NULL);
1357:
1358: ----------------------------------- update_sqlset ---------------------------
1359: PROCEDURE update_sqlset(
1360: sqlset_name IN VARCHAR2,
1361: sql_id IN VARCHAR2,
1362: plan_hash_value IN NUMBER,
1363: attribute_name IN VARCHAR2,
1364: attribute_value IN VARCHAR2 := NULL,
1361: sql_id IN VARCHAR2,
1362: plan_hash_value IN NUMBER,
1363: attribute_name IN VARCHAR2,
1364: attribute_value IN VARCHAR2 := NULL,
1365: sqlset_owner IN VARCHAR2 := NULL);
1366:
1367: ----------------------------------- update_sqlset ---------------------------
1368: -- NAME:
1369: -- update_sqlset
1363: attribute_name IN VARCHAR2,
1364: attribute_value IN VARCHAR2 := NULL,
1365: sqlset_owner IN VARCHAR2 := NULL);
1366:
1367: ----------------------------------- update_sqlset ---------------------------
1368: -- NAME:
1369: -- update_sqlset
1370: --
1371: -- DESCRIPTION:
1365: sqlset_owner IN VARCHAR2 := NULL);
1366:
1367: ----------------------------------- update_sqlset ---------------------------
1368: -- NAME:
1369: -- update_sqlset
1370: --
1371: -- DESCRIPTION:
1372: -- This is an overloaded procedure of the previous one. It is provided
1373: -- to be able to set numerical attributes of a SQL in a sqlset.
1369: -- update_sqlset
1370: --
1371: -- DESCRIPTION:
1372: -- This is an overloaded procedure of the previous one. It is provided
1373: -- to be able to set numerical attributes of a SQL in a sqlset.
1374: -- The only NUMBER attribute that could be updated is PRIORITY.
1375: -- If the statement has more than one plan (i.e., multiple plans with an
1376: -- entry for every different plan_hash_value in plan table),
1377: -- the attribute value will be then changed (replaced) for all plan
1380: -- version of this procedure that, besides sql_id, it takes
1381: -- a plan_hash_value as an argument.
1382: --
1383: -- PARAMETERS:
1384: -- sqlset_name (IN) - the sqlset name
1385: -- sql_id (IN) - identifier of the statement to update
1386: -- plan_hash_value (IN) - plan hash value of a particular plan of
1387: -- the SQL
1388: -- attribute_name (IN) - the name of the attribute to modify.
1386: -- plan_hash_value (IN) - plan hash value of a particular plan of
1387: -- the SQL
1388: -- attribute_name (IN) - the name of the attribute to modify.
1389: -- attribute_value (IN) - the new value of the attribute
1390: -- sqlset_owner (IN) - the owner of the sqlset, or null for current
1391: -- schema owner
1392: -----------------------------------------------------------------------------
1393: PROCEDURE update_sqlset(
1394: sqlset_name IN VARCHAR2,
1389: -- attribute_value (IN) - the new value of the attribute
1390: -- sqlset_owner (IN) - the owner of the sqlset, or null for current
1391: -- schema owner
1392: -----------------------------------------------------------------------------
1393: PROCEDURE update_sqlset(
1394: sqlset_name IN VARCHAR2,
1395: sql_id IN VARCHAR2,
1396: attribute_name IN VARCHAR2,
1397: attribute_value IN NUMBER := NULL,
1390: -- sqlset_owner (IN) - the owner of the sqlset, or null for current
1391: -- schema owner
1392: -----------------------------------------------------------------------------
1393: PROCEDURE update_sqlset(
1394: sqlset_name IN VARCHAR2,
1395: sql_id IN VARCHAR2,
1396: attribute_name IN VARCHAR2,
1397: attribute_value IN NUMBER := NULL,
1398: sqlset_owner IN VARCHAR2 := NULL);
1394: sqlset_name IN VARCHAR2,
1395: sql_id IN VARCHAR2,
1396: attribute_name IN VARCHAR2,
1397: attribute_value IN NUMBER := NULL,
1398: sqlset_owner IN VARCHAR2 := NULL);
1399:
1400: ----------------------------------- update_sqlset ---------------------------
1401: PROCEDURE update_sqlset(
1402: sqlset_name IN VARCHAR2,
1396: attribute_name IN VARCHAR2,
1397: attribute_value IN NUMBER := NULL,
1398: sqlset_owner IN VARCHAR2 := NULL);
1399:
1400: ----------------------------------- update_sqlset ---------------------------
1401: PROCEDURE update_sqlset(
1402: sqlset_name IN VARCHAR2,
1403: sql_id IN VARCHAR2,
1404: plan_hash_value IN NUMBER,
1397: attribute_value IN NUMBER := NULL,
1398: sqlset_owner IN VARCHAR2 := NULL);
1399:
1400: ----------------------------------- update_sqlset ---------------------------
1401: PROCEDURE update_sqlset(
1402: sqlset_name IN VARCHAR2,
1403: sql_id IN VARCHAR2,
1404: plan_hash_value IN NUMBER,
1405: attribute_name IN VARCHAR2,
1398: sqlset_owner IN VARCHAR2 := NULL);
1399:
1400: ----------------------------------- update_sqlset ---------------------------
1401: PROCEDURE update_sqlset(
1402: sqlset_name IN VARCHAR2,
1403: sql_id IN VARCHAR2,
1404: plan_hash_value IN NUMBER,
1405: attribute_name IN VARCHAR2,
1406: attribute_value IN NUMBER := NULL,
1403: sql_id IN VARCHAR2,
1404: plan_hash_value IN NUMBER,
1405: attribute_name IN VARCHAR2,
1406: attribute_value IN NUMBER := NULL,
1407: sqlset_owner IN VARCHAR2 := NULL);
1408:
1409: ------------------------------ add_sqlset_reference -------------------------
1410: -- NAME:
1411: -- add_sqlset_reference
1405: attribute_name IN VARCHAR2,
1406: attribute_value IN NUMBER := NULL,
1407: sqlset_owner IN VARCHAR2 := NULL);
1408:
1409: ------------------------------ add_sqlset_reference -------------------------
1410: -- NAME:
1411: -- add_sqlset_reference
1412: --
1413: -- DESCRIPTION:
1407: sqlset_owner IN VARCHAR2 := NULL);
1408:
1409: ------------------------------ add_sqlset_reference -------------------------
1410: -- NAME:
1411: -- add_sqlset_reference
1412: --
1413: -- DESCRIPTION:
1414: -- This function adds a new reference to an existing sqlset
1415: -- to indicate its use by a client.
1410: -- NAME:
1411: -- add_sqlset_reference
1412: --
1413: -- DESCRIPTION:
1414: -- This function adds a new reference to an existing sqlset
1415: -- to indicate its use by a client.
1416: --
1417: -- PARAMETERS:
1418: -- sqlset_name (IN) - the sqlset name.
1414: -- This function adds a new reference to an existing sqlset
1415: -- to indicate its use by a client.
1416: --
1417: -- PARAMETERS:
1418: -- sqlset_name (IN) - the sqlset name.
1419: -- description (IN) - description of the usage of sqlset.
1420: -- sqlset_owner (IN) - the owner of the sqlset, or null for current schema
1421: -- owner
1422: --
1415: -- to indicate its use by a client.
1416: --
1417: -- PARAMETERS:
1418: -- sqlset_name (IN) - the sqlset name.
1419: -- description (IN) - description of the usage of sqlset.
1420: -- sqlset_owner (IN) - the owner of the sqlset, or null for current schema
1421: -- owner
1422: --
1423: -- RETURN:
1416: --
1417: -- PARAMETERS:
1418: -- sqlset_name (IN) - the sqlset name.
1419: -- description (IN) - description of the usage of sqlset.
1420: -- sqlset_owner (IN) - the owner of the sqlset, or null for current schema
1421: -- owner
1422: --
1423: -- RETURN:
1424: -- The identifier of the added reference.
1422: --
1423: -- RETURN:
1424: -- The identifier of the added reference.
1425: -----------------------------------------------------------------------------
1426: FUNCTION add_sqlset_reference(
1427: sqlset_name IN VARCHAR2,
1428: description IN VARCHAR2 := NULL,
1429: sqlset_owner IN VARCHAR2 := NULL)
1430: RETURN NUMBER;
1423: -- RETURN:
1424: -- The identifier of the added reference.
1425: -----------------------------------------------------------------------------
1426: FUNCTION add_sqlset_reference(
1427: sqlset_name IN VARCHAR2,
1428: description IN VARCHAR2 := NULL,
1429: sqlset_owner IN VARCHAR2 := NULL)
1430: RETURN NUMBER;
1431:
1425: -----------------------------------------------------------------------------
1426: FUNCTION add_sqlset_reference(
1427: sqlset_name IN VARCHAR2,
1428: description IN VARCHAR2 := NULL,
1429: sqlset_owner IN VARCHAR2 := NULL)
1430: RETURN NUMBER;
1431:
1432: ------------------------------ remove_sqlset_reference ----------------------
1433: -- NAME:
1428: description IN VARCHAR2 := NULL,
1429: sqlset_owner IN VARCHAR2 := NULL)
1430: RETURN NUMBER;
1431:
1432: ------------------------------ remove_sqlset_reference ----------------------
1433: -- NAME:
1434: -- remove_sqlset_reference
1435: --
1436: -- DESCRIPTION:
1430: RETURN NUMBER;
1431:
1432: ------------------------------ remove_sqlset_reference ----------------------
1433: -- NAME:
1434: -- remove_sqlset_reference
1435: --
1436: -- DESCRIPTION:
1437: -- This procedure is used to deactivate a sqlset to indicate it
1438: -- is no longer used by the client.
1433: -- NAME:
1434: -- remove_sqlset_reference
1435: --
1436: -- DESCRIPTION:
1437: -- This procedure is used to deactivate a sqlset to indicate it
1438: -- is no longer used by the client.
1439: --
1440: -- PARAMETERS:
1441: -- name (IN) - the SQLSET name
1437: -- This procedure is used to deactivate a sqlset to indicate it
1438: -- is no longer used by the client.
1439: --
1440: -- PARAMETERS:
1441: -- name (IN) - the SQLSET name
1442: -- reference_id (IN) - the identifier of the reference to remove.
1443: -- sqlset_owner (IN) - the owner of the sqlset, or null for current
1444: -- schema owner
1445: -----------------------------------------------------------------------------
1439: --
1440: -- PARAMETERS:
1441: -- name (IN) - the SQLSET name
1442: -- reference_id (IN) - the identifier of the reference to remove.
1443: -- sqlset_owner (IN) - the owner of the sqlset, or null for current
1444: -- schema owner
1445: -----------------------------------------------------------------------------
1446: PROCEDURE remove_sqlset_reference(
1447: sqlset_name IN VARCHAR2,
1442: -- reference_id (IN) - the identifier of the reference to remove.
1443: -- sqlset_owner (IN) - the owner of the sqlset, or null for current
1444: -- schema owner
1445: -----------------------------------------------------------------------------
1446: PROCEDURE remove_sqlset_reference(
1447: sqlset_name IN VARCHAR2,
1448: reference_id IN NUMBER,
1449: sqlset_owner IN VARCHAR2 := NULL);
1450:
1443: -- sqlset_owner (IN) - the owner of the sqlset, or null for current
1444: -- schema owner
1445: -----------------------------------------------------------------------------
1446: PROCEDURE remove_sqlset_reference(
1447: sqlset_name IN VARCHAR2,
1448: reference_id IN NUMBER,
1449: sqlset_owner IN VARCHAR2 := NULL);
1450:
1451: ----------------------------------- select_sqlset ---------------------------
1445: -----------------------------------------------------------------------------
1446: PROCEDURE remove_sqlset_reference(
1447: sqlset_name IN VARCHAR2,
1448: reference_id IN NUMBER,
1449: sqlset_owner IN VARCHAR2 := NULL);
1450:
1451: ----------------------------------- select_sqlset ---------------------------
1452: -- NAME:
1453: -- select_sqlset
1447: sqlset_name IN VARCHAR2,
1448: reference_id IN NUMBER,
1449: sqlset_owner IN VARCHAR2 := NULL);
1450:
1451: ----------------------------------- select_sqlset ---------------------------
1452: -- NAME:
1453: -- select_sqlset
1454: --
1455: -- DESCRIPTION:
1449: sqlset_owner IN VARCHAR2 := NULL);
1450:
1451: ----------------------------------- select_sqlset ---------------------------
1452: -- NAME:
1453: -- select_sqlset
1454: --
1455: -- DESCRIPTION:
1456: -- This is a table function to read sql tuning set content.
1457: --
1455: -- DESCRIPTION:
1456: -- This is a table function to read sql tuning set content.
1457: --
1458: -- PARAMETERS:
1459: -- sqlset_name (IN) - sqlset name to select from
1460: -- basic_filter (IN) - SQL predicate to filter the SQL statements
1461: -- from the specified sqlset
1462: -- object_filter (IN) - objects that should exist in the object list
1463: -- of selected SQL. Currently not supported.
1457: --
1458: -- PARAMETERS:
1459: -- sqlset_name (IN) - sqlset name to select from
1460: -- basic_filter (IN) - SQL predicate to filter the SQL statements
1461: -- from the specified sqlset
1462: -- object_filter (IN) - objects that should exist in the object list
1463: -- of selected SQL. Currently not supported.
1464: -- ranking_measure(i) (IN) - an order-by clause on the selected SQL
1465: -- result_percentage (IN) - a percentage on the sum of a ranking measure
1502: -- + MAX_DISK_READS: plan with max disk reads
1503: -- + MAX_DIRECT_WRITES: plan with max direct
1504: -- writes
1505: -- + MAX_OPTIMIZER_COST: plan with max opt. cost
1506: -- sqlset_owner (IN) - the owner of the sqlset, or null for current
1507: -- schema owner
1508: -- recursive_sql (IN) - filter out the recursive SQL
1509: -- if NO_RECURSIVE_SQL
1510: -- RETURN:
1507: -- schema owner
1508: -- recursive_sql (IN) - filter out the recursive SQL
1509: -- if NO_RECURSIVE_SQL
1510: -- RETURN:
1511: -- This function returns a sqlset object.
1512: -----------------------------------------------------------------------------
1513: FUNCTION select_sqlset(
1514: sqlset_name IN VARCHAR2,
1515: basic_filter IN VARCHAR2 := NULL,
1509: -- if NO_RECURSIVE_SQL
1510: -- RETURN:
1511: -- This function returns a sqlset object.
1512: -----------------------------------------------------------------------------
1513: FUNCTION select_sqlset(
1514: sqlset_name IN VARCHAR2,
1515: basic_filter IN VARCHAR2 := NULL,
1516: object_filter IN VARCHAR2 := NULL,
1517: ranking_measure1 IN VARCHAR2 := NULL,
1510: -- RETURN:
1511: -- This function returns a sqlset object.
1512: -----------------------------------------------------------------------------
1513: FUNCTION select_sqlset(
1514: sqlset_name IN VARCHAR2,
1515: basic_filter IN VARCHAR2 := NULL,
1516: object_filter IN VARCHAR2 := NULL,
1517: ranking_measure1 IN VARCHAR2 := NULL,
1518: ranking_measure2 IN VARCHAR2 := NULL,
1520: result_percentage IN NUMBER := 1,
1521: result_limit IN NUMBER := NULL,
1522: attribute_list IN VARCHAR2 := 'TYPICAL',
1523: plan_filter IN VARCHAR2 := NULL,
1524: sqlset_owner IN VARCHAR2 := NULL,
1525: recursive_sql IN VARCHAR2 := HAS_RECURSIVE_SQL)
1526: RETURN sys.sqlset PIPELINED;
1527:
1528: ---------------------------- select_cursor_cache ----------------------------
1522: attribute_list IN VARCHAR2 := 'TYPICAL',
1523: plan_filter IN VARCHAR2 := NULL,
1524: sqlset_owner IN VARCHAR2 := NULL,
1525: recursive_sql IN VARCHAR2 := HAS_RECURSIVE_SQL)
1526: RETURN sys.sqlset PIPELINED;
1527:
1528: ---------------------------- select_cursor_cache ----------------------------
1529: -- NAME:
1530: -- select_cursor_cache
1566: --
1567: -- recursive_sql (IN) - filter out the recursive SQL
1568: -- if NO_RECURSIVE_SQL
1569: -- RETURN:
1570: -- This function returns a sqlset object.
1571: -----------------------------------------------------------------------------
1572: FUNCTION select_cursor_cache(
1573: basic_filter IN VARCHAR2 := NULL,
1574: object_filter IN VARCHAR2 := NULL,
1578: result_percentage IN NUMBER := 1,
1579: result_limit IN NUMBER := NULL,
1580: attribute_list IN VARCHAR2 := 'TYPICAL',
1581: recursive_sql IN VARCHAR2 := HAS_RECURSIVE_SQL)
1582: RETURN sys.sqlset PIPELINED;
1583:
1584: ------------------------- select_workload_repository ------------------------
1585: -- NAME:
1586: -- select_workload_repository
1625: -- to SQL_PLAN + row source statistics
1626: -- recursive_sql (IN) - filter out the recursive SQL
1627: -- if NO_RECURSIVE_SQL
1628: -- RETURN:
1629: -- This function returns a sqlset object.
1630: -----------------------------------------------------------------------------
1631: FUNCTION select_workload_repository(
1632: begin_snap IN NUMBER,
1633: end_snap IN NUMBER,
1639: result_percentage IN NUMBER := 1,
1640: result_limit IN NUMBER := NULL,
1641: attribute_list IN VARCHAR2 := 'TYPICAL',
1642: recursive_sql IN VARCHAR2 := HAS_RECURSIVE_SQL)
1643: RETURN sys.sqlset PIPELINED;
1644:
1645: -------------------------- select_workload_repository -----------------------
1646: FUNCTION select_workload_repository(
1647: baseline_name IN VARCHAR2,
1653: result_percentage IN NUMBER := 1,
1654: result_limit IN NUMBER := NULL,
1655: attribute_list IN VARCHAR2 := 'TYPICAL',
1656: recursive_sql IN VARCHAR2 := HAS_RECURSIVE_SQL)
1657: RETURN sys.sqlset PIPELINED;
1658:
1659: ------------------------------ select_sql_trace -----------------------------
1660: -- NAME:
1661: -- select_sql_trace
1662: --
1663: -- DESCRIPTION:
1664: -- This table function reads the content of one or more trace
1665: -- files and returns the sql statements it finds in the format
1666: -- of sqlset_row.
1667: --
1668: -- PARAMETERS:
1669: -- directory (IN) - directory/location/path of the trace file(s).
1670: -- This field is mandatory.
1699: -- result_limit (IN) - top SQL from the (filtered) source. Default
1700: -- to MAXSB4 if NULL;
1701: --
1702: -- return:
1703: -- This function returns a sqlset_row object.
1704: --
1705: ------------------------------------------------------------------------
1706: -- EXAMPLE: LOAD SQLs from SQL TRACE INTO STS and convert it into trial
1707: ------------------------------------------------------------------------
1723: -- select user_id id, username owner, null name
1724: -- from dba_users;
1725: --
1726: -- /* create the STS on the database running the SPA */
1727: -- dbms_sqltune.create_sqlset('my_sts', 'test purpose');
1728: --
1729: -- /* load the sqls into STS from SQL TRACE */
1730: -- DECLARE
1731: -- cur sys_refcursor;
1736: -- dbms_sqltune.select_sql_trace(
1737: -- directory=>'SQL_TRACE_DIR',
1738: -- file_name=>'%trc',
1739: -- mapping_table_name=>'mapping')) p;
1740: -- dbms_sqltune.load_sqlset('my_sts', cur);
1741: -- END;
1742: -- /
1743: --
1744: -- /* create a trial from the STS */
1743: --
1744: -- /* create a trial from the STS */
1745: -- var aname varchar2(30)
1746: -- exec :aname := dbms_sqlpa.create_analysis_task(
1747: -- sqlset_name => 'my_sts');
1748: -- exec dbms_sqlpa.execute_analysis_task(task_name =>:aname,
1749: -- execution_type => 'convert sqlset');
1750: -----------------------------------------------------------------------------
1751: FUNCTION select_sql_trace(
1745: -- var aname varchar2(30)
1746: -- exec :aname := dbms_sqlpa.create_analysis_task(
1747: -- sqlset_name => 'my_sts');
1748: -- exec dbms_sqlpa.execute_analysis_task(task_name =>:aname,
1749: -- execution_type => 'convert sqlset');
1750: -----------------------------------------------------------------------------
1751: FUNCTION select_sql_trace(
1752: directory IN VARCHAR2,
1753: file_name IN VARCHAR2 := NULL,
1757: options IN BINARY_INTEGER := LIMITED_COMMAND_TYPE,
1758: pattern_start IN VARCHAR2 := NULL,
1759: pattern_end IN VARCHAR2 := NULL,
1760: result_limit IN POSITIVE := NULL)
1761: RETURN sys.sqlset PIPELINED;
1762:
1763: ----------------------------- select_sqlpa_task -----------------------------
1764: -- NAME:
1765: -- select_sqlpa_task
1819: -- SQL_PLAN_STATISTICS: similar
1820: -- to SQL_PLAN + row source statistics
1821: --
1822: -- RETURN:
1823: -- This function returns a sqlset object.
1824: -----------------------------------------------------------------------------
1825: FUNCTION select_sqlpa_task(
1826: task_name IN VARCHAR2,
1827: task_owner IN VARCHAR2 := NULL,
1829: level_filter IN VARCHAR2 := 'REGRESSED',
1830: basic_filter IN VARCHAR2 := NULL,
1831: object_filter IN VARCHAR2 := NULL,
1832: attribute_list IN VARCHAR2 := 'TYPICAL')
1833: RETURN sys.sqlset PIPELINED;
1834:
1835: -----------------------------------------------------------------------------
1836: -- Pack / Unpack SQL tuning set procedures and functions --
1837: -- --
1845: ---------------------------------
1846: -- EXAMPLE: PACK/UNPACK TWO STS --
1847: ---------------------------------
1848: -- /* Create a staging table to move to */
1849: -- dbms_sqltune.create_stgtab_sqlset(table_name => 'STAGING_TABLE');
1850: --
1851: -- /* Put two STS in the staging table */
1852: -- dbms_sqltune.pack_stgtab_sqlset(sqlset_name => 'my_sts',
1853: -- staging_table_name => 'STAGING_TABLE');
1848: -- /* Create a staging table to move to */
1849: -- dbms_sqltune.create_stgtab_sqlset(table_name => 'STAGING_TABLE');
1850: --
1851: -- /* Put two STS in the staging table */
1852: -- dbms_sqltune.pack_stgtab_sqlset(sqlset_name => 'my_sts',
1853: -- staging_table_name => 'STAGING_TABLE');
1854: -- dbms_sqltune.pack_stgtab_sqlset(sqlset_name => 'full_app_workload',
1855: -- staging_table_name => 'STAGING_TABLE');
1856: --
1850: --
1851: -- /* Put two STS in the staging table */
1852: -- dbms_sqltune.pack_stgtab_sqlset(sqlset_name => 'my_sts',
1853: -- staging_table_name => 'STAGING_TABLE');
1854: -- dbms_sqltune.pack_stgtab_sqlset(sqlset_name => 'full_app_workload',
1855: -- staging_table_name => 'STAGING_TABLE');
1856: --
1857: -- /* transport STS_STAGING_TABLE to foreign system */
1858: -- ...
1857: -- /* transport STS_STAGING_TABLE to foreign system */
1858: -- ...
1859: --
1860: -- /* On new system, unpack both from staging table */
1861: -- dbms_sqltune.unpack_stgtab_sqlset(sqlset_name => '%',
1862: -- replace => TRUE,
1863: -- staging_table_name => 'STAGING_TABLE');
1864: --
1865: -----------------------------------------------------------------------------
1863: -- staging_table_name => 'STAGING_TABLE');
1864: --
1865: -----------------------------------------------------------------------------
1866:
1867: ------------------------------- create_stgtab_sqlset ------------------------
1868: -- NAME:
1869: -- create_stgtab_sqlset
1870: --
1871: -- DESCRIPTION:
1865: -----------------------------------------------------------------------------
1866:
1867: ------------------------------- create_stgtab_sqlset ------------------------
1868: -- NAME:
1869: -- create_stgtab_sqlset
1870: --
1871: -- DESCRIPTION:
1872: -- This procedure creates a staging table to be used by the pack
1873: -- procedure. Call it once before issuing a pack call. It can
1897: -- STS_STGTAB_10_2_VERSION : 10.2 DB version
1898: -- STS_STGTAB_11_1_VERSION : 11.1 DB version
1899: -- STS_STGTAB_11_2_VERSION : 11.2 DB version
1900: -----------------------------------------------------------------------------
1901: PROCEDURE create_stgtab_sqlset(
1902: table_name IN VARCHAR2,
1903: schema_name IN VARCHAR2 := NULL,
1904: tablespace_name IN VARCHAR2 := NULL,
1905: db_version IN NUMBER := NULL);
1903: schema_name IN VARCHAR2 := NULL,
1904: tablespace_name IN VARCHAR2 := NULL,
1905: db_version IN NUMBER := NULL);
1906:
1907: ----------------------------- pack_stgtab_sqlset ----------------------------
1908: -- NAME:
1909: -- pack_stgtab_sqlset
1910: --
1911: -- DESCRIPTION:
1905: db_version IN NUMBER := NULL);
1906:
1907: ----------------------------- pack_stgtab_sqlset ----------------------------
1908: -- NAME:
1909: -- pack_stgtab_sqlset
1910: --
1911: -- DESCRIPTION:
1912: -- This function moves one or more STS from their location in the SYS
1913: -- schema to a staging table created by the create_stgtab_sqlset fct.
1909: -- pack_stgtab_sqlset
1910: --
1911: -- DESCRIPTION:
1912: -- This function moves one or more STS from their location in the SYS
1913: -- schema to a staging table created by the create_stgtab_sqlset fct.
1914: -- It can be called several times to move more than one STS. Users can
1915: -- then move the populated staging table to another system using any
1916: -- method of their choice, such as database link or datapump (expdp/
1917: -- impdp functions). Users can then call unpack_stgtab_sqlset to create
1913: -- schema to a staging table created by the create_stgtab_sqlset fct.
1914: -- It can be called several times to move more than one STS. Users can
1915: -- then move the populated staging table to another system using any
1916: -- method of their choice, such as database link or datapump (expdp/
1917: -- impdp functions). Users can then call unpack_stgtab_sqlset to create
1918: -- the STS on the other system.
1919: --
1920: -- Note that this fct commits after packing each STS, so if it raises
1921: -- an error mid-execution, some STS may already be in the staging table.
1920: -- Note that this fct commits after packing each STS, so if it raises
1921: -- an error mid-execution, some STS may already be in the staging table.
1922: --
1923: -- PARAMETERS:
1924: -- sqlset_name (IN) - name of STS to pack (not NULL).
1925: -- Wildcard characters ('%') are supported
1926: -- to move multiple STS in a single call.
1927: -- sqlset_owner (IN) - name of STS owner, or NULL for current
1928: -- schema owner. Wildcard characters ('%')
1923: -- PARAMETERS:
1924: -- sqlset_name (IN) - name of STS to pack (not NULL).
1925: -- Wildcard characters ('%') are supported
1926: -- to move multiple STS in a single call.
1927: -- sqlset_owner (IN) - name of STS owner, or NULL for current
1928: -- schema owner. Wildcard characters ('%')
1929: -- are supported to pack STS from multiple
1930: -- owners in one call.
1931: -- staging_table_name (IN) - name of staging table, created by
1928: -- schema owner. Wildcard characters ('%')
1929: -- are supported to pack STS from multiple
1930: -- owners in one call.
1931: -- staging_table_name (IN) - name of staging table, created by
1932: -- create_stgtab_sqlset (case-sensitive)
1933: -- staging_schema_owner (IN) - name of staging table owner, or NULL for
1934: -- current schema owner (case-sensitive)
1935: -- db_version (IN) - database version to decide the format of
1936: -- the staging table. It is possible to
1943: -- STS_STGTAB_10_2_VERSION : 10.2 DB version
1944: -- STS_STGTAB_11_1_VERSION : 11.1 DB version
1945: -- STS_STGTAB_11_2_VERSION : 11.2 DB version
1946: -----------------------------------------------------------------------------
1947: PROCEDURE pack_stgtab_sqlset(
1948: sqlset_name IN VARCHAR2,
1949: sqlset_owner IN VARCHAR2 := NULL,
1950: staging_table_name IN VARCHAR2,
1951: staging_schema_owner IN VARCHAR2 := NULL,
1944: -- STS_STGTAB_11_1_VERSION : 11.1 DB version
1945: -- STS_STGTAB_11_2_VERSION : 11.2 DB version
1946: -----------------------------------------------------------------------------
1947: PROCEDURE pack_stgtab_sqlset(
1948: sqlset_name IN VARCHAR2,
1949: sqlset_owner IN VARCHAR2 := NULL,
1950: staging_table_name IN VARCHAR2,
1951: staging_schema_owner IN VARCHAR2 := NULL,
1952: db_version IN NUMBER := NULL);
1945: -- STS_STGTAB_11_2_VERSION : 11.2 DB version
1946: -----------------------------------------------------------------------------
1947: PROCEDURE pack_stgtab_sqlset(
1948: sqlset_name IN VARCHAR2,
1949: sqlset_owner IN VARCHAR2 := NULL,
1950: staging_table_name IN VARCHAR2,
1951: staging_schema_owner IN VARCHAR2 := NULL,
1952: db_version IN NUMBER := NULL);
1953:
1950: staging_table_name IN VARCHAR2,
1951: staging_schema_owner IN VARCHAR2 := NULL,
1952: db_version IN NUMBER := NULL);
1953:
1954: --------------------------- unpack_stgtab_sqlset ----------------------------
1955: -- NAME:
1956: -- unpack_stgtab_sqlset
1957: --
1958: -- DESCRIPTION:
1952: db_version IN NUMBER := NULL);
1953:
1954: --------------------------- unpack_stgtab_sqlset ----------------------------
1955: -- NAME:
1956: -- unpack_stgtab_sqlset
1957: --
1958: -- DESCRIPTION:
1959: -- Moves one or more STS from the staging table, as populated by a call
1960: -- to pack_stgtab_sqlset and moved by the user, into the STS schema,
1956: -- unpack_stgtab_sqlset
1957: --
1958: -- DESCRIPTION:
1959: -- Moves one or more STS from the staging table, as populated by a call
1960: -- to pack_stgtab_sqlset and moved by the user, into the STS schema,
1961: -- making them proper STS. Users can drop the staging table after this
1962: -- procedure completes successfully.
1963: --
1964: -- The unpack procedure commits after successfully loading each STS. If
1963: --
1964: -- The unpack procedure commits after successfully loading each STS. If
1965: -- it fails with one, no part of that STS will have been unpacked, but
1966: -- those which it saw previously will exist. When failures occur due to
1967: -- sts name or owner conflicts, users should use the remap_stgtab_sqlset
1968: -- function to patch the staging table, and then call this procedure
1969: -- again to unpack those STS that remain.
1970: --
1971: -- PARAMETERS:
1968: -- function to patch the staging table, and then call this procedure
1969: -- again to unpack those STS that remain.
1970: --
1971: -- PARAMETERS:
1972: -- sqlset_name (IN) - name of STS to unpack (not NULL).
1973: -- Wildcard characters ('%') are supported
1974: -- to unpack multiple STS in a single call.
1975: -- for example, just specify '%' to unpack
1976: -- all STS from the staging table.
1973: -- Wildcard characters ('%') are supported
1974: -- to unpack multiple STS in a single call.
1975: -- for example, just specify '%' to unpack
1976: -- all STS from the staging table.
1977: -- sqlset_owner (IN) - name of STS owner, or NULL for current
1978: -- schema owner. Wildcards supported
1979: -- replace (IN) - replace STS if they already exist.
1980: -- If FALSE, function errors when trying to
1981: -- unpack an existing STS
1979: -- replace (IN) - replace STS if they already exist.
1980: -- If FALSE, function errors when trying to
1981: -- unpack an existing STS
1982: -- staging_table_name (IN) - name of staging table, moved after a call
1983: -- to pack_stgtab_sqlset (case-sensitive)
1984: -- staging_schema_owner (IN) - name of staging table owner, or NULL for
1985: -- current schema owner (case-sensitive)
1986: -----------------------------------------------------------------------------
1987: PROCEDURE unpack_stgtab_sqlset(
1983: -- to pack_stgtab_sqlset (case-sensitive)
1984: -- staging_schema_owner (IN) - name of staging table owner, or NULL for
1985: -- current schema owner (case-sensitive)
1986: -----------------------------------------------------------------------------
1987: PROCEDURE unpack_stgtab_sqlset(
1988: sqlset_name IN VARCHAR2 := '%',
1989: sqlset_owner IN VARCHAR2 := NULL,
1990: replace IN BOOLEAN,
1991: staging_table_name IN VARCHAR2,
1984: -- staging_schema_owner (IN) - name of staging table owner, or NULL for
1985: -- current schema owner (case-sensitive)
1986: -----------------------------------------------------------------------------
1987: PROCEDURE unpack_stgtab_sqlset(
1988: sqlset_name IN VARCHAR2 := '%',
1989: sqlset_owner IN VARCHAR2 := NULL,
1990: replace IN BOOLEAN,
1991: staging_table_name IN VARCHAR2,
1992: staging_schema_owner IN VARCHAR2 := NULL);
1985: -- current schema owner (case-sensitive)
1986: -----------------------------------------------------------------------------
1987: PROCEDURE unpack_stgtab_sqlset(
1988: sqlset_name IN VARCHAR2 := '%',
1989: sqlset_owner IN VARCHAR2 := NULL,
1990: replace IN BOOLEAN,
1991: staging_table_name IN VARCHAR2,
1992: staging_schema_owner IN VARCHAR2 := NULL);
1993:
1990: replace IN BOOLEAN,
1991: staging_table_name IN VARCHAR2,
1992: staging_schema_owner IN VARCHAR2 := NULL);
1993:
1994: ------------------------------- remap_stgtab_sqlset -------------------------
1995: -- NAME:
1996: -- remap_stgtab_sqlset
1997: --
1998: -- DESCRIPTION:
1992: staging_schema_owner IN VARCHAR2 := NULL);
1993:
1994: ------------------------------- remap_stgtab_sqlset -------------------------
1995: -- NAME:
1996: -- remap_stgtab_sqlset
1997: --
1998: -- DESCRIPTION:
1999: -- Changes the sqlset names and owners in the staging table so that they
2000: -- can be unpacked with different values than they had on the host
1995: -- NAME:
1996: -- remap_stgtab_sqlset
1997: --
1998: -- DESCRIPTION:
1999: -- Changes the sqlset names and owners in the staging table so that they
2000: -- can be unpacked with different values than they had on the host
2001: -- system.
2002: -- Users should first check to see if the names they are changing to will
2003: -- conflict first -- this function does not enforce that constraint.
2006: -- STS name/owner. Note that this procedure only handles one STS per
2007: -- call.
2008: --
2009: -- PARAMETERS:
2010: -- old_sqlset_name (IN) - name of STS to target for a name/owner
2011: -- remap. Wildcards are NOT supported.
2012: -- old_sqlset_owner (IN) - name of STS owner to target for a
2013: -- remap. NULL for current schema owner.
2014: -- new_sqlset_name (IN) - new name for STS. NULL to keep the same
2008: --
2009: -- PARAMETERS:
2010: -- old_sqlset_name (IN) - name of STS to target for a name/owner
2011: -- remap. Wildcards are NOT supported.
2012: -- old_sqlset_owner (IN) - name of STS owner to target for a
2013: -- remap. NULL for current schema owner.
2014: -- new_sqlset_name (IN) - new name for STS. NULL to keep the same
2015: -- name.
2016: -- new_sqlset_owner (IN) - new owner name for STS. NULL to keep the
2010: -- old_sqlset_name (IN) - name of STS to target for a name/owner
2011: -- remap. Wildcards are NOT supported.
2012: -- old_sqlset_owner (IN) - name of STS owner to target for a
2013: -- remap. NULL for current schema owner.
2014: -- new_sqlset_name (IN) - new name for STS. NULL to keep the same
2015: -- name.
2016: -- new_sqlset_owner (IN) - new owner name for STS. NULL to keep the
2017: -- same owner name.
2018: -- staging_table_name (IN) - name of staging table (case-sensitive)
2012: -- old_sqlset_owner (IN) - name of STS owner to target for a
2013: -- remap. NULL for current schema owner.
2014: -- new_sqlset_name (IN) - new name for STS. NULL to keep the same
2015: -- name.
2016: -- new_sqlset_owner (IN) - new owner name for STS. NULL to keep the
2017: -- same owner name.
2018: -- staging_table_name (IN) - name of staging table (case-sensitive)
2019: -- staging_schema_owner (IN) - name of staging table owner, or NULL for
2020: -- current schema owner (case-sensitive)
2022: -- remap. NULL to keep the same.
2023: -- new_con_dbid (IN) - new container db id to replace with.
2024: -- NULL to keep the same.
2025: -----------------------------------------------------------------------------
2026: PROCEDURE remap_stgtab_sqlset(
2027: old_sqlset_name IN VARCHAR2,
2028: old_sqlset_owner IN VARCHAR2 := NULL,
2029: new_sqlset_name IN VARCHAR2 := NULL,
2030: new_sqlset_owner IN VARCHAR2 := NULL,
2023: -- new_con_dbid (IN) - new container db id to replace with.
2024: -- NULL to keep the same.
2025: -----------------------------------------------------------------------------
2026: PROCEDURE remap_stgtab_sqlset(
2027: old_sqlset_name IN VARCHAR2,
2028: old_sqlset_owner IN VARCHAR2 := NULL,
2029: new_sqlset_name IN VARCHAR2 := NULL,
2030: new_sqlset_owner IN VARCHAR2 := NULL,
2031: staging_table_name IN VARCHAR2,
2024: -- NULL to keep the same.
2025: -----------------------------------------------------------------------------
2026: PROCEDURE remap_stgtab_sqlset(
2027: old_sqlset_name IN VARCHAR2,
2028: old_sqlset_owner IN VARCHAR2 := NULL,
2029: new_sqlset_name IN VARCHAR2 := NULL,
2030: new_sqlset_owner IN VARCHAR2 := NULL,
2031: staging_table_name IN VARCHAR2,
2032: staging_schema_owner IN VARCHAR2 := NULL,
2025: -----------------------------------------------------------------------------
2026: PROCEDURE remap_stgtab_sqlset(
2027: old_sqlset_name IN VARCHAR2,
2028: old_sqlset_owner IN VARCHAR2 := NULL,
2029: new_sqlset_name IN VARCHAR2 := NULL,
2030: new_sqlset_owner IN VARCHAR2 := NULL,
2031: staging_table_name IN VARCHAR2,
2032: staging_schema_owner IN VARCHAR2 := NULL,
2033: old_con_dbid IN NUMBER := NULL,
2026: PROCEDURE remap_stgtab_sqlset(
2027: old_sqlset_name IN VARCHAR2,
2028: old_sqlset_owner IN VARCHAR2 := NULL,
2029: new_sqlset_name IN VARCHAR2 := NULL,
2030: new_sqlset_owner IN VARCHAR2 := NULL,
2031: staging_table_name IN VARCHAR2,
2032: staging_schema_owner IN VARCHAR2 := NULL,
2033: old_con_dbid IN NUMBER := NULL,
2034: new_con_dbid IN NUMBER := NULL);
2032: staging_schema_owner IN VARCHAR2 := NULL,
2033: old_con_dbid IN NUMBER := NULL,
2034: new_con_dbid IN NUMBER := NULL);
2035:
2036: --------------------------- transform_sqlset_cursor -------------------------
2037: -- NAME:
2038: -- transform_sqlset_cursor
2039: --
2040: -- DESCRIPTION:
2034: new_con_dbid IN NUMBER := NULL);
2035:
2036: --------------------------- transform_sqlset_cursor -------------------------
2037: -- NAME:
2038: -- transform_sqlset_cursor
2039: --
2040: -- DESCRIPTION:
2041: -- This function transforms a user specified sql tuning set cursor to
2042: -- a table (function) so that the cursor can be queried in SQL query.
2041: -- This function transforms a user specified sql tuning set cursor to
2042: -- a table (function) so that the cursor can be queried in SQL query.
2043: -- The function is also used to transform an internal cursor created
2044: -- to contain all statements to be deleted from the sql tuning set using
2045: -- the delete_sqlset API.
2046: --
2047: --
2048: -- PARAMETERS:
2049: -- populate_cursor (IN) - cursor to transform.
2047: --
2048: -- PARAMETERS:
2049: -- populate_cursor (IN) - cursor to transform.
2050: -- RETURN:
2051: -- rows of type sqlset_row.
2052: --
2053: -- NOTICE:
2054: -- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
2055: -- ! This function exists for internal use and MUST NOT be documented !
2054: -- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
2055: -- ! This function exists for internal use and MUST NOT be documented !
2056: -- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
2057: ----------------------------------------------------------------------------
2058: FUNCTION transform_sqlset_cursor(
2059: populate_cursor IN sqlset_cursor)
2060: RETURN sys.sqlset PIPELINED;
2061:
2062:
2055: -- ! This function exists for internal use and MUST NOT be documented !
2056: -- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
2057: ----------------------------------------------------------------------------
2058: FUNCTION transform_sqlset_cursor(
2059: populate_cursor IN sqlset_cursor)
2060: RETURN sys.sqlset PIPELINED;
2061:
2062:
2063:
2056: -- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
2057: ----------------------------------------------------------------------------
2058: FUNCTION transform_sqlset_cursor(
2059: populate_cursor IN sqlset_cursor)
2060: RETURN sys.sqlset PIPELINED;
2061:
2062:
2063:
2064:
3494: is_hyper in varchar2 default NULL)
3495: RETURN xmltype;
3496:
3497: --
3498: PROCEDURE check_sqlset_privs(
3499: sqlset_name IN VARCHAR2,
3500: sqlset_owner IN VARCHAR2,
3501: sqlset_create IN BOOLEAN := false,
3502: read_only IN BOOLEAN := false);
3495: RETURN xmltype;
3496:
3497: --
3498: PROCEDURE check_sqlset_privs(
3499: sqlset_name IN VARCHAR2,
3500: sqlset_owner IN VARCHAR2,
3501: sqlset_create IN BOOLEAN := false,
3502: read_only IN BOOLEAN := false);
3503:
3496:
3497: --
3498: PROCEDURE check_sqlset_privs(
3499: sqlset_name IN VARCHAR2,
3500: sqlset_owner IN VARCHAR2,
3501: sqlset_create IN BOOLEAN := false,
3502: read_only IN BOOLEAN := false);
3503:
3504: --
3497: --
3498: PROCEDURE check_sqlset_privs(
3499: sqlset_name IN VARCHAR2,
3500: sqlset_owner IN VARCHAR2,
3501: sqlset_create IN BOOLEAN := false,
3502: read_only IN BOOLEAN := false);
3503:
3504: --
3505: PROCEDURE check_sql_profile_priv(priv IN VARCHAR2);
3505: PROCEDURE check_sql_profile_priv(priv IN VARCHAR2);
3506:
3507: --
3508: PROCEDURE cap_sts_cbk(
3509: sqlset_name IN VARCHAR2,
3510: iterations IN POSITIVE,
3511: cap_option IN VARCHAR2,
3512: cap_mode IN NUMBER,
3513: cbk_proc_name IN VARCHAR2,
3511: cap_option IN VARCHAR2,
3512: cap_mode IN NUMBER,
3513: cbk_proc_name IN VARCHAR2,
3514: basic_filter IN VARCHAR2 := NULL,
3515: sqlset_owner IN VARCHAR2 := NULL);
3516:
3517: --
3518: FUNCTION prepare_sqlset_statement(
3519: sqlset_name IN VARCHAR2,
3514: basic_filter IN VARCHAR2 := NULL,
3515: sqlset_owner IN VARCHAR2 := NULL);
3516:
3517: --
3518: FUNCTION prepare_sqlset_statement(
3519: sqlset_name IN VARCHAR2,
3520: sqlset_owner IN VARCHAR2,
3521: basic_filter IN VARCHAR2 := NULL,
3522: stmt_filter IN BOOLEAN := FALSE,
3515: sqlset_owner IN VARCHAR2 := NULL);
3516:
3517: --
3518: FUNCTION prepare_sqlset_statement(
3519: sqlset_name IN VARCHAR2,
3520: sqlset_owner IN VARCHAR2,
3521: basic_filter IN VARCHAR2 := NULL,
3522: stmt_filter IN BOOLEAN := FALSE,
3523: object_filter IN VARCHAR2 := NULL,
3516:
3517: --
3518: FUNCTION prepare_sqlset_statement(
3519: sqlset_name IN VARCHAR2,
3520: sqlset_owner IN VARCHAR2,
3521: basic_filter IN VARCHAR2 := NULL,
3522: stmt_filter IN BOOLEAN := FALSE,
3523: object_filter IN VARCHAR2 := NULL,
3524: plan_filter IN VARCHAR2 := NULL,
3556: flags IN NUMBER := 0)
3557: RETURN VARCHAR2;
3558:
3559: --
3560: PROCEDURE sqlset_progress_stats(
3561: sqlset_name IN VARCHAR2,
3562: sqlset_owner IN VARCHAR2,
3563: basic_filter IN VARCHAR2 := NULL,
3564: plan_filter IN VARCHAR2 := NULL,
3557: RETURN VARCHAR2;
3558:
3559: --
3560: PROCEDURE sqlset_progress_stats(
3561: sqlset_name IN VARCHAR2,
3562: sqlset_owner IN VARCHAR2,
3563: basic_filter IN VARCHAR2 := NULL,
3564: plan_filter IN VARCHAR2 := NULL,
3565: rank1 IN VARCHAR2 := NULL,
3558:
3559: --
3560: PROCEDURE sqlset_progress_stats(
3561: sqlset_name IN VARCHAR2,
3562: sqlset_owner IN VARCHAR2,
3563: basic_filter IN VARCHAR2 := NULL,
3564: plan_filter IN VARCHAR2 := NULL,
3565: rank1 IN VARCHAR2 := NULL,
3566: rank2 IN VARCHAR2 := NULL,