Skip to Main Content
  • Questions
  • With clause in distributed transactions

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Hrvoje.

Asked: February 14, 2019 - 10:22 am UTC

Last updated: August 22, 2022 - 12:46 pm UTC

Version: Oracle RDBMS 12.1.0.2.0

Viewed 10K+ times! This question is

You Asked


Hi Tom !


As there is put a restriction on GTTs:
Distributed transactions are not supported for temporary tables


does that mean that inline views in a query, i.e. using WITH clause,
but those with MATERIALIZED hint will not work properly?

If there is neither MATERIALIZED nor INLINE hint given by a developer in the WITH clause
does Oracle takes care then not to use internally MATERIALIZED way of handling this?

The problem is that Oracle sometimes does not handle properly JOIN over db link,
fetching first data from the huge table and joining it that with the other table over db link
instead of doing join remotely - which is slow of course.

So, to prevent that there is used something like this:
WITH agg_data as (
SELECT /*+ MATERIALIZE */
       column_1, column_2, sum(column_3) agg_val
  FROM huge_table@over_db_link
 WHERE <some_where_cond>
 GROUP BY column_1, column_2
SELECT *
  FROM smaller_table@over_db_link  st
  JOIN agg_data
    ON st.column_1 = agg_data.column_1;


Will it work?

On the other hand if MATERIALIZED hint is removed it works much slower,
but is there guarantee that Oracle will not use MATERIALIZE hint internally anyway?


I can't find a word in the documentation about it.


BR,
Hrvoje


and Chris said...

Tuning distributed queries is tricky. So I went into this topic in-depth in yesterday's SQL Office Hours.

You can watch the recording at:



If you're looking for the TL;DR for your query, I'd suggest:

- Changing the materialized hint => no_merge
- Moving small_table in the subquery and joining it there

Which gives a query like:

with agg_data as (
  select /*+ no_merge */
         c1, c2, sum ( c3 ) agg_val
  from   huge_table@over_db_link ht
  join   small_table@over_db_link st
  on     st.c1 = agg_data.c1
  group  by c1, c2
)
  select *
  from   agg_data;


If it's still too slow, I'd look at making a materialized view to pre-compute the aggregation.

Rating

  (17 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

WITH subquery and distributed transaction

Hrvoje, February 20, 2019 - 8:29 pm UTC

Hi Chris !

Thank you !

This was most useful.
Especially the /*+ no_merge */ hint.

I've already answered directly on Office Hours response,
because as I've explained there I had to change the email address and I've lost your original mail.

But we did not understand each other completely.
(If you answered there).
My question goes further.
You said there that I should not use /*+ materialize */ hint as it is undocumented.
That's fine. I've used it only as a temporary solution.

My worry is about this:
If I understand correctly - if there is not used ANY hint inside a WITH clause, something like this:
WITH t as (
         /* NO HINT ! */
  SELECT column1, column2,
    FROM t@some_db_link
)
SELECT * FROM T;


in that situation Oracle RDBMS (again as I've learned on some Oracle conference) decides INTERNALLY will it use MATERIALIZE way of handling subfactoring part, i.e. will it create temporary GTT or will it merge it with main query part (INLINE way), based on some statistics.
But as there is explicitly stated in the documentation that GTTs must not be used in distributed transactions
and there are some related bugs in MOS about it,
then do we have to put always /*+ INLINE */ hint inside subfactoring part to be sure that Oracle RDBMS will not try to create GTTs when remote tables are used inside,
or does RDBMS worries about it,
or there can't be harm even if (internal) GTTs are used because this documentation line restriction is only relevant for inserting into GTTs from distributed transaction and not querying them?

I'm asking this - in order to hardwire this /*+ INLINE */ hint if necessary and forget about it and not to find out few months later that I've lost some few hundred of thousands of records or have to deal with some ORA-00600 and Oracle Support will tell me - but hey - we stated there in the documentation that you should not use GTTs in distributed transactions in the first place :-)

Thank you !

BR,
Hrvoje


Chris Saxon
February 21, 2019 - 5:29 pm UTC

The INLINE hint is also undocumented ;)

So, you should not put this in your distributed queries either!

You're getting too deep into the internal workings of Oracle Database. If temp table transformations cause distributed queries to give wrong results, that's a bug. And you should raise this with support.

You, the developer/DBA are NOT using GTTs in your process. If, under the covers, we happen to, then that's on us.

If you start using undocumented hints however...

riding shotgun

Racer I., February 22, 2019 - 9:03 am UTC

Hi,

Would maybe DRIVING_SITE help here?

regards,
Chris Saxon
February 22, 2019 - 10:27 am UTC

In general it could do; in this case sadly no. In an offline chat the OP said they were getting "end-of-communication" errors when doing this.

DRIVING_SITE hint problem

Hrvoje Devcic, February 22, 2019 - 11:05 am UTC

Hi Chris !

The DRIVING_SITE hint is giving the end-of-communication channel problem because we have setup like this:
We have additional schema for all external communication.
So we created a synonym big_table_syn there for big_table@some_db_link.
But when we tried to grant SELECT on this to BASE_SCHEMA
we are getting ORA-02021: DDL Operations Are Not Allowed On a Remote Database.
So we used the recommended way from O'Reilly to create a view on big_table@some_db_link, and grant SELECT on that view to BASE_SCHEMA.
And that works !
It is little bit strange that one of the almost the same things works and other doesn't.
I do not see any security benefits if views can mimic the behavior.

But !
When we try to use the DRIVING_SITE hint in that setup on the BASE_SCHEMA - the end-of-communication channel happens.
All the time.
And what is the most strange comparable with quantum fields entaglments is that there is no single trace about it in the alert.log :-O.

So, we will investigate it little bit further and try to create Oracle SR, but do not know how exactly as there are no trace files to send.

Now, we created the db link on the BASE_SCHEMA (against the application policy !) and I'm playing with some combinations of /*+ NO_MERGE */ and /*+ DRIVING_SITE */ hints, but the speed is still not satisfactory.
Currently the only realistic (regarding speed) solution is the /*+ MATERIALIZE */ hint I've presented in the first place.

The MVIEWs are not in the game, as the original table is not huge, but gigantic.
We do not have that much space in our database.

So, the struggle continues...

BR,
Hrvoje


Chris Saxon
February 22, 2019 - 3:33 pm UTC

For synonyms vs. view:

A synonym is just an alias for another object.

If I create a synonym for a table, then grant select on the synonym, I'm really granting this against the base table. For example:

create table t (
  c1 int
);
create synonym s for t;

grant select on s to hr;

select * from dba_tab_privs
where  grantee = 'HR';

GRANTEE   OWNER   TABLE_NAME   GRANTOR   PRIVILEGE   GRANTABLE   HIERARCHY   COMMON   TYPE    INHERITED   
HR        CHRIS   T            CHRIS     SELECT      NO          NO          NO       TABLE   NO       


Grant is DDL. So to grant on a synonym over a DB link, you're really running the grant on the remote object.

When you create a view over a DB link, the view is a local object. So you're NOT running remote DDL when granting on it.

Racer's already made the other points I was going to on this.

cat on a hot tin roof

Racer I., February 22, 2019 - 12:28 pm UTC

Hi,

> It is little bit strange that one of the almost the same things works and other doesn't.
> I do not see any security benefits if views can mimic the behavior.

I guess its because the view is an object in your db so can have grants but the remote table doesn't exist in your db so where would the grant be stored? And the remote db doesn't know your GRANTEE. Also a remote object is virtual as it's no biggy if the the link or the table temporarily doesn't exist or the link is recreated and now points to another db.

> that there is no single trace about it in the alert.log

Have you looked on both dbs?

> The MVIEWs are not in the game, as the original table is not huge, but gigantic.
> We do not have that much space in our database.

Is this also true for the aggregated version? Which is what the mview would presumable contain.

How much control do you have over both sides? Maybe you can run the whole statement on the remote site (via a helper package plus reverse link) and retrieve the result from a new remote table this fills (iffy if this is executed by many concurrent sessions)?

How big will the final result be? If fairly tiny you can try to make the remote package PIPELINED (also haven't tried this across a link, might also not work because the return type will not be shared).

Also : We never knew MATERIALIZE is not supported. Using it all the time...

WITH subquery and distributed transaction

Hrvoje, February 22, 2019 - 1:21 pm UTC

Hi cat !

> I guess its because the view is an object in your db so can have grants but the remote table doesn't exist in your db so where would the grant be stored? And the remote db doesn't know your GRANTEE. Also a remote object is virtual as it's no biggy if the the link or the table temporarily doesn't exist or the link is recreated and now points to another db.

You have a point, but there can be implemented privileges on synonyms, as synonyms are local objects.
I now realize that it does not work that way, but it could.
Synonyms can behave as
create or replace view_name as select * from <table>[@db_link],
regarding privileges.
Whatever. We are here where we are.
This would be not a problem if there is not this bug.

> Is this also true for the aggregated version? Which is what the mview would presumable contain.

Unfortunately, yes.
Btw. we would need both aggregated versiom and non-aggregated (full) version.

> How much control do you have over both sides? Maybe you can run the whole statement on the remote site ...

As I say - none. Even our customer DBAs are limited. This is big vendor application and it's a black box and this vendor is not ready to make much of changes in it's schema. Mildly said.
Creating application objects there for us is out of question.

That's why I will strongly suggest to Chris, Oracle and others that the solution he proposed for statistics over db link to create a procedure in the remote schema which would execute ALTER SESSION to gather statistics there is at least for us, but I presume and for others - impossible.
Please make a new parameter in DBMS_XPLAIN which will do just that without need for such tricks :-)

> Also : We never knew MATERIALIZE is not supported. Using it all the time...

I know it's undocumented.
There is no a single word about it in the documentation. Never.
But - on some MOS pages it is recommended, so I think Berluson said - it is half-undocumented :-)
Differently from GTT usage in distributed transactions, which is restricted, but was de-restricted, obviously by mistake, removed from I think 12.1 documentation and than returned back.
But life has to go on and find its way just like a water.
If I had the only viable solution now to use MATERIALIZE hint and in some future version Oracle will change the way it handles it and we would need to change our code, ah we will do it. Not the first time.
We have no time to write Oracle SRs whole day and wait for their reply.
We just document it - as !!! and keep in mind that this is not the perfect solution as long as the number of such workarounds is small.

