Howdy,
I'm wondering about how SPM and things like
https://blogs.oracle.com/optimizer/post/what-is-add-verified-spm would be impacted by the presence of global temporary tables within the query(s).
I've been looking for documentation that would outline how SQL plan management would behave when dealing with queries relying on GTTs but I haven't had any luck so far.
Basically I'm curious how reliably baselines, evolving, etc can/do work when dealing with queries that could have wildly different data sets based on GTTs within the query.
Cheers,
You can create baselines and evolve them on queries using global temporary tables like regular permanent tables. The main challenge - like many things with GTTs - is ensuring they have representative data when you run the evolve task.
Below is a short demo showing this in action.
I'm curious how reliably baselines, evolving, etc can/do work when dealing with queries that could have wildly different data sets based on GTTs within the query. If you have many accepted baselines for a query the optimizer will choose between them. It'll pick the baseline that has the lowest cost, so ensuring you have correct stats for the GTT can be key to get this right. Session-level stats for GTTs from 12c can help with this.
That said, the idea with SPM is to restrict queries to a few known good plans. If you expect lots of variation in the data in the GTT and as a result lots of different plans there may be too many options to get the plans you want.
create global temporary table emp_gtt (
employee_id int
) on commit preserve rows;
select * from hr.employees
join emp_gtt using ( employee_id );
var report clob
exec :report := dbms_spm.add_verified_sql_plan_baseline('5s2byzfrp4vh8');
set serveroutput off
select * from hr.employees
join emp_gtt using ( employee_id );
select * from dbms_xplan.display_cursor( format => 'ALLSTATS LAST');
---------------------------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | NESTED LOOPS | | 1 |
| 2 | NESTED LOOPS | | 1 |
| 3 | TABLE ACCESS FULL | EMP_GTT | 1 |
|* 4 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 |
| 5 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 |
---------------------------------------------------------------
insert into emp_gtt
with rws as ( select level x from dual connect by level <= 10000 ) select * from rws;
exec dbms_stats.gather_table_stats(user,'emp_gtt');
select * from hr.employees
join emp_gtt using ( employee_id );
select * from dbms_xplan.display_cursor( format => 'ALLSTATS LAST');
---------------------------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | NESTED LOOPS | | 107 |
| 2 | NESTED LOOPS | | 10000 |
| 3 | TABLE ACCESS FULL | EMP_GTT | 10000 |
|* 4 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 |
| 5 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 |
---------------------------------------------------------------
exec :report := dbms_spm.evolve_sql_plan_baseline();
print :report
REPORT
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
---------------------------------------------------------------------------------------------
Task Information:
---------------------------------------------
Task Name : TASK_296
Task Owner : CHRIS
Execution Name : EXEC_409
Execution Type : SPM EVOLVE
Scope : COMPREHENSIVE
Status : COMPLETED
Started : 03/18/2024 18:09:08
Finished : 03/18/2024 18:09:08
Last Updated : 03/18/2024 18:09:08
Global Time Limit : 2147483646
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_gsvwx50wc82cm5e741b12
Base Plan Name : SQL_PLAN_gsvwx50wc82cmeb2deead
SQL Handle : SQL_fc6f9d2838c40993
Parsing Schema : CHRIS
Test Plan Creator : CHRIS
SQL Text : select * from hr.employees join emp_gtt using (
employee_id )
Execution Statistics:
-----------------------------
Base Plan Test Plan
---------------------------- ----------------------------
Elapsed Time (s): .002527 .000508
CPU Time (s): .002495 .000769
Buffer Gets: 132 27
Optimizer Cost: 114 10
Disk Reads: 0 0
Direct Writes: 0 0
Rows Processed: 107 107
Executions: 10 10
FINDINGS SECTION
---------------------------------------------------------------------------------------------
Findings (2):
-----------------------------
1. The plan was verified in 0.05100 seconds. It passed the benefit criterion
because its verified performance was 4.88422 times better than that of the
baseline plan.
2. The plan was automatically accepted.
Recommendation:
-----------------------------
Consider accepting the plan.
…
select * from hr.employees
join emp_gtt using ( employee_id );
select * from dbms_xplan.display_cursor( format => 'ALLSTATS LAST');
----------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
|* 1 | HASH JOIN | | 107 | 932K| 932K| 1387K (0)|
| 2 | TABLE ACCESS FULL| EMPLOYEES | 107 | | | |
| 3 | TABLE ACCESS FULL| EMP_GTT | 10000 | | | |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMPLOYEES"."EMPLOYEE_ID"="EMP_GTT"."EMPLOYEE_ID")
Note
-----
- SQL plan baseline SQL_PLAN_gsvwx50wc82cm5e741b12 used for this statement
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
- Global temporary table session private statistics used