Как ръчно да стартираме задача в 10g SQL Tuning Advisor
Използвайте следната процедура за да генерирате 10g SQL Tuning Advisor съобщения и препоръки от командният ред когато Database Control или Grid Control не са налични.
Първо, да създадем SQL TUNING задача.
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
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.
Изпълнете задачата за оптимизиране ...
BEGIN
dbms_sqltune.execute_tuning_task (task_name =>‘CHECK_PAYROLL_RUN’);
end;
/
PL/SQL procedure successfully completed.
ПРОВЕРКА ЗА СТАТУСА НА job-a
SQL> SELECT status FROM USER_ADVISOR_TASKS
WHERE task_name =’CHECK_PAYROLL_RUN’;
STATUS
———–
COMPLETED
Сега разгледайте SQL TUNING ADVISOR резултата по следния начин:
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(’ASSI
GNMENT_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’,'PAYROLL_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.