Let’s imagine the following situation – you have a table called EMPLOYEES_TMP which is a global temporary table with a variable number of rows – totally nondeterministic. And there is a query which runs very often to compare primary key values between this table and a remote one (which is basically the same but not temporary).
select count(1)
from employees_tmp e1
where not exists (select 1
from employees@db11g e2
where e2.employee_id=e1.employee_id);
And the query sometimes run very fast and other times – very very slow. I had a similar situation last week with one of my customers.
Well, actually the case is quite simple here – let’s see the execution plan:
SQL> select * from table(dbms_xplan.display_cursor('70cmy0u5kpjhn',0,'all'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 70cmy0u5kpjhn, child number 0
-------------------------------------
select count(1) from employees_tmp e1 where not exists (select 1
from employees@db11g e2 where
e2.employee_id=e1.employee_id)
Plan hash value: 3650570951
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| | | |
| 1 | SORT AGGREGATE | | 1 | 13 | | | | |
|* 2 | FILTER | | | | | | | |
| 3 | TABLE ACCESS FULL| EMPLOYEES_TMP | 1 | 13 | 2 (0)| 00:00:01 | | |
| 4 | REMOTE | EMPLOYEES | 1 | 13 | 0 (0)| | DB11G | R->S |
-----------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / E1@SEL$1
4 - SEL$2 / E2@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( IS NULL)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
3 - "E1"."EMPLOYEE_ID"[NUMBER,22]
4 - "E2"."EMPLOYEE_ID"[NUMBER,22]
Remote SQL Information (identified by operation id):
----------------------------------------------------
4 - SELECT "EMPLOYEE_ID" FROM "EMPLOYEES" "A1" WHERE "EMPLOYEE_ID"=:1 (accessing 'DB11G' )
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
47 rows selected.
OK so we can see that for each row in the local temporary table (EMPLOYEES_TMP – E1@SEL$1) the subquery will be executed (SEL$2).
We can actually check the number of socket reads and writes by this process server, while executing this query for different number of rows in the temporary table.
The process has opened a socket for dblink communication:
[root@rico ~]# ls -al /proc/12925/fd | grep socket lrwx------. 1 oracle oinstall 64 01-13 20:06 8 -> socket:[41123]
File descriptor for the socket is 8 – we will use it with strace.
- 0 rows in EMPLOYEES_TMP – 1108 rows in EMPLOYEES@DB11g
[root@rico ~]# strace -p 12925 -o /tmp/hr_trace Process 12925 attached ^CProcess 12925 detached [root@rico ~]# grep -c "read(8," /tmp/hr_trace 0 [root@rico ~]# vim /tmp/hr_trace
- 100 rows in EMPLOYEES_TMP – 1108 rows in EMPLOYEES@DB11g
[root@rico ~]# strace -p 12925 -o /tmp/hr_trace Process 12925 attached ^CProcess 12925 detached [root@rico ~]# grep -c "read(8," /tmp/hr_trace 106 [root@rico ~]# grep -c "write(8," /tmp/hr_trace 106 [root@rico ~]#
- 201 rows in EMPLOYEES_TMP – 1108 rows in EMPLOYEES@DB11g
[root@rico ~]# strace -p 12925 -o /tmp/hr_trace Process 12925 attached ^CProcess 12925 detached [root@rico ~]# strace -p 12925 -o /tmp/hr_trace Process 12925 attached ^CProcess 12925 detached [root@rico ~]# grep -c "read(8," /tmp/hr_trace 308
- 500 rows in EMPLOYEES_TMP – 1108 rows in EMPLOYEES@DB11g
[root@rico ~]# strace -p 12925 -o /tmp/hr_trace Process 12925 attached ^CProcess 12925 detached [root@rico ~]# grep -c "read(8," /tmp/hr_trace 764 [root@rico ~]# grep -c "write(8," /tmp/hr_trace 764
- 14621 rows in EMPLOYEES_TMP – 1108 rows in EMPLOYEES@DB11g
[root@rico ~]# strace -p 12925 -o /tmp/hr_trace Process 12925 attached ^CProcess 12925 detached [root@rico ~]# grep -c "read(8," /tmp/hr_trace 15419 [root@rico ~]# grep -c "write(8," /tmp/hr_trace 15419
So as you can see – it doesn’t scale good. But this should be obvious just by looking on the explain plan – strace here is just for fun 😉
How can we change the query? First of all we have to be sure, that there will be no nested loops in the explain plan because that changes nothing.
SQL Plan with nested loops:
----------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT| ----------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 52 (100)| | | | | 1 | SORT AGGREGATE | | 1 | 26 | | | | | | 2 | NESTED LOOPS ANTI | | 16127 | 409K| 52 (25)| 00:00:01 | | | | 3 | TABLE ACCESS FULL| EMPLOYEES_TMP | 16127 | 204K| 39 (0)| 00:00:01 | | | | 4 | REMOTE | EMPLOYEES | 1 | 13 | 0 (0)| | DB11G | R->S | -----------------------------------------------------------------------------------------------------
For this plan, strace shows:
[root@rico ~]# strace -p 12925 -o /tmp/hr_trace Process 12925 attached ^CProcess 12925 detached [root@rico ~]# grep -c "read(8," /tmp/hr_trace 16137
So its even worse! Let’s try to force unnesting but with hash join – the query would look like this:
select /*+ qb_name(local) UNNEST(@remote) USE_HASH(e1@local e2@remote) */ count(1)
from employees_tmp e1
where not exists (select /*+ qb_name(remote) */ 1
from employees@db11g e2
where e2.employee_id=e1.employee_id);
Explain plan:
------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT| ------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 40 (100)| | | | | 1 | SORT AGGREGATE | | 1 | 26 | | | | | |* 2 | HASH JOIN RIGHT ANTI| | 16127 | 409K| 40 (0)| 00:00:01 | | | | 3 | REMOTE | EMPLOYEES | 107 | 1391 | 1 (0)| 00:00:01 | DB11G | R->S | | 4 | TABLE ACCESS FULL | EMPLOYEES_TMP | 16127 | 204K| 39 (0)| 00:00:01 | | | -------------------------------------------------------------------------------------------------------
In the STRACE we can see huge improvement!
[root@rico ~]# strace -p 12925 -o /tmp/hr_trace Process 12925 attached ^CProcess 12925 detached [root@rico ~]# grep -c "read(8," /tmp/hr_trace 5 [root@rico ~]# grep -c "write(8," /tmp/hr_trace 4
Reducing the number of SQL*Net socket reads and writes was the solution. But what to do if we can’t change the SQL text? There are a few methods – for example SQLPatch:
1 declare 2 v_sql_text clob; 3 begin 4 select sql_fulltext into v_sql_text 5 from v$sql 6 where sql_id='70cmy0u5kpjhn' 7 and child_number=0; 8 dbms_sqldiag_internal.I_CREATE_PATCH(SQL_TEXT=>v_sql_text, 9 HINT_TEXT=>'UNNEST(@SEL$2) USE_HASH(e1@SEL$1 e2@SEL$2)', 10 NAME=>'KSTAWIARSKI_UNNEST_HASH'); 11* end; SQL> / PL/SQL procedure successfully completed.
Now we can run our SQL even in changed form 🙂
SQL> ;
1 select COUNT(1)
2 from employees_tmp e1
3* where not exists (select 1 from employees@db11g e2 where e2.employee_id=e1.employee_id)
SQL> set autot trace
SQL> /
Execution Plan
----------------------------------------------------------
Plan hash value: 2645133600
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 40 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 26 | | | | |
|* 2 | HASH JOIN RIGHT ANTI| | 16127 | 409K| 40 (0)| 00:00:01 | | |
| 3 | REMOTE | EMPLOYEES | 107 | 1391 | 1 (0)| 00:00:01 | DB11G | R->S |
| 4 | TABLE ACCESS FULL | EMPLOYEES_TMP | 16127 | 204K| 39 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("E2"."EMPLOYEE_ID"="E1"."EMPLOYEE_ID")
Remote SQL Information (identified by operation id):
----------------------------------------------------
3 - SELECT /*+ USE_HASH ("E2") */ "EMPLOYEE_ID" FROM "EMPLOYEES" "E2" (accessing 'DB11G' )
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- SQL patch "KSTAWIARSKI_UNNEST_HASH" used for this statement