Chapter 35. DBMS_SPH
Prev   Next

Chapter 35. DBMS_SPH

Table of Contents

35.1. Overview
35.2. Procedures
35.2.1. REPORT_PLANS
35.2.2. REPORT_PLANS_BY_DATE
35.2.3. REPORT_PLAN_HISTORY
35.2.4. REPORT_PLAN_HISTORY_BY_DATE
35.2.5. TRUNCATE_PLAN_HISTORY
35.2.6. TRUNCATE_PLAN_HISTORY_BY_DATE
35.2.7. UPDATE_PLAN_HISTORY

This chapter briefly introduces the DBMS_SPH package, and describes how to use the procedures of the package.

35.1. Overview

DBMS_SPH provides functions required for using SQL Plan History (hereafter SPH). SPH saves and manages history of SQL statements executed on a server.

Tibero manages SQL execution plans. An execution plan can be queried from a dynamic view such as V$SQL_PLAN. Since an execution plan is removed from the memory when it is not used for a long time or the server reboots, it is difficult to detect and trace execution plan changes over a long period of time.

SPH helps users to easily manage execution plan changes over time by saving execution plans in separate tables. However, this package can be used only by SYS users.

35.2. Procedures

This section describes the procedures provided by the DBMS_SPH package, in alphabetical order.

35.2.1. REPORT_PLANS

Outputs the change history of the specified SQL execution plans. This procedure provides the same function as the REPORT_PLANS_BY_DATE procedure, but has a different parameter type. The result is displayed on the screen or saved in a file.

The result is saved in the following file when the TO_FILE parameter is set to TRUE.

$TB_HOME/instance/$TB_SID/sph_report.{mthr_pid}.{current_time}

Details about the REPORT_PLANS procedure are as follows:

  • Prototype

    PROCEDURE REPORT_PLANS 
(
    SQL_HASH_VALUE  IN NUMBER,
    DURATION          IN PLS_INTEGER    DEFAULT 24*365*1000,
    TO_FILE           IN BOOLEAN        DEFAULT TRUE
);

  • Parameter

    ParameterDescription
    SQL_HASH_VALUESQL hash value to output.
    DURATION

    Time period for which SQL history will be displayed. This value is used to calculate the start time. The end time is always the current time.

    • Basis: last execution time of each execution plan

    • Unit: hour

    • Range: from 0 to 24*365*1000

    • Default value: 24*365*1000 (approx. 1,000 years)

    TO_FILE

    Option to save the result in a file.

    • TRUE: save the result in a file. (Default value)

    • FALSE: display the result on screen. The 'serveroutput' option must be set to 'on'.

  • Exception

    ExceptionDescription
    PARAMETER_OUT_OF_RANGEOccurs when the parameter value is out of the specified range.

  • Example 

    SQL> set serveroutput on

SQL> select hash_value from v$sqlarea where sql_text like 'select hash_value%';

HASH_VALUE
----------
3870695416

1 row selected.

SQL> exec dbms_sph.report_plans(3870695416, 24, FALSE); 
            -- Display all execution plans of the day on screen.

                         +-------------------------+
                         | SQL_PLAN_HISTORY REPORT |
                         +-------------------------+

FROM: 1997/05/29 17:29:41
TO:   1997/05/30 17:29:41

... Omitted ...

35.2.2. REPORT_PLANS_BY_DATE

Outputs the change history of the specified SQL execution plans. This procedure provides the same function as the REPORT_PLANS procedure, but has a different parameter type. The result is displayed on the screen or saved in a file.

The result is saved in the following file when the TO_FILE parameter is set to TRUE.

$TB_HOME/instance/$TB_SID/sph_report.{mthr_pid}.{current_time}

Details about the REPORT_PLANS_BY_DATE procedure are as follows:

  • Prototype

    PROCEDURE REPORT_PLANS_BY_DATE 
(
    SQL_HASH_VALUE  IN NUMBER,
    START_DATE        IN DATE       DEFAULT SYSDATE - 365*1000,
    TO_FILE           IN BOOLEAN    DEFAULT TRUE
);

  • Parameter

    ParameterDescription
    SQL_HASH_VALUESQL hash value to output.
    START_DATE

    Start time of the time period for which SQL history will be displayed. The end time is always the current time.

    • Basis: last execution time of each execution plan

    • Unit: day

    • Range: from SYSDATE - 365*1000 to SYSDATE

    • Default value: SYSDATE - 365*1000 (approx. 1,000 years starting from SYSDATE)

    TO_FILE

    Option to save the result in a file.

    • TRUE: save the result in a file. (Default value)

    • FALSE: display the result on screen. The 'serveroutput' option must be set to 'on'.

  • Exception

    ExceptionDescription
    PARAMETER_OUT_OF_RANGEOccurs when the parameter value is out of the specified range.

  • Example 

    SQL> set serveroutput on

