Saturday, December 19, 2009

TEMP Table Transformation and Remote Procedure Calls

I've recently come across a interesting side-effect regarding temp table transformations at one of my clients.

There was a PL/SQL package procedure that worked fine when called locally but somehow "hung" when being called from a remote database - all it did was to call exactly the same package procedure with the same parameters as the local call, but one of the SQL statements executed as part of the procedure generated an suboptimal execution plan that never completed.

Further investigations revealed that the significant difference between the execution plan of the local and the remote execution of the procedure was the different treatment of a contained "WITH" clause.

The interesting point is that the procedure called itself didn't perform any "distributed" queries or DML - the only difference was that one time the procedure got called locally, and one time remotely per database link. All processing within the procedure was local - no activities using database links were involved.

There are (at least) two known areas where Oracle can optionally use a so called TEMP TABLE TRANSFORMATION as part of the execution plan:

1. Materialization of a Subquery Factoring, also known as "Common Table Expression" or simply "WITH clause"

Oracle uses this when the subquery is used more than once in the execution plan, or if forced with the undocumented MATERIALIZE hint as part of the SELECT in the WITH clause. There are a few (not really documented) limitations of this materialization, in particular if LOBs or LONGs are part of the projection then this TEMP TABLE transformation can't get used.

2. Star transformation with TEMP TABLE transformation

Star transformations can also make use of the TEMP TABLE transformation. This is enabled by default when STAR_TRANSFORMATION_ENABLED is set to TRUE, but can be disabled by setting STAR_TRANSFORMATION_ENABLED to TEMP_DISABLE.


The following testcase shall demonstrate the subtle side effect of the Remote Procedure Call via Database Link.

Consider the following simple package:


create or replace package pkg_test_materialize_remote authid current_user as
procedure test_star_transform_statement;
procedure test_simple_statement;
end pkg_test_materialize_remote;
/

create or replace package body pkg_test_materialize_remote as
procedure test_star_transform_statement as
c sys_refcursor;
n1 number;
c1 varchar2(255);
c2 varchar2(255);
begin
execute immediate 'alter session set star_transformation_enabled = true';

open c for '
select
sum(quantity_sold),
p.prod_subcategory_desc,
c.cust_gender
from
sh.sales s
join sh.products p ON (s.prod_id = p.prod_id)
join sh.customers c ON (s.cust_id = c.cust_id)
join sh.countries d ON (c.country_id = d.country_id)
where
p.prod_subcategory_desc = ''Memory'' and
c.cust_city = ''Oxford'' and
c.cust_gender = ''F''
group by
p.prod_subcategory_desc, c.cust_gender';

fetch c into n1, c1, c2;

close c;
end test_star_transform_statement;

procedure test_simple_statement as
begin
for rec in (
with a as (
select /*+ materialize */
*
from
dual
)
select
*
from
a
where
1 = 2
) loop
null;
end loop;
end test_simple_statement;
end pkg_test_materialize_remote;
/


The simple statement is not really useful but probably the simplest form of requesting a TEMP TABLE transformation.

The statement using star transformation generates a TEMP TABLE transformation when using the default demo SH setup. I've only wrapped it in dynamic SQL to take advantage of any enabled roles due to the AUTHID CURRENT_USER clause.

And indeed when calling the two procedures locally like the following:


exec pkg_test_materialize_remote.test_simple_statement

exec pkg_test_materialize_remote.test_star_transform_statement


You get these execution plans both using TEMP TABLE transformations:


SQL_ID cdudvb233tdau, child number 0
-------------------------------------
WITH A AS ( SELECT /*+ materialize */ * FROM DUAL ) SELECT * FROM A
WHERE 1 = 2

Plan hash value: 1137659336

-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | | | | | |
| 3 | TABLE ACCESS FULL | DUAL | 1 | 2 | 2 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
| 5 | VIEW | | 1 | 2 | 2 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6617_E0AEB | 1 | 2 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - filter(NULL IS NOT NULL)


