DECLARE
 my_task_name VARCHAR2(30);
 my_sqltext   CLOB;
 mTaskName varchar2(100) := 'my_sql_tuning_task';
BEGIN
 my_sqltext := 'select * from aldo.vsl_lav_attivita where id_commessa = :c';
 begin
   DBMS_SQLTUNE.DROP_TUNING_TASK(mTaskName);
 exception
   when others then null;
 end;
 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
         sql_text    => my_sqltext,
         --bind_list   => sql_binds(anydata.ConvertNumber(100)),
         user_name   => 'ALDO',
         scope       => 'COMPREHENSIVE',
         time_limit  => 60*60*24,
         task_name   => mTaskName,
         description => 'Task to tune a query');
END;
/

SELECT task_name FROM DBA_ADVISOR_LOG

BEGIN
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task' );
END;
/

SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'my_sql_tuning_task';

--this doesn't work 
--SELECT * FROM V$ADVISOR_PROGRESS WHERE task_name = 'my_sql_tuning_task';

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task')
  FROM DUAL;
  
--if it suggest a profile:
begin
dbms_sqltune.accept_sql_profile(task_name => 'my_sql_tuning_task', replace => TRUE);
--select * from DBA_SQL_PROFILES
end;

create the plan table

CREATE TABLE PLAN_TABLE
(
STATEMENT_ID VARCHAR2(30 BYTE),
TIMESTAMP DATE,
REMARKS VARCHAR2(80 BYTE),
OPERATION VARCHAR2(30 BYTE),
OPTIONS VARCHAR2(255 BYTE),
OBJECT_NODE VARCHAR2(128 BYTE),
OBJECT_OWNER VARCHAR2(30 BYTE),
OBJECT_NAME VARCHAR2(30 BYTE),
OBJECT_INSTANCE INTEGER,
OBJECT_TYPE VARCHAR2(30 BYTE),
OPTIMIZER VARCHAR2(255 BYTE),
SEARCH_COLUMNS NUMBER,
ID INTEGER,
PARENT_ID INTEGER,
POSITION INTEGER,
COST INTEGER,
CARDINALITY INTEGER,
BYTES INTEGER,
OTHER_TAG VARCHAR2(255 BYTE),
PARTITION_START VARCHAR2(255 BYTE),
PARTITION_STOP VARCHAR2(255 BYTE),
PARTITION_ID INTEGER,
OTHER LONG,
DISTRIBUTION VARCHAR2(30 BYTE),
CPU_COST INTEGER,
IO_COST INTEGER,
TEMP_SPACE INTEGER,
ACCESS_PREDICATES VARCHAR2(4000 BYTE),
FILTER_PREDICATES VARCHAR2(4000 BYTE)
)

set timing on
set autotrace on statistics
set linesize 300
select * from scott.salgrade;

Elapsed: 00:03:35.87

Statistics
----------------------------------------------------------
268 recursive calls
0 db block gets
29407762 consistent gets
138779 physical reads
0 redo size
1212 bytes sent via SQL*Net to client
4812 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
35 sorts (memory)
0 sorts (disk)
0 rows processed


no rows selected

set timing on
set autotrace traceonly explain
set linesize 300
select * from scott.salgrade;

Hint
#Enable parallel and index:
SELECT /*+ parallel (pa) index (PK_TUNING)*/ * from pippo pa

#Many indexes
/*+ INDEX (b I_ACC_NUMFW i_etlrouting_tln) INDEX (c I_ETLROUTING_TEL)*/

Virtual index

CREATE INDEX PIN.V_INDX ON PIN.EVENT_BAL_IMPACTS_T(OBJ_ID0,ITEM_OBJ_ID0) NOSEGMENT

alter session set "_use_nosegment_indexes" = true;

--DROP INDEX BEFORE EXIT!

Find tables accessed by a statement

This script has not been used a lot. Be sure to separately run each block starting from the core

--Get the number of rows for each accessed table 
--declare a number; s number:=0; begin
select 'select count(*) into a from ' || object_owner ||'.' || object_name || ';' || chr(13)||chr(10) ||
'dbms_output.put_line(''' || object_owner ||'.' || object_name || ' '' || a || '' righe'');' || chr(13)||chr(10) ||
's := s+a;'
from (
  --
  select * from (
    select a.*
    , (select object_type from dba_objects where owner=a.object_owner and object_name = a.object_name 
       and nvl(subobject_name, '0') = decode(partition_id, null, '0', partition_name)) obj_type
    from (
      select statement_id, object_owner, object_name, partition_id
	  , (select partition_name from dba_tab_partitions 
	     where table_owner = object_owner and table_name = object_name
	     and partition_position = partition_id) partition_name
      from plan_table 
      where statement_id = (select statement_id from plan_table 
        where timestamp = (select max(timestamp) from plan_table)
        and rownum = 1)
      and not object_owner is null
    ) a
  )
  where obj_type in ('TABLE', 'TABLE PARTITION')
  --
)
--end; 

I created some indexes, which are no more used?
select owner, object_name, value from v$segment_statistics
where statistic_name = 'logical reads'
and object_type = 'INDEX'
and object_name in (
  select index_name from dba_indexes where table_owner||'.'||table_name in (
    'DIMENSION.NODE', 'NISTOTTS.TBL_NIS_CLIENTE_APP_SERUCC', 'DIMENSION.SERVICEOBJECT',
	'DIMENSION.SUBSCRIBER'
  )
)