Skip to content
February 13, 2017 / doganay

SQL TUNING USING DBMS_SQLTUNE

--sqlid
--exec DBMS_SQLTUNE.DROP_TUNING_TASK('STT_TMP_1');
DEFINE v_sql_id='30nf3tqcuj758'

DECLARE
    v_sql_id       VARCHAR2 (13) := '&v_sql_id';
    my_task_name   VARCHAR2 (30);
BEGIN
    my_task_name :=
        DBMS_SQLTUNE.create_tuning_task (sql_id      => '&v_sql_id',
                                         task_name   => 'STT_TMP_1');

    DBMS_SQLTUNE.execute_tuning_task (task_name => my_task_name);
END;
/






--sqltext
--EXEC DBMS_SQLTUNE.DROP_TUNING_TASK('STT_TMP_1');
DECLARE
    my_task_name   VARCHAR2 (30);
    my_sqltext     CLOB;
BEGIN
    my_sqltext := 'select 1 from dual';

    my_task_name :=
        DBMS_SQLTUNE.create_tuning_task (sql_text    => my_sqltext,
                                         task_name   => 'STT_TMP_1');

    DBMS_SQLTUNE.execute_tuning_task (task_name => my_task_name);
END;
/






--historic
SELECT *
  FROM (SELECT snap_id, begin_interval_time, end_interval_time
          FROM dba_hist_snapshot
        ORDER BY snap_id DESC)
 WHERE ROWNUM <= 15;

--EXEC DBMS_SQLTUNE.DROP_TUNING_TASK('STT_TMP_1');

DEFINE v_sql_id='bjf05cwcj5s6p'
DECLARE     my_task_name   VARCHAR2 (30);
     my_sqltext     CLOB;
BEGIN
     my_task_name := DBMS_SQLTUNE.create_tuning_task
                                        (begin_snap   => 62478,
                                         end_snap     => 62479,
                                         sql_id       => '&v_sql_id');

    DBMS_SQLTUNE.execute_tuning_task (task_name => my_task_name);
END;
/



select * from DBA_ADVISOR_TASKS;


--print tuning advice
SET PAGES 1000
SET SERVEROUTPUT ON
ALTER SESSION SET nls_language=american;

DECLARE
    xml_out   XMLTYPE;

    PROCEDURE print_clob (p_clob IN CLOB)
    IS
        v_offset       NUMBER := 1;
        v_chunk_size   NUMBER := 4000;
        v_length       NUMBER;
    BEGIN
        v_length := DBMS_LOB.getlength (p_clob);

        LOOP
            EXIT WHEN v_offset > v_length;
            DBMS_OUTPUT.put_line (
                DBMS_LOB.SUBSTR (p_clob, v_chunk_size, v_offset));
            v_offset := v_offset + v_chunk_size;
        END LOOP;
    END print_clob;
BEGIN
    DBMS_OUTPUT.enable (NULL);
    print_clob (DBMS_SQLTUNE.report_tuning_task ('STT_TMP_1'));
END;
/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: