-1

Im trying to execute a query with some parameters. If I execute this sql in pl/sql developer (oracle) with literal parameters It runs in 1 second. If I execute this sql using java, jdbc driver and literal parameters It runs very fast too. For example using this code:

    String query = "select ID, (a lot of other columns)"
            + " from VCP_TIT_LIQDC"
            + " WHERE"
            + " ((123 is not null and id_tit = 123 and exists"
            + " (select 1 from sd_dual"
            + " where ((exists"
            + " (select 1"
            + " from fn_config_usr cfg"
            + " where cfg.nome_usr_bd = 'NAME' and cfg.ind_acesso_qualquer_site = 'S') or exists"
            + " (select 1"
            + " from fn_rel_usr_site rel"
            + " where rel.nome_usr_bd = 'NAME' and rel.cod_site = decode(vcp_tit_liqdc.id_empresa_lider, null, vcp_tit_liqdc.cod_site, vcp_tit_liqdc.cod_site_empresa_lider)))))) OR"
            + " (123 is null and (28 is null or"
            + " ((id_empresa = nvl(28 ,id_empresa) and id_empresa_lider is null) OR"
            + " id_empresa_lider = 28 )) AND decode (id_empresa_lider, null, cod_site, cod_site_empresa_lider) in"
            + " (select nvl (null , site.cod_site)"
            + " from fn_site site, fn_rel_usr_site rus, fn_config_usr cfg"
            + " where cfg.nome_usr_bd = 'NAME' and rus.nome_usr_bd (+) = cfg.nome_usr_bd and site.cod_site = decode (cfg.ind_acesso_qualquer_site, 'S', site.cod_site, rus.cod_site) group by site.cod_site))) AND (usr_cadastro = 'NAME' or ( id_tit in"
            + " (select ac.id_tit"
            + " from cp_titulo_acesso ac, cp_usr_acesso_janela jan"
            + " where ac.id_acesso = jan.id_acesso and 'WINDOW' = nvl(jan.nome_janela_sis, 'WINDOW' ) and jan.nome_usr_bd = 'NAME' ))) ORDER BY ID_TIT DESC";

    PreparedStatement p = connection.prepareStatement(query);
    ResultSet rs = p.executeQuery();

But if I set the parameters using "?" (binding) It takes 10 minutes or more to run. For example in code:

    String query = "select ID, (a lot of other columns)"
            + " from VCP_TIT_LIQDC"
            + " WHERE"
            + " ((? is not null and id_tit = ? and exists"
            + " (select 1 from sd_dual"
            + " where ((exists"
            + " (select 1"
            + " from fn_config_usr cfg"
            + " where cfg.nome_usr_bd = ? and cfg.ind_acesso_qualquer_site = 'S') or exists"
            + " (select 1"
            + " from fn_rel_usr_site rel"
            + " where rel.nome_usr_bd = ? and rel.cod_site = decode(vcp_tit_liqdc.id_empresa_lider, null, vcp_tit_liqdc.cod_site, vcp_tit_liqdc.cod_site_empresa_lider)))))) OR"
            + " (? is null and (? is null or"
            + " ((id_empresa = nvl(? ,id_empresa) and id_empresa_lider is null) OR"
            + " id_empresa_lider = ? )) AND decode (id_empresa_lider, null, cod_site, cod_site_empresa_lider) in"
            + " (select nvl (? , site.cod_site)"
            + " from fn_site site, fn_rel_usr_site rus, fn_config_usr cfg"
            + " where cfg.nome_usr_bd = ? and rus.nome_usr_bd (+) = cfg.nome_usr_bd and site.cod_site = decode (cfg.ind_acesso_qualquer_site, 'S', site.cod_site, rus.cod_site) group by site.cod_site))) AND (usr_cadastro = ? or ( id_tit in"
            + " (select ac.id_tit"
            + " from cp_titulo_acesso ac, cp_usr_acesso_janela jan"
            + " where ac.id_acesso = jan.id_acesso and ? = nvl(jan.nome_janela_sis, ? ) and jan.nome_usr_bd = ? ))) ORDER BY ID_TIT DESC";

    PreparedStatement p = connection.prepareStatement(query);

    p.setInt(1, 123);
    p.setInt(2, 123);
    p.setString(3, "NAME");
    p.setString(4, "NAME");
    p.setInt(5, 123);
    p.setInt(6, 28);
    p.setInt(7, 28);
    p.setInt(8, 28);
    p.setString(9, null);
    p.setString(10, "NAME");
    p.setString(11, "NAME");
    p.setString(12, "WINDOW");
    p.setString(13, "WINDOW");
    p.setString(14, "NAME");

    ResultSet rs = p.executeQuery();

