Oracle SQL optimization Consultant

Oracle SQL optimization Consultant

-- Authorization
Grant administer any SQL tuning set to scott;
Grant advisor to scott;
Grant create any SQL profile to scott;
Grant alter any SQL profile to scott;
Grant drop any SQL profile to scott;

-- Create a task
Declare
Tuning_task_name VARCHAR2 (30 );
Tuning_sqltext CLOB;
Begin
Tuning_sqltext: = 'select job from emp'; -- Note: * is not supported here. Enter the field name.
Tuning_task_name: = DBMS_SQLTUNE.CREATE_TUNING_TASK (
SQL _text => tuning_sqltext,
User_name => 'Scott ',
Scope => 'computing ',
Time_limit => 60,
Task_name => 'SQL _ trace_20131124 ',
Description => 'empselect TUNE ');
End;

-- View all created tasks
Select * from user_advisor_log;

-- Task execution
Exec dbms_sqltune.execute_tuning_task (task_name => 'SQL _ trace_20131124 ');

-- Check the status after the task is executed
Select * from user_advisor_tasks t where t. task_name = 'SQL _ trace_20131124'

-- Generate the Final Report
Select dbms_sqltune.report_tuning_task ('SQL _ trace_20131124') from dual;

-- Delete a task
Exec dbms_sqltune.drop_tuning_task ('SQL _ trace_20131124 ');

Oracle Update Execution Plan Principle Analysis and Optimization

Oracle tablespace management and optimization

Oracle table connection modes (SQL optimization)

Summary of Oralce database Optimization

Oracle-insert Performance Optimization

Oracle data block optimization parameters