Copying Data From A Ruby On Rails Session Into An Oracle Session

2008-04-27 Dan Bikle

Motivation

An Oracle session contains information about a current connection to the database.

Three useful features of Oracle can be tied to an Oracle session:

  • Historical reporting of session behavior (Auditing and Performance Tuning)
  • Attach resource constraints to active sessions
  • Attach security constraints to active sessions

Oracle Sessions vs. RoR Sessions

Oracle Session Documentation:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/process.htm#sthref1481

An Oracle session, in some ways, is similar to an an RoR session.

A Ruby On Rails (RoR) session is an object which keeps track of a connection to an RoR website.

RoR Session Documentation:

A typical data value found in an RoR session is the login-name of the user who initiated the connection. Of additional interest to us is the name of the RoR-controller and the last called method in that controller.

These three data values can be copied to the corresponding Oracle session.

RoR Controllers

If you are unfamiliar with RoR, a method in an RoR-controller can serve as an entry point to the Ruby code resident within an RoR application.

Most of the business logic within an RoR site lies within the methods of the controller classes of that site.

RoR-Controller Documentation:

http://api.rubyonrails.com/classes/ActionController/Base.html

For example the URL,

http://hpricot.com/demos

corresponds to a controller class named "DemosController".

If you navigate to the above URL you may both interact with the methods in that controller and see the Ruby syntax which drives the controller. The methods in that controller have names like:

  • search
  • search_remove
  • search_search
  • at
  • innerhtml

and so on

Another way to see the Ruby syntax behind the controller methods is via RDoc:

http://hpricot.com/app/classes/DemosController.html

If you spend some time interacting with the demos at hpricot.com, take some time to study your URL history of that site. Then study the Ruby syntax behind the controller methods and determine how the URLs map to controller functionality.

Motivation 1: Historical Reporting Of Oracle Session Behavior

Historical reporting of Oracle session behavior is often the first step for any Oracle DBA tasked with performance tuning the database. Aside from performance tuning, the DBA might depend on historical reporting of Oracle session behavior for auditing and general information about growth trends. But generally if a DBA is looking at historical Oracle session behavior he is doing it as part of a performance tuning effort.

Oracle provides a sophisticated mechanism for constraining resource consumption by Oracle sessions.

It is called the "Resource Manager":

http://www.google.com/search?q=oracle+resource+manager

The Resource Manager looks at attributes in each Oracle session. Next, it tries to match them to resource constraint policies established by the DBA.

If a match is found, then resources such as CPU and I/O are rationed to the matched Oracle session.

If a match is not found, the Oracle session is free to consume all the resources it can pull from the Operating System.

Here is an example policy statement which illustrates the value of the Resource Manager to a book selling site:

  • A user with books in his cart and is "checking-out" should not be constrained
  • A user with books in his cart should get more resources than a user with no books in his cart
  • A user with no books in his cart should get more resources than a bot which is crawling the site
  • A bot crawling the site should get more resources than batch programs which create BI reports.

Motivation 2: Security constraints applied to active Oracle sessions

Oracle provides functionality to restrict data access based on Oracle session attributes. This is useful for situations where permissions need to change at run-time. It's a large topic which can be studied via a careful reading about "Oracle VPD":

http://www.google.com/search?q=oracle+vpd

So, any RoR developer who knows how to copy RoR-session values to the corresponding Oracle session, will then be able to access three large areas of Oracle functionality:

  • Performance Tuning combined with end-to-end tracing
  • Policy based resource constraints
  • Run-time configurable security constraints

Adding attributes to an Oracle session via sqlplus

Oracle provides us a tool named sqlplus which can be used to both craft and submit SQL statements to the database.

Sqlplus Documentation:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14356/toc.htm

It's a powerful command line tool; demonstrating it's use is simple.

Sqlplus allows us to describe the columns of the view v$session: 



502 bash-03:19:47-dbiklelt:~ $
502 bash-03:19:48-dbiklelt:~ $
502 bash-03:19:48-dbiklelt:~ $
502 bash-03:19:49-dbiklelt:~ $ sqlplus system/manager

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Apr 27 03:19:57 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

