为什么oracle存储过程的执行时间会大大增加，具体取决于它的执行方式？ [英] Why oracle stored procedure execution time is greatly increased depending on how it is executed?
问题描述
这是我的问题：
- 我们有一个名为 HEAVY_SP 的存储过程与
相同参数 - 我们有一个 oracle sql developer 。这可以称为： IDE
- We have a stored procedure called : HEAVY_SP excuted with the same parameters in all scenarios
- We have a oracle sql developer. This can be called : IDE
根据执行方式的不同，执行时间会大大增加：
Depending on how it is executed, execution time is greatly increased:
-
（1）
CALL HEAVY_SP（0，'F'，5 ，...）直接在查询窗口（IDE）= 15秒（我们当前的解决方案）
(1)
CALL HEAVY_SP(0, 'F', 5, ...)directly in query window (IDE) = 15 seconds (Our current solution)
（2）使用IDE的特殊按钮= 15秒
(2) Using a special button of IDE = 15 seconds
（3）使用dbms_job（预定执行）= 15秒（作业已安排但未执行。使用IDE执行作业：右键单击并执行）
(3) Using dbms_job (scheduled execution) = 15 seconds (Job is scheduled and not executed. Job is executed using IDE: right click and execute)
（4）使用dbms_job（即时执行）= 超过01小时，迭代非常慢
(4) Using dbms_job (instant execution) = more than 01 hour, iteration very slow
（5）从SQL_PLUS（linux）= 超过01小时，迭代非常慢
(5) From SQL_PLUS (linux) = more than 01 hour, iteration very slow
（6）从JAVA = 超过01小时，迭代速度很慢
（7）从TOAD = 超过01小时，迭代非常慢
我们吃了很多谷歌网页，如下所示：
We have eaten a lot of google pages such as the following:
oracle-pl-sql-procedure-runs-slow-than -sql
oracle-insert-in-stored-procedure-very-slow-comparison-to-insert-run-manual
stored-proc-running-30-slow-through-java-versus-running-direct-on-database
所以我的问题是：
- 为什么Oracle采用这种方式行事？
- 应该它在所有场景中都表现得很快（相同参数）？
- 存储过程必须修改？
- 如果查询计划，跟踪文件或统计信息显示了不同的行为，那么必须修复存储的行为？
- 为什么查询窗口中的执行速度很快？ / strong>
- 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 deferents behaviors, stored prodecure must be fixed ?
- Why execution in query window is fast?
提前致谢。
提示＃1
遵循 @BobJarvis 关于统计信息的建议
Following the recommendations of @BobJarvis about statistics
结果：我们的统计数据是最新的。甚至，我们在所有问题表中执行 EXEC DBMS_STATS.GATHER_TABLE_STATS（ownname =>'SOME_USER'，tabname =>'SOME_TABLE'，cascade => TRUE）; 结果是一样的。
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.
提示＃2
遵循 @KonstantinSorokin 的建议
我怀疑执行计划可能会有所不同会话设置的差异。考虑比较 v $ ses_optimizer_env
结果：我们已经比较了结果 v $ ses_optimizer_env 与（1）和（4）方案相同。
Result : We have compared and result v$ses_optimizer_env is the same for (1) and (4) scenarios.
提示＃3
使用此查询：
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;
我注意到机器，程序和ouser的变化取决于测试：
I've noticed that machine, program and ouser change depending of the test:
快速模式（查询窗口）
machine | program | ouser
--------------------|------------------ | -------
my laptop username | SQL DEVELOPER | User
LAG MODE（后台执行）
machine | program | ouser
--------------------|------------------ | -------
ip-10-6-7-1 | oracle@ip-10-6-7-1| rdsdb
提示＃4
遵循与追踪相关的 @KonstantinSorokin 的建议。
Following the recommendations of @KonstantinSorokin related to traces.
结果：一个临时DBA已经调查过，他告诉我们一些sql_id有不同的执行计划。他的建议是：使用提示。
Result : A temporal DBA has investigated and He told us that some sql_id have different execution plans. His advice was : use hints.
这可能是解决方案但是，为什么有些SQL ID有不同的执行计划？
This could be the solution but, why some SQL ID have different executions plan?
[已解决]
感谢@IsaacMejia， NLS_COMP = LINGUISTIC 是缓慢执行的原因。
Thanks to @IsaacMejia, NLS_COMP=LINGUISTIC was the reason of slowly execution.
必须在实例级别为 NLS_COMP = BINARY 设置正确的解决方案。
Solution must be set the correct value for NLS_COMP=BINARY at instance level.
但就我而言，我有几个应用程序可以很好地使用这个值。因此，为了避免在我们的应用程序中排序和比较问题，我无法覆盖实例NLS设置。
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''';
现在存储过程在查询窗口中直接执行（ORACLE SQL DEVELOPER）
Now stored procedure run fast as directly execution in query window (ORACLE SQL DEVELOPER)
推荐答案
尝试从不同的案例（ide或java程序）中获取nls参数它们必须是不同的
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''';
一旦SP拥有所有nls参数。它会快速运行。
Once you SP have all nls parameters . It will run fast.
我最近在 Alter会话通过Hibernate减慢查询速度。但在他们的情况下，他们改变de参数，然后变慢。
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.
我调查并发现参数NLS_COMP和NLS_SORT可能会影响oracle如何使用执行计划的字符串（当它比较或排序时）
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)
当NLS_COMP被定义为LINGUISTIC时，它将使用NLS_SORT中定义的语言。
When NLS_COMP is defined as LINGUISTIC it will use the language define in NLS_SORT.
例如，如果NLS_COMP = LINGUISTIC和NLS_SORT = BINARI_AI你的查询
是
for example, if NLS_COMP = LINGUISTIC and NLS_SORT=BINARI_AI your querys is
select * from table where string_column like 'HI%'
在内部它会执行
select * from table where
NLSSORT(string_column,'BINARI_AI') >= HEXTORAW('324242432')
NLSSORT(string_column,'BINARI_AI') >= HEXTORAW('675757576')
所以如果你没有NLSSORT的索引（列，'BINARI_AI'），那将非常慢。
so if you dont have a index for NLSSORT(column,'BINARI_AI') it will be very slow.
知道NLS_SORT = BINARY_AI会使重音不敏感和案例 - 对您的订购和比较不敏感。
knowing that NLS_SORT=BINARY_AI will make accent-insensitive and case-insensitive your ordering and comparisons.
这篇关于为什么oracle存储过程的执行时间会大大增加，具体取决于它的执行方式？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
