Oracle DBA: Do we need REDO statistics in V$SQL ?

Jul 22, 2011 / By Yury Velikanov

Tags: , ,

In this post:

  • Introduction
  • Question and background
  • My opinion

Introduction

Tom Kyte, Cary Millsap, Steven Feuerstein hold a Database Guru Panel at #Kscope 11′s conference organized by ODTUG. I had a great opportunity to not just watch the Live Stream (you can see the recorded version  HERE) but also ASK questions ;). Big kudos to organizers for the fantastic opportunity to ask questions via Twitter and Face Book live. I wasn’t sure if it was going to work . Surprisingly enough several of my questions had been asked and answered live. One of the questions was related to UNDO/REDO statistics. I think that it was a bit of challenge  to define the question precisely having just 140 Twitter characters and therefore I decided to share my question here, give a bit of background and ask you “What is your opinion?”

Question and background

The following is the question I asked in the twitter (16 min 14sec in the recording ).

Tweet: #kscope Q to Guru Panel: What is the best way to find TOP SQLs generating the most UNDO/REDO in 11GR2?

Tom Kyte response: My first response to that would be “Why?”

Well … This questions is coming from my day to day Oracle DBA duties. On several occasions I faced situations when an Oracle database (Instance) all over the sudden started to generate significantly more REDO data then usually. This quickly may “eat” all space allocated to archive log destinations and generate many other problems quite quickly. In such cases an Oracle DBA uses any diagnostics options available to find the root cause of the problem and resolve it in as short time as possible. My question is what Oracle DB kernel instrumentation do we have in place to troubleshoot such cases?

NOTE: It might be that word “SQLs” in my original question text introduced a bit of confusion. I really should have said DML operations instead of SQL. However I think many of us use word SQL for DML operations inclusively (including Oracle in V$SQL views ;).

My opinion

In my opinion we are missing a bit of instrumentation here. V$SQL, V$SQLAREA and other Oracle view are focused on Reads related statistics. This is understandable as most of operations a database is doing are data retrieval and Oracle did a great job here. However for the use case I described it would be very useful to have a set of additional columns in these views that would provide us with data changes related statistics.

What other options/instrumentation we have for finding the place in an application that suddenly started to generate a lot of data changes? Well there are several. However none of those provides us with a way to point the SQL (DML) that is a root cause of the problem. The options that I can think about are as following:

  1. LogMiner – there are 2 problems with this option. 1 – it takes some time to setup and run it (under time pressure we may not have such luxury) 2- surprisingly enough LogMiner views don’t provide REDO statistics :) We are left with an option to guess what statement generates most of changes based on execution count (which isn’t always a good indicator)
  2. V$SEGMENT_STATISTICS – provides exactly what we need with a small exception. The information is segment/table based. As soon as we determine the table we on our own to find the related SQL. In most of the cases it might not be that difficult to find SQLs that have been executed during statistics gathering time-frame and guess which one did cause the problem.  However if DML operation didn’t use a table name in it’s text (e.g. based on a view) we may not be that lucky and it isn’t that simple than to find any segment related DML operations.
  3. V$SESSTAT – great view, but provides statistics per database session. It provides valuable information for our investigation, however it doesn’t give use precise information what SQL (module) is causing the issue. We are on our own again to guess where the problem is.
  4. Extended SQL Trace (10046) – :) guess what ? there is no UNDO/REDO statistics available in trace files. Even if we have found an application module and enabled trace there is still no easy way to find the SQL that generates the most REDO data.

You would say that combining all methods named above we could narrow the scope and find the root cause of issue. And you are right. In no other options available we use any diagnostics to find the root cause. However I would say that if V$SQL… views would have UNDO/REDO statistics we could point to the SQL causing the issue in no time. I think there is a place for enhancement request.

What do you think? Have you been in such situations as I described? What options did you use to find the SQL responsible for the problem? Does Oracle need to add UNDO/REDO related statistics in database kernel instrumentation?

Please do not hesitate to leave your comment for this blog post.

Yury

