Archive for July, 2007

Of course it’s the Database, I don’t need a Profiler

We all know that when any database backed application starts to behave slowly, its always the databases fault, right? After all, if the application code hasn’t change so what else could it be?

If you have spent much time around Oracle, you are sure to have seen complex queries when Oracle decides to changes its query plan for “no good reason” and application performance changes drastically. Perhaps this is what leads to all this finger pointing at the database, mostly by people who don’t understand how it works.

Last week I was contacted to investigate a performance problem in an application that had everyone baffled. The system works off 15 – 20 queues of requests, and a batch job is responsible for processing each queue, so there are up to 20 batch jobs running at once. Normal throughput for a batch job is several requests per second, but this had dropped to several a minute and things were backing up.

Lost Time

The DBAs had provided me with a level 1 trace file captured during this slowdown that looked very normal – no bad queries in sight. Only strange thing was a lot of walk clock time in the trace compared to CPU seconds (5 or 6 times more elapsed time). My initial reaction was certain there was a locking issue somewhere in the database, so I asked the DBAs to create a level 8 trace file that would surely should show me where the all this “lost time” had gone.

No such luck, Oracle was not waiting on anything, so where had all this time gone?

Perhaps the database server was under heavy load you say? Not so, this was a 24 core server 50% idle through the test, humm.

Luckily, I had read Optimising Oracle Performance and actually understand much of the gibberish inside an SQL Trace file. Still mystified where all the time had gone, I fired up Emacs and used Ruby to tell me the biggest time between two database calls, which stood at an impressive 30 seconds between a fetch on cursor 18 and exec on 19 – ie the “lost time” was in the application somewhere between those two queries, not the database calls.

In this case its a bit greyer, as the application is written in PLSQL, but the problem turned out to be the OS struggling to open log files in a directory containing over 2 million files!

Power to the Profiler

The morale of this story, apart from the need to housekeep your log files, is that its important to have the ability to profile your application code as well as the database calls.

Instead of writing scripts to parse Oracle trace files, I should have ran the code through DBMS_Profiler which would have solved this mystery in about two minutes and saved much head scratching and other people making ridiculous suggestions to change irrelevant database parameters!

July 28, 2007 at 6:26 pm Leave a comment

Emacs and Oracle

After learning so much about Emacs to get my Rails setup working the way I wanted it, I though it was about time I figured out how to use SQLPLUS in Emacs too. Turns out it was really easy, as sql-mode is built right in, no .emacs changes required or extra files to download.

To connect to SQLPLUS in an emacs buffer, fire up emacs and type ALT-x sql-oracle. Emacs will prompt you for a username and password and a database to connect to. The database name will need to be in your local tnsnames.ora – in other words, if you cannot sqlplus username/password@database, emacs will not be able to connect either.

You can now enter commands into SQLPLUS just like normal, only inside Emacs. The thing that always frustrated me about SQLPLUS is that there is no command history recall, so I always found myself writing a query in an editor, and copying and pasting into SQLPLUS. Not anymore … thanks to Emacs.

When you enter sql-oracle mode and login, Emacs splits your window in two. You can then edit your SQL in the original window and send the query to SQLPLUS in the other buffer in (at least) one of two ways:

  • Send the entire buffer by typing ALT-x sql-send-buffer
  • Select a region to send by typing ALT-x sql-send-region, or if you buffer is in sql-mode, type CTRL-C CTRL-r

You can of course bind a key sequence to each of these commands to save on the typing if you use them a lot!

If you have an SQLPLUS buffer open for a while, it could get very large. To truncate it, use the command ALT-x comint-truncate-buffer, or add the following to your .emacs:

(add-hook 'sql-interactive-mode-hook
              (function (lambda ()
                          (setq comint-output-filter-functions 'comint-truncate-buffer
                                comint-buffer-maximum-size 5000
                                comint-scroll-show-maximum-output t
                                comint-input-ring-size 500))))

Which will keep your buffer under 5000 lines.

July 10, 2007 at 12:54 pm 4 comments

Emacs and Rails

This tutorial is a bit out dated now.  I have created a new version of it over on my new blog which is an updated version of this one.

In the last article, I described how to get Ruby syntax highlighting, electric mode and some useful techniques to run code you are editing. This time I am going to describe how to install a full blown Rails development environment, that includes code snippets, and much more. Note that the Emacs-rails package requires Emacs 22 or greater.

What you need

If you have been following along with my setup, copy snippet.el and find-recursive.el into your include directory (or somewhere on your emails load path). Extract the Emacs-rails tar file into the include directory too. That should create a new directory, emacs-rails.

Now for the necessary changes to the _emacs file:

