Posts

Showing posts from November, 2013

Oracle 10g:How to use SQL Tuning Advisor.

Image
SQL Tuning Advisor Dear User, Read More about SQL Tuning Advisor from Oracle site http://docs.oracle.com/cd/B28359_01/server.111/b28274/sql_tune.htm#CHDJDFGE ***Tuning SQL Queries by Oracle SQL Tuning Advisor*** A)SQL Query tuning by SQL_TEXT. 1.Get the Query info from AWR Report like  Top CPU Consuming Queries or SQL Queries by Elapsed time. SQL>select SQL_TEXT,sql_id from v$sqlarea where sql_id='&sql_id'; 2.Get bind variable details from V$SQL_BIND_CAPTURE or DBA_HIST_SQLBIND. SQL>select address, hash_value,name,last_captured,DATATYPE_STRING,VALUE_STRING from  V$SQL_BIND_CAPTURE where sql_id= '&sql_id';  order by last_captured; SQL>select sql_id,name,last_captured,DATATYPE_STRING,VALUE_STRING from DBA_HIST_SQLBIND where sql_id= '&sql_id'   and trunc(last_captured)=trunc(sysdate); 3.Prepare Query and add bind variable values(VALUE_STRING) into SQL Queries.Values should match with DATAT