Oracle Query optimization

We are working on a RoR application that needs to crunch data across multiple tables (> 7 million records) from a huge Oracle database. The sql queries were initially taking a long time, upto 5 minutes for some queries.
Following are some links and tips to optimize the sql queries - specific to Oracle.

1. I added the following snippet to the environment.rb which gives me some stats for the queries:
   
   

   # only run this code in development
   if ENV["RAILS_ENV"] == "development"
   
   # modify MysqlAdapter to track transfer stats
   class ActiveRecord::ConnectionAdapters::OracleAdapter
   @@stats_queries = @@stats_bytes = @@stats_rows = 0
   
   def self.get_stats
   { :queries => @@stats_queries,
   :rows => @@stats_rows,
   :bytes => @@stats_bytes }
   end
   
   def self.reset_stats
   @@stats_queries = @@stats_bytes = @@stats_rows = 0
   end
   
   def select_with_stats(sql, name)
   bytes = 0
   rows = select_old(sql, name)
   rows.each do |row|
   row.each do |key, value|
   bytes += key.length
   bytes += value.length if value && value.respond_to?('length')
   end
   end
   @@stats_queries += 1
   @@stats_rows += rows.length
   @@stats_bytes += bytes
   @logger.info sprintf("%d rows, %.1fk", rows.length, bytes.to_f / 1024)
   rows
   end
   
   alias :select_old :select
   alias :select :select_with_stats
   end
   
   # modify ActionController to reset/print stats for each request
   class ActionController::Base
   def perform_action_reset
   ActiveRecord::ConnectionAdapters::OracleAdapter::reset_stats
   perform_action_old
   end
   
   alias :perform_action_old :perform_action
   alias :perform_action :perform_action_reset
   
   def active_record_runtime(runtime)
   stats = ActiveRecord::ConnectionAdapters::OracleAdapter::get_stats
   "#{super} #{sprintf("%.1fk", stats[:bytes].to_f / 1024)}"
   end
   end
   
   # trim blob logging
   #class ActiveRecord::ConnectionAdapters::OracleAdapter
   #def format_log_entry(message, dump = nil)
   #if dump
   #dump = dump.gsub(/x.([^.]+)./) do |blob|
   #(blob.length > 32) ? "x.#{$1[0,32]}. (#{blob.length} bytes)." : $0
   #end
   #end
   #super
   #end
   #end
   
   end
   

1. Check out this link on Oracle.com and download the Oracle version of query_analyzer. This plugin automatically runs an explain plan query for the select query.
   

   Analyzing
   plan_table_output
   --------------------------------------------------------------------
   
   --------------------------------------------------------------------
   | Id | Operation | Name | Rows | Bytes | Cost |
   --------------------------------------------------------------------
   | 0 | SELECT STATEMENT | | 1 | 31 | 8598 |
   | 1 | SORT UNIQUE | | 1 | 31 | 8598 |
   | 2 | TABLE ACCESS FULL | XXX | 128 | 3968 | 8583 |
   --------------------------------------------------------------------

   
   
2. First level of optimization: the output of the explain plan shows you which tables columns in the WHERE clause cause a FULL table access. These columns are targets for optimizations by dropping indexes on them. This will result in a explain plan output as shown below. Note the INDEX RANGE SCAN bit.
   

   ----------------------------------------------------------------------------
   | Id | Operation | Name | Rows | Bytes | Cost |
   ----------------------------------------------------------------------------
   | 0 | SELECT STATEMENT | | 25 | 675 | 332 |
   | 1 | TABLE ACCESS BY INDEX ROWID| XX_XXXXX | 25 | 675 | 332 |
   | 2 | INDEX RANGE SCAN | XX_X_INDEX1 | 430 | | 4 |
   ----------------------------------------------------------------------------
   

   However, being too aggressive in creating indexes is not recommended. An index is essentially a seperate file on the filesystem and every time the table data is altered, each of the index files need to be updated.
   
   
3. Second level of optimization: Read this link to understand Index selectivity. Here are the steps I took to gather statistics and compute selectivity:
   
1. Download and install SQL Developer from oracle.com and connect to the database.
2. Right click on Table -> Statistics -> Gather statistics
   Alternately, you can run the following query:
   analyze table XXXX compute statistics;
   select column_name, num_distinct from user_tab_columns where table_name='XXXXX'
   
3. Note the number of distinct values of the data in the column you are interested in. Alternately run a sql query such as
   select count(distinct ) from XXXXX
   
4. Run a sql query to get number of records in the table:
   select count(*) from XXXXX
