[20240607]PL/SQL中sql语句的注解.txt
[20240607]PL/SQL中sql语句的注解.txt
--//别人测试遇到的问题,重复测试说明问题.
1.环境:
SCOTT@test01p> @ ver
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
$ cat m2.sql
DECLARE
l_count PLS_INTEGER;
BEGIN
FOR i IN 1..&&1
LOOP
SELECT /*+ find_me */ COUNT(*) INTO L_COUNT FROM DEPT WHERE DEPTNO=10;
END LOOP;
END;
/
2.测试1:
--//session 1:
SCOTT@test01p> set timing on
SCOTT@test01p> @ m2.sql 10000
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.47
--//session 2:
SYS@test> select sql_id ,sql_text,executions from v$sql where sql_text like '%find_me%' and executions>=1e4;
SQL_ID SQL_TEXT EXECUTIONS
------------- ------------------------------------------------------------ ----------
b744uv7rtsf8t SELECT /*+ find_me */ COUNT(*) FROM DEPT WHERE DEPTNO=10 10000
3.继续:
$ cat m3.sql
DECLARE
l_count PLS_INTEGER;
BEGIN
FOR i IN 1..&&1
LOOP
SELECT /* find_me */ COUNT(*) INTO L_COUNT FROM DEPT WHERE DEPTNO=10;
END LOOP;
END;
/
--//仅仅注解部分没有+加号.
--//退出会话刷新共享池:
SYS@test> alter system flush shared_pool;
System altered.
--//session 1:
SCOTT@test01p> @ m3.sql 30000
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.51
--//session 2:
SYS@test> select sql_id ,sql_text,executions from v$sql where sql_text like '%find_me%' and executions>=1e4;
no rows selected.
--//没有发现包含find_me字符串的sql语句.
--//而实际上匿名PL SQL做了特殊处理,删除注解部分,真正执行的是:
--// SELECT COUNT(*) FROM DEPT WHERE DEPTNO=10
SYS@test> select sql_id ,sql_text,executions from v$sql where sql_id='9y1zzt1h1f3pz';
SQL_ID SQL_TEXT EXECUTIONS
------------- ------------------------------------------------------------ ----------
9y1zzt1h1f3pz SELECT COUNT(*) FROM DEPT WHERE DEPTNO=10 30000
--//简单验证:
--//session 1:
SCOTT@test01p> @ m3.sql 9999
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.39
--//session 2:
SYS@test> select sql_id ,sql_text,executions from v$sql where sql_id='9y1zzt1h1f3pz';
SQL_ID SQL_TEXT EXECUTIONS
------------- ------------------------------------------------------------ ----------
9y1zzt1h1f3pz SELECT COUNT(*) FROM DEPT WHERE DEPTNO=10 39999
--//如果注解部分没有+,PL/SQL对于里面的sql语句会作出特殊处理,取消注解,另外注意PL/SQL会格式化sql语句,这些在我以前blog提到.
--//别人测试遇到的问题,重复测试说明问题.
1.环境:
SCOTT@test01p> @ ver
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
$ cat m2.sql
DECLARE
l_count PLS_INTEGER;
BEGIN
FOR i IN 1..&&1
LOOP
SELECT /*+ find_me */ COUNT(*) INTO L_COUNT FROM DEPT WHERE DEPTNO=10;
END LOOP;
END;
/
2.测试1:
--//session 1:
SCOTT@test01p> set timing on
SCOTT@test01p> @ m2.sql 10000
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.47
--//session 2:
SYS@test> select sql_id ,sql_text,executions from v$sql where sql_text like '%find_me%' and executions>=1e4;
SQL_ID SQL_TEXT EXECUTIONS
------------- ------------------------------------------------------------ ----------
b744uv7rtsf8t SELECT /*+ find_me */ COUNT(*) FROM DEPT WHERE DEPTNO=10 10000
3.继续:
$ cat m3.sql
DECLARE
l_count PLS_INTEGER;
BEGIN
FOR i IN 1..&&1
LOOP
SELECT /* find_me */ COUNT(*) INTO L_COUNT FROM DEPT WHERE DEPTNO=10;
END LOOP;
END;
/
--//仅仅注解部分没有+加号.
--//退出会话刷新共享池:
SYS@test> alter system flush shared_pool;
System altered.
--//session 1:
SCOTT@test01p> @ m3.sql 30000
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.51
--//session 2:
SYS@test> select sql_id ,sql_text,executions from v$sql where sql_text like '%find_me%' and executions>=1e4;
no rows selected.
--//没有发现包含find_me字符串的sql语句.
--//而实际上匿名PL SQL做了特殊处理,删除注解部分,真正执行的是:
--// SELECT COUNT(*) FROM DEPT WHERE DEPTNO=10
SYS@test> select sql_id ,sql_text,executions from v$sql where sql_id='9y1zzt1h1f3pz';
SQL_ID SQL_TEXT EXECUTIONS
------------- ------------------------------------------------------------ ----------
9y1zzt1h1f3pz SELECT COUNT(*) FROM DEPT WHERE DEPTNO=10 30000
--//简单验证:
--//session 1:
SCOTT@test01p> @ m3.sql 9999
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.39
--//session 2:
SYS@test> select sql_id ,sql_text,executions from v$sql where sql_id='9y1zzt1h1f3pz';
SQL_ID SQL_TEXT EXECUTIONS
------------- ------------------------------------------------------------ ----------
9y1zzt1h1f3pz SELECT COUNT(*) FROM DEPT WHERE DEPTNO=10 39999
--//如果注解部分没有+,PL/SQL对于里面的sql语句会作出特殊处理,取消注解,另外注意PL/SQL会格式化sql语句,这些在我以前blog提到.
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库