[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生效。