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

Golang range and close channel

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 {
  fmt.Println("Null")
  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)
 }
}



func main() {
 ta := tree.New(1)
 ca := make(chan int)
 go func() {
  Walk(ta, ca)
  close(ca)
 }()


 for v := range ca {
  fmt.Println("get ", v)
 }
}


Comments:

See which line the channel is closed.

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