[20210621]Driving site patch.txt

来源：这里教程网时间：2026-03-03 16:45:58 作者：

[20210621]Driving site patch.txt --//昨天看了以上链接,我感兴趣的是加入driving_site提示后看执行计划.自己重复测试看看. 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 CREATE PUBLIC DATABASE LINK LOOPBACK USING 'localhost:1521/book'; define m_target=loopback execute sys.dbms_sqldiag.drop_sql_patch('driving_site'); --//drop table t1 purge ; --//drop table t2 purge ; create table t1 as select * from all_objects where rownum <= 10000 ; alter table t1 add constraint t1_pk primary key (object_id); create table t2 as select * from all_objects where rownum <= 10000 ; begin dbms_stats.gather_table_stats( ownname => null, tabname => 'T1', method_opt => 'for all columns size 1' ); dbms_stats.gather_table_stats( ownname => null, tabname => 'T2', method_opt => 'for all columns size 1 for columns owner size 254' ); end; / 2.测试: SCOTT@book> alter session set statistics_level = all; Session altered. select t1.object_name, t1.object_type, t2.object_name, t2.object_type from t1, t2@&m_target t2 where t2.object_id = t1.object_id and t2.owner = 'OUTLN' / SCOTT@book> @ dpc '' outline PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 8sqzk6bcr650v, child number 0 ------------------------------------- select t1.object_name, t1.object_type, t2.object_name, t2.object_type from t1, t2@loopback t2 where t2.object_id = t1.object_id and t2.owner = 'OUTLN' Plan hash value: 2842506388 ---------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Inst |IN-OUT| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 66 (100)| | | | 8 |00:00:00.01 | 134 | | | | |* 1 | HASH JOIN | | 1 | 2000 | 173K| 66 (0)| 00:00:01 | | | 8 |00:00:00.01 | 134 | 1301K| 1301K| 893K (0)| | 2 | REMOTE | T2 | 1 | 2000 | 113K| 26 (0)| 00:00:01 | LOOPB~ | R->S | 8 |00:00:00.01 | 0 | | | | | 3 | TABLE ACCESS FULL| T1 | 1 | 10000 | 302K| 40 (0)| 00:00:01 | | | 10000 |00:00:00.01 | 134 | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / T2@SEL$1 3 - SEL$1 / T1@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.4') DB_VERSION('11.2.0.4') ALL_ROWS OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "T2"@"SEL$1") FULL(@"SEL$1" "T1"@"SEL$1") LEADING(@"SEL$1" "T2"@"SEL$1" "T1"@"SEL$1") USE_HASH(@"SEL$1" "T1"@"SEL$1") END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("T2"."OBJECT_ID"="T1"."OBJECT_ID") Remote SQL Information (identified by operation id): ---------------------------------------------------- 2 - SELECT "OWNER","OBJECT_NAME","OBJECT_ID","OBJECT_TYPE" FROM "T2" "T2" WHERE "OWNER"='OUTLN' (accessing 'LOOPBACK' ) --//T2表实际返回8行，而估计返回2000行，与原作者测试不同。不过也一样说明问题。 3.加入提示: select /*+ gather_plan_statistics driving_site(t2) */ t1.object_name, t1.object_type, t2.object_name, t2.object_type from t1, t2@&m_target t2 where t2.object_id = t1.object_id and t2.owner = 'OUTLN' / SCOTT@book> @ dpc '' outline PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------ SQL_ID 2sp4912y0uvdz, child number 0 select /*+ gather_plan_statistics driving_site(t2) */ t1.object_name, t1.object_type, t2.object_name, t2.object_type from t1, t2@loopback t2 where t2.object_id = t1.object_id and t2.owner = 'OUTLN' NOTE: cannot fetch plan for SQL_ID: 2sp4912y0uvdz, CHILD_NUMBER: 0 Please verify value of SQL_ID and CHILD_NUMBER; It could also be that the plan is no longer in cursor cache (check v$sql_plan) 11 rows selected. --//加入提示后，在远端执行，这样无法这样的方式查看执行计划。 SCOTT@book> set linesize 132 SCOTT@book> column sql_text wrap word format a75 SCOTT@book> select sql_id, sql_text from V$sql where sql_text like '%OUTLN%' ; SQL_ID SQL_TEXT ------------- --------------------------------------------------------------------------- 8sqzk6bcr650v select t1.object_name, t1.object_type, t2.object_name, t2.object_type from t1, t2@loopback t2 where t2.object_id = t1.object_id and t2.owner = 'OUTLN' 5hmjcxgt0jc8t SELECT "A2"."OBJECT_NAME","A2"."OBJECT_TYPE","A1"."OBJECT_NAME","A1"."OBJECT_TYPE" FROM "T1"@! "A2","T2" "A1" WHERE "A1"."OBJECT_ID"="A2"."OBJECT_ID" AND "A1"."OWNER"='OUTLN' 2sp4912y0uvdz select /*+ gather_plan_statistics driving_site(t2) */ t1.object_name, t1.object_type, t2.object_name, t2.object_type from t1, t2@loopback t2 where t2.object_id = t1.object_id and t2.owner = 'OUTLN' 7d9arad2muwqa select /*+ driving_site(t2) */ t1.object_name, t1.object_type, t2.object_name, t2.object_type from t1, t2@loopback t2 where t2.object_id = t1.object_id and t2.owner = 'OUTLN' gqtc03nug4uvb SELECT "OWNER","OBJECT_NAME","OBJECT_ID","OBJECT_TYPE" FROM "T2" "T2" WHERE "OWNER"='OUTLN' 2wbvdvt3a9kwp select sql_id, sql_text from V$sql where sql_text like '%OUTLN%' 6 rows selected. --//应该查看sql_id=5hmjcxgt0jc8t的执行计划。 SCOTT@book> @ dpc 5hmjcxgt0jc8t outline PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 5hmjcxgt0jc8t, child number 0 ------------------------------------- SELECT "A2"."OBJECT_NAME","A2"."OBJECT_TYPE","A1"."OBJECT_NAME","A1"."OB JECT_TYPE" FROM "T1"@! "A2","T2" "A1" WHERE "A1"."OBJECT_ID"="A2"."OBJECT_ID" AND "A1"."OWNER"='OUTLN' Plan hash value: 3485226535 -------------------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Inst |IN-OUT| -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 48 (100)| | | | | 1 | NESTED LOOPS | | 8 | 624 | 48 (0)| 00:00:01 | | | |* 2 | TABLE ACCESS FULL| T2 | 8 | 296 | 40 (0)| 00:00:01 | | | | 3 | REMOTE | T1 | 1 | 41 | 1 (0)| 00:00:01 | ! | R->S | -------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / A1@SEL$1 3 - SEL$1 / A2@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.4') DB_VERSION('11.2.0.4') ALL_ROWS OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "A1"@"SEL$1") FULL(@"SEL$1" "A2"@"SEL$1") LEADING(@"SEL$1" "A1"@"SEL$1" "A2"@"SEL$1") USE_NL(@"SEL$1" "A2"@"SEL$1") END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("A1"."OWNER"='OUTLN') Remote SQL Information (identified by operation id): ---------------------------------------------------- 3 - SELECT "OBJECT_NAME","OBJECT_ID","OBJECT_TYPE" FROM "T1" "A2" WHERE :1="OBJECT_ID" (accessing '!' ) Note ----- - Warning: basic plan statistics not available. These are only collected when: * hint 'gather_plan_statistics' is used for the statement or * parameter 'statistics_level' is set to 'ALL', at session or system level --//这样提示丢失了。 4.测试加入补丁: --//我的测试环境必须以sys用户执行。 declare v_sql CLOB; begin select distinct sql_text into v_sql from v$sql where sql_id='&sql_id'; --// sys.dbms_sqldiag.create_sql_patch( sys.dbms_sqldiag_internal.i_create_patch( sql_text => v_sql, hint_text => 'driving_site(t2@sel$1))', name => 'driving_site'); end; / --//输入sql_id=8sqzk6bcr650v SYS@book> select name, status, created, sql_text from dba_sql_patches; NAME STATUS CREATED SQL_TEXT ------------ -------- -------------------------- ------------------------------------------------------------ driving_site ENABLED 2021-06-22 09:11:55.000000 select t1.object_name, t1.object_type, t2.object _name, t2.object_type from t1, t2@loopback t2 where t2.object_id = t1.object_id and t2.owner = 'O UTLN' select t1.object_name, t1.object_type, t2.object_name, t2.object_type from t1, t2@&m_target t2 where t2.object_id = t1.object_id and t2.owner = 'OUTLN' / SCOTT@book> @ dpc '' outline PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------- SQL_ID 8sqzk6bcr650v, child number 0 select t1.object_name, t1.object_type, t2.object_name, t2.object_type from t1, t2@loopback t2 where t2.object_id = t1.object_id and t2.owner = 'OUTLN' NOTE: cannot fetch plan for SQL_ID: 8sqzk6bcr650v, CHILD_NUMBER: 0 Please verify value of SQL_ID and CHILD_NUMBER; It could also be that the plan is no longer in cursor cache (check v$sql_plan) 10 rows selected. --//说明提示生效。执行前面比较： SYS@book> select sql_id, sql_text,executions from V$sql where sql_text like '%OUTLN%' ; SQL_ID SQL_TEXT EXECUTIONS ------------- ------------------------------------------------------------ ---------- 8sqzk6bcr650v select t1.object_name, t1.object_type, t2.object 1 _name, t2.object_type from t1, t2@loopback t2 where t2.object_id = t1.object_id and t2.owner = 'O UTLN' 3207j4v6rzu2d select sql_id, sql_text,executions from V$sql where sql_text 1 like '%OUTLN%' 5hmjcxgt0jc8t SELECT "A2"."OBJECT_NAME","A2"."OBJECT_TYPE","A1"."OBJECT_NA 11 ME","A1"."OBJECT_TYPE" FROM "T1"@! "A2","T2" "A1" WHERE "A1" ."OBJECT_ID"="A2"."OBJECT_ID" AND "A1"."OWNER"='OUTLN' --//执行 SYS@book> select sql_id, sql_text,executions from V$sql where sql_text like '%OUTLN%' ; SQL_ID SQL_TEXT EXECUTIONS ------------- ------------------------------------------------------------ ---------- 8sqzk6bcr650v select t1.object_name, t1.object_type, t2.object 2 _name, t2.object_type from t1, t2@loopback t2 where t2.object_id = t1.object_id and t2.owner = 'O UTLN' 3207j4v6rzu2d select sql_id, sql_text,executions from V$sql where sql_text 1 like '%OUTLN%' 5hmjcxgt0jc8t SELECT "A2"."OBJECT_NAME","A2"."OBJECT_TYPE","A1"."OBJECT_NA 12 ME","A1"."OBJECT_TYPE" FROM "T1"@! "A2","T2" "A1" WHERE "A1" ."OBJECT_ID"="A2"."OBJECT_ID" AND "A1"."OWNER"='OUTLN' --//可以发现5hmjcxgt0jc8t的执行次数增加。说明打的sql patch生效。