Exadata – direct path write temp and flash disks

Some time ago one of my students asked me if temp segments are being written to flash disks on Exadata… Well I wasn’t sure 🙂 But recently I had some time to check it.

Let’s create some query that will generate temp segment:

SQL> ;
  1  with v_cust as
  2  (
  3    select CUST_LAST_NAME, CUST_FIRST_NAME, CUST_CITY,
  4	      count(1) over (partition by cust_city) as cnt
  5    from customers
  6  )
  7  select *
  8  from v_cust
  9* where cnt<=2
SQL> /

no rows selected

SQL> get workarea
  1  select operation_Type, last_tempseg_size/1024/1024
  2  from v$sql_workarea
  3* where sql_id='7c6y1n3tn8rd6'
SQL> /

OPERATION_TYPE		       LAST_TEMPSEG_SIZE/1024/1024
------------------------------ ---------------------------
WINDOW (SORT)					       256
SEGMENT SCAN

Great. Now we have to do some tracing at the cell servers level. The process responsible for all the reads and writes at cells is cellsrv. It’s working in threaded mode, so we will have to follow all forks.

Before we do that, we will save info about all physical disks at each cell to a separate file – we will need this later:

exa1celadm01

[root@exa1celadm01 9317]# cellcli -e list physicaldisk attributes deviceName,diskType > /tmp/cell_disks.out

exa1celadm02

[root@exa1celadm02 9278]# cellcli -e list physicaldisk attributes deviceName,diskType > /tmp/cell_disks.out

exa1celadm03

[root@exa1celadm03 9299]# cellcli -e list physicaldisk attributes deviceName,diskType > /tmp/cell_disks.out

OK. Now we will start the strace at each cell (note that this is my training Exadata and I’m sure that there is no one but me on it right now).

exa1celadm01

[root@exa1celadm01 9317]# cd /proc/9317
[root@exa1celadm01 9317]# strace -fF -o /tmp/cellsrv.trc -p 9317
Process 9317 attached with 139 threads - interrupt to quit

exa1celadm02

[root@exa1celadm02 9278]# cd /proc/9278
[root@exa1celadm02 9278]# strace -fF -o /tmp/cellsrv.trc -p 9278
Process 9278 attached with 139 threads - interrupt to quit

exa1celadm03

[root@exa1celadm03 9299]# cd /proc/9299
[root@exa1celadm03 9299]# strace -fF -o /tmp/cellsrv.trc -p 9299
Process 9299 attached with 139 threads - interrupt to quit

After query execution we can analyze the trace files 🙂

exa1celadm01

[root@exa1celadm01 9317]# grep "pwrite(" /tmp/cellsrv.trc | awk '{print("ls -al task/" $1 "/fd/"substr($2,8,length($2)-8) ) }' | awk '{system($0)}' | awk '{system("grep " $NF " /tmp/cell_disks.out")}' | awk '{names[$0]++;} END {for(dev in names){print dev "\t" names[dev]}}'
	 /dev/sdu 	 FlashDisk	3
	 /dev/sdz 	 FlashDisk	2
	 /dev/sds 	 FlashDisk	3
	 /dev/sdq 	 FlashDisk	3
	 /dev/sdx 	 FlashDisk	3
	 /dev/sdo 	 FlashDisk	3
	 /dev/sdm 	 FlashDisk	2
	 /dev/sdv 	 FlashDisk	3
	 /dev/sdt 	 FlashDisk	2
	 /dev/sdr 	 FlashDisk	2
	 /dev/sdy 	 FlashDisk	3
	 /dev/sdp 	 FlashDisk	2
	 /dev/sdw 	 FlashDisk	3
	 /dev/sdn 	 FlashDisk	3
	 /dev/sdab	 FlashDisk	3
	 /dev/sdaa	 FlashDisk	2

exa1celadm02

[root@exa1celadm02 9278]# grep "pwrite(" /tmp/cellsrv.trc | awk '{print("ls -al task/" $1 "/fd/"substr($2,8,length($2)-8) ) }' | awk '{system($0)}' | awk '{system("grep " $NF " /tmp/cell_disks.out")}' | awk '{names[$0]++;} END {for(dev in names){print dev "\t" names[dev]}}'
	 /dev/sdu 	 FlashDisk	8
	 /dev/sdz 	 FlashDisk	7
	 /dev/sds 	 FlashDisk	8
	 /dev/sdx 	 FlashDisk	7
	 /dev/sdq 	 FlashDisk	7
	 /dev/sdo 	 FlashDisk	7
	 /dev/sdm 	 FlashDisk	7
	 /dev/sdv 	 FlashDisk	7
	 /dev/sdt 	 FlashDisk	7
	 /dev/sdy 	 FlashDisk	7
	 /dev/sdr 	 FlashDisk	8
	 /dev/sdp 	 FlashDisk	7
	 /dev/sdw 	 FlashDisk	7
	 /dev/sdn 	 FlashDisk	7
	 /dev/sdl 	 FlashDisk	7
	 /dev/sdaa	 FlashDisk	7

exa1celadm03

[root@exa1celadm03 9299]# grep "pwrite(" /tmp/cellsrv.trc | awk '{print("ls -al task/" $1 "/fd/"substr($2,8,length($2)-8) ) }' | awk '{system($0)}' | awk '{system("grep " $NF " /tmp/cell_disks.out")}' | awk '{names[$0]++;} END {for(dev in names){print dev "\t" names[dev]}}'
	 /dev/sdu 	 FlashDisk	9
	 /dev/sdz 	 FlashDisk	9
	 /dev/sds 	 FlashDisk	9
	 /dev/sdx 	 FlashDisk	8
	 /dev/sdq 	 FlashDisk	9
	 /dev/sdo 	 FlashDisk	8
	 /dev/sdm 	 FlashDisk	9
	 /dev/sdv 	 FlashDisk	9
	 /dev/sdt 	 FlashDisk	9
	 /dev/sdr 	 FlashDisk	9
	 /dev/sdy 	 FlashDisk	9
	 /dev/sdp 	 FlashDisk	9
	 /dev/sdw 	 FlashDisk	9
	 /dev/sdn 	 FlashDisk	9
	 /dev/sdl 	 FlashDisk	9
	 /dev/sdaa	 FlashDisk	9

We can see that "pwrite" was used only to write on flash disks. I suppose this is the answer we were looking for.