Ask Me

Scripts for Performance Tuning

 


******************************************************************************
Tuning SQL
******************************************************************************
#!/bin/ksh
echo "enter the value for sql_id:"
read sqlid
sqlplus -s '/ as sysdba' <<EOF
variable stmt_task varchar2(60);
EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '$sqlid');
select task_name, advisor_name from dba_advisor_tasks where advisor_name = 'SQL
Tuning Advisor';

Execute dbms_sqltune.execute_tuning_task(:stmt_task);
set long 4000
set longchunksize 1000
set linesize 100
select dbms_sqltune.report_tuning_task(:stmt_task) from dual;

set pagesize 0
set long 9999999
spool SQL_TUNING_Example3_rpt.out
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_1467900835573') from dual ;
spool off
execute dbms_sqltune.accept_sql_profile(task_name =>'SQL_TUNING_1437688430016', task_owner => 'SYS', replace => TRUE , force_match => TRUE );
SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK('SQL_TUNING_1467807139580','PROFILES') from dual;
SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK('SQL_TUNING_Example3','INDEXES') from dual;
SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK('SQL_TUNING_Example3','STATISTICS') from dual;
SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK('SQL_TUNING_Example3') from dual;

 execute dbms_sqltune.accept_sql_profile(task_name =>
            'SQL_TUNING_1448382538320', task_owner => 'SYS', replace => TRUE,
            profile_type => DBMS_SQLTUNE.PX_PROFILE);   ----- paralle profile DOp
execute dbms_sqltune.accept_sql_profile(task_name =>'SQL_TUNING_1448382538320', task_owner => 'SYS', replace => TRUE);

execute dbms_sqltune.create_sql_plan_baseline(task_name =>
            'SQL_TUNING_1467807139580', owner_name => 'SYS', plan_hash_value
            => 931315478);




Popular Posts