[Oracle-l] To find out the Top process or Top User sessions generating highest number of archive logs

The accounting for redo size by session is located in v$sesstat. You can do a simple query for a RAC cluster to see the largest redo generating session:

select s.inst_id, s.sid, serial#, program, module, username, value redo_size
from gv$session s, gv$sesstat ss, v$statname sn
where s.sid = ss.sid
and ss.statistic# = sn.statistic#
and sn.name = 'redo size'
and s.inst_id = ss.inst_id
order by redo_size

This query might help you find the culprit. However, this only gives you the currently logged-in sessions and their total redo size since they logged in. So if you are seeing lots of redo generation right now, you need to sample this query several times over a period when the redo is being generated, and delta the values, using instead, sid and serial# as the keys.

You could write a simple piece of PL/SQL to do this, or you could use Tanel Poder's wonderful Snapper script, available right here:

http://files.e2sn.com/scripts/snapper.sql

Good luck!

Hi,
while playing with Tanel query for reporting sessions which suffer
from global variables state change in 9.2.0.8 DB .
I've found something strange (for me) :

select t.* , (select sid from v$session where saddr = t.KGLLKUSE) sid
from x$kgllk t
where
kglnahsh in (select /*+ no_unnest */ kglnahsh
from x$kglob
where
upper(kglnaown) like upper('TEST')
and upper(kglnaobj) like upper('DEMO_PKG')
)


Lets focus on KGLLKFLG column.
So looks like after getting breakable parse look KGLLKFLG = 0
then after package is recompiled there is 0 and 3 reported for 2 rows
related to one session which executed package procedure .

After recompilation the KGLLKFLG is 2 and 3 and looks like when we ask
for value 3 we get what we want
so sessions which after running recompiled package reports ORA-04068 .

Question is why we have 2 rows in x$kgllk for 1 session (is that because
separate lock for package header and package body )?
Next one, Im not sure what is the meaning of columns:
KGLHDNSP
KGLLKSPN

Any ideas ?
Thanks
GregG

I note that the query below always seems to return smon as the largest redo generator on the couple of systems where I checked so if you are interested in only user sessions you might want to add "and s.username is not null" as a condition to filter out the Oracle rdbms background sessions.


-----Original Message-----
From: [email protected] On Behalf Of Jeremiah Wilton
Sent: Thursday, February 16, 2012 10:06 AM
To: [email protected]
Cc: ORACLE-L
Subject: Re: To find out the Top process or Top User sessions generating highest number of archive logs

The accounting for redo size by session is located in v$sesstat. You can do a simple query for a RAC cluster to see the largest redo generating session:

select s.inst_id, s.sid, serial#, program, module, username, value redo_size from gv$session s, gv$sesstat ss, v$statname sn where s.sid = ss.sid and ss.statistic# = sn.statistic# and sn.name = 'redo size'
and s.inst_id = ss.inst_id
order by redo_size

This query might help you find the culprit. However, this only gives you the currently logged-in sessions and their total redo size since they logged in. So if you are seeing lots of redo generation right now, you need to sample this query several times over a period when the redo is being generated, and delta the values, using instead, sid and serial# as the keys.

You could write a simple piece of PL/SQL to do this, or you could use Tanel Poder's wonderful Snapper script, available right here:

http://files.e2sn.com/scripts/snapper.sql

Good luck!

I don't see a good reason to exclude background processes, especially if one choses to sample and delta the redo size over a number of samples. What if one of them is somehow generating the large amount of redo that the I am trying to track down?

If you do choose to exclude background processes, there's a column for that in gv$session. Just select where type != 'BACKGROUND'.

