Oracle mechanics

Igor Usoltsev's Oracle blog

Recursive queries for materialized view operations with database link

Posted by Игорь Усольцев on May 23, 2011

For operations Create Materialized View or Complete Refresh for mviews with remote tables (over database links), the execution plan can significantly differ from the base query plan, as well as from Create Table As Select (CTAS) plan. Some tests:

SQL> create table t as select * from dba_tables;

Table created.

SQL> exec dbms_stats.gather_table_stats('SYSTEM','T');

PL/SQL procedure successfully completed.

SQL> create materialized view t_mv as select t1.* from t@db_link tr, t t1,t t2 where tr.table_name = t1.table_name and t2.table_name = t1.table_name;

In Oracle 11.2 there can be found two open active cursors for my session in v$open_cursor, and one of cursors  is marked as OPEN-RECURSIVE (new column CURSOR_TYPE in v$open_cursor 11.2 version), with the strange value for LAST_SQL_EXEC_TIME column. In Oracle 10.2 and 11.1 there isn’t the field CURSOR_TYPE in v$open_cursor yet, and recursive cursor can be easily found by v$sql.sql_text

SID USER_NAME ADDRESS  HASH_VALUE SQL_ID        SQL_TEXT                                                     LAST_SQL_ACTIVE_TIME SQL_EXEC_ID CURSOR_TYPE
--- --------- -------- ---------- ------------- ------------------------------------------------------------ -------------------- ----------- --------------
67  SYSTEM    233CD018 2317899508 3vcya0y52hprn CREATE TABLE "SYSTEM"."T_MV"  AS select t1.* from t@db_link  01-JAN-70              16777217  OPEN-RECURSIVE
67  SYSTEM    2639FCDC 1245822999 2dvw4u1543h0r create materialized view t_mv as select t1.* from t@db_link                         16777217  OPEN


For the recursive (generated by CREATE MATERIALIZED VIEW operation) query CREATE TABLE “SYSTEM”.”T” AS SELECT, optimizer estimates remote table cardinality  as 1. As the result some non-optimal access pathes may be choosen , for exapmle MERGE JOIN  CARTESIAN instead of NESTED LOOP or HASH JOIN:

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
-----------------------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT |      |       |       |    60 (100)|          |        |      |
|   1 |  LOAD AS SELECT        |      |       |       |            |          |        |      |
|*  2 |   HASH JOIN            |      |     1 |   278 |    59   (2)| 00:00:01 |        |      |
|   3 |    MERGE JOIN CARTESIAN|      |     1 |    37 |    30   (0)| 00:00:01 |        |      |
|   4 |     REMOTE             | T    |     1 |    17 |     2   (0)| 00:00:01 | DB_LI~ | R->S |
|   5 |     BUFFER SORT        |      |  2793 | 55860 |    28   (0)| 00:00:01 |        |      |
|   6 |      TABLE ACCESS FULL | T    |  2793 | 55860 |    28   (0)| 00:00:01 |        |      |
|   7 |    TABLE ACCESS FULL   | T    |  2793 |   657K|    28   (0)| 00:00:01 |        |      |
-----------------------------------------------------------------------------------------------

Remote SQL Information (identified by operation id):
----------------------------------------------------

4 - SELECT "TABLE_NAME" FROM "T" "TR" (accessing 'DB_LINK' )

For the explicit CREATE TABLE AS SELECT (and the base SELECT too) Oracle can correctly estimate remote table cardinality (at least from 10.2 version):

create table t_ctas as select t1.* from t@db_link tr, t t1,t t2 where
tr.table_name = t1.table_name and t2.table_name = t1.table_name

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
-----------------------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT |      |       |       |    96 (100)|          |        |      |
|   1 |  LOAD AS SELECT        |      |       |       |            |          |        |      |
|*  2 |   HASH JOIN            |      |  2809 |   762K|    76   (2)| 00:00:01 |        |      |
|   3 |    TABLE ACCESS FULL   | T    |  2793 | 55860 |    28   (0)| 00:00:01 |        |      |
|*  4 |    HASH JOIN           |      |  2801 |   705K|    48   (3)| 00:00:01 |        |      |
|   5 |     REMOTE             | T    |  2793 | 47481 |    19   (0)| 00:00:01 | DB_LI~ | R->S |
|   6 |     TABLE ACCESS FULL  | T    |  2793 |   657K|    28   (0)| 00:00:01 |        |      |
-----------------------------------------------------------------------------------------------

The same remote table cardinality estimation error can be noticed for MV Complete Refresh

SQL> exec dbms_mview.refresh('T_MV', method => 'c', atomic_refresh => false);

– for the recursive INSERT /*+ APPEND*/ Oracle choose similar plan based on incorrect remote table cardinality estimation (ROWS=1):

