How to see number of uncommitted records?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: How to see number of uncommitted records?

  1. #1
    Join Date
    Sep 2000
    Posts
    43

    How to see number of uncommitted records?

    Hello,
    If a user or a process executes update on a table, how can DBA see the number of records that the update did? Of course that update has not committed yet.
    Thank you very much!
    Alla
    Alla S. Pfauntsch
    Oracle DBA
    ========================================
    "Life is what happens while you are planning something else".

  2. #2
    Join Date
    Oct 2002
    Posts
    807
    You can "estimate" based on amount of undo generated.

  3. #3
    Join Date
    Sep 2000
    Posts
    43
    Can you please give me an example if possibe?
    Alla S. Pfauntsch
    Oracle DBA
    ========================================
    "Life is what happens while you are planning something else".

  4. #4
    Join Date
    Jul 2000
    Posts
    521
    This will be a really bad way to estimate something that isn't of much use : *Unless someone knows a better way)

    Start a session. Do some UPDATEs. Lets say you updated 1000 records. Now, look at the USED_UBLK column in V$TRANSACTION using this query : select USED_UBLK from v$transaction where addr=(select taddr from v$session where sid=&1). &1 is the SID of the session from where you did the UPDATEs. Record that number. Now, do another DML that updates 2000 records. Check the USED_UBLK value again. Do you see a relation / trend ?

    Please remember : UPDATE / INSERT / DELETE generate different amount of UNDO amount. So, there isn't going to be any "formula" that can be used.

    Now, if you want to call it "estimation", so be it...
    svk

  5. #5
    Join Date
    Sep 2000
    Posts
    43
    Thank you so much! Let me give it a shot :-)
    Alla S. Pfauntsch
    Oracle DBA
    ========================================
    "Life is what happens while you are planning something else".

  6. #6
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    explain plan for
    update hrishy
    set rn=10
    where rownum>10

    Code:
    L> select * from table(dbms_xplan.display);
    
    AN_TABLE_OUTPUT
    ------------------------------------------------------------------------------
    
    ------------------------------------------------------------------
    Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
    ------------------------------------------------------------------
      0 | UPDATE STATEMENT     |             |  5035 | 15105 |     3 |
      1 |  UPDATE              | HRISHY      |       |       |       |
      2 |   COUNT              |             |       |       |       |
      3 |    FILTER            |             |       |       |       |
      4 |     TABLE ACCESS FULL| HRISHY      |  5035 | 15105 |     3 |
    ------------------------------------------------------------------
    will isnt this much simpler and straight forward.

    regards
    Hrishy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width