Re: Parallel Query Performance Issues

Hi Andy,

Sorry for that. Please find it below.

2019_query:
https://gist.github.com/aryangoti/6826bc5ee8a788f8c0761baa4b73bb58 2018_query:
https://gist.github.com/aryangoti/a7704a8075f118f7d942e49acee1900d Details: https://gist.github.com/aryangoti/a3797424ce0cb4fd87e194c05ad099b6

Thanks,

Goti

On Thu, Jun 9, 2022 at 5:04 PM Andy Sayer <andysayer_at_gmail.com> wrote:

> Hi Goti,
>
> You’ve linked the same monitor report twice, I think we’re missing the
> slower '2019'
>
> Thanks,
> Andy
>
> On Thu, 9 Jun 2022 at 12:15, Goti <aryan.goti_at_gmail.com> wrote:
>
>> 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-l