понедельник, 19 января 2015 г.

SQL Plan Management

По умолчанию параметры такие:

ALTER SYSTEM SET OPTIMIZER_USE_SQL_PLAN_BASELINES = TRUE;
ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE;


-- Сбросим пул:

alter system flush shared_pool;

-- В SQL Management Base планов пока нет:

select signature, sql_handle, plan_name, enabled, accepted, fixed, sql_text from dba_sql_plan_baselines;

-- Если есть то удаляем:

DECLARE
my_plans PLS_INTEGER;
BEGIN
my_plans :=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_c938eaa3612ad683',plan_name=>null);
dbms_output.put_line('Value is '||my_plans);
END;
/

select signature, sql_handle, plan_name, enabled, accepted, fixed, sql_text from dba_sql_plan_baselines;


-- Создаём таблицу

drop table t;
create table t as select * from all_objects;

-- Выполняем запрос

select distinct object_name from t;

-- Находим sql_id запроса:

SELECT sql_id, child_number, plan_hash_value, hash_value, address, sql_text
FROM v$sql
WHERE sql_text LIKE 'select distinct object_name from t%';

748bpm0yk2x76    0    1793979440    1025602790    000000009D9141C0    select distinct object_name from t

-- Получаем план запроса
select * from table (dbms_xplan.display_cursor('748bpm0yk2x76',0,'ADVANCED'));

SQL_ID  748bpm0yk2x76, child number 0
-------------------------------------
select distinct object_name from t

Plan hash value: 1793979440

-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |       |   857 (100)|          |
|   1 |  HASH UNIQUE       |      | 51024 |  1245K|  2800K|   857   (1)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T    | 88914 |  2170K|       |   388   (1)| 00:00:01 |
-----------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / T@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      DB_VERSION('12.1.0.2')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T"@"SEL$1")
      USE_HASH_AGGREGATION(@"SEL$1")
      END_OUTLINE_DATA
  */

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "OBJECT_NAME"[VARCHAR2,128]
   2 - (rowset=200) "OBJECT_NAME"[VARCHAR2,128]


-- И загружаем план  в baseline:

DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
sql_id => '748bpm0yk2x76',
PLAN_HASH_VALUE =>1793979440,
FIXED =>'NO');
dbms_output.put_line('Value is '||my_plans);
END;
/

-- План запроса появился в SQL Management Base и baseline:

select signature, sql_handle, plan_name, enabled, accepted, fixed, sql_text from dba_sql_plan_baselines;

14499596987750078083    SQL_c938eaa3612ad683    SQL_PLAN_ckf7andhkppn3bba443c6    YES    YES    NO    select distinct object_name from t


-- Создаем индекс:

create index t_idx on t(object_name);


-- Выполняем запрос:

select distinct object_name from t;


-- Новый план запроса появился в SQL Management Base:

select signature, sql_handle, plan_name, enabled, accepted, fixed, sql_text from dba_sql_plan_baselines;

14499596987750078083    SQL_c938eaa3612ad683    SQL_PLAN_ckf7andhkppn37883a793    YES    NO    NO    select distinct object_name from t
14499596987750078083    SQL_c938eaa3612ad683    SQL_PLAN_ckf7andhkppn3bba443c6    YES    YES    NO    select distinct object_name from t

Оптимизатор построил новый план для данного запроса, занес этот план в историю, но запрос выполнил по старому плану из baseline


-- Посмотрим на  план:

SELECT sql_id, child_number, plan_hash_value, hash_value, address, sql_text
FROM v$sql
WHERE sql_text LIKE 'select distinct object_name from t%';

748bpm0yk2x76    1    1793979440    1025602790    000000009D9141C0    select distinct object_name from t


select * from table (dbms_xplan.display_cursor('748bpm0yk2x76',1,'ADVANCED'));

SQL_ID: 748bpm0yk2x76, child number: 1 cannot be found

-- Повторно выполняем запрос:

select distinct object_name from t;

-- И смотрим план:

select * from table (dbms_xplan.display_cursor('748bpm0yk2x76',1,'ADVANCED'));


SQL_ID  748bpm0yk2x76, child number 1
-------------------------------------
select distinct object_name from t

Plan hash value: 1793979440

-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |       |   857 (100)|          |
|   1 |  HASH UNIQUE       |      | 51024 |  1245K|  2800K|   857   (1)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T    | 88914 |  2170K|       |   388   (1)| 00:00:01 |
-----------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / T@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      DB_VERSION('12.1.0.2')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T"@"SEL$1")
      USE_HASH_AGGREGATION(@"SEL$1")
      END_OUTLINE_DATA
  */

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "OBJECT_NAME"[VARCHAR2,128]
   2 - (rowset=200) "OBJECT_NAME"[VARCHAR2,128]

Note
-----
   - SQL plan baseline SQL_PLAN_ckf7andhkppn3bba443c6 used for this statement


Теперь в shared pool этот план уже был, но запрос по прежнему был выполнен по старому плану из baseline.


Т.е. при включенном управлении планами, планы  выполнения берутся для всех запросов из baseline, если они там есть.


-- Отключим управление планами:

alter session set optimizer_use_sql_plan_baselines = false;

-- Выполним запрос:

select distinct object_name from t;


-- И посмотрим на план:

SELECT sql_id, child_number, plan_hash_value, hash_value, address, sql_text
FROM v$sql
WHERE sql_text LIKE 'select distinct object_name from t%';

748bpm0yk2x76    0    1741570181    1025602790    000000009FEE5A80    select distinct object_name from t


