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