Method

Execution plans can be queried through dynamic views such as V$SQL_PLAN. 

However, if an execution plan is not used for a long time or if the server is rebooted, that execution plan is removed from memory, making it difficult to detect and track changes in execution plans over an extended period.

In this case, SPH helps users easily manage changes in execution plans over time by storing execution plans in a separate table. 

Caution

Note that this package can only be used by the SYS user.

Scenario

SQL> create table tibero.sph_test(c1 number);
SQL> insert into tibero.sph_test select level from dual connect by level < 100000;
SQL> commit;

SQL> set autot on
SQL> select * from tibero.sph_test where c1=8629; -- executed 4 times

/*
SQL ID: 516a56bm7x7d8
Child number: 283
Plan hash value: 808101735
*/

SQL> exec dbms_sph.update_plan_history(2);

SQL> SELECT * FROM SYS._SPH WHERE PLAN_HASH_VALUE='808101735'; -- verify if stored in SPH table

--SQL_HASH_VALUE : 3867057576
SQL> alter table tibero.sph_test add primary key (c1); -- plan change

SQL> select * from tibero.sph_test where c1=8629; -- executed 4 times

/*
SQL ID: 516a56bm7x7d8
Child number: 371
Plan hash value: 3929546620
*/

SQL> exec dbms_sph.update_plan_history(2);

SQL> SELECT * FROM SYS._SPH WHERE PLAN_HASH_VALUE='3929546620'; -- verify if stored in SPH table

--SQL_HASH_VALUE : 3867057576


SQL> set autot off
SQL> set serveroutput on
SQL> exec dbms_sph.report_plans(3867057576, 24, FALSE); --SQL_HASH_VALUE, DURATION, TO_FILE

Scenario Results

Changes in the plan are output in chronological order.

1. Execution plan performing a table full scan immediately after table creation
2. Execution plan changed to index scan after adding a primary key constraint to the table

SQL> set serveroutput on
SQL> exec dbms_sph.report_plans(3867057576, 24, FALSE);
 
                         +-------------------------+
                         | SQL_PLAN_HISTORY REPORT |
                         +-------------------------+
 
FROM: 2025/11/03
TO:   2025/11/04
 
 
================================================================================
SQL Hash Value: 3867057576
 
SQL: 
select * from tibero.sph_test where c1=8629
================================================================================
 
Plan Hash Value :    808101735
User ID         :            0
Schema ID       :            0
 
Modified Parameters : 
    _OPT_ADJUST_TABLE_PRED_SELECTIVITY=100
    _OPT_BIND_PEEKING="NO"
 
Plan Statistics
    Executions       :                   8
    Fetches          :                   8
    Optimizer cost   :                  83
    Buffer gets      :                1328 (AVG:              166.00)
    Rows processed   :                   8 (AVG:                1.00)
    Elapsed time(us) :               30773 (AVG:             3846.63)
                           00:00:00.030773 (AVG:     00:00:00.003846)
    First load       : 2025/11/04
    Last execution   : 2025/11/04
 
SQL Hash Value: 3867057576
Plan Hash Value: 808101735
 
Execution Plan
--------------------------------------------------------------------------------
   1  TABLE ACCESS (FULL): SPH_TEST (Cost:83, %CPU:2, Rows:1) 
 
Predicate Information
--------------------------------------------------------------------------------
   1 - filter: ("SPH_TEST"."C1" = 8629) (0.000)
--------------------------------------------------------------------------------
 
No bind variables captured.
 
Plan Hash Value :   3929546620
User ID         :            0
Schema ID       :            0
 
Modified Parameters : 
    _OPT_ADJUST_TABLE_PRED_SELECTIVITY=100
    _OPT_BIND_PEEKING="NO"
 
Plan Statistics
    Executions       :                   4
    Fetches          :                   4
    Optimizer cost   :                   2
    Buffer gets      :                   8 (AVG:                2.00)
    Rows processed   :                   4 (AVG:                1.00)
    Elapsed time(us) :                 656 (AVG:              164.00)
                           00:00:00.000656 (AVG:     00:00:00.000164)
    First load       : 2025/11/04
    Last execution   : 2025/11/04
 
SQL Hash Value: 3867057576
Plan Hash Value: 3929546620
 
Execution Plan
--------------------------------------------------------------------------------
   1  COLUMN PROJECTION (Cost:2, %CPU:0, Rows:1) 
   2    INDEX (UNIQUE SCAN): _TIBERO_CON56100334 (Cost:2, %CPU:0, Rows:1) 
 
Predicate Information
--------------------------------------------------------------------------------
   2 - access: ("SPH_TEST"."C1" = 8629) (0.000)
--------------------------------------------------------------------------------
 
No bind variables captured.
 
 

PSM completed.

How to Use

1. DBMS_SPH.UPDATE_PLAN_HISTORY

Stores execution plans from the library cache into SPH.

PROCEDURE UPDATE_PLAN_HISTORY 
(
    MIN_EXEC_COUNT    IN PLS_INTEGER  DEFAULT 1
);

Example

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

2. DBMS_SPH.REPORT_PLANS

