Santosh Kangane

Well, as all other Oracle databases, we too facing classical problem of data fragmentation on OLTP system handling 2500 + Concurrent users. With daily data archival process for around 3.5K+ master rows and its 60 child tables in proportion of 2 - 7 times bigger. So much of concurrent operations and every day data deletion was causing data fragmentation and index badness; we have to do shrinking activity periodically. Then, I have started investigation as to how can we do this auto? Can oracle take care of data fragmentation by auto shrinking? Can’t DML operation de-fragment underling data?    I have ASM as underling data storage. After investigation and talking with Tom Keyt, I found that Keeping “Row movement Enable” is the solution that oracle has provided with 9i and above version. Enable Row movement is just a permission given to oracle to change the row address(ROWID) when Inert/Update DML operation executes. Oracle will check if it can move the curre...

Well, Here is my Favorite Poem by  “Rudyard Kipling” If you can keep your head when all about you Are losing theirs and blaming it on you, If you can trust yourself when all men doubt you, But make allowance for their doubting too; If you can wait and not be tired by waiting, Or being lied about, don't deal in lies, Or being hated, don't give way to hating, And yet don't look too good, nor talk too wise: If you can dream - and not make dreams your master; If you can think - and not make thoughts your aim; If you can meet with Triumph and Disaster And treat those two impostors just the same; If you can bear to hear the truth you've spoken Twisted by knaves to make a trap for fools, Or watch the things you gave your life to broken, And stoop and build 'em up with wornout tools: If you can make one heap of all your winnings And risk it on one turn of pitch-and-toss, And lose, and start again at your beginnings And never breathe a word about your loss; If you can ...

To track the data changes in Oracle database, I’m planning to use Timestamp based approach. During implementation Rajesh has taken my attention to an interesting case, Where I have 2 Nodes in Oracle RAC, these 2 node can return different  timestamp even if you fire the concurrent requests. This difference can be up to 2mins as Timestamp is dependent on machine clock cycle. Oracle RAC keeps timestamp in Sync with the help of NTP (Network Time Protocol), which triggers after every 15mins ( http://www.oracledatabase12g.com/wp-content/uploads/html/RAC-Frequently%20Asked%20Questions.htm#A10074 ) Solution to this problem could be :          1.    Use of System Change Number (SCN) : A sequence number allocated by oracle to keep track of the changes. Oracle keep all the nodes and data changes in sync by allocating unique SCN number and uses this for backup and restore purpose.  SCN gets allocated at Block level on Commit operation by default. To ...

It was a great pleasure to present something in Cost based Oracle . Great thanks to Jonathan Lewis for his book Cost Based Oracle: Fundamentals . I would recommend everyone to read this book at least once to get the exact depth and breadth of CBO. It’s amazing…  Cost Based Oracle by  Santosh Kangane

Recently, I was working on an Archival process for one of my client, where this process suppose to move data from Live OLTP system to Archived database. This process is going little slow, so one of my DBA has advice me following approach, In delete part instead of using single delete statement with Join, open cursor and loop it through. Then fir delete statement for each row by row in a loop. I was wondering how oracle would go about it ? and will it improve the things? So, h ere is my small investigation on this ,      1.    If you have db_file_multiblock_read_count = 8 and Tablespace Block size = 8KB . So, in single IO            read request oracle would read 64KB of data.      2. If you fir a delete statements in a loop , oracle will have to instantiate IO request for each delete statement           and out of that it will pick up just one row or few rows if there is one to m...

Hi Friends, Recently for one of the functionality in my app, I was working on the bulk data operation. To handle the bulk data movement with DML exception handling Oracle Provides us two inherent functionality,             1.        Save Exceptions : http://rwijk.blogspot.com/2007/11/save-exceptions.html             2.        DML Error Logs : http://www.orafaq.com/node/76 So, I was evaluating on both the approaches, here is what I have came up to so far, Performance for Save Exceptions Vs DML Error Logs, a.        Save exception works better for the large data volume over DML Error logs. -           You can keep better control on the number of rows been process in particular iteration by configuring Bulk collect row count limit...

Few days back, I had seen some magical behavior of oracle sequences. We got a complaint from users about missing on some data. When we check database and SQLs, logically it must have traveled in data files. After analyzing the data download plug-ins, file status screen we found that, 17 files have the same name around time frame of 1658Hrs to 1735Hrs. We are using the Oracle Sequence Number to allocate the file name uniquely, and then also why file names are duplicate?? What's wrong there??  -- After 1636hrs oracle started allocating the sequence number with lesser value than the current one and continued till 1740Hrs.    -- The file allocation logic was looking for max file ID value.  -- Which coming out to be same for this entire time frame; as new file ID has lesser value than the old file ID. How this happ...