select * from table (dbms_xplan.display_cursor('748bpm0yk2x76',0,'ADVANCED'));

SQL_ID  748bpm0yk2x76, child number 0
-------------------------------------
select distinct object_name from t

Plan hash value: 1741570181

---------------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |       |       |       |   592 (100)|          |
|   1 |  HASH UNIQUE          |       | 51024 |  1245K|  2800K|   592   (2)| 00:00:01 |
|   2 |   INDEX FAST FULL SCAN| T_IDX | 88914 |  2170K|       |   123   (1)| 00:00:01 |
---------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / T@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      DB_VERSION('12.1.0.2')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_FFS(@"SEL$1" "T"@"SEL$1" ("T"."OBJECT_NAME"))
      USE_HASH_AGGREGATION(@"SEL$1")
      END_OUTLINE_DATA
  */

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "OBJECT_NAME"[VARCHAR2,128]
   2 - "OBJECT_NAME"[VARCHAR2,128]


Теперь запрос отработал уже по новому плану с учетом индекса.


-- Вернем управление планами:

alter session set optimizer_use_sql_plan_baselines = true;


-- Посмотрим что у нас в SQL Management Base:

select signature, sql_handle, plan_name, enabled, accepted, fixed, sql_text from dba_sql_plan_baselines;

14499596987750078083    SQL_c938eaa3612ad683    SQL_PLAN_ckf7andhkppn37883a793    YES    NO    NO    select distinct object_name from t
14499596987750078083    SQL_c938eaa3612ad683    SQL_PLAN_ckf7andhkppn3bba443c6    YES    YES    NO    select distinct object_name from t

запрос вернул две строки, одна с accepted = no и вторая с accepted = yes


Все планы с признаками  accepted = no, могут быть проанализированы оптимизатором и в зависимости от результата  проверки помещены в baseline
(если они окажутся по эффективности не хуже ранее там имеющихся):

-- Используем для этого функцию DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE

DECLARE
report clob;
BEGIN
report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
sql_handle => 'SQL_c938eaa3612ad683', time_limit => 60);
DBMS_OUTPUT.PUT_LINE(report);
END;
/

GENERAL INFORMATION SECTION
---------------------------------------------------------------------------------------------

 Task Information:                            
 ---------------------------------------------
 Task Name            : ЗАДАЧА_751         
 Task Owner           : SCOTT              
 Execution Name       : EXEC_801           
 Execution Type       : SPM EVOLVE         
 Scope                : COMPREHENSIVE      
 Status               : COMPLETED          
 Started              : 01/19/2015 10:35:10
 Finished             : 01/19/2015 10:35:11
 Last Updated         : 01/19/2015 10:35:11
 Global Time Limit    : 60                 
 Per-Plan Time Limit  : UNUSED             
 Number of Errors     : 0                  
---------------------------------------------------------------------------------------------

SUMMARY SECTION
---------------------------------------------------------------------------------------------
  Number of plans processed  : 1 
  Number of findings         : 2 
  Number of recommendations  : 1 
  Number of errors           : 0 
---------------------------------------------------------------------------------------------

DETAILS SECTION
---------------------------------------------------------------------------------------------
 Object ID          : 2                                                        
 Test Plan Name     : SQL_PLAN_ckf7andhkppn37883a793                           
 Base Plan Name     : SQL_PLAN_ckf7andhkppn3bba443c6                           
 SQL Handle         : SQL_c938eaa3612ad683                                     
 Parsing Schema     : SCOTT                                                    
 Test Plan Creator  : SCOTT                                                    
 SQL Text           : select distinct object_name from t                       

Execution Statistics:
-----------------------------
                    Base Plan                     Test Plan                   
                    ----------------------------  ----------------------------
 Elapsed Time (s):  .002383                       .002215                     
 CPU Time (s):      .001911                       .001767                     
 Buffer Gets:       139                           45                          
 Optimizer Cost:    857                           592                         
 Disk Reads:        0                             0                           
 Direct Writes:     0                             0                           
 Rows Processed:    5138                          5138                        
 Executions:        10                            10                          


FINDINGS SECTION
---------------------------------------------------------------------------------------------

Findings (2):
-----------------------------
 1. Проверка плана длилась 0.56000 секунд. План прошел проверку на критерий    
    преимущества, поскольку проверенный уровень производительности был в       
    3.06716 раз лучше основного плана.                                         
 2. План был автоматически принят.                                             

Recommendation:
-----------------------------
 Consider accepting the plan.                                                  


EXPLAIN PLANS SECTION
---------------------------------------------------------------------------------------------

Baseline Plan
-----------------------------
 Plan Id          : 3901      
 Plan Hash Value  : 3148104646

------------------------------------------------------------------------
| Id | Operation            | Name | Rows  | Bytes   | Cost | Time     |
------------------------------------------------------------------------
|  0 | SELECT STATEMENT     |      | 51024 | 1275600 |  857 | 00:00:01 |
|  1 |   HASH UNIQUE        |      | 51024 | 1275600 |  857 | 00:00:01 |
|  2 |    TABLE ACCESS FULL | T    | 88914 | 2222850 |  388 | 00:00:01 |
------------------------------------------------------------------------

Test Plan
-----------------------------
 Plan Id          : 3902      
 Plan Hash Value  : 2021894035