----------------------------------------------------------------------------------------------------
SQL_ID  67bhrr62u207g, child number 0
-------------------------------------
INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND  */ INTO "SYSTEM"."T_MV"
select t1.* from t@db_link tr, t t1,t t2 where tr.table_name =
t1.table_name and t2.table_name = t1.table_name

Plan hash value: 3159662255

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
-----------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT       |      |       |       |    59 (100)|          |        |      |
|   1 |  LOAD AS SELECT        |      |       |       |            |          |        |      |
|*  2 |   HASH JOIN            |      |     1 |   278 |    59   (2)| 00:00:01 |        |      |
|   3 |    MERGE JOIN CARTESIAN|      |     1 |    37 |    30   (0)| 00:00:01 |        |      |
|   4 |     REMOTE             | T    |     1 |    17 |     2   (0)| 00:00:01 | DB_LI~ | R->S |
|   5 |     BUFFER SORT        |      |  2793 | 55860 |    28   (0)| 00:00:01 |        |      |
|   6 |      TABLE ACCESS FULL | T    |  2793 | 55860 |    28   (0)| 00:00:01 |        |      |
|   7 |    TABLE ACCESS FULL   | T    |  2793 |   657K|    28   (0)| 00:00:01 |        |      |
-----------------------------------------------------------------------------------------------

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

2 - access("TR"."TABLE_NAME"="T1"."TABLE_NAME" AND
"T2"."TABLE_NAME"="T1"."TABLE_NAME")

Remote SQL Information (identified by operation id):
----------------------------------------------------

4 - SELECT /*+ */ "TABLE_NAME" FROM "T" "TR" (accessing 'DB_LINK' )

As workaround we can execute sequentially CREATE TABLE AS SELECT and CREATE MVIEW … ON PREBUILD TABLE.

As well the optimizer hints can be successfully used: Oracle transparently translates hints into recursive queries. For the MV Complete Refresh operations the hints are the only way to improve execution plan, imho

SQL>create materialized view t_mv as select--+ cardinality(tr 10000)
2  t1.* from t@db_link tr, t t1,t t2, t t3, t t4 where tr.table_name = t1.table_name and t2.table_name = t1.table_name;

Recursive CTAS execution plan successfully uses hinted remote table cardinality:

CREATE TABLE "SYSTEM"."T_MV"  AS select--+ cardinality(tr 10000) t1.*
from t@db_link tr, t t1,t t2, t t3, t t4 where tr.table_name =
t1.table_name and t2.table_name = t1.table_name

-------------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
-------------------------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT   |      |       |       |   544M(100)|          |        |      |
|   1 |  LOAD AS SELECT          |      |       |       |            |          |        |      |
|*  2 |   HASH JOIN              |      |    78G|    19T|   849K (92)| 02:50:00 |        |      |
|   3 |    TABLE ACCESS FULL     | T    |  2793 | 55860 |    28   (0)| 00:00:01 |        |      |
|*  4 |    HASH JOIN             |      |    78G|    18T|   461K (84)| 01:32:19 |        |      |
|   5 |     TABLE ACCESS FULL    | T    |  2793 |   657K|    28   (0)| 00:00:01 |        |      |
|   6 |     MERGE JOIN CARTESIAN |      |    78G|  1235G|    81M(100)|270:03:09 |        |      |
|   7 |      MERGE JOIN CARTESIAN|      |  7800K|       | 74315   (1)| 00:14:52 |        |      |
|   8 |       TABLE ACCESS FULL  | T    |  2793 |       |    28   (0)| 00:00:01 |        |      |
|   9 |       BUFFER SORT        |      |  2793 |       | 74287   (1)| 00:14:52 |        |      |
|  10 |        TABLE ACCESS FULL | T    |  2793 |       |    27   (0)| 00:00:01 |        |      |
|  11 |      BUFFER SORT         |      | 10000 |   166K|    81M(100)|270:03:09 |        |      |
|  12 |       REMOTE             | T    | 10000 |   166K|     0   (0)|          | DB_LI~ | R->S |
-------------------------------------------------------------------------------------------------

Remote SQL Information (identified by operation id):
----------------------------------------------------

12 - SELECT /*+ OPT_ESTIMATE (TABLE "TR" ROWS=10000.000000 ) */ "TABLE_NAME" FROM "T"
"TR" (accessing 'DB_LINK' )

In Remote SQL Information section the hint /*+ OPT_ESTIMATE (TABLE “TR” ROWS=10000.000000 ) */ is shown, which is generated by my cardinality(tr 10000) hint from parent Create Materialized View statement

Multiplied  cartesian conjunction in the last Create Materialized View statement was used only to increase the query duration – for convenience 🙂

Results were tested for Oracle 10.2.0.4, 11.2.0.1 versions

Advertisements

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

 
%d bloggers like this: