Rem
Rem $Header: reuse.sql 30-apr-2003.16:42:38 ychan Exp $
Rem
Rem sqlbindvars_types.sql
Rem
Rem Copyright (c) 2002, 2003, Oracle Corporation.  All rights reserved.  
Rem
Rem    NAME
Rem      sqlbindvars_types.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    ychan       04/30/03 - remove set cmd
Rem    ychan       11/05/02 - Change permission
Rem    vchao       10/07/02 - Replace CASE with IF for 817
Rem    ychan       09/10/02 - ychan_dbconf7
Rem    ychan       08/14/02 - ychan_dbconf
Rem    vchao       08/05/02 - Change data structure due to reduction of columns
Rem    vchao       07/29/02 - vchao_sql_bindvars_0
Rem    vchao       07/29/02 - Created
Rem

Rem Please don't put set command here
Rem SET ECHO ON
Rem SET FEEDBACK 1
Rem SET NUMWIDTH 10
Rem SET LINESIZE 80
Rem SET TRIMSPOOL ON
Rem SET TAB OFF
Rem SET PAGESIZE 100

/*
|| This object defines the datapoints necessary to toggle the bind variable
|| assessment process.
||
|| There are two latches whose contention drive this: the shared_pool latch
|| and the library_cache latch.  For each type of latch there are two
|| properties which drive the sensitivity of the contention.
||   <li>sleep level - the number of sleeps per wait which is required on
||       the latch.  For example, if this value is 3 only waits which cause
||       3 or more sleeps are factored into the sensitivity.</li>
||   <li>sleep rate - the wait rate according to sleep level.  For example,
||       if this value is 0.25 the waits above sleep level must be 25% of
||       all of the gets.
||
|| If the sleep rate is set to 1.0 for both latches, then the contention
|| check is infinitely sensitive (i.e., will never run).
||
|| If at least one of the latches has it's sleep rate < 1.0 and the sleep
|| level set to 0, then the contention is completely insensitive (i.e.,
|| will always run).
||
|| Otherwise, there must be appropriately high contention on at least one
|| latch to perform expensive bind variable processing.
*/

CREATE OR REPLACE TYPE SqlCacheEfficiencyDatapoint AS OBJECT (
    m_sharedPoolGets  NUMBER,
    m_sharedPool1Sleep NUMBER,
    m_sharedPool2Sleeps NUMBER,
    m_sharedPool3Sleeps NUMBER,
    m_sharedPool4OrMoreSleeps NUMBER,
    m_libraryCacheGets NUMBER,
    m_libraryCache1Sleep NUMBER,
    m_libraryCache2Sleeps NUMBER,
    m_libraryCache3Sleeps NUMBER ,
    m_libraryCache4OrMoreSleeps NUMBER,

    MEMBER FUNCTION getSharedPoolGets RETURN NUMBER,
    MEMBER FUNCTION getSharedPool1Sleep RETURN NUMBER,
    MEMBER FUNCTION getSharedPool2Sleeps RETURN NUMBER,
    MEMBER FUNCTION getSharedPool3Sleeps RETURN NUMBER,
    MEMBER FUNCTION getSharedPool4OrMoreSleeps RETURN NUMBER,
    MEMBER FUNCTION getLibraryCacheGets RETURN NUMBER,
    MEMBER FUNCTION getLibraryCache1Sleep RETURN NUMBER,
    MEMBER FUNCTION getLibraryCache2Sleeps RETURN NUMBER,
    MEMBER FUNCTION getLibraryCache3Sleeps RETURN NUMBER,
    MEMBER FUNCTION getLibraryCache4OrMoreSleeps RETURN NUMBER,

    MEMBER PROCEDURE setSharedPoolLatchValues(sharedPoolGets NUMBER,
                                             sharedPool1Sleep NUMBER,
                                             sharedPool2Sleeps NUMBER,
                                             sharedPool3Sleeps NUMBER,
                                             sharedPool4OrMoreSleeps NUMBER),

    MEMBER PROCEDURE setLibraryCacheLatchValues(libraryCacheGets NUMBER,
                                               libraryCache1Sleep NUMBER,
                                               libraryCache2Sleeps NUMBER,
                                               libraryCache3Sleeps NUMBER,
                                               libraryCache4OrMoreSleeps NUMBER),

    MEMBER FUNCTION isLibraryCacheLatchDataValid RETURN BOOLEAN,

    MEMBER FUNCTION isSharedPoolLatchDataValid RETURN BOOLEAN,

    MEMBER FUNCTION isSqlCachePoorlyUtilized(pdp SqlCacheEfficiencyDatapoint,
                                            sharedPoolLatchSensitivity NUMBER,
                                            sharedPoolLatchSleepPct NUMBER,
                                            libraryCacheLatchSensitivity NUMBER,
                                            libraryCacheLatchSleepPct NUMBER)
        RETURN BOOLEAN
 );
/
show error;

CREATE OR REPLACE TYPE BODY SqlCacheEfficiencyDatapoint
AS
    MEMBER FUNCTION getSharedPoolGets RETURN NUMBER
    IS
    BEGIN
      RETURN m_sharedPoolGets;
    END getSharedPoolGets;

    MEMBER FUNCTION getSharedPool1Sleep RETURN NUMBER
    IS
    BEGIN
      RETURN m_sharedPool1Sleep;
    END getSharedPool1Sleep;

    MEMBER FUNCTION getSharedPool2Sleeps RETURN NUMBER
    IS
    BEGIN
      RETURN m_sharedPool2Sleeps;
    END getSharedPool2Sleeps;

    MEMBER FUNCTION getSharedPool3Sleeps RETURN NUMBER
    IS
    BEGIN
      RETURN m_sharedPool3Sleeps;
    END getSharedPool3Sleeps;

    MEMBER FUNCTION getSharedPool4OrMoreSleeps RETURN NUMBER
    IS
    BEGIN
      RETURN m_sharedPool4OrMoreSleeps;
    END getSharedPool4OrMoreSleeps;

    MEMBER PROCEDURE setSharedPoolLatchValues(sharedPoolGets NUMBER,
                                             sharedPool1Sleep NUMBER,
                                             sharedPool2Sleeps NUMBER,
                                             sharedPool3Sleeps NUMBER,
                                             sharedPool4OrMoreSleeps NUMBER)
    IS
    BEGIN
      m_sharedPoolGets := -1.0;
      IF (sharedPool1Sleep >= 0.0 AND sharedPool2Sleeps >= 0.0 AND
          sharedPool3Sleeps >= 0.0 AND sharedPool4OrMoreSleeps >= 0.0)
      THEN
        m_sharedPool4OrMoreSleeps := sharedPool4OrMoreSleeps;
        m_sharedPool3Sleeps := sharedPool3Sleeps + m_sharedPool4OrMoreSleeps;
        m_sharedPool2Sleeps := sharedPool2Sleeps + m_sharedPool3Sleeps;
        m_sharedPool1Sleep := sharedPool1Sleep + m_sharedPool2Sleeps;

        IF (m_sharedPool1Sleep <= sharedPoolGets) THEN
          m_sharedPoolGets := sharedPoolGets;
        END IF;
      END IF;
    END setSharedPoolLatchValues;

    MEMBER FUNCTION getLibraryCacheGets RETURN NUMBER
    IS
    BEGIN
      return m_libraryCacheGets;
    END getLibraryCacheGets;

    MEMBER FUNCTION getLibraryCache1Sleep RETURN NUMBER
    IS
    BEGIN
      return m_libraryCache1Sleep;
    END getLibraryCache1Sleep;

    MEMBER FUNCTION getLibraryCache2Sleeps RETURN NUMBER
    IS
    BEGIN
      return m_libraryCache2Sleeps;
    END getLibraryCache2Sleeps;

    MEMBER FUNCTION getLibraryCache3Sleeps RETURN NUMBER
    IS
    BEGIN
      return m_libraryCache3Sleeps;
    END getLibraryCache3Sleeps;

    MEMBER FUNCTION getLibraryCache4OrMoreSleeps RETURN NUMBER
    IS
    BEGIN
      return m_libraryCache4OrMoreSleeps;
    END getLibraryCache4OrMoreSleeps;


    MEMBER PROCEDURE setLibraryCacheLatchValues(libraryCacheGets NUMBER,
                                               libraryCache1Sleep NUMBER,
                                               libraryCache2Sleeps NUMBER,
                                               libraryCache3Sleeps NUMBER,
                                               libraryCache4OrMoreSleeps NUMBER)
    IS
    BEGIN
      m_libraryCacheGets := -1.0;
      IF (libraryCache1Sleep >= 0.0 AND libraryCache2Sleeps >= 0.0 AND
          libraryCache3Sleeps >= 0.0 AND libraryCache4OrMoreSleeps >= 0.0)
      THEN
        m_libraryCache4OrMoreSleeps := libraryCache4OrMoreSleeps;
        m_libraryCache3Sleeps := libraryCache3Sleeps
                                + m_libraryCache4OrMoreSleeps;
        m_libraryCache2Sleeps := libraryCache2Sleeps + m_libraryCache3Sleeps;
        m_libraryCache1Sleep := libraryCache1Sleep + m_libraryCache2Sleeps;

        IF (m_libraryCache1Sleep <= libraryCacheGets) THEN
          m_libraryCacheGets := libraryCacheGets;
        END IF;
      END IF;
    END setLibraryCacheLatchValues;

    MEMBER FUNCTION isLibraryCacheLatchDataValid RETURN BOOLEAN
    IS
    BEGIN
        IF (m_libraryCacheGets >= 0.0) THEN
            RETURN true;
        ELSE
            RETURN false;
        END IF;
    END isLibraryCacheLatchDataValid;


    MEMBER FUNCTION isSharedPoolLatchDataValid RETURN BOOLEAN
    IS
    BEGIN
        IF (m_sharedPoolGets >= 0.0) THEN
            RETURN true;
        ELSE
            RETURN false;
        END IF;
    END isSharedPoolLatchDataValid;

    MEMBER FUNCTION isSqlCachePoorlyUtilized(pdp SqlCacheEfficiencyDatapoint,
                                            sharedPoolLatchSensitivity NUMBER,
                                            sharedPoolLatchSleepPct NUMBER,
                                            libraryCacheLatchSensitivity NUMBER,
                                            libraryCacheLatchSleepPct NUMBER)
        RETURN BOOLEAN
    IS
        dGets NUMBER;
        dHiSleeps NUMBER;
        poorlyUtilized BOOLEAN := false;
    BEGIN

    IF (pdp IS NULL) THEN
        RETURN false;
    ELSE
        IF ( isSharedPoolLatchDataValid()  AND
             pdp.isSharedPoolLatchDataValid() ) THEN
          dGets := (getSharedPoolGets() - pdp.getSharedPoolGets());

          IF (sharedPoolLatchSensitivity = 0) THEN
            /* 0 means 0 sleeps required */
            dHiSleeps := dGets;
          ELSIF  (sharedPoolLatchSensitivity = 1) THEN
            dHiSleeps := (getSharedPool1Sleep() - pdp.getSharedPool1Sleep());
          ELSIF  (sharedPoolLatchSensitivity = 2) THEN
            dHiSleeps := (getSharedPool2Sleeps() - pdp.getSharedPool2Sleeps());
          ELSIF  (sharedPoolLatchSensitivity = 3) THEN
            dHiSleeps := (getSharedPool3Sleeps() - pdp.getSharedPool3Sleeps());
          ELSE
            dHiSleeps := (getSharedPool4OrMoreSleeps() -
                         pdp.getSharedPool4OrMoreSleeps());
          END IF;

          IF (dGets > 0.0 AND dHiSleeps >= 0.0 AND dHiSleeps <= dGets) THEN
            poorlyUtilized := poorlyUtilized OR
                             ((dHiSleeps / dGets) > sharedPoolLatchSleepPct);
          END IF;
        END IF;

        IF ( isLibraryCacheLatchDataValid()  AND
             pdp.isLibraryCacheLatchDataValid() ) THEN

          dGets := (getLibraryCacheGets() - pdp.getLibraryCacheGets());

          IF  (sharedPoolLatchSensitivity = 0) THEN
                /* 0 means 0 sleeps required */
                dHiSleeps := dGets;
          ELSIF  (sharedPoolLatchSensitivity = 1) THEN
                dHiSleeps := getLibraryCache1Sleep()
                                - pdp.getLibraryCache1Sleep();
          ELSIF  (sharedPoolLatchSensitivity = 2) THEN
                dHiSleeps := getLibraryCache2Sleeps()
                                - pdp.getLibraryCache2Sleeps();
          ELSIF  (sharedPoolLatchSensitivity = 3) THEN
                dHiSleeps := getLibraryCache3Sleeps()
                                - pdp.getLibraryCache3Sleeps();
          ELSE
                dHiSleeps := getLibraryCache4OrMoreSleeps()
                                - pdp.getLibraryCache4OrMoreSleeps();
          END IF;

          IF ( (dGets > 0.0 AND dHiSleeps >= 0.0) AND (dHiSleeps <= dGets) ) THEN
            poorlyUtilized := poorlyUtilized OR
                           ((dHiSleeps / dGets) > libraryCacheLatchSleepPct);
          END IF;
        END IF;
    END IF;
      RETURN poorlyUtilized;
  END isSqlCachePoorlyUtilized;

