Oracle JDBC preparedStatement bad performance with string parameter

This is just a guess, but a good one (I think), and too long for a comment.

I suspect there is some kind of character set conversion issue happening that is causing the Oracle database to interpret your bind value as an NVARCHAR2. The resulting implicit type conversion then prevents Oracle from making use of your index.

Here is a quick example of what I mean:

Set up

CREATE TABLE matt1 ( a varchar2(30) );

INSERT INTO matt1  SELECT dbms_random.string('X',20) FROM   dual CONNECT BY ROWNUM <= 50000;

COMMIT;

CREATE INDEX matt1_n1 ON matt1 (a);

Get a sample value

SELECT * FROM matt1 order by dbms_random.value fetch first 1 row only;

I got "UCBBTRAB0K8QV1UC8ERA" -- you'll get a different value if you are trying this in your own database.

Simulate what you are doing through SQL*Developer:

EXPLAIN PLAN SET STATEMENT_ID='MM1' FOR
SELECT * FROM matt1 WHERE a = 'UCBBTRAB0K8QV1UC8ERA';

SELECT * 
FROM   TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','MM1','ADVANCED'));

Plan hash value: 2474448389

----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time    |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |    17 |     1   (0)| 00:00:01|
|*  1 |  INDEX RANGE SCAN| MATT1_N1 |     1 |    17 |     1   (0)| 00:00:01|
----------------------------------------------------------------------------

So far, so good. The index is being used.

Simulate what I think is happening via JDBC

EXPLAIN PLAN SET STATEMENT_ID='MM2' FOR SELECT * FROM matt1 WHERE a =
N'UCBBTRAB0K8QV1UC8ERA';

SELECT *  FROM  
TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','MM2','ADVANCED'));

Plan hash value: 1348340248

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     3 |    51 |    50  (10)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| MATT1 |     3 |    51 |    50  (10)| 00:00:01 |
---------------------------------------------------------------------------

... lots of stuff omitted...

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

   1 - filter(SYS_OP_C2C("A")=U'UCBBTRAB0K8QV1UC8ERA')

You can see that Oracle is doing an implicit type conversion on that database column. That type conversion function is preventing Oracle from using the index and resulting in a full table scan.

How you can test this

To confirm whether this is truly your problem, modify your JDBC SQL to include a distinctive comment. E.g.,

String consultaSQL = "SELECT /* THIS_IS_MY_JDBC_STATEMENT_1 */ SE.ID_SAIDA_ESTOQUE, SE.DATA_ULTIMA_ATUALIZACAO,  "
                + "SE.SITUACAO_VENDA, SE.ID_CLIENTE, SE.ID_ENTRADA_ESTOQUE_TROCA, SE.UUID, SE.ID_OPERACAO_MOVIMENTO, SE.ID_SETOR_ESTOQUE, SE.ID_EMPRESA, SE.ID_TERMINAL "
                + "FROM EST_SAIDA_ESTOQUE SE WHERE SE.UUID = ?"    

Then, run your JDBC program and look in the database to see what Oracle did with it. First, find your executed statement in the library cache, like this:

select sql_id, child_number from gv$sql where sql_text like 
'%THIS_IS_MY_JDBC_STATEMENT_1%' and sql_text not like '%THIS_ONE%';

Then, use the sql_id and child_number to view the plan.

SELECT *
FROM   TABLE (DBMS_XPLAN.display_cursor ('gyzm0fq259h5d' /* sql_id */,
                                         0 /* child_number */,
                                         'ADVANCED LAST'));

If the plan indicates a full table scan and the predicate information has a SYS_OP_C2C (or similar) function in it, then you have your explanation.

What you can do about it

Easiest way that should work:

Change your JDBC SQL to this:

String consultaSQL = "SELECT /* THIS_IS_MY_JDBC_STATEMENT_1 */ SE.ID_SAIDA_ESTOQUE, SE.DATA_ULTIMA_ATUALIZACAO,  "
                + "SE.SITUACAO_VENDA, SE.ID_CLIENTE, SE.ID_ENTRADA_ESTOQUE_TROCA, SE.UUID, SE.ID_OPERACAO_MOVIMENTO, SE.ID_SETOR_ESTOQUE, SE.ID_EMPRESA, SE.ID_TERMINAL "
                + "FROM EST_SAIDA_ESTOQUE SE WHERE SE.UUID = CAST(? AS VARCHAR2(255 CHAR))"    

(From OP: Explain Plan)

SqlDeveloper JDBC