Developer 木匠: 12/01/2015

Tuesday, December 22, 2015

Golang Oracle Performance Extensions

Purpose: Demo Go Oracle database driver OCI Performance Extensions.

We will do a benchmark and show you the improvement before and after enabling Prefetching rows.
Also use SQL session trace file to prove that Update Batching works, than is insert many rows at one time.

Info:

Update Batching

You can reduce the number of round-trips to the database, thereby improving application performance, by grouping multiple UPDATE, DELETE, or INSERT statements into a single batch and having the whole batch sent to the database and processed in one trip. This is referred to as 'update batching'.

Prefetching rows

This reduces round-trips to the database by fetching multiple rows of data each time data is fetched. The extra data is stored in client-side buffers for later access by the client. The number of rows to prefetch can be set as desired.
...

Code:

// Copyright 2015, Author: Charlie Database Craftsman. All rights reserved.
// Use of this source code is governed by a BSD-style
// license that can be found in the LICENSE file.
/*
Package main demo 2 Oracle OCI Performance Extensions: array interface and Prefetching rows.
- Update Batching
It calls StmtCfg.SetPrefetchRowCount to set Prefetching size for SELECT query.
- Prefetching rows
It is binding slice type data, to pass array to database in single SQL to do Bulk DML, in one context switch.
Notes:
stmtQry.Cfg().SetPrefetchRowCount(uint32(200)) works,
ses.Cfg().StmtCfg.SetPrefetchRowCount(2000) works,
EnvCfg -> SrvCfg are both not work, because code issue, NewStmtCfg() overwrite Env/Srv on session level.
Env.StmtCfg.SetPrefetchRowCount not working.

| Modification History:
| Date        Who          What
| 11-Jan-2016: Charlie(Yi): Abstract checkErr function,
| 18-Dec-2015: Charlie(Yi): create the package,
*/
package main

import (
"fmt"
"gopkg.in/rana/ora.v3"
)