SQL_ID 5bp3rpfs2f4bw, child number 0
-------------------------------------
select sum(quantity_sold),
p.prod_subcategory_desc, c.cust_gender from
sh.sales s join sh.products p ON (s.prod_id = p.prod_id)
join sh.customers c ON (s.cust_id = c.cust_id) join
sh.countries d ON (c.country_id = d.country_id) where
p.prod_subcategory_desc = 'Memory' and c.cust_city = 'Oxford'
and c.cust_gender = 'F' group by
p.prod_subcategory_desc, c.cust_gender

Plan hash value: 3883759770

------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 435 (100)| | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | |
| 2 | LOAD AS SELECT | | | | | | | |
|* 3 | TABLE ACCESS FULL | CUSTOMERS | 45 | 990 | 405 (1)| 00:00:05 | | |
| 4 | HASH GROUP BY | | 2 | 94 | 30 (10)| 00:00:01 | | |
|* 5 | HASH JOIN | | 27 | 1269 | 24 (9)| 00:00:01 | | |
|* 6 | HASH JOIN | | 27 | 1134 | 22 (5)| 00:00:01 | | |
|* 7 | HASH JOIN | | 27 | 810 | 20 (5)| 00:00:01 | | |
|* 8 | TABLE ACCESS FULL | PRODUCTS | 3 | 54 | 3 (0)| 00:00:01 | | |
| 9 | PARTITION RANGE ALL | | 31 | 372 | 16 (0)| 00:00:01 | 1 | 28 |
| 10 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 31 | 372 | 16 (0)| 00:00:01 | 1 | 28 |
| 11 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
| 12 | BITMAP AND | | | | | | | |
| 13 | BITMAP MERGE | | | | | | | |
| 14 | BITMAP KEY ITERATION | | | | | | | |
| 15 | BUFFER SORT | | | | | | | |
|* 16 | TABLE ACCESS FULL | PRODUCTS | 3 | 54 | 3 (0)| 00:00:01 | | |
|* 17 | BITMAP INDEX RANGE SCAN | SALES_PROD_BIX | | | | | 1 | 28 |
| 18 | BITMAP MERGE | | | | | | | |
| 19 | BITMAP KEY ITERATION | | | | | | | |
| 20 | BUFFER SORT | | | | | | | |
| 21 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6616_E0AEB | 45 | 225 | 2 (0)| 00:00:01 | | |
|* 22 | BITMAP INDEX RANGE SCAN | SALES_CUST_BIX | | | | | 1 | 28 |
| 23 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6616_E0AEB | 45 | 540 | 2 (0)| 00:00:01 | | |
| 24 | INDEX FULL SCAN | COUNTRIES_PK | 23 | 115 | 1 (0)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter(("C"."CUST_CITY"='Oxford' AND "C"."CUST_GENDER"='F'))
5 - access("C1"="D"."COUNTRY_ID")
6 - access("S"."CUST_ID"="C0")
7 - access("S"."PROD_ID"="P"."PROD_ID")
8 - filter("P"."PROD_SUBCATEGORY_DESC"='Memory')
16 - filter("P"."PROD_SUBCATEGORY_DESC"='Memory')
17 - access("S"."PROD_ID"="P"."PROD_ID")
22 - access("S"."CUST_ID"="C0")

Note
-----
- star transformation used for this statement


Let's simulate a Remote Procedure Call using a loopback database link:


create database link loopback@local connect to user identified by pwd using 'orcl';


Now get rid of the two execution plans in the Shared Pool (e.g. by flushing it if a test system) and execute the following:


exec pkg_test_materialize_remote.test_simple_statement@loopback@local

exec pkg_test_materialize_remote.test_star_transform_statement@loopback@local


The execution plans look now like this:


SQL_ID cdudvb233tdau, child number 0
-------------------------------------
WITH A AS ( SELECT /*+ materialize */ * FROM DUAL ) SELECT * FROM A
WHERE 1 = 2