5. Index selectivity = Distinct Column values / Num rows in table
6. A larger value for Index selectivity is good. This means there are fewer rows corresponding to the column's each distinct value.
   
   
4. In my case, due to a large number of records in the table and the num distinct values of the columns being small, the index selectivity was less than 0.01. So even after adding indexs on the columns in the WHERE clause, the query was slow. In fact it made loading data into the database slower.
   However if the query is returning only the indexed column's data in the SELECT clause, the data can be returned from the index file directly (the table data is not referred) and that indeed may result in a faster query.
   Another option is to consider a Bitmap index instead of a B-Tree index. Bitmap indexes are useful when the column to be indexed has low cardinality, e.g. gender or marital status. Bitmap indexes work well with Data warehousing kind of apps which B-Tree indexes (typical) are more common with OLTP systems. So consider the Bitmap index characteristics and impact on your application carefully before deciding on using Bitmap indexes. Especially the fact that inserting data into tables with bitmap indexes is a lot slower than tables with B-Tree indexes.
   
   
5. Third level of optimization: Composite indexes. Sometimes indexes with lower selectivity can be combined into a composite index to create an overall index with a higher selectivity. Oracle also supports Index Skip scan which allows a composite index to be used even when all the columns specified in the composite index are not present in the SQL WHERE clause.
   
   
6. Note that using LIKE '%xxx' and other terms in the WHERE clause can cause FULL Table scans. However LIKE 'xxx%' can potentially use an index. There are several options as listed here and here.
   

Links:

• Oracle 9i Books portal
  
• Selecting an Index strategy
• Index Selectivity
• Tips for optimizing Rails on Oracle
• Query Analyzer for Oracle
• Using EXPLAIN PLAN
• Steps in the EXPLAIN PLAN output
• Computing Table and Database statistics
• Bitmap Index vs B-Tree index
• Secrets of Bitmap indexes
• Tuning a LIKE clause
• LIKE with % at START

Announcing YUIRails

Update 2/24/08 - I have uploaded new versions of yuirails.js and yuirails-min.js.
The new version adds file upload capability (YUI + YuiRails + attachment_fu) and fixes a few misc bugs. The file upload capability works with single as well as multiple file uploads (ajax). I will be posting on this in coming weeks. Let me know if there is interest and I will try to put it up sooner. The demo with the new js files is here and is otherwise unchanged.

I am using YUI with Rails at work to develop a complex web application using the treeview, panel, connection, and various other widgets. I used to miss being able to use link_to_remote and remote_form_for without having to include prototype.js (which at the latest version is about 124KB.
So over the Christmas break, I sat down and coded up YUIRails. The intent of this library is to provide a thin layer of logic that glues RJS - PrototypeHelper with YUI connection manager. In its present form, it provides partial support for RJS Prototypehelper, limited to Element methods and Ajax.Request and Ajax.Updater. No support is provided for Scriptaculous effects.

To use this library, simply drop it in public/javascripts. In its uncompressed form it is at 18K, but a minimized version is only 6K. If there is enough interest, I will look into making the code tighter.

Following are a few restrictions in the usage:
a) I have stayed away from extending Dom elements with Element.methods. As a result you can't call $('foo').hide(); Rather you will have to use Element.hide('foo');
b) Following Element methods are supported:
- visible
- toggle
- hide
- show
- remove
- update
- replace

As a demo, a simple rails application is included in the distribution. You will need to create a MYSQL database test_yuirails_development owned by yuirails/testing

create database test_yuirails_development;
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX ON test_yuirails_development.* TO 'yuirails'@'localhost' IDENTIFIED BY 'testing';

and then

rake db:migrate

demo.tgz
yuirails.js - 18K
yuirails-min.js - 6.5K

I just setup the demo on a server. Check it out.

Enjoy.

acts_as_state_machine enhancements

Download the plugin enhancement here. Drop it into the lib directory and do a require in the model.

A project I am working on required me to use a state machine to manage the state of a model. After downloading acts_as_state_machine plugin and I set it up as mentioned here.

Though I liked the way the plugin sets up the state machine, it seemed to lack some functionality for my usage:

• To be able to define an override event that would allow the user to select the state the model should go to.
  
• While performing the transition from one state to another state, perform some operation that is specific to the triad - event, : from, :to
  

Let me show you what I mean by extending the example mentioned here.

%> script/generate model Person shirt_color:string trouser_type:string status:string
%> rake db:migrate

%> emacs -nw app/models/person.rb

require 'acts_as_state_machine_overrides'

class Person < ActiveRecord::Base

acts_as_state_machine :initial => :sleeping, :column => 'status'
state :sleeping
state :showering
state :working
state :dating

event :shower do
transitions :from => :sleeping, :to => :showering
transitions :from => :working, :to => :showering
transitions :from => :dating, :to => :showering
end

