Oracle / SQL Tuning Advisor

SQL Tuning Advisor can work on queries that have never been run before. I'm not sure what interface you're using, but the DBMS_SQLTUNE package makes it easy to pass in the SQL text.

For example, let's create a simple table with no indexes:

--Create a simple table with no indexes.
create table table_missing_index(a number);

insert into table_missing_index
select level from dual connect by level <= 100000;

begin
    dbms_stats.gather_table_stats(user, 'TABLE_MISSING_INDEX');
end;
/

Then pass in a query that could obviously benefit from an index:

--Create, execute, and display the tuning task.
declare
    v_task varchar2(64);
begin
    v_task := dbms_sqltune.create_tuning_task
    (
        sql_text => 'select a from table_missing_index where a = 1'
    );

    dbms_sqltune.execute_tuning_task(task_name => v_task);

    dbms_output.put_line('Task name: '||v_task);
end;
/

Grab the task name from the output, and plug it into this query to see the results:

select dbms_sqltune.report_tuning_task('TASK_362') from dual;

The output should look something like this:

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : TASK_362
Tuning Task Owner  : JHELLER
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 04/04/2021 14:26:15
Completed at       : 04/04/2021 14:26:15

-------------------------------------------------------------------------------
Schema Name   : JHELLER
Container Name: ORCLPDB
SQL ID        : 0g6v1x1c7kcjt
SQL Text      : select a from table_missing_index where a = 1

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- Index Finding (see explain plans section below)
--------------------------------------------------
  The execution plan of this statement can be improved by creating one or more
  indices.

  Recommendation (estimated benefit: 98.56%)
  ------------------------------------------
  - Consider running the Access Advisor to improve the physical schema design
    or creating the recommended index.
    create index JHELLER.IDX$$_016A0001 on JHELLER.TABLE_MISSING_INDEX("A");

... [removed rest of the large report] ...