Relation.explain are not cursor_sharing = force aware

In the previous article SQL execution plans called "Row Source Operation" are captured from 10046 trace files.

Many of you should know "ActiveRecord has a feature to get SQL execution plan by using Relation.explain, why not that method?"

Here is the answer:

Relation.explain method executes EXPLAIN for statement for Oracle database. Unfortunately it looks EXPLAIN for statement are not cursor_shaing = force aware.

Here are examples:

  • User.where(:database => "Oracle").explain
004 > User.where(:database => "Oracle").explain
  User Load (5.1ms)  SELECT "USERS".* FROM "USERS"  WHERE "USERS"."DATABASE" = 'Oracle'
 => EXPLAIN for: SELECT "USERS".* FROM "USERS"  WHERE "USERS"."DATABASE" = 'Oracle'
Plan hash value: 2733404355

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                         |     1 |    32 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| USERS                   |     1 |    32 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | INDEX_USERS_ON_DATABASE |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("USERS"."DATABASE"='Oracle')
  • User.where(:database => "PostgreSQL").explain
2.1.2 :005 > User.where(:database => "PostgreSQL").explain
  User Load (6091.1ms)  SELECT "USERS".* FROM "USERS"  WHERE "USERS"."DATABASE" = 'PostgreSQL'
 => EXPLAIN for: SELECT "USERS".* FROM "USERS"  WHERE "USERS"."DATABASE" = 'PostgreSQL'
Plan hash value: 3461732445

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       | 49776 |  1555K|   172   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| USERS | 49776 |  1555K|   172   (2)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("USERS"."DATABASE"='PostgreSQL')
2.1.2 :006 >

If there is any good way to support Relation.explain are cursor_sharing = force aware. Pull requests are very welcomed!