STEPS OF HANDALING
PERFORMACE ISSUES
1.
Check the current plan of
execution for the sql_id
Select
inst_id,sql_id,parsing_schema_name, PLAN_HASH_VALUE, executions,
elapsed_time,sql_profile,(elapsed_time/executions)/1000000 as avg_elapsed,
sql_plan_baseline from gv$sqlarea where sql_id in ('sql_id');
If the
current plan is best from all the plans available in the history baseline the
plan so that the sql_id will pick the plan further.
2.
If the plan is having more average
elapsed time check the history of the sql_id for any better plan.
SELECT snap_id,
(select begin_interval_time from dba_hist_snapshot
where instance_number=a.instance_number and snap_id=a.snap_id) begin_time,
(select end_interval_time from dba_hist_snapshot
where instance_number=a.instance_number and snap_id=a.snap_id) end_time,
instance_number,
parsing_Schema_name,
sql_id,
plan_hash_value,
sql_profile,
executions_delta,
(elapsed_time_delta /
decode(executions_delta,0,1,executions_delta)) msecs,
ROUND ( (elapsed_time_delta
/ decode(executions_delta,0,1,executions_delta)) / 1000000,
per_elapsed_sec,
ROUND (
(rows_processed_delta / decode(executions_delta,0,1,executions_delta)) , 2)
rows_processed_per_execution,
ROUND ( (buffer_gets_delta
/ decode(executions_delta,0,1,executions_delta)))
buffer_gets_per_executions ,
ROUND (
(disk_Reads_delta / decode(executions_delta,0,1,executions_delta)))
disk_Reads_per_executions
FROM dba_hist_sqlstat a
WHERE sql_id in ('sql_id')
and parsing_schema_name='schema name'
3.
To check the plans available in awr
select parsing_schema_name,sql_id, PLAN_HASH_VALUE, executions_delta,
elapsed_time_delta,
(decode(elapsed_time_delta,0,1,elapsed_time_delta)/decode(executions_delta,0,1,executions_delta))/1000000
as avg_elapsed
fromdba_hist_sqlstat where
EXECUTIONS_DELTA>0 and
sql_id='&SQL_ID' and parsing_schema_name='&schema_name' order by
avg_elapsed desc;
4.
If found any better plan in Cursor
Cache baseline the plan with the best available
plan in awr
set serveroutput on
declare
v_intpls_integer;
begin
v_int := dbms_spm.load_plans_from_cursor_cache (
sql_id => 'SQL_ID',
plan_hash_value =>'BEST PLAN
HASHBVALUE',
fixed => 'YES',
enabled => 'YES');
DBMS_OUTPUT.PUT_line(v_int);
end;
/
If found in AWR, baseline the plan
with following commands:
Replace SQLID with actual sql_id
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET ( sqlset_name => 'STS_SQLID',description
=> 'For Sql id xxxxxx');
END;
/
Find the snapshot from
dba_hist_sqlstat with good PHV and put in the following command
DECLARE
l_cursorDBMS_SQLTUNE.sqlset_cursor;
BEGIN
OPEN l_cursor FOR
SELECT VALUE (a) FROM
TABLE(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY
(snapshotid_begin,snapshotid_end,'sql_id= ''SQLID''', null,null,null,null,1,null,'ALL' )) a;
DBMS_SQLTUNE.load_sqlset
(sqlset_name => 'STS_SQLID',
populate_cursor =>l_cursor);
END;
/
set serveroutput on
DECLARE sts_load PLS_INTEGER;
BEGIN
sts_load :=
DBMS_SPM.load_plans_from_sqlset
( sqlset_name => 'STS_SQLID',
basic_filter => 'sql_id=''SQLID''',
fixed
=> 'YES',
enabled => 'YES'
);
DBMS_OUTPUT.put_line('Plans Loaded: '
|| sts_load );
END;
/
5.
After base lining with the best plan,
purge the old plan, profile available.
To drop SQL Profile:
execdbms_sqltune.drop_sql_profile('SYS_SQLPROF_015fe8de27e80007');
To drop SQL Plan Baseline:
Pass the
baseline name in below query and run it to generate the command. Run the drop
command to drop baseline.
select 'declare '||chr(10)||' v_sql_plan_idpls_integer;'||chr(10)||'
begin '||chr(10)||' v_sql_plan_id := dbms_spm.drop_sql_plan_baseline(sql_handle
=> '''||SQL_HANDLE||''',plan_name
=>'''||PLAN_NAME||''');end;'||chr(10)||'/' from dba_sql_plan_baselines where plan_name=’&sql_baseline_name’;
or
Select sql_handle and plan_name from
dba_sql_plan_baselines and drop the baseline accordingly based on the following
commands:
selectsql_handle,plan_name,CREATED,enabled
from dba_sql_plan_baselines order by created desc;
where plan_name='SQL_PLAN_gv3kg06wn2g2h73ab7ff8';
set serveroutput on
declare
v_intpls_integer;
BEGIN
v_int :=
DBMS_SPM.DROP_SQL_PLAN_BASELINE (sql_handle =>
'SQL_fd8e4f01b9413c50',plan_name => 'SQL_PLAN_gv3kg06wn2g2h3b514d4e');
END;
/
Query to purge from shared pool:
Run below
statement on each node, pass the SQL_ID to get the command. Run that command to
purge the SQL_ID.
select 'exec DBMS_SHARED_POOL.PURGE
('''||ADDRESS||', '||HASH_VALUE||''',''C'');' from V$SQLAREA where
SQL_ID='&SQL_ID';
exec DBMS_SHARED_POOL.PURGE
('000000323D9B99F8, 1954855109','C');
or
selectinst_id,address,hash_value,PLAN_HASH_VALUE
from gv$sqlarea where SQL_ID='SQL_ID';
from the output choose address and
hash value and run the following command:
execdbms_shared_pool.purge('address
from above query, hash value from above query ','C');
6.
Run the tuning adviser for any
recommendations
@?/rdbms/admin/sqltrpt.sql
Plug the profile if found any recommendations with minimum benefit of 50
%
Or
if we know a particular sqlid, this
will give a proper format:
run from sqlplus :
set lines 300
set trims on
set trim on
set linesize 200
set pages 2000
set long 10000000 longchunksize 100000;
variable task_name varchar2(30);
begin
:task_name :=
dbms_sqltune.create_tuning_task(sql_id=> '&SQL_ID');
dbms_sqltune.execute_tuning_task(task_name
=> :task_name);
end;
/
Spool sql_id_provided.log
selectdbms_sqltune.report_tuning_task(:task_name)
as recommendations from dual;
spool off
NOTE: make sure you are NOT plugging the
parallel profile (_PX)
Gather the stats check for the index recommendation from adviser.
If found any stats recommendation gather the stats on the tables
BEGIN
DBMS_STATS.GATHER_TABLE_STATS (
OWNNAME => 'Schema name',
TABNAME => 'Table name' ,
ESTIMATE_PERCENT =>dbms_stats.auto_sample_size,
METHOD_OPT =>‘FOR ALL
COLUMNS SIZE AUTO’,
DEGREE => 24,
GRANULARITY => 'ALL',
CASCADE => TRUE,
BLOCK_SAMPLE => TRUE,
NO_INVALIDATE => FALSE
);
END;
/
7.
To use Coe profile
cd /home/oracle/scripts/sqlt/utl
sqlplus / as sysdba
SQL>start coe_xfr_sql_profile.sql 0y9dt1vwmm7uq 688265959
then choose the best plan_hash_vlaue
It will create a .sql file using the sql_id in current directory
run that created .sql file to create the profile
EXIT
ls -lrt
sqlplus / as sysdba
SQL> @sql_script.sql
8.
Check the OEM
Find the top sql_id with high CPU usage and tune the sql_id having high
CPU usage with available best plan
9.
Find the relevant or similar sql_id
running on different clients and check
select * from TABLE(dbms_xplan.display_awr('SQL_ID'));
and for plan from cursor
cache
select * from
TABLE(dbms_xplan.display_cursor('SQL_ID'));
IF Sql_id exists in any other Client and see the plan
used in that database, compare the plans, and see elapsed time and find any
missing indexes in the current client and recommend based on that.
