So, i'm a bit confused about this alert.
I'm running a report that generates two alerts: PEGA0005 (after 2 secs) and PEGA0042 (after 2 mins!!)
What i noticed is that the time spent running the query on DB, traced with PEGA0005, is consistente with the time it takes running the same query in a SQL client (2 secs).
It's unclear to me what are the operations carried out by pega, after fetching the records, that takes such a huge time generating the PEGA0042 alert.
I'm a bit at loss here because i have no idea how can i optimize this report since the SQL time spent on DB (2 secs) is well within acceptable range for our users.
Any idea on what generates PEGA0042 alert?
Sadly in those articles it's not clearly explained what are the reasons behind a PEGA0042 alert. I really can't figure out what "packaging of the query" means. DB time time and fetching of the results seems to occour before PEGA0005 alert which is consistent to the observation that the same query executed in a SQL client takes the same time (2 secs).
Given those evidences working on query optimization doesn't seems to get any appreciable result in the overall execution time.
So i'm still left wondering what's happening between PEGA0005 alert and PEGA0042 one.
I'm aware that i can configure threshold time for the alerts but the main problem here is not having an alert traced in the logs but the fact that the user has to wait 2 mins to view the report
Just out of interest, how are you measuring the query time directly?
That is, normally when I run a query directly in, say SQLDeveloper, I can see results faster than the real time needed to complete the query. I believe this is because it just performing the first fetch/display of those results.
Whereas if you want to measure the actual time it took to complete the query then you're probably need to wrap this into a block of SQL (I'd tend to wrap this into a cursor in PL/SQL on Oracle), so I can get an idea of the definitive start/end time.