[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次执行光标缓存。