Another trick to avoid doing deltas (why are we avoiding that when we have Tanel's snapper handy?) might be to divide the redo size by the length of time the session has been logged in. That way you get sort of redo size per unit time figure, instead of the grand total for the lifetime of the session (which is what is making SMON look so large).

Regards,

A little late?

But wanted to share.

http://www.freelists.org/post/oracle-l/archived-log-switch-detecting-whos-causing-excessive-redo-generation

Michael Dinh
Disparity Breaks Automation (DBA)

Great minds discuss ideas; average minds discuss events; small minds discuss people - Eleanor Roosevelt
Confidence comes not from always being right but from not fearing to be wrong - Peter T Mcintyre

-----Original Message-----
From: [email protected] On Behalf Of Jeremiah Wilton
Sent: Thursday, February 16, 2012 11:13 AM
To: [email protected]
Cc: ORACLE-L
Subject: Re: To find out the Top process or Top User sessions generating highest number of archive logs
I don't see a good reason to exclude background processes, especially if one choses to sample and delta the redo size over a number of samples. What if one of them is somehow generating the large amount of redo that the I am trying to track down?

If you do choose to exclude background processes, there's a column for that in gv$session. Just select where type != 'BACKGROUND'.

Another trick to avoid doing deltas (why are we avoiding that when we have Tanel's snapper handy?) might be to divide the redo size by the length of time the session has been logged in. That way you get sort of redo size per unit time figure, instead of the grand total for the lifetime of the session (which is what is making SMON look so large).

Regards,

Tanel's snapper script is very handy for trouble shooting if the issue is currently going on.
I would like to add few points if in case you need to trouble shooting this type of issue which happened in the past.
Â
1) AWR can nail down 'redo size per second' to service level ( DBA_HIST_SERVICE_STAT)
That roughly tells a direction where the user/process comes from.
Â
2) check for objects which went through the highest change rate.
This can be done from AWR ‘Top Segment by block change’ (pay attention to the '% of capture' for accuracy), or from logminer
select TABLE_NAME, OPERATION, count(1)
from V$LOGMNR_CONTENTS
group by TABLE_NAME, OPERATION
order by  TABLE_NAME, OPERATION;
Â
3)  From the object, we can check DBA_HIST_SQL_PLAN and DBA_HIST_SQLSTAT to see if we can dig out the sql statement.
Or, sometime it is just some background knowledge of what application or process could land on those objects.
Â
WITH x AS (
    SELECT DISTINCT DBID, SQL_ID, PLAN_HASH_VALUE
    FROM DBA_HIST_SQL_PLAN
    WHERE DBID=? AND OBJECT_NAME=? AND OBJECT_OWNER=?
 ),
 y AS (
    SELECT DBID, INSTANCE_NUMBER,
    LAG(SNAP_ID, 1) OVER (PARTITION BY DBID,INSTANCE_NUMBER ORDER BY SNAP_ID) begin_snap_id,
    SNAP_ID end_snap_id, END_INTERVAL_TIME end_snap_time
    FROM DBA_HIST_SNAPSHOT
    WHERE DBID=? AND INSTANCE_NUMBER=? AND END_INTERVAL_TIME BETWEEN ? and ?
 )
 SELECT /*+ NO_MERGE(x) ORDERED */
 y.begin_snap_id||'-'||y.end_snap_id snap_id, y.end_snap_time, z.INSTANCE_NUMBER inst#,
 z.SQL_ID, z.PLAN_HASH_VALUE, ROUND(z.ELAPSED_TIME_DELTA/1000000) elapsed_seconds,
 ROUND(z.CPU_TIME_DELTA/1000000) cpu_seconds,
    z.ROWS_PROCESSED_DELTA rows_processed, z.BUFFER_GETS_DELTA buffer_gets, z.DISK_READS_DELTA  disk_reads, z.EXECUTIONS_DELTA  executions, z.PARSE_CALLS_DELTA parses
 FROM DBA_HIST_SQLSTAT z JOIN y ON (z.DBID=y.dbid AND z.INSTANCE_NUMBER=y.instance_number AND z.SNAP_ID=y.end_snap_id)
 JOIN x ON (z.DBID=x.dbid AND z.SQL_ID =x.sql_id AND z.PLAN_HASH_VALUE=x.plan_hash_value)
 WHERE y.begin_snap_id IS NOT NULL
 ORDER BY y.begin_snap_id, y.end_snap_id, z.SQL_ID, z.PLAN_HASH_VALUE
Â
Lei
DBspeed  http://www.dbspeed.com/product.html



________________________________
From: Michael Dinh <[email protected]>
To: "'[email protected]'" <[email protected]>; "[email protected]" <[email protected]>
Cc: ORACLE-L <[email protected]>
Sent: Friday, February 17, 2012 9:15 AM
Subject: RE: To find out the Top process or Top User sessions generating highest number of archive logs

A little late?

But wanted to share.

http://www.freelists.org/post/oracle-l/archived-log-switch-detecting-whos-causing-excessive-redo-generation

Michael Dinh
Disparity Breaks Automation (DBA)

Great minds discuss ideas; average minds discuss events; small minds discuss people - Eleanor RooseveltÂ
Confidence comes not from always being right but from not fearing to be wrong - Peter T Mcintyre
Â
-----Original Message-----
From: [email protected] On Behalf Of Jeremiah Wilton
Sent: Thursday, February 16, 2012 11:13 AM
To: [email protected]
Cc: ORACLE-L
Subject: Re: To find out the Top process or Top User sessions generating highest number of archive logs
I don't see a good reason to exclude background processes, especially if one choses to sample and delta the redo size over a number of samples. What if one of them is somehow generating the large amount of redo that the I am trying to track down?

If you do choose to exclude background processes, there's a column for that in gv$session.  Just select where type != 'BACKGROUND'.

Another trick to avoid doing deltas (why are we avoiding that when we have Tanel's snapper handy?) might be to divide the redo size by the length of time the session has been logged in.  That way you get sort of redo size per unit time figure, instead of the grand total for the lifetime of the session (which is what is making SMON look so large).

Regards,

Greg,

x$kglob.kglobtyp holds the object type number .
to translate it to a type name just look at the definition of dba_objects .

this will give you the desired information .

best regards

kf


-----Ursprüngliche Nachricht-----
...
...