It's not totally trivial working out the timing of activity in a parallel
plan, although a good starting point is to follow the ordering given by the
TQ details for each individual DFO tree. And the "Start Active / Time
Active" is an interesting problem - presumably it's likely to cover the
first slave start to the last slave ending time, which in unbalanced cases
could lead to some confusion.
Looking at your plan here, though, the tablescan at operation 86 starts
at +9 and takes 4 seconds to complete. I'm having a little difficulty
working out exactly how this table scan passed through lagers of slave to
get to the sort group by at operation 6 that starts at +12 and runs for 5
seconds - though that 5 seconds is probably about receiving data from the
tablescan and join, and not really about sorting.
Given that it's a parallel query, it's not surprising that there are two
sort group by operations - this is Oracle attempting to reduce the data
size each PX slave has picked up from its hash join before distributing by
range for a final sort group by. The sort group by at operation 3 starts
at +16 and appears to take 24 seconds, but again I think very little time
is spent in the actual sort, and most of the active time is related to
passing the data to the client; the "PX SEND QC" is probably the indicator
that the sort completed at +17.
Given that you seem to have 2M rows moving up the pipeline it's possible
that the two-pass sorting could be eliminated. If you check the Outline
Data you probably had a gby_pushdown(@qbname) in it. If you change this to
no_gby_pushdown(@qbname) you should find that the preliminary sort group by
disappears - this might make a couple of seconds difference. (I've had a
draft note on that hint for 9 years but haven't got around to finishing it
- there is a mention of it in
https://jonathanlewis.wordpress.com/2022/04/13/adaptive-joins/ ;)
As far as a small amount of data making a big difference - that's always a
possibility in any query. Given the complete cascade of hash joins, though,
I don't think you're likely to see that happen. I have to say, though, that
you do seem to have a skew in your data that might be adding a couple of
seconds to your query. In the PX details you can see that slave p005 uses
far more CPU time than the rest in slave set 1, and slave p00a does far
more I/O to temp that the rest in slave set 2. Snapshots of PX session
stats may give you some clues about those two anomalies.
Your general question about timing for 10 table joins with 2M rows - you've
got more joins than that, and I can see 4, 5, and 8m rows reported, so
plenty of work to do. 40 seconds doesn't seem unreasonable, until you say
you only want to look at the first few. Possibly you could find a way to
create a materialized view (perhaps of only a subset of the tables) that
reduces that enough to make it look a lot better.
Regards
Jonathan Lewis
On Thu, 9 Nov 2023 at 10:51, yudhi s <learnerdatabase99@xxxxxxxxx> wrote:
I ran the query manually with parallel(6) and below is the sql monitor. It
does finish in ~40 seconds overall. But yes need to execute it from the UI
by changing the view definition with parallel hint , to see howmuch extent
its helping the real production run which runs with a first_rows kind of
setup.
https://gist.github.com/databasetech0073/62b51587b3f590914e9a92218609672a
But again, if volume increase a bit the response time may again exceed and
goes beyond ~10 second reponse which is not good experience for any UI
user. So my doubt is, if we should really not expect these type of queries
(e.g. with 10 table joins +~2million matching rows +sorting on top) to
finish in <10 seconds and thus some different design approach should be
followed to cater such requirement? Like persisting the
transformed/aggregated results before hand in a materialized view or
anything else?