03:19:57 orcl SQL >
03:20:05 orcl SQL >
03:20:05 orcl SQL > desc v$session
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SADDR                                              RAW(4)
 SID                                                NUMBER
 SERIAL#                                            NUMBER
 AUDSID                                             NUMBER
 PADDR                                              RAW(4)
 USER#                                              NUMBER
 USERNAME                                           VARCHAR2(30)
 COMMAND                                            NUMBER
 OWNERID                                            NUMBER
 TADDR                                              VARCHAR2(8)
 LOCKWAIT                                           VARCHAR2(8)
 STATUS                                             VARCHAR2(8)
 SERVER                                             VARCHAR2(9)
 SCHEMA#                                            NUMBER
 SCHEMANAME                                         VARCHAR2(30)
 OSUSER                                             VARCHAR2(30)
 PROCESS                                            VARCHAR2(12)
 MACHINE                                            VARCHAR2(64)
 TERMINAL                                           VARCHAR2(16)
 PROGRAM                                            VARCHAR2(64)
 TYPE                                               VARCHAR2(10)
 SQL_ADDRESS                                        RAW(4)
 SQL_HASH_VALUE                                     NUMBER
 SQL_ID                                             VARCHAR2(13)
 SQL_CHILD_NUMBER                                   NUMBER
 PREV_SQL_ADDR                                      RAW(4)
 PREV_HASH_VALUE                                    NUMBER
 PREV_SQL_ID                                        VARCHAR2(13)
 PREV_CHILD_NUMBER                                  NUMBER
 MODULE                                             VARCHAR2(48)
 MODULE_HASH                                        NUMBER
 ACTION                                             VARCHAR2(32)
 ACTION_HASH                                        NUMBER
 CLIENT_INFO                                        VARCHAR2(64)
 FIXED_TABLE_SEQUENCE                               NUMBER
 ROW_WAIT_OBJ#                                      NUMBER
 ROW_WAIT_FILE#                                     NUMBER
 ROW_WAIT_BLOCK#                                    NUMBER
 ROW_WAIT_ROW#                                      NUMBER
 LOGON_TIME                                         DATE
 LAST_CALL_ET                                       NUMBER
 PDML_ENABLED                                       VARCHAR2(3)
 FAILOVER_TYPE                                      VARCHAR2(13)
 FAILOVER_METHOD                                    VARCHAR2(10)
 FAILED_OVER                                        VARCHAR2(3)
 RESOURCE_CONSUMER_GROUP                            VARCHAR2(32)
 PDML_STATUS                                        VARCHAR2(8)
 PDDL_STATUS                                        VARCHAR2(8)
 PQ_STATUS                                          VARCHAR2(8)
 CURRENT_QUEUE_DURATION                             NUMBER
 CLIENT_IDENTIFIER                                  VARCHAR2(64)
 BLOCKING_SESSION_STATUS                            VARCHAR2(11)
 BLOCKING_INSTANCE                                  NUMBER
 BLOCKING_SESSION                                   NUMBER
 SEQ#                                               NUMBER
 EVENT#                                             NUMBER
 EVENT                                              VARCHAR2(64)
 P1TEXT                                             VARCHAR2(64)
 P1                                                 NUMBER
 P1RAW                                              RAW(4)
 P2TEXT                                             VARCHAR2(64)
 P2                                                 NUMBER
 P2RAW                                              RAW(4)
 P3TEXT                                             VARCHAR2(64)
 P3                                                 NUMBER
 P3RAW                                              RAW(4)
 WAIT_CLASS_ID                                      NUMBER
 WAIT_CLASS#                                        NUMBER
 WAIT_CLASS                                         VARCHAR2(64)
 WAIT_TIME                                          NUMBER
 SECONDS_IN_WAIT                                    NUMBER
 STATE                                              VARCHAR2(19)
 SERVICE_NAME                                       VARCHAR2(64)
 SQL_TRACE                                          VARCHAR2(8)
 SQL_TRACE_WAITS                                    VARCHAR2(5)
 SQL_TRACE_BINDS                                    VARCHAR2(5)

03:20:10 orcl SQL >
03:20:11 orcl SQL >
03:20:11 orcl SQL >

v$session Documentation:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2088.htm#i1414383

Each Oracle session has a row in v$session.

The columns we are interested in are listed below: 


 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERNAME                                           VARCHAR2(30)
 MODULE                                             VARCHAR2(48)
 ACTION                                             VARCHAR2(32)
 CLIENT_INFO                                        VARCHAR2(64)
 CLIENT_IDENTIFIER                                  VARCHAR2(64)

The USERNAME column corresponds to the username field in the RoR database.yml file.

When RoR connects to Oracle it depends on database.yml and when the connection is made, the USERNAME column is filled for us (by the Oracle kernel).

RoR Database Connection Information:

http://api.rubyonrails.com/classes/ActiveRecord/Base.html#M001467 http://www.google.com/search?q=rails+database.yml

This means that if you have several RoR sites connecting to one Oracle database, then you can group the Oracle sessions for each RoR site via USERNAME.

The demo SQL query below illustrates this point by giving us a count of active connections to the database active ROR sites: 


  SELECT COUNT(USERNAME), USERNAME FROM v$session GROUP BY USERNAME;

The MODULE column is also useful. It is well suited for holding the name of the RoR-controller associated with this session.

The ACTION column is well suited for holding the name of the current method inside the RoR-controller associated with this session.

The CLIENT_INFO is well suited for holding any piece of information from the RoR-session. For example the number of books in the shopping cart might be useful.

The CLIENT_IDENTIFIER is well suited for holding information about the end-user such as login-name. If you collect login information into CLIENT_IDENTIFIER, then you will be able to identify all the SQL for that end user (since we can easily query information about all SQL statements generated by a given Oracle session).

For example if that user is complaining that the site is slow, you should be able to quickly identify the SQL statements consuming most of the resources for that user. This is a powerful technique.

Most webmasters of PHP or JDBC connected sites lack the ability to do this. Often they need to load log files into a table and then run SQL against that table.

Another sub-optimal approach webmasters use is correlation based.

Consider this line of thinking based on correlation logic, "Last night we deployed a new release of the site, today the site is slow. Therefore we must have a performance bug in the new code resident within the new release."

An optimal approach to performance tuning is based on end-to-end tracing. This is possible if the webmaster knows which controller-methods are tied to which Oracle sessions (which can then lead to specific resource intensive SQL statements).

A Sqlplus Demonstration

Using sqlplus to create a session and then add data values to that session is easily demonstrated: 



502 bash-03:48:38-dbiklelt:~ $ sqlplus app10/a

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Apr 27 03:48:53 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

03:49:34 orcl SQL > SELECT username , module from v$session WHERE username='APP10';

USERNAME                       MODULE
------------------------------ ------------------------------------------------
APP10                          ruby.exe
APP10                          SQL*Plus

03:51:26 orcl SQL > EXEC dbms_session.set_identifier(client_id => 'bikle@bikle.com')

PL/SQL procedure successfully completed.

03:51:37 orcl SQL > SET lines 222 pages 33
03:52:27 orcl SQL > SELECT username, module, client_identifier FROM v$session WHERE username='APP10';

USERNAME                       MODULE                                           CLIENT_IDENTIFIER
------------------------------ ------------------------------------------------ ----------------------
APP10                          ruby.exe
APP10                          SQL*Plus                                         bikle@bikle.com

03:52:52 orcl SQL > EXEC dbms_application_info.set_module(module_name => 'sqlplus!!!', action_name => 'fake action')

PL/SQL procedure successfully completed.

03:55:21 orcl SQL > SELECT username, module, action, client_identifier FROM v$session WHERE username='APP10';

USERNAME                       MODULE                                           ACTION                           CLIENT_IDENTIFIER
------------------------------ ------------------------------------------------ -------------------------------- ----------------------
APP10                          ruby.exe
APP10                          sqlplus!!!                                       fake action                      bikle@bikle.com

03:56:16 orcl SQL >
03:56:39 orcl SQL >
03:56:55 orcl SQL >

So, the above demonstration shows how to create a session, set attributes in it and then get the attribute values via a simple SELECT statement against v$session.

Notice that sqlplus has the useful ability to call PL/SQL procedures using the 'EXEC' command.

RoR also has the ability to call PL/SQL procedures but it's less graceful.

The way it's done is to obtain the raw_connection object from ActiveRecord::Base and then make use of its .parse() and .exec() methods: 


class C10Controller < ApplicationController
  def m10
    conn = ActiveRecord::Base.connection.raw_connection
    proc = conn.parse("BEGIN dbms_session.set_identifier(client_id => 'bikle@bikle.com'); END;")
    proc.exec
    @m10 = "hello world"
  end
end

proc.exec Documentation:

http://ruby-oci8.rubyforge.org/en/api_OCI8.html#l4

Some developers want to leave this type of syntax in the RoR site's code base where it can be managed by source control like SVN or CVS.

I, however, prefer move as much of this type of syntax out of RoR into Oracle. I see it as being more DRY; it is easier to share. Also, if the syntax resides in Oracle, I can rely on ActiveRecord::Base.find_by_sql() to call it which seems more generic and less likely to fall-over after a Rails or OCI8 upgrade.

We can do this by wrapping multiple PL/SQL calls into an SQL function.

SQL Function Documentation:

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/overview.htm#sthref196

Once the function is built, we can call it from an SQL statement.

And calling an SQL statement from RoR is easy: 


  xyz = ActiveRecord::Base.find_by_sql ["SELECT my_function(?) FROM dual", some_variable]

ActiveRecord::Base.find_by_sql Documentation:

http://api.rubyonrails.com/classes/ActiveRecord/Base.html#M001377

The next section describes how to build a function which wraps multiple PL/SQL calls.

Wrapping procedure calls with a function

To create a function we place the function creation syntax in a .sql file and then call that file from sqlplus.

Here is the syntax: 



