Enable Trace for a concurrent program
step 1
select q.concurrent_queue_name || ' - ' || target_node qname
,a.request_id "Req Id"
,decode(a.parent_request_id,-1,NULL,a.parent_request_id) "Parent"
,a.concurrent_program_id "Prg Id"
,a.phase_code,a.status_code
,b.os_process_id "OS"
,vs.sid
,vp.spid
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 "Time"
,c.concurrent_program_name||' - '||
c2.user_concurrent_program_name "program"
from APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs_tl c2
,APPLSYS.fnd_concurrent_programs c
,v$session vs
,v$process vp
where a.controlling_manager = b.concurrent_process_id
and a.concurrent_program_id = c.concurrent_program_id
and a.program_application_id = c.application_id
and c2.concurrent_program_id = c.concurrent_program_id
and a.phase_code in ('I','P','R','T')
and b.queue_application_id = q.application_id
and b.concurrent_queue_id = q.concurrent_queue_id
and c2.language = 'US'
and vs.process (+) = b.os_process_id
and vs.paddr = vp.addr (+)
--order by 9,2,1
order by 9
SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = &Request_ID;
Enter value for request_id: 75696767
old 9: AND a.request_id = &Request_ID
new 9: AND a.request_id = 75696767
REQUEST_ID SID SERIAL# OSUSER PROCESS SPID
---------- ---------- ---------- ------------------------------ ------------------------ ------------------------
75696767 25 50125 applmgr 21945 186
SQL> !ps -ef|grep 186
oracle 1218 28905 37 03:43:25 pts/2 0:00 grep 186
oracle 186 1 0 03:39:17 ? 0:00 oracleOICP (LOCAL=NO)
SQL> oradebug setospid 186
Oracle pid: 72, Unix process pid: 186, image: oracle@eprdsc1
SQL> oradebug unlimit
Statement processed.
SQL> oradebug Event 10046 trace name context forever, level 12
Statement processed.
SQL> oradebug tracefile_name
/oracle/app/oracle/uat/db/tech_st/11.2.0.3/admin/instance_name/instance/diag/rdbms/uat/UAT/trace/UAT_ora_21152.trc
SQL>
SQL>
SQL> oradebug event 10046 trace name context off
Statement processed.
SQL>
tkprof DEV_ora_12762.trc DEV_ora_12762.txt explain=apps/**** sort='(prsela,exeela,fchela)' print=10
tkprof raw_trace_file.trc output_file sys=no explain=apps/ sort=’(prsela,exeela,fchela)’ print=10
tkprof <filename.trc> <output_filename> sys=no explain=apps/<password> sort='(prsela,exeela,fchela)' print=10
**********************************************************************
Users who have logged in from EBS front end
select p.spid "spid",
substr(s.osuser,1,10) "osuser",
s.AUDSID,
substr(s.username,1,10) "ora_user",
s.sql_hash_value,
s.sid "ses_id",
s.serial# "serial#",
substr(s.machine,1,12) "machine",
to_char(s.logon_time,'mm/dd/rrrr:hh24:mi:ss') "logon_time",
s.status "status",
s.program "program",
s.module, s.CLIENT_IDENTIFIER, s.ACTION
from v$process p, v$session s where
p.addr = s.paddr and s.username='APPS’
and s.CLIENT_IDENTIFIER='DOLIVA';
**********************************************************************
SELECT s.sid,
s.serial#,
p.spid,
s.process,
substr(to_char(s.logon_time,'mm-dd-yy hh24:mi:ss'),1,20) Logon_Time,
s.action,
s.module,
fu.user_name,
fip.ipaddress
FROM
V$SESSION s, V$PROCESS p,fnd_oam_forms_rti fip, fnd_logins fl, fnd_user fu
WHERE s.paddr = p.addr
AND s.process = fip.PID
AND fl.pid = p.pid
AND fl.user_id = fu.user_id
AND s.username IS NOT NULL
AND s.username = <'apps username'>
AND s.osuser = <'applmgr or os user who owns the application>'
and s.action like 'FRM%';
**********************************************************************
No of users connected to Oracle APPS
Check user and their responbility
SELECT a.session_id,
a.user_id,
b.user_name,
b.description,
c.RESPONSIBILITY_NAME,
d.APPLICATION_SHORT_NAME,
e.APPLICATION_NAME,
TO_CHAR (first_connect, 'MM/DD/YYYY HH:MI:SS') start_time,
TO_CHAR (last_connect,
'MM/DD/YYYY HH:MI:SS'
)"Date and time of last hit"
FROM apps.icx_sessions a,
apps.fnd_user b,
apps.fnd_responsibility_tl c,
apps.fnd_application d,
apps.fnd_application_tl e
WHERE a.user_id = b.user_id
AND last_connect > SYSDATE -1
AND a.counter < limit_connects
and a.responsibility_id = c.responsibility_id
and c.application_id = d.application_id
and c.application_id = e.application_id
order by a.last_update_date desc;
**********************************************************************
Distinct users connected to Oracle apps
select count(distinct user_id) "users" from icx_sessions where last_connect > sysdate - 1 and user_id != '-1';
**********************************************************************
Checking the details of indexes and sampling size and judge whether it is required or not
Take out the query which is taking most amount of time.
select table_name,index_name,column_name,column_position from dba_ind_columns where table_name
IN ('PER_ALL_ASSIGNMENTS_F','XKB_BALANCES','XKB_TAX_BALANCE_DETAILS') order by table_name,index_name,column_position asc
select owner,table_name,column_name,num_distinct,num_nulls,sample_Size from dba_tab_col_statistics where table_name IN ('PER_ALL_ASSIGNMENTS_F','XKB_BALANCES','XKB_TAX_BALANCE_DETAILS')
get the explain plan
**********************************************************************
Taken from Arup Nanda Blog
select sid
from v$session s, v$process p
where p.spid = 956
and s.paddr = p.addr;
3806
select sql_fulltext
from v$sql l, v$session s
where s.sid = 3806
and l.sql_id = s.sql_id;
select * from v$session where sid='2523';
--------------------------------------
Query for displaying sessions, session state, and wait details
col "Description" format a50
select sid,
decode(state, 'WAITING','Waiting',
'Working') state,
decode(state,
'WAITING',
'So far '||seconds_in_wait,
'Last waited '||
wait_time/100)||
' secs for '||event
"Description"
from v$session
where username = 'ARUP';
----------------------------------------
blocking session and instance by issuing the following SQL statement:
select
blocking_session B_SID,
blocking_instance B_Inst
from v$session
where sid = 3346;
B_SID B_INST
—————— ———————
2832 1
----------------------------------------------------
Getting row lock information
select row_wait_obj#,
row_wait_file#,
row_wait_block#,
row_wait_row#
from v$session
where sid = 3346;
ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
————————————— —————————————— ———————————————— ——————————————
241876 1024 2307623 0
-------------------------------------------------------
To get the object information:
select owner, object_type, object_name, data_object_id
from dba_objects
where object_id = 241876;
OWNER OBJECT_TYPE OBJECT_NAME DATA_OBJECT_ID
————— ———————————— ———————————— ——————————————
ARUP TABLE T1 241877
Finding the row information
REM Filename: rowinfo.sql
REM This shows the row from the table when the
REM components of ROWID are passed. Pass the
REM following in this exact order
REM 1. owner
REM 2. table name
REM 3. data_object_id
REM 4. relative file ID
REM 5. block ID
REM 6. row Number
REM
select *
from &1..&2
where rowid =
dbms_rowid.rowid_create (
rowid_type => 1,
object_number => &3,
relative_fno => &4,
block_number => &5,
row_number => &6
)
/
SQL> @rowinfo ARUP T1 241877 1024 2307623 0
COL1 C
————— —
1 x
----------------------------------
Session waits for a specific machine
col username format a5
col program format a10
col state format a10
col last_call_et head 'Called|secs ago' format 999999
col seconds_in_wait head 'Waiting|for secs' format 999999
col event format a50
select sid, username, program,
decode(state, 'WAITING', 'Waiting',
'Working') state,
last_call_et, seconds_in_wait, event
from v$session
where machine = 'appsvr1'
/
Called Waiting
SID USERNAME PROGRAM STATE secs ago for secs EVENT
————— ——————— ——————————— ——————— ————————— ———————— ——————————————————
2832 ARUP sqlplus.exe Waiting 152 151 SQL*Net message
from client
3089 ARUP sqlplus.exe Waiting 146 146 enq: TX - row lock
contention
3346 ARUP sqlplus.exe Working 18 49 SQL*Net message
from client
--------------------------------------------
Getting the SQL
select sql_id
from v$session
where sid = 3089;
SQL_ID
—————————————————
g0uubmuvk4uax
set long 99999
select sql_fulltext
from v$sql
where sql_id = 'g0uubmuvk4uax';
SQL_FULLTEXT
————————————————————————————————————————
update t1 set col2 = 'y' where col1 = 1
-------------------------------------------------------
Checking the data access and from which table it is coming
select SID, state, event, p1, p2
from v$session
where username = 'ARUP';
SID STATE EVENT P1 P2
———— ——————— ——————————————————————— —— ————
2201 WAITING db file sequential read 5 3011
The P1 column shows the file ID, and the P2 column shows the block ID. From that information in the result in Listing 7, you can get the segment name from the extent information in DBA_EXTENTS, as shown below:
select owner, segment_name
from dba_extents
where file_id = 5
and 3011 between block_id
and block_id + blocks;
OWNER SEGMENT_NAME
—————— —————————————
ARUP T1
select s.value
from v$sesstat s, v$statname n
where s.sid = 3806
and n.statistic# = s.statistic#
and n.name = 'CPU used by this session';
SELECT a.average_wait "SEQ READ", b.average_wait "SCAT READ"
FROM sys.v_$system_event a, sys.v_$system_event b
WHERE a.event = 'db file sequential read'
AND b.event = 'db file scattered read';
select
s1.h_date,
trunc(s1.v_avg,2) pyh_reads,
trunc(s2.v_avg,2) dir_reads,
trunc(s1.mb_sec,2) pyh_mb_s,
trunc(s2.mb_sec,2) dir_mb_s,
trunc((s2.v_avg/s1.v_avg)*100,2) R_PCT
from
--S1-B-----------
(
select
trunc(b_snap_date,'HH') h_date,
sum(snap_value) svalue,
sum(snap_value/snap_secs) v_avg,
sum(snap_value/snap_secs)*v_db_block_size/1024/1024 mb_sec
from
(select
s.INSTANCE_NUMBER,
cast (s.END_INTERVAL_TIME as date) e_snap_date,
cast (s.BEGIN_INTERVAL_TIME as date) b_snap_date,
(cast(s.END_INTERVAL_TIME as date) - cast(s.BEGIN_INTERVAL_TIME as date))*24*60*60 snap_secs,
t.VALUE,
(t.VALUE-LAG (t.VALUE) OVER (ORDER BY s.INSTANCE_NUMBER, s.BEGIN_INTERVAL_TIME)) snap_value
from
DBA_HIST_SNAPSHOT s,
DBA_HIST_SYSSTAT t
where 1=1
and s.SNAP_ID = t.SNAP_ID
and s.DBID = t.DBID
and s.INSTANCE_NUMBER = t.INSTANCE_NUMBER
and s.DBID = (select DBID from V$DATABASE)
and t.STAT_NAME = 'physical reads'
) pr,
(select VALUE v_db_block_size from v$parameter where name = 'db_block_size')
where snap_value > 0
group by trunc(b_snap_date,'HH'),v_db_block_size
) S1,
--S2-B-----------
(
select
trunc(b_snap_date,'HH') h_date,
sum(snap_value) svalue,
sum(snap_value/snap_secs) v_avg,
sum(snap_value/snap_secs)*v_db_block_size/1024/1024 mb_sec
from
(select
s.INSTANCE_NUMBER,
cast (s.END_INTERVAL_TIME as date) e_snap_date,
cast (s.BEGIN_INTERVAL_TIME as date) b_snap_date,
(cast(s.END_INTERVAL_TIME as date) - cast(s.BEGIN_INTERVAL_TIME as date))*24*60*60 snap_secs,
t.VALUE,
(t.VALUE-LAG (t.VALUE) OVER (ORDER BY s.INSTANCE_NUMBER, s.BEGIN_INTERVAL_TIME)) snap_value
from
DBA_HIST_SNAPSHOT s,
DBA_HIST_SYSSTAT t
where 1=1
and s.SNAP_ID = t.SNAP_ID
and s.DBID = t.DBID
and s.INSTANCE_NUMBER = t.INSTANCE_NUMBER
and s.DBID = (select DBID from V$DATABASE)
and t.STAT_NAME = 'physical reads direct'
) pr,
(select VALUE v_db_block_size from v$parameter where name = 'db_block_size')
where snap_value > 0
group by trunc(b_snap_date,'HH'),v_db_block_size
) S2
--S2-E-----------
where 1=1
and s1.h_date = s2.h_date (+)
order by
s1.h_date;
**********************************************************************
Checking the percentage compeletion for long ops
SELECT X.*, TO_CHAR(SYSDATE, 'HH24:MIS') TIMESTAMP
FROM (select sid, serial#, opname, sofar, totalwork,
round(sofar/totalwork*100,2) "% Complete" from v$session_longops) X
WHERE "% Complete" < 100 and totalwork > 0;
**************************************************************
Finding SQL with Performance changing over time
taken from https://carlos-sierra.net/2014/11/02/finding-sql-with-performance-changing-over-time/
-------------------------------------------------------------------------------------------- File name: sql_performance_changed.sql---- Purpose: Lists SQL Statements with Elapsed Time per Execution changing over time---- Author: Carlos Sierra---- Version: 2014/10/31---- Usage: Lists statements that have changed their elapsed time per execution over-- some history.-- Uses the ration between "elapsed time per execution" and the median of -- this metric for SQL statements within the sampled history, and using-- linear regression identifies those that have changed the most. In other-- words where the slope of the linear regression is larger. Positive slopes-- are considered "improving" while negative are "regressing".---- Example: @sql_performance_changed.sql---- Notes: Developed and tested on 11.2.0.3.---- Requires an Oracle Diagnostics Pack License since AWR data is accessed.---- To further investigate poorly performing SQL use sqltxplain.sql or sqlhc -- (or planx.sql or sqlmon.sql or sqlash.sql).-- -----------------------------------------------------------------------------------------SPO sql_performance_changed.txt;DEF days_of_history_accessed = '31';DEF captured_at_least_x_times = '10';DEF captured_at_least_x_days_apart = '5';DEF med_elap_microsecs_threshold = '1e4';DEF min_slope_threshold = '0.1';DEF max_num_rows = '20';SET lin 200 ver OFF;COL row_n FOR A2 HEA '#';COL med_secs_per_exec HEA 'Median Secs|Per Exec';COL std_secs_per_exec HEA 'Std Dev Secs|Per Exec';COL avg_secs_per_exec HEA 'Avg Secs|Per Exec';COL min_secs_per_exec HEA 'Min Secs|Per Exec';COL max_secs_per_exec HEA 'Max Secs|Per Exec';COL plans FOR 9999;COL sql_text_80 FOR A80;PRO SQL Statements with "Elapsed Time per Execution" changing over timeWITHper_time AS (SELECT h.dbid, h.sql_id, SYSDATE - CAST(s.end_interval_time AS DATE) days_ago, SUM(h.elapsed_time_total) / SUM(h.executions_total) time_per_exec FROM dba_hist_sqlstat h, dba_hist_snapshot s WHERE h.executions_total > 0 AND s.snap_id = h.snap_id AND s.dbid = h.dbid AND s.instance_number = h.instance_number AND CAST(s.end_interval_time AS DATE) > SYSDATE - &&days_of_history_accessed. GROUP BY h.dbid, h.sql_id, SYSDATE - CAST(s.end_interval_time AS DATE)),avg_time AS (SELECT dbid, sql_id, MEDIAN(time_per_exec) med_time_per_exec, STDDEV(time_per_exec) std_time_per_exec, AVG(time_per_exec) avg_time_per_exec, MIN(time_per_exec) min_time_per_exec, MAX(time_per_exec) max_time_per_exec FROM per_time GROUP BY dbid, sql_idHAVING COUNT(*) >= &&captured_at_least_x_times. AND MAX(days_ago) - MIN(days_ago) >= &&captured_at_least_x_days_apart. AND MEDIAN(time_per_exec) > &&med_elap_microsecs_threshold.),time_over_median AS (SELECT h.dbid, h.sql_id, h.days_ago, (h.time_per_exec / a.med_time_per_exec) time_per_exec_over_med, a.med_time_per_exec, a.std_time_per_exec, a.avg_time_per_exec, a.min_time_per_exec, a.max_time_per_exec FROM per_time h, avg_time a WHERE a.sql_id = h.sql_id),ranked AS (SELECT RANK () OVER (ORDER BY ABS(REGR_SLOPE(t.time_per_exec_over_med, t.days_ago)) DESC) rank_num, t.dbid, t.sql_id, CASE WHEN REGR_SLOPE(t.time_per_exec_over_med, t.days_ago) > 0 THEN 'IMPROVING' ELSE 'REGRESSING' END change, ROUND(REGR_SLOPE(t.time_per_exec_over_med, t.days_ago), 3) slope, ROUND(AVG(t.med_time_per_exec)/1e6, 3) med_secs_per_exec, ROUND(AVG(t.std_time_per_exec)/1e6, 3) std_secs_per_exec, ROUND(AVG(t.avg_time_per_exec)/1e6, 3) avg_secs_per_exec, ROUND(MIN(t.min_time_per_exec)/1e6, 3) min_secs_per_exec, ROUND(MAX(t.max_time_per_exec)/1e6, 3) max_secs_per_exec FROM time_over_median t GROUP BY t.dbid, t.sql_idHAVING ABS(REGR_SLOPE(t.time_per_exec_over_med, t.days_ago)) > &&min_slope_threshold.)SELECT LPAD(ROWNUM, 2) row_n, r.sql_id, r.change, TO_CHAR(r.slope, '990.000MI') slope, TO_CHAR(r.med_secs_per_exec, '999,990.000') med_secs_per_exec, TO_CHAR(r.std_secs_per_exec, '999,990.000') std_secs_per_exec, TO_CHAR(r.avg_secs_per_exec, '999,990.000') avg_secs_per_exec, TO_CHAR(r.min_secs_per_exec, '999,990.000') min_secs_per_exec, TO_CHAR(r.max_secs_per_exec, '999,990.000') max_secs_per_exec, (SELECT COUNT(DISTINCT p.plan_hash_value) FROM dba_hist_sql_plan p WHERE p.dbid = r.dbid AND p.sql_id = r.sql_id) plans, REPLACE((SELECT DBMS_LOB.SUBSTR(s.sql_text, 80) FROM dba_hist_sqltext s WHERE s.dbid = r.dbid AND s.sql_id = r.sql_id), CHR(10)) sql_text_80 FROM ranked r WHERE r.rank_num <= &&max_num_rows. ORDER BY r.rank_num/SPO OFF; |
Once you get the output of this script above, you can use the one below to actually list the time series for one of the SQL statements of interest:
-------------------------------------------------------------------------------------------- File name: one_sql_time_series.sql---- Purpose: Performance History for one SQL---- Author: Carlos Sierra---- Version: 2014/10/31---- Usage: Script sql_performance_changed.sql lists SQL Statements with performance-- improvement or regressed over some History.-- This script one_sql_time_series.sql lists the Performance Time Series for-- one SQL.---- Parameters: SQL_ID---- Example: @one_sql_time_series.sql---- Notes: Developed and tested on 11.2.0.3.---- Requires an Oracle Diagnostics Pack License since AWR data is accessed.---- To further investigate poorly performing SQL use sqltxplain.sql or sqlhc -- (or planx.sql or sqlmon.sql or sqlash.sql).-- -----------------------------------------------------------------------------------------SPO one_sql_time_series.txt;SET lin 200 ver OFF;COL instance_number FOR 9999 HEA 'Inst';COL end_time HEA 'End Time';COL plan_hash_value HEA 'Plan|Hash Value';COL executions_total FOR 999,999 HEA 'Execs|Total';COL rows_per_exec HEA 'Rows Per Exec';COL et_secs_per_exec HEA 'Elap Secs|Per Exec';COL cpu_secs_per_exec HEA 'CPU Secs|Per Exec';COL io_secs_per_exec HEA 'IO Secs|Per Exec';COL cl_secs_per_exec HEA 'Clus Secs|Per Exec';COL ap_secs_per_exec HEA 'App Secs|Per Exec';COL cc_secs_per_exec HEA 'Conc Secs|Per Exec';COL pl_secs_per_exec HEA 'PLSQL Secs|Per Exec';COL ja_secs_per_exec HEA 'Java Secs|Per Exec';SELECT h.instance_number, TO_CHAR(CAST(s.end_interval_time AS DATE), 'YYYY-MM-DD HH24:MI') end_time, h.plan_hash_value, h.executions_total, TO_CHAR(ROUND(h.rows_processed_total / h.executions_total), '999,999,999,999') rows_per_exec, TO_CHAR(ROUND(h.elapsed_time_total / h.executions_total / 1e6, 3), '999,990.000') et_secs_per_exec, TO_CHAR(ROUND(h.cpu_time_total / h.executions_total / 1e6, 3), '999,990.000') cpu_secs_per_exec, TO_CHAR(ROUND(h.iowait_total / h.executions_total / 1e6, 3), '999,990.000') io_secs_per_exec, TO_CHAR(ROUND(h.clwait_total / h.executions_total / 1e6, 3), '999,990.000') cl_secs_per_exec, TO_CHAR(ROUND(h.apwait_total / h.executions_total / 1e6, 3), '999,990.000') ap_secs_per_exec, TO_CHAR(ROUND(h.ccwait_total / h.executions_total / 1e6, 3), '999,990.000') cc_secs_per_exec, TO_CHAR(ROUND(h.plsexec_time_total / h.executions_total / 1e6, 3), '999,990.000') pl_secs_per_exec, TO_CHAR(ROUND(h.javexec_time_total / h.executions_total / 1e6, 3), '999,990.000') ja_secs_per_exec FROM dba_hist_sqlstat h, dba_hist_snapshot s WHERE h.sql_id = '&sql_id.' AND h.executions_total > 0 AND s.snap_id = h.snap_id AND s.dbid = h.dbid AND s.instance_number = h.instance_number ORDER BY h.sql_id, h.instance_number, s.end_interval_time, h.plan_hash_value/SPO OFF;
No comments:
Post a Comment