Query slow using positional bindings against varchar columns

[sax]

I've been finding that queries to Oracle 11g wind up very slow when using query or query! with parameters defined via positional bindings like :1. Today I found this:

https://stackoverflow.com/questions/37784388/slow-with-bind-parameters-even-slower-with-jdbctemplate#comment121142811_38078671

I think we're hitting this... the database has varchar columns, meaning that our queries are skipping indexes and scanning the table to convert each matched column to nvarchars. When we explicitly to_char(:1), our query speeds up dramatically.

Is there a possible connection configuration that would help? Maybe specify the default string parameter type as varchar?

If not hopefully we can contribute something. Just wondering if anyone already has a solution.

[vstavskyi]

In jdbc

short defaultFormOfUse = 1;
if (this.connection.defaultnchar)
  defaultFormOfUse = 2; 

In erlang https://github.com/erlangbureau/jamdb_oracle/blob/f6b64ca4dc6f339f4292283d3b5e8aa91ee06c3b/src/jamdb_oracle_tns_encoder.erl#L339-L343

To set UTF8 and FORM_CHAR, replace with this code

    (encode_sb4(?AL32UTF8_CHARSET))/binary,
    1,