END;
/
show error;


CREATE OR REPLACE TYPE VString AS VARRAY(6) OF VARCHAR2(1000);
/


/* The value or payload for each key in our partial SQL text hash table is
|| an array list of these values.  For each normalized SQL prefix, we store
|| the byte address, hash value, and the length of the sql_text field from
|| V$SQLAREA.  If this length is less than 995 chars, we will retrieve the
|| full SQL text (later, if necessary) from V$SQLTEXT_WITH_NEWLINES.
*/

CREATE OR REPLACE TYPE SqlPrefixHashValue AS OBJECT (
    /* ------------- Member attributes -------------------- */
    /* Note: We cannot initialize an attribute in its declaration
    || using the assignment operator or DEFAULT clause. !!!
    */
    m_sqlTextLen NUMBER,
    m_byteAddress RAW(8),
    m_candidate VString,

    /* ------------- Member methods -------------------- */
    MEMBER PROCEDURE init,

    MEMBER FUNCTION  getByteAddress RETURN RAW,

    MEMBER FUNCTION getSqlTextLen RETURN NUMBER,

    MEMBER FUNCTION getResults RETURN VString,

    MEMBER PROCEDURE setSqlText(sqlText VARCHAR2),

    MEMBER FUNCTION  getSqlText RETURN VARCHAR2,

    MEMBER FUNCTION containsFullText RETURN BOOLEAN,

    MEMBER PROCEDURE setApplication(application VARCHAR2),

    MEMBER FUNCTION  getApplication RETURN VARCHAR2,

    MEMBER  PROCEDURE setModule(module VARCHAR2),

    MEMBER FUNCTION getModule RETURN VARCHAR2,

    MEMBER PROCEDURE setNormalizedSqlText(normalizedSqlText VARCHAR2),

    MEMBER FUNCTION  getNormalizedSqlText RETURN VARCHAR2,

    MEMBER PROCEDURE setSharableMem(sharableMem VARCHAR2),

    MEMBER FUNCTION getSharableMem RETURN VARCHAR2,

    MEMBER PROCEDURE setHashValue(hashValue VARCHAR2),

    MEMBER FUNCTION getHashValue RETURN VARCHAR2

 );