func main() {
// example usage of the ora package driver
// connect to a server and open a session
env, err := ora.OpenEnv(nil)
defer env.Close()
checkErr(err)
srvCfg := ora.NewSrvCfg()
srvCfg.Dblink = "//localhost/orcl"
srv, err := env.OpenSrv(srvCfg)
defer srv.Close()
checkErr(err)
sesCfg := ora.NewSesCfg()
sesCfg.Username = "scott"
sesCfg.Password = "tiger"
fmt.Println("Session PrefetchRowCount :", sesCfg.StmtCfg.PrefetchRowCount())
sesCfg.StmtCfg.SetPrefetchRowCount(uint32(2000))
ses, err := srv.OpenSes(sesCfg)
fmt.Println("connected")
defer ses.Close()
checkErr(err)
fmt.Println("Session PrefetchRowCount :", ses.Cfg().StmtCfg.PrefetchRowCount())
err = ses.Cfg().StmtCfg.SetPrefetchRowCount(uint32(1000))
checkErr(err)
fmt.Println("Session PrefetchRowCount :", ses.Cfg().StmtCfg.PrefetchRowCount())
stmtTbl, err := ses.Prep(
  `declare
l_sql varchar2(32767);
l_cnt pls_integer;
begin
l_sql := 'drop TABLE emp_go purge';
select count(*) into l_cnt from user_tables where table_name='EMP_GO';
if l_cnt > 0 then
    execute immediate l_sql;
end if;
end;`)
defer stmtTbl.Close()
checkErr(err)
rowsAffected, err := stmtTbl.Exe()
checkErr(err)
fmt.Println(rowsAffected, " rows Affected. drop table emp_go if exists.")
rowsAffected, err = ses.PrepAndExe("CREATE TABLE emp_go (empno number(5,0), ename VARCHAR2(50))")
checkErr(err)
fmt.Println("table emp_go created")
rowsAffected, err = ses.PrepAndExe("ALTER SESSION SET TRACEFILE_IDENTIFIER='go1'")
checkErr(err)
//SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Default Trace File';
rowsAffected, err = ses.PrepAndExe("begin dbms_monitor.session_trace_enable( WAITS=>TRUE, binds=>true); end;")
checkErr(err)
tx1, err := ses.StartTx()
stmt, err := ses.Prep("INSERT INTO emp_go (empno, ename) VALUES (:N1, :C1)")
defer stmt.Close()
checkErr(err)
fmt.Println("Demo bulk/batch insert. Bulk DML with slice (array in Go)")
l_no := make([]int64, 4)
l_name := make([]ora.String, 4)
for n, _ := range l_no {
  l_no[n] = int64(n)
  l_name[n] = ora.String{Value: fmt.Sprintf("Mr. %v", n)}
  fmt.Println(n)
}
rowsAffected, err = stmt.Exe(l_no, l_name)
checkErr(err)
fmt.Println(rowsAffected, " rows add.")
tx1.Commit()
fmt.Println("commit")
fmt.Println("Demo fetch records")
stmtQry, err := ses.Prep("SELECT /* set PrefetchRowCount = 0 */ empno, ename FROM emp_go")
defer stmtQry.Close()
checkErr(err)
err = stmtQry.Cfg().SetPrefetchRowCount(uint32(0))
checkErr(err)
fmt.Println("stmtQry.Cfg().PrefetchRowCount default:", stmtQry.Cfg().PrefetchRowCount())
rset, err := stmtQry.Qry()
checkErr(err)
for rset.Next() {
  fmt.Println(rset.Row[0], rset.Row[1])
}
checkErr(rset.Err)
stmtQry, err = ses.Prep("SELECT /* set PrefetchRowCount = 200 */ empno, ename FROM emp_go")
defer stmtQry.Close()
checkErr(err)
err = stmtQry.Cfg().SetPrefetchRowCount(uint32(200))
checkErr(err)
fmt.Println("stmtQry.Cfg().SetPrefetchRowCount(200)", stmtQry.Cfg().PrefetchRowCount())
rset, err = stmtQry.Qry()
checkErr(err)
for rset.Next() {
  fmt.Println(rset.Row[0], rset.Row[1])
}
checkErr(rset.Err)
rowsAffected, err = ses.PrepAndExe("begin dbms_monitor.session_trace_disable(); end;")
checkErr(err)
rset, err = ses.PrepAndQry("SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Default Trace File'")
for rset.Next() {
  fmt.Println(rset.Row[0])
}
}

func checkErr(err error) {
if err != nil {
  panic(err)
}
}

Output:

Session PrefetchRowCount : 0
connected
Session PrefetchRowCount : 2000
Session PrefetchRowCount : 1000
0 rows Affected. drop table emp_go if exists.
table emp_go created
Demo bulk/batch insert. Bulk DML with slice (array in Go)
0
1
2
3
4 rows add.
commit
Demo fetch records
stmtQry.Cfg().PrefetchRowCount default: 0
0 Mr. 0
1 Mr. 1
2 Mr. 2
3 Mr. 3
stmtQry.Cfg().SetPrefetchRowCount(200) 200
0 Mr. 0
1 Mr. 1
2 Mr. 2
3 Mr. 3
/home/oracle/app/oracle/diag/rdbms/cdb1/cdb1/trace/cdb1_ora_17092_go1.trc
.
SQL session trace:

tkprof /home/oracle/app/oracle/diag/rdbms/cdb1/cdb1/trace/cdb1_ora_17092_go1.trc rpt.txt
.
TKProf report:
.
Before, PrefetchRowCount = 0.

SELECT /* set PrefetchRowCount = 0 */ empno, ename
FROM emp_go

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        3      0.00       0.00          0          9          0           4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        5      0.00       0.00          0         10          0           4
.
After SetPrefetchRowCount = 200.
.
SELECT /* set PrefetchRowCount = 200 */ empno, ename
FROM emp_go

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          7          0           4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        3      0.00       0.00          0          8          0           4
.
Bulk INSERT,
.
INSERT INTO emp_go (empno, ename)
VALUES (:N1, :C1)

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0             4         43           4
Fetch        0      0.00       0.00          0          0          0           0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        2      0.00       0.00