Is there any way to resolve this problem? Because the sql is the same in each case.

I am using 10g database and tested this versions of jdbc: 10.2.0.1.0, 11.2.0.2.0 and 12.1.0.2.0.

Computered
  • 440
  • 1
  • 7
  • 21
  • 4
    The performance difference almost certainly has nothing to do with JDBC, but rather everything to do with the fact that the Oracle optimizer has far less information to work with than it does when you give it literals. To follow best practices, you do NOT need to replace every literal with a bind variable. You only need to replace the literals that will change from one execution to another. "NAME", "WINDOW", etc look like they may be the same in every query run. Start by trying to bind only the literals that change at runtime and see if that helps. It may or may not. – Matthew McPeak Nov 10 '16 at 17:27
  • I need to set this variables in runtime, because this variables came from the user. – Computered Nov 10 '16 at 17:45
  • Since some of those string parameters are used in WHERE clauses, I'd be inclined to investigate whether the parameter values are being passed in such a way that the actual execution plan is unable to use any available indexes and is forced to do one or more table scans. Some of the information [here](http://docs.oracle.com/cd/B19306_01/server.102/b14225/ch7progrunicode.htm#i1006858) might help determine whether that is the case. – Gord Thompson Nov 10 '16 at 17:59
  • 2
    Sounds like you might be suffering from [bind variable peeking.](http://kerryosborne.oracle-guy.com/2009/03/bind-variable-peeking-drives-me-nuts/), and have an execution plan set from unfortunate initial bind values. (This is improved in 11g database - hot JDBC - , which doesn't help you). [Tom Kyte has a story about this](http://tkyte.blogspot.co.uk/2012/05/another-debugging-story.html). Might be worth checking your stats are up to date. – Alex Poole Nov 10 '16 at 18:40
  • When you prepare a statement there is a significant amount of processing that is a one time cost to parse and come up with the execution. This happens only the first time you execute the statement. When you re-execute the query with different bind values, does it take the same time? – Jean de Lavarene Nov 11 '16 at 11:08

2 Answers2

1

The query containing static values has predicates such as "123 is null and (28 is null" With static values the optimizer knows those conditions are never true so it can simply ignore them. As an analogy, if you were told to go to the shop and buy a tin of striped paint, you can immediately see there's no point in going because there's no such thing.

When they are actual variables there is the potential that they can be null so there is a whole bunch of extra work that needs doing.

You are probably best off testing some of those values for null-ness in the Java code and constructing a simpler query to execute.

Gary Myers
  • 34,963
  • 3
  • 49
  • 74
  • Hi Gary, to check it we have tested using binding in a sql script (without java). For example using this kind of code (:cod_tit is null and ...). And this script runs very fast. Apparently the problema occurs using java and jdbc. – Computered Nov 14 '16 at 18:09
0

There's an interesting optimisation in Oracle when you're using x = NVL(?, x) as opposed to ? is null or x = ?, see this article by Connor McDonald: https://connor-mcdonald.com/2018/02/13/nvl-vs-coalesce

In many cases, this distinction may not be a real problem, but in your case, the query is quite complex and thus the optimiser may simply fail to optimise this "correctly".

I suggest you write a dynamic SQL query where the SQL string and bind variables are different depending on the execution use case. This can be done very easily using SQL builders like jOOQ (disclaimer: I work for the vendor), or other SQL builders.

If you prefer using static SQL, you could still design a few distinct SQL queries that do not depend on bind values being null to work around this issue.

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509