Hi DBa's,
I would like to get a single page to narrow down the performance tuning in oracle database.
I have listed the step by step process to do performance tuning in oracle database.(looking SQL query tuning)
step by step performance tuning in oracle database
---------------------------------------------------------
1.ask the user when & what time they face slowness in database?
Narrow down the time and specific sql facing slowness in database and find the SQL_ID using V$sql or dba_hist_sqlstat
2.collect the AWR for the specified time and check the TOP elapsed SQL,TOP CPU consuming SQL and check the current Vs past SQL execution time using AWR.
3.if you want you may compare the database/SQL performance using AWR report for specific time using OEM or manually generate awr report.
an AWR report that compares two distinct time periods
@$ORACLE_HOME/rdbms/adminawrddrpt.sql
@$ORACLE_HOME/rdbms/admin/awrrpt.sql,incase of RAC use @$ORACLE_HOME/rdbms/admin/awrgrpt.sql
4.run the ADDM on the same time and check if any recommendation from ADDM report.
@$ORACLE_HOME/rdbms/admin/addmrpt.sql
Please enter the begin & end snap ID to get the addm report and check the logs,if you see any recommendation review and implment it.
5.In real time check on DB if any blocking/locking happeening when the query runs on DB?,you can seethis OEM page also.
select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status from v$lock l1, v$session s1, v$lock l2, v$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 ;
6.In real time check the DB has any library cache lock using the below query.,take the action according to situation.you can seethis OEM page also.
select sid Waiter, p1raw,
substr(rawtohex(p1),1,30) Handle,
substr(rawtohex(p2),1,30) Pin_addr
from v$session_wait where wait_time=0 and event like '%library cache%';
7.check if you have any major wait event on database during time,using the AWR repot for historical and use the script for realtime events.
set lines 100
set pages 100
col event format a40
select EVENT ,TOTAL_WAITS,TIME_WAITED from v$system_event;
8.check if any sql profiles disabled using the below query
set lines 200 pages 200
select name,type,status,created from dba_sql_profiles;
9.check if any sql plan baseline modified for the below SQL_ID
script to check the SQL plan baseline status
-----------------------------------------------------
select SQL_HANDLE ,accepted,fixed,enabled ,created from DBA_SQL_PLAN_BASELINES;
10.check the table & index belongs to SQL_ID having any fragmentation using the below query.
Enter value for 1: <object_type,either table or index>
Enter value for 2: <owner_name>
Enter value for 3: <Table_name>
SET SERVEROUTPUT ON SIZE 1000000
SET LINESIZE 200
SET VERIFY OFF
DECLARE
l_object_id NUMBER;
l_task_name VARCHAR2(32767) := 'SEGMENT_ADVISOR_TASK';
l_object_type VARCHAR2(32767) := UPPER('&1');
l_attr1 VARCHAR2(32767) := UPPER('&2');
l_attr2 VARCHAR2(32767) := UPPER('&3');
BEGIN
IF l_attr2 = 'NULL' THEN
l_attr2 := NULL;
END IF;
DBMS_ADVISOR.create_task (
advisor_name => 'Segment Advisor',
task_name => l_task_name);
DBMS_ADVISOR.create_object (
task_name => l_task_name,
object_type => l_object_type,
attr1 => l_attr1,
attr2 => l_attr2,
attr3 => NULL,
attr4 => 'null',
attr5 => NULL,
object_id => l_object_id);
DBMS_ADVISOR.set_task_parameter (
task_name => l_task_name,
parameter => 'RECOMMEND_ALL',
value => 'TRUE');
DBMS_ADVISOR.execute_task(task_name => l_task_name);
FOR cur_rec IN (SELECT f.impact,
o.type,
o.attr1,
o.attr2,
f.message,
f.more_info
FROM dba_advisor_findings f
JOIN dba_advisor_objects o ON f.object_id = o.object_id AND f.task_name = o.task_name
WHERE f.task_name = l_task_name
ORDER BY f.impact DESC)
LOOP
DBMS_OUTPUT.put_line('..');
DBMS_OUTPUT.put_line('Type : ' || cur_rec.type);
DBMS_OUTPUT.put_line('Attr1 : ' || cur_rec.attr1);
DBMS_OUTPUT.put_line('Attr2 : ' || cur_rec.attr2);
DBMS_OUTPUT.put_line('Message : ' || cur_rec.message);
DBMS_OUTPUT.put_line('More info : ' || cur_rec.more_info);
END LOOP;
DBMS_ADVISOR.delete_task(task_name => l_task_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('Error : ' || DBMS_UTILITY.format_error_backtrace);
DBMS_ADVISOR.delete_task(task_name => l_task_name);
END;
/
11.compare the SQL_ID execution time current Vs past and check if the SQL_ID plan changed or not ,using below query
set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = nvl('&sql_id','gm628a6uadc9a')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3
/
12.if you find any increase SQL execution time of SQL_Id,Please collect the stats for table using the below query.
dbms_stats.gather_table_stats('OWNER', 'TABLE_NAME', estimate_percent => 100, method_opt => 'for all columns size auto', cascade => true);
13.if you find any increase SQL execution time of SQL_Id,Pleaserun the SQL tunning advisor,using the below script.
Create Tuning Task:
===================
DECLARE
my_task_name VARCHAR2(30);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => '<SQL_ID to be tunned>',
scope => 'COMPREHENSIVE',
time_limit => 3600,
task_name => 'my_sql_tuning_task_1',
description => 'Tune query using sqlid');
end;
/
Execute Tuning Task:
===================
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task_1');
end;
/
Execute Tuning Task:
===================
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task_1');
end;
/
SET LONG 100000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
set pagesize 100
***************************
--TO GET SUMMARY INFORMATION
--***************************
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_1') from DUAL;
--***************************
--TO GET DETAILED INFORMATION
--***************************
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('my_sql_tuning_task_1','TEXT','ALL','ALL') FROM DUAL;
If necessary (the profile does not help),
you can drop the profile.
BEGIN
DBMS_SQLTUNE.DROP_SQL_PROFILE ('MY_SQL_TUNING_TASK_1');
END;
/
14.if you find any recommendation from SQL tunning advisor,Please implement and check the SQL performance.
I would like to get a single page to narrow down the performance tuning in oracle database.
I have listed the step by step process to do performance tuning in oracle database.(looking SQL query tuning)
step by step performance tuning in oracle database
---------------------------------------------------------
1.ask the user when & what time they face slowness in database?
Narrow down the time and specific sql facing slowness in database and find the SQL_ID using V$sql or dba_hist_sqlstat
2.collect the AWR for the specified time and check the TOP elapsed SQL,TOP CPU consuming SQL and check the current Vs past SQL execution time using AWR.
3.if you want you may compare the database/SQL performance using AWR report for specific time using OEM or manually generate awr report.
an AWR report that compares two distinct time periods
@$ORACLE_HOME/rdbms/adminawrddrpt.sql
@$ORACLE_HOME/rdbms/admin/awrrpt.sql,incase of RAC use @$ORACLE_HOME/rdbms/admin/awrgrpt.sql
4.run the ADDM on the same time and check if any recommendation from ADDM report.
@$ORACLE_HOME/rdbms/admin/addmrpt.sql
Please enter the begin & end snap ID to get the addm report and check the logs,if you see any recommendation review and implment it.
5.In real time check on DB if any blocking/locking happeening when the query runs on DB?,you can seethis OEM page also.
select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status from v$lock l1, v$session s1, v$lock l2, v$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 ;
6.In real time check the DB has any library cache lock using the below query.,take the action according to situation.you can seethis OEM page also.
select sid Waiter, p1raw,
substr(rawtohex(p1),1,30) Handle,
substr(rawtohex(p2),1,30) Pin_addr
from v$session_wait where wait_time=0 and event like '%library cache%';
7.check if you have any major wait event on database during time,using the AWR repot for historical and use the script for realtime events.
set lines 100
set pages 100
col event format a40
select EVENT ,TOTAL_WAITS,TIME_WAITED from v$system_event;
8.check if any sql profiles disabled using the below query
set lines 200 pages 200
select name,type,status,created from dba_sql_profiles;
9.check if any sql plan baseline modified for the below SQL_ID
script to check the SQL plan baseline status
-----------------------------------------------------
select SQL_HANDLE ,accepted,fixed,enabled ,created from DBA_SQL_PLAN_BASELINES;
10.check the table & index belongs to SQL_ID having any fragmentation using the below query.
Enter value for 1: <object_type,either table or index>
Enter value for 2: <owner_name>
Enter value for 3: <Table_name>
SET SERVEROUTPUT ON SIZE 1000000
SET LINESIZE 200
SET VERIFY OFF
DECLARE
l_object_id NUMBER;
l_task_name VARCHAR2(32767) := 'SEGMENT_ADVISOR_TASK';
l_object_type VARCHAR2(32767) := UPPER('&1');
l_attr1 VARCHAR2(32767) := UPPER('&2');
l_attr2 VARCHAR2(32767) := UPPER('&3');
BEGIN
IF l_attr2 = 'NULL' THEN
l_attr2 := NULL;
END IF;
DBMS_ADVISOR.create_task (
advisor_name => 'Segment Advisor',
task_name => l_task_name);
DBMS_ADVISOR.create_object (
task_name => l_task_name,
object_type => l_object_type,
attr1 => l_attr1,
attr2 => l_attr2,
attr3 => NULL,
attr4 => 'null',
attr5 => NULL,
object_id => l_object_id);
DBMS_ADVISOR.set_task_parameter (
task_name => l_task_name,
parameter => 'RECOMMEND_ALL',
value => 'TRUE');
DBMS_ADVISOR.execute_task(task_name => l_task_name);
FOR cur_rec IN (SELECT f.impact,
o.type,
o.attr1,
o.attr2,
f.message,
f.more_info
FROM dba_advisor_findings f
JOIN dba_advisor_objects o ON f.object_id = o.object_id AND f.task_name = o.task_name
WHERE f.task_name = l_task_name
ORDER BY f.impact DESC)
LOOP
DBMS_OUTPUT.put_line('..');
DBMS_OUTPUT.put_line('Type : ' || cur_rec.type);
DBMS_OUTPUT.put_line('Attr1 : ' || cur_rec.attr1);
DBMS_OUTPUT.put_line('Attr2 : ' || cur_rec.attr2);
DBMS_OUTPUT.put_line('Message : ' || cur_rec.message);
DBMS_OUTPUT.put_line('More info : ' || cur_rec.more_info);
END LOOP;
DBMS_ADVISOR.delete_task(task_name => l_task_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('Error : ' || DBMS_UTILITY.format_error_backtrace);
DBMS_ADVISOR.delete_task(task_name => l_task_name);
END;
/
11.compare the SQL_ID execution time current Vs past and check if the SQL_ID plan changed or not ,using below query
set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = nvl('&sql_id','gm628a6uadc9a')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3
/
12.if you find any increase SQL execution time of SQL_Id,Please collect the stats for table using the below query.
dbms_stats.gather_table_stats('OWNER', 'TABLE_NAME', estimate_percent => 100, method_opt => 'for all columns size auto', cascade => true);
13.if you find any increase SQL execution time of SQL_Id,Pleaserun the SQL tunning advisor,using the below script.
Create Tuning Task:
===================
DECLARE
my_task_name VARCHAR2(30);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => '<SQL_ID to be tunned>',
scope => 'COMPREHENSIVE',
time_limit => 3600,
task_name => 'my_sql_tuning_task_1',
description => 'Tune query using sqlid');
end;
/
Execute Tuning Task:
===================
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task_1');
end;
/
Execute Tuning Task:
===================
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task_1');
end;
/
SET LONG 100000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
set pagesize 100
***************************
--TO GET SUMMARY INFORMATION
--***************************
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_1') from DUAL;
--***************************
--TO GET DETAILED INFORMATION
--***************************
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('my_sql_tuning_task_1','TEXT','ALL','ALL') FROM DUAL;
If necessary (the profile does not help),
you can drop the profile.
BEGIN
DBMS_SQLTUNE.DROP_SQL_PROFILE ('MY_SQL_TUNING_TASK_1');
END;
/
14.if you find any recommendation from SQL tunning advisor,Please implement and check the SQL performance.
No comments:
Post a Comment