Hi Guys,
Last week I was asked to take a look in to one performance problem where customer DBA reported excessive ‘SQL*Net message from dblink’ in the system. As I don’t have direct access to the box, I asked him to share both AWR and ASH performance reports for the time when they observe the problem. And yes, he was right, there were few waits on this event but with very high average elapsed times or single execution times. Next I verified the ASH data for the period and I was able to identify this SQL (SELECT) which is frequently waiting on this wait tvent.
About this event, as you guys know SQL Net messages are classified as Network classed events and happens when some part of the final dataset is coming from a remote database which is accessed via DBLink. Alright, so we know the problem and we have the problematic SQL, let’s simulate the problem and the solution.
I will first create the DBLink that I will be using to access the remote data which is present in a different database on a different server/host and next I will create the sample table with some test data both on Site A and Site B.
Reference used:
BIGTAB: Table on Site 1, local site.
BIGTAB2: Table on site 2, remote location
SQL>
SQL> CREATE DATABASE LINK dixitdlink1
2 CONNECT TO dixit IDENTIFIED BY oracle90
USING '(DESCRIPTION=
(ADDRESS = (PROTOCOL = TCP)(HOST = canttowinsec.quebecdomain)(PORT = 1521))
(CONNECT_DATA=(SERVICE_NAME=dhavaldb.quebecdomain))
)';
Database link created.
SQL>
-- On source database, on local host.
CREATE TABLE BIGTAB (
id NUMBER,
weight NUMBER,
adate DATE
);
Table created.
INSERT INTO BIGTAB (id, weight, adate)
SELECT MOD(ROWNUM,1000),
DBMS_RANDOM.RANDOM,
SYSDATE-1000+DBMS_RANDOM.VALUE(0,1000)
FROM all_objects
/
67447 rows created.
SQL> delete from prashant.BIGTAB where rownum < 60000;
59999 rows deleted.
-- This I have created on target database, on a remote host.
CREATE TABLE BIGTAB2 (
id NUMBER,
weight NUMBER,
adate DATE
);
Table created.
INSERT INTO BIGTAB2 (id, weight, adate)
SELECT MOD(ROWNUM,1000),
DBMS_RANDOM.RANDOM,
SYSDATE-1000+DBMS_RANDOM.VALUE(0,1000)
FROM all_objects
/
67447 rows created.
Alright, the test data is created on both the sites, and in order to mimic the exact case that I’d faced, I have intentionally deleted 59999 table from table that exists in site 1. So now we have table BIGTAB with 7735 records and on remote database we have a table with name BIGTAB2 with 67447 rows. Let’s execute few queries and do a Inner Join on both the tables.
Test 1: With no hint or tuning/tweaking of any kind. Asking to bring all data from remote site here to local site and then perform the join operation or this can also be done if we put the hint and mention name of the local table, or table that is small i.e. /*+ DRIVING_SITE(BIGTAB) */
SQL> select * from prashant.BIGTAB, dixit.BIGTAB2@dixitdlink1 where ADATE=DATEOFREGISTER;
ID WEIGHT ADATE ID WEIGHT DATEOFREG
---------- ---------- --------- ---------- ---------- ---------
502 -1.363E+09 09-MAR-20 989 1353403341 09-MAR-20
577 -185863752 22-FEB-21 247 -626839962 22-FEB-21
........
............
...............
.................
Execution Plan
----------------------------------------------------------
Plan hash value: 2705338834
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19931 | 1362K| 144 (0)| 00:00:01 | | |
|* 1 | HASH JOIN | | 19931 | 1362K| 144 (0)| 00:00:01 | | |
| 2 | TABLE ACCESS FULL| BIGTAB | 6948 | 237K| 68 (0)| 00:00:01 | | |
| 3 | REMOTE | BIGTAB2 | 19930 | 681K| 76 (0)| 00:00:01 | DIXIT~ | R->S |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ADATE"="DATEOFREGISTER")
Remote SQL Information (identified by operation id):
----------------------------------------------------
3 - SELECT "ID","WEIGHT","DATEOFREGISTER" FROM "DIXIT"."BIGTAB2" "BIGTAB2"
(accessing 'DIXITDLINK1.ONTADOMAIN' )
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
So, this was happening there on that day, on that system with that query waiting on ‘SQL*Net message from dblink‘, now this is the right time I should introduce the DRIVING_SITE hint, I have been trying and experimenting with this hint for a long time now, maybe since my 9i days.
This hint is useful for distributed queries to force optimizer to use a particular site as a driving site, or it instructs the optimizer to execute the query at a different site than that selected by the database. Like in previous test case all rows from remote host which matches the condition are sent to the local site and the join is finally executed on the local site.
Now, I am going to use the DRIVING_SITE (will mention name of the remote table) and force the optimizer to perform all join operations at the remote site, hence the query will be executed there and the result set is returned to the local site.
So, you should try with the DRIVING_SITE hint on the site where the huge table resides!
Test 2: With DRIVING_HINT on remote table BIGTAB2 which is big and has 67447 records.
SQL> select /*+ DRIVING_SITE(BIGTAB2) */ * from prashant.BIGTAB, dixit.BIGTAB2@dixitdlink1 where ADATE=DATEOFREGISTER;
ID WEIGHT ADATE ID WEIGHT DATEOFREG
---------- ---------- --------- ---------- ---------- ---------
502 -1.363E+09 09-MAR-20 989 1353403341 09-MAR-20
577 -185863752 22-FEB-21 247 -626839962 22-FEB-21
.....
.......
........
..........
Execution Plan
----------------------------------------------------------
Plan hash value: 2214131741
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT REMOTE| | 82 | 5740 | 70 (0)| 00:00:01 | | |
|* 1 | HASH JOIN | | 82 | 5740 | 70 (0)| 00:00:01 | | |
| 2 | REMOTE | BIGTAB | 82 | 2870 | 2 (0)| 00:00:01 | ! | R->S |
| 3 | TABLE ACCESS FULL | BIGTAB2 | 53126 | 1815K| 68 (0)| 00:00:01 | DHAVA~ | |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A2"."ADATE"="A1"."DATEOFREGISTER")
Remote SQL Information (identified by operation id):
----------------------------------------------------
2 - SELECT "ID","WEIGHT","ADATE" FROM "PRASHANT"."BIGTAB" "A2" (accessing '!' )
Note
-----
- fully remote statement
- dynamic statistics used: dynamic sampling (level=2)
Look at the cost, rows processed and bytes (data processed), all of them are reduced by a great extent. Of course not much elapsed time difference you see here in this example as the dataset is small and is a less complex query, you can try it yourself and see the magic!
Hope It Helped!
Prashant Dixit
Tales From A Lazy Fat DBA 