Reference:
.
Oracle driver basic, http://mujiang.blogspot.com/2015/12/golang-connect-to-oracle-database.html
driver readme, https://github.com/rana/ora/blob/master/README.md
Performance, http://docs.oracle.com/database/121/JJDBC/oraperf.htm

Friday, December 18, 2015

Golang connect to Oracle database

Purpose: setup Go Oracle driver, and a complete code to demo some basic DDL, DML, and bulk batching processing.

Minimum requirements are
- Go 1.3 with CGO enabled,
- a GCC C compiler,
- and Oracle 11g (11.2.0.4.0) or Oracle Instant Client (11.2.0.4.0).

This demo is tested on Go 1.5.2, Oracle Virtual Box, imported OTN_Developer_Day_VM.ova appliance/image file.

** environment settings **

------- Linux ----------
export GOROOT=/usr/local/go
#export GOPATH=/home/oracle/go
export GOPATH=/media/sf_GTD/Project/Go
export PATH=$PATH:/usr/local/go/bin:$GOPATH/bin
mkdir -p $GOPATH/src/github.com/user/hello
cd $GOPATH/src/github.com/user/hello
echo $PATH
echo $GOPATH
# Oracle client driver, environment parameters
# Set the CGO_CFLAGS and CGO_LDFLAGS environment variables to locate the OCI headers and library,
export CGO_CFLAGS=-I$ORACLE_HOME/rdbms/public
export CGO_LDFLAGS="-L$ORACLE_HOME/lib -lclntsh"

-----------
-- install Oracle driver --
go get github.com/rana/ora

-- manual install driver from source zip files --
download source zip files, and extract into $GOPATH/src/gopkg.in/rana/ora.v3/ , or rename ora-master to ora.v3

download address: https://github.com/rana/ora/tree/v3

The files structure will be looks like,
src\gopkg.in\rana\ora.v3\
                         ora.go
                         env.go
                         ...
                         \examples\...

To import this package, add the following line to your code:
import "gopkg.in/rana/ora.v3"

Code:

// Copyright 2015, Author: Charlie Database Craftsman. All rights reserved.
// Use of this source code is governed by a BSD-style
// license that can be found in the LICENSE file.
/*
Package main demo OCI array interface.
It connects to a database, create table, insert data, and query the data.
E:\GTD\Project\Go\src\github.com\user\hello\oracle_demo.go
*/
package main

import (
"fmt"
"gopkg.in/rana/ora.v3"
)

func main() {
// example usage of the ora package driver
// connect to a server and open a session
env, err := ora.OpenEnv(nil)
defer env.Close()
if err != nil {
  panic(err)
}
srvCfg := ora.NewSrvCfg()
srvCfg.Dblink = "//localhost/orcl"
srv, err := env.OpenSrv(srvCfg)
defer srv.Close()
if err != nil {
  panic(err)
}
sesCfg := ora.NewSesCfg()
sesCfg.Username = "scott"
sesCfg.Password = "tiger"
ses, err := srv.OpenSes(sesCfg)
fmt.Println("connected")
defer ses.Close()
if err != nil {
  panic(err)
}
//StmtCfg.PrefetchRowCount = 1000
stmtTbl, err := ses.Prep(
  `declare
l_sql varchar2(32767);
l_cnt pls_integer;
begin
l_sql := 'drop TABLE emp_go purge';
select count(*) into l_cnt from user_tables where table_name='EMP_GO';
if l_cnt > 0 then
    execute immediate l_sql;
end if;
end;`)
defer stmtTbl.Close()
if err != nil {
  panic(err)
}
rowsAffected, err := stmtTbl.Exe()
if err != nil {
  panic(err)
}
fmt.Println(rowsAffected, " rows Affected. drop table emp_go if exists.")
rowsAffected, err = ses.PrepAndExe("CREATE TABLE emp_go (empno number(5,0), ename VARCHAR2(50))")
if err != nil {
  panic(err)
}
fmt.Println("table emp_go created")
tx1, err := ses.StartTx()
rowsAffected, err = ses.PrepAndExe("delete emp_go")
tx1.Commit()
stmt, err := ses.Prep("INSERT INTO emp_go (empno, ename) VALUES (:N1, :C1)")
defer stmt.Close()
rowsAffected, err = stmt.Exe(uint64(1001), "Charlie")
rowsAffected, err = stmt.Exe(uint64(1002), "Vicky")
if err != nil {
  panic(err)
}
fmt.Println(rowsAffected, "add")
tx1.Commit()
tx1.Rollback()
fmt.Println("commit")
fmt.Println("Demo fetch records")
stmtQry, err := ses.Prep("SELECT empno, ename FROM emp_go")
defer stmtQry.Close()
if err != nil {
  panic(err)
}
rset, err := stmtQry.Qry()
if err != nil {
  panic(err)
}
for rset.Next() {
  fmt.Println(rset.Row[0], rset.Row[1])
}
if rset.Err != nil {
  panic(rset.Err)
}
}

