Oracle

January 5, 2012

Session statistics for Parallel Query Coordinator

Filed under: Uncategorized — srivenu @ 10:01 pm

Trying to gauge the statistics of a parallel sql operation? consider using query coordinator session statistics from V$SESSTAT with a pinch of salt.

When a parallel query (i used query for simplicity) operation is performed, the statistics from all the slave sessions spawned by the coordinator are rolled up (summed up) into the coordinator session once the slave session completes its operation. (If the parallel operation is cancelled in the middle, say by pressing ctrl-c then the slave stats are not rolled up into the coordinator).

I was tuning some parallel sql and in the process i was using statistics from V$SESSTAT for the coordinator session to compare the different execution plans. Some of the stats seemed suspect to me and i decided to test if V$SESSTAT values for the coordinator session are really true and reflect the slave stats spawned by them.

The environment is Oracle 10.2.0.4 on HP-UX ia64 B.11.31.

It was a simple test.

Create a table X with some 10 GB of data and do a count(*) from it.

select	/*+parallel(x 2)*/
	count(*)
from	x
where	col1  'xxxxxx'
/

Gather the session stats for the parallel query coordinator at the start of the query. Fire the query and while it is in execution, get the stats for the query coordinator and the slaves in a continuous loop. At the end of the operation get the stats of the coordinator.

************************
Stats before query start
************************

coordinator stats


   Sid Stat# NAME                                                                         VALUE
------ ----- ------------------------------------------------------------- --------------------
 10695    57 physical read IO requests                                                        4
 10695    58 physical read bytes                                                          32768
 10695    37 physical read total IO requests                                                  4
 10695    39 physical read total bytes                                                    32768
 10695    38 physical read total multi block requests                                         0
 10695    54 physical reads                                                                   4
 10695    55 physical reads cache                                                             4
 10695   114 physical reads cache prefetch                                                    0
 10695    56 physical reads direct                                                            0

**************************************************
Stats at the last iteration just before query ends
**************************************************

Slave 1 stats

   Sid Stat# NAME                                                                         VALUE
------ ----- ------------------------------------------------------------- --------------------
 11092    57 physical read IO requests                                                    93796
 11092    58 physical read bytes                                                     5294833664
 11092    37 physical read total IO requests                                              93662
 11092    39 physical read total bytes                                               5287428096
 11092    38 physical read total multi block requests                                     78927
 11092    54 physical reads                                                              646158
 11092    55 physical reads cache                                                         13915
 11092   114 physical reads cache prefetch                                                    0
 11092    56 physical reads direct                                                       632356


Slave 2 stats


   Sid Stat# NAME                                                                         VALUE
------ ----- ------------------------------------------------------------- --------------------
  9351    57 physical read IO requests                                                    92785
  9351    58 physical read bytes                                                     4911235072
  9351    37 physical read total IO requests                                              92660
  9351    39 physical read total bytes                                               4904845312
  9351    38 physical read total multi block requests                                     72387
  9351    54 physical reads                                                              599208
  9351    55 physical reads cache                                                         19579
  9351   114 physical reads cache prefetch                                                    0
  9351    56 physical reads direct                                                       579734


**********************
Stats after query ends
**********************


coordinator stats


   Sid Stat# NAME                                                                         VALUE
------ ----- ------------------------------------------------------------- --------------------
 10695    57 physical read IO requests                                                   188227
 10695    58 physical read bytes                                                     1710325760
 10695    37 physical read total IO requests                                             188227
 10695    39 physical read total bytes                                               1710325760
 10695    38 physical read total multi block requests                                    152957
 10695    54 physical reads                                                             1257356
 10695    55 physical reads cache                                                         33705
 10695   114 physical reads cache prefetch                                                    0
 10695    56 physical reads direct                                                      1223651

There is large discrepency in the “physical read bytes” and “physical read total bytes” values.

There were some bugs reported in older versions of oracle that could explain some discrepencies.
Bug no’s – 715649, 818237 (which seems relevant)

So we should keep this in mind from taking the coordinator stats from V$SESSTAT at face value.

Coming to bugs, i remember (and sometimes follow) reading on Tanel Poder’s blog that he reads on oracle bug’s to get those scraps of good oracle internal information that oracle developers occasionally and unintentionally reveal in them

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: