Rem
Rem $Header: owb_uprop_tune.sql 01-feb-2006.02:47:55 bjeffrie Exp $
Rem
Rem owb_uprop_tune.sql
Rem
Rem Copyright (c) 2006, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      owb_uprop_tune.sql - SQL tuning functions
Rem
Rem    DESCRIPTION
Rem      This package contains functions to simplify running SQL tuning on the 
Rem    queries supporting the owb_uprop table functions. They can additionally
Rem    be used to run SQL tuning on any query that has its text in the 
Rem    PUBLIC_VIEWS table.
Rem
Rem    NOTES
Rem      See the PL/SQL Packages and Types Reference, DBMS_SQLTUNE section for
Rem    more information about SQL tuning.
Rem      See owb_uprop_views.sql for information on the PUBLIC_VIEW table.
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    bjeffrie    02/01/06 - Created
Rem

--alter session set sql_trace=true;
--alter session set "_stn_trace"=1023;

CREATE OR REPLACE PACKAGE owb_uprop_tune AS

  /* Run the SQL Tuning process on the given view. The view query text must be
   * available from the PUBLIC_VIEWS table.
   * RETURN the SQL tuning task id.
   * Note: You must have ADVISOR privileges to use this function.
   */
  FUNCTION execute_tuning(p_view_name IN VARCHAR2 default 'getProperties') RETURN VARCHAR2;

  /* Monitor the tuning task for the given view.
   * RETURN one of INITIAL,EXCECUTING,COMPLETED,INTERRUPTED,CANCELLED,FATAL ERROR
   */
  FUNCTION monitor_tuning(p_view_name IN VARCHAR2 default 'getProperties') RETURN VARCHAR2;
  
  /* Report tuning results for the given view
   * RETURN tuning results (which can be quite extensive)
   * Note: Suggested usage is something like:
   * SET LONG 10000;
   * SET PAGESIZE 1000
   * SET LINESIZE 200
   * SELECT owb_uprop_tune.report_tuning('<name>') AS Recommendations FROM dual;
   * SET PAGESIZE 24
   * Note: You must have ADVISOR privileges to use this function.
   */
  FUNCTION report_tuning(p_view_name IN VARCHAR2 default 'getProperties') RETURN VARCHAR2;
  
  /* Accept (put in to active use) the SQL PROFILE recommended by the tuning task.
   * RETURN the SQL tuning task id.
   * Note: You must have CREATE,DROP,ALTER ANY SQL PROFILE privileges to use this function.
   */
  FUNCTION accept_tuning(p_view_name IN VARCHAR2 default 'getProperties') RETURN VARCHAR2;

END owb_uprop_tune;
/

CREATE OR REPLACE PACKAGE BODY owb_uprop_tune AS

  /* Run the SQL Tuning process on the given view. The view query text must be
   * available from the PUBLIC_VIEWS table.
   * RETURN the SQL tuning task id.
   * Note: You must have ADVISOR privileges to use this function.
   */
  FUNCTION execute_tuning(p_view_name IN VARCHAR2 default 'getProperties') RETURN VARCHAR2
  IS
    my_procname  VARCHAR2(65) := 'owb_uprop_tuning.execute_tuning';
    my_sqltext   CLOB;
    my_task_name VARCHAR2(30);
  BEGIN
   
     -- select from ... where USERNAME='OWB_UPROP' will give all proc queries
    /* Get our SQL... */
    SELECT SQL_TEXT INTO my_sqltext FROM PUBLIC_VIEWS WHERE VIEW_NAME=p_view_name;
    
    /* Drop the task in case we are re-running... */
    BEGIN
      DBMS_SQLTUNE.DROP_TUNING_TASK( 
        task_name => p_view_name
      );
    EXCEPTION
      WHEN OTHERS THEN -- ORA-13605
        NULL;
    END;
  
    /* Create a SQL Tuning task for our SQL... */
    my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
      sql_text    => my_sqltext,
      user_name   => USER,
      scope       => 'COMPREHENSIVE',
      time_limit  => 180,
      task_name   => p_view_name,
      description => 'owb_uprop_tune.execute_tuning('|| p_view_name ||')'
    );

    /* Execute the task... */
    DBMS_SQLTUNE.EXECUTE_TUNING_TASK( 
      task_name => p_view_name
    );

    RETURN my_task_name;
  END;

  /* Monitor the tuning task for the given view.
   * RETURN one of INITIAL,EXCECUTING,COMPLETED,INTERRUPTED,CANCELLED,FATAL ERROR
   */
  FUNCTION monitor_tuning(p_view_name IN VARCHAR2 default 'getProperties') RETURN VARCHAR2
  IS
    my_procname  VARCHAR2(65) := 'owb_uprop_tuning.monitor_tuning';
    my_status VARCHAR2(11);
  BEGIN
    SELECT status INTO my_status FROM DBA_ADVISOR_LOG WHERE task_name=p_view_name;
    RETURN my_status;
  END;
  
  /* Report tuning results for the given view
   * RETURN tuning results (which can be quite extensive)
   * Note: Suggested usage is something like:
   * SET LONG 10000;
   * SET PAGESIZE 1000
   * SET LINESIZE 200
   * SELECT owb_uprop_tune.report_tuning('<name>') AS Recommendations FROM dual;
   * SET PAGESIZE 24
   * Note: You must have ADVISOR privileges to use this function.
   */
  FUNCTION report_tuning(p_view_name IN VARCHAR2 default 'getProperties') RETURN VARCHAR2
  IS
    my_procname  VARCHAR2(65) := 'owb_uprop_tuning.report_tuning';
  BEGIN
    RETURN DBMS_SQLTUNE.report_tuning_task(
             task_name => p_view_name
           );
  END;
  
  /* Accept (put in to active use) the SQL PROFILE recommended by the tuning task.
   * RETURN the SQL tuning task id.
   * Note: You must have CREATE,DROP,ALTER ANY SQL PROFILE privileges to use this function.
   */
  FUNCTION accept_tuning(p_view_name IN VARCHAR2 default 'getProperties') RETURN VARCHAR2
  IS
    my_procname  VARCHAR2(65) := 'owb_uprop_tuning.accept_tuning';
  BEGIN
    RETURN DBMS_SQLTUNE.accept_sql_profile (
             task_name => p_view_name,
             name      => p_view_name
           );
  END;

END owb_uprop_tune;
/