event :work do
transitions :from => :showering, :to => :working
# Going to work before showering?  Stinky.
transitions :from => :sleeping, :to => :working
end

event :date do
transitions :from => :showering, :to => :dating
end

event :sleep do
transitions :from => :showering, :to => :sleeping
transitions :from => :working, :to => :sleeping
transitions :from => :dating, :to => :sleeping
end

event :wakeup do
transitions :from => :sleeping,   :to => [:showering, :working]
end

event :dress do
transitions  :from => :showering,   :to => [:working, :dating],
:on_transition => :wear_clothes
end

def wear_clothes(shirt_color, trouser_type)
self.shirt_color = shirt_color
self.trouser_type = trouser_type
end

end

Ok, I put in a lot of stuff in there to chew on. Lets discuss it one by one.

event :wakeup do
transitions :from => :sleeping,   :to => [:showering, :working]
end

I can wake up and either go take a shower, or go sit on my computer and start working. This implies I have two transitions in the event wakeup:

a) from sleeping to showering
b) from sleeping to working

If I express this as

event :wakeup do
transitions   :from => :sleeping,    :to => :showering
transitions   :from => :sleeping,    :to => :working
end

%> script/console
>> p = Person.new({:shirt_color => 'red', :trouser_type => 'dress pants'})
>> p.save!
>> p.current_state
=> :sleeping
>> p.wakeup!
=> true
>> p.current_state
=> :showering

acts_as_state_machine simply picks up the first transition in this case. There is no way for me to wake up and start working, unless I create a new event for this transition and invoke it.

If you notice, these two transitions were already present in the example, but were fired when the user called work! or shower!. And that makes complete sense for this particular example where for all incoming transitions into a state are modeled as the event - i.e. all transitions that end up in the state :working are bundled in the event :work. I call this bottom up approach, where you create events based on incoming transitions for a state. In complex state machines however, you are not always free to choose the event based on the :to state, rather the events are modeled after real life actions that users perform. This is more like the top down approach, where you are creating events based on outgoing transitions from a state. Does that make sense? In any case, continuing on with this post.

So I extended acts_as_state_machine plugin to accept an array for the :to argument and allow the user to specify the next state to transition to when the event is fired.

event :wakeup do
transitions   :from => :sleeping,    :to => [:working, :showering]
end

%> script/console
>> p = Person.find(:all).last
>> p.set_initial_state
>> p.current_state
=> :sleeping
>> p.wakeup!(:next_state => 'working')
=> true
>> p.current_state
=> :working
>> p.set_initial_state
=> "sleeping"
>> p.wakeup!(:next_state => 'showering')
=> true
>> p.current_state
=> :showering

So now the user can be queried for the desired :to state.

Did you notice the extension to the event method. It now accepts the an optional hash argument as its last argument. Yup, I said - as the last argument- . But more on that in a minute.

So the new method signature is now event_name!(*args, [:next_state => ...]).

Next, we consider the issue of performing some work during the transition. The existing acts_as_state_machine plugin assumes any work that needs to be done would be done as you enter, after and exit each state. But with our new addition above of multiple possible :to states, there might be some common work that needs to be done that is not specific to the final :to state.
Case in point, after I take a shower, I need to wear clothes before either going to work or on a date. The act of wearing clothes does not fit in either going to work or going on a date.

I know what the counter-argument is going to be - :dress should be a state in itself and there should be a transition going from :showering to :dress and then transitions from :dress to :working and :dress to :dating. And then wearing the clothes should be done in the :dress state.

state :dress , :enter => :wear_clothes

event :dress do
transitions :from => :showering, :to => :dress
end
event :work do
transitions :from => :dress, :to => :working
...
end
event :work do
transitions :from => :dress, :to => :working
...
end
event :date do
transition :from => :dress, :to => :dating
...

Good point! I don't really have an answer to that yet, just a gut feeling that creating a virtual state for every little action that needs to be performed may lead to an unnecessarily complex state machine. If the work that needs to be performed is not related to the domain of the model which is being considered, it will introduce unrelated states in model's state machine.
I know from a purist pov all such logic should reside in the controller. But then you have the model domain logic creeping out into the controller where some actions may need to be performed based on the current state and the next state.
In any case, I implemented the on_transition feature for my project and here it is:

Similar to the :guard option for a transition, one can specify a :on_transition argument to the transitions call. The callback can be a symbol or a Proc. You can also specify arguments to the callback by specifying them when you fire the event. Oh, and the callback can also return a value back as shown below:

event :dress do
transitions  :from => :showering,   :to => [:working, :dating],
:on_transition => :wear_clothes
end

def wear_clothes(shirt_color, trouser_type)
self.shirt_color = shirt_color
self.trouser_type = trouser_type
id
end

