[20210621]Driving site patch.txt
[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生效。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2777871/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Hints : DRIVING_SITE
- Oracle hint之DRIVING_SITEOracle
- DRIVING_SITE Hint (R1)
- zt_Oracle hint driving_site Hint的用法Oracle
- DBLINK HINT /*+ driving_site(org) */ 最佳化策略和思路
- DB Link因 driving_site導致的效能問題
- [20150810]關於提示DRIVING_SITE.txt
- driving_site:Oracle 跨庫關聯資料時速度問題Oracle
- 分散式資料庫查詢中 DRIVING_SITE 的疑問分散式資料庫
- Site-to-Site *** Static map薦
- Kalman Filters( self-driving)Filter
- site命令
- Sql最佳化(十四)分散式環境中的最佳化(2)選擇合適的驅動節點(driving site hint)SQL分散式
- Valuable site on githubGithub
- backup site collection
- c++ siteC++
- Fan's Site
- JSON official siteJSON
- deploy-your-site
- translate-your-site
- [20210621]plsql_code_type=native.txtSQL
- Speed Up Your WordPress Site
- Case two -- Recover a datafile in primary site from the backup taken in standby site without using c
- Case One -- Recover a datafile in primary site from the backup taken in standby site using catalog d
- Good site on Oracle tech blogGoOracle
- idapython import 'site' failedPythonImportAI
- 2017 CCPC Qinhuangdao Site
- EverQuest Buy fifa coins site launched
- [20181007]12cR2 Using SQL Patch.txtSQL
- How can I add a site title refiner
- Script to Collect Data Guard Primary Site Diagnostic InformationORM
- How to Prevent Cross-Site Scripting AttacksROS
- 3: Android Developer Site映象網站AndroidDeveloper網站
- 急聘--Senior JAVA Engineer(on-site HP)-- ShanghaiJavaAI
- Best Practices for Speeding up Your Web SiteWeb
- SAP Spartacus Multi-Site Configuration
- How To Search and Restore files from Site Collection Recycle BinREST
- [Drupal] multi site with subdomain running the same code in DrupalAI