SQL> select hash_value from v$sqlarea where sql_text like 'select hash_value%';

HASH_VALUE
----------
3870695416

1 row selected.

SQL> exec dbms_sph.report_plans_by_date(3870695416, sysdate - 1, FALSE); 
            -- Display all execution plans of the day on screen.

                         +-------------------------+
                         | SQL_PLAN_HISTORY REPORT |
                         +-------------------------+

FROM: 1997/05/29 17:49:11
TO:   1997/05/30 17:49:11

... Omitted ...

35.2.3. REPORT_PLAN_HISTORY

Outputs the change history of all SQL execution plans that meet the specified condition. This procedure provides the same function as the REPORT_PLAN_HISTORY_BY_DATE procedure, but has a different parameter type. The result is displayed on the screen or saved in a file.

The result is saved in the following file when the TO_FILE parameter is set to TRUE.

$TB_HOME/instance/$TB_SID/sph_report.{mthr_pid}.{current_time}

Details about the REPORT_PLAN_HISTORY procedure are as follows:

  • Prototype

    PROCEDURE REPORT_PLAN_HISTORY 
(
    DURATION          IN PLS_INTEGER  DEFAULT 24*365*1000,
    MIN_PLAN_COUNT    IN PLS_INTEGER  DEFAULT 1,
    TO_FILE           IN BOOLEAN      DEFAULT TRUE
);

  • Parameter

    ParameterDescription
    DURATION

    Time period for which SQL history will be displayed. This value is used to calculate the start time. The end time is always the current time.

    • Basis: last execution time of each execution plan

    • Unit: hour

    • Range: from 0 to 24*365*1000

    • Default value: 24*365*1000 (approx. 1,000 years)

    MIN_PLAN_COUNT

    Minimum number of execution plans that a SQL to output can have. For example, if the value is 2, only SQL statements with 2 or more execution plans are saved or displayed.

    • Range: 1 - 1000000

    • Default value: 1

    TO_FILE

    Option to save the result in a file.

    • TRUE: save the result in a file. (Default value)

    • FALSE: display the result on screen. The 'serveroutput' option must be set to 'on'.

  • Exception

    ExceptionDescription
    PARAMETER_OUT_OF_RANGEOccurs when the parameter value is out of the specified range.

  • Example 

    SQL> set serveroutput on

SQL> exec dbms_sph.report_plan_history(24, 1, FALSE);
             -- Display all execution plans of SQL
                statements executed during the day on screen.

                         +-------------------------+
                         | SQL_PLAN_HISTORY REPORT |
                         +-------------------------+

FROM: 1997/05/29 17:49:11
TO:   1997/05/30 17:49:11

... Omitted ...

35.2.4. REPORT_PLAN_HISTORY_BY_DATE

Outputs the change history of all SQL execution plans that meet the specified condition. This procedure provides the same function as the REPORT_PLAN_HISTORY procedure, but has a different parameter type. The result is displayed on the screen or saved in a file.

The result is saved in the following file when the TO_FILE parameter is set to TRUE.

$TB_HOME/instance/$TB_SID/sph_report.{mthr_pid}.{current_time}

Details about the REPORT_PLAN_HISTORY_BY_DATE procedure are as follows:

  • Prototype

    PROCEDURE REPORT_PLAN_HISTORY_BY_DATE 
(
    START_DATE        IN DATE         DEFAULT SYSDATE - 365*1000,
    MIN_PLAN_COUNT    IN PLS_INTEGER  DEFAULT 1,
    TO_FILE           IN BOOLEAN      DEFAULT TRUE
);

  • Parameter

    ParameterDescription
    START_DATE

    Start time of the time period for which SQL history will be displayed. The end time is always the current time.

    • Basis: last execution time of each execution plan

    • Unit: day

    • Range: from SYSDATE - 365*1000 to SYSDATE

    • Default value: SYSDATE - 365*1000 (approx. 1,000 years starting from SYSDATE)

    MIN_PLAN_COUNT

    Minimum number of execution plans that a SQL to output can have. For example, if the value is 2, only SQL statements with 2 or more execution plans are saved or displayed.

    • Range: 1 - 1000000

    • Default value: 1

    TO_FILE

    Option to save the result in a file.

    • TRUE: save the result in a file. (Default value)

    • FALSE: display the result on screen. The 'serveroutput' option must be set to 'on'.

  • Exception

    ExceptionDescription
    PARAMETER_OUT_OF_RANGEOccurs when the parameter value is out of the specified range.

  • Example 

    SQL> set serveroutput on