----------------------------------------------------------------------------
| Id | Operation               | Name  | Rows  | Bytes   | Cost | Time     |
----------------------------------------------------------------------------
|  0 | SELECT STATEMENT        |       | 51024 | 1275600 |  592 | 00:00:01 |
|  1 |   HASH UNIQUE           |       | 51024 | 1275600 |  592 | 00:00:01 |
|  2 |    INDEX FAST FULL SCAN | T_IDX | 88914 | 2222850 |  123 | 00:00:01 |
----------------------------------------------------------------------------
---------------------------------------------------------------------------------------------


-- Смотрим планы в SQL Management Base:

select signature, sql_handle, plan_name, enabled, accepted, fixed, sql_text from dba_sql_plan_baselines;

14499596987750078083    SQL_c938eaa3612ad683    SQL_PLAN_ckf7andhkppn37883a793    YES    YES    NO    select distinct object_name from t
14499596987750078083    SQL_c938eaa3612ad683    SQL_PLAN_ckf7andhkppn3bba443c6    YES    YES    NO    select distinct object_name from t


Новый план после верификации был включен в baseline.


-- Выполняем запрос:

select distinct object_name from t;

-- Посмотрим на  план:

SELECT sql_id, child_number, plan_hash_value, hash_value, address, sql_text
FROM v$sql
WHERE sql_text LIKE 'select distinct object_name from t%';


748bpm0yk2x76    0    1741570181    1025602790    000000009BB9F680    select distinct object_name from t


select * from table (dbms_xplan.display_cursor('748bpm0yk2x76',0,'ADVANCED'));


SQL_ID  748bpm0yk2x76, child number 0
-------------------------------------
select distinct object_name from t

Plan hash value: 1741570181

---------------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |       |       |       |   592 (100)|          |
|   1 |  HASH UNIQUE          |       | 51024 |  1245K|  2800K|   592   (2)| 00:00:01 |
|   2 |   INDEX FAST FULL SCAN| T_IDX | 88914 |  2170K|       |   123   (1)| 00:00:01 |
---------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / T@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      DB_VERSION('12.1.0.2')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_FFS(@"SEL$1" "T"@"SEL$1" ("T"."OBJECT_NAME"))
      USE_HASH_AGGREGATION(@"SEL$1")
      END_OUTLINE_DATA
  */

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "OBJECT_NAME"[VARCHAR2,128]
   2 - "OBJECT_NAME"[VARCHAR2,128]

Note
-----
   - SQL plan baseline SQL_PLAN_ckf7andhkppn37883a793 used for this statement




-- Получать планы из baseline теперь можно так:


DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE (
   sql_handle      IN VARCHAR2 := NULL,
   plan_name       IN VARCHAR2 := NULL,
   format          IN VARCHAR2 := 'TYPICAL')
 RETURN dbms_xplan_type_table;


select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_c938eaa3612ad683'));
select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_c938eaa3612ad683',format=>'BASIC'));
select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_c938eaa3612ad683',format=>'ALL -projection'));
select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_c938eaa3612ad683',format=>'ALL +peeked_binds'));
select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_c938eaa3612ad683',format=>'ALLSTATS'));
select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_c938eaa3612ad683',format=>'ALLSTATS LAST'));
select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_c938eaa3612ad683',format=>'ALLSTATS LAST +alias -predicate'));
select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_c938eaa3612ad683',format=>'ALLSTATS LAST +outline'));
select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_c938eaa3612ad683',format=>'ADVANCED'));
select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_c938eaa3612ad683',format=>'ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS '));
select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_c938eaa3612ad683',plan_name=>'SQL_PLAN_ckf7andhkppn3bba443c6',format=>'ADVANCED'));


-- Пополнение baseline новыми планами:


Мы уже добавляли новые планы в baseline используя функцию  DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE


Еще планы можно загружать в baseline без предварительной проверки их эффективности:

-- Например можно загрузить планы из STS:

SELECT sqlset_name, count(*) FROM  DBA_SQLSET_STATEMENTS
group by sqlset_name;

new_sts    66
my_sts    212


DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
SQLSET_NAME => 'my_sts'
);
dbms_output.put_line('Value is '||my_plans);
END;
/

select signature, sql_handle, plan_name, enabled, accepted, fixed, sql_text from dba_sql_plan_baselines;


