performance Query

Author – A.Kishore http://www.appsdba.info

What are the queries that are running?


select sesion.sid,

sesion.username,

optimizer_mode,

hash_value,

address,

cpu_time,

elapsed_time,

sql_text

from v$sqlarea sqlarea, v$session sesion

where sesion.sql_hash_value = sqlarea.hash_value

and sesion.sql_address = sqlarea.address

and sesion.username is not null
/ Author – A.Kishore http://www.appsdba.info


Get the rows fetched, if there is difference it means processing is happening

select b.name, a.value vlu

from v$sesstat a, v$statname b

where a.statistic# = b.statistic#

and sid =&sid

and a.value != 0

and b.name like '%row%'
/ Author – A.Kishore http://www.appsdba.info


Get the sql_hash_value

select sql_hash_value from v$session where sid='&sid';

SQL> select sql_hash_value from v$session where sid='&sid';

Enter value for sid: 1075

old 1: select sql_hash_value from v$session where sid='&sid'

new 1: select sql_hash_value from v$session where sid='1075'

SQL_HASH_VALUE

--------------

928832585

Get the sql_Text

SQL> select sql_text v$sql from v$sql where hash_value =&Enter_Hash_Value;

Enter value for enter_hash_value: 928832585

Get the explain_plan

set lines 190

col XMS_PLAN_STEP format a40

set pages 100

select

case when access_predicates is not null then 'A' else ' ' end ||

case when filter_predicates is not null then 'F' else ' ' end xms_pred,

id xms_id,

lpad(' ',depth*1,' ')||operation || ' ' || options xms_plan_step,

object_name xms_object_name,

cost xms_opt_cost,

cardinality xms_opt_card,

bytes xms_opt_bytes,

optimizer xms_optimizer

from

v$sql_plan

where

hash_value in (&SQL_HASH_VALUE)
and to_char(child_number) like '%'; Author – A.Kishore http://www.appsdba.info


Based the cost u can decide what to be done.

One of the solutions is to analyse the statistics
exec fnd_stats.gather_schema_statistics('ALL');
 Time Remaining to complete the current task


set lines 150

col username format a20

col opname format a30

col target format a40

select sid,opname,target,

to_char(start_time,'DD-MON-YY HH24:MI') START_TIME,

time_remaining/60 "Time Remaining in Mins",username

from v$session_longops where time_remaining>1 order by time_remaining
/ Author – A.Kishore http://www.appsdba.info


-- what sessions are active this SQL will help

select sid,

to_char(logon_time,'MMDDYYYY:HH24:MI') logon_time,

username,

type,

status,

process,

sql_address,

sql_hash_value

from v$session

where username is not null

/
 Work remaining


select V1.sid, V1.serial#, V2.USERNAME, V2.OSUSER, substr(V1.opname,1,10), to_char(V1.start_time, 'HH24:MI:SS') AS Started, (V1.SOFAR/V1.TOTALWORK)*100 AS Pct_completed

FROM V$SESSION_LONGOPS V1, V$SESSION V2

WHERE V1.SID= V2.SID AND V1.SERIAL#=V2.SERIAL#

AND (SOFAR/TOTALWORK)*100 < 100

AND TOTALWORK > 0

/
 Memory usage


SELECT username, value/(1024*1024) "Current session memory MB", sess.sid,sess.status
FROM v$session sess, v$sesstat stat, v$statname name Author – A.Kishore http://www.appsdba.info


WHERE sess.sid = stat.sid

AND stat.statistic# = name.statistic#

AND name.name like '%memory%'

and username = 'APPS'

-- and sess.status='ACTIVE'

order by 2,4 asc

/