SQL> exec dbms_sph.report_plan_history_by_date(sysdate - 1, 1, FALSE); 
              -- Display all execution plans of SQL
                statements executed during the day on screen.

                         +-------------------------+
                         | SQL_PLAN_HISTORY REPORT |
                         +-------------------------+

FROM: 1997/05/29 17:49:11
TO:   1997/05/30 17:49:11

... Omitted ...

35.2.5. TRUNCATE_PLAN_HISTORY

Deletes the change history of execution plans saved in SPH. This procedure provides the same function as the TRUNCATE_PLAN_HISTORY_BY_DATE procedure, but has a different DURATION parameter type.

Details about the TRUNCATE_PLAN_HISTORY procedure are as follows:

  • Prototype

    PROCEDURE TRUNCATE_PLAN_HISTORY 
(
    RETENTION_PERIOD  IN PLS_INTEGER  DEFAULT 24*365*1000
    MAX_COUNT         IN PLS_INTEGER  DEFAULT 1000000
);

  • Parameter

    ParameterDescription
    RETENTION_PERIOD

    Time period for which SQL history will be deleted. All execution plans that are not executed during this time period are deleted.

    • Unit: hour

    • Range: from 0 to 24*365*1000

    • Default value: 24*365*1000 (approx. 1,000 years)

    MAX_COUNT

    Number of SQL statements whose execution plans will not be removed.

    If both RETENTION_PERIOD and MAX_COUNT are specified, only statements that meet both conditions are maintained.

    • Range: 0 - 1000000

    • Default value: 1000000

  • Exception

    ExceptionDescription
    PARAMETER_OUT_OF_RANGEOccurs when the parameter value is out of the specified range.

  • Example 

    SQL> exec dbms_sph.truncate_plan_history(24); 
            -- Delete all execution plans that have not been
               executed for a day.

PSM completed.

35.2.6. TRUNCATE_PLAN_HISTORY_BY_DATE

Deletes the change history of execution plans saved in SPH. This procedure provides the same function as the TRUNCATE_PLAN_HISTORY procedure, but has a different DURATION parameter type.

Details about the TRUNCATE_PLAN_HISTORY_BY_DATE procedure are as follows:

  • Prototype

    PROCEDURE TRUNCATE_PLAN_HISTORY_BY_DATE 
(
    START_DATE        IN DATE         DEFAULT SYSDATE - 365*1000
    MAX_COUNT         IN PLS_INTEGER  DEFAULT 1000000
);

  • Parameter

    ParameterDescription
    START_DATE

    Start time of the time period for which SQL history will be deleted. All execution plans that are executed before this time period are removed.

    • Unit: day

    • Range: from SYSDATE - 365*1000 to SYSDATE

    • Default value: SYSDATE - 365*1000 (about the past 1,000 years from SYSDATE)

    MAX_COUNT

    Number of SQL statements whose execution plans will not be removed.

    If both START_DATE and MAX_COUNT are specified, only statements that meet both conditions will be maintained.

    • Range: 0 - 1000000

    • Default value: 1000000

  • Exception

    ExceptionDescription
    PARAMETER_OUT_OF_RANGEOccurs when the parameter value is out of the specified range.

  • Example 

    SQL> exec dbms_sph.truncate_plan_history_by_date(sysdate - 1); 
          -- Delete all execution plans that have not been
             executed for a day.
PSM completed.

35.2.7. UPDATE_PLAN_HISTORY

Saves execution plans in the library cache to SPH.

Details about the UPDATE_PLAN_HISTORY procedure are as follows:

  • Prototype

    PROCEDURE UPDATE_PLAN_HISTORY 
(
    MIN_EXEC_COUNT    IN PLS_INTEGER  DEFAULT 1
);

  • Parameter

    ParameterDescription
    MIN_EXEC_COUNT

    Execution plans that have been executed this many times or less are saved to SPH. Execution plans that are executed less than this number of times are not saved.

    • Range: 1 - 1000000

    • Default value: 1

  • Exception

    ExceptionDescription
    PARAMETER_OUT_OF_RANGEOccurs when the parameter value is out of the specified range.

  • Example 

    SQL> exec dbms_sph.update_plan_history(2); 
-- Save execution plans executed two or more times to SPH.

PSM completed.
Prev   Next
Chapter 34. DBMS_SPACE_ADMIN Home Chapter 36. DBMS_SQL