228404646742146688    SQL_032b74d0ea1d3e80    SQL_PLAN_06avnu3p1ugn07f2c8b72    YES    YES    NO    SELECT COUNT(*) FROM DBA_SCHEDULER_WINDOWS A, DBA_SCHEDULER_WINGROUP_MEMBERS B WHERE ENABLED = 'TRUE' AND ACTIVE = 'TRUE' AND A.WINDOW_NAME = B.WINDOW_NAME AND B.WINDOW_GROUP_NAME = 'MAINTENANCE_WINDOW_GROUP'
228404646742146688    SQL_032b74d0ea1d3e80    SQL_PLAN_06avnu3p1ugn0fa6e684e    YES    YES    NO    SELECT COUNT(*) FROM DBA_SCHEDULER_WINDOWS A, DBA_SCHEDULER_WINGROUP_MEMBERS B WHERE ENABLED = 'TRUE' AND ACTIVE = 'TRUE' AND A.WINDOW_NAME = B.WINDOW_NAME AND B.WINDOW_GROUP_NAME = 'MAINTENANCE_WINDOW_GROUP'
271831168975187047    SQL_03c5bd0209a52c67    SQL_PLAN_07jdx084uab37df05bb92    YES    YES    NO    CREATE TABLE map_tab
616121024526838780    SQL_088ce6ceb5926bfc    SQL_PLAN_0j376tuut4uzw5c154e55    YES    YES    NO    update ind$ set ts#=:2,file#=:3,block#=:4,intcols=:5,type#=:6,flags=:7,property=:8,pctfree$=:9,initrans=:10,maxtrans=:11,blevel=:12,leafcnt=:13,distkey=:14,lblkkey=:15,dblkkey=:16,clufac=:17,cols=:18,analyzetime=:19,samplesize=:20,dataobj#=:21,degree=decode(:22,1,null,:22),instances=decode(:23,1,null,:23),rowcnt=:24,pctthres$=:31*256+:25, indmethod#=:26, trunccnt=:27,evaledition#=decode(:33,1,null,:33),unusablebefore#=decode(:34,0,null,:34),unusablebeginning#=decode(:35,0,null,:35),spare4=:29,spare2=:30,spare6=:32 where obj#=:1
734444955242386636    SQL_0a3145cba84cd4cc    SQL_PLAN_0nca5tfn4tp6caf4c254a    YES    YES    NO    select order#,columns,types from access$ where d_obj#=:1
764063482407431782    SQL_0a9a7fb066be2e66    SQL_PLAN_0p6mzq1mbwbm68dd47f8f    YES    YES    NO    SELECT CONTROLFILE_TYPE FROM V$DATABASE
908110696082932625    SQL_0c9a41dde0c18391    SQL_PLAN_0t6k1vrhc30wj02acaae9    YES    YES    NO    SELECT AO.ATTR1 OBJD, SUM(AR.BENEFIT) AS BENEFIT FROM DBA_ADVISOR_ACTIONS AA, DBA_ADVISOR_OBJECTS AO, DBA_ADVISOR_RECOMMENDATIONS AR WHERE AA.TASK_ID IN ( SELECT TASK_ID FROM DBA_ADVISOR_TASKS WHERE ADVISOR_ID=1 AND EXECUTION_START > :B1 AND STATUS='COMPLETED' AND HOW_CREATED='AUTO') AND AA.COMMAND = 'RUN SEGMENT ADVISOR' AND AA.TASK_ID = AO.TASK_ID AND AA.OBJECT_ID = AO.OBJECT_ID AND AA.TASK_ID = AR.TASK_ID AND AA.REC_ID = AR.REC_ID GROUP BY AO.ATTR1 ORDER BY 2 DESC
........


-- В baseline можно загружать планы из stage таблицы:


-- Create staging table

BEGIN
DBMS_SPM.CREATE_STGTAB_BASELINE(
table_name => 'STG_TAB_BASELINE',
table_owner => 'SCOTT',
tablespace_name => 'USERS' );
END;
/


--Pack Baseline in staging table

SET SERVEROUTPUT ON
DECLARE
l_plans_packed PLS_INTEGER;
BEGIN
l_plans_packed := DBMS_SPM.pack_stgtab_baseline(
table_name => 'STG_TAB_BASELINE',
table_owner => 'SCOTT');

DBMS_OUTPUT.put_line('Plans Packed: ' || l_plans_packed);
END;
/

-- Проверим:

select * from stg_tab_baseline;


-- Удалим все планы из baseline:

DECLARE
res PLS_INTEGER;
BEGIN
FOR n IN (SELECT distinct plan_name FROM dba_sql_plan_baselines) LOOP
         DBMS_OUTPUT.PUT_LINE('Deleting ' || n.plan_name);
         res := dbms_spm.drop_sql_plan_baseline(sql_handle=>NULL, plan_name=> n.plan_name);
END LOOP;
END;
/


-- Проверим:

select signature, sql_handle, plan_name, enabled, accepted, fixed, sql_text from dba_sql_plan_baselines;


-- Unpack staging table into SPM

SET SERVEROUTPUT ON
DECLARE
l_plans_unpacked PLS_INTEGER;
BEGIN
l_plans_unpacked := DBMS_SPM.unpack_stgtab_baseline(
table_name => 'STG_TAB_BASELINE',
table_owner => 'SCOTT',
creator => 'SCOTT');

DBMS_OUTPUT.put_line('Plans Unpacked: ' || l_plans_unpacked);
END;
/


-- Проверим:

select signature, sql_handle, plan_name, enabled, accepted, fixed, sql_text from dba_sql_plan_baselines;



-- Можно включить режим автоматического захвата новых планов в baseline:

ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;

При этом все новые планы в CURSOR_CACHE станут потенциальными кандидатами на пополнение baseline.

Новый планы будут добавлены в Plan History но не будут использованы (не попадут в baseline) до тех пор, пока не будут проверены на производительность.
SQL Tuning Advisor будет их автоматически проверять и добавлять в baseline.


-- Можно включать автоматический захват на уровне сессии:

-- Включили

ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;


-- Выполнили запрос:

select distinct object_name from t;

-- Отключили

ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE;



-- Чтобы узнать каким путем планы попали в baseline нужно сделать запрос:

select signature, sql_handle, plan_name, enabled, accepted, fixed, origin from dba_sql_plan_baselines;

8469865103426906748    SQL_758b016fb13cea7c    SQL_PLAN_7b2s1dysmtumwb29d3e60    YES    YES    NO    MANUAL-LOAD
8469865103426906748    SQL_758b016fb13cea7c    SQL_PLAN_7b2s1dysmtumw7b7fa22d    YES    NO    NO    AUTO-CAPTURE

Из поля origin мы узнаем как был добавлен план.

Также в baseline попадают планы для которых DBA, согласно рекомендациям SQL Tuning Advisor принимает профили.

Информация baseline хранится в компоненте SMB (SQL Management Base), который расположен в табличном пространстве SYSAUX.

