How to manually submit a 10g SQL Tuning Advisor task
- Posted by Gavin Soorma
- On June 22, 2009
- 0 Comments
Purpose:
Use the following procedure to generate the 10g SQL Tuning Advisor reports and recommendations from the command line when Database Control or Grid Control GUI is not available.
CREATE A SQL TUNING TASK
DECLARE
my_task_name VARCHAR2 (30);
my_sqltext CLOB;
BEGIN
my_sqltext := ‘INSERT INTO BWINT.PAYSLIP_CURRENT_RUN (ASSIGNMENT_ID, FULL_NAME,
ASSIGNMENT_NUMBER, LOCATION_CODE, PAYROLL_ACTION_ID,PAY_DATE) (SELECT
PAA.ASSIGNMENT_ID, PAA.FULL_NAME, PAA.ASSIGNMENT_NUMBER,
PAA.LOCATION_CODE, PAA.PAYROLL_ACTION_ID,PAA.PERIOD_END_DATE FROM
PAY_AU_ASG_PAYMENT_RUNS_V PAA, PER_PERIODS_OF_SERVICE PPS WHERE
PAA.PAYROLL_ACTION_ID = :B1 AND PAA.PERSON_ID = PPS.PERSON_ID AND
PPS.ACTUAL_TERMINATION_DATE IS NULL)’; >>>>> SQL TO BE TUNED GOES HERE
my_task_name := dbms_sqltune.create_tuning_task (sql_text=> my_sqltext,
bind_list => sql_binds (anydata.convertnumber (9)),
user_name => ‘BWINT’,
scope => ‘COMPREHENSIVE’,
time_limit => 60,
task_name => ‘CHECK_PAYROLL_RUN’,
description => ‘Testing Slow Running Online Payslip Run’
);
END;
/
PL/SQL procedure successfully completed.
EXECUTE THE TUNING TASK
BEGIN
dbms_sqltune.execute_tuning_task (task_name => 2 ‘CHECK_PAYROLL_RUN’);
end;
/
PL/SQL procedure successfully completed.
VERIFY THE STATUS OF THE TASK
SQL> SELECT status FROM USER_ADVISOR_TASKS WHERE task_name =’CHECK_PAYROLL_RUN’;
STATUS
———–
COMPLETED
RUN THE SQL TUNING ADVISOR REPORT
SQL> set pagesize 120
SQL> set pagesize 5000
SQL> set linesize 120
SQL> set long 50000
SQL> set longchunksize 500000
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(‘CHECK_PAYROLL_RUN’) FROM DUAL;
DBMS_SQLTUNE.REPORT_TUNING_TASK(‘CHECK_PAYROLL_RUN’)
—————————————————————————————————-
GENERAL INFORMATION SECTION
——————————————————————————-
Tuning Task Name : CHECK_PAYROLL_RUN
Tuning Task Owner : SYS
Scope : COMPREHENSIVE
Time Limit(seconds) : 60
Completion Status : COMPLETED
Started at : 05/09/2008 10:34:44
Completed at : 05/09/2008 10:35:54
Number of Index Findings : 1
Number of SQL Restructure Findings: 1
Number of Errors : 1
——————————————————————————-
Schema Name: BWINT
SQL ID : 35mw20gycm0q5
SQL Text : INSERT INTO BWINT.PAYSLIP_CURRENT_RUN (ASSIGNMENT_ID, FULL_NAME,
ASSIGNMENT_NUMBER, LOCATION_CODE,
PAYROLL_ACTION_ID,PAY_DATE) (SELECT
PAA.ASSIGNMENT_ID, PAA.FULL_NAME,
PAA.ASSIGNMENT_NUMBER,
PAA.LOCATION_CODE, PAA.PAYROLL_ACTION_ID,PAA.PERIOD_END_
DATE FROM
PAY_AU_ASG_PAYMENT_RUNS_V PAA, PER_PERIODS_OF_SERVICE
PPS WHERE
PAA.PAYROLL_ACTION_ID = :B1 AND PAA.PERSON_ID =
PPS.PERSON_ID AND
PPS.ACTUAL_TERMINATION_DATE IS NULL)
——————————————————————————-
FINDINGS SECTION (2 findings)
——————————————————————————-
1- Index Finding (see explain plans section below)
————————————————–
The execution plan of this statement can be improved by creating one or more
indices.
Recommendation (estimated benefit: 100%)
—————————————-
– Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index HR.IDX$$_04DE0001 on HR.PER_ALL_ASSIGNMENTS_F(‘ASSIGNMENT_ID’)
;
– Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index HR.IDX$$_04DE0002 on HR.PER_ALL_PEOPLE_F(‘PERSON_ID’);
– Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index HR.IDX$$_04DE0003 on HR.PAY_PAYROLL_ACTIONS(‘DATE_EARNED’,’PAY
ROLL_ID’);
Rationale
———
Creating the recommended indices significantly improves the execution plan
of this statement. However, it might be preferable to run “Access Advisor”
using a representative SQL workload as opposed to a single statement. This
will allow to get comprehensive index recommendations which takes into
account index maintenance overhead and additional space consumption.
2- Restructure SQL finding (see plan 1 in explain plans section)
—————————————————————-
An expensive cartesian product operation was found at line ID 17 of the
execution plan.
Recommendation
————–
– Consider removing the disconnected table or view from this statement or
add a join condition which refers to it.
Rationale
———
A cartesian product should be avoided whenever possible because it is an
expensive operation and might produce a large amount of data.
0 Comments