[20241123]PLSQL语句代码执行几次会缓存.txt
[20241123]PLSQL语句代码执行几次会缓存.txt
--//测试看看PLSQL语句代码执行几次会缓存。
1.环境:
SCOTT@book> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
2.建立测试环境:
create table job_times (sid number, time_ela number,method varchar2(20));
drop table t purge ;
create table t as select rownum id ,'test' pad from dual connect by level<=5e5;
create unique index pk_t on t(id);
exec dbms_stats.gather_table_stats(user, 't', method_opt=>'for all columns size 1');
$ cat ma.sql
DECLARE
l_count PLS_INTEGER;
BEGIN
FOR i IN 1..&1
LOOP
EXECUTE IMMEDIATE 'Select /*+ &2 */ count(*) from t where id = :j ' INTO l_count USING i;
END LOOP;
END;
/
3.测试:
--//session 1:
SCOTT@book> @ spid
==============================
SID : 198
SERIAL# : 15
PROCESS : 4149
SERVER : DEDICATED
SPID : 4150
PID : 23
P_SERIAL# : 8
KILL_COMMAND : alter system kill session '198,15' immediate;
PL/SQL procedure successfully completed.
SCOTT@book> @ ma.sql 1 aaa
PL/SQL procedure successfully completed.
--//session 2:
SYS@book> select sql_id ,sql_text from v$sql where sql_text like '%aaa%';
SQL_ID SQL_TEXT
------------- ------------------------------------------------------------
guks3pkwd919m select sql_id ,sql_text from v$sql where sql_text like '%aaa
%'
7qw22mv75gdk3 DECLARE l_count PLS_INTEGER; BEGIN FOR i IN 1..1
LOOP EXECUTE IMMEDIATE 'Select /*+ aaa */ count(*) fr
om t where id = :j ' INTO l_count USING i; END LOOP; END
;
a69n5u4fp3dsm Select /*+ aaa */ count(*) from t where id = :j
SYS@book> select * from v$open_cursor where sql_id='a69n5u4fp3dsm'
2 @pr
==============================
SADDR : 000000008F06AFE0
SID : 198
USER_NAME : SCOTT
ADDRESS : 0000000088940A68
HASH_VALUE : 491894547
SQL_ID : a69n5u4fp3dsm
SQL_TEXT : Select /*+ aaa */ count(*) from t where id = :j
LAST_SQL_ACTIVE_TIME :
SQL_EXEC_ID :
CURSOR_TYPE : PL/SQL CURSOR CACHED
PL/SQL procedure successfully completed.
--//session 1:
SCOTT@book> @ ma.sql 1 aaa
PL/SQL procedure successfully completed.
--//session 2:
SYS@book> @pr
==============================
SADDR : 000000008F06AFE0
SID : 198
USER_NAME : SCOTT
ADDRESS : 0000000088940A68
HASH_VALUE : 491894547
SQL_ID : a69n5u4fp3dsm
SQL_TEXT : Select /*+ aaa */ count(*) from t where id = :j
LAST_SQL_ACTIVE_TIME :
SQL_EXEC_ID :
CURSOR_TYPE : PL/SQL CURSOR CACHED
PL/SQL procedure successfully completed.
SYS@book> select sql_id,sql_text,executions from v$sql where sql_id='a69n5u4fp3dsm';
SQL_ID SQL_TEXT EXECUTIONS
------------- ------------------------------------------------------------ ----------
a69n5u4fp3dsm Select /*+ aaa */ count(*) from t where id = :j 2
--//可以发现plsql代码执行1次就缓存了。
--//而普通执行的sql语句:
$ cat mb.txt
select * from dept where deptno=31;
@ hash
--//注意:后面执行@hash,不然查看v$open_cursor光标处于打开状态。
--//session 1:
SCOTT@book> @ spid
==============================
SID : 19
SERIAL# : 81
PROCESS : 7552
SERVER : DEDICATED
SPID : 7553
PID : 24
P_SERIAL# : 50
KILL_COMMAND : alter system kill session '19,81' immediate;
PL/SQL procedure successfully completed.
--//第1次执行:
SCOTT@book> @ mb.txt
no rows selected
HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX SQL_EXEC_START SQL_EXEC_ID
---------- ------------- ------------ ---------- --------------- ---------- ------------------- -----------
1289778109 363ktkj6f0wxx 0 29629 2852011669 4ce073bd 2024-11-23 12:06:57 16777216
--//session 2:
--//第1次执行后查询:
SYS@book> select sql_id,sql_text,executions from v$sql where sql_id='363ktkj6f0wxx';
SQL_ID SQL_TEXT EXECUTIONS
------------- ------------------------------------------------------------ ----------
363ktkj6f0wxx select * from dept where deptno=31 1
SYS@book> select * from v$open_cursor where sql_id='363ktkj6f0wxx'
2 @pr
PL/SQL procedure successfully completed.
--//第2次执行后查询:
SYS@book> select sql_id,sql_text,executions from v$sql where sql_id='363ktkj6f0wxx';
SQL_ID SQL_TEXT EXECUTIONS
------------- ------------------------------------------------------------ ----------
363ktkj6f0wxx select * from dept where deptno=31 2
SYS@book> select * from v$open_cursor where sql_id='363ktkj6f0wxx'
2 @pr
PL/SQL procedure successfully completed.
--//第3次执行后查询:
SYS@book> select sql_id,sql_text,executions from v$sql where sql_id='363ktkj6f0wxx';
SQL_ID SQL_TEXT EXECUTIONS
------------- ------------------------------------------------------------ ----------
363ktkj6f0wxx select * from dept where deptno=31 3
SYS@book> select * from v$open_cursor where sql_id='363ktkj6f0wxx'
2 @pr
==============================
SADDR : 000000008F5E7120
SID : 19
USER_NAME : SCOTT
ADDRESS : 000000008F84DF70
HASH_VALUE : 1289778109
SQL_ID : 363ktkj6f0wxx
SQL_TEXT : select * from dept where deptno=31
LAST_SQL_ACTIVE_TIME :
SQL_EXEC_ID :
CURSOR_TYPE : DICTIONARY LOOKUP CURSOR CACHED
PL/SQL procedure successfully completed.
--//第4次执行后查询:
SYS@book> select sql_id,sql_text,executions from v$sql where sql_id='363ktkj6f0wxx';
SQL_ID SQL_TEXT EXECUTIONS
------------- ------------------------------------------------------------ ----------
363ktkj6f0wxx select * from dept where deptno=31 4
SYS@book> select * from v$open_cursor where sql_id='363ktkj6f0wxx'
2 @pr
==============================
SADDR : 000000008F5E7120
SID : 19
USER_NAME : SCOTT
ADDRESS : 000000008F84DF70
HASH_VALUE : 1289778109
SQL_ID : 363ktkj6f0wxx
SQL_TEXT : select * from dept where deptno=31
LAST_SQL_ACTIVE_TIME :
SQL_EXEC_ID :
CURSOR_TYPE : SESSION CURSOR CACHED
PL/SQL procedure successfully completed.
--//第4次执行光标缓存。
--//测试看看PLSQL语句代码执行几次会缓存。
1.环境:
SCOTT@book> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
2.建立测试环境:
create table job_times (sid number, time_ela number,method varchar2(20));
drop table t purge ;
create table t as select rownum id ,'test' pad from dual connect by level<=5e5;
create unique index pk_t on t(id);
exec dbms_stats.gather_table_stats(user, 't', method_opt=>'for all columns size 1');
$ cat ma.sql
DECLARE
l_count PLS_INTEGER;
BEGIN
FOR i IN 1..&1
LOOP
EXECUTE IMMEDIATE 'Select /*+ &2 */ count(*) from t where id = :j ' INTO l_count USING i;
END LOOP;
END;
/
3.测试:
--//session 1:
SCOTT@book> @ spid
==============================
SID : 198
SERIAL# : 15
PROCESS : 4149
SERVER : DEDICATED
SPID : 4150
PID : 23
P_SERIAL# : 8
KILL_COMMAND : alter system kill session '198,15' immediate;
PL/SQL procedure successfully completed.
SCOTT@book> @ ma.sql 1 aaa
PL/SQL procedure successfully completed.
--//session 2:
SYS@book> select sql_id ,sql_text from v$sql where sql_text like '%aaa%';
SQL_ID SQL_TEXT
------------- ------------------------------------------------------------
guks3pkwd919m select sql_id ,sql_text from v$sql where sql_text like '%aaa
%'
7qw22mv75gdk3 DECLARE l_count PLS_INTEGER; BEGIN FOR i IN 1..1
LOOP EXECUTE IMMEDIATE 'Select /*+ aaa */ count(*) fr
om t where id = :j ' INTO l_count USING i; END LOOP; END
;
a69n5u4fp3dsm Select /*+ aaa */ count(*) from t where id = :j
SYS@book> select * from v$open_cursor where sql_id='a69n5u4fp3dsm'
2 @pr
==============================
SADDR : 000000008F06AFE0
SID : 198
USER_NAME : SCOTT
ADDRESS : 0000000088940A68
HASH_VALUE : 491894547
SQL_ID : a69n5u4fp3dsm
SQL_TEXT : Select /*+ aaa */ count(*) from t where id = :j
LAST_SQL_ACTIVE_TIME :
SQL_EXEC_ID :
CURSOR_TYPE : PL/SQL CURSOR CACHED
PL/SQL procedure successfully completed.
--//session 1:
SCOTT@book> @ ma.sql 1 aaa
PL/SQL procedure successfully completed.
--//session 2:
SYS@book> @pr
==============================
SADDR : 000000008F06AFE0
SID : 198
USER_NAME : SCOTT
ADDRESS : 0000000088940A68
HASH_VALUE : 491894547
SQL_ID : a69n5u4fp3dsm
SQL_TEXT : Select /*+ aaa */ count(*) from t where id = :j
LAST_SQL_ACTIVE_TIME :
SQL_EXEC_ID :
CURSOR_TYPE : PL/SQL CURSOR CACHED
PL/SQL procedure successfully completed.
SYS@book> select sql_id,sql_text,executions from v$sql where sql_id='a69n5u4fp3dsm';
SQL_ID SQL_TEXT EXECUTIONS
------------- ------------------------------------------------------------ ----------
a69n5u4fp3dsm Select /*+ aaa */ count(*) from t where id = :j 2
--//可以发现plsql代码执行1次就缓存了。
--//而普通执行的sql语句:
$ cat mb.txt
select * from dept where deptno=31;
@ hash
--//注意:后面执行@hash,不然查看v$open_cursor光标处于打开状态。
--//session 1:
SCOTT@book> @ spid
==============================
SID : 19
SERIAL# : 81
PROCESS : 7552
SERVER : DEDICATED
SPID : 7553
PID : 24
P_SERIAL# : 50
KILL_COMMAND : alter system kill session '19,81' immediate;
PL/SQL procedure successfully completed.
--//第1次执行:
SCOTT@book> @ mb.txt
no rows selected
HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX SQL_EXEC_START SQL_EXEC_ID
---------- ------------- ------------ ---------- --------------- ---------- ------------------- -----------
1289778109 363ktkj6f0wxx 0 29629 2852011669 4ce073bd 2024-11-23 12:06:57 16777216
--//session 2:
--//第1次执行后查询:
SYS@book> select sql_id,sql_text,executions from v$sql where sql_id='363ktkj6f0wxx';
SQL_ID SQL_TEXT EXECUTIONS
------------- ------------------------------------------------------------ ----------
363ktkj6f0wxx select * from dept where deptno=31 1
SYS@book> select * from v$open_cursor where sql_id='363ktkj6f0wxx'
2 @pr
PL/SQL procedure successfully completed.
--//第2次执行后查询:
SYS@book> select sql_id,sql_text,executions from v$sql where sql_id='363ktkj6f0wxx';
SQL_ID SQL_TEXT EXECUTIONS
------------- ------------------------------------------------------------ ----------
363ktkj6f0wxx select * from dept where deptno=31 2
SYS@book> select * from v$open_cursor where sql_id='363ktkj6f0wxx'
2 @pr
PL/SQL procedure successfully completed.
--//第3次执行后查询:
SYS@book> select sql_id,sql_text,executions from v$sql where sql_id='363ktkj6f0wxx';
SQL_ID SQL_TEXT EXECUTIONS
------------- ------------------------------------------------------------ ----------
363ktkj6f0wxx select * from dept where deptno=31 3
SYS@book> select * from v$open_cursor where sql_id='363ktkj6f0wxx'
2 @pr
==============================
SADDR : 000000008F5E7120
SID : 19
USER_NAME : SCOTT
ADDRESS : 000000008F84DF70
HASH_VALUE : 1289778109
SQL_ID : 363ktkj6f0wxx
SQL_TEXT : select * from dept where deptno=31
LAST_SQL_ACTIVE_TIME :
SQL_EXEC_ID :
CURSOR_TYPE : DICTIONARY LOOKUP CURSOR CACHED
PL/SQL procedure successfully completed.
--//第4次执行后查询:
SYS@book> select sql_id,sql_text,executions from v$sql where sql_id='363ktkj6f0wxx';
SQL_ID SQL_TEXT EXECUTIONS
------------- ------------------------------------------------------------ ----------
363ktkj6f0wxx select * from dept where deptno=31 4
SYS@book> select * from v$open_cursor where sql_id='363ktkj6f0wxx'
2 @pr
==============================
SADDR : 000000008F5E7120
SID : 19
USER_NAME : SCOTT
ADDRESS : 000000008F84DF70
HASH_VALUE : 1289778109
SQL_ID : 363ktkj6f0wxx
SQL_TEXT : select * from dept where deptno=31
LAST_SQL_ACTIVE_TIME :
SQL_EXEC_ID :
CURSOR_TYPE : SESSION CURSOR CACHED
PL/SQL procedure successfully completed.
--//第4次执行光标缓存。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
2018-11-24 [20181124]关于降序索引问题3.txt
2018-11-24 [20181124]关于降序索引问题2.txt
2017-11-24 [20171124]xxd与通配符.txt
2017-11-24 [20171124]手工使用Seed_Database.dfb和Seed_Database.ctl建库.txt
2017-11-24 [20171124]bbed的使用问题2.txt