RE: query taking a long time

From: "Mark W. Farnham" <mwf@xxxxxxxx>
To: "'ORACLE-L'" <oracle-l@xxxxxxxxxxxxx>
Date: Thu, 5 Nov 2020 14:31:00 -0500

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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of ahmed.fikri@xxxxxxxxxxx
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

References:
- query taking a long time
  - From: ahmed.fikri@xxxxxxxxxxx

RE: query taking a long time

Other related posts: