FAQ
Hi all,

How to find out which process or user session(s) are generating highest
number of archive logs in a RAC database?

My database is in 10.2.0.3 RAC

In 10.2.0.3 Grid control, I noticed there is a link "Top Segments", but it
does not have any info on segments having highest physical writes.

Thanks in advance...

--

Anurag Verma,
Database Administrator
ERCOT(Electric Reliability Council of Texas),
Texas 76574


--
http://www.freelists.org/webpage/oracle-l

Search Discussions

  • Jeremiah Wilton at Feb 16, 2012 at 3:07 pm

    On Feb 15, 2012, at 10:37 PM, Anurag Verma wrote:
    Hi all,

    How to find out which process or user session(s) are generating highest
    number of archive logs in a RAC database?

    My database is in 10.2.0.3 RAC

    In 10.2.0.3 Grid control, I noticed there is a link "Top Segments", but it
    does not have any info on segments having highest physical writes.

    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!
  • GG at Feb 16, 2012 at 6:28 pm
    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
  • Powell, Mark at Feb 16, 2012 at 6:29 pm
    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: oracle-l-bounce@freelists.org On Behalf Of Jeremiah Wilton
    Sent: Thursday, February 16, 2012 10:06 AM
    To: anuragdba@gmail.com
    Cc: ORACLE-L
    Subject: Re: To find out the Top process or Top User sessions generating highest number of archive logs
    On Feb 15, 2012, at 10:37 PM, Anurag Verma wrote:
    Hi all,

    How to find out which process or user session(s) are generating
    highest number of archive logs in a RAC database?

    My database is in 10.2.0.3 RAC

    In 10.2.0.3 Grid control, I noticed there is a link "Top Segments",
    but it does not have any info on segments having highest physical writes.

    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!
  • Jeremiah Wilton at Feb 16, 2012 at 7:13 pm

    On Feb 16, 2012, at 11:27 AM, Powell, Mark wrote:

    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.
    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,
  • Michael Dinh at Feb 17, 2012 at 5:17 pm
    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: oracle-l-bounce@freelists.org On Behalf Of Jeremiah Wilton
    Sent: Thursday, February 16, 2012 11:13 AM
    To: mark.powell2@hp.com
    Cc: ORACLE-L
    Subject: Re: To find out the Top process or Top User sessions generating highest number of archive logs
    On Feb 16, 2012, at 11:27 AM, Powell, Mark wrote:

    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.
    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,
  • Lei Zeng at Feb 17, 2012 at 9:32 pm
    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 <mdinh@XIFIN.Com>
    To: "'jwilton@bluegecko.net'" <jwilton@bluegecko.net>; "mark.powell2@hp.com" <mark.powell2@hp.com>
    Cc: ORACLE-L <oracle-l@freelists.org>
    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: oracle-l-bounce@freelists.org On Behalf Of Jeremiah Wilton
    Sent: Thursday, February 16, 2012 11:13 AM
    To: mark.powell2@hp.com
    Cc: ORACLE-L
    Subject: Re: To find out the Top process or Top User sessions generating highest number of archive logs
    On Feb 16, 2012, at 11:27 AM, Powell, Mark wrote:

    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.
    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,
  • Kurt-franke at Feb 17, 2012 at 3:24 am
    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-----
    ...
    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')
    ) ...
    Question is why we have 2 rows in x$kgllk for 1 session (is that because
    separate lock for package header and package body )?
    ...

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedFeb 16, '12 at 5:38a
activeFeb 17, '12 at 9:32p
posts8
users7
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase