Parallel Query Performance Issues
Date: Thu, 9 Jun 2022 16:45:05 +0530
Message-ID: <CAOzfMupouEr-G+fVfHW9rR1pmEH=u4McovDJBEVBB-1qTf_D=g_at_mail.gmail.com>
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
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jun 09 2022 - 13:15:05 CEST