Chris Saxon
February 22, 2019 - 3:47 pm UTC

but I presume and for others - impossible.

Really? Impossible? I seriously doubt that. If this slow query is affecting the business in a material way I'm sure you can find a way to escalate this and find a solution.

Racer suggests a possible way below.

Please make a new parameter in DBMS_XPLAIN which will do just that without need for such tricks :-)

The issue is by the time you call DBMS_xplain.display... it's too late. You need to call a procedure on the remote database to enable all stats in the remote session. Before you run the query.

Anyway, I just realized there is a way to do this. That doesn't involve creating a remote procedure!

Call dbms_monitor over the database link:

exec dbms_monitor.session_trace_enable@loopback


This will also create a trace file on the remote database. Which you can parse with TKPROF or SQL Dev.

We have no time to write Oracle SRs whole day and wait for their reply.

Well it's been over a week since you submitted this here...

scanning tunneling

Racer I., February 22, 2019 - 3:00 pm UTC

Hi,

> in it's schema.

Sometimes there is a chink here. If you could get your own schema in the remote db, were you can deploy helper packages and have some tablespace quotas for intermediate tables. Then connect the link there (with a back-link if possible). Then the vendor would "only" need to add a SELECT grant on the big table(s) for you schema. They might even prefer it if you don't directly connect to their schema user.

