Oracle JDBC types on the wire – DATE

In this mini-series I’m going to show various Oracle types representation on network layer. The scope of of investigation Oracle JDBC Thin driver/interface.
I’m going to catch bind variables appearance on network layer.

Year representation

Lets start with year representation:

I have prepared a statement with 40 Date binds, like that:
0 1970-01-01
1 1971-01-01
2 1972-01-01
….
38 2008-01-01
39 2009-01-01

below is a Wireshark dump of corresponding network packet

                                                                                                                        

0000   02 00 00 00 45 00 05 77 1f c9 40 00 80 06 00 00  ....E..w..@.....
0010   c0 a8 38 01 c0 a8 38 01 c8 6e 05 f2 58 8c 7b 55  ..8...8..n..X.{U
0020   82 8d 61 44 50 18 00 1c 82 15 00 00 05 4f 00 00  ..aDP........O..
0030   06 00 00 00 00 00 11 69 06 01 01 01 01 02 03 5e  .......i.......^
0040   07 02 80 29 00 01 02 01 8f 01 01 0d 00 00 04 ff  ...)............
0050   ff ff ff 01 0a 04 7f ff ff ff 01 01 28 00 00 00  ............(...
0060   00 00 00 00 00 00 01 00 00 00 00 00 53 45 4c 45  ............SELE
0070   43 54 20 2f 2a 77 20 34 30 49 73 73 75 65 20 2a  CT /*w 40Issue *
0080   2f 20 66 31 2c 66 32 2c 66 33 20 46 52 4f 4d 20  / f1,f2,f3 FROM
0090   44 44 54 45 53 54 20 57 48 45 52 45 20 28 66 31  DDTEST WHERE (f1
00a0   2c 66 32 2c 66 33 2c 66 34 2c 66 35 2c 66 36 2c  ,f2,f3,f4,f5,f6,
00b0   66 37 2c 66 38 2c 66 39 2c 66 31 30 2c 66 31 31  f7,f8,f9,f10,f11
00c0   2c 66 31 32 2c 66 31 33 2c 66 31 34 2c 66 31 35  ,f12,f13,f14,f15
00d0   2c 66 31 36 2c 66 31 37 2c 66 31 38 2c 66 31 39  ,f16,f17,f18,f19
00e0   2c 66 32 30 2c 66 32 31 2c 66 32 32 2c 66 32 33  ,f20,f21,f22,f23
00f0   2c 66 32 34 2c 66 32 35 2c 66 32 36 2c 66 32 37  ,f24,f25,f26,f27
0100   2c 66 32 38 2c 66 32 39 2c 66 33 30 2c 66 33 31  ,f28,f29,f30,f31
0110   2c 66 33 32 2c 66 33 33 2c 66 33 34 2c 66 33 35  ,f32,f33,f34,f35
0120   2c 66 33 36 2c 66 33 37 2c 66 33 38 2c 66 33 39  ,f36,f37,f38,f39
0130   2c 66 34 30 29 20 49 4e 20 28 28 3a 66 31 2c 3a  ,f40) IN ((:f1,:
0140   66 32 2c 3a 66 33 2c 3a 66 34 2c 3a 66 35 2c 3a  f2,:f3,:f4,:f5,:
0150   66 36 2c 3a 66 37 2c 3a 66 38 2c 3a 66 39 2c 3a  f6,:f7,:f8,:f9,:
0160   66 31 30 2c 3a 66 31 31 2c 3a 66 31 32 2c 3a 66  f10,:f11,:f12,:f
0170   31 33 2c 3a 66 31 34 2c 3a 66 31 35 2c 3a 66 31  13,:f14,:f15,:f1
0180   36 2c 3a 66 31 37 2c 3a 66 31 38 2c 3a 66 31 39  6,:f17,:f18,:f19
0190   2c 3a 66 32 30 2c 3a 66 32 31 2c 3a 66 32 32 2c  ,:f20,:f21,:f22,
01a0   3a 66 32 33 2c 3a 66 32 34 2c 3a 66 32 35 2c 3a  :f23,:f24,:f25,:
01b0   66 32 36 2c 3a 66 32 37 2c 3a 66 32 38 2c 3a 66  f26,:f27,:f28,:f
01c0   32 39 2c 3a 66 33 30 2c 3a 66 33 31 2c 3a 66 33  29,:f30,:f31,:f3
01d0   32 2c 3a 66 33 33 2c 3a 66 33 34 2c 3a 66 33 35  2,:f33,:f34,:f35
01e0   2c 3a 66 33 36 2c 3a 66 33 37 2c 3a 66 33 38 2c  ,:f36,:f37,:f38,
01f0   3a 66 33 39 2c 3a 66 34 30 29 29 01 01 00 00 00  :f39,:f40)).....
0200   00 00 00 01 01 00 00 00 00 00 0c 03 00 00 01 07  ................
0210   00 00 00 00 01 b2 01 00 0c 03 00 00 01 07 00 00  ................
0220   00 00 01 b2 01 00 0c 03 00 00 01 07 00 00 00 00  ................
0230   01 b2 01 00 0c 03 00 00 01 07 00 00 00 00 01 b2  ................
0240   01 00 0c 03 00 00 01 07 00 00 00 00 01 b2 01 00  ................
0250   0c 03 00 00 01 07 00 00 00 00 01 b2 01 00 0c 03  ................
0260   00 00 01 07 00 00 00 00 01 b2 01 00 0c 03 00 00  ................
0270   01 07 00 00 00 00 01 b2 01 00 0c 03 00 00 01 07  ................
0280   00 00 00 00 01 b2 01 00 0c 03 00 00 01 07 00 00  ................
0290   00 00 01 b2 01 00 0c 03 00 00 01 07 00 00 00 00  ................
02a0   01 b2 01 00 0c 03 00 00 01 07 00 00 00 00 01 b2  ................
02b0   01 00 0c 03 00 00 01 07 00 00 00 00 01 b2 01 00  ................
02c0   0c 03 00 00 01 07 00 00 00 00 01 b2 01 00 0c 03  ................
02d0   00 00 01 07 00 00 00 00 01 b2 01 00 0c 03 00 00  ................
02e0   01 07 00 00 00 00 01 b2 01 00 0c 03 00 00 01 07  ................
02f0   00 00 00 00 01 b2 01 00 0c 03 00 00 01 07 00 00  ................
0300   00 00 01 b2 01 00 0c 03 00 00 01 07 00 00 00 00  ................
0310   01 b2 01 00 0c 03 00 00 01 07 00 00 00 00 01 b2  ................
0320   01 00 0c 03 00 00 01 07 00 00 00 00 01 b2 01 00  ................
0330   0c 03 00 00 01 07 00 00 00 00 01 b2 01 00 0c 03  ................
0340   00 00 01 07 00 00 00 00 01 b2 01 00 0c 03 00 00  ................
0350   01 07 00 00 00 00 01 b2 01 00 0c 03 00 00 01 07  ................
0360   00 00 00 00 01 b2 01 00 0c 03 00 00 01 07 00 00  ................
0370   00 00 01 b2 01 00 0c 03 00 00 01 07 00 00 00 00  ................
0380   01 b2 01 00 0c 03 00 00 01 07 00 00 00 00 01 b2  ................
0390   01 00 0c 03 00 00 01 07 00 00 00 00 01 b2 01 00  ................
03a0   0c 03 00 00 01 07 00 00 00 00 01 b2 01 00 0c 03  ................
03b0   00 00 01 07 00 00 00 00 01 b2 01 00 0c 03 00 00  ................
03c0   01 07 00 00 00 00 01 b2 01 00 0c 03 00 00 01 07  ................
03d0   00 00 00 00 01 b2 01 00 0c 03 00 00 01 07 00 00  ................
03e0   00 00 01 b2 01 00 0c 03 00 00 01 07 00 00 00 00  ................
03f0   01 b2 01 00 0c 03 00 00 01 07 00 00 00 00 01 b2  ................
0400   01 00 0c 03 00 00 01 07 00 00 00 00 01 b2 01 00  ................
0410   0c 03 00 00 01 07 00 00 00 00 01 b2 01 00 0c 03  ................
0420   00 00 01 07 00 00 00 00 01 b2 01 00 0c 03 00 00  ................
0430   01 07 00 00 00 00 01 b2 01 00 07 07 77 aa 01 01  ............w...
0440   01 01 01 07 77 ab 01 01 01 01 01 07 77 ac 01 01  ....w.......w...
0450   01 01 01 07 77 ad 01 01 01 01 01 07 77 ae 01 01  ....w.......w...
0460   01 01 01 07 77 af 01 01 01 01 01 07 77 b0 01 01  ....w.......w...
0470   01 01 01 07 77 b1 01 01 01 01 01 07 77 b2 01 01  ....w.......w...
0480   01 01 01 07 77 b3 01 01 01 01 01 07 77 b4 01 01  ....w.......w...
0490   01 01 01 07 77 b5 01 01 01 01 01 07 77 b6 01 01  ....w.......w...
04a0   01 01 01 07 77 b7 01 01 01 01 01 07 77 b8 01 01  ....w.......w...
04b0   01 01 01 07 77 b9 01 01 01 01 01 07 77 ba 01 01  ....w.......w...
04c0   01 01 01 07 77 bb 01 01 01 01 01 07 77 bc 01 01  ....w.......w...
04d0   01 01 01 07 77 bd 01 01 01 01 01 07 77 be 01 01  ....w.......w...
04e0   01 01 01 07 77 bf 01 01 01 01 01 07 77 c0 01 01  ....w.......w...
04f0   01 01 01 07 77 c1 01 01 01 01 01 07 77 c2 01 01  ....w.......w...
0500   01 01 01 07 77 c3 01 01 01 01 01 07 77 c4 01 01  ....w.......w...
0510   01 01 01 07 77 c5 01 01 01 01 01 07 77 c6 01 01  ....w.......w...
0520   01 01 01 07 77 c7 01 01 01 01 01 07 78 64 01 01  ....w.......xd..
0530   01 01 01 07 78 65 01 01 01 01 01 07 78 66 01 01  ....xe......xf..
0540   01 01 01 07 78 67 01 01 01 01 01 07 78 68 01 01  ....xg......xh..
0550   01 01 01 07 78 69 01 01 01 01 01 07 78 6a 01 01  ....xi......xj..
0560   01 01 01 07 78 6b 01 01 01 01 01 07 78 6c 01 01  ....xk......xl..
0570   01 01 01 07 78 6d 01 01 01 01 01                 ....xm.....

At the end of the packet your can see a place where bind values begin

JDBCdate
A common pattern can be seen
07 (which is bind length) and then 7 bytes of the representation (I believe you already guessed that DATE data type representation has 7 bytes)

Let have a look in more details:

  

07 77 aa 01 01 01 01 01    1970-01-01
07 77 ab 01 01 01 01 01    1971-01-01 
07 77 ac 01 01 01 01 01    1972-01-01
07 77 ad 01 01 01 01 01    1973-01-01
....................................
07 77 c7 01 01 01 01 01    1999-01-01
07 78 64 01 01 01 01 01    2000-01-01
.........................  

It seems 1st 2 bytes represent year part of the date
and the most interesting part is a century boundaries.

It can be guessed that 1st byte represents a century part of the year
and 2nd byte represents 2 digit year (starting with 100 0x64==100)

Day and Month representation

Let’s continue with day and month representaion.

I have prepared other test case with binds like:

0 1970-01-01
1 1970-01-02
2 1970-01-03
……………
38 1970-02-08
39 1970-02-09

 
0000   11 69 06 01 01 01 01 03 03 5e 07 02 80 29 00 01  .i.......^...)..
0010   02 01 8f 01 01 0d 00 00 04 ff ff ff ff 01 0a 04  ................
0020   7f ff ff ff 01 01 28 00 00 00 00 00 00 00 00 00  ......(.........
0030   01 00 00 00 00 00 53 45 4c 45 43 54 20 2f 2a 77  ......SELECT /*w
0040   20 34 30 49 73 73 75 65 20 2a 2f 20 66 31 2c 66   40Issue */ f1,f
0050   32 2c 66 33 20 46 52 4f 4d 20 44 44 54 45 53 54  2,f3 FROM DDTEST
0060   20 57 48 45 52 45 20 28 66 31 2c 66 32 2c 66 33   WHERE (f1,f2,f3
0070   2c 66 34 2c 66 35 2c 66 36 2c 66 37 2c 66 38 2c  ,f4,f5,f6,f7,f8,
0080   66 39 2c 66 31 30 2c 66 31 31 2c 66 31 32 2c 66  f9,f10,f11,f12,f
0090   31 33 2c 66 31 34 2c 66 31 35 2c 66 31 36 2c 66  13,f14,f15,f16,f
00a0   31 37 2c 66 31 38 2c 66 31 39 2c 66 32 30 2c 66  17,f18,f19,f20,f
00b0   32 31 2c 66 32 32 2c 66 32 33 2c 66 32 34 2c 66  21,f22,f23,f24,f
00c0   32 35 2c 66 32 36 2c 66 32 37 2c 66 32 38 2c 66  25,f26,f27,f28,f
00d0   32 39 2c 66 33 30 2c 66 33 31 2c 66 33 32 2c 66  29,f30,f31,f32,f
00e0   33 33 2c 66 33 34 2c 66 33 35 2c 66 33 36 2c 66  33,f34,f35,f36,f
00f0   33 37 2c 66 33 38 2c 66 33 39 2c 66 34 30 29 20  37,f38,f39,f40)
0100   49 4e 20 28 28 3a 66 31 2c 3a 66 32 2c 3a 66 33  IN ((:f1,:f2,:f3
0110   2c 3a 66 34 2c 3a 66 35 2c 3a 66 36 2c 3a 66 37  ,:f4,:f5,:f6,:f7
0120   2c 3a 66 38 2c 3a 66 39 2c 3a 66 31 30 2c 3a 66  ,:f8,:f9,:f10,:f
0130   31 31 2c 3a 66 31 32 2c 3a 66 31 33 2c 3a 66 31  11,:f12,:f13,:f1
0140   34 2c 3a 66 31 35 2c 3a 66 31 36 2c 3a 66 31 37  4,:f15,:f16,:f17
0150   2c 3a 66 31 38 2c 3a 66 31 39 2c 3a 66 32 30 2c  ,:f18,:f19,:f20,
0160   3a 66 32 31 2c 3a 66 32 32 2c 3a 66 32 33 2c 3a  :f21,:f22,:f23,:
0170   66 32 34 2c 3a 66 32 35 2c 3a 66 32 36 2c 3a 66  f24,:f25,:f26,:f
0180   32 37 2c 3a 66 32 38 2c 3a 66 32 39 2c 3a 66 33  27,:f28,:f29,:f3
0190   30 2c 3a 66 33 31 2c 3a 66 33 32 2c 3a 66 33 33  0,:f31,:f32,:f33
01a0   2c 3a 66 33 34 2c 3a 66 33 35 2c 3a 66 33 36 2c  ,:f34,:f35,:f36,
01b0   3a 66 33 37 2c 3a 66 33 38 2c 3a 66 33 39 2c 3a  :f37,:f38,:f39,:
01c0   66 34 30 29 29 01 01 00 00 00 00 00 00 01 01 00  f40))...........
01d0   00 00 00 00 0c 03 00 00 01 07 00 00 00 00 01 b2  ................
01e0   01 00 0c 03 00 00 01 07 00 00 00 00 01 b2 01 00  ................
01f0   0c 03 00 00 01 07 00 00 00 00 01 b2 01 00 0c 03  ................
0200   00 00 01 07 00 00 00 00 01 b2 01 00 0c 03 00 00  ................
0210   01 07 00 00 00 00 01 b2 01 00 0c 03 00 00 01 07  ................
0220   00 00 00 00 01 b2 01 00 0c 03 00 00 01 07 00 00  ................
0230   00 00 01 b2 01 00 0c 03 00 00 01 07 00 00 00 00  ................
0240   01 b2 01 00 0c 03 00 00 01 07 00 00 00 00 01 b2  ................
0250   01 00 0c 03 00 00 01 07 00 00 00 00 01 b2 01 00  ................
0260   0c 03 00 00 01 07 00 00 00 00 01 b2 01 00 0c 03  ................
0270   00 00 01 07 00 00 00 00 01 b2 01 00 0c 03 00 00  ................
0280   01 07 00 00 00 00 01 b2 01 00 0c 03 00 00 01 07  ................
0290   00 00 00 00 01 b2 01 00 0c 03 00 00 01 07 00 00  ................
02a0   00 00 01 b2 01 00 0c 03 00 00 01 07 00 00 00 00  ................
02b0   01 b2 01 00 0c 03 00 00 01 07 00 00 00 00 01 b2  ................
02c0   01 00 0c 03 00 00 01 07 00 00 00 00 01 b2 01 00  ................
02d0   0c 03 00 00 01 07 00 00 00 00 01 b2 01 00 0c 03  ................
02e0   00 00 01 07 00 00 00 00 01 b2 01 00 0c 03 00 00  ................
02f0   01 07 00 00 00 00 01 b2 01 00 0c 03 00 00 01 07  ................
0300   00 00 00 00 01 b2 01 00 0c 03 00 00 01 07 00 00  ................
0310   00 00 01 b2 01 00 0c 03 00 00 01 07 00 00 00 00  ................
0320   01 b2 01 00 0c 03 00 00 01 07 00 00 00 00 01 b2  ................
0330   01 00 0c 03 00 00 01 07 00 00 00 00 01 b2 01 00  ................
0340   0c 03 00 00 01 07 00 00 00 00 01 b2 01 00 0c 03  ................
0350   00 00 01 07 00 00 00 00 01 b2 01 00 0c 03 00 00  ................
0360   01 07 00 00 00 00 01 b2 01 00 0c 03 00 00 01 07  ................
0370   00 00 00 00 01 b2 01 00 0c 03 00 00 01 07 00 00  ................
0380   00 00 01 b2 01 00 0c 03 00 00 01 07 00 00 00 00  ................
0390   01 b2 01 00 0c 03 00 00 01 07 00 00 00 00 01 b2  ................
03a0   01 00 0c 03 00 00 01 07 00 00 00 00 01 b2 01 00  ................
03b0   0c 03 00 00 01 07 00 00 00 00 01 b2 01 00 0c 03  ................
03c0   00 00 01 07 00 00 00 00 01 b2 01 00 0c 03 00 00  ................
03d0   01 07 00 00 00 00 01 b2 01 00 0c 03 00 00 01 07  ................
03e0   00 00 00 00 01 b2 01 00 0c 03 00 00 01 07 00 00  ................
03f0   00 00 01 b2 01 00 0c 03 00 00 01 07 00 00 00 00  ................
0400   01 b2 01 00 07 07 77 aa 01 01 01 01 01 07 77 aa  ......w.......w.
0410   01 02 01 01 01 07 77 aa 01 03 01 01 01 07 77 aa  ......w.......w.
0420   01 04 01 01 01 07 77 aa 01 05 01 01 01 07 77 aa  ......w.......w.
0430   01 06 01 01 01 07 77 aa 01 07 01 01 01 07 77 aa  ......w.......w.
0440   01 08 01 01 01 07 77 aa 01 09 01 01 01 07 77 aa  ......w.......w.
0450   01 0a 01 01 01 07 77 aa 01 0b 01 01 01 07 77 aa  ......w.......w.
0460   01 0c 01 01 01 07 77 aa 01 0d 01 01 01 07 77 aa  ......w.......w.
0470   01 0e 01 01 01 07 77 aa 01 0f 01 01 01 07 77 aa  ......w.......w.
0480   01 10 01 01 01 07 77 aa 01 11 01 01 01 07 77 aa  ......w.......w.
0490   01 12 01 01 01 07 77 aa 01 13 01 01 01 07 77 aa  ......w.......w.
04a0   01 14 01 01 01 07 77 aa 01 15 01 01 01 07 77 aa  ......w.......w.
04b0   01 16 01 01 01 07 77 aa 01 17 01 01 01 07 77 aa  ......w.......w.
04c0   01 18 01 01 01 07 77 aa 01 19 01 01 01 07 77 aa  ......w.......w.
04d0   01 1a 01 01 01 07 77 aa 01 1b 01 01 01 07 77 aa  ......w.......w.
04e0   01 1c 01 01 01 07 77 aa 01 1d 01 01 01 07 77 aa  ......w.......w.
04f0   01 1e 01 01 01 07 77 aa 01 1f 01 01 01 07 77 aa  ......w.......w.
0500   02 01 01 01 01 07 77 aa 02 02 01 01 01 07 77 aa  ......w.......w.
0510   02 03 01 01 01 07 77 aa 02 04 01 01 01 07 77 aa  ......w.......w.
0520   02 05 01 01 01 07 77 aa 02 06 01 01 01 07 77 aa  ......w.......w.
0530   02 07 01 01 01 07 77 aa 02 08 01 01 01 07 77 aa  ......w.......w.
0540   02 09 01 01 01                                   .....

Let’s have a look in more details, I have reformatted it a bit:

 
07 77 aa 01 01 01 01 01      1970-01-01
07 77 aa 01 02 01 01 01      1970-01-02
07 77 aa 01 03 01 01 01      1970-01-03 
.......................................
07 77 aa 01 1f 01 01 01      1970-01-31
07 77 aa 02 01 01 01 01      1970-02-01
07 77 aa 02 02 01 01 01      1970-02-02 
07 77 aa 02 03 01 01 01      1970-02-03

So 1st byte is bind length
2nd byte century part of the year part
3rd byte 2 digit part of the year part as we saw before

4th byte seems month representation as 01- Jan, 02 – Feb, 03 – Mar
5th byte seems day representation

and finally:

JDBCDate3

Advertisements

systemtap array sorting descending

I am quite newbie in “systemtap programming” so recently got stack at a very simple point (I needed to sort an array by key in descending order), I so I decided to share my obstacles and the solution.

Here is a demonstration how to do various sorts for associative arrays


global arr

probe begin {
arr[1] = "z";
arr[2] ="d";
arr[3] = "m";
arr[4] = "a";

printf("default sorting -> (cnt+ in arr)\n");
foreach (cnt in arr) {
printf(" %d -> %s\n",cnt, arr[cnt]);
}

printf("variant 2 (cnt in arr+)\n");
foreach (cnt in arr+) {
printf(" %d -> %s\n",cnt, arr[cnt]);
}

printf("variant 2 (cnt in arr-)\n");
foreach (cnt in arr-) {
printf(" %d -> %s\n",cnt, arr[cnt]);
}

printf("variant 4 (cnt- in arr)\n");
foreach (cnt in arr+) {
printf(" %d -> %s\n",cnt, arr[cnt]);
}

exit();
}

and here is the output of the script above:

default sorting -> (cnt+ in arr)
1 -> z
2 -> d
3 -> m
4 -> a

default sort is by key ascending

variant 2 (cnt in arr+)
4 -> a
2 -> d
3 -> m
1 -> z

sort by by value ascending

variant 3 (cnt in arr-)
1 -> z
3 -> m
2 -> d
4 -> a

sort by value descending

variant 4 (cnt- in arr)
4 -> a
2 -> d
3 -> m
1 -> z

and finally sort by key descending

Follow-up of one discussion

That is short post related to a recent discussion on
Jonanthan Lewis’s blog.

During my experiments I found that sys.dbms_shared_pool.purge removes cursor from vsql (v$sqlarea) but it is still in x$kglob

You can easily check it by yourself

select ‘dskhk56khkj’ FROM dual;

— check that the cursor above in the shared pool (visible part)

select a.* from v$sql a
where sql_text like ‘select ”dskhk56khkj%’;

you can see one record there

— and you can execute below as SYS user
— select kglhdpar, kglhdadr, kglobt03, kglnaobj from x$kglob where kglnaobj
— like ‘select ”dskhk56khkj%’ and kglnaobj not like ‘%kgl%’;

Let’s clean up our cursor

column pexec NEW_VALUE doexec
select ‘sys.dbms_shared_pool.purge(”’|| address || ‘,’ || hash_value || ”’, ”c”)’ pexec from v$sqlarea
where sql_text like ‘select ”dskhk56khkj%’;

exec &doexec

and check shared pool for the cursor again

select a.* from v$sql a
where sql_text like ‘select ”dskhk56khkj%’;

The cursor has gone

However if you execute the query below (AS SYS), you still would see some records (actually 2)
— select kglhdpar, kglhdadr, kglobt03, kglnaobj from x$kglob where kglnaobj
— like ‘select ”dskhk56khkj%’ and kglnaobj not like ‘%kgl%’;

PS
ALTER SYSTEM FLUSH SHARED_POOL
behaves differently and cleans up all stuff completely.

PPS
That “hidden area” can keep some cursor statistics like LOADS so you can see how some attributes were “resurrected from nothing” 🙂

Some missing optimization

Last time I blogged about incorrect BITMAP CONVERSION TO ROWIDS operation costing by Oracle optimizer, here is my “near real life” example.

I have 2 huge tables (1TB and 100++GB) and
I have some “unit_id” column where id value repeats quite often,
both tables have bitmap indexes on “init_id”.

I need to find “unit ids” in table1 which already exists in table2.
First idea was to compare indexes which are relatively small instead of manipulating huge tables.

Initial setup


set lines 200
set pages 0

DROP TABLE IDXTEST;
DROP TABLE IDXTEST2;

CREATE TABLE IDXTEST AS select 1e6 + rownum id, 1e6 + trunc(rownum/1000)*2 id2, rpad('a',399,'a') filler
from dual connect by level < 5e5
order by dbms_random.value;

CREATE BITMAP INDEX IDXTEST_IDX1 ON IDXTEST (id2);

CREATE TABLE IDXTEST2 AS select 1e6 + rownum id, 1e6 + trunc(rownum/1000)*2 id2, rpad('a',399,'a') filler
from dual connect by level < 1e6
order by dbms_random.value;

CREATE BITMAP INDEX IDXTEST2_IDX1 ON IDXTEST2 (id2);

execute dbms_stats.gather_table_stats(null, 'IDXTEST');

execute dbms_stats.gather_table_stats(null, 'IDXTEST2');

So I have started with

select id2 FROM IDXTEST WHERE id2 IN (SELECT id2 FROM IDXTEST2);

and observed

                                                                                                                        
Plan hash value: 2149971028        
                                   
-------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name          | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |               |      1 |        |       |       |  1962 (100)|          |    499K|00:00:10.53 |     766 |
|*  1 |  HASH JOIN SEMI               |               |      1 |    499K|  5859K|  8792K|  1962   (1)| 00:00:24 |    499K|00:00:10.53 |     766 |
|   2 |   BITMAP CONVERSION TO ROWIDS |               |      1 |    499K|  2929K|       |   225   (0)| 00:00:03 |    499K|00:00:01.19 |     258 |
|   3 |    BITMAP INDEX FAST FULL SCAN| IDXTEST_IDX1  |      1 |        |       |       |            |          |    500 |00:00:00.01 |     258 |
|   4 |   BITMAP CONVERSION TO ROWIDS |               |      1 |    999K|  5859K|       |   450   (0)| 00:00:06 |    499K|00:00:01.15 |     508 |
|   5 |    BITMAP INDEX FAST FULL SCAN| IDXTEST2_IDX1 |      1 |        |       |       |            |          |    500 |00:00:00.01 |     508 |
-------------------------------------------------------------------------------------------------------------------------------------------------
                                   
Predicate Information (identified by operation id):                                       
---------------------------------------------------                                       
                                   
   1 - access("ID2"="ID2")         

So 1st of all I see that only indexes were used as I’ve expected and that is good.
I also see 2 “BITMAP CONVERSION TO ROWIDS” operations in the plan which is not so good.
I’ve almost immediately realized that 1st “BITMAP CONVERSION TO ROWIDS” (line marked 2) is completely my fault. I need to specify that only unique ids are interesting for me otherwise “BITMAP CONVERSION TO ROWIDS” operation is unavoidable (to provide as many repeating values as exists in table rows).
However I have been a bit surprised by operation on line 4 (which I believed unnecessary).

So I have rewritten my query as


select distinct id2 FROM IDXTEST WHERE id2 IN (SELECT id2 FROM IDXTEST2)

and got

Plan hash value: 3034616292                                                                                                                                                                             
                                                                                                                                                                                                        
---------------------------------------------------------------------------------------------------------------------------------------------                                                           
| Id  | Operation                       | Name            | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |                                                           
---------------------------------------------------------------------------------------------------------------------------------------------                                                           
|   0 | SELECT STATEMENT                |                 |      1 |        |       |   708 (100)|          |    500 |00:00:11.64 |     766 |                                                           
|   1 |  HASH UNIQUE                    |                 |      1 |    500 |  6000 |   708   (5)| 00:00:09 |    500 |00:00:11.64 |     766 |                                                           
|*  2 |   HASH JOIN                     |                 |      1 |    500K|  5859K|   693   (3)| 00:00:09 |    499K|00:00:10.38 |     766 |                                                           
|   3 |    VIEW                         | VW_DTP_6945D498 |      1 |    500 |  3000 |   239   (6)| 00:00:03 |    500 |00:00:00.02 |     258 |                                                           
|   4 |     HASH UNIQUE                 |                 |      1 |    500 |  3000 |   239   (6)| 00:00:03 |    500 |00:00:00.01 |     258 |                                                           
|   5 |      BITMAP INDEX FAST FULL SCAN| IDXTEST_IDX1    |      1 |        |       |            |          |    500 |00:00:00.01 |     258 |                                                           
|   6 |    BITMAP CONVERSION TO ROWIDS  |                 |      1 |    999K|  5859K|   450   (0)| 00:00:06 |    999K|00:00:02.35 |     508 |                                                           
|   7 |     BITMAP INDEX FAST FULL SCAN | IDXTEST2_IDX1   |      1 |        |       |            |          |   1000 |00:00:00.01 |     508 |                                                           
---------------------------------------------------------------------------------------------------------------------------------------------                                                           
                                                                                                                                                                                                        
Predicate Information (identified by operation id):                                                                                                                                                     
---------------------------------------------------                                                                                                                                                     
                                                                                                                                                                                                        
   2 - access("ITEM_1"="ID2")

So I successfully got rid 1st BITMAP CONVERSION TO ROWIDS operation
but have quite ineffective HASH JOIN (line 2) which produced a big data set eliminated by HASH UNIQUE (line 1), and I still have 2nd “BITMAP CONVERSION TO ROWIDS”

So I have rewritten a bit more as


select id2 FROM (select distinct id2 FROM IDXTEST t1) t11 WHERE t11.id2
IN (SELECT t2.id2 FROM IDXTEST2 t2)

and corresponding execution plan is a bit better.

Plan hash value: 174643609                                                                                                                                                                              
                                                                                                                                                                                                        
------------------------------------------------------------------------------------------------------------------------------------------                                                              
| Id  | Operation                      | Name          | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |                                                              
------------------------------------------------------------------------------------------------------------------------------------------                                                              
|   0 | SELECT STATEMENT               |               |      1 |        |       |   693 (100)|          |    500 |00:00:04.42 |     533 |                                                              
|*  1 |  HASH JOIN SEMI                |               |      1 |    500 |  9500 |   693   (3)| 00:00:09 |    500 |00:00:04.42 |     533 |                                                              
|   2 |   VIEW                         |               |      1 |    500 |  6500 |   239   (6)| 00:00:03 |    500 |00:00:00.02 |     258 |                                                              
|   3 |    HASH UNIQUE                 |               |      1 |    500 |  3000 |   239   (6)| 00:00:03 |    500 |00:00:00.02 |     258 |                                                              
|   4 |     BITMAP INDEX FAST FULL SCAN| IDXTEST_IDX1  |      1 |    499K|  2929K|   225   (0)| 00:00:03 |    500 |00:00:00.01 |     258 |                                                              
|   5 |   BITMAP CONVERSION TO ROWIDS  |               |      1 |    999K|  5859K|   450   (0)| 00:00:06 |    499K|00:00:01.11 |     275 |                                                              
|   6 |    BITMAP INDEX FAST FULL SCAN | IDXTEST2_IDX1 |      1 |        |       |            |          |    500 |00:00:00.01 |     275 |                                                              
------------------------------------------------------------------------------------------------------------------------------------------                                                              
                                                                                                                                                                                                        
Predicate Information (identified by operation id):                                                                                                                                                     
---------------------------------------------------                                                                                                                                                     
                                                                                                                                                                                                        
   1 - access("T11"."ID2"="T2"."ID2")                                                                                                                                                                   

Here I’ve got initially desired access to the first table
but still ineffective access to second 2

and finally I have transformed my query as

select id2 FROM (select distinct id2 FROM IDXTEST t1) t11
WHERE id2 IN (SELECT t22.id2
FROM (SELECT /*+NO_MERGE */ distinct t2.id2 FROM IDXTEST2 t2) t22);

I obtained plan I was looking for initially

-------------------------------------------------------------------------------------------------------------------------------------------                                                             
| Id  | Operation                       | Name          | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |                                                             
-------------------------------------------------------------------------------------------------------------------------------------------                                                             
|   0 | SELECT STATEMENT                |               |      1 |        |       |   719 (100)|          |    500 |00:00:00.07 |     766 |                                                             
|*  1 |  HASH JOIN SEMI                 |               |      1 |    500 | 13000 |   719   (7)| 00:00:09 |    500 |00:00:00.07 |     766 |                                                             
|   2 |   VIEW                          |               |      1 |    500 |  6500 |   239   (6)| 00:00:03 |    500 |00:00:00.02 |     258 |                                                             
|   3 |    HASH UNIQUE                  |               |      1 |    500 |  3000 |   239   (6)| 00:00:03 |    500 |00:00:00.02 |     258 |                                                             
|   4 |     BITMAP INDEX FAST FULL SCAN | IDXTEST_IDX1  |      1 |    499K|  2929K|   225   (0)| 00:00:03 |    500 |00:00:00.01 |     258 |                                                             
|   5 |   VIEW                          | VW_NSO_1      |      1 |   1000 | 13000 |   479   (7)| 00:00:06 |    941 |00:00:00.03 |     508 |                                                             
|   6 |    VIEW                         |               |      1 |   1000 |  2000 |   479   (7)| 00:00:06 |    941 |00:00:00.02 |     508 |                                                             
|   7 |     HASH UNIQUE                 |               |      1 |   1000 |  6000 |   479   (7)| 00:00:06 |    941 |00:00:00.02 |     508 |                                                             
|   8 |      BITMAP INDEX FAST FULL SCAN| IDXTEST2_IDX1 |      1 |    999K|  5859K|   450   (0)| 00:00:06 |   1000 |00:00:00.01 |     508 |                                                             
-------------------------------------------------------------------------------------------------------------------------------------------                                                             
                                                                                                                                                                                                        
Predicate Information (identified by operation id):                                                                                                                                                     
---------------------------------------------------                                                                                                                                                     
                                                                                                                                                                                                        
   1 - access("ID2"="ID2")                                                                                                                                                                              

Anomaly in “BITMAP CONVERSION TO ROWIDS” estimation

Let’s create our test harness first:

CREATE TABLE IDXTEST AS select 1e6 + rownum id, 1e6 + trunc(rownum/1000)*2 id2, rpad(‘a’,399,’a’) filler
from dual connect by level <2e6
order by dbms_random.value;

CREATE BITMAP INDEX IDXTEST_IDX1 ON IDXTEST (id2);

I’ve created a table with column id2 having a single unique value per 1000 rows.

Let’s have a look at 2 execution plans (of 2 a bit different queries):
1st one uses bitmap index to return all rows for given indexed column


SQL_ID  bv039034q966g, child number 1                                                                                                                                                                   
-------------------------------------                                                                                                                                                                   
select id2 FROM IDXTEST                                                                                                                                                                                 
                                                                                                                                                                                                        
Plan hash value: 1842016195                                                                                                                                                                             
                                                                                                                                                                                                        
---------------------------------------------------------------------------------------------                                                                                                           
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                           
---------------------------------------------------------------------------------------------                                                                                                           
|   0 | SELECT STATEMENT             |              |       |       |   900 (100)|          |                                                                                                           
|   1 |  BITMAP CONVERSION TO ROWIDS |              |  1999K|    11M|   900   (0)| 00:00:01 |                                                                                                           
|   2 |   BITMAP INDEX FAST FULL SCAN| IDXTEST_IDX1 |       |       |            |          |                                                                                                           
---------------------------------------------------------------------------------------------                                                                                                           
                                                                                                                                                                                                    

Second one returns distinct value of bitmap indexed column:

SQL_ID  0gtn94fcg82hj, child number 0                                                                                                                                                                   
-------------------------------------                                                                                                                                                                   
select distinct id2 FROM IDXTEST                                                                                                                                                                        
                                                                                                                                                                                                        
Plan hash value: 473080087                                                                                                                                                                              
                                                                                                                                                                                                        
---------------------------------------------------------------------------------------------                                                                                                           
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                           
---------------------------------------------------------------------------------------------                                                                                                           
|   0 | SELECT STATEMENT             |              |       |       |   949 (100)|          |                                                                                                           
|   1 |  HASH UNIQUE                 |              |  2000 | 12000 |   949   (6)| 00:00:01 |                                                                                                           
|   2 |   BITMAP INDEX FAST FULL SCAN| IDXTEST_IDX1 |  1999K|    11M|   900   (0)| 00:00:01 |                                                                                                           
---------------------------------------------------------------------------------------------                                                                                                           
                                                                                                                                                                                                        

You can see that Oracle estimate the 2nd query a bit higher and it doesn’t estimate “BITMAP CONVERSION TO ROWIDS” at all which is very suspicious for me as that operation on big datasets can be both CPU and IO(temp) intensive.
Let’s see what is in reality?

Let’s repeat our exercise one more time with ALTER SESSION SET statistics_level=ALL
And show execution plan as select * from TABLE(dbms_xplan.display_cursor(null,null,’typical +iostats last’));
instead of select * from TABLE(dbms_xplan.display);

So here is my results:
1st execution:

SQL_ID  bv039034q966g, child number 2                                                                                                  
-------------------------------------                                                                                                  
select id2 FROM IDXTEST                                                                                                                
                                                                                                                                       
Plan hash value: 1842016195                                                                                                            
                                                                                                                                       
---------------------------------------------------------------------------------------------------------------------------------------                                                                 
| Id  | Operation                    | Name         | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | 
---------------------------------------------------------------------------------------------------------------------------------------                                                                 
|   0 | SELECT STATEMENT             |              |      1 |        |       |   900 (100)|          |   1999K|00:00:05.93 |    2012 | 
|   1 |  BITMAP CONVERSION TO ROWIDS |              |      1 |   1999K|    11M|   900   (0)| 00:00:01 |   1999K|00:00:05.93 |    2012 | 
|   2 |   BITMAP INDEX FAST FULL SCAN| IDXTEST_IDX1 |      1 |        |       |            |          |   2000 |00:00:00.05 |    2012 | 
---------------------------------------------------------------------------------------------------------------------------------------

2nd execution:

SQL_ID  0gtn94fcg82hj, child number 1                                                                                                  
-------------------------------------                                                                                                  
select distinct id2 FROM IDXTEST                                                                                                       
                                                                                                                                       
Plan hash value: 473080087                                                                                                              
                                                                                                                                        
--------------------------------------------------------------------------------------------------------------------------------------- 
| Id  | Operation                    | Name         | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | 
--------------------------------------------------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT             |              |      1 |        |       |   949 (100)|          |   2000 |00:00:00.02 |    1012 | 
|   1 |  HASH UNIQUE                 |              |      1 |   2000 | 12000 |   949   (6)| 00:00:01 |   2000 |00:00:00.02 |    1012 | 
|   2 |   BITMAP INDEX FAST FULL SCAN| IDXTEST_IDX1 |      1 |   1999K|    11M|   900   (0)| 00:00:01 |   2000 |00:00:00.01 |    1012 | 
--------------------------------------------------------------------------------------------------------------------------------------- 

You can see that in reality 2nd execution is faster more than 100 times (0.02 sec vs 5 sec).
It seems a severe limitation of Oracle optimizer that can block potentially very beneficial (from performance perspective) execution plans. I’m going to publish some “near real life” example in my next blog.

No Parse execution and on the fly DDL change

There was an interesting question on PGDay17

“What is going on if we have a cursor cached on server side and suddenly (for the client) underlying DDL changed (added column, changed column type, etc. etc.).  I was claiming that an additional round trip would happened with re-parse request, but I was wrong!

I made an experiment and here is results:

“usual execution“: w/o DDL change for the underlying table,
That is an execution after cursor have been cached both on server and client side, actually I have not closed the cursor, but reused the same cursor:

DDL_chg1execution after DDL: I’ve added a single column to the underlying table:
It is the same cursor and the same round of execution as above, but I’ve just added a column.

DDL_chg2

You can see Oracle sends the same packet as after 1st execution and  parsing so client aware of a new column.

For just in case the very 1st query execution result is here:

DDL_chg3

PS.
Oracle resend column definitions to client after any changes that invalidate underlying cursor. For example I executed ALTER TABLE … DEALLOCATE UNUSED and observed the same effect.

 

Remote daemon autot

When you do performance troubleshooting and investigation it is quite common to measure some “deltas” (for example v$sesstat deltas) . It is also important that your measurement do not posses a “quantum” effect :), i.e.  doesn’t influence your measured process. Sometimes you can neglect such effects, sometimes (for example for parsing investigations) when you measure a tiny effect it can be quite annoying.

So I decided to implement (and  just did it)  a small daemon to measure session stats on behalf of my “main” session.

The idea is quite simple:

  1. You have a pretty small  web server which keeps connection/s with the same database as your main session.
  2. You communicate with the daemon with a very underutilized sqlplus feature – “remote scripting” , i.e. you can call *.sql (or other file) not only from your local machine but rather from any other place on the network (like http://localhost:7777/some.sql).
  3. Your daemon keeps your “main session” v$sesstat snapshots and gives you back “delta” results.

You can download the daemon from here:

https://github.com/drema201/RDAutot/tree/master/build/distributions

to run the daemon you need:

RDAutot.bat scott/tyger@localhost:1521:ORCL  [optional port]

and to use it from sqlplus:

@http://127.0.0.1:80/stats/sid=&SID/statname=(parse)/

select count(1) from dba_tables;

@http://127.0.0.1:80/stats/sid=&SID/statname=(parse)/

PS

statname takes as input REGEXP_LIKE expression and unfortunately you need to do URLEncode by yourself  😦 (Oracle http client inside sqlplus seems very primitive and in many cases just truncates http request after unknown character)