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
Leave a Reply