Why Oracle connection to HANA works much faster with bind variables

Question

we have a problem with connection from Oracle to SAP HANA (by Oracle Gateway). SQLs works much faster when using bind variables in execute immediate than while executing standard SQLs.

F.e.:

select count (1) from SYNONYM_TO_HANA_TABLE where field='1234';

Elapsed time: 96.808 s

    declare
v_test number;
begin
execute immediate 'select count (1) from SYNONYM_TO_HANA_TABLE where field= :a'   into v_test using '1234';
dbms_output.put_line('return: ' || v_test);
end;
/

Elapsed time: 0.179 s

Basicly both SQLs returns same result but maybe someone will be able to explain why there is such a difference between execution time. Thanks!

Piotr

Answer 1

Figure out what has been done with those queries. First step can be to compare execution plans.

select /* my_query1 */  count(1) from SYNONYM_TO_HANA_TABLE ;

declare
begin
  execute immediate 'select  /* my_query2 */ count(1) from SYNONYM_TO_HANA_TABLE ';
end;
/

select sql_id,sql_text from v$sql where sql_text like '%my_quere%' ; 


select * from table(dbms_xplan.display_cursor('sql_id',0,'ALL'));