-- Размером SMB можно управлять с помощью параметров:

SELECT parameter_name, parameter_value
FROM   dba_sql_management_config;

PARAMETER_NAME                 PARAMETER_VALUE
------------------------------ ---------------
SPACE_BUDGET_PERCENT                        10
PLAN_RETENTION_WEEKS                        53


SPACE_BUDGET_PERCENT - сколько места в процентах в ТП SYSAUX  может занимать SMB

PLAN_RETENTION_WEEKS - сколько недель хранить планы


-- Изменять эти параметры можно так:

BEGIN
  DBMS_SPM.configure('space_budget_percent', 11);
  DBMS_SPM.configure('plan_retention_weeks', 54);
END;
/



Можно попытаться загрузить в baseline текст некоторого запроса, а план для него загрузить от другого запроса.

dbms_spm.load_plans_from_cursor_cache(

sql_id                    => sql statement a,
plan_hash_value  => sql statement a,
sql_text                => sql statement b

)

Иногда это может сработать.


Рассмотрим примеры подмены планов.

По умолчанию параметры такие:

ALTER SYSTEM SET OPTIMIZER_USE_SQL_PLAN_BASELINES = TRUE;
ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE;


SPM  - SQL Plan Management

SMB  - SQL Management Base

Какие планы  попали в SQL Management Base и baseline ?

select signature,
       sql_handle,
       plan_name,
       enabled,
       accepted,
       fixed,
       sql_text
from dba_sql_plan_baselines;



signature    14499596987750078083  
sql_handle   SQL_c938eaa3612ad683  
plan_name    SQL_PLAN_ckf7andhkppn3bba443c6  
enabled      YES   (План попал в SMB)
accepted     YES   (План попал в baseline)
fixed        NO    (Новые планы будут добавляться в baseline)
sql_text     select distinct object_name from t


Ещё пример:

select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines;

SQL_HANDLE             PLAN_NAME                        ENABLED ACCEPTED FIXED
--------------------   ------------------------------   ------- -------- -----
SQL_c938eaa3612ad683   SQL_PLAN_ckf7andhkppn37883a793   YES           NO    NO
SQL_c938eaa3612ad683   SQL_PLAN_ckf7andhkppn3bba443c6   YES          YES    NO

План первого запроса попал только в SMB
План второго запроса попал в SMB и в baseline
Второй план также позволит добавлять новые планы в baseline

Посмотрим что у нас в shared pool.

SELECT sql_id,
       child_number,
       plan_hash_value,
       hash_value,
       address,
       sql_text
FROM v$sql
WHERE sql_text LIKE 'select distinct object_name from t%';


SQL_ID        CHILD_NUMBER PLAN_HASH_VALUE HASH_VALUE ADDRESS          SQL_TEXT
------------- ------------ --------------- ---------- ---------------- ----------------------------------
748bpm0yk2x76 0            1741570181      1025602790 000000009BB9F680 select distinct object_name from t
748bpm0yk2x76 1            1793979440      1025602790 000000009D9141C0 select distinct object_name from t

Как видим, SQL_ID запроса в кэше курсоров соответствует SQL_HANDLE в SMB
a PLAN_HASH_VALUE запроса в кэше курсоров соответствует PLAN_NAME в SMB


Загрузить первый план нового запроса в SMB можно так:

DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
 sql_id => '748bpm0yk2x76',
 PLAN_HASH_VALUE =>1793979440);
dbms_output.put_line('Value is '||my_plans);
END;
/

Первый план нового запроса попадет в SMB и в baseline.
После этого все новые планы данного запроса будут автоматически добавляться в SMB (но не в baseline).


Включить план, находящийся в SMB в baseline можно двумя способами:

- с верификацией
- без верификации

С верификацией новый план запроса добавится в baseline только если он окажется по производительности
не хуже уже имеющегося плана.

Используем для этого функцию DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE:

DECLARE
report clob;
BEGIN
report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
sql_handle => 'SQL_c938eaa3612ad683', time_limit => 60);
DBMS_OUTPUT.PUT_LINE(report);
END;
/

Верификацией занимается Automatic SQL Tuning Advisor
Также будет выдан отчет о производительности планов запроса.


Включить план в baseline без верификации:

DECLARE rpt clob;
BEGIN
rpt := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
    sql_handle=>'SQL_758b016fb13cea7c',
    plan_name=>'SQL_PLAN_7b2s1dysmtumwb29d3e60',
    verify=>'NO',
    commit=>'YES'
);
DBMS_OUTPUT.PUT_LINE(rpt);
END;
/



Можно наоборот, провести верификацию плана чтобы посмотреть отчет,
но план в baseline не включать.

DECLARE
    rpt clob;
BEGIN
    rpt := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
        sql_handle=>'SQL_758b016fb13cea7c',
        plan_name=>'SQL_PLAN_7b2s1dysmtumwb29d3e60',
        verify=>'YES',
        commit=>'NO'
    );
DBMS_OUTPUT.PUT_LINE(rpt);
END;
/

commit=>'NO' означает, что  ненужно изменять ACCEPTED с NO на YES 
ACCEPTED - Признак того, что план включен в baseline как приемлемый.


По умолчанию:

verify=>'YES'
commit=>'YES'





Модификацию атрибутов планов можно производить так:


DECLARE
    ret number;
BEGIN
    ret := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
        sql_handle=>'SQL_758b016fb13cea7c',
        plan_name=>'SQL_PLAN_7b2s1dysmtumwb29d3e60',
        attribute_name=>'ENABLED',
        attribute_value=>'NO'
    );