PROCEDURE REPORT_PLANS 
(
   SQL_HASH_VALUE  IN NUMBER,  -- The HASH VALUE of the SQL to report.
   DURATION          IN PLS_INTEGER    DEFAULT 24*365*1000,    -- The period to output the SQL history. This indicates the start point; the end point is the current time. Unit is hours (h).
   TO_FILE           IN BOOLEAN        DEFAULT TRUE  -- Whether to save the report content to a file.
);

SQL> set serveroutput on
SQL> exec dbms_sph.report_plans('HASH_VALUE', 24, FALSE); 
           -- Output all execution plans for one day on the screen

Note

When you want output on the screen (when the TO_FILE parameter is FALSE), you must enable serveroutput before execution.

When you want output to a file (when the TO_FILE parameter is TRUE), it is saved at the following path: $TB_HOME/instance/$TB_SID/dump/report/sph_report.{mthr_pid}.{current_time}

3. DBMS_SPH.REPORT_PLANS_BY_DATE

Outputs the execution plan change history for a specified SQL. 
Functions the same as REPORT_PLANS but differs only in parameter types.

PROCEDURE REPORT_PLANS_BY_DATE 
(
    SQL_HASH_VALUE  IN NUMBER, -- The HASH VALUE of the SQL to report.
    START_DATE        IN DATE       DEFAULT SYSDATE - 365*1000,  -- The period to output the SQL history. This indicates the start point; the end point is the current time. Unit is days (d).
    TO_FILE           IN BOOLEAN    DEFAULT TRUE -- Whether to save the report content to a file.
);

Example

SQL> set serveroutput on
SQL> exec dbms_sph.report_plans_by_date('HASH_VALUE', sysdate - 1, FALSE); 
            -- Output all execution plans for one day on the screen

4. DBMS_SPH.REPORT_PLAN_HISTORY

Outputs the execution plan change history for all SQLs that meet the conditions.

PROCEDURE REPORT_PLAN_HISTORY 
(
    DURATION          IN PLS_INTEGER  DEFAULT 24*365*1000, -- The period to output SQL history. This indicates the start point; the end point is the current time. Unit is hours (h).
    MIN_PLAN_COUNT    IN PLS_INTEGER  DEFAULT 1, -- The minimum number of execution plans for the SQLs to output. For example, if set to 2, only SQLs with two or more execution plans will be output.
    TO_FILE           IN BOOLEAN      DEFAULT TRUE -- Whether to save the report content to a file.
);

Example

SQL> set serveroutput on
SQL> exec dbms_sph.report_plan_history(24, 1, FALSE);
                -- Output all execution plans for all SQLs executed during one day on the screen

5. DBMS_SPH.REPORT_PLAN_HISTORY_BY_DATE

PROCEDURE REPORT_PLAN_HISTORY_BY_DATE 
(
    START_DATE        IN DATE         DEFAULT SYSDATE - 365*1000, -- The period to output SQL history. This indicates the start point; the end point is the current time. Unit is days (d).
    MIN_PLAN_COUNT    IN PLS_INTEGER  DEFAULT 1, -- The minimum number of execution plans for the SQLs to output. For example, if set to 2, only SQLs with two or more execution plans will be output.
    TO_FILE           IN BOOLEAN      DEFAULT TRUE -- Whether to save the report content to a file.
);

Example

SQL> set serveroutput on
SQL> exec dbms_sph.report_plan_history_by_date(sysdate - 1, 1, FALSE); 
                 -- Output all execution plans for all SQLs executed during one day on the screen

6. DBMS_SPH.TRUNCATE_PLAN_HISTORY

PROCEDURE TRUNCATE_PLAN_HISTORY 
(
    RETENTION_PERIOD  IN PLS_INTEGER  DEFAULT 24*365*1000 -- Specifies the period for deleting SQL history. Execution plans not executed during this period are all deleted. Unit is hours (h).
    MAX_COUNT         IN PLS_INTEGER  DEFAULT 1000000 -- The number of SQLs whose execution plans are retained without deletion.
);

Example

SQL> exec dbms_sph.truncate_plan_history(24); 
            -- Delete all execution plans not executed during one day

Note

If both RETENTION_PERIOD and MAX_COUNT are specified, only SQLs that satisfy both conditions will be retained.

7. DBMS_SPH.TRUNCATE_PLAN_HISTORY_BY_DATE

Deletes execution plan histories stored in SPH. 
Functions the same as TRUNCATE_PLAN_HISTORY but differs only in parameters.

PROCEDURE TRUNCATE_PLAN_HISTORY_BY_DATE 
(
    START_DATE        IN DATE         DEFAULT SYSDATE - 365*1000 -- The cutoff date for deleting SQL history. All execution plan information before this date will be deleted. Unit is days (d).
    MAX_COUNT         IN PLS_INTEGER  DEFAULT 1000000 -- The number of SQLs whose execution plans are retained without deletion.
);

Example

SQL> exec dbms_sph.truncate_plan_history_by_date(sysdate - 1); 
            -- Delete all execution plans not executed during one day

Note

If both START_DATE and MAX_COUNT are specified, only SQLs that satisfy both conditions will be retained.