在SQL中使用PL/SQL函数(三)
0
16 16 0
17 17 0
18 18 0
19 19 0
19 rows selected.
Elapsed: 00:00:23.87
注:我的SQLPLUS里这里设置了 SET NULL 'null'
在会话2里我们执行的SQL如下:
--SESSION 2:
[sql]
13:37:02 SCOTT@orcl> UPDATE emp_test SET r = 0 WHERE r < 15;
14 rows updated.
Elapsed: 00:00:00.01
13:38:14 SCOTT@orcl> commit;
Commit complete.
Elapsed: 00:00:00.00
注意执行两个SQL的时间,在会话1执行SQL后,然后执行会话2的SQL并立即提交(留意命令行前的时间)
根据读一致性原理,会话1查询的时候,会话2还未进行更新,所以理应会话1查出的结果应当是更新前的结果,但是这里会话2的更新直接影响到了会话1了,也就是破坏了读一致性.
由于SESSION 2更新导致了SESSION 1执行的PLSQL函数内找不到对应的R值,故返回为空了.
这个是比较危险的,因为它没有得到我们预想的结果.
对于这种情况,我们一般的解决方法有:1.SET TRANSACTION READ ONLY;2.FOR UPDATE加锁;3.使用闪回查询,查询指定时间的数据;4.设置事务隔离级别为serialize
一般来说出现这种情况,开发人员用的比较多的就是2,加锁,其实这又是另一个性能开销了。。。
3.对于SQL条件中出现的PL/SQL函数,CBO是比较难优化的,因为此时对于SQL中的PL/SQL函数是没有统计信息的,而对CBO来说,最重要的就是统计信息.所以此时CBO会采用其默认的分析处理方法来对待PL/SQL函数.
示例:
[sql]
13:19:18 SCOTT@orcl> CREATE TABLE EMP_TEST AS
13:19:27 2 SELECT ROWNUM R FROM DUAL CONNECT BY LEVEL<1000;
Table created.
Elapsed: 00:00:00.20
13:19:28 SCOTT@orcl> EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'EMP_TEST');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.19
13:20:08 SCOTT@orcl> CREATE OR REPLACE FUNCTION fun_test(p_number IN NUMBER)
13:20:20 2 RETURN NUMBER AS
13:20:20 3 BEGIN
13:20:20 4 RETURN p_number;
13:20:20 5 END fun_test;
13:20:21 6 /
Function created.
Elapsed: 00:00:00.01
13:20:22 SCOTT@orcl> alter session set events '10053 trace name context forever,level 1';
Session altered.
Elapsed: 00:00:00.00
13:20:36 SCOTT@orcl> set autotrace traceonly explain
13:20:45 SCOTT@orcl> SELECT * FROM EMP_TEST
13:20:54 2 WHERE FUN_TEST(R)=1;
Elapsed: 00:00:00.06
Execution Plan
----------------------------------------------------------
Plan hash value: 3124080142
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 40 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP_TEST | 10 | 40 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("FUN_TEST"("R")=1)
13:21:18 SCOTT@orcl> alter session set events '10053 trace name context off';
Session altered.
Elapsed: 00:00:00.00
查看10053产生的TRACE信息发现:
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: EMP_TEST Alias: EMP_TEST
#Rows: 999 #Blks: 5 AvgRowLen: 4.00
Access path analysis for EMP_TEST
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for EMP_TEST[EMP_TEST]
No default cost defined for function FUN_TEST
No default selectivity defined for function FUN_TEST