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