Adaptive Cursor Sharing with ActiveRecord

Oracle 11g introduced a new feature called Adaptive Cursor Sharing.

The adaptive cursor sharing feature enables a single statement that contains bind variables to use multiple execution plans. Cursor sharing is "adaptive" because the cursor adapts its behavior so that the database does not always use the same plan for each execution or bind variable value.

ActiveRecord also implements prepared statements for simple find methods.
In addition to that Oracle enhanced adapter sets cursor_sharing = force to change sql statement literal values to bind ones whenever possible, which enables better cursor sharing and to choose better sql execution plans based on each bind values.

This article explains how Adaptive Cursor Sharing works with Oracle enhanced adapter.

Steps

  • Create a rails app named acs_app using -d oracle option
$ rails new acs_app -d oracle
  • Edit acs_app/Gemfile then add following gem entries
gem "activerecord-oracle_enhanced-adapter", "~> 1.5.0"
gem 'arel', github: 'yahonda/arel', branch: 'sandbox_limit'  
  • Edit acs_app/config/database.yml. Do not forget to create acs_app database user
default: &default
  adapter: oracle_enhanced
  username: acs_app
  password: acs_app

development:
  <<: *default
  database: ORCL
  • Bundle install to install both gems
$ cd acs_app
$ bundle install
  • Generate a model named User then migrate
$ rails generate model User database:string
$ rake db:migrate
  • Startup dbconsole to login Oracle database
$ rails dbconsole
  • Generate dummy data, it would mean only one uses "Oracle", 50,000 users uses "PostgreSQL", then remaining 50,000 ones are using something else.

SQL> set echo on
SQL> insert into users values (users_seq.nextval,'Oracle',sysdate,sysdate);

1 row created.

SQL>
SQL> begin
  2    for i in 1..50000 loop
  3      insert into users values (users_seq.nextval,dbms_random.string('X',10),sysdate,sysdate);
  4    end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> begin
  2    for i in 1..50000 loop
  3      insert into users values (users_seq.nextval,'PostgreSQL',sysdate,sysdate);
  4    end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> exit
  • Enable events 10046 level 4 to generate sql trace file with bind variable
$ sqlplus / as sysdba
SQL> ALTER SYSTEM SET EVENTS '10046 trace name context forever, level 4';
System altered.

SQL> exit
  • Add migration to add_index for database column in users.
$ rails generate migration AddIndexToUser
class AddIndexToUser < ActiveRecord::Migration
  def change
    add_index :users, :database
  end
end
  • Let's migrate to add index
$ rake db:migrate
  • Execute dbms_stats.gather_table_stats to let Oracle know the exact statistics of Users table with histograms
$ rails dbconsole
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'USERS', method_opt=>'for all indexed columns size skewonly', cascade=>TRUE);

PL/SQL procedure successfully completed.

SQL> exit
  • Startup rails console and find the last.id who uses Oracle database.
$ rails console
Loading development environment (Rails 4.1.5)
2.1.2 :001 > User.where(:database => "Oracle").last.id
... snip ...  User Load (148.3ms)  SELECT  "USERS".* FROM "USERS"  WHERE "USERS"."DATABASE" = 'Oracle'  ORDER BY "USERS"."ID" DESC FETCH FIRST 1 ROWS ONLY
 => 10000
2.1.2 :002 > exit
  • Format the trace file into human-readable one by using tkprof
$ tkprof ORCL_ora_31887.trc ORCL_ora_31887.prf sys=no
  • Open the ORCL_ora_31961.prf file and go to the bottom.
    You can find the sql statement changed from WHERE "USERS"."DATABASE" = 'Oracle' to WHERE "USERS"."DATABASE" = :"SYS_B_0" and FETCH FIRST 1 ROWS ONLY to FETCH FIRST :"SYS_B_1" ROWS ONLY. Both literal values 'Oracle' and 1 has been replaced to :"SYS_B_0" and :"SYS_B_1

SQL ID: 1a9nx4a2kypwf Plan Hash: 3139782547

SELECT  "USERS".*
FROM
 "USERS"  WHERE "USERS"."DATABASE" = :"SYS_B_0"  ORDER BY "USERS"."ID" DESC
  FETCH FIRST :"SYS_B_1" ROWS ONLY


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.05          0          0          0           0
Execute      1      0.00       0.04          0          0          0           0
Fetch        1      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.10          0          3          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 152
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  VIEW  (cr=3 pr=0 pw=0 time=8132 us cost=3 size=569 card=1)
         1          1          1   WINDOW SORT PUSHED RANK (cr=3 pr=0 pw=0 time=8123 us cost=3 size=32 card=1)
         1          1          1    TABLE ACCESS BY INDEX ROWID BATCHED USERS (cr=3 pr=0 pw=0 time=61 us cost=2 size=32 card=1)
         1          1          1     INDEX RANGE SCAN INDEX_USERS_ON_DATABASE (cr=2 pr=0 pw=0 time=41 us cost=1 size=0 card=1)(object id 362392)

Here INDEX RANGE SCAN using INDEX_USERS_ON_DATABASE. Yes this plan is suitable because it chooses only 1 record from 100,001 records.

Let's find last.id who uses PostgreSQL.

$ rails console
Loading development environment (Rails 4.1.5)
2.1.2 :001 > User.where(:database => "PostgreSQL").last.id
... snip ...
  User Load (67.9ms)  SELECT  "USERS".* FROM "USERS"  WHERE "USERS"."DATABASE" = 'PostgreSQL'  ORDER BY "USERS"."ID" DESC FETCH FIRST 1 ROWS ONLY
 => 110000
2.1.2 :002 > exit
  • Format the trace file into human-readable one by using tkprof
$ tkprof ORCL_ora_31961.trc ORCL_ora_31961.prf sys=no
  • Open the ORCL_ora_31961.prf file and go to the bottom.
    You can find the sql statement changed also.

SQL ID: 1a9nx4a2kypwf Plan Hash: 2169198979

SELECT  "USERS".*
FROM
 "USERS"  WHERE "USERS"."DATABASE" = :"SYS_B_0"  ORDER BY "USERS"."ID" DESC
  FETCH FIRST :"SYS_B_1" ROWS ONLY


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.05       0.05          0        569          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.05       0.05          0        569          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 152
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  VIEW  (cr=569 pr=0 pw=0 time=52204 us cost=610 size=28322544 card=49776)
         1          1          1   WINDOW SORT PUSHED RANK (cr=569 pr=0 pw=0 time=52197 us cost=610 size=1592832 card=49776)
     50000      50000      50000    TABLE ACCESS FULL USERS (cr=569 pr=0 pw=0 time=54714 us cost=172 size=1592832 card=49776)

Here TABLE ACCESS FULL USERS chosen. Yes this plan is suitable because it chooses 50,000 rows from 100,001 records.

Conclusion

You can find SQL_ID value between both test cases are 1a9nx4a2kypwf, which means by setting cursor_sharing = exact Oracle handles these two sql statements are the "same" sql statement. Then Plan Hash are unique 3139782547 2169198979 Oracle generates different execution plan for the same sql statment based on bind parameter values.

Reference

http://www.oracle-base.com/articles/11g/adaptive-cursor-sharing-11gr1.php

http://blog.rayapps.com/2009/09/28/new-features-in-activerecord-oracle-enhanced-adapter-version-1-2-2/