Oracle Database Optimization for Developers

by Ian Hellström (17 August 2014)

Let’s talk about my goings-on over at Read The Docs (RTD).

What is Read The Docs?

Before I can explain RTD, I have to mention Sphinx. Sphinx is a freely available, open-source documentation generator that converts reStructuredText (reST) into HTML, PDF, EPUB, and man pages. Sphinx is firmly rooted in the Python language, for which it was originally conceived. It handles table of contents automatically, code blocks, including language-specific highlighting with Pygments, cross-references, hyperlinks, images, mathematical equations, indices, and more thanks to a plethora of extensions.

Sphinx allows you to write comments inside your source code that are then picked up by the autodoc extension, which generates structured documentation for you. It does the same for Python as Javadoc does for Java, Doxygen for a decent collection of programming languages, and Sandcastle for .NET assemblies.

Sphinx comes with a bunch of built-in HTML templates. The default can be changed in the configuration file conf.py by editing the line with html_theme = "default". If you’re not wowed by the built-in themes, you are of course free to create your own.

reStructuredText is a markup language not unlike Markdown, which I am using to write all posts on this blog. You may wonder why anyone would do that, and I could name a few good reasons why word processors and WYSIWYG editors are rarely ideal when working with larger documents, but I won’t.1 Truth be told, I am accustomed to writing documents in stripped-down editors, which enables me to be productive even without a word processor or internet connection; I have created loads of documents in LaTeX, which is a document preparation system and another markup language, which is mainly used by academics and people who enjoy good typesetting but do not mind clacking away in a text editor.

A great editor for reST and Markdown is ReText. It offers a built-in, live preview pane that, admittedly, is not as fancy as MarkdownPad’s real-time preview with source-synchronized scrolling, but it is neither exclusively dedicated to Markdown nor only for Windows, which MarkdownPad is.

Read The Docs (RTD) is a publishing platform that compiles and hosts Sphinx documents. It integrates smoothly with standard code repositories based on Git, Mercurial, SVN, and Bazaar. You can therefore link up your repos on GitHub or Bitbucket, and — thanks to post-commit hooks — automatically update the documentation on RTD. The RTD theme has, in my opinion, a more modern feel to it than the default Sphinx themes, so it adds a certain pizazz to your documentation.

RTD not only hosts your technical documentation, it also provides downloadable HTML, PDF, and EPUB files. So, if your documentation spans more than a few pages, you can download it, and read it offline too, which is pretty neat.

I actually stumbled into RTD while I was working on an R library to perform a principal-component analysis on system metrics time series from an Oracle database (v$sysmetric_history) to find the root causes of performance troubles, when I discovered Avril Coghlan’s great Little Book of R. Her efforts combined with a monstrous text file that I had been maintaining for myself but always wanted to share in a more appropriate form gave me the impetus to look more closely at Sphinx and RTD.

Why Oracle?

Why not?!

Seriously though, as I spend most of my working days on Oracle’s databases, it seems only natural to focus on Oracle. I have worked with other database systems too, in particular Microsoft SQL Server, PostgreSQL, and Neo4j, but I am most familiar with the inner workings of Oracle.

The reasons I concentrate on optimization techniques for developers are twofold. One, I am a developer myself, so it’s only natural to stick to what I know best. Two, I feel that developers are often left out a bit. There are plenty of excellent resources for Oracle DBAs. What I find is missing though is a one-stop solution for developers with many tricks of the trade collected, especially techniques that developers can use that do not require a DBA to perform database tuning magic. And there are a lot of things developers can do without a DBA’s assistance to make queries run faster and have the database be more responsive.

I think the scope of the project, to provide a place for SQL and PL/SQL developers to learn about optimization techniques, is already ambitious enough without having to include other relational database management systems.

Mind you, I do not have the unholy notion of wanting to replace all the resources on Oracle. What I intend to do is structure it in a way that is useful to (aspiring) developers, as a lot is geared towards DBAs and not easily accessible to devs. We devs are simple folk.

Additionally, it is important to me that the project is open: anyone can contribute and anyone can read it. For free. Forever.

Up Next

As of this writing, the project is still under active development, and it likely will be because a) Oracle will continue to develop its flagship product, b) I shall continue to learn more about Oracle, and c) I shall therefore never run out of material any time soon. You can peek at the preliminary roadmap to see what I have planned so far. Should you be interested (and able) to contribute, please let me know and we can talk about your role in the project.

So, if your appetite’s been whetted, head on over to oracle.rtfd.io to read the latest on optimization techniques for Oracle developers.2

  1. OK, that’s a lie. I just can’t help myself… What is great about markup languages is that you do not have to worry about formatting and lay-out: it’s all defined in your style definitions; once you’re happy with the results you never have to think about it again. Because you create entire documents out of one or more source (text) files, you can add the files to your repo and enjoy all the benefits of version control — just like ‘normal’ source code! Moreover, if you work on one document with several people who happen to be at different geographical locations, you have a single, shared, up-to-date document repository, from which everyone on the project can work simultaneously. 

  2. By the way, if you like RTD and would like to keep the project alive, be sure to check out Gittip and make a donation. Disclaimer: I am not affiliated with RTD in any way.