Dear DBA-Mates,
Hope you all are Safe and doing well!!! As we all know
about corona virus, so be-careful and safe.
Yes… Long time haven’t written anything but if you ask
me I was also lost in this world and lots of things came up and down and that’s
called life for all…
It’s ok… Let’s start the topic now which is very
important and DBA’s should know it. You all may heard about SQL Tuning but very
few people only do it.
It is very important as whenever developer/functional raise
a concerns about the request or program is taking time to complete then we can do
the SQLT which helps a lots and resolve the issue…
Please find the below steps:
1. Create Tuning Task:
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task
(
sql_id => '8b98n39ax64uj',
scope =>
DBMS_SQLTUNE.scope_comprehensive,
time_limit => 300,
task_name => '1q7d0p8qnpv51_tuning_task11',
description => 'Tuning task1 for statement 1q7d0p8qnpv51');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
2. Execute Tuning task:
EXEC
DBMS_SQLTUNE.execute_tuning_task(task_name =>
'1q7d0p8qnpv51_tuning_task11');
3. Get the Tuning advisor report:
set longchunksize 65536
SET LONG 100000;
SET PAGESIZE 50000
SET LINESIZE 200
select dbms_sqltune.report_tuning_task('1q7d0p8qnpv51_tuning_task11') from dual;
This output can be spool also, which we can give to
developer team to verify and then we can execute it.
This output will give advice and sql tune script by
which running it will run fast and get it complete.
Some useful links:
How to change database DB name using NID utility