If you don't get direct deployment rights in that schema you might try if they allow you to install a single constant package once which will execute whatever string you pass via execute immediate. That way you could create/adjust/drop whatever you need on the fly (in your own schema only). With rights limited to the bare-bone needed for this task it shouldn't be too horrible a security risk.

regards,
Chris Saxon
February 22, 2019 - 3:48 pm UTC

Indeed. If this is a big enough problem A Way can be found.

Ways can be found...

Hrvoje, February 22, 2019 - 6:41 pm UTC

Hi Chris and Racer,

First thanks a lot for your help !

I'll definitely try
exec dbms_monitor.session_trace_enable@loopback

on Monday.

I thought that /*+ NO_MERGE */ will help, but it did not. I don't know why - trying to figure out. It first fetches data from one table than from another and does not want to join them remotely although there are proper indexes on remote tables.
And not only that is slow, but sometimes breaks up TEMP tablespace (hash join).

And don't get me wrong. I did not meant to be offensive.
But for example I've submitted a bug for
collect aggregate function as an Oracle SR,
(which is pretty common aggregate function) for which when you use DISTINCT and ORDER BY in it simultaneously it does neither sort nor distinct values.
It was recognized as a bug, but solution came more than 6 months after submitting.
Of course we had to find a workaround in the meantime.
I'm finding few bugs in RDBMS every month, because our application has to be very fast and we stretch databases to the limits, but nevertheless.
Usually it is much faster to find workarounds than to wait for support. When it is possible.

Regarding Racer proposal,
and comment
Really? Impossible? I seriously doubt that. If this slow query is affecting the business in a material way I'm sure you can find a way to escalate this and find a solution.