Plan hash value: 3752461848

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(NULL IS NOT NULL)

SQL_ID 5bp3rpfs2f4bw, child number 0
-------------------------------------
select sum(quantity_sold),
p.prod_subcategory_desc, c.cust_gender from
sh.sales s join sh.products p ON (s.prod_id = p.prod_id)
join sh.customers c ON (s.cust_id = c.cust_id) join
sh.countries d ON (c.country_id = d.country_id) where
p.prod_subcategory_desc = 'Memory' and c.cust_city = 'Oxford'
and c.cust_gender = 'F' group by
p.prod_subcategory_desc, c.cust_gender

Plan hash value: 3619028137

-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1238 (100)| | | |
| 1 | SORT GROUP BY NOSORT | | 1 | 57 | 1238 (1)| 00:00:15 | | |
|* 2 | HASH JOIN | | 26 | 1482 | 830 (1)| 00:00:10 | | |
|* 3 | HASH JOIN | | 26 | 1352 | 828 (1)| 00:00:10 | | |
|* 4 | HASH JOIN | | 27 | 810 | 423 (1)| 00:00:06 | | |
|* 5 | TABLE ACCESS FULL | PRODUCTS | 3 | 54 | 3 (0)| 00:00:01 | | |
| 6 | PARTITION RANGE ALL | | 31 | 372 | 419 (1)| 00:00:06 | 1 | 28 |
| 7 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 31 | 372 | 419 (1)| 00:00:06 | 1 | 28 |
| 8 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
| 9 | BITMAP AND | | | | | | | |
| 10 | BITMAP MERGE | | | | | | | |
| 11 | BITMAP KEY ITERATION | | | | | | | |
| 12 | BUFFER SORT | | | | | | | |
|* 13 | TABLE ACCESS FULL | PRODUCTS | 3 | 54 | 3 (0)| 00:00:01 | | |
|* 14 | BITMAP INDEX RANGE SCAN | SALES_PROD_BIX | | | | | 1 | 28 |
| 15 | BITMAP MERGE | | | | | | | |
| 16 | BITMAP KEY ITERATION | | | | | | | |
| 17 | BUFFER SORT | | | | | | | |
|* 18 | TABLE ACCESS FULL | CUSTOMERS | 45 | 765 | 405 (1)| 00:00:05 | | |
|* 19 | BITMAP INDEX RANGE SCAN | SALES_CUST_BIX | | | | | 1 | 28 |
|* 20 | TABLE ACCESS FULL | CUSTOMERS | 45 | 990 | 405 (1)| 00:00:05 | | |
| 21 | INDEX FULL SCAN | COUNTRIES_PK | 23 | 115 | 1 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("C"."COUNTRY_ID"="D"."COUNTRY_ID")
3 - access("S"."CUST_ID"="C"."CUST_ID")
4 - access("S"."PROD_ID"="P"."PROD_ID")
5 - filter("P"."PROD_SUBCATEGORY_DESC"='Memory')
13 - filter("P"."PROD_SUBCATEGORY_DESC"='Memory')
14 - access("S"."PROD_ID"="P"."PROD_ID")
18 - filter(("C"."CUST_CITY"='Oxford' AND "C"."CUST_GENDER"='F'))
19 - access("S"."CUST_ID"="C"."CUST_ID")
20 - filter(("C"."CUST_CITY"='Oxford' AND "C"."CUST_GENDER"='F'))

Note
-----
- star transformation used for this statement


Notice how the TEMP TABLE TRANSFORMATION is gone from both plans?

The odd thing is that I wasn't able so far to pinpoint any optimizer environment settings that are related to this - in fact the optimizer happily shares the existing plan with the remote or local execution, so there seems not to be any difference in the optimizer environment (and which makes me think that this is more a side-effect than an intended feature).

Therefore it is required to get rid of the plans generated by the local execution to reproduce the issue above.

