Blocking Sessions
Monitoring
===============
Blocking session
select blocking_session,blocked_session,script from
( select distinct
s1.username || '@' || s1.machine || ' ( INST=' || s1.inst_id || ' SID=' || s1.sid || ' ET=' || s1.last_call_et || 'sn. STATUS=' || s1.status || ' EVENT=' || s1.event || ' ACTION= ' || s1.action || ' PROGRAM=' || s1.program || ' MODULE=' || s1.module || ')' blocking_session,
s2.username || '@' || s2.machine || ' ( INST=' || s2.inst_id || ' SID=' || s2.sid || ' ET=' || s2.last_call_et || 'sn. STATUS=' || s2.status || ' EVENT=' || s2.event || ' ACTION= ' || s2.action || ' PROGRAM=' || s2.program || ' MODULE=' || s2.module || ')' blocked_session,
decode(s1.type,'USER','alter system kill session ''' || s1.sid || ',' || s1.serial# || ',@' || s1.inst_id || ''' immediate;' ,null)
script ,
count(*) over (partition by s1.inst_id,s1.sid) blocked_cnt
from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and s1.inst_id=l1.inst_id and s2.inst_id=l2.inst_id
and l1.block > 0 and l2.request > 0
and l1.id1 = l2.id1 and l1.id2 = l2.id2 )
order by blocked_cnt desc;
Query to Find Concurrent Request Running more than 2 hrs
Script:
set pagesize 1000
set pause off
set linesize 150
column fcr.request_id format a10 heading 'RQST_ID'
column fu.user_name format a10 heading 'Username'
column fr.responsibility_name format a35 heading 'Resp Name'
column fcp.user_concurrent_program_name format a40 heading 'Program Name'
column fcr.actual_start_date format a30 heading 'Start Date'
column fcr.status.code heading 'Status'
column fcr.actual_start_date format a10 heading 'Runtime Minutes'
column fcr.os_process_id format a15 heading 'SID, SERIAL'
column fcr.os_process_id format a10 heading 'SPID'
column fcr.os_process_id format a10 heading 'OS PID'
prompt
SELECT fcr.request_id rqst_id
,fu.user_name
,fr.responsibility_name
,fcp.user_concurrent_program_name program_name
,TO_CHAR (fcr.actual_start_date, 'DD-MON-YYYY HH24:MI:SS')start_datetime
,DECODE (fcr.status_code, 'R', 'R:Running', fcr.status_code) status
,ROUND (((SYSDATE - fcr.actual_start_date) * 60 * 24), 2) runtime_min
,fcr.oracle_process_id "SPID"
,fcr.os_process_id os_pid
FROM apps.fnd_concurrent_requests fcr
,apps.fnd_user fu
,apps.fnd_responsibility_vl fr
,apps.fnd_concurrent_programs_vl fcp
WHERE fcr.status_code LIKE 'R'
AND fu.user_id = fcr.requested_by
AND fr.responsibility_id = fcr.responsibility_id
AND fcr.concurrent_program_id = fcp.concurrent_program_id
AND fcr.program_application_id = fcp.application_id
AND ROUND (((SYSDATE - fcr.actual_start_date) * 60 * 24), 2) > 120
ORDER BY fcr.concurrent_program_id
,request_id DESC;
=======SQL Query to find All profile set at Responsibility Level
Script:
SELECT distinct fpo.profile_option_name SHORT_NAME,
fpot.user_profile_option_name NAME,
frtl.responsibility_name,
DECODE (fpov.level_id,
10001, 'Site',
10002, 'Application',
10003, 'Responsibility',
10004, 'User',
10005, 'Server',
'UnDef')
LEVEL_SET,
DECODE (TO_CHAR (fpov.level_id),
'10001', '',
'10002', fap.application_short_name,
'10003', frsp.responsibility_key,
'10005', fnod.node_name,
'10006', hou.name,
'10004', fu.user_name,
'UnDef')
"CONTEXT",
fpov.profile_option_value VALUE
FROM fnd_profile_options fpo,
fnd_profile_option_values fpov,
fnd_profile_options_tl fpot,
fnd_user fu,
fnd_application fap,
fnd_responsibility frsp,
fnd_nodes fnod,
fnd_responsibility_tl frtl,
hr_operating_units hou
WHERE fpo.profile_option_id = fpov.profile_option_id(+)
AND fpo.profile_option_name = fpot.profile_option_name
AND fu.user_id(+) = fpov.level_value
AND frsp.application_id(+) = fpov.level_value_application_id
AND frsp.responsibility_id(+) = fpov.level_value
AND fap.application_id(+) = fpov.level_value
AND fnod.node_id(+) = fpov.level_value
AND hou.organization_id(+) = fpov.level_value
and fpot.language='US'
and frtl.responsibility_id=frsp.responsibility_id
and frtl.language ='US'
--and fpot.user_profile_option_name like '%MO: Security Profile%'
and frtl.responsibility_name in ('AP XX_CUST Super User')
ORDER BY short_name;
============
SQL script to find the All Hash Plans and Explain plan for a respective SQL ID
Query:
set verify on lines 200 pages 9999
undefine sqlid
accept level char DEFAULT 'ADVANCED' PROMPT "Explain Plan level (BASIC, TYPICAL, ALL, ADVANCED) (Default : ADVANCED) : "
col cpu_exe format 9999.9999
col ela_exe format 9999.9999
col prds_exe format 9999.9999
col bg_exe format 99999999
col rows_exe format 999999
col snap_time format a14
col inst format 999
col sql_id new_value sqlid noprint
break on plan_hash_value skip 1
select
sql_id,
plan_hash_value,
s.instance_number inst,
s.snap_id,
to_char(s.begin_interval_time,'YYYYMMDD:HH24:MI') snap_time,
executions_delta delta_exe,
cpu_time_delta/1000000 delta_cpu_sec,
(case when executions_delta>0 then ((cpu_time_delta/1000000)/executions_delta) else 0 end) cpu_exe,
(case when executions_delta>0 then ((elapsed_time_delta/1000000)/executions_delta) else 0 end) ela_exe,
(case when executions_delta>0 then (buffer_gets_delta/executions_delta) else 0 end) bg_exe,
(case when executions_delta>0 then (disk_reads_delta/executions_delta) else 0 end) prd_exe,
(case when executions_delta>0 then (rows_processed_delta/executions_delta) else 0 end) rows_exe
from
dba_hist_sqlstat b,
dba_hist_snapshot s
where
b.sql_id = '&sqlid'
and b.snap_id = s.snap_id
and s.begin_interval_time >= sysdate-&days_back
and s.instance_number = b.instance_number
order by
2,3,4;
select * from table(dbms_xplan.display_awr('&sqlid'));
undefine sqlid
clear columns
clear breaks
===========
SQL Script to Find the Hash Plans for a SQL id and its execution details
Script
select sql_id, plan_hash_value as "Plan hash"
, sum(executions_calc) as "Times called"
, sum(end_of_fetch_count) as "Times completed"
, round(100 * sum(end_of_fetch_count) / sum(executions_calc),1) as "Success %"
, cast(numtodsinterval(sum(elapsed_time)/1E6,'SECOND') as interval day(1) to second(2)) as "Total time"
-- , round(sum(elapsed_time)/1e6) as "Total seconds"
, round(sum(elapsed_time)/1E6 / nvl(sum(executions_calc),1),1) as "Average seconds"
, cast(numtodsinterval(sum(elapsed_time)/1E6 / nvl(sum(executions_calc),1),'SECOND') as interval day(1) to second(1)) as "Average time"
-- , sum(buffer_gets) as "Buffer gets"
, round(sum(buffer_gets)/sum(executions_calc)) as "Buffer gets/exec"
, round(sum(buffer_gets)/nullif(sum(rows_processed),0)) as "Buffer gets/row"
, sum(rows_processed) as "Rows"
, round(sum(rows_processed) / sum(executions_calc)) as "Rows/exec"
, cast(numtodsinterval(sum(elapsed_time)/nullif(sum(rows_processed),0)/1E6,'SECOND') as interval day(1) to second(3)) as "Avg time/row"
, cast
( case
when sum(elapsed_time)/nullif(sum(rows_processed),0) < 2147483647 then -- 2**31 -1, limit for 32 bit integers and a
numtodsinterval(sum(elapsed_time)/nullif(sum(rows_processed),0),'SECOND')
else numtodsinterval(sum(elapsed_time/3600)/nullif(sum(rows_processed),0),'HOUR')
end -- Avoid "ORA-01873: the leading precision of the interval is too small" for large values
as interval day(5) to second(0)) as "Avg time/1M rows"
, sum(px_servers_executions) as "PX server executions"
from ( select s.sql_id
, trunc(s.last_active_time) as exec_date
, plan_hash_value
--, executions
, case executions when 0 then 1 else executions end as executions_calc -- to use in expressions without NULLIF
, px_servers_executions
, elapsed_time
, buffer_gets
, rows_processed
, end_of_fetch_count
from v$sqlstats s
union
select s.sql_id
, trunc(cast(h.begin_interval_time as date)) as exec_date
, plan_hash_value
--, executions_delta executions
, nullif(executions_delta,0) executions_calc
, px_servers_execs_delta as px_servers_executions
, elapsed_time_delta as elapsed_time
, buffer_gets_delta as buffer_gets
, rows_processed_delta as rows_processed
, end_of_fetch_count_delta as end_of_fetch_count
from dba_hist_sqlstat s
join dba_hist_snapshot h on h.snap_id = s.snap_id and h.dbid = s.dbid and h.instance_number = s.instance_number
)
where sql_id = '&sql_id'
group by sql_id,plan_hash_value
having sum(executions_calc) > 0
order by sql_id;
=============
SQL script to find SQL ID's having more than one hash plan
This will be for the last 7 days.
Script:
SELECT
sql_id, COUNT(DISTINCT plan_hash_value)
FROM
dba_hist_sqlstat stat,
dba_hist_snapshot ss
WHERE
stat.snap_id = ss.snap_id
AND ss.dbid = stat.dbid
AND ss.instance_number = stat.instance_number
AND ss.begin_interval_time >= sysdate - 7
AND ss.end_interval_time <= sysdate
AND stat.plan_hash_value <> 0
AND stat.executions_delta > 0
AND stat.parsing_schema_name NOT IN ( 'SYS', 'SYSTEM' )
GROUP BY
sql_id
having COUNT(DISTINCT plan_hash_value) >1
ORDER BY
1;
PL/SQL to Compare if they are the same or different
Script:
SET SERVEROUTPUT ON
DECLARE
v_count number := 0;
CURSOR SQLID IS
SELECT
sql_id,
COUNT(DISTINCT plan_hash_value) cnt
FROM
dba_hist_sqlstat stat,
dba_hist_snapshot ss
WHERE
stat.snap_id = ss.snap_id
AND ss.dbid = stat.dbid
AND ss.instance_number = stat.instance_number
AND ss.begin_interval_time >= sysdate - 7
AND ss.end_interval_time <= sysdate
AND stat.plan_hash_value <> 0
AND stat.executions_delta > 0
AND stat.parsing_schema_name NOT IN ( 'SYS', 'SYSTEM' )
GROUP BY
sql_id
ORDER BY
1;
BEGIN
FOR I IN SQLID
loop
DBMS_OUTPUT.PUT_LINE ('~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~');
IF I.cnt > 1 THEN
DBMS_OUTPUT.PUT_LINE ('Multiple plan for this sql :-'||I.sql_id||'. Checked for last 7 days. Please login to DB and do action item as needed ');
ELSE
DBMS_OUTPUT.PUT_LINE ('This sqlid '||I.sql_id||' has more than one plan, but the plan was not changed since last 7days');
END IF;
end loop;
END;
/
========================
SQL Script to find Queries Running More than 30 seconds
This script will help to find queries that are running more than 30 seconds under APPS schema
Script:
SELECT distinct s.username,Q.SQL_ID,round(s.last_call_et ,2) Time_in_seconds,round(s.last_call_et / 60,2) Time_in_mins,s.sid,s.serial#,s.inst_id
FROM gv$session s
JOIN gv$sqltext_with_newlines q ON s.sql_address = q.address
WHERE status = 'ACTIVE' AND username like '%APPS%' AND TYPE <> 'BACKGROUND' AND s.last_call_et > 30
ORDER BY s.inst_id;
==================
Performance - How To Generate Explain Plan in Oracle Database
Various methods of generating explain plan .
1.Explain plan for a sql query:
Query:
SELECT COUNT(*) FROM employee;
--- LOAD THE EXPLAIN PLAN TO PLAN_TABLE
SQL> explain plan for select count(*) from employee;
Explained.
--- DISPLAY THE EXPLAIN PLAN
SQL> select * from table(dbms_xplan.display);
2. Explain plan for a sql_id from cursor
set lines 2000
set pagesize 2000
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id'));
3. Explain plan of a sql_id from AWR:
SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('&sql_id'));
Above will display the explain plan for all the plan_hash_value in AWR. If you wish to see the plan for a particular plan_hash_value.
SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('&sql_id',&plan_hash_value));
4. Explain plan of sql baseline:
SELECT * FROM TABLE(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'SQL_PLAN_hjkadgkjaduad3232haass'));
If you wish the see the plan for a sql_handle,then
SELECT * FROM TABLE(DBMS_XPLAN.display_sql_plan_baseline(sql_handle =>'SYS_SQL_43jah7w2adpj29hda'));
5. Explain plan for sql id from sql tuning set:
-- Display all the explain plans of a sql_id from a sql set employee_SET, sql_id-gdjhd6asks7aad
SELECT *
FROM TABLE(dbms_xplan.display_sqlset('employee_SET', 'gdjhd6asks7aad'));
-- Display explain plan for particular plan_hash_value - 1475959015
SELECT *
FROM TABLE(dbms_xplan.display_sqlset('employee_SET','gdjhd6asks7aad', 1475959015));
Comments
Post a Comment