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
No comments:
Post a Comment