Output:

$> go run oracle_demo.go

connected
0 rows Affected. drop table emp_go if exists.
table emp_go created
1 add
commit
Demo fetch records
1001 Charlie
1002 Vicky
.

Reference:
https://github.com/rana/ora/blob/master/README.md
http://gopkg.in/rana/ora.v3

Tuesday, December 15, 2015

Monday, December 14, 2015

Golang Exercise: Equivalent Binary Trees

code:

package main

import "golang.org/x/tour/tree"
import "fmt"

// Walk walks the tree t sending all values
// from the tree to the channel ch.
func Walk(t *tree.Tree, ch chan int) {
if t == nil {
  close(ch)
  return
}
if t.Left != nil {
  Walk(t.Left, ch)
}
ch <- t.Value
//fmt.Println("send ", t.Value)
if t.Right != nil {
  Walk(t.Right, ch)
}
}

// Same determines whether the trees
// t1 and t2 contain the same values.
func Same(t1, t2 *tree.Tree) (b_same bool) {
c1 := make(chan int)
c2 := make(chan int)
b_same = true
go Walk(t1, c1)
go Walk(t2, c2)
for i := 0; i < 10; i++ {
  //fmt.Println("get ", <-c1, <-c2)
  if <-c1 != <-c2 {
   b_same = false
   break
  }
}
return
}

func main() {
//t1 := tree.New(1)
//fmt.Println(t1)
fmt.Println(Same(tree.New(1), tree.New(1)))
fmt.Println(Same(tree.New(1), tree.New(2)))
}

Output:

true
false

Program exited.

Function Same, version 2:

func Same(t1, t2 *tree.Tree) (b_same bool) {
c1 := make(chan int)
c2 := make(chan int)
b_same = true
go func() {
  Walk(t1, c1)
  close(c1)
}()
go func() {
  Walk(t2, c2)
  close(c2)
}()
for {
  v1, ok1 := <-c1
  v2, ok2 := <-c2
  if !ok1 || !ok2 {
   return ok1 == ok2
  }
  if v1 != v2 {
   b_same = false
   break
  }
}
return
}

Ref:
http://tour.golang.org/concurrency/8
https://golang.org/doc/play/tree.go
https://github.com/golang/tour/blob/master/tree/tree.go

Tuesday, December 08, 2015

Golang Exercise: Images

code:

package main

import "golang.org/x/tour/pic"
import "image"
import "image/color"

type Image struct{}

func (p Image) Bounds() image.Rectangle {
return image.Rect(0, 0, 256, 256)
}

func (p Image) ColorModel() color.Model {
return color.RGBAModel
}

func (m Image) At(x, y int) color.Color {
v := uint8(x * y)
return color.RGBA{v, v, 255, 255}
}

