Optional SPM demo. Run after 05-add-index-and-run-good.sql.
Requires ADMINISTER SQL MANAGEMENT OBJECT.
It loads, verifies, and then drops one SQL Plan Baseline.

Index altered.


PL/SQL procedure successfully completed.


Index altered.


PL/SQL procedure successfully completed.





1 row selected.

Plans observed for SQL ID 461z401yjjmf3 before loading the baseline:
old   3: where  sql_id = '&&spm_sql_id'
new   3: where  sql_id = '461z401yjjmf3'

SQL_ID        PLAN_HASH_VALUE EXECUTIONS
------------- --------------- ----------
461z401yjjmf3      3093251051          2

1 row selected.

old   3:                      sql_id          => '&&spm_sql_id',
new   3:                      sql_id          => '461z401yjjmf3',
old   4:                      plan_hash_value => to_number('&&spm_good_phv'),
new   4:                      plan_hash_value => to_number('3093251051'),

PL/SQL procedure successfully completed.


PLANS_LOADED
------------
           1

old   8:   where  s.sql_id = '&&spm_sql_id'
new   8:   where  s.sql_id = '461z401yjjmf3'




1 row selected.

Loaded baseline SQL_PLAN_d8y47aqck8jka3baf76d4 under handle SQL_d47887559924464a
old   3: where  sql_handle = '&&spm_sql_handle'
new   3: where  sql_handle = 'SQL_d47887559924464a'

SQL_HANDLE                     PLAN_NAME                                                                                                                        ENA ACC FIX REP
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- --- --- --- ---
SQL_d47887559924464a           SQL_PLAN_d8y47aqck8jka3baf76d4                                                                                                   YES YES NO  YES

1 row selected.

Invalidate the cursor without changing the SQL text, then execute again.

Index altered.


Index altered.


PL/SQL procedure successfully completed.

old   3: where  sql_id = '&&spm_sql_id'
new   3: where  sql_id = '461z401yjjmf3'

SQL_ID        PLAN_HASH_VALUE SQL_PLAN_BASELINE                                                                                                                EXECUTIONS
------------- --------------- -------------------------------------------------------------------------------------------------------------------------------- ----------
461z401yjjmf3      3093251051 SQL_PLAN_d8y47aqck8jka3baf76d4                                                                                                            1

1 row selected.

old   3:                       sql_handle => '&&spm_sql_handle',
new   3:                       sql_handle => 'SQL_d47887559924464a',
old   4:                       plan_name  => '&&spm_plan_name'
new   4:                       plan_name  => 'SQL_PLAN_d8y47aqck8jka3baf76d4'

PL/SQL procedure successfully completed.


PLANS_DROPPED
-------------
            1

old   3: where  sql_handle = '&&spm_sql_handle'
new   3: where  sql_handle = 'SQL_d47887559924464a'

no rows selected

SPM demo complete. The baseline was removed; the lab index remains visible.
