在使用dblink访问远端表的时候,查询或者结合等操作大多会在远端数据库执行,
当发生性能问题的时候,如何收集本地语句和远端语句的10046 trace呢?
例子:
SQL> select * from T1@DBLINKR1201;
ID
----------
1
DBLINK的定义
SQL> col OWNER format a21
col DB_LINK format a28
col USERNAME format a10
col HOST format a10
SELECT OWNER,DB_LINK,USERNAME,HOST FROM DBA_DB_LINKS;SQL> SQL> SQL> SQL>
OWNER DB_LINK USERNAME HOST
--------------------- ---------------------------- ---------- ----------
TEST DBLINKR1201 TEST R1201
首先在远端创建两个PROCEDURE,一个用来收集trace,一个用来关闭。
CREATE OR REPLACE PROCEDURE trace_on
as
begin
dbms_session.set_sql_trace(TRUE);
end;
/
CREATE OR REPLACE PROCEDURE trace_off
as
begin
dbms_session.set_sql_trace(FALSE);
end;
/
然后赋予远端用户ALTER SESSION的权限。
GRANT ALTER SESSION TO test;
回到本地端开启收集。
EXECUTE trace_on@DBLINKR1201
执行分布式语句
这个时候可以同时收集本地查询的10046.
SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.
SQL> select * from T1@DBLINKR1201;
ID
----------
1
然后关闭收集。
EXECUTE trace_off@DBLINKR1201
到本地和远端数据库的trace下面看看生成的10046.
这里我们直接用tkprof进行了解析。
本地处理的执行计划:
SQL ID: ftfzuyqn49g71 Plan Hash: 861941715
select *
from
T1@DBLINKR1201 A,T2 B where A.id = B.id
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 7 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.01 0 8 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 109
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 HASH JOIN (cr=7 pr=0 pw=0 time=5455 us starts=1 cost=6 size=10634 card=409)
1 1 1 TABLE ACCESS FULL T2 (cr=7 pr=0 pw=0 time=87 us starts=1 cost=3 size=13 card=1)
1 1 1 REMOTE T1 (cr=0 pr=0 pw=0 time=4974 us starts=1 cost=3 size=5317 card=409) <--- 这里表示这部分处理在remote端执行。
远端数据库该处理的执行计划:
SQL ID: b4ad1u4wupw2m Plan Hash: 3617692013
SELECT "ID"
FROM
"T1" "A"
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 7 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 8 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 103
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 TABLE ACCESS FULL T1 (cr=7 pr=0 pw=0 time=20 us cost=3 size=13 card=1)
********************************************************************************
我们看到对于远端数据库的T1表进行了全表扫描。