Yes. In the real world there is something called core business (for example Billing System - money !), and something called "important, but, not so core business" :-) - namely our application. And as I said our application has to be fast - it has to process 50-100 millions of records daily on a database instance (which is not supercomputer) in the near real time. From reading from files, pre-, post- processing, storing, aggregating, rating and handling complex dynamic rules (RBE), with latency no bigger than 15 minutes.
But we only got access to big tables over db link to additional schema and synonyms in it.
No other objects are allowed. Period. Even this db link is limited for number of parallel sessions.
And we are expected to deliver, work things around which we usually do.
Until we are blocked completely.
Then we call heavy cavalry - Oracle Support :-)

And to be very long with this reply (which is possible as I'm writing this from home - to reply Chris on his remark)
ways can always be found, but at what cost.
For example on Oracle 11.2.0.2 we had to denormalize counter tables for performance reasons
from simple key-value map to multiple key-value maps in the single record.
So to avoid changing lot of code we created a view with UNPIVOT clause and it worked perfectly,
until 11.2.0.4 when it stopped working perfectly because there Oracle RDBMS decided first to UNPIVOT complete partitioned table and than to make a join (push predicate) instead vice-versa. No statistics gathering/imposing and hints helped.
But again we found on MOS some undocumented parameter which when set instructed database again to happily handle this thing properly, but from 12.1 nothing helped, so lot of working hours were spent to change the code.
Just an example. And we were not naughty. On 11.2.0.2 we did not use any hints or undocumented features, but behavior changed anyway - drastically.
Yes everything is possible, only the wooden stove is not, although it is also possible only for the first time.

BR,
Hrvoje

Chris Saxon
February 25, 2019 - 11:02 am UTC

I thought that /*+ NO_MERGE */ will help, but it did not

If you can get the local and remote plans we'll see how we can help.

No offense taken; I get that different databases are subject to different SLAs.

My point is that this is a business decision. Sometimes doing a tiny amount of work upstream can save you lots of effort downstream.

Of course resolving political problems can be much harder than technical ones ;)

Remote plans.

Hrvoje Devcic, February 26, 2019 - 2:12 pm UTC

Hi Chris !

exec dbms_monitor.session_trace_enable@loopback
has been rejected also. Not allowed.

OK, /*+ MATERIALIZE */ hint stays.

I'm subscribing to you to inform me if and when you'll implement fetching remote plans and statistics as part of the core system
without need to ask for additional changes in remote DB :-).

BR,
Hrvoje

Chris Saxon
February 26, 2019 - 2:34 pm UTC

Exactly which extra changes are the problem here?

And what exactly would we include in Oracle Database that would change the no for dbms_monitor.session_trace_enable@loopback to a yes?

subquery in WITH

Fay Chen, July 15, 2020 - 9:39 pm UTC

Your WITH no_merge hint has simple join. If I have subquery in the WITH, the plan show it didn't join them as a whole but passing each row over db link, join local, then back to run subquery. I use no_unnest hint in the subquery, no difference. Here's the syntax:

with remote_sql as (
select /*+ no_merge */ a.col1, a.a1_dt
from tbl1@test a, tbl2@test b
where A.col1 = B.col1
and a.a1_dt = (select max(a1_dt) from tbl1
where a.col1 = a1.col1)
and a.col1 = 'E'
)
select distinct a.col1 from remote_sql a
where A.a1_dt between (select c_dt from tbl3 where program_id = 'a1')
and ( select c_dt from tbl3 where program_id = 'a2')

12c WITH FUNCTION

Asim, August 11, 2022 - 5:06 pm UTC


I know we can use Recursive WITH clause in which we refer the same query, alias

In 12c we can also use WITH FUNCTION to define a function and use it in query.

My question is can we define recursive functions in WITH FUNCTION clause in 12c or later versions.

For example a function to calculate factorials of a number

Connor McDonald
August 12, 2022 - 2:18 am UTC

SQL> with
  2   function FF(n int) return int is
  3   begin
  4     if n = 1 then return 1;
  5     else return n*ff(n-1); end if;
  6   end;
  7  select  ff(5) from dual;
  8  /

     FF(5)
----------
       120

Factorial built in function

Asim, August 11, 2022 - 5:13 pm UTC

By the way does Oracle has any built in function for factorial calculation
Connor McDonald
August 12, 2022 - 3:02 am UTC

No, but of course, easily written

