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