; needed for rails mode
(require 'snippet)
(require 'find-recursive)
; The rails require needs to go after ECB
; otherwise it loads a new incompatible speedbar
(add-to-list 'load-path "C:/emacs-22.1/includes/emacs-rails")
(require 'rails)

One other thing to note – I needed to perform these includes in the _emacs AFTER my code to setup ECB. Emacs-rails seems to include a different version of the Speedbar, which breaks ECB.

What can emacs-rails do?

I have found the documentation to be a bit sketchy, but there is a good summary of the various snippets and some of the other commands available.

In my setup, rails-minor-mode started automatically when I browsed into one of my Rails projects using ECB. If for some reason it doesn’t start, you can start it with ALT-x rails-minor-mode.

Since I am using ECB, I don’t envisage using the commands to jump to models and controllers much but I will try them out and see how it goes!

The snippets are very useful. If you are running emacs with toolbars enabled (the default) a new snippets menu will appear when in Rails mode, which you can use to insert them. Better is to learn the short codes for the snippets you use often. For example, typing:


and hitting the tab key will result in the following appearing:

flash[:notice] = 'Successfully'

Each of the placeholder fields are highlighted, so you can hit TAB to get into each of them and just start typing to replace them – its easier to play with than to explain!

There are a host of other menu options available when in Rails mode that allow you to view the log files and start the webserver among other things. I have only just scratched the surface myself and am still learning, so I cannot say much more at the moment!

Update – A few months later

After using Ruby mode and Rails mode for a few months, something I could just not get used to was Electric mode. I can live with the automatically inserted ‘end’ keys words when I open a new method def or an if statement, but single character completions like a closing quote or curly bracket just frustrated me. You still have to type ctrl-f to skip forward a character to get past that auto-inserted quote, or use your arrow keys – its easier to just type the quote!

Different people will prefer different things, so to customise just how much electric you want Emacs to provide you can edit the ruby-electric.el file. Look for a section like the following:

(defun ruby-electric-setup-keymap()
  (define-key ruby-mode-map " " 'ruby-electric-space)
;;  (define-key ruby-mode-map "{" 'ruby-electric-curlies)
;;  (define-key ruby-mode-map "(" 'ruby-electric-matching-char)
;;  (define-key ruby-mode-map "[" 'ruby-electric-matching-char)
;;  (define-key ruby-mode-map "\"" 'ruby-electric-matching-char)
;;  (define-key ruby-mode-map "\'" 'ruby-electric-matching-char)
;;  (define-key ruby-mode-map "|" 'ruby-electric-bar))

Each line provides electric mode for each of the different characters – I commented out all the lines starting with ‘;;’ (as ; denotes a commented line), which means that now I only get electric ‘end’ keywords added and nothing else.


In this series of five articles, I have explained why I decided to try Emacs, how to install it on windows and the Mac, setup ECB, some Ruby specific tips and finally this article to get the Rails development environment working. Hopefully you found them useful! At this point I am still a relative Emacs newbie – I don’t know much beyond what is in these posts, but I am learning something new almost every day. For the sake of completeness, the contents of my .emacs is below.

; sets emacs to prompt on close so you cannot close by accident!!
;(setq confirm-kill-emacs 'yes-or-no-p)
(global-font-lock-mode 1)

; directory to put various el files into
(add-to-list 'load-path "C:/emacs-22.1/includes")

; loads ruby mode when a .rb file is opened.
(autoload 'ruby-mode "ruby-mode" "Major mode for editing ruby scripts." t)
(setq auto-mode-alist  (cons '(".rb$" . ruby-mode) auto-mode-alist))
(setq auto-mode-alist  (cons '(".rhtml$" . html-mode) auto-mode-alist))

(add-hook 'ruby-mode-hook
            (add-hook 'local-write-file-hooks
                           (untabify (point-min) (point-max))
            (set (make-local-variable 'indent-tabs-mode) 'nil)
            (set (make-local-variable 'tab-width) 2)
            (imenu-add-to-menubar "IMENU")
            (define-key ruby-mode-map "C-m" 'newline-and-indent)
	    (require 'ruby-electric)
            (ruby-electric-mode t)

; These lines are required for ECB
(add-to-list 'load-path "C:/emacs-22.1/plugins/eieio-0.17")
(add-to-list 'load-path "C:/emacs-22.1/plugins/speedbar-0.14beta4")
(add-to-list 'load-path "C:/emacs-22.1/plugins/semantic-1.4.4")
(setq semantic-load-turn-everything-on t)
(require 'semantic-load)

; This installs ecb - it activated with M-x ecb-activate
(add-to-list 'load-path "C:/emacs-22.1/plugins/ecb-2.32")
(require 'ecb-autoloads)

(setq ecb-gzip-setup (quote cons))
(setq ecb-layout-name "left14")
(setq ecb-layout-window-sizes (quote (("left14" (0.2564102564102564 . 0.6949152542372882) (0.2564102564102564 . 0.23728813559322035)))))
(setq ecb-source-path (quote ("c:/rails")))

; needed for rails mode
(require 'snippet)
(require 'find-recursive)
; The rails require needs to go after ECB
; otherwise it loads a new incompatible speedbar
(add-to-list 'load-path "C:/emacs-22.1/includes/emacs-rails")
(require 'rails)

July 3, 2007 at 4:56 pm 6 comments