It will prompt you for the SQL details, usually SQL_ID, tuning scope, and report options depending on your Oracle version.
1. Check available SQL Tuning Advisor tasks
Run as DBA/SYS:
SET LINESIZE 220
COL owner FORMAT A20
COL task_name FORMAT A35
COL advisor_name FORMAT A35
COL status FORMAT A15
COL created FORMAT A20
COL last_modified FORMAT A20
SELECT owner,
task_name,
advisor_name,
status,
TO_CHAR(created, 'DD-MON-YYYY HH24:MI:SS') AS created,
TO_CHAR(last_modified, 'DD-MON-YYYY HH24:MI:SS') AS last_modified
FROM dba_advisor_tasks
WHERE advisor_name = 'SQL Tuning Advisor'
ORDER BY created DESC;
For your specific task:
SELECT owner,
task_name,
advisor_name,
status,
execution_type,
TO_CHAR(created, 'DD-MON-YYYY HH24:MI:SS') AS created,
TO_CHAR(execution_start, 'DD-MON-YYYY HH24:MI:SS') AS execution_start,
TO_CHAR(execution_end, 'DD-MON-YYYY HH24:MI:SS') AS execution_end
FROM dba_advisor_tasks
WHERE task_name = 'TASK_13481';
Expected status should ideally be:
COMPLETED
If the task is not completed, do not accept the profile yet.
2. Check SQL Tuning Advisor executions for the task
COL task_name FORMAT A35
COL execution_name FORMAT A35
COL status FORMAT A15
SELECT owner,
task_name,
execution_name,
status,
TO_CHAR(execution_start, 'DD-MON-YYYY HH24:MI:SS') AS execution_start,
TO_CHAR(execution_end, 'DD-MON-YYYY HH24:MI:SS') AS execution_end
FROM dba_advisor_executions
WHERE task_name = 'TASK_13481'
ORDER BY execution_start DESC;
3. Check findings for the task
SET LINESIZE 220
COL type FORMAT A30
COL message FORMAT A100
COL more_info FORMAT A100
SELECT owner,
task_name,
finding_id,
type,
message,
more_info
FROM dba_advisor_findings
WHERE task_name = 'TASK_13481'
ORDER BY finding_id;
Look for findings related to:
SQL Profile
Statistics
Index
Restructure SQL
4. Check recommendations for the task
SET LINESIZE 220
COL type FORMAT A30
COL benefit FORMAT 999999999
COL rationale FORMAT A120
SELECT owner,
task_name,
rec_id,
type,
rank,
benefit,
rationale
FROM dba_advisor_recommendations
WHERE task_name = 'TASK_13481'
ORDER BY rank, rec_id;
If SQL Profile is recommended, you should see a recommendation type related to SQL Profile.
5. Generate SQL Tuning Advisor report before accepting profile
SET LONG 10000000
SET LONGCHUNKSIZE 10000000
SET LINESIZE 220
SET PAGESIZE 50000
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(
task_name => 'TASK_13481',
type => 'TEXT',
level => 'ALL',
section => 'ALL',
owner_name => 'SYS'
) AS report
FROM dual;
If your Oracle version does not accept owner_name, use:
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(
task_name => 'TASK_13481',
type => 'TEXT',
level => 'ALL',
section => 'ALL'
) AS report
FROM dual;
Review the report carefully before accepting. SQL Tuning Advisor can recommend SQL Profiles, indexes, stats collection, SQL rewrite, or SQL Plan Baselines.
6. Check if SQL Profile already exists
Before running ACCEPT_SQL_PROFILE, check existing profiles:
SET LINESIZE 220
COL name FORMAT A40
COL category FORMAT A20
COL status FORMAT A10
COL force_matching FORMAT A15
COL sql_text FORMAT A80
SELECT name,
category,
status,
force_matching,
created,
last_modified,
SUBSTR(sql_text, 1, 80) AS sql_text
FROM dba_sql_profiles
ORDER BY created DESC;
Search for a profile related to this task or SQL:
SELECT name,
category,
status,
force_matching,
created,
last_modified,
SUBSTR(sql_text, 1, 120) AS sql_text
FROM dba_sql_profiles
WHERE name LIKE '%13481%'
OR description LIKE '%TASK_13481%'
ORDER BY created DESC;
7. Accept SQL Profile from the task
Your command is broadly correct:
EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(
task_name => 'TASK_13481',
task_owner => 'SYS',
replace => TRUE
);
I usually prefer giving the profile a meaningful name:
DECLARE
l_profile_name VARCHAR2(128);
BEGIN
l_profile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(
task_name => 'TASK_13481',
task_owner => 'SYS',
name => 'SP_TASK_13481',
replace => TRUE,
force_match => FALSE
);
DBMS_OUTPUT.PUT_LINE('Accepted SQL Profile: ' || l_profile_name);
END;
/
Use this only after the report confirms a SQL Profile recommendation with good estimated benefit.
8. Verify the accepted SQL Profile
SELECT name,
category,
status,
force_matching,
created,
last_modified,
SUBSTR(sql_text, 1, 120) AS sql_text
FROM dba_sql_profiles
WHERE name = 'SP_TASK_13481';
Check if it is enabled:
SELECT name,
status
FROM dba_sql_profiles
WHERE name = 'SP_TASK_13481';
Expected:
ENABLED
9. Disable or drop if needed
Disable profile:
BEGIN
DBMS_SQLTUNE.ALTER_SQL_PROFILE(
name => 'SP_TASK_13481',
attribute_name => 'STATUS',
value => 'DISABLED'
);
END;
/
Drop profile:
BEGIN
DBMS_SQLTUNE.DROP_SQL_PROFILE(
name => 'SP_TASK_13481',
ignore => TRUE
);
END;
/
Recommended validation sequence
For your case, run in this order:
SELECT owner, task_name, advisor_name, status
FROM dba_advisor_tasks
WHERE task_name = 'TASK_13481';
SELECT owner, task_name, rec_id, type, rank, benefit
FROM dba_advisor_recommendations
WHERE task_name = 'TASK_13481'
ORDER BY rank;
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(
task_name => 'TASK_13481',
type => 'TEXT',
level => 'ALL',
section => 'ALL',
owner_name => 'SYS'
)
FROM dual;
Only after confirming the recommendation, execute:
EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name => 'TASK_13481', task_owner => 'SYS', replace => TRUE);
No comments:
Post a Comment