Skip to content

Commit c275388

Browse files
nigelbaylissnigelbayliss
committed
new SPM control
1 parent 2f51ea5 commit c275388

File tree

15 files changed

+905
-0
lines changed

15 files changed

+905
-0
lines changed

‎optimizer/spm_plan_control/12_2_to_19c_scripts/README.md

Lines changed: 43 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,43 @@
1+
<h2>Using SQL Plan Management to Control SQL Execution Plans</h2>
2+
3+
Based on <a href="https://blogs.oracle.com/optimizer/using-sql-plan-management-to-control-sql-execution-plans">this blog article.</a>
4+
5+
The utility scripts are fix_all.sql and fix_spm.sql
6+
7+
They are intended to be similar in usage to the MOS script "coe_xfr_sql_profile.sql" - but using SQL plan baselines rather than SQL profiles.
8+
9+
You can run a simple testcase as follows:
10+
11+
```
12+
$ sqlplus dbauser/password
13+
SQL> @@setup
14+
SQL> @@q --- The query will use the BOB_PK execution plan (this is the best one)
15+
SQL> @@test_spm --- This will force the query to use the BOB_IDX plan instead (not the best plan, but this is a demo!)
16+
SQL> @@q --- See the query is now using the SQL plan baseline
17+
SQL> @@look --- Take a look at the SQL plan baseline
18+
```
19+
20+
If you are licensed, you can retireve plans from AWR or SQL tuining sets:
21+
22+
```
23+
$ sqlplus dbauser/password
24+
SQL> @@setup
25+
SQL> @@awr --- Load two different plans in AWR for our SQL Statement [BOB_IDX and BOB_PK]
26+
SQL> @@sts --- Load BOB FULL plan into a SQL tuning set
27+
SQL> @@test_all --- Pick one of the plans...
28+
--- BOB_PK = plan hash value 772239758
29+
--- BOB_IDX = plan hash value 4251244305
30+
--- BOB FULL scan = plan hash value 1006760864
31+
SQL> @@q --- Run the query to see the plan you chose
32+
SQL> @test_all --- Pick a different plan
33+
SQL> @@q --- Take another look at the plan
34+
SQL> @@look --- View SQL plan baselines
35+
```
36+
37+
The scripts require Oracle Database 12c Release 2 onwards.
38+
39+
DISCLAIMER:
40+
<br/>-- These scripts are provided for educational purposes only.
41+
<br/>-- They are NOT supported by Oracle World Wide Technical Support.
42+
<br/>-- The scripts have been tested and they appear to work as intended.
43+
<br/>-- You should always run scripts on a test instance.

‎optimizer/spm_plan_control/12_2_to_19c_scripts/awr.sql

Lines changed: 56 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,56 @@
1+
alter system flush shared_pool;
2+
3+
DECLARE
4+
l_plans_dropped PLS_INTEGER;
5+
BEGIN
6+
FOR REC IN (SELECT DISTINCT SQL_HANDLE FROM DBA_SQL_PLAN_BASELINES where sql_text like '%HELLO%')
7+
LOOP
8+
L_PLANS_DROPPED := DBMS_SPM.DROP_SQL_PLAN_BASELINE (
9+
sql_handle => rec.sql_handle,
10+
PLAN_NAME => NULL);
11+
END LOOP;
12+
END;
13+
/
14+
15+
REM We are going to put three alternative plans into AWR:
16+
REM
17+
REM BOB_PK - plan hash value 772239758
18+
REM BOB_IDX - plan hash value 4251244305
19+
REM BOB FULL - plan hash value 1006760864
20+
21+
EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;
22+
23+
@@q
24+
25+
declare
26+
n pls_integer;
27+
begin
28+
for i in 1..100000
29+
loop
30+
execute immediate 'select /* HELLO */ num from bob where id = 100' into n;
31+
end loop;
32+
end;
33+
/
34+
35+
EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;
36+
37+
alter index bob_pk invisible;
38+
39+
@@q
40+
41+
declare
42+
n pls_integer;
43+
begin
44+
for i in 1..200000
45+
loop
46+
execute immediate 'select /* HELLO */ num from bob where id = 100' into n;
47+
end loop;
48+
end;
49+
/
50+
51+
EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;
52+
53+
alter index bob_pk visible;
54+
alter index bob_idx visible;
55+
56+
select /* HELLO */ num from bob where id = 100;

‎optimizer/spm_plan_control/12_2_to_19c_scripts/drop.sql

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,15 @@
1+
set echo on
2+
alter system flush shared_pool;
3+
4+
DECLARE
5+
l_plans_dropped PLS_INTEGER;
6+
BEGIN
7+
FOR REC IN (SELECT DISTINCT SQL_HANDLE FROM DBA_SQL_PLAN_BASELINES where sql_text like '%HELLO%')
8+
LOOP
9+
L_PLANS_DROPPED := DBMS_SPM.DROP_SQL_PLAN_BASELINE (
10+
sql_handle => rec.sql_handle,
11+
PLAN_NAME => NULL);
12+
END LOOP;
13+
END;
14+
/
15+

0 commit comments

Comments
 (0)