SQL> create or replace
  2  function factorial(n int) return int is
  3    res int := 1;
  4  begin
  5    for i in 2 .. n
  6    loop
  7      res := res*i;
  8    end loop;
  9    return res;
 10  end;
 11  /

Function created.

SQL>
SQL> set serverout on
SQL> exec dbms_output.put_line(factorial(5));
120

PL/SQL procedure successfully completed.


I've steered clear of recursion because "elegant" and "performance" often do not go hand in hand.

But if you want one which is (theoretically) more efficient for large numbers then here's one as well :-)


SQL> declare
  2        p int := 1;
  3        r int := 1;
  4
  5    function nminussumofbits( v int ) return int is
  6      w int := v;
  7    begin
  8        w := w - bitand(2863311530,w) /2;
  9        w := bitand(w,858993459) + ( bitand(floor(w / 4) ,858993459));
 10        w := w + bitand(floor(w / 16) ,252645135);
 11        w := w + floor(w / 256);
 12        w := w + floor(w / 65536);
 13        return v - bitand(w ,255);
 14    end;
 15
 16    function partProduct( n int,  m int) return int is
 17      k int;
 18    begin
 19        if (m <= (n + 1)) then return n; end if;
 20        if (m = (n + 2)) then return n * m; end if;
 21        k := floor( (n + m) / 2);
 22        if (bitand(k,1) != 1) then k := k - 1; end if;
 23        return partProduct(n, k) * partProduct(k + 2, m);
 24    end;
 25
 26    procedure xloop( n int) is
 27    begin
 28        if (n <= 2) then return; end if;
 29        xloop(floor(n / 2));
 30        p := p * partProduct(floor(n / 2) + 1 + ( bitand(floor(n / 2),1)), n - 1 + bitand(n,1));
 31        r := r * p;
 32    end;
 33
 34  function Factorial(n int) return int is
 35    begin
 36        xloop(n);
 37        return r * power(2,nminussumofbits(n));
 38    end;
 39
 40  begin
 41    dbms_output.put_line(factorial(20));
 42  end;
 43  /
37123138552500

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>

Very nice

Asim, August 12, 2022 - 3:51 am UTC

Thanks very nice.

If we define two functions in WITH FUNCTION can we call 2nd function in first function?

Like this

WITH FUNCTION f1 ()
Begin
...
f2();
...end;

f2()
Begin
....
End;


Chris Saxon
August 12, 2022 - 12:26 pm UTC

Yes, though the function you're calling must be declared first

Not possible with queries

Asim, August 12, 2022 - 6:22 pm UTC

But the same is not possible with queries. Right?
Connor McDonald
August 15, 2022 - 5:23 am UTC

what do you mean? test case please

With query

Asim, August 15, 2022 - 11:27 am UTC

Means

I cannot write

WITH a as ( select.. from b),
b as ( select... from emp)
Select ... from ..

i.e cant reference b in a

Whereas in case of functions we can reference functions which are defined afterwards
Connor McDonald
August 17, 2022 - 4:27 am UTC

They work the same - declare the called item first, eg


SQL> with
  2  function fz1 return number is
  3  begin
  4    return fz2;
  5  end;
  6  function fz2 return number is
  7  begin
  8    return 17;
  9  end;
 10  select fz1 from dual;
 11  /
select fz1 from dual
       *
ERROR at line 10:
ORA-06553: PLS-313: 'FZ2' not declared in this scope


SQL>
SQL> with
  2  function fz2 return number is
  3  begin
  4    return 17;
  5  end;
  6  function fz1 return number is
  7  begin
  8    return fz2;
  9  end;
 10  select fz1 from dual;
 11  /

       FZ1
----------
        17

1 row selected.


just like tables

SQL> with
  2  tab1 as
  3  ( select * from emp ),
  4  tab2 as
  5  ( select * from tab1 )
  6  select * from tab2;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    20

3 rows selected.

SQL>
SQL>
SQL>
SQL> with
  2  tab2 as
  3  ( select * from tab1 ),
  4  tab1 as
  5  ( select * from emp )
  6  select * from tab2;
( select * from tab1 ),
                *
ERROR at line 3:
ORA-32031: illegal reference of a query name in WITH clause


???

Asim, August 16, 2022 - 10:30 am UTC

Seems some error

Thanks

Asim, August 17, 2022 - 10:35 pm UTC

Thanks alot, that clarifies all.
Chris Saxon
August 22, 2022 - 12:46 pm UTC

You're welcome

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database