The behaviour seems to be consistent across 10.2.0.4, 11.1.0.7 and 11.2.0.1.

I could imagine there is something related to the fact that a distributed transaction has been started by the RPC call, but since the plans are re-used when already in the Shared Pool, this doesn't seem to be a reasonable explanation either.

I couldn't find yet any MetaLink (sorry, MOS) documents that describe this particular issue. I might raise an SR if I find the time, the issue has been worked around at the client side by ensuring particular execution paths of the critical statement.

Update Jan 2011: Thanks to the anonymous poster below - the corresponding official bug is Bug 9399589: "WITH" subqueries cannot be materialized inside a global transaction

Saturday, December 12, 2009

UKOUG 2009 - The Slides

As promised in one of my comments here are the slides of the presentations I did at UKOUG 2009.

I've already considered some of the valuable feedback I got - in particular for the FIRST_ROWS_N presentation since it was the first time I did this one.

So those downloads are even valuable for those who attended my sessions - they offer a couple of goodies:

1. The "CBO fundamentals: Understanding System Statistics" slides have a part "The gory details" right after the official end of the presentation where you can find the really "gory" details, if anyone is interested

2. The "Everything You Wanted To Ask About FIRST_ROWS_N But Were Afraid To Ask" presentation has been revised - in particular I have worked on those parts of the slides that were "suboptimal" so far. The complex pagination queries are now better readable and explained step-by-step which I think makes them much simpler to understand, but also some other parts have been re-worked and extended.

Furthermore the slides for this presentation are available in two versions: The presentation slides, and additionally the notes where I explain some more details for those who are interested.

Here are the links to the downloads:

"CBO fundamentals: Understanding System Statistics"

"Everything You Wanted To Ask About FIRST_ROWS_N But Were Afraid To Ask"

"Everything You Wanted To Ask About FIRST_ROWS_N But Were Afraid To Ask" - Notes

Saturday, December 5, 2009

OakTable Book "Oracle Expert Practices"

I haven't had much time in the recent past to write interesting blog posts, and the main reason for this is that I was very busy during the last couple of months - in particular contributing to the latest OakTable book "Oracle Expert Practices: Oracle Database Administration from the OakTable" by APress. It has been a very interesting experience - I've been co-authoring two chapters about Performance Optimization Methods together with another OakTable member: Charles Hooper.

This was a real collaborative work, a joint-effort if you want to say so. We exchanged the chapter contents rather frequently via Internet and I think this approach worked out quite well. I also have to thank Charles for spending a lot of time in rewording most of my "german" English into something that was consistent with his style.

It actually worked so well that what was originally planned as a single chapter grew so fast that it was finally decided to split it into two chapters - so we ended up with two chapters each co-authored by two authors.

Although it is obvious that something as complex as Performance Optimization Methods can't be covered to a full extend in a chapter (or even two) and therefore sometimes only the surface could be scratched and countless omissions were necessary I hope that the two chapters help to get a good overview of the available Performance Monitoring and Optimization methods.

I guess that these two chapters are not an easy read - we have packed a lot of details into them, but they should really be worth spending some time to dig through. We have also prepared numerous scripts that can be downloaded at the APress website to reproduce the described methods.

For a full coverage of the Performance Optimization area to me personally Christian Antognini's "Troubleshooting Oracle Performance" is still the benchmark - a very remarkable book.

I really hope that the same will be true for the "Oracle Expert Practices" book - it is supposed to be shipping real soon now.

By the way - it is still a bit early I know, but Charles and myself plan to perform a presentation together about our book chapters at the "Michigan OakTable Symposium (MOTS)" which will take place right before the OOW 2010 on the 16th and 17th of September 2010. So if you're looking for a "technical" conference rather than the more marketing oriented stuff at OOW - this might be interesting for you.

We have some very good ideas about this presentation - it will probably be more or less "zero-slide" and cover lots of demonstrations I guess, but it's too early obviously to reveal too much.