Document Type | Technical Information
Category | Tuning
Applicable Product Version | Tibero7.2.4
Document Number | TTUTI028
Overview
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.
- Execution plan performing a table full scan immediately after table creation
- 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. );
Example
SQL> set serveroutput on
SQL> exec dbms_sph.report_plans('HASH_VALUE', 24, FALSE);
-- Output all execution plans for one day on the screenNote
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
Functions the same as REPORT_PLAN_HISTORY but differs only in parameter types.
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 dayNote
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 dayNote
If both START_DATE and MAX_COUNT are specified, only SQLs that satisfy both conditions will be retained.