Tips on Using SQL Access Advisor in Oracle

oracleperformancetuning

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.

Best Answer

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

Related Solutions

Multiple Oracle Instances — is this a good practice

Isaac, preferably we run one instance on one server and implement the different applications as schemas and services in that single datatabase. If the server has enough memory, there won't be a problem, if the applications all behave like good Oracle citizens. As soon as there is an application that does not use bind variables there is a growing pain on the host. The same for applications that keep creating new connections every few seconds, instead of re-using existing connections. Things like auto commit also does not help to improve the situation. What is the normal behavior of your application? Does it do large updates? When does it do a commit? How much redo is generate/hour? Does your app use bind variables? In many cases, the location of the online redolog files is critical. If the app does generate a lot of redo (or competes with an other app that does this), the logwriter will slow down and cause a big performance degradation because the session is waiting for the logfile write to complete. Give you customer a disk layout where the redo's are on dedicated raid-10 disks and see how much this helps. Also find out the memory allocation situation. There might be a need for a fair redistribution of available memory.

I hope this helps

Oracle SQL, how to access latest queries and their data

To count the rows deleted just after the delete statement use SQL%ROWCOUNT. Here is a demonstration.

--Setup.
drop table t1;
create table t1 as (select level x from dual connect by level <=5);

set serveroutput on size 1000000 format wrapped
DECLARE
   vCount Number(3);
BEGIN
   DELETE FROM T1 WHERE rownum<=2;
   vCount := SQL%ROWCOUNT;  --<<---<<---<<---<<---<<---<<---<<---<<---
   DBMS_Output.Put_Line('RowCount:' || vCount);
END;
/

For your broader question you can look into tracing, auditing, flashback database, flashback query, logminer, materialized views, streams, and change data capture (not necessarily in that order). We would need more details about what data you need and why before directing you any more specifically.

Best Answer

Related Solutions

Multiple Oracle Instances — is this a good practice

Oracle SQL, how to access latest queries and their data

Related Question