Oracle database internals by Riyaj

Published papers

1. Why does optimizer hate my SQL-presentation

2. cost based query transformation-paper

3. Cost based query transformation presentation – presentation

4. Debunking myths about redo undo commit – paper

5. Debunking myths about redo undo commit – presentation

6. Exciting SQL new features – paper

7. Exciting SQL new features – presentation

8. Tools and techniques for advanced debugging

9. Performance tuning with SQL new features – paper

10. Performance tuning with SQL new features – presentations

11. Internals of locks

12. redo internals ppt

13. redo internals doc

14. Performance features 11g ppt

15. Battle of the nodes RAC performance myths ppt

16. Battle of the nodes RAC performance myths doc

17. Battle of the nodes RAC performance myths paper

18. Cost based query transformation ppt

19. Tuning 101 scientific approach to bottleneck identification ppt

20. Redo internals scripts in pdf format

21. Advanced_index_internals-presentation

22. COLLAB 2009 – 11g performance specific new features – presentation

23. COLLAB 2009 – 11g performance specific new features – paper

13 Comments »

  1. [...] My papers and presentations [...]

    Pingback by Correlation between column predicates « Oracle database internals — April 4, 2008 @ 2:06 am | Reply

  2. [...] RMOUG, etc., and I am also an Oak Table member. Some of my papers can be found on my personal blog: my papers and presentations. Bookmark online using:These icons link to social bookmarking sites where readers can share and [...]

    Pingback by Multi-Column Correlation and Extended Stats in Oracle 11g — April 9, 2008 @ 6:35 pm | Reply

  3. Hi Riyaj,

    Just found your website via Pythian entry. Good to see you blogging.

    Raj

    Comment by Raj Jamadagni — April 10, 2008 @ 12:27 pm | Reply

  4. Thanks for your kind words Raj.

    Comment by orainternals — April 11, 2008 @ 2:21 am | Reply

  5. I have just quickly scanned your site and notice that for each new sql feature you demonstrate a performance benefit – with the exception of pipelined functions.

    Have you investigated whether pipelined functions offer a performance benefit?

    In particular, I wonder if they would leverage multiple cpus when using Standard Edition.

    My assumption is that pipelined functions would favour indexes over full scans, but I don’t know if having > 1 cpu processing offsets the greater IO.

    Jim

    Comment by Jim Dickson — August 1, 2008 @ 4:16 pm | Reply

  6. Hi Jim
    Thank you for reading my blog.

    Pipelined parallel processing will improve performance. It is a performance specific feature. This feature is quite useful for multi-stage application processing. I know of a DBA who implemented pipelined functions to improve performance.

    I am not sure why would you think that pipelined parallel functions would favor indexes, but if you can provide your test case, definitely I can research that. What version of Oracle?

    These pipelined functions are especially useful in multi-cpu environment. I don’t know how it will interact with standard Edition. I just looked it up and I don’t see any thing specific to pipelined functions and standard edition though.

    Cheers
    Riyaj

    Comment by orainternals — August 4, 2008 @ 11:28 pm | Reply

  7. Thanks for an excellent blog Riyaj. What are the changes that you will make your SQL scripts mentioned in Redo Internals Tuning available?

    Martin

    Comment by Martin — August 5, 2008 @ 12:25 pm | Reply

  8. Riyaj,

    Excellent presentation/material on Oracle11g Performance related features. I was wondering to see if you happen to figure out what the lock type DO really means or indicates. I also noticed that there is another lock type AE in 11g for which I didn’t find any reference.

    Good Job.

    Thanks
    Chandra Pabba

    Comment by Chandra Pabba — November 13, 2008 @ 2:30 am | Reply

    • Hi Chandra:
      you can just check the new v$ view v$lock_type for a brief description of the lock.

      Comment by jametong — August 15, 2009 @ 1:07 pm | Reply

  9. Riyaj,

    Great stuff. I hope you don’t mind my asking – how did you discover that LMS won’t serve CR blocks until redo is flushed on the serving node? I think I have encountered a situation at work that fits that theory. I need to collect statistics from a few more work cycles before I can be sure. So far it looks as if freeing up lgwr on a high-dml instance is causing a significant reduction in gc cr waits in other instances. Does that sound right to you? Or did I perhaps misunderstand your point?

    Thanks.

    Paul

    Comment by Paul Kelley — January 6, 2009 @ 12:47 am | Reply

  10. Hello Paul

    Thank you for reading my blog.

    Your understanding is precisely correct. I dealt with a situation: A costly, parallel DML batch process was allowed to run in one node with the idea that effect in other nodes will be minimal. To our surprise, GC CR waits increased multi-fold causing excessive performance issues. Eventually, nodes became so unusable that we ended up killing parallel DML (which in turn caused massive rollback, increased redo and that is a story for another day).

    We tested this scenario in our pre-production test bed and we were able to prove that CR log flush waits by LMS is what caused issues in other nodes, with our test scenarios.

    Either way, workload characteristics (printed in first part of statspack or AWR report ) should help you here (somewhat). Specifically, values for the statistics “global cache cr block flush time” in the serving node will be much higher. Obviously, statspack/AWR report are printing averages, which in turn , can hide real problems. So, querying few of these global cache statistics from v$sysstat, every second or so, and analyzing that raw data would provide clear picture.

    Also read here about this statistics in this glossary glossary.

    HTH

    Cheers
    Riyaj

    Comment by orainternals — January 6, 2009 @ 3:18 pm | Reply

  11. Hi Riyaz,

    Good documents to read on. Very well framed.. Thanks.. Keep going on.

    I want to find out the correct estimations or understanding on wait events and avg time outs and their calculations. Can you please suggest anything.

    -Thanks again
    Suresh

    Comment by Suresh — April 7, 2009 @ 1:52 pm | Reply

  12. [...] a paper/presentation by Riyaj Shamsudeen entitled Battle of the Nodes: RAC Performance Myths (avaiable here). As I was looking through it I saw one example that struck me as very odd (Myth #3) and I [...]

    Pingback by Oracle Parallel Execution: Interconnect Myths And Misunderstandings | Structured Data — July 7, 2009 @ 12:02 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a comment

Blog at WordPress.com.