SQL Tunning Mannual ADV.

  l_sql_tune_task_id VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          sql_id      => 'bkdp184jg0psc',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit=> 500,
                          task_name   => 'JAY_6yn1nsy93kn54',
                          description => 'JAY_6yn1nsy93kn54',
                          force_match  => true);
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

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


SET LONG 99999
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'JAY_6yn1nsy93kn54')
FROM DUAL;




check sql plan of below sql.

explain plan for


@?/rdbms/admin/utlxpls.sql




check , what plans are avilable in cursor cache.
===============================================================================================================================
set trimspool on
set lines 220
set long 10000
set longchunk 10000
set pages 99
set longchunk 100000
set long 10000
set timing on
col sql_id format a15 heading SQL_ID
col sql_profile format a4 heading SQL|PROF
COL TRANS_NAME FORMAT A30 HEADING TRANS_NAME
col module format a10 heading module
col plan_hash_value format 9999999999 heading PLAN|HASH
col buffer_gets_delta format 999999999 heading lio
col lio_per_exec format 9999999 heading lio|exec
col disk_reads_delta format 9999999 heading pio
col pio_per_exec format 9999999 heading pio|exec
col ELAPSED_TIME_DELTA format 9999999.99 heading EXEC|SECS
col SECS_PER_EXEC format 9999.99 heading SECS|PER|EXEC
col executions_delta format 999999 heading EXECS
col ela_ex format 9999999.99 heading SECS|PER|EXEC
--spool awr_cware_report.txt
SELECT   sql_id,
     module,
     case when sql_profile is null then 'N' else 'Y' end as sql_profile,
         plan_hash_value,
         dba_hist_sqlstat.snap_id,
         TO_CHAR (dba_hist_snapshot.begin_interval_time,
                  'MM-DD_hh24:mi'
                 ) snap_beg,
--      module,
         executions_delta,
         buffer_gets_delta,
        buffer_gets_delta/executions_delta lio_per_exec,
         disk_reads_delta,
        disk_reads_delta/executions_delta pio_per_exec,
         elapsed_time_delta / 1000000 elapsed_time_delta,
         CASE
            WHEN executions_delta = 0
               THEN NULL
            WHEN elapsed_time_delta = 0
               THEN NULL
            ELSE round((elapsed_time_delta / executions_delta) / 1000000,2)
         END ela_ex
    FROM dba_hist_sqlstat, dba_hist_snapshot
   WHERE sql_id = '&sql_id'
      AND executions_delta > 0
     AND dba_hist_sqlstat.snap_id = dba_hist_snapshot.snap_id
     AND dba_hist_sqlstat.instance_number = dba_hist_snapshot.instance_number
ORDER BY
--        trans_name,
         dba_hist_sqlstat.snap_id; 



=================================================================================================================================================
note the plan and good hash value.
                           SQL         PLAN                                                lio               pio        EXEC         PER
SQL_ID          module     PROF        HASH    SNAP_ID SNAP_BEG      EXECS        lio     exec      pio     exec        SECS        EXEC
--------------- ---------- ---- ----------- ---------- ----------- ------- ---------- -------- -------- -------- ----------- -----------
qgqgah0fpbqjds   JDBC Thin  N     3658746485     108096 05-17_07:01       1   50267129 ######## ######## ########     3392.91     3392.91
                Client

qgqgah0fpbqjds   JDBC Thin  N     3658746485     108097 05-17_07:30       4   56754790 ######## ######## ########     4725.50     1181.38
                Client

qgqgah0fpbqjds   JDBC Thin  N     3658746485     108098 05-17_08:00       8   94552023 ######## ######## ########     7312.43      914.05

qgqgah0fpbqjds   JDBC Thin  N      977601456     108351 05-22_14:30      16       1528       96       26        2         .53         .03
                Client

qgqgah0fpbqjds   JDBC Thin  N      977601456     108355 05-22_16:30      13       1502      116       37        3         .65         .05
                Client

qgqgah0fpbqjds   JDBC Thin  N     3658746485     108355 05-22_16:30      16  150418614  9401163 ########  9355075    16882.89     1055.18
                Client

qgqgah0fpbqjds   JDBC Thin  N      977601456     108386 05-23_08:00       9        723       80        9        1         .48         .05
                Client

=================================================================================================================================================

chech the joins in sql should be no of tahle - 1.
=================================================================================================================================================

check the profile is available in db for sql.


sql>select PLAN_NAME,SQL_TEXT,ENABLED,ACCEPTED from dba_sql_plan_baselines where SQL_TEXT like '%SELECT /* WbciCustomerIn%'

 Verify that OPTIMIZER_USE_SQL_BLAN_BASELINES is set to true (the default)

Check sql profiles are accepted or not. if sql profiles are not avulable needs to create.
=================================================================================================================================================

If plan is avilable but not enabled , enable it

set serverout on;
DECLARE
v_cnt PLS_INTEGER;
BEGIN
v_cnt := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(              
                           sql_handle=>'SQL_53962ef526fb56cb',
                           attribute_name=>'ENABLED',
                           attribute_value=>'NO');
  DBMS_OUTPUT.PUT_LINE('Plans altered: ' || v_cnt);
END;
=================================================================================================================================================

check plan

select * from table(dbms_xplan.display);
=================================================================================================================================================

Still plan is not improved, attached good plan to sql profile force fully.

execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_402456',task_owner => 'ADBM', replace => TRUE);

var v_num number;
exec :v_num:=dbms_spm.load_plans_from_cursor_cache(sql_id =>'gqgah0fpbqjds',plan_hash_value => 977601456 );



=================================================================================================================================================

Opctiolannly we can check sqltrpt.sql (sqltune).

set long 200000000
set longchunksize 200000000
set linesize 190
select dbms_sqltune.report_tuning_task('&sql_id') from dual;

& check the findings.
=================================================================================================================================================
check plan

select * from table(dbms_xplan.display);
=================================================================================================================================================

Plan hash value: 977601456

Plan changed with new hash value
=================================================================================================================================================



select plan_table_output from table(dbms_xplan.display_cursor('&sqlid',null));




http://www.areaetica.com/create-oracle-sql-profile-for-tuning/