Skip to main content

Search

Welcome to Tibero GTS!

How to Use the SQL Plan History Feature (SPH)

Document Type | Technical Information

Category | Monitoring/Inspection

Document Number | TMOTI033

 

Overview

This guide explains how to use the SQL Plan History (hereafter SPH) feature.
SPH is a feature that stores and manages the history of plans required for query execution. (It is similar to Oracle's SQL Plan Baseline feature.)
 
The reasons why SPH is necessary are as follows.
  • Queries that run with an optimal plan may experience plan changes due to various reasons (side effects of parameter changes, impact after patches, optimizer misjudgments after statistics updates, etc.), which can cause query performance degradation.
  • Plans stored in cache can be evicted due to reasons such as restarts or the LRU algorithm, leading to situations where previous plans cannot be retrieved again.
 
 

Method

How to Use SPH

Use the DBMS_SPH package to save and query plans.
  • UPDATE_PLAN_HISTORY(N) : Saves plans from the current Library cache that have been executed at least N times.
  • REPORT_PLANS(), REPORT_PLAN_HISTORY().. : Queries the plan change history.
  • SYS._SPH, SYS._SPH_TEXT, SYS._PLAN, SYS._SPH_VARIABLES, SYS._SPH_IPARAMS : Related tables.
 

SPH Usage Example

The scenario is as follows.
create table tibero.sph_test(c1 number);
insert into tibero.sph_test select level from dual connect by level < 100000;
commit;

select * from tibero.sph_test where c1=8629; -- executed 4 times
/*
set autot on
SQL ID: 516a56bm7x7d8
Child number: 82
Plan hash value: 1698064395
*/

exec dbms_sph.update_plan_history(2);

SELECT * FROM SYS._SPH WHERE PLAN_HASH_VALUE='1698064395'; -- Check if stored in SPH table

alter table tibero.sph_test add primary key (c1); -- Plan change

select * from tibero.sph_test where c1=8629; -- executed 4 times
/*
set autot on
SQL ID: 516a56bm7x7d8
Child number: 111
Plan hash value: 854204648
*/

exec dbms_sph.update_plan_history(2);

SELECT * FROM SYS._SPH WHERE PLAN_HASH_VALUE='854204648'; -- Check if stored in SPH table

select SQL_HASH_VALUE from sys._sph where PLAN_HASH_VALUE='1384442108'; 
--SQL_HASH_VALUE : 3867057576
select SQL_HASH_VALUE from sys._sph where PLAN_HASH_VALUE='423184405';   
--SQL_HASH_VALUE : 3867057576

set serveroutput on

exec dbms_sph.report_plans(3867057576, 24, FALSE); --SQL_HASH_VALUE, DURATION, TO_FILE

 

The output results are as follows.

 

  • Plan changes are displayed in reverse chronological order.
  • After applying a primary key constraint to the table, the execution plan changes to use an index scan.
  • The execution plan initially uses a table full scan right after the table is created.

 
 

Related to