Environment : 11.2.0.4 database running on Linux.
Need help to understand parallel query performance issues. Below are the
query details and its associated plans. The 2018_query does execute in 24
seconds and returns about 2.5K rows. The 2019_query is also expected to
process almost the same number of rows however it consumes a lot of TEMP
space and finally fails. The 2019_query without parallel completes in 45
minutes (Just by removing the parallel hint). The only difference between
both the queries is related to the predicate "opclf.year_number =
to_number('YYYY')". The stats are up to date for the tables are partitions.
2019_query:
https://gist.github.com/aryangoti/a7704a8075f118f7d942e49acee1900d
2018_query:
https://gist.github.com/aryangoti/a7704a8075f118f7d942e49acee1900d
Stats and other details:
https://gist.github.com/aryangoti/a3797424ce0cb4fd87e194c05ad099b6
Thanks,
Goti