-- session_setter12.sql
-- Run via sqlplus user/pass @session_setter12.sql
CREATE OR REPLACE FUNCTION session_setter12(
  client_id_in    VARCHAR2
  ,client_info_in VARCHAR2
  ,module_name_in VARCHAR2
  ,action_name_in VARCHAR2
) RETURN NUMBER IS
BEGIN
  dbms_session.set_identifier(client_id => client_id_in);
  dbms_application_info.set_client_info(client_info => client_info_in);
  dbms_application_info.set_module(module_name => module_name_in, action_name => action_name_in);
  RETURN 1;
END;
/
SHOW ERRORS

dbms_session.set_identifier Documentation:

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_sessio.htm#i996935

dbms_application_info Documentation:

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_appinf.htm#CHECEIEB

After you create the above function in Oracle, you use this syntax to call the function to set the session values: 


SELECT session_setter12 ('sqlplus@bikle.com', 'sqlplus cart', 'sqlplus', 'nada') FROM dual;

Here is a demo screen dump showing how to get the values out of v$session: 


  1* SELECT username, module, action, client_info, client_identifier FROM v$session WHERE username='APP10'
04:22:39 orcl SQL > /

USERNAME    MODULE        ACTION                           CLIENT_INFO            CLIENT_IDENTIFIER
----------- ------------- -------------------------------- ---------------------- ----------------------------------------------------------------
APP10       sqlplus       nada                             sqlplus cart           sqlplus@bikle.com

04:22:40 orcl SQL >
04:22:55 orcl SQL >

So, calling the function from sqlplus is easy; how about an RoR-controller?

Some demonstration controller syntax is displayed below (notice the use of the handy "self" object): 


class C10Controller < ApplicationController

  # Trigger login via this URL: http://bikle.com/c10/login
  def login
    # Put some data in the session
    session[:login_name] = 'bikle@bikle.com'
    session[:cart_state] = 'cart empty'
    @login = "You are now logged in."
  end

  # Trigger m11 via this URL: http://bikle.com/c10/m11
  def m11
    themodule = self.class.to_s
    theaction = self.action_name
    theclient_info = session[:cart_state]
    the_client_id = session[:login_name]
    # Copy RoR session data to the corresponding Oracle session
    xyz = ActiveRecord::Base.find_by_sql ["SELECT session_setter12(?,?,?,?) FROM dual" , the_client_id, theclient_info,themodule, theaction]
    @m11 = "hello world"
  end
end
And we look in v$session for data from RoR variables: session[:login_name] and session[:cart_state]: 


04:58:16 orcl SQL > l
  1* select username, module, action, client_info, client_identifier from v$session where username='APP10'
05:03:30 orcl SQL > /

USERNAME    MODULE        ACTION                           CLIENT_INFO CLIENT_IDENTIFIER
----------- ------------- -------------------------------- ----------- ------------------------------------
APP10       C10Controller m11                              cart empty  bikle@bikle.com

05:03:31 orcl SQL >

RoR provides a feature which allows us to centralize RoR code which we want called from all or many controller methods. This feature is called "Filters".

Filters Documentation:

http://api.rubyonrails.com/classes/ActionController/Filters/ClassMethods.html

Demonstration RoR-controller syntax with an "after_filter" is displayed below: 


class C10Controller < ApplicationController
  after_filter :copy_data2oracle_session

  # Trigger login via this URL: http://bikle.com/c10/login
  def login
    session[:login_name] = 'bikle@bikle.com'
    session[:cart_state] = 'cart empty'
    @login = "You are now logged in."
  end

  # Trigger m11 via this URL: http://bikle.com/c10/m11
  def m11
    @m11 = "hello world"
  end

  private
  # Send some data about the RoR session to the corresponding Oracle session
  def copy_data2oracle_session
    themodule = self.class.to_s
    theaction = self.action_name
    theclient_info = session[:cart_state]
    the_client_id = session[:login_name]
    xyz = ActiveRecord::Base.find_by_sql ["SELECT session_setter12(?,?,?,?) FROM dual" , the_client_id, theclient_info,themodule, theaction]
  end
end

So when m11 (or any other public method in the controller) is called, copy_data2oracle_session is also called (afterwards).

Filters are an embodiment of the DRY principal. DRY is an acronym for "Don't Repeat Yourself":

http://www.google.com/search?q=rails+dry

Summary

This paper discussed implementation of the simple idea of copying data from an RoR session into the corresponding Oracle session.

The core of the technique is making calls to these Oracle supplied packaged procedures from an RoR-controller: 


  dbms_session.set_identifier
  dbms_application_info.set_client_info
  dbms_application_info.set_module

We may call the Oracle supplied packaged procedures directly via the OCI8 API or we may call them using a custom built Oracle function which is accessed by ActiveRecord::Base.find_by_sql