Ok

En poursuivant votre navigation sur ce site, vous acceptez l'utilisation de cookies. Ces derniers assurent le bon fonctionnement de nos services. En savoir plus.

  • Tuning SQL ID

    http://www.runningoracle.com/product_info.php?products_id=435

     

    Summary 
    You can create an SQL TUNING TASK manually ad hoc with the following simple steps. 

    ALTER SESSION SET NLS_LANGUAGE='AMERICAN'; 

    0. Find the sql_id of the oracle session you would like to analyze. Usually the AWR has the top sql_ids. 
    In case this is a current sql running use the v$session. 

    select sql_id from v$session where sid = :x 

    1. Login as SYSTEM (or any other user) at sqlplus and create the tuning task:

    SET SERVEROUTPUT ON
declare
stmt_task VARCHAR2(40);
begin
stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '5tru8vxmktswq');
DBMS_OUTPUT.put_line('task_id: ' || stmt_task );
end;
/
task_id: TASK_69287

    2. Run the SQL TUNING TASK

    begin
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'TASK_69287');
end;
/

    3. You can monitor the processing of the tuning task with the statement 

    SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME = 'TASK_69287'; 

    4. When the task has a status=COMPLETED, then run:

    SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_69287') AS recommendations FROM dual;

    5. Examine the recommendations from Oracle, in case you agree, then accept the best SQL profile.

    begin
DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name => 'TASK_69287', task_owner => 'SYSTEM', replace => TRUE);
end;
/

6. You can check the database sql profiles with the statement:

select * from dba_sql_profiles;

In case you want to disable an sql profile use the statement:

begin
DBMS_SQLTUNE.ALTER_SQL_PROFILE('SQL_PROFILE','STATUS','DISABLED');
end;
/

     

     

    https://dbaclass.com/article/how-to-run-sql-tuning-advisor-for-a-sql_id/

     


    declare
    l_sql_tune_task_id varchar2(100);
    begin
    l_sql_tune_task_id := dbms_sqltune.create_tuning_task (
    begin_snap => 4883,
    end_snap => 4888,
    sql_id => '1pfbzr2wbnwsy',
    scope => dbms_sqltune.scope_comprehensive,
    time_limit => 10800,
    task_name => 'tune_sql_1pfbzr2wbnwsy',
    description => 'tuning_du_sql_1pfbzr2wbnwsy');
    dbms_output.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
    end;
    /

    EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'tune_sql_1pfbzr2wbnwsy');

    set long 65536
    set longchunksize 65536
    set linesize 100
    select dbms_sqltune.report_tuning_task('tune_sql_1pfbzr2wbnwsy') from dual;