Performance Monitoring
In order to investigate locking session and killing session, DBA need to follow below steps
Login on Database and check blocking session with below commands
1) To check session lock on database
SELECT DECODE(request,0,'Holder: ','Waiter: ') ||
sid sess, id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request > 0)
ORDER BY id1, request;
2) To identify session as well as SQL details
select sid,serial#,osuser,username,program,status,SQl_id from v$session where sid=&sid;
or
select s.username, s.sid, s.serial#,t.sql_text "Last SQL"from v$session s, v$sqlarea t where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and s.sid = '&sid';
3) Map OS processes with database process as below
select p.spid,s.sql_id,s.sql_hash_value,s.sql_id,s.sid,s.serial#,s.username,s.event,to_char(s.logon_time,'dd-mon:hh24:mi') from v$session s, v$process p
where p.addr=s.paddr and s.sid = '&sid';
4) Session can be killed from OS prompt as well as database level
From OS level
$ kill -9 <spid>
From Database level
alter system kill session 'sid,serial#' immediate;
eg
alter system kill session '41,5' immediate;
5) Cross check session at database level.
select sid,serial#,osuser,username,program,status,SQl_id from v$session where sid=&sid;
Here are some basic
tips to work with Execution Plan stability, using Baselines.
==========================================================
We use the below
queries to see the available execution plans and see which plan was running
fine.
1. @?/rdbms/admin/awrsqrpi.sql
---> This will generate the html page for the
required query based on the SQLID and its awr history.
or
2. dbms_xplan.display_awr()
Ex: select * from
TABLE(dbms_xplan.display_awr('47qjdv3ncanhr'));
or
3. USING GRID Control 12c
To gather the history of a SQL
execution and the plans used during those runs, obtain the SQL Id to be
evaluated, connect to the GRID Control 12c:
select the
Targets/Databases -->Select the database -->Performance/SQL/Search
SQL-->
Check AWR Snapshots
-->Enter the SQL ID in the SQL ID filed / Search-->
Verify the
executions and the different Hash Plans used.
The ones with the Smallest Elapsed
Times are the best execution Plans for the SQL.
·
If the HASH Plan is still in the Cursor Cache it can be created as a
baseline and instructed to run every time that SQL ID is loaded to the Shared
Pool.
·
If the HASH Plan is no longer in the Cursor Cache, then it is still
possible to load the HASH Plan to a Sql Tuning Set and create a baseline from
the STS and assign it the SQL ID as well. Take note of the Snap
ID (from the GRID SQL Search above) for the desired HASH Plan
HASH /SQL plan needed
found in the Cursor Cache
Now you know which
hash plan hash to be fixed. Now follow the below example. If the needed plan is
found in the cursor cache then it is very simple to create a baseline and
fixing the plan for the SQL query.
Ex: Determined the
Hash Plan: 2601263939 is the best to run against the SQL
ID: 47qjdv3ncanhr
1.Create the
Baseline:
var v_num number;
exec
:v_num:=dbms_spm.load_plans_from_cursor_cache(sql_id =>'47qjdv3ncanhr',plan_hash_value
=> 2601263939);
OR
Example from
Internet for script:
SQL> !cat
create_baseline.sql
var ret number
exec :ret :=
dbms_spm.load_plans_from_cursor_cache(sql_id=>'&sql_id',
plan_hash_value=>&plan_hash_value);
SQL>
@create_baseline
Enter value for
sql_id: 47qjdv3ncanhr
Enter value for
plan_hash_value: 2601263939
2. Verify the
baseline got created or not
=================================
SQL> select
sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines;
SQL_HANDLE
PLAN_NAME
ENA ACC FIX
------------------------------
------------------------------
--- ---
---
SQL_4bd90f15ef3c1f10
SQL_PLAN_4rq8g2rrms7sh3cc6a555 YES
YES NO
To see all the details, this will create a file
with all the baseline info.:
spool
baseline_plan.txt
select * from
table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_4bd90f15ef3c1f10',
format=>'basic'));
verify the spool
file to confime the SQL ID and the HASH associated with it.
3.TO MODIFY A SQL PLAN BASELINE
var v_num number;
exec
:v_num:=dbms_spm.ALTER_SQL_PLAN_BASELINE (sql_handle
=>'SQL_4bd90f15ef3c1f10',plan_name => 'SQL_PLAN_4rq8g2rrms7sh3cc6a555',
attribute_name=> 'FIXED', attribute_value => 'YES');
Attributes
· enabled (YES/NO)
: If YES, the plan is available for the optimizer if it is also marked as
accepted.
· fixed (YES/NO)
: If YES, the SQL plan baseline will not evolve over time. Fixed plans are used
in preference to non-fixed plans.
· autopurge (YES/NO)
: If YES, the SQL plan baseline is purged automatically if it is not used for a
period of time.
· plan_name :
Used to amend the SQL plan name, up to a maximum of 30 character.
· description :
Used to amend the SQL plan description, up to a maximum of 30 character.
Sometimes
the required HASH / SQL plan will not be present in the Cursor Cache, then you have to load it from a AWR snapshots.
Steps are as
below:
======================================
To load plans to
the cursor cache from awr snapshots:
1. --
Drop SQL Tuning Set (STS)
BEGIN
DBMS_SQLTUNE.DROP_SQLSET(
sqlset_name => 'SAMPLE_TUNING_SET');
END;
2. --
Create SQL Tuning Set (STS)
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'SAMPLE_TUNING_SET',
description => 'SQL Tuning Set for loading plan into SQL Plan Baseline');
END;
3.-- Populate STS
from AWR using a time duration when the desired plan was used.
Retrieve the begin
Snap ID from the same session described in the GRID Contol above or by
:
SELECT SNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME FROM
dba_hist_snapshot ORDER BY END_INTERVAL_TIME DESC;
Note: Specify the
sql_id in the basic_filter (other predicates are available, see desc
dba_hist_snapshot) if necessary.
DECLARE
cur
sys_refcursor;
BEGIN
OPEN cur FOR
SELECT VALUE(P)
FROM TABLE(
dbms_sqltune.select_workload_repository(begin_snap=>1477,
end_snap=>1478,basic_filter=>'sql_id = ''9n82zq1gkpg2t''',attribute_list=>'ALL')
) p;
DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'SAMPLE_TUNING_SET',
populate_cursor=>cur);
CLOSE cur;
END;
/
4. -- List
out SQL Tuning Set contents to check we got what we wanted
SELECT
first_load_time,
executions
as execs,
parsing_schema_name,
elapsed_time / 1000000 as elapsed_time_secs,
cpu_time /
1000000 as cpu_time_secs,
buffer_gets,
disk_reads,
direct_writes,
rows_processed,
fetches,
optimizer_cost,
sql_plan,
plan_hash_value,
sql_id,
sql_text
FROM
TABLE(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name => 'SAMPLE_TUNING_SET'));
5.-- Finally create
the baseline from the STS:
DECLARE
my_plans
pls_integer;
BEGIN
my_plans :=
DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
sqlset_name => 'SAMPLE_TUNING_SET',
basic_filter=>'plan_hash_value = ''1117073691'''
);
END;
/
6.-- Verify the
baseline got created and modify it if necessary
select sql_handle,
plan_name, enabled, accepted, fixed from dba_sql_plan_baselines;
exec
:v_num:=dbms_spm.ALTER_SQL_PLAN_BASELINE (sql_handle
=>'SQL_ab2ab5c194ee0fc8',plan_name => 'SQL_PLAN_aqapps6afw3y81722054c',
attribute_name=> 'FIXED', attribute_value
=> 'YES');
7.-- Verify all
details for the new Baseline:
spool
baseline_plan.txt
select * from
table(
dbms_xplan.display_sql_plan_baseline(
sql_handle=>'SQL_ab2ab5c194ee0fc8',
format=>'basic'));
using below query
to see which all plan_hash_value are being used by my sql_id...
select ss.snap_id,
ss.instance_number
node,
begin_interval_time,
sql_id,
plan_hash_value,
nvl(executions_delta,0)
execs,
round((elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000,2)
avg_etime,
round((buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)),2)
avg_lio
from
DBA_HIST_SQLSTAT S,
DBA_HIST_SNAPSHOT
SS
where sql_id =
'xxxxxxxx'
and ss.snap_id =
S.snap_id
and ss.instance_number
= S.instance_number
and
executions_delta > 0
order by 1 desc, 2,
3;
HOW TO GET 11G
TO USE THE 10G EXECUTION PLAN
The next challenge was to get SQL with exactly the
same SQL text as the application used (exactly the same SQL_ID) and set a
SQL plan baseline. I work from Australia and the client’s team is based in
North America. Therefore, I couldn’t just call the developer and ask to
re-execute the SQL. I had to come with a method that would allow me to find the
exact SQL text without executing it. The SQL’s execution took a lot of
resources Oracle captured SQL in AWR repository. (The client had a Diagnostic
license.) I used the following statement to get the SQL and associated “good”
execution plan in a shared pool.
alter
session set optimizer_features_enable='10.2.0.4';
declare
v_sql
varchar2(8000);
c
NUMBER;
begin
select
sql_text into v_sql from DBA_HIST_SQLTEXT where sql_id='djkbyr8vkc64h';
c :=
dbms_sql.open_cursor;
dbms_sql.parse(c,
v_sql, dbms_sql.NATIVE);
dbms_sql.close_cursor(c);
end;
I confirmed that the “good” execution plan had been
used by the following SQL:
select
sql_id,LAST_LOAD_TIME,PLAN_HASH_VALUE, exact_matching_signature, sysdate from
v$sqlarea where sql_id='djkbyr8vkc64h';
SQL_ID
LAST_LOAD_TIME PLAN_HASH_VALUE EXACT_MATCHING_SIGNATURE SYSDATE
-------------
------------------- --------------- ------------------------
-------------------
djkbyr8vkc64h
2012.11.12 01:25:51 810205201 14465951278806438046 2012.11.12 01:26:04
The final bit was to create a SQL plan baseline
based on the pair of SQL_ID and PLAN_HASH_VALUE. From there, it was too easy.
:)
n
number;
begin
n:=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
sql_id
=> 'djkbyr8vkc64h',
plan_hash_value
=> '810205201'
FIXED
=> 'YES',
ENABLED
=> 'YES');
dbms_output.put_line(n);
end;
/
PL/SQL
procedure successfully completed.
To validate that the base line was created for the
SQL:
col
SQL_HANDLE for a25
col
CREATED for a30
col
sb.last_executed for a30
SELECT
sb.sql_handle,sb.plan_name,
sb.origin,sb.version,
cast(sb.created
as date)
created,
cast(sb.last_executed
as date)
last_executed,
sb.enabled,
sb.accepted FROM dba_sql_plan_baselines
sb
WHERE sb.signature
= 14465951278806438046;
SQL_HANDLE
PLAN_NAME ORIGIN VERSION CREATED LAST_EXECUTED ENA ACC
-------------------------
------------------------------ -------------- ----------
------------------------------ ------------------- --- ---
SQL_c8c1620b4f5d909e
SQL_PLAN_cjhb21d7pv44y27600b06 MANUAL-LOAD 11.2.0.3.0 2012.11.12 01:31:58 YES YES
Just in case we would need to rollback the change,
we just need to run the following PL/SQL block:
DECLARE
i
NATURAL;
BEGIN
i :=
DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
sql_handle=>'SQL_c8c1620b4f5d909e',
PLAN_NAME=>
'SQL_PLAN_cjhb21d7pv44y27600b06',
attribute_name=>'enabled',attribute_value=>'no');
dbms_output.put_line(i);
END;
My query is picking a bad
execution plan , how to fix it to use the good execution plan available ( using
Oracle baselines ) ?
Using Oracle baselines you can fix
the sql plan for a SQLID:
SQL plan management is a preventative
mechanism that records and evaluates the execution plans of SQL statements over
time. This mechanism can build a SQL plan baseline, which is a set of accepted
plans for a SQL statement. The accepted plans have been proven to perform well.
The goal of SQL plan baselines is to
preserve the performance of corresponding SQL statements, regardless of changes
in the database. Examples of changes include:
·
New optimizer version
·
Changes to optimizer statistics and optimizer parameters
·
Changes to schema and metadata definitions
·
Changes to system settings
·
SQL profile creation
SQL plan baselines
cannot help in cases where an event has caused irreversible execution plan
changes, such as dropping an index.
The SQL tuning
features of Oracle Database generate SQL profiles that help the optimizer to
produce well-tuned plans. However, this mechanism is reactive and cannot
guarantee stable performance when drastic database changes occur. SQL tuning
can only resolve performance issues after they have occurred and are
identified.
For example, a SQL statement may become high-load because of a plan
change, but SQL tuning cannot solve this problem until after the plan change
occurs.
To know more details, follow the below link
To identify blocking session and killing it after approval
Blocking session occurs when one session acquired an exclusive lock on an object and doesn't release it,another session (one or more) want to modify the same data.First session will block the second until it completes its job.
Mostly DML statement caused this issue
From the view of the user It look like the application is completely hanged while waiting for the first session to release its lock.
In most of case, session lock release automatically when we get mutliple email regarding session lock
DBA need to investigate and identify block session on database and inform to IM Team/Service management Team with complete SQL details .
These details will help IM Team/Service management team to get approval shop direct or application team .Once IM Team/Service management team get approval from shop direct or application team
The will inform to DBA to kill session