func main() {
m := &Image{}
pic.ShowImage(m)
}

Ref:

http://tour.golang.org/methods/16

Golang Exercise: HTTP Handlers

Code:

package main

import (
"fmt"
"log"
"net/http"
)

type String string

func (h String) ServeHTTP(w http.ResponseWriter, r *http.Request) {
fmt.Fprint(w, h)
}

type Struct struct {
    Greeting string
    Punct    string
    Who      string
}

func (h *Struct) ServeHTTP(w http.ResponseWriter, r*http.Request){
fmt.Fprintf(w, "%v %v %v", h.Greeting, h.Punct, h.Who)
}

func main() {
// your http.Handle calls here
http.Handle("/string", String("I'm a good man."))
http.Handle("/struct", &Struct{"Hello", ":", "Gophers! 你好吗？"})
log.Fatal(http.ListenAndServe("localhost:4000", nil))
}

Call example:

http://localhost:4000/struct

http://localhost:4000/string

Reference:

http://tour.golang.org/methods/14

Monday, December 07, 2015

Golang Exercise: rot13Reader

package main

import (
"fmt"
"io"
"os"
"strings"
)

type rot13Reader struct {
r io.Reader
}

func (p rot13Reader) Read(b []byte) (n int, err error) {
n, err = p.r.Read(b)
for i := 0; i < n; i++ {
  b[i] = b[i] + 13
  if b[i] > 'z' || (b[i] > 'Z' && b[i] < 'a') {
   b[i] = b[i] - 26
  }
  fmt.Printf("b[%v] = %q\n", i, b[i])
}
n = len(b)
return n, err
}

func main() {
s := strings.NewReader("Lbh penpxrq gur pbqr!")
r := rot13Reader{s}
io.Copy(os.Stdout, &r)
}

Output:

b[0] = 'Y'
b[1] = 'o'
b[2] = 'u'
b[3] = '-'
b[4] = 'c'
b[5] = 'r'
b[6] = 'a'
b[7] = 'c'
b[8] = 'k'
b[9] = 'e'
b[10] = 'd'
b[11] = '-'
b[12] = 't'
b[13] = 'h'
b[14] = 'e'
b[15] = '-'
b[16] = 'c'
b[17] = 'o'
b[18] = 'd'
b[19] = 'e'
b[20] = '.'
You-cracked-the-code.You-cracked-the-code.
Program exited.

Reference:

http://tour.golang.org/methods/12

Friday, December 04, 2015

Golang Exercise: Errors

package main

import (
"fmt"
"math"
)

type ErrNegativeSqrt float64

func (e ErrNegativeSqrt) Error() string {
if e < 0 {
return fmt.Sprintf("cannot Sqrt negative number: %v ", float64(e))
}
return ""
}

func Sqrt(x float64) (float64, error) {
if x < 0 {
return x, ErrNegativeSqrt(x)
}
return math.Sqrt(x), ErrNegativeSqrt(x)
}

func main() {
fmt.Println(Sqrt(2))
fmt.Println(Sqrt(-2))
}
Output:

1.4142135623730951 
-2 cannot Sqrt negative number: -2 

Program exited.

Reference:

http://tour.golang.org/methods/9

Thursday, December 03, 2015

Golang Exercise: Stringers

package main

import "fmt"

type IPAddr [4]byte

// TODO: Add a "String() string" method to IPAddr.
func (ip IPAddr) String() string {
var ls_ip string
for i, value := range ip {
  //ls_ip = ls_ip + string(i) + ":" + string(int(value)) + "."
  switch {
  case i == 0:
   ls_ip = fmt.Sprintf("%v", value)
  default:
   ls_ip = fmt.Sprintf("%v.%v", ls_ip, value)
  }
}
return ls_ip
}

func main() {
addrs := map[string]IPAddr{
  "loopback": {127, 0, 0, 1},
  "googleDNS": {8, 8, 8, 8},
}
for n, a := range addrs {
  fmt.Printf("%v: %v\n", n, a)
}
}

