[20230527]RESULT_CACHE提示选项2.txt
发布时间 2023-05-28 20:28:58作者: lfree
--//昨天测试了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;