ActiveRecord associations not using bind variables?

[Nate Wiger]

Hey all, so I don't necessarily think this is an OracleEnhanced
adapter issue, but I think it may be a more fundamental problem with
ActiveRecord.

If you do something like this:

for user in @users
user.posts
end

It will cause a lazy load of posts with an IN clause:

select * from posts where posts.user_id IN (1,5,6,18,4,3)

Depending on who's in your @users object.

The problem is, according to the DBA's here, that query actually
passes in the numbers, and not bind variables. They send me
nastygrams with snippets like this:

SELECT lobby_channel_players.* FROM lobby_channel_players WHERE
(lobby_channel_players.lobby_channel_id IN
(:"SYS_B_0",:"SYS_B_1",:"SYS_B_2",:"SYS_B_3",:"SYS_B_4",:"SYS_B_5",:"SYS_B_6"))

I've tracked the code down to these files:

vendor/rails/activerecord/lib/active_record/
associations.rb
association_preload.rb

Any ideas? Can we fix this in the Oracle adapter, or is this a code
Rails issue?

Thanks,
Nate

[Raimonds Simanovskis]

ActiveRecord always generates plain SQL statements without any bind
variables as initially it was created for MySQL where as I understand
there are no bind variables.

Oracle enhanced adapter when connecting to database by default
executes
alter session set cursor_sharing = similar
which tells Oracle optimizer to always replace any literals /
constants in SQL statements with bind variables. Therefore Oracle
optimizer changes statement

select * from posts where posts.user_id IN (1,5,6,18,4,3)

to something like

select * from posts where posts.user_id IN

(:"SYS_B_0",:"SYS_B_1",:"SYS_B_2",:"SYS_B_3",:"SYS_B_4",:"SYS_B_5")

so as a result they are transformed to statements with bind variables
but there could be quite a lot of such statement if number of
constants could vary a lot.

But in general there shouldn't be any major issue with this from the
performance perspective.

SELECT statment generation as string is very deep inside ActiveRecord
and in Oracle enhanced adapter I receive it already as a string - so
it is not possible to change it otherwise to bind variables.

Raimonds

On Mar 12, 7:34 pm, Nate Wiger <nwi...@gmail.com> wrote:
> Hey all, so I don't necessarily think this is an OracleEnhanced
> adapter issue, but I think it may be a more fundamental problem with
> ActiveRecord.
>
> If you do something like this:
>
>    for user in @users
>       user.posts
>    end
>
> It will cause a lazy load of posts with an IN clause:
>
>    select * from posts where posts.user_id IN (1,5,6,18,4,3)
>
> Depending on who's in your @users object.
>
> The problem is, according to the DBA's here, that query actually
> passes in the numbers, and not bind variables.  They send me
> nastygrams with snippets like this:
>
> SELECT lobby_channel_players.* FROM lobby_channel_players     WHERE
> (lobby_channel_players.lobby_channel_id IN

> (:"SYS_B_0",:"SYS_B_1",:"SYS_B_2",:"SYS_B_3",:"SYS_B_4",:"SYS_B_5",:"SYS_B_ 6"))

[Nate Wiger]

Raimonds-

Thanks for the reply as always. Always appreciate the help.

Cursor sharing similar is a good solution when there are no other
options. The problem is our app needs a solution that makes better
native use of Oracle bind variables, as the parsing overhead is
currently causing performance problems. Do you think you and I could
brainstorm some ideas? I can devote some time to an ActiveRecord
patch if we need to make changes to AR::Base.

The problem we're hitting is that association sub-selects in
particular are currently generating variable length queries, depending
on the number of records present in the association. So, using your
example as a starting point:

select * from posts where posts.user_id IN (1,5)
select * from posts where posts.user_id IN (2,4,6)
select * from posts where posts.user_id IN (3,5,8,18)
select * from posts where posts.user_id IN (1,2,5,6,10)
select * from posts where posts.user_id IN (1,4,6,9,14,23)

As I'm sure you know, the problem is, each time the literal SQL string
is different, Oracle has to parse and create an explain plan for each
one:

select * from posts where posts.user_id IN (:"SYS_B_0",:"SYS_B_1")

select * from posts where posts.user_id IN

(:"SYS_B_0",:"SYS_B_1",:"SYS_B_2")

select * from posts where posts.user_id IN

(:"SYS_B_0",:"SYS_B_1",:"SYS_B_2",:"SYS_B_3")

select * from posts where posts.user_id IN

(:"SYS_B_0",:"SYS_B_1",:"SYS_B_2",:"SYS_B_3",:"SYS_B_4")

select * from posts where posts.user_id IN
(:"SYS_B_0",:"SYS_B_1",:"SYS_B_2",:"SYS_B_3",:"SYS_B_4",:"SYS_B_5")
select * from posts where posts.user_id IN

(:"SYS_B_0",:"SYS_B_1",:"SYS_B_2",:"SYS_B_3",:"SYS_B_4",:"SYS_B_5","SYS_B_6")

Since we have a large schema and many associations, this is currently
generating an overhead upwards of 20%.

Do you have any ideas? Maybe we could monkey-patch out some of the
SQL-generation methods in AR::Base as a proof-of-concept.

Thanks,
Nate


On Mar 12, 4:21 pm, Raimonds Simanovskis

[Raimonds Simanovskis]

Oracle will create explain plan for each SELECT with different number
of keys in IN list. But

select * from posts where posts.user_id IN (1,5)

and

select * from posts where posts.user_id IN (2,6)

both will be transformed to

select * from posts where posts.user_id IN (:"SYS_B_0",:"SYS_B_1")

and will share one explain plan.

Actually it would be the same if bind variables would be used - anyway
for each number of keys in IN list separate SQL statement would be
analyzed and explain plan would be created.

Of course Oracle is telling that bind variables are better than
cursor_sharing similar. But I think it would be interesting to test
what is actual overhead to replace constants with bind variables when
using cursor_sharing = similar. Maybe I will try to do some tests to
see what is impact of that.

If this really will be significant then it might be good to try to
experiment to make ActiveRecord to use bind variables. But this might
require quite deep hacks in ActiveRecord which would be quite hard to
maintain. Or we need to convince Rails core team that it would be good
to better support databases where bind variables are used :)

Raimonds