/
show error;

CREATE OR REPLACE TYPE BODY SqlPrefixHashValue
AS
    /* Init m_candidate since we cannot init object attribute */
    MEMBER PROCEDURE init
    IS
    BEGIN
        /* an non-null VARRAY with empty elements (uninitialized) */
        m_candidate := VString();

        /* NOTE: We have to call EXTEND to initialize all elements */
        m_candidate.EXTEND(6);
    END init;

    MEMBER FUNCTION  getByteAddress RETURN RAW
    IS
    BEGIN
        return m_byteAddress;
    END getByteAddress;

    MEMBER FUNCTION getSqlTextLen RETURN NUMBER
    IS
    BEGIN
        return m_sqlTextLen;
    END getSqlTextLen;

    MEMBER FUNCTION getResults RETURN VString
    IS
    BEGIN
        return m_candidate;
    END getResults;

    MEMBER PROCEDURE setSqlText(sqlText VARCHAR2)
    IS
    BEGIN
      m_byteAddress := null;
      m_candidate(1) := sqlText;
    END setSqlText;

    MEMBER FUNCTION  getSqlText RETURN VARCHAR2
    IS
        ret VARCHAR2(1000);
    BEGIN
        ret := m_candidate(1);
        return ret;
    END getSqlText;

    MEMBER FUNCTION containsFullText RETURN BOOLEAN
    IS
    BEGIN
        IF m_byteAddress IS NULL THEN
            return true;
        ELSE
            return false;
        END IF;
    END containsFullText;

    MEMBER PROCEDURE setApplication(application VARCHAR2)
    IS
        ret VARCHAR2(1000);
    BEGIN
        m_candidate(2) := application;
    END setApplication;

    MEMBER FUNCTION  getApplication RETURN VARCHAR2
    IS
        ret VARCHAR2(1000);
    BEGIN
        ret := m_candidate(2);
        return ret;
    END;

    MEMBER PROCEDURE setModule(module VARCHAR2)
    IS
    BEGIN
        m_candidate(3) := module;
    END setModule;


    MEMBER FUNCTION getModule RETURN VARCHAR2
    IS
        ret VARCHAR2(1000);
    BEGIN
        ret := m_candidate(3);
        return ret;
    END getModule;

    MEMBER PROCEDURE setNormalizedSqlText(normalizedSqlText VARCHAR2)
    IS
    BEGIN
        m_candidate(4) := normalizedSqlText;
    END setNormalizedSqlText;


    MEMBER FUNCTION  getNormalizedSqlText RETURN VARCHAR2
    IS
        ret VARCHAR2(1000);
    BEGIN
        ret := m_candidate(4);
        return ret;
    END getNormalizedSqlText;


    MEMBER PROCEDURE setSharableMem(sharableMem VARCHAR2)
    IS
    BEGIN
        m_candidate(5) := sharableMem;
    END setSharableMem;

    MEMBER FUNCTION getSharableMem RETURN VARCHAR2
    IS
        ret VARCHAR2(1000);
    BEGIN
        ret := m_candidate(5);
        return ret;
    END getSharableMem;

    MEMBER PROCEDURE setHashValue(hashValue VARCHAR2)
    IS
    BEGIN
        IF ( (hashValue IS NULL) OR LENGTH(hashValue) <= 0) THEN
            m_candidate(6) := '';
        ELSE
            m_candidate(6) := hashValue;
        END IF;
    END setHashValue;

    MEMBER FUNCTION getHashValue RETURN VARCHAR2
    IS
        ret VARCHAR2(1000);
    BEGIN
        ret := m_candidate(6);
        return ret;
    END getHashValue;

 END;
/

show error;

 CREATE OR REPLACE TYPE SqlCandidate AS OBJECT (
    /* ------------- Member attributes -------------------- */
    sql_text        VARCHAR(4000),
    application     VARCHAR2(256),
    module          VARCHAR2(256),
    normalized_sql  VARCHAR2(4000),
    sharable_mem    NUMBER
 );
/
show error;


CREATE OR REPLACE TYPE TCandidateType AS TABLE OF SqlCandidate;
/
show error;


CREATE OR REPLACE PACKAGE mgmt_sqlreuse
AS
   /* Externally callable subprograms. */
   FUNCTION assessBindVariables(ignoreStat BOOLEAN)
      RETURN TCandidateType;

   FUNCTION getVersion
      RETURN VARCHAR2;

END mgmt_sqlreuse;
/
show errors