Output:

loopback: 127.0.0.1
googleDNS: 8.8.8.8

Program exited.

Reference:

http://tour.golang.org/methods/7

Wednesday, December 02, 2015

Golang Exercise: Slices

Exercise: Slices

Implement Pic. It should return a slice of length dy, each element of which is a slice of dx 8-bit unsigned integers. When you run the program, it will display your picture, interpreting the integers as grayscale (well, bluescale) values.
The choice of image is up to you. Interesting functions include (x+y)/2, x*y, and x^y.
(You need to use a loop to allocate each []uint8 inside the [][]uint8.)
(Use uint8(intValue) to convert between types.)

.
Solution:

package main

import "golang.org/x/tour/pic"
//import "fmt"

func Pic(dx, dy int) [][]uint8 {
var la_pic [][]uint8
var la_x []uint8
la_x = make([]uint8, dx)
//la_pic = make([][]uint8, 0)
for i := 0; i < dy; i++ {
  la_pic = append(la_pic, la_x)
  for j := 0; j < dx; j++ {
   la_pic[i][j] = uint8((i ^ j))
   //fmt.Println(i, j)
  }
}
return la_pic
}
func main() {
pic.Show(Pic)
}

Reference:

http://tour.golang.org/moretypes/15

Tuesday, December 01, 2015

Go语言斐波那契数列 Exercise: Fibonacci closure

My code:

package main

import "fmt"

// fibonacci is a function that returns
// a function that returns an int.
func fibonacci() func() int {
var ln_prev, ln_current, ln_next int
ln_current = 1
return func() int {
  ln_next = ln_current + ln_prev
  ln_prev = ln_current
  ln_current = ln_next
  fmt.Println("log:", ln_prev, ln_current, ln_next)
  return ln_next
}
}
func main() {
f := fibonacci()
for i := 0; i < 10; i++ {
  fmt.Println(i, f())
}
}

Output:

log: 1 1 1
0 1
log: 1 2 2
1 2
log: 2 3 3
2 3
log: 3 5 5
3 5
log: 5 8 8
4 8
log: 8 13 13
5 13
log: 13 21 21
6 21
log: 21 34 34
7 34
log: 34 55 55
8 55
log: 55 89 89
9 89

Program exited.

Option 2, Learned from Python way :

package main

import "fmt"

// fibonacci is a function that returns
// a function that returns an int.
func fibonacci() func() int {
var la_f []int
la_f = make([]int, 2)
la_f = []int{0, 1}
//la_f := []int{0, 1}
var li_idx int
li_idx = 1
return func() int {
  la_f = append(la_f, la_f[li_idx]+la_f[li_idx-1])
  li_idx = li_idx + 1
  fmt.Printf("log: i %d, value %d, pre element %d \n", li_idx, la_f[li_idx], la_f[li_idx-1])
  return la_f[li_idx]
}
}
func main() {
f := fibonacci()
for i := 0; i < 10; i++ {
  fmt.Printf("idx: %d, value: %d \n", i, f())
}
}

Output:

log: i 2, value 1, pre element 1
idx: 0, value: 1
log: i 3, value 2, pre element 1
idx: 1, value: 2
log: i 4, value 3, pre element 2
idx: 2, value: 3
log: i 5, value 5, pre element 3
idx: 3, value: 5
log: i 6, value 8, pre element 5
idx: 4, value: 8
log: i 7, value 13, pre element 8
idx: 5, value: 13
log: i 8, value 21, pre element 13
idx: 6, value: 21
log: i 9, value 34, pre element 21
idx: 7, value: 34
log: i 10, value 55, pre element 34
idx: 8, value: 55
log: i 11, value 89, pre element 55
idx: 9, value: 89

Program exited.

Reference:

http://tour.golang.org/moretypes/23

https://zh.wikipedia.org/wiki/%E6%96%90%E6%B3%A2%E9%82%A3%E5%A5%91%E6%95%B0%E5%88%97