DBMS_OUTPUT.PUT_LINE(ret);
END;
/


Отключить устаревшие планы в baseline
(оптимизатор будет игнорировать такой план
т.к. ENABLED - признак нахождения плана в рабочем состоянии)

attribute_name=>'ENABLED'
attribute_value=>'NO'


Автоматическое удаление плана из SMB автоматикой AWR
по прошествии установленного времени.

attribute_name=>'AUTOPURGE'
attribute_value=>'YES'


Описание плана.

attribute_name=>'DESCRIPTION'
attribute_value=>'Описание'


Для таких запросов новые планы больше не будут добваляться в baseline.

attribute_name=>'FIXED'
attribute_value=>'YES'



При добавлении новых планов их можно сразу фиксировать:

DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
 sql_id => '748bpm0yk2x76',
 PLAN_HASH_VALUE =>1793979440,
 FIXED =>'YES');
dbms_output.put_line('Value is '||my_plans);
END;
/

По умолчанию:

FIXED =>'NO'
ENABLED =>'YES'



Удалить план из SMB можно так:

DECLARE
my_plans PLS_INTEGER;
BEGIN
my_plans :=dbms_spm.drop_sql_plan_baseline(
 sql_handle=>'SQL_c938eaa3612ad683',
 plan_name=>null);
dbms_output.put_line('Value is '||my_plans);
END;
/



!!!Удалить все планы из baseline можно так:

DECLARE
res PLS_INTEGER;
BEGIN
FOR n IN (SELECT distinct plan_name FROM dba_sql_plan_baselines) LOOP
         DBMS_OUTPUT.PUT_LINE('Deleting ' || n.plan_name);
         res := dbms_spm.drop_sql_plan_baseline(sql_handle=>NULL, plan_name=> n.plan_name);
END LOOP;
END;
/


Рассмотрим примеры:


Выполним исходный запрос и посмотрим его план:

SQL> var A1 number
SQL> exec :A1 := 338746;

PL/SQL procedure successfully completed.

SQL> SELECT COL1 FROM T WHERE COL2 = :A1;

no rows selected

SQL> select * from table(dbms_xplan.display_cursor);




Загрузим план исходного запроса в SMB:

DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
 SQL_ID=>'SQL_ID',
 PLAN_HASH_VALUE=>'PLAN_HASH_VALUE');
END;
/

или так:

SQL> var res number ;
SQL> exec :res := dbms_spm.load_plans_from_cursor_cache( -
                  sql_id => '&sql_id', -
                  plan_hash_value => '&plan_hash_value' );

Enter value for sql_id: dljmr5z188i35
Enter value for plan_hash_value: 5422946733

PL/SQL procedure successfully completed.



Проверим, что он попал в SMB и baseline:

col origin format a15
col sql_handle format a21
select s.sql_id, s.plan_hash_value, b.sql_handle, b.plan_name,
       b.parsing_schema_name, b.optimizer_cost, b.origin,
       b.enabled, b.accepted, b.fixed, b.autopurge
from v$sql s, dba_sql_plan_baselines b
where s.exact_matching_signature = b.signature
and s.sql_id= 'SQL_ID';

и находим SQL_HANDLE


Оптимизируем исходный запрос, выполняем его и смотрим план:

SQL> var A1 number
SQL> exec :A1 := 338746;

PL/SQL procedure successfully completed.


SQL> SELECT /*+ INDEX() */ COL1 FROM T WHERE COL2 = :A1;

no rows selected

SQL> select * from table(dbms_xplan.display_cursor);


Находим:
OPT_SQL_ID и OPT_PLAN_HASH_VALUE для оптимизированного SQL запроса.



Ассоциируем план от оптимизированного SQL запроса с исходным SQL запросом:

DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
 SQL_ID=>'OPT_SQL_ID',
 PLAN_HASH_VALUE=>'OPT_PLAN_HASH_VALUE',
 SQL_HANDLE=>'SQL_HANDLE');
dbms_output.put_line('Number of plans loaded: ' || my_plans);
END;
/

или так:

SQL> var res number
SQL> exec :res := dbms_spm.load_plans_from_cursor_cache( -
     sql_id => '&OPT_SQL_ID', -
     plan_hash_value => &opt_plan_hash_value, -
     sql_handle => '&sql_handle');

Enter value for opt_sql_id: a13xgtdf6hjuy
Enter value for opt_plan_hash_value: 5678340957
Enter value for sql_handle: SQL_f45ty80gh34ft358

PL/SQL procedure successfully completed.



Проверить что оптимизированный план ассоциированный с исходным запросом
появился в SMB:

col origin format a15
col sql_handle format a21
select s.sql_id, s.plan_hash_value, b.sql_handle, b.plan_name,
       b.parsing_schema_name, b.optimizer_cost, b.origin,
       b.enabled, b.accepted, b.fixed, b.autopurge
from v$sql s, dba_sql_plan_baselines b
where s.exact_matching_signature = b.signature
  and s.sql_id= 'SQL_ID';


SQL> select sql_handle,sql_text, plan_name
     from dba_sql_plan_baselines
     where sql_text like '%COL1 FROM T WHERE%';


SQL_HANDLE            SQL_TEXT                             PLAN_NAME
--------------------- ------------------------------------ ------------------------------
SQL_f45ty80gh34ft358  SELECT COL1 FROM T WHERE COL2 = :A1; SQL_PLAN_tityruyr2k4k10167mc53
SQL_f45ty80gh34ft358  SELECT COL1 FROM T WHERE COL2 = :A1; SQL_PLAN_tigfgg8w2k4k45h6e3169


