0

Please share how to use oracle SQL access adviser for single or multiple queries. you can assume that we know the sql ids for the queries we are interested in.

  • what did you already try? – ik_zelf Feb 14 '14 at 12:30
  • I cant use a OEM and I read the article docs.oracle.com/cd/E11882_01/server.112/e41573/advisor.htm. I am trying to find a source which has a simple example to use SQL Tuning advisor using the DBMS Advisor package – Deven Feb 14 '14 at 12:43
2

something like:

declare
task varchar2(30);

begin
task:= dbms_sqltune.CREATE_TUNING_TASK ( sql_id => '&sql_id',
plan_hash_value => '&hv' );
dbms_output.put_line (task);
dbms_sqltune.execute_tuning_task(task);
end;
/

to get the job running. List the tasks:

SELECT task_id, task_name, created, advisor_name, status
FROM dba_advisor_tasks
where TRUNC(created) = TRUNC(sysdate)
order by CREATEd desc;

when it is ready, read the report

SELECT DBMS_SQLTUNE.report_tuning_task('&tuning_task') AS recommendations FROM dual;

and if you want, implement recommendations:

DECLARE
-- accept plan profile
  l_sql_tune_task_id  VARCHAR2(20);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.accept_sql_profile (
                          task_name => '&tuning_task',
                          name      => '&profile');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

Reading the packages can help a lot.

For the SQL access advisor take a look at SQL Access Advisor in Oracle Database

  • Can you please provide a similar solution to use the SQL Access Advisor as you provided for tuning advisor. Thanks in advance. – Deven Feb 14 '14 at 14:05
  • @a_horse_with_no_name yes, you are right. Thanks for the tip, I edited accordingly. – ik_zelf Feb 14 '14 at 14:36
  • @Deven hmm, that's is quite a bit more complex. Parameters need to be added to the created task. Currently I have no example at hand. Check oracle-base.com/articles/10g/sql-access-advisor-10g.php – ik_zelf Feb 14 '14 at 14:53
0

Another way would be using inbuilt script .

@?/rdbms/admin/sqltrpt.sql.

It will display top 15 resource intensive SQL and prompt to enter the desire SQL_ID you want to tune.

You can even customize it to display the number of top SQL's .

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.