9 Responses to “Oracle DBA: Do we need REDO statistics in V$SQL ?”

  • Are you looking for something historical in nature or real-time? (i.e. AWR or v$)

    v$transaction should tell you via used_ublk , and you can join that with v$session and v$sql to get the sql and session information. Although I have doubts this will point to a single DML statement as it is transaction based.

    Would it be enough to look at all DML statements in the v$sql view and ch eck out the rows_processed field? I suppose that doesn’t tell much as the size of the row would determine the change vectors and redo.

    real time I use something like:
    select a.os_user_name os
    , a.oracle_username us
    , b.owner own
    , b.object_name obj
    , b.object_type typ
    , c.segment_name seg
    , d.used_urec urec
    , d.used_ublk ublk
    from v$locked_object a
    , dba_objects b
    , dba_rollback_segs c
    , v$transaction d
    , v$session e
    where a.object_id = b.object_id
    and a.xidusn = c.segment_id
    and a.xidusn = d.xidusn
    and a.xidslot = d.xidslot
    and d.addr = e.taddr
    ;

    • Hi Bradd,

      Thank you for the hint. The only concern that I have is the join in between “v$locked_object a” and “dba_rollback_segs c”. As Gary rightly noticed in this post comments the relation ship between a and c are one to many. Therefore if a transaction updates 2 objects at once you will link an UNDO data to both of the objects and there is no information how much UNDO is related to one object or another.
      Just to demonstrate the point I made the following simple example:

      21:35:34 SYSTEM@SON:1> create table a as select lpad('a',1000,'a') text from dba_objects where rownum create table b as select lpad('a',1000,'a') text from dba_objects where rownum @curpid

      SPID SID SERIAL# USERNAME SERVER SADDR
      ------------------------ --------------- --------------- ------------------------------ --------- --------
      6336 7 3 SYSTEM DEDICATED 2858D19C

      21:37:18 SYSTEM@SON:1> update a set text = lpad('b',1000,'b') ;

      1000 rows updated.

      21:37:25 SYSTEM@SON:1> update b set text = lpad('b',1000,'b') ;

      1000 rows updated.

      21:37:29 SYSTEM@SON:1>

      21:40:24 SYSTEM@SON:1> select a.os_user_name os
      21:40:25 SYSTEM@SON:1> , a.oracle_username us
      21:40:25 SYSTEM@SON:1> , b.owner own
      21:40:25 SYSTEM@SON:1> , b.object_name obj
      21:40:25 SYSTEM@SON:1> , b.object_type typ
      21:40:25 SYSTEM@SON:1> , c.segment_name seg
      21:40:25 SYSTEM@SON:1> , d.used_urec urec
      21:40:25 SYSTEM@SON:1> , d.used_ublk ublk
      21:40:25 SYSTEM@SON:1> from v$locked_object a
      21:40:25 SYSTEM@SON:1> , dba_objects b
      21:40:25 SYSTEM@SON:1> , dba_rollback_segs c
      21:40:25 SYSTEM@SON:1> , v$transaction d
      21:40:25 SYSTEM@SON:1> , v$session e
      21:40:25 SYSTEM@SON:1> where a.object_id = b.object_id
      21:40:25 SYSTEM@SON:1> and a.xidusn = c.segment_id
      21:40:25 SYSTEM@SON:1> and a.xidusn = d.xidusn
      21:40:25 SYSTEM@SON:1> and a.xidslot = d.xidslot
      21:40:25 SYSTEM@SON:1> and d.addr = e.taddr
      21:40:25 SYSTEM@SON:1> ;

      OS US OWN OBJ TYP SEG UREC UBLK
      -------------------- ---------- ---------- ---------- ------------------- ------------------------------ ---------- ----------
      MYHOST\velikanov SYSTEM SYSTEM B TABLE _SYSSMU10_1955032764$ 286 286
      MYHOST\velikanov SYSTEM SYSTEM A TABLE _SYSSMU10_1955032764$ 286 286

      21:40:28 SYSTEM@SON:1>

  • Gary says:

    I had a couple of corrections by Nuno when I last blogged on Redo/undo.

    http://blog.sydoracle.com/2011/03/everything-happened-at-once.html

    Multiple DML statements in a transaction can depend on the same undo block, so that makes it hard to allocate it to a SQL statement (which is why it makes sense in v$transaction).

    Redo doesn’t even seem to belong to a transaction.

    rows_processed for a delete/update/insert should give some idea (though rows can be very different sizes). Delayed block cleanout would also have an interesting effect on redo/undo. Queries could generate it without being ‘responsible’ for it.

    • Thanks Gary for the comments. I agree on the transaction & undo & sql relationship. However in general an SQL (I should say DML here) changes block and generates undo. IMHO: It shouldn’t bee too complex for Oracle kernel team to introduce related instrumentation. Or I am mistaken?

  • Andy Klock says:

    I think your question is very valid.

    Perhaps for item 2 we could the objects in the SQL with dba_dependancies to determine if they are referencing the segmentes in question.

    • Thanks Andy for the comment and the support.
      dba_dependancies is a good hint. There are some possible issues tho: a) what is the segment used in 100+ PL/SQL objects? it will point us to nowhere than. b) what if the segment is changed from outside (SQL running from application rather than from PL/SQL)? Than this option may even mislead an investigation.

  • Gwen Shapira says:

    I agree. I also got interesting suggestions when I blogged on the subject (esp. logoff trigger that copies the numbers from v$sesstat).

    http://prodlife.wordpress.com/2009/11/04/the-senile-dba-guide-to-troubleshooting-sudden-growth-in-redo-generation/

    • Hi Gwen,

      Thank you for the comment and the reference. The approach you described in your blog is very good one in given circumstances.
      However my point here is: Oracle missing instrumentation.
      Is it a valid point? What do you think?

      PS Just wonder if “sum(RBABYTE) as RBABYTE ,sum(RBABLK) as RBABLK from v$logmnr_contents” really gives us bytes that the statement generated in the redo log file? According to the documentation it is not
      REF: http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_1154.htm
      RBABLK NUMBER RBA block number within the log file
      RBABYTE NUMBER RBA byte offset within the block

  • THX Charlie for the vote!

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>