План SQL_PLAN_tityruyr2k4k10167mc53  оптимизированный.


Его можно переименовать:

declare
myplan pls_integer;
begin
myplan:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
 sql_handle=>'SQL_f45ty80gh34ft358',
 plan_name=>'SQL_PLAN_tityruyr2k4k10167mc53',
 attribute_name=>'PLAN_NAME',
 attribute_value=>'SQL_PLAN_optimyzed_20180301');
end;
/

Зафиксируем новый план в baseline:

declare
myplan pls_integer;
begin
myplan:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
 sql_handle=>'SQL_f45ty80gh34ft358',
 plan_name=>'SQL_PLAN_optimyzed_20180301',
 attribute_name=>'FIXED',
 attribute_value=>'YES');
end;
/


Проверить использование нового закреплённого плана на базе SPM Baseline

col sql_profile format a30
col sql_patch format a28
col sql_plan_baseline format a30
col last_load_time format a20
col last_active_time format a20
select INST_ID,
       SQL_ID,
       CHILD_NUMBER,
       PLAN_HASH_VALUE,
       SQL_PROFILE,
       SQL_PATCH,
       SQL_PLAN_BASELINE,
       LAST_LOAD_TIME,
       LAST_ACTIVE_TIME
from GV$SQL
where SQL_ID in ('SQL_ID');


Удалить исходный план из baseline.

var res number
exec :res :=DBMS_SPM.DROP_SQL_PLAN_BASELINE ('SQL_f45ty80gh34ft358','SQL_PLAN_tigfgg8w2k4k45h6e3169');
exec dbms_output.put_line('Number of plans dropped: ' || :res);



Использование инструмента SQLT.


Если исходный запрос находится в памяти (shared pool) или в AWR репозитории
и оптимизированный запрос находится в памяти (shared pool)
то ассоциировать план от оптимизированного SQL запроса с исходным SQL запросом можно так:

Исползуем инструмент SQLT

Находим SQL_ID и PLAN_HASH_VALUE для обоих запросов (исходного и оптимизированного)

SQL_ID и PLAN_HASH_VALUE
OPT_SQL_ID и OPT_PLAN_HASH_VALUE

Переходим в директорию sqlt/utl где установлен инструмент SQLT
Запускаем скрипт coe_load_sql_baseline.sql с такими параметрами:

SQL_ID  OPT_SQL_ID  OPT_PLAN_HASH_VALUE

