RE: query taking a long time

Prune ALLC1 down to the specific user, table_name, and constraint type before the join, just as you did for ALLC2.    

That would be the first thing I would try.    

Second step: Add materialize to each inline projection if no cure.    

The point is that you probably want to join the two smallish sets (constraints of 2 different sets of particular types on a specific table from a single user) and then join them on name, instead of probing a potentially huge list of all constraints for a matching user. Since the two halves are already reduced to the user, table_name, type_sets you want, I *think* all you need on the join is constraint_name if you first reduce ALLC1 as suggested.    

Third step: If the CBO is not convinced by that, actually produce the two tiny gtts representing the sets and join them. (And maybe file an SR if you get past step one, but certainly file an SR if you need step 3).    

Good luck.        

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of ahmed.fikri_at_t-online.de Sent: Wednesday, November 04, 2020 12:51 PM To: list, oracle
Subject: query taking a long time  

Hi all,  

following sql takes a long time to be executed. I have seen that the query was the whole time on CPU.

I checked one execute plan and I found a lot of NLs and the E-Rows is much less than the A-Rows.

I gathered the data dictionary and fixed objects statistics but it doesn't help. We are using 12.1.0.2  

any Idea?  

Best regards

Ahmed  



--
http://www.freelists.org/webpage/oracle-l




image/png attachment: image001.png