Why oracle stored procedure execution time is greatly increased depending on how it is executed?

Question

This is my problem: we have a stored procedure called HEAVY_SP and depending on how it is executed, execution time is greatly increased:

(1) Call execution

Direct execution in Oracle SQL Developer IDE

CALL HEAVY_SP(0, 'F', 5, ...)

takes 15 seconds (our current solution)

(2) Using play button

Opening the procedure using Oracle SQL Developer and executing the "play" button:

takes 15 seconds

(3) dbms_job : scheduled mode

takes 15 seconds

(4) dbms_job : instant execution mode

takes more than 1 hour

Reviewing how data in processed, we are detected that each iteration is very slow.

(5) From SQL_PLUS (linux)

Takes more than 1 hour, iteration very slow

(6) From JAVA

Takes more than 1 hour, iteration very slow

(7) From TOAD

Takes more than 1 hour, iteration very slow

Research

We have eaten a lot of google pages such as the following:

why-does-a-query-run-slower-in-a-stored-procedure-than-in-the-query-window

oracle-pl-sql-procedure-runs-slower-than-sql

oracle-insert-in-stored-procedure-very-slow-compared-to-insert-run-manually

stored-proc-running-30-slower-through-java-versus-running-directly-on-database

So my questions are :

• Why Oracle act this way?
• Shouldn't it behave fast in all scenarios (same parameters)?
• Stored Procedure must be modified?
• If query plan, trace files or statistics shows different behaviors, stored procedure must be fixed ?
• Why execution in query window is fast?

Thanks in advance.

---

Tips from comments

TIP #1

Following the recommendations of @BobJarvis about statistics

Result : Our statistics are up to date. Even, we re executed EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SOME_USER', tabname=>'SOME_TABLE', cascade => TRUE); in all problematics tables and the result is the same.

---

TIP #2

Following the recommendations of @KonstantinSorokin

I suspect execution plans may differ due to a difference in sessions settings. Consider comparing v$ses_optimizer_env

Result : We have compared and result v$ses_optimizer_env is the same for (1) and (4) scenarios.

---

TIP #3

Using this query:

select s.sid,s.serial#,s.username, s.machine,replace(q.SQL_FULLTEXT,chr(0)) sql_text, s.program, s.logon_time, s.status, s.OSUSER
from v$session s, v$sql q
where 
s.status='ACTIVE'
and s.username is not null 
and s.sql_hash_value = q.hash_value
order by s.LOGON_TIME, s.username;

I've noticed that machine, program and ouser change depending of the test:

FAST MODE (query window)

machine             | program           | ouser
--------------------|------------------ | -------
my laptop username  | SQL DEVELOPER     | User

LAG MODE (background execution)

machine             | program           | ouser
--------------------|------------------ | -------
ip-10-6-7-1         | oracle@ip-10-6-7-1| rdsdb

---

TIP #4

Following the recommendations of @KonstantinSorokin related to traces.

Result : A temporal DBA has investigated and He told us that some sql_id have different execution plans. His advice was : use hints.

This could be the solution but, why some SQL ID have different executions plan?

---

[SOLVED]

Thanks to @IsaacMejia, NLS_COMP=LINGUISTIC was the reason of slowly execution. So java was not the cause of problem. Oracle misconfigurations were the cause of our problem.

Solution must be set the correct value for NLS_COMP=BINARY at instance level.

But in my case, I have several applications working well with this value. So in order to avoid ordering and comparisons issues in our applications, I can't override instance NLS settings.

Temporary solution is execute at the beginning of stored procedure :

execute immediate 'alter session set NLS_COMP=''BINARY''';

and return to previous value at finish :

execute immediate 'alter session set NLS_COMP=''LINGUISTIC''';

Now stored procedure run fast as directly execution in query window (ORACLE SQL DEVELOPER)

Answer 1

Try getting nls parameters from your diferent cases(ide's or java programs) they must be diferent

select * from NLS_SESSION_PARAMETERS

Then inside of your store procedure set the variables to make them equals from the fastest case.

  execute immediate 'alter session set NLS_SORT=''SPANISH''';

Once you SP have all nls parameters . It will run fast.

I just recently found a similar case in Alter session slows down the query through Hibernate . but in their case they change de parameters and then became slow.

I investigated and found that The parameters NLS_COMP y NLS_SORT may affect how oracle make uses of execute plan for string ( when it is comparing or ordering)

When NLS_COMP is defined as LINGUISTIC it will use the language define in NLS_SORT.

for example, if NLS_COMP = LINGUISTIC and NLS_SORT=BINARI_AI your querys is

select * from table where string_column like 'HI%'

internally it will do

select * from table where  
NLSSORT(string_column,'BINARI_AI') >= HEXTORAW('324242432')
NLSSORT(string_column,'BINARI_AI') >= HEXTORAW('675757576')

so if you dont have a index for NLSSORT(column,'BINARI_AI') it will be very slow.

knowing that NLS_SORT=BINARY_AI will make accent-insensitive and case-insensitive your ordering and comparisons.