Grokbase › Groups › Oracle › oracle-l › September 2005
FAQ
Got any SQL for me?

Want to monitor how the log buffer entries are reacting / growing.
What is best to see how the buffer is performing or log buffer activity?

SELECT name, value FROM V$SYSSTAT WHERE NAME like 'redo%' ???
Specifically "redo buffer allocation retries" ???

PS I know I have activity as we are archiving like crazy during heavy load.

Thanks in advance.

Chris Marquez
Oracle DBA

Search Discussions

  • Hallas, John, Tech Dev at Oct 3, 2005 at 6:34 am ⇧
    Howabout, the following but I am sure you have looked at that already



    John



    PROMPT ================================================================

    PROMPT Redo Log Space Requests

    PROMPT Should be near 0

    PROMPT If greater, increase 'log_buffer' parameter

    select name, value

    from v$sysstat

    where name = 'redo log space requests'

    /







    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Marquez, Chris
    Sent: 30 September 2005 16:40
    To: oracle-l_at_freelists.org
    Subject: SQL for Monitoring; log_buffer entries/growth?



    Got any SQL for me?

    Want to monitor how the log buffer entries are reacting / growing.
    What is best to see how the buffer is performing or log buffer activity?

    SELECT name, value FROM V$SYSSTAT WHERE NAME like 'redo%' ???
    Specifically "redo buffer allocation retries" ???

    PS I know I have activity as we are archiving like crazy during heavy
    load.

    Thanks in advance.

    Chris Marquez
    Oracle DBA
  • Marquez, Chris at Oct 3, 2005 at 9:14 am ⇧
    Yeah I have that one and a few others like it.

    Seems now I specifically want to know/find SQL for how much of the
    log_buffer is used/free/growing?

    I want to anticipate contention (full log_buffer) rather than seeing the
    resulting (past) waits/stats.

    ???

    Thanks,

    Chris Marquez

    Oracle DBA

    From: Hallas, John, Tech Dev
    Sent: Monday, October 03, 2005 7:32 AM
    To: Marquez, Chris; oracle-l_at_freelists.org
    Subject: RE: SQL for Monitoring; log_buffer entries/growth?

    Howabout, the following but I am sure you have looked at that already



    John



    PROMPT ================================================================

    PROMPT Redo Log Space Requests

    PROMPT Should be near 0

    PROMPT If greater, increase 'log_buffer' parameter

    select name, value

    from v$sysstat

    where name = 'redo log space requests'

    /







    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Marquez, Chris
    Sent: 30 September 2005 16:40
    To: oracle-l_at_freelists.org
    Subject: SQL for Monitoring; log_buffer entries/growth?



    Got any SQL for me?

    Want to monitor how the log buffer entries are reacting / growing.
    What is best to see how the buffer is performing or log buffer activity?

    SELECT name, value FROM V$SYSSTAT WHERE NAME like 'redo%' ???
    Specifically "redo buffer allocation retries" ???

    PS I know I have activity as we are archiving like crazy during heavy
    load.

    Thanks in advance.

    Chris Marquez
    Oracle DBA
  • Rjamya at Oct 3, 2005 at 9:21 am ⇧
    x$KGopal <http://www.jlcomp.demon.co.uk/faq/redologuse.html>once responded
    with following info that could be useful to you.

    "X$KCCCP will have the redo block number (CPODR_BNO) in the log file. The
    log block is X$KCCLE.LEBSZ So by joining X$KCCLE and X$KCCCP we can find the
    current block in the redo log file.

    Log file size/X$KCCLE.LEBZ will give you the number of log blocks for that
    log file. Let us say it is K. X$KCCCP.CPODR_BNO/K*100 will give how much %
    of log file is full.."

    (credit to X$KG.)

    check the highlighted link as well.

    Raj
    On 10/3/05, Marquez, Chris wrote:

    Yeah I have that one and a few others like it.

    Seems now I specifically want to know/find SQL for how much of the
    log_buffer is used/free/growing?

    I want to anticipate contention (full log_buffer) rather than seeing the
    resulting (past) waits/stats.

    ???

    Thanks,

    Chris Marquez

    Oracle DBA

    ------------------------------
    *From:* Hallas, John, Tech Dev
    *Sent:* Monday, October 03, 2005 7:32 AM
    *To:* Marquez, Chris; oracle-l_at_freelists.org
    *Subject:* RE: SQL for Monitoring; log_buffer entries/growth?

    Howabout, the following but I am sure you have looked at that already

    John

    PROMPT ================================================================
    PROMPT Redo Log Space Requests

    PROMPT Should be near 0

    PROMPT If greater, increase 'log_buffer' parameter

    select name, value

    from v$sysstat

    where name = 'redo log space requests'

    /

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

    *From:* oracle-l-bounce_at_freelists.org [mailto:
    oracle-l-bounce@freelists.org] *On Behalf Of *Marquez, Chris
    *Sent:* 30 September 2005 16:40
    *To:* oracle-l_at_freelists.org
    *Subject:* SQL for Monitoring; log_buffer entries/growth?

    Got any SQL for me?

    Want to monitor how the log buffer entries are reacting / growing.
    What is best to see how the buffer is performing or log buffer activity?

    SELECT name, value FROM V$SYSSTAT WHERE NAME like 'redo%' ???
    Specifically "redo buffer allocation retries" ???

    PS I know I have activity as we are archiving like crazy during heavy
    load.

    Thanks in advance.

    Chris Marquez
    Oracle DBA
    --
    ------------------------------
    select standard_disclaimer from company_requirements where category =
    'MANDATORY';

    --
    http://www.freelists.org/webpage/oracle-l
  • Marquez, Chris at Oct 3, 2005 at 2:17 pm ⇧
    Raj,

    Thanks...I will have a look.

    Chris Marquez
    Oracle DBA

    -----Original Message-----
    From: rjamya
    Sent: Mon 10/3/2005 10:19 AM
    To: Marquez, Chris
    Cc: Hallas, John, Tech Dev; oracle-l_at_freelists.org
    Subject: Re: SQL for Monitoring; log_buffer entries/growth?


    x$KGopal <http://www.jlcomp.demon.co.uk/faq/redologuse.html>once responded
    with following info that could be useful to you.

    "X$KCCCP will have the redo block number (CPODR_BNO) in the log file. The
    log block is X$KCCLE.LEBSZ So by joining X$KCCLE and X$KCCCP we can find the
    current block in the redo log file.

    Log file size/X$KCCLE.LEBZ will give you the number of log blocks for that
    log file. Let us say it is K. X$KCCCP.CPODR_BNO/K*100 will give how much %
    of log file is full.."

    (credit to X$KG.)

    check the highlighted link as well.

    Raj
    On 10/3/05, Marquez, Chris wrote:

    Yeah I have that one and a few others like it.

    Seems now I specifically want to know/find SQL for how much of the
    log_buffer is used/free/growing?

    I want to anticipate contention (full log_buffer) rather than seeing the
    resulting (past) waits/stats.

    ???

    Thanks,

    Chris Marquez

    Oracle DBA

    ------------------------------
    *From:* Hallas, John, Tech Dev
    *Sent:* Monday, October 03, 2005 7:32 AM
    *To:* Marquez, Chris; oracle-l_at_freelists.org
    *Subject:* RE: SQL for Monitoring; log_buffer entries/growth?

    Howabout, the following but I am sure you have looked at that already

    John

    PROMPT ================================================================
    PROMPT Redo Log Space Requests

    PROMPT Should be near 0

    PROMPT If greater, increase 'log_buffer' parameter

    select name, value

    from v$sysstat

    where name = 'redo log space requests'

    /

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

    *From:* oracle-l-bounce_at_freelists.org [mailto:
    oracle-l-bounce@freelists.org] *On Behalf Of *Marquez, Chris
    *Sent:* 30 September 2005 16:40
    *To:* oracle-l_at_freelists.org
    *Subject:* SQL for Monitoring; log_buffer entries/growth?

    Got any SQL for me?

    Want to monitor how the log buffer entries are reacting / growing.
    What is best to see how the buffer is performing or log buffer activity?

    SELECT name, value FROM V$SYSSTAT WHERE NAME like 'redo%' ???
    Specifically "redo buffer allocation retries" ???

    PS I know I have activity as we are archiving like crazy during heavy
    load.

    Thanks in advance.

    Chris Marquez
    Oracle DBA
    --
    ------------------------------
    select standard_disclaimer from company_requirements where category =
    'MANDATORY';

    --
    http://www.freelists.org/webpage/oracle-l
  • Barbara Baker at Oct 4, 2005 at 9:01 am ⇧
    Chris:
    Tim Gorman had a very nice script at his site
    (www.evdbt.com<http://www.evdbt.com>)
    I believe it would track log growth for several days.
    It's probably still there.
    You might have a look.
    Barb
    On 9/30/05, Marquez, Chris wrote:

    Got any SQL for me?

    Want to monitor how the log buffer entries are reacting / growing.
    What is best to see how the buffer is performing or log buffer activity?

    SELECT name, value FROM V$SYSSTAT WHERE NAME like 'redo%' ???
    Specifically "redo buffer allocation retries" ???

    PS I know I have activity as we are archiving like crazy during heavy
    load.

    Thanks in advance.

    Chris Marquez
    Oracle DBA

    --
    http://www.freelists.org/webpage/oracle-l
  • Marquez, Chris at Oct 6, 2005 at 11:33 am ⇧
    Tim Gorman had a very nice script
    I can not seem to find it?
    it would track log growth for several days.
    log_buffer entries/growth?

    Thanks,

    Chris Marquez
    Oracld DBA

    -----Original Message-----
    From: Barbara Baker
    Sent: Tue 10/4/2005 9:58 AM
    To: Marquez, Chris
    Cc: oracle-l_at_freelists.org
    Subject: Re: SQL for Monitoring; log_buffer entries/growth?


    Chris:
    Tim Gorman had a very nice script at his site
    (www.evdbt.com<http://www.evdbt.com>)
    I believe it would track log growth for several days.
    It's probably still there.
    You might have a look.
    Barb
    On 9/30/05, Marquez, Chris wrote:

    Got any SQL for me?

    Want to monitor how the log buffer entries are reacting / growing.
    What is best to see how the buffer is performing or log buffer activity?

    SELECT name, value FROM V$SYSSTAT WHERE NAME like 'redo%' ???
    Specifically "redo buffer allocation retries" ???

    PS I know I have activity as we are archiving like crazy during heavy
    load.

    Thanks in advance.

    Chris Marquez
    Oracle DBA

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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedSep 30, '05 at 10:43a
activeOct 6, '05 at 11:33a
posts7
users4
websiteoracle.com

4 users in discussion

Marquez, Chris: 4 posts Hallas, John, Tech Dev: 1 post Barbara Baker: 1 post Rjamya: 1 post

Content

People

Support

Translate

site design / logo © 2022 Grokbase