Skip to main content

Search

Welcome to Tibero GTS!

How to Delete SQL Execution Plans

Document Type | Technical Information

Category | Tuning

Applicable Product Versions | 6FS06, 6FS07, 6FS07PS

Document Number | TTUTI006

 

Overview

This is a method to delete SQL execution plans stored in the DB memory, similar to Oracle's DBMS_SHARED_POOL.PURGE package function.
 

Method

In Tibero, the command ALTER SYSTEM FLUSH PPC; is used to delete execution plans.
 
The usage method is as follows: 
alter system flush ppc <child_number> '<sql_id>';

 

1. Check the physical plan to be deleted (SQL_ID: f6kwymknrt7z1)
SQL>
SET AUTOT ON
SET ROWS OFF
SELECT COUNT(*) FROM TAB;

1 row selected.

SQL ID: f6kwymknrt7z1
Child number: 28738
Plan hash value: 3443268965

Execution Plan
--------------------------------------------------------------------------------
  1  COLUMN PROJECTION (Cost:40, %%CPU:0, Rows:1)
  2    SORT AGGR (Cost:40, %%CPU:0, Rows:1)
  3      INDEX JOIN (Cost:40, %%CPU:0, Rows:30)
  4        TABLE ACCESS (ROWID): _DD_OBJ (Cost:39, %%CPU:0, Rows:223)
  5          INDEX (RANGE SCAN): _DD_OBJ_IDX2 (Cost:6, %%CPU:0, Rows:540)
  6        FILTER (Cost:2, %%CPU:0, Rows:1)
  7          INDEX (UNIQUE SCAN): _SYS_CON13700693 (Cost:2, %%CPU:0, Rows:1)


Predicate Information
--------------------------------------------------------------------------------
  4 - filter: (("_DD_OBJ"."TYPE_NO") IN ((1),(2),(3))) (0.107)
  5 - access: ("_DD_OBJ"."OWNER_ID" = USERENV('SCHEMAID')) (0.035)
  6 - filter: (("_DD_OBJ"."TYPE_NO") IN ((1),(2),(3))) (0.107)
  7 - access: ("T"."TYPE_NO" = "_DD_OBJ"."TYPE_NO") (0.048)

NAME                                VALUE
------------------------------ ----------
db block gets                           0
consistent gets                       191
physical reads                          0
redo size                               0
sorts (disk)                            0
sorts (memory)                          7
rows processed                          1
 
2.  Query V$SQL to check the existence of the SQL_ID
SQL> 
SET AUTOT OFF
SET ROWS ON
SELECT SQL_ID, CHILD_NUMBER, SQL_TEXT FROM V$SQL WHERE SQL_ID = 'f6kwymknrt7z1';

SQL_ID        CHILD_NUMBER SQL_TEXT
------------- ------------ --------------------------------------------------
f6kwymknrt7z1        28738 SELECT COUNT(*) FROM TAB
 
3.  Delete the physical plan with SQL_ID: f6kwymknrt7z1
SQL>
ALTER SYSTEM FLUSH PPC 28738 'f6kwymknrt7z1';

System altered.
 
4.  Verify deletion by querying V$SQL
Since it has been deleted, it will show as not existing.
SQL> 
SELECT SQL_ID, CHILD_NUMBER, SQL_TEXT FROM V$SQL WHERE SQL_ID = 'f6kwymknrt7z1';

0 row selected.
Caution
1. Plans generated on node 1 must be deleted by executing the below SQL statement on node 1.
alter system flush ppc <child_number> '<sql_id>';
 
2. If you execute sql_id and child_number omitted, just alter system flush ppc; is run, all plans will be deleted. Therefore, do not execute alter system flush ppc; when using this command.
 
 
 

Related to