Re: Cursor with hold for select takes too long

Hi team,
There is no order by, query returns 5341 rows.
Query is like
Select a.id, sum(b.amount),
Count(c.am_id) ... from fact.a join fact.b on a.id= b.id join
Fact.vw_c on a.ida=vw_c.ida group by a.id;

On Fri, 22 Mar 2024, 23:09 Erik Brandsberg, <erik(at)heimdalldata(dot)com> wrote:

> What is the actual query being done, and how many rows are being
> returned? Depending on the query, it could change the execution path, so
> this is critical to understand. If you are doing an order by on a large
> result-set, it may be doing a huge amount of processing to get your first
> 100 rows.
>
> On Fri, Mar 22, 2024 at 12:50 PM Bindra Bambharoliya <
> bindra(dot)bambharoliya(at)gmail(dot)com> wrote:
>
>> Hi team,
>> I am facing issues with cursor with hold for select.
>>
>> If I execute select quaery without cursor it takes 13 seconds,
>> If execute it like
>> Begin;
>> Cursor "sql1" with hold for select.... ;fetch 100 to "sql1"
>> It takes more than 3 minutes.
>> Even sometimes it takes more than 10 minutes.
>>
>> What could be the problem?
>> Request to help on this ASAP.
>> I am facing this issue on production.
>> I checked locks etc. But nothing like that.
>>
>>
>> Thanks & Regards
>> Bindra
>>
>