About
Hello, Welcome to my blog
I am Riyaj Shamsudeen living in Dallas, TX USA.
I have 19+ years of experience in Oracle and 18 years as an Oracle DBA/Oracle Apps DBA. I am the principal engineer for OraInternals, a RAC & performance consulting company. We specialize in RAC, performance tuning, database internals, recovery and EBusiness suite. If you would like to hire me, please visit Ora!nternals or email me at rshamsud-x@gmail.com (Remove -x from email, trying to escape from bots :-). I also conduct specialized training on Advanced RAC troubleshooting, database performance analysis etc. My tweet handle is riyajshamsudeen
I have authored numerous papers, presentations and co-authored two books about Database internals, RAC, performance, EBusiness Suite and recovery. I used to teach in community colleges in Dallas such as North lake college. I was a board member for DOUG (Dallas Oracle User Group).
Would love to hear comments from readers.
Oracle database internals by Riyaj 
RMOUG Training Days 2010: It’s A Wrap! said
[…] Riyaj Shamsudeen was not able to make the flight to Denver due to illness, but he has made his presentations (“Why Does Optimizer Hate My SQL?” and “Advanced RAC Troubleshooting”) available online. […]
James said
Hello,
I was wondering if you accept guest post for your blog. If you do, I would like to submit a few. You can see a sample of my work at LaptopComputers.org under the author James Mowery. I’ve also written for several high-profile blogs like Mashable, Perfromancing, and CMSWire. Thank you for your time.
– James
anshabhishek2000 said
Hi
Suppose My concurrent is reading from UNDO +GSI03D01/gsiprdpm/datafile/apps_undo4.504.693782129
File ID : 219
%Activity : 1.63
Event : DB FILE SEQUE READ
How can I find how much time program has spend in reading Undo? Is there any script here?
Please Advise
Regards
Ansh
orainternals said
Hi
If the process is spending more time reading undo blocks, you need to understand why the process is reading undo block. Few statistics are important, watch the following statistics in those sessions: [corrective action depends upon which one of the statistics is increasing at an higher rate.]
–transaction tables consistent reads – undo records applied
–data blocks consistent reads – undo records applied
If you want to find the time spent on reading undo blocks, you could use ASH data. Following script should show the events, count of waits (Remember ASH uses sampling technique, different from a trace file analysis). For undo segments, current_obj# is set to 0 ( as tested in 10gR2). You could filter out just current_obj#=0 too, but just make sure the state is WAITING for ‘db file sequential read’.
select
min(start_time) start_time,
max(end_time) end_time,
current_obj#,
decode (event , null, 'ON CPU',event) event,
sum(decode(session_state,'ON CPU',1,0)) cnt_on_cpu,
sum(decode(session_state,'WAITING',1,0)) cnt_waiting
from
( select
first_value(sample_time) over( order by sample_time ) start_time,
last_value(sample_time) over( order by sample_time
rows between unbounded preceding and unbounded following ) end_time,
current_obj#,
event, session_state , sql_id
from
(select * from v$active_session_history ash where session_id= &&sid and session_serial#=&&serial_number)
)
group by decode (event , null, 'ON CPU',event), current_obj#
order by sum(decode(session_state,'ON CPU',1,0)) + sum(decode(session_state,'WAITING',1,0))
/
If you can enable sqltrace, you can also measure time spent on undo block reading by analyzing trace file. Here are the few lines that shows that session is trying to access undo blocks. It is easy to grep for obj#=0 and count them this way too.
WAIT #79: nam=’gc cr block 2-way’ ela= 1813 p1=94 p2=731983 p3=162 obj#=0 tim=307589480427
WAIT #79: nam=’gc cr block 2-way’ ela= 3204 p1=94 p2=731973 p3=162 obj#=0 tim=307589483955
WAIT #79: nam=’gc cr block 2-way’ ela= 3230 p1=94 p2=731971 p3=162 obj#=0 tim=307589487490
WAIT #79: nam=’gc cr block 2-way’ ela= 4840 p1=94 p2=731970 p3=162 obj#=0 tim=307589492896
WAIT #79: nam=’gc cr block 2-way’ ela= 3369 p1=94 p2=731969 p3=162 obj#=0 tim=307589497146
If the undo blocks are already in the cache, then none of these waits will occur. In that case, it is probably easier to take corrective action using the statistics posted earlier. You could potentially turn on logical consistent block reading event and review those trace files too, but this method is very time consuming and usually, you can get all of that information from the statistics itself.
cheers
Riyaj
salman said
Hello,
I am new to Oracle RAC and learning slowly.
I am trying to understand the wait events and wanted to know if “gc current request” and
“gc cr request” ?
Also could you guide me where I can read and understand more about such waits on the internet.
Thanks,
Salman.
Mohd Rasheeduddin said
Respected Riyaz Shamsuddin Sir,
I am an oracle DBA here in India, want to understand the concept of undo and redo in deapth. Kindly refer a document through which I could understand the concept.
Thanks,
Mohd Rashid
orainternals said
Hello Mohd
Concepts guide, Tom Kyte’s book will be a good starting point.
Tom Kyte’s book:
ttp://www.amazon.com/Expert-Oracle-Database-Architecture-Programming/dp/1430229462/ref=sr_1_1?ie=UTF8&qid=1315670377&sr=8-1
Cheers
Riyaj
Mohammad Rasheeduddin said
Thanks ,
It really fills me with joy when I see a prompt response from a Oracle expert to very new comer.I am just a three year experience DBA, passed out in 2007(B.Tech) from AMU.
My confusion is:-
As I know that for Rollback command to execute, undo data is required not the redo. Redo data is required for rollforwarding the changes. But as per my senior, for rollback, redo is also required, but I could not get the justification …
Due to this I decided to understand that how actually redo and undo entries maintained and managed at the time of requirement….
Link provided for the book is paid one. So I request you If there is some document (free) related to this … please do forward …
Regards,
Mohammad Rasheeduddin
orainternals said
Read my paper and presentation https://orainternals.files.wordpress.com/2008/04/riyaj_debunking_myths_about_redo_doc.pdf, https://orainternals.files.wordpress.com/2008/04/riyaj_debunking_myths_about_redo_ppt.pdf.
Sorry, but you should buy books to enhance your knowledge. Tom Kyte’s books are extremely well written and if you want to understand the concepts correctly, then you should start with books written by him. Consider that as an investment in to your future.
“If you think education is expensive, try ignorance” – Derek Bok.
I will try to explain (over simplified version of a complex Database software!).
Let’s say that you are updating a row from ABC to DEF in a table. Couple of things happen in the database for this one statement (not a complete list since it is far more complex to discuss this in a comment).
1. Database will construct a redo record that will modify the row from ABC to DEF.
2. Statement can be rolled back, and so, database also need to store pre-image of the row. So, undo records are constructed that will modify the undo blocks to store undo records. But, you can not modify a non-temporary database block without a redo record. Essentially, a redo record will be constructed that will have both (i) a change vector indicating how to change the row from ABC to DEF (ii) a change vector to modify the undo block adding the undo record. This redo record will be copied to the log buffer.
3. Then this redo record is applied, which will make the changes both in database block and undo block.
4. It is very much possible ( more likely in production environment) for the DBW to write the modified block even if the transaction is still pending.
Let’s say that, you submit a rollback statement now. Remember the changes are already in the blocks. they need to be reversed. Now, what happens is:
1. Database software (the session) will walk the undo chain, read the undo record, construct a redo record from the undo record, copy to log buffer and then apply the redo record to the block.
2. Generating redo and applying redo will generate redo.
In a nutshell, changes are done through redo record whether is roll-forward or rollback. Of course, I am really scratching the surface of a complex database. There are so many things such as direct more inserts, changes to indexes, branch block changes, splits, block defragmentation etc that I am not discussing here.
Cheers
Riyaj
Mohammad Rasheeduddin said
Thanks Sir,
I said this, because I bought “Expert Oracle Database by Sam R Alapati’s”.
But today only I’ll have this recommended book. I really need some one like you who could help me in my doubts and building my stong base in Oracle.
What actually change vector mean?
Is it just the statement (way to apply changes) or statement + (dirty buffers).
What I know is that, DBWn process copies data block from datafile (cache miss) upon the user process’s request, put it in database buffer cache plus a copy of the same data block in undo (pre image of block) for reverting the changes when required (Rollback). Changed block in db cache (Dirty buffer),as per user’s request, are copied to log buffer (by server process),before they are written back to datafile by DBWn.
Correct me if I am wrong. !!
If above said is correct, means that what ever things are moved to redo buffer .. is actually dirty buffer (chaged portion of data block, except when tablespace in backup mode , when whole block image is copied to avoid block corruption). Here I need to have clear picture that What is this dirty buffer… I mean in what form it is stored in log buffer …. As per your comment .. Redo entry will be constructed that will have both (i) a change vector indicating how to change the row from ABC to DEF (ii) a change vector to modify the undo block adding the undo record. .. Kindly elaborate on change vector.
Thanks and Regards,
Mohammad Rasheeduddin.
Mohammad Rasheeduddin said
Hi,
We need database queries to figure out the status of a service, where its running currently, any service statistics available etc., Also some monitoring queries for certain stats, status etc.,
Like FOR TESTT01A@testdbaa, TESTT01B@testdbab
Service T01A is running on instance(s) TESTT01A
Service T01B is running on instance(s) TESTT01B
We do have crsstat , for service’s status … but Is there is any script to fulfill above requirement.
Thanks & regards,
Mohammad Rasheeduddin
Mohd Rasheeduddin said
The objective is to educate the developers on the ‘Performance Engineering’ related activities and provide them concrete set of guidelines, instructions (standard operating procedure in DBA terminology) to work with when dealing with performance issues related to Oracle. We need to have a document which would capture various data points and instructions to be followed when dealing with any Oracle related performance issue.
The document should have below sections:
1. Validation: How to validate the performance problem definition i.e what factors we need to evaluate before attributing the problem to database layer
2. Specification/Quantification: (Specify/Quantify) After the problem is validated what are the data points to be captured when reporting the problem such as response time, through put, data volume, concurrency etc
3. Reactive Performance Analysis Methodology to identify the root cause of the performance problem. This is where the tracing techniques, stats, bind variable etc would come into picture.
4. Proactive Performance Analysis Methodology to identify the POSSIBLE bottlenecks in a given module, to establish performance benchmark etc BEFORE the code goes into production.
oracle dba architecture said
Hi can you refer me any document for performance tuning please..
Sen said
Hi Riyaj,
For developing Oracle Database Administrator / Developer skill – Everyone has a philosophical way of saying it: Read Oracle documentation and practice on a test machine. But I feel, most of the times, documents are aligned in a format which is suitable for reading – To practice, either you should read the document completely and start practicing or follow Oracle university course (practice at the end of each chapter). What’s your advice or method you adapted over the years?
winfo solutions said
Nice Post Oracle Cloud Automated Testing Tool
shusssain said
Hi,
Whats the fastest way to contact you?
Riyaj Shamsudeen said
You can gmail rshamsud, but not promising anything though.