%> script/console
>> p.current_state
=> :showering
>> success, ret_val = p.dress!("blue", "jeans", :next_state => :working)
=> [true, 10001]
>> p.current_state
=> :working
>> p.shirt_color
=> "blue"

The on_transition callback is called after the guard callback. The return value of the on_transition callback in no way affects the transition. It is simply considered to be a side effect of the trasition.

Oh and one more thing, the :from argument in the transitions call can be an array as well (this was part of the original acts_as_state_machine plugin). So you could do :

event :sleep do
transitions :from => [:showering, :working, :dating], :to => :sleeping, :guard => :brush_teeth
end

With the above enhancements, you can now do

event :dress do
transitions :from => [:showering, :sleeping], :to => [:working, :dating], :on_transition => :wear_clothes

Update: I forgot to mention these enhancements also include a method called next_events_for_current_state that I picked up from here, and fixed it to return the event names rather than the next states.

Note: I haven't tested this rigorously, so if you do find something amiss, drop me a note.

Njoy.

IE issue - style tag embedded in ajax response not parsed

While working on a recent project, my rails application was returning a html snippet with an embedded style tag in response to an ajax request. Firefox displayed the updated html content perfectly, but IE refused to play nice.

This issue is documented here and a solution has also been provided. I use YUI so I updated the code to use YAHOO.env.ua for browser detection.

applyStyles: function(rawHTML) {
if (YAHOO.env.ua.gecko > 0) return;
var headEl = null; // lazy-load

// find all styles in the string
var styleFragRegex = '<style[^>]*>([\u0001-\uFFFF]*?)</style>';
var matchAll = new RegExp(styleFragRegex, 'img');
var matchOne = new RegExp(styleFragRegex, 'im');
var styles = (rawHTML.match(matchAll) || []).map(function(tagMatch) {
return (tagMatch.match(matchOne) || ['', ''])[1];
});

// add all found style blocks to the HEAD element.
for (i = 0; i < styles.length; i++) {
if (!headEl) {
headEl = document.getElementsByTagName('head')[0];
if (!headEl){
return;
}
}
var newStyleEl = document.createElement('style');
newStyleEl.type = 'text/css';
if (YAHOO.env.ua.ie > 0){
newStyleEl.styleSheet.cssText = styles[i];
} else {
var cssDefinitionsEl = document.createTextNode(styles[i]);
newStyleEl.appendChild(cssDefinitionsEl);
}
headEl.appendChild(newStyleEl);
}
}

Njoy.

escape_javascript goodness

I was trying to print some javascript in application .rhtml and wanted to use a link_to helper to print a link.

var user = <%= current_user.json_user_data %>;
$('welcome_p').innerHTML = "Welcome " + user.login + '! ( <%= link_to("Sign out", session_path(), :method => :delete, :class => "wlcmLnk")) -%> )';

This resulted in an error as link_to expanded to

$('welcome_p').innerHTML = "Welcome " + ud.login + '! ( <a href="/session" class="wlcmLnk" onclick="var f = document.createElement('form'); f.style.display = 'none'; this.parentNode.appendChild(f); f.method = 'POST'; f.action = this.href;var m = document.createElement('input'); m.setAttribute('type', 'hidden'); m.setAttribute('name', '_method'); m.setAttribute('value', 'delete'); f.appendChild(m);f.submit();return false;">Sign out</a> )';

The single quotes are not escaped :(

But not to worry. escape_javascript will do the needful. It escapes carrier returns and single and double quotes.

$('welcome_p').innerHTML = "Welcome " + ud.login + '! ( <a href=\"/session\" class=\"wlcmLnk\" onclick=\"var f = document.createElement(\'form\'); f.style.display = \'none\'; this.parentNode.appendChild(f); f.method = \'POST\'; f.action = this.href;var m = document.createElement(\'input\'); m.setAttribute(\'type\', \'hidden\'); m.setAttribute(\'name\', \'_method\'); m.setAttribute(\'value\', \'delete\'); f.appendChild(m);f.submit();return false;\">Sign out</a> )';

Njoy!

Rails caching

Options :
Page caching
Action caching
Fragment Caching

Read these:
Real World Rails series
Content Caching in Rails

For fragment caching, use timed_fragment_cache which allows the cached content to be expired automatically based on a timeout.

I was thinking of allowing my users to request an explicit rebuild of the page by forcing the cache to expire before the timeout. This could be done by providing an action which will simply expire the fragment and redirect back to the original action.
However in the event that two users click on the rebuild request within say 1 minute of each other, I want the subsequent build request to be ignored automatically. I will take a stab at modifying the timed_fragment_cache plugin for doing this.