I gave you this explicit instruction in my last post:
*>> For the system and session activity stats keep the same order as the
output order - don't sort by value or alphetic order of name. *
There is no point in sorting, and there is no point in finding the "biggest
5 numbers" -
e,g, physical bytes read will be much bigger than physical blocks read but
doesn't add any information by being at the top of the list.
In fact it makes things harder because (a) the order of *information* is
effectively randomised and (b) it may gives a spurious importance to an
irrelevant number.
Sorting by the differences between a good and a bad run is even worse.
Regards
Jonathan Lewis
On Thu, 19 Aug 2021 at 20:14, Pap <oracle.developer35@xxxxxxxxx> wrote:
Thank You Jonathan. Was not able to get hold of good run to compare, now I
was able to get hold of a good run and below is few top most non zero
differences of stats between good run vs bad run figures , each was for ~10
minute run duration. And it's a four node RAC database, whereas the query
for which we are seeing uneven performance is running on node-2. I
have collected the stats with the top-5-10 highest difference and posted
below. However, the attached sheet has all the data without filter.
I see "file io wait time" as top one in *v$sesstat *which points towards
the IO slowness during bad run. If i see the top most stats for node-2
from v$sysstat its showing, "cell physical IO bytes sent directly to DB
node to balance CPU", is it anyway pointing to CPU starvation at cell
nodes? Overall node-1 seems to be much busier during that time but i am
still struggling to find anything which can make such situation.
For v$systemevent and v$event_histograms , i have just filtered 'cell
single block physical read' it does show higher wait time. But not sure how
to get the exact culprit here?
