[20230527]RESULT_CACHE提示选项2.txt

来源：这里教程网时间：2026-03-03 18:49:59 作者：

[20230527]RESULT_CACHE提示选项2.txt --//昨天测试了result_cache(snapshot=N)提示，它相当于不管查询对象数据有何变化，这个结果集合保持一定的时刻的状态。 --//我在想许多情况下其实可能不需要知道准确结果，可以通过它减少对数据库的压力，测试通过sql profile或者sql patch方式实现这个功能。 1.环境： SCOTT@test01p> @ ver1 PORT_STRING VERSION BANNER CON_ID -------------------- ---------- ---------------------------------------------------------------------------- ------ IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0 2.例子建立： SCOTT@test01p> create table DEMO as select rownum id from xmltable('1 to 100000'); Table created. SCOTT@test01p> @ o2 demo owner object_name object_type SUBOBJECT_NAME status OID D_OID CREATED LAST_DDL_TIME ----- ----------- ----------- -------------- ------ ----- ----- ------------------- ------------------- SCOTT DEMO TABLE VALID 30026 30026 2023-05-27 21:37:36 2023-05-27 21:37:36 --//object_id=30026 --//分析略。 SCOTT@test01p> exec if dbms_result_cache.flush then dbms_output.put_line('Flushed.'); end if; PL/SQL procedure successfully completed. SCOTT@test01p> select * from v$result_cache_dependency where OBJECT_NO=30026; no rows selected SCOTT@test01p> select CREATION_TIMESTAMP,id,type,status,name,cache_id,invalidations from v$result_cache_objects order by id; no rows selected SCOTT@test01p> select count(*) from DEMO; COUNT(*) -------- 100000 SCOTT@test01p> @ hash HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX SQL_EXEC_START SQL_EXEC_ID ---------- ------------- ------------ ---------- --------------- ---------- ------------------- ----------- 6441961 7f9rsjn064kz9 0 19433 2180342005 624be9 2023-05-27 21:40:54 16777219 --//记下sql_id=7f9rsjn064kz9. SCOTT@test01p> select /*+ result_cache(snapshot=120) */ count(*) from DEMO; COUNT(*) -------- 100000 SCOTT@test01p> @ hash HASH_VALUE SQL_ID CHILD_NUMBER KGL_BUCKET PLAN_HASH_VALUE HASH_HEX SQL_EXEC_START SQL_EXEC_ID ---------- ------------- ------------ ---------- --------------- ---------- ------------------- ----------- 2674345099 c8utd9agqfj4b 0 83083 2180342005 9f67448b 2023-05-27 21:41:55 16777220 --//记下sql_id=c8utd9agqfj4b 3.首先尝试sql profile"稳定"执行计划： SCOTT@test01p> @ spsw c8utd9agqfj4b 0 7f9rsjn064kz9 0 '' true PL/SQL procedure successfully completed. ================================================================================================================================================= if drop or alter sql profile ,run : execute dbms_sqltune.drop_sql_profile(name => 'switch tuning 7f9rsjn064kz9') execute dbms_sqltune.alter_sql_profile(name => 'switch tuning 7f9rsjn064kz9',attribute_name=>'STATUS',value=>'DISABLED') ================================================================================================================================================= SCOTT@test01p> @ spext 7f9rsjn064kz9 HINT NAME DESCRIPTION LAST_MODIFIED ------------------------------------- --------------------------- ---------------------------------------- ------------------------------ IGNORE_OPTIM_EMBEDDED_HINTS switch tuning 7f9rsjn064kz9 switch c8utd9agqfj4b => 7f9rsjn064kz9 2023-05-27 21:43:33.000000 OPTIMIZER_FEATURES_ENABLE('12.2.0.1') switch tuning 7f9rsjn064kz9 switch c8utd9agqfj4b => 7f9rsjn064kz9 2023-05-27 21:43:33.000000 DB_VERSION('12.2.0.1') switch tuning 7f9rsjn064kz9 switch c8utd9agqfj4b => 7f9rsjn064kz9 2023-05-27 21:43:33.000000 ALL_ROWS switch tuning 7f9rsjn064kz9 switch c8utd9agqfj4b => 7f9rsjn064kz9 2023-05-27 21:43:33.000000 OUTLINE_LEAF(@"SEL$1") switch tuning 7f9rsjn064kz9 switch c8utd9agqfj4b => 7f9rsjn064kz9 2023-05-27 21:43:33.000000 FULL(@"SEL$1" "DEMO"@"SEL$1") switch tuning 7f9rsjn064kz9 switch c8utd9agqfj4b => 7f9rsjn064kz9 2023-05-27 21:43:33.000000 RESULT_CACHE(@"SEL$1") switch tuning 7f9rsjn064kz9 switch c8utd9agqfj4b => 7f9rsjn064kz9 2023-05-27 21:43:33.000000 7 rows selected. --//可以发现这样不行，提示里面没有snapshot=120信息。还有我尝试发现这样sql profile根本不起作用. SCOTT@test01p> select count(*) from DEMO; COUNT(*) -------- 100000 SCOTT@test01p> @ dpc '' outline '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 7f9rsjn064kz9, child number 0 ------------------------------------- select count(*) from DEMO Plan hash value: 2180342005 ------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 47 (100)| | 1 |00:00:00.01 | 169 | | 1 | SORT AGGREGATE | | 1 | 1 | | | 1 |00:00:00.01 | 169 | | 2 | TABLE ACCESS FULL| DEMO | 1 | 100K| 47 (3)| 00:00:01 | 100K|00:00:00.01 | 169 | ------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / DEMO@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('12.2.0.1') DB_VERSION('12.2.0.1') ALL_ROWS OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "DEMO"@"SEL$1") END_OUTLINE_DATA */ Note ----- - SQL profile switch tuning 7f9rsjn064kz9 used for this statement 38 rows selected. --//连RESULT_CACHE提示也没有起作用，为什么？ SCOTT@test01p> execute dbms_sqltune.drop_sql_profile(name => 'switch tuning 7f9rsjn064kz9'); PL/SQL procedure successfully completed. 4.尝试sql patch"稳定"执行计划： SCOTT@test01p> @ sqlpatch 7f9rsjn064kz9 result_cache(snapshot=120) input @sqlpatch sqlid 'hint_text' oracle_version(11 or 12) drop sql patch ,run exec sys.dbms_sqldiag.drop_sql_patch('sqlpatch_7f9rsjn064kz9'); display sql path message , run @spext 7f9rsjn064kz9 PL/SQL procedure successfully completed. SCOTT@test01p> @spext 7f9rsjn064kz9 HINT NAME DESCRIPTION LAST_MODIFIED -------------------------- ---------------------- ----------- ------------------------------ result_cache(snapshot=120) sqlpatch_7f9rsjn064kz9 2023-05-27 21:48:19.000000 --//OK,sql patch已经加入。 5.测试： SCOTT@test01p> @ sl all alter session set statistics_level = all; Session altered. SCOTT@test01p> select count(*) from DEMO; COUNT(*) -------- 100000 Plan hash value: 2180342005 ----------------------------------------------------------------------------------------------------------------- |Id|Operation |Name |Starts|E-Rows|Cost(%CPU)|E-Time |A-Rows| A-Time |Buffers| ----------------------------------------------------------------------------------------------------------------- | 0|SELECT STATEMENT | | 1| | 47 (100)| | 1 |00:00:00.01| 169| | 1| RESULT CACHE |drgpstytrnd7c4f0ahjnpjmv8h| 1| | | | 1 |00:00:00.01| 169| | 2| SORT AGGREGATE | | 1| 1 | | | 1 |00:00:00.01| 169| | 3| TABLE ACCESS FULL|DEMO | 1| 100K| 47 (3)|00:00:01| 100K|00:00:00.01| 169| ----------------------------------------------------------------------------------------------------------------- --//执行计划第一次执行，存在逻辑读169正常。id=1出现RESULT CACHE,说明补丁有用。 Plan hash value: 2180342005 --------------------------------------------------------------------------------------------------------- |Id|Operation |Name |Starts|E-Rows|Cost(%CPU)|E-Time |A-Rows| A-Time | --------------------------------------------------------------------------------------------------------- | 0|SELECT STATEMENT | | 1| | 47 (100)| | 1|00:00:00.01| | 1| RESULT CACHE |drgpstytrnd7c4f0ahjnpjmv8h| 1| | | | 1|00:00:00.01| | 2| SORT AGGREGATE | | 0| 1 | | | 0|00:00:00.01| | 3| TABLE ACCESS FULL|DEMO | 0| 100K| 47 (3)|00:00:01| 0|00:00:00.01| --------------------------------------------------------------------------------------------------------- --//执行计划第二次执行，逻辑读为0，id=3,A-rows=0。可以确定根本没有读表块。 SCOTT@test01p> select count(*) from DEMO; COUNT(*) -------- 100000 SCOTT@test01p> select sysdate,CREATION_TIMESTAMP,id,type,status,name,cache_id,invalidations from v$result_cache_objects where cache_id='drgpstytrnd7c4f0ahjnpjmv8h' order by id; SYSDATE CREATION_TIMESTAMP ID TYPE STATUS NAME CACHE_ID INVALIDATIONS ------------------- ------------------- --- ------ --------- ---------------------------------------- ------------------------------ ------------- 2023-05-27 21:57:01 2023-05-27 21:50:14 86 Result Invalid select count(*) from DEMO drgpstytrnd7c4f0ahjnpjmv8h 0 2023-05-27 21:57:01 2023-05-27 21:54:14 87 Result Invalid select count(*) from DEMO drgpstytrnd7c4f0ahjnpjmv8h 0 2023-05-27 21:57:01 2023-05-27 21:56:58 88 Result Published select count(*) from DEMO drgpstytrnd7c4f0ahjnpjmv8h 0 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 2023-05-27 21:57:01 2023-05-27 21:46:36 245 Result Invalid select /*+ result_cache(snapshot=120) */ drgpstytrnd7c4f0ahjnpjmv8h 0 count(*) from DEMO --//注意看CREATION_TIMESTAMP时间，前面的status=Invalid,失效。 SCOTT@test01p> delete from DEMO where id=1; 1 row deleted. SCOTT@test01p> select count(*) from DEMO; COUNT(*) -------- 100000 --//理论应该返回99999。 SCOTT@test01p> commit ; Commit complete. SCOTT@test01p> select count(*) from DEMO; COUNT(*) -------- 100000 --//还是100000。说明我打的补丁起作用。等上2分钟以后 SCOTT@test01p> select count(*) from DEMO; COUNT(*) ---------- 99999 --//现在结果才是正确的。也就是这种方法完全不受dml影响。 SCOTT@test01p> delete from DEMO where id=2; 1 row deleted. SCOTT@test01p> commit; Commit complete. SCOTT@test01p> select count(*) from DEMO; COUNT(*) -------- 99999 SCOTT@test01p> Select count(*) from DEMO; COUNT(*) -------- 99999 --//更改select => Select ,sql patch还是起作用。 SCOTT@test01p> Select /*+1111 */ count(*) from DEMO; COUNT(*) -------- 99998 --//加入注解，sql patch无效。 SCOTT@test01p> select sysdate,CREATION_TIMESTAMP,id,type,status,name,cache_id,invalidations from v$result_cache_objects where cache_id='drgpstytrnd7c4f0ahjnpjmv8h' order by id; SYSDATE CREATION_TIMESTAMP ID TYPE STATUS NAME CACHE_ID INVALIDATIONS ------------------- ------------------- --- ------ ------- ---------------------------------------- -------------------------- ------------- 2023-05-27 22:05:03 2023-05-27 21:50:14 86 Result Invalid select count(*) from DEMO drgpstytrnd7c4f0ahjnpjmv8h 0 2023-05-27 22:05:03 2023-05-27 21:54:14 87 Result Invalid select count(*) from DEMO drgpstytrnd7c4f0ahjnpjmv8h 0 2023-05-27 22:05:03 2023-05-27 21:56:58 88 Result Invalid select count(*) from DEMO drgpstytrnd7c4f0ahjnpjmv8h 0 2023-05-27 22:05:03 2023-05-27 21:59:03 89 Result Invalid select Count(*) from DEMO drgpstytrnd7c4f0ahjnpjmv8h 0 2023-05-27 22:05:03 2023-05-27 22:02:09 90 Result Invalid select Count(*) from DEMO drgpstytrnd7c4f0ahjnpjmv8h 0 2023-05-27 22:05:03 2023-05-27 21:46:36 245 Result Invalid select /*+ result_cache(snapshot=120) */ drgpstytrnd7c4f0ahjnpjmv8h 0 count(*) from DEMO 6 rows selected. 5.收尾： SCOTT@test01p> exec sys.dbms_sqldiag.drop_sql_patch('sqlpatch_7f9rsjn064kz9'); PL/SQL procedure successfully completed. 6.总结: --//纯粹无聊做的测试，也就是通过这样的方式我的一些想法可以实现，sql profile理论也是可以，只不过需要人为编辑提示。 --//相对比较复杂,放弃。 7.附上执行脚本： $ cat sqlpatch.sql prompt prompt input @sqlpatch sqlid 'hint_text' oracle_version(11 or 12) prompt drop sql patch ,run exec sys.dbms_sqldiag.drop_sql_patch('sqlpatch_&1');; prompt display sql path message , run @spext &1 prompt define noprint='noprint' set term off col tpt_version_old &noprint new_value _tpt_version_old col tpt_version_new &noprint new_value _tpt_version_new col tpt_noprint &noprint new_value _tpt_noprint WITH version AS (SELECT TO_NUMBER (SUBSTR (version, 1, 2)) v FROM v$instance) SELECT CASE WHEN v <= 11 THEN '' ELSE '--' END tpt_version_old ,CASE WHEN v > 11 THEN '' ELSE '--' END tpt_version_new FROM version; set term on declare v_sql CLOB; patch_name VARCHAR2 (100); begin select sql_fulltext into v_sql from v$sql where sql_id='&1' and rownum=1; &&_tpt_version_old sys.dbms_sqldiag_internal.i_create_patch( &&_tpt_version_old sql_text => v_sql, &&_tpt_version_old hint_text => '&2', &&_tpt_version_old name => 'sqlpatch_&1'); &&_tpt_version_new patch_name := &&_tpt_version_new sys.DBMS_SQLDIAG.create_sql_patch &&_tpt_version_new ( &&_tpt_version_new sql_text => v_sql &&_tpt_version_new ,hint_text => '&2' &&_tpt_version_new ,name => 'sqlpatch_&1' &&_tpt_version_new ); end; / $ cat spext.sql column hint format a100 column name format a40 column description format a40 column last_modified format a30 SELECT EXTRACTVALUE (VALUE (h), '.') AS hint ,so.name ,substr(ad.description,1,40) description ,ad.last_modified FROM SYS.sqlobj$data od ,SYS.sqlobj$ so ,SYS.sqlobj$auxdata ad ,TABLE ( XMLSEQUENCE ( EXTRACT (XMLTYPE (od.comp_data), '/outline_data/hint') ) ) h WHERE ( so.NAME IN ('profile &&1', 'tuning &&1', 'switch tuning &&1') OR LOWER (so.name) LIKE LOWER ('%&&1%') OR LOWER (ad.description) LIKE LOWER('%&&1%') ) AND so.signature = od.signature AND so.CATEGORY = od.CATEGORY AND so.obj_type = od.obj_type AND so.plan_id = od.plan_id AND so.signature = ad.signature AND so.category = ad.category;

编辑推荐：

返回列表

[20230527]RESULT_CACHE提示选项2.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推