Oracle Scratchpad

August 19, 2013

Distributed Queries – 3

Filed under: distributed,Histograms,Oracle — Jonathan Lewis @ 7:25 am BST Aug 19,2013

A comment I’ve made many times in the past about distributed queries is that Oracle doesn’t try to retrieve histogram information from remote databases when optimizing a query. Checking back through previous posts, though, I don’t think I’ve ever mentioned it on the blog – so here’s an example demonstrating the point.

I’ll start with 11.2.0.3, with some fixed system stats (as they could make a difference to repeatability), and the following data set.

begin
	dbms_stats.set_system_stats('MBRC',16);
	dbms_stats.set_system_stats('MREADTIM',10);
	dbms_stats.set_system_stats('SREADTIM',5);
	dbms_stats.set_system_stats('CPUSPEED',1000);
end;
/

create table t1 (
	id,	skew,	padding
)
as
select rownum, r1, rpad('x',400)
from
(
	select /*+ no_merge */
		rownum r1
	from all_objects
	where rownum <= 80
)	v1,
(
	select /*+ no_merge */
		rownum r2
	from all_objects
	where rownum <= 80
)	v2
where r2 <= r1
order by r2,r1
;

alter table t1 modify skew not null;
alter table t1 modify id not null;

create index t1_skew on t1(skew);
create index t1_pk on t1(id);
alter table t1 add constraint t1_pk primary key(id) deferrable;

begin
	dbms_stats.gather_table_stats(
		ownname		 => user,
		tabname		 =>'T1',
		method_opt 	 => 'for all columns size 1 for columns skew size 254'
	);
end;
/

You’ll notice that the skew column is generated from carefully skewed data. I have one row with the value 1, two rows with the value 2, and so on until I end up with 80 rows with the value 80.

With this data in place, check the execution plan for a simple join – joining the table to itself on the id column selecting two different sets of data based on the value of column skew. Here’s the query, with the resulting execution plan from autotrace.


select
	*
from
	t1,
	t1 t2
where
	t1.skew = 5
and	t2.id = t1.id
and	t2.skew = 80
;

-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |     5 |  4080 |    16   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |         |       |       |            |          |
|   2 |   NESTED LOOPS                |         |     5 |  4080 |    16   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T1      |     5 |  2040 |     6   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | T1_SKEW |     5 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN           | T1_PK   |     1 |       |     1   (0)| 00:00:01 |
|*  6 |   TABLE ACCESS BY INDEX ROWID | T1      |     1 |   408 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("T1"."SKEW"=5)
   5 - access("T2"."ID"="T1"."ID")
   6 - filter("T2"."SKEW"=80)

It’s a simple nested loop – for each row of a predicted 5, Oracle will use the t1_pk index to acquire a corresponding row in the second instance of the table and then check the table for the value of column skew.

Now watch what happens to the plan if I make the second instance of the table remote (in this case by simply setting up a loopback database link). I’ve got two versions of this query, one unhinted, and one with the /*+ driving_site(t2) */ hint telling Oracle to execute the query at the remote site (the one holding the table t2 referenced by the hint). If the optimizer had the same information for the distributed versions of the query as it did for the original case we would probably expect to see the same cardinalities and plans in all three cases – though the cost might vary slightly because of some estimated cost of network traffic.


define m_remote='d11g@loopback'

select
	*
from
	t1,
	t1@&m_remote t2
where
	t1.skew = 5
and	t2.id = t1.id
and	t2.skew = 80
;

--------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     5 |  3180 |    11  (10)| 00:00:01 |        |      |
|*  1 |  HASH JOIN                   |         |     5 |  3180 |    11  (10)| 00:00:01 |        |      |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1      |     5 |  2040 |     6   (0)| 00:00:01 |        |      |
|*  3 |    INDEX RANGE SCAN          | T1_SKEW |     5 |       |     1   (0)| 00:00:01 |        |      |
|   4 |   REMOTE                     | T1      |    41 |  9348 |     4   (0)| 00:00:01 | D11G@~ | R->S |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T2"."ID"="T1"."ID")
   3 - access("T1"."SKEW"=5)

Remote SQL Information (identified by operation id):
----------------------------------------------------
   4 - SELECT "ID","SKEW","PADDING" FROM "T1" "T2" WHERE "SKEW"=80 (accessing 'D11G@LOOPBACK' )

select
	/*+ driving_site(t2) */
	*
from
	t1,
	t1@&m_remote t2
where
	t1.skew = 5
and	t2.id = t1.id
and	t2.skew = 80
;

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE|      |    40 | 25440 |    30   (4)| 00:00:01 |        |      |
|*  1 |  HASH JOIN             |      |    40 | 25440 |    30   (4)| 00:00:01 |        |      |
|   2 |   REMOTE               | T1   |    41 |  9348 |     4   (0)| 00:00:01 |      ! | R->S |
|*  3 |   TABLE ACCESS FULL    | T1   |    80 | 32640 |    25   (0)| 00:00:01 |   D11G |      |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("A1"."ID"="A2"."ID")
   3 - filter("A1"."SKEW"=80)

Remote SQL Information (identified by operation id):
----------------------------------------------------
   2 - SELECT "ID","SKEW","PADDING" FROM "T1" "A2" WHERE "SKEW"=5 (accessing '!' )

The cardinality error in the first distributed join is easy to see – Oracle’s estimate for the remote predicate “skew = 80″ is 41, which is exactly what we would expect if we dropped the histogram. Clearly the histogram hasn’t been pulled to the local database.

Similarly, though the mental gymnastics may be a little harder, the same cardinality error appears in the second distributed join – the “remote” database sends the “local” database a query with the predicate “skew = 5″ expecting to get back 41 rows, the average number of rows per value. The remote database didn’t ask for the histogram data before optimizer the join.

It’s interesting to note, by the way, that when I ran this test on 12c a couple of the plans were reported as adaptive – allowing Oracle to switch between nested loops and hash joins as the query progressed. Distributed queries tend to be a bit of a problem at the best of times – maybe adaptive plans will reduce the amount of time we need to spend re-engineering them.

[Further reading on distributed databases]

Leave a Comment »

No comments yet.

RSS feed for comments on this post.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,507 other followers