SQL> connect system/*****
SQL> @coe_load_sql_baseline.sql SQL_ID  OPT_SQL_ID  OPT_PLAN_HASH_VALUE

Необходимо подключиться как пользователь с привилегией администратора баз данных,
например SYSTEM.

Не подключайтесь как SYS,
так как промежуточная таблица не может быть создана в схеме SYS,
и вы получите сообщение об ошибке:
ORA-19381: невозможно создать промежуточную таблицу в схеме SYS.


Проверить закрепление нового плана для исходного SQL_ID:

col origin format a15
col sql_handle format a21
select s.sql_id, s.plan_hash_value, b.sql_handle, b.plan_name,
       b.parsing_schema_name, b.optimizer_cost, b.origin,
       b.enabled, b.accepted, b.fixed, b.autopurge
from v$sql s, dba_sql_plan_baselines b
where s.exact_matching_signature = b.signature
  and s.sql_id= 'SQL_ID';


Его можно переименовать:

declare
myplan pls_integer;
begin
myplan:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
 sql_handle=>'SQL_f45ty80gh34ft358',
 plan_name=>'SQL_PLAN_tityruyr2k4k10167mc53',
 attribute_name=>'PLAN_NAME',
 attribute_value=>'SQL_PLAN_optimyzed_20180301');
end;
/

Зафиксируем новый план в baseline:

declare
myplan pls_integer;
begin
myplan:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
 sql_handle=>'SQL_f45ty80gh34ft358',
 plan_name=>'SQL_PLAN_optimyzed_20180301',
 attribute_name=>'FIXED',
 attribute_value=>'YES');
end;
/

Проверить использование нового закреплённого плана для исходного SQL_ID
на базе SPM Baseline:

col sql_profile format a30
col sql_patch format a28
col sql_plan_baseline format a30
col last_load_time format a20
col last_active_time format a20
select INST_ID,
       SQL_ID,
       CHILD_NUMBER,
       PLAN_HASH_VALUE,
       SQL_PROFILE,
       SQL_PATCH,
       SQL_PLAN_BASELINE,
       LAST_LOAD_TIME,
       LAST_ACTIVE_TIME
from GV$SQL
where SQL_ID in ('SQL_ID');



Другие примеры:


План действий такой:

Находим sql_id запроса  основного потребителя ресурсов

SELECT sql_id, buffer_gets, sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('buffer_gets > 500'))
ORDER BY sql_id;

SELECT sql_id, disk_reads, sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('disk_reads > 1000'))
ORDER BY sql_id;

SELECT sql_id, cpu_time, sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('cpu_time > 10000000'))
ORDER BY sql_id;

SELECT sql_id, elapsed_time, sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('elapsed_time > 100000000'))
ORDER BY sql_id;

SELECT sql_id, executions, sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('executions > 10000'))
ORDER BY sql_id;

SELECT sql_id, direct_writes, sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('direct_writes > 10000'))
ORDER BY sql_id;

SELECT sql_id, rows_processed, sql_text
FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('rows_processed > 10000'))
ORDER BY sql_id;

Смотрим из каких SQL он вызван:

select top_level_sql_id, count(*)
from v$active_session_history
where sql_id = 'b6usrg82hwsa3'
group by top_level_sql_id
order by count(*) desc


Появились ли дочерние курсоры из-за различий в оптимизаторе:

select sql_id,
child_number,
optimizer_mismatch
from v$sql_shared_cursor
where sql_id = 'b6usrg82hwsa3';

В представлении v$sql_shared_cursor можно найти все причины
почему курсор не использовался повторно и был содан дочерний


select *
from v$sql_shared_cursor
where sql_id = 'b6usrg82hwsa3';


Или так

select s.inst_id as INST,
       s.EXECUTIONS as EXECS,
       to_char(to_date(s.last_load_time, 'yyyy-mm-dd/hh24:mi:ss'), 'dd.mm hh24:mi') as last_load_time,
       s.users_opening,
       to_char(s.last_active_time, 'dd.mm hh24:mi') as last_active_time,
       round(s.elapsed_time/decode(s.EXECUTIONS,0,1,s.EXECUTIONS)) as ELA_PER_EXEC,
       s.PLAN_HASH_VALUE,
       s.optimizer_cost,
       s.child_number as CHILD,
       s.IS_BIND_SENSITIVE as "BIND_SENSE",
       s.IS_BIND_AWARE as "BIND_AWARE",
       s.IS_SHAREABLE as "SHAREABLE",
       use_feedback_stats as USE_FEEDBACK_STATS,
       load_optimizer_stats as OPTIMIZER_STATS,
       bind_equiv_failure as BIND_EQ_FAILURE,
       ROLL_INVALID_MISMATCH,
       bind_uacs_diff,
       (select reasons || '  |  ' || details
          from xmltable('/ChildNode' passing
                        (select case when dbms_lob.instr(reason, '', 1, 2) = 0
                                       then xmltype(reason)
                                     when dbms_lob.instr(reason, '', 1, 2) > 4000
                                       then xmltype(dbms_lob.substr(reason, dbms_lob.instr(reason, '
', 1, 1) + 8) || '')
                                  else xmltype(dbms_lob.substr(reason, dbms_lob.instr(reason, '', 1, 2) - 1))
                                  end as xmlval
                           from gv$sql_shared_cursor
                          where dbms_lob.substr(reason, 256) <> ' '
                            and sql_id = sc.sql_id
                            and inst_id = sc.inst_id
                            and child_address = sc.child_address)
                        columns Reasons varchar2(4000) path '/ChildNode/reason',
                                Details varchar2(4000) path '/ChildNode/details')) as Reason1,
       SQL_PLAN_BASELINE,
       SQL_PATCH,
       OUTLINE_CATEGORY,
       SQL_PROFILE,
       IS_OBSOLETE
  from gv$sql_shared_cursor sc, gv$sql s
 where sc.sql_id = 'b6usrg82hwsa3'
   and sc.inst_id = s.inst_id
   and sc.child_address = s.child_address
   and sc.sql_id = s.sql_id
   and sc.inst_id > 0
and (s.EXECUTIONS>0 or s.users_opening>0)
order by s.inst_id, --s.child_number
    s.last_active_time desc;




Если курсоров несколько, то выбираем с лучшим планом (смотрим ela_per_exec)
Подключаемся к соответствующему экземпляру в sared pool-е которого находится курсор.

Зафиксируем данный курсор в базовой линии.
создать base_line (sql_id plan_hash_value "название");

Рассмотреть вопрос с использованием sql Patch или просто хинтов.

Компиляция нового курсора - это полный разбор (parse count (hard)), активность защёлок,
расход времени CPU (это дорогой процесс).
Нужно повторно использовать курсоры.

При повторном использовании будет частичный разбор
soft_parse = parse count (total) -
             parse count (hard)

из v$sysstat

ещё смотрите на время

parse time cpu
parse time elapsed

эти значения не должны расти во время работы приложения в идеале.

Чтобы курсоры использовались повторно должны еще, кроме текстов запросов,
совпадать и установки режимов работы оптимизатора

alter session set optimizer_mode = first_rows;
select ...

alter session set optimizer_mode = all_rows;
select ...

планы будут разные

Столбец sql_id в v$sql и v$open_cursor однозначно идентифицирует курсор
Раньше, до 10g использовали комбинацию (hash_value, address)


Примеры парсинга:



create table test1(a number);

--Flush the pool, or else this test won't be repeatable.
alter system flush shared_pool;

select value, name
from v$sesstat natural join v$statname
where sid = sys_context('userenv', 'sid')
    and name in ('parse count (total)', 'parse count (hard)');

47  parse count (total)
5   parse count (hard)



begin
    for i in 1 .. 10000 loop
        execute immediate 'insert into test1 values('||i||')';
    end loop;
    commit;
end;
/

select value, name
from v$sesstat natural join v$statname
where sid = sys_context('userenv', 'sid')
    and name in ('parse count (total)', 'parse count (hard)');

10072   parse count (total)
10007   parse count (hard)




begin
    for i in 1 .. 10000 loop
        execute immediate
        'begin
            delete from test1 where a = :i;
        end;'
        using i;
    end loop;
    commit;
end;
/

select value, name
from v$sesstat natural join v$statname
where sid = sys_context('userenv', 'sid')
    and name in ('parse count (total)', 'parse count (hard)');

10106   parse count (total)
10019   parse count (hard)