go-oci8 make fetch for each row

prefetch_rows.go

/*
All these OS environment variables to specify how to connect.
 * DB_USERNAME
 * DB_PASSWORD
 * DB_PORT 
 * DB_HOSTNAME
 * DB_SERVICENAME 
 * DB_PREFETCHMEMORY 
 * DB_PREFETCHROWS
DB Privileges need
 * create session
 * create table
 * select on v$session
 * select on v§sql
 * And some quota on default tablespace for the ues
*/
package main

import (
	"context"
	"database/sql"
	"fmt"
	"log"
	"os"
	"time"

	_ "github.com/mattn/go-oci8"
)

const (
	tblQuery = `
CREATE TABLE PREFETH_ROWS_TEST (
	C1 INTEGER, 
	c2 varchar2(300), 
	c3 varchar2(300), 
	c4 varchar2(300), 
	c5 varchar2(300), 
	c6 varchar2(300), 
	c7 varchar2(300), 
	c8 varchar2(300), 
	c9 varchar2(300),
	c10 varchar2(300),
	c11 varchar2(300),
	c12 varchar2(300),
	c13 varchar2(300),
	c14 varchar2(300),
	c15 varchar2(300)
)
`
	dropTblQuery = `
DROP TABLE PREFETH_ROWS_TEST
`
	loadTestData = `
BEGIN
for rec in 1..1000 
loop
 insert into PREFETH_ROWS_TEST values(rec,rpad(' ',255,'X'),rpad(' ',255,'X'),rpad(' ',255,'X'),
  rpad(' ',255,'X'),rpad(' ',255,'X'),rpad(' ',255,'X'),rpad(' ',255,'X'),rpad(' ',255,'X')
  ,rpad(' ',255,'X'),rpad(' ',255,'X'),rpad(' ',255,'X'),rpad(' ',255,'X'),rpad(' ',255,'X'),rpad(' ',255,'X'));
 end loop;
 end;
 `
	querySlow = `
SELECT /*kalle*/
	 C1
	 ,C2,C3,C4, C5,C6,C7,C8,C9,c10,c11,c12,c13,c14,c15
FROM PREFETH_ROWS_TEST`
	queryFast = `
SELECT /*fast*/
	 C1
FROM PREFETH_ROWS_TEST
`
	querySQLStats = `
select 
    fetches,
    rows_processed
from v$sql where sql_id = :1
`
	queryGetPrevSQLID = `
SELECT 
	prev_sql_id 
FROM v$session s 
WHERE username = 'SYSTEM' 
AND s.module = 'PREFETCHROWS_TEST'
`
	contextTimeout = 10 * time.Second
)

func main() {
	var dbUsername, dbPassword, dbHostname, dbPort, dbServiceName, prefetchMemory, prefetchRows string
	if dbUsername = os.Getenv("DB_USERNAME"); dbUsername == "" {
		log.Printf("Set OS environment variable DB_USERNAME\n")
	}
	if dbPassword = os.Getenv("DB_PASSWORD"); dbPassword == "" {
		log.Printf("Set OS environment variable DB_PASSWORD\n")
	}
	if dbHostname = os.Getenv("DB_HOSTNAME"); dbHostname == "" {
		log.Printf("Set OS environment variable DB_HOSTNAME\n")
	}
	if dbPort = os.Getenv("DB_PORT"); dbPort == "" {
		log.Printf("Set OS environment variable DB_USERNAME\n")
	}
	if dbServiceName = os.Getenv("DB_SERVICENAME"); dbServiceName == "" {
		log.Printf("Set OS environment variable DB_SERVICENAME\n")
	}
	if prefetchMemory = os.Getenv("DB_PREFETCHMEMORY"); prefetchMemory == "" {
		log.Printf("Set OS environment variable DB_PREFETCHMEMORY\n")
	}
	if prefetchRows = os.Getenv("DB_PREFETCHROWS"); prefetchRows == "" {
		log.Printf("Set OS environment variable DB_PREFETCHROWS\n")
	}

	if dbUsername == "" || dbPassword == "" || dbHostname == "" || dbPort == "" || dbServiceName == "" || prefetchMemory == "" || prefetchRows == "" {
		log.Fatalf("All these OS environment variables DB_USERNAME, DB_PASSWORD, DB_PORT, DB_HOSTNAME, DB_SERVICENAME, DB_PREFETCHMEMORY DB_PREFETCHROWS must be set")
	}

	conn := fmt.Sprintf("%s/%s@%s:%s/%s?prefetch_rows=%s&prefetch_memory=%s", dbUsername, dbPassword, dbHostname, dbPort, dbServiceName, prefetchRows, prefetchMemory)
	log.Printf("conn: %s\n", conn)
	db, err := sql.Open("oci8", conn)
	if err != nil {
		log.Fatalf("db connect failed with error: %v\n", err)
	}
	err = db.Ping()
	if err != nil {
		log.Fatalf("db ping failed with error: %v\n", err)
	}
	adminConn, err := sql.Open("oci8", conn)
	if err != nil {
		log.Fatalf("db connect failed with error: %v\n", err)
	}
	err = db.Ping()
	if err != nil {
		log.Fatalf("db ping failed with error: %v\n", err)
	}

	db.Exec(`
	begin
	DBMS_APPLICATION_INFO.SET_MODULE ( 
		module_name => 'PREFETCHROWS_TEST', 
		action_name => 'FAST_QUERY'); 
	`)
	//Setup test table
	ctx, cancel := context.WithTimeout(context.Background(), contextTimeout)
	stmt, err := db.PrepareContext(ctx, tblQuery)
	cancel()
	ctx, cancel = context.WithTimeout(context.Background(), contextTimeout)
	_, err = stmt.ExecContext(ctx)
	cancel()
	if err != nil {
		log.Fatalf("err:%v\n", err)
	}
	db.Exec(`
	begin
		DBMS_APPLICATION_INFO.SET_MODULE ( 
			module_name => 'PREFETCHROWS_TEST', 
			action_name => 'SLOW_QUERY'); 
    end;
	`)
	//Load data into test table
	ctx, cancel = context.WithTimeout(context.Background(), contextTimeout)
	stmt, err = db.PrepareContext(ctx, loadTestData)
	ctx, cancel = context.WithTimeout(context.Background(), contextTimeout)
	_, err = stmt.ExecContext(ctx)
	cancel()
	if err != nil {
		log.Fatalf("err:%v\n", err)
	}

	// Execute test with one query
	var rows *sql.Rows
	ctx, cancel = context.WithTimeout(context.Background(), contextTimeout)
	defer cancel()
	stmt, err = db.PrepareContext(ctx, queryFast)
	rows, err = stmt.QueryContext(ctx)
	if err != nil {
		log.Fatal(err)
		log.Printf("[ERROR]: getRegtime failed with %v\n", err)
	}
	log.Printf("Start looping result\n")
	defer rows.Close()
	start := time.Now()
	for rows.Next() {
		var data1 int64

		rows.Scan(
			&data1,
		)
	}
	elapsed := time.Since(start)
	log.Printf("Fast Query took %s", elapsed)

	var sqlID string
	adminConn.QueryRow(queryGetPrevSQLID).Scan(&sqlID)
	var fetches, rowsProcessed int64
	adminConn.QueryRow(querySQLStats, sqlID).Scan(&fetches, &rowsProcessed)
	log.Printf("Fast Query SQLID: %s, fetches:%d , rowsProcessed: %d \n", sqlID, fetches, rowsProcessed)

	//Run test with multiple Columns with make bad performance
	ctx, cancel = context.WithTimeout(context.Background(), contextTimeout)
	defer cancel()
	stmt, err = db.PrepareContext(ctx, querySlow)
	rows, err = stmt.QueryContext(ctx)
	if err != nil {
		log.Fatal(err)
		log.Printf("[ERROR]: getRegtime failed with %v\n", err)
	}

	defer rows.Close()
	start = time.Now()
	for rows.Next() {
		var data1 int64
		var data2, data3, data4, data5, data6, data7, data8, data9, data10, data11, data12, data13, data14, data15 string

		rows.Scan(
			&data1,
			&data2,
			&data3,
			&data4,
			&data5,
			&data6,
			&data7,
			&data8,
			&data9,
			&data10,
			&data11,
			&data12,
			&data13,
			&data14,
			&data15,
		)
	}
	elapsed = time.Since(start)
	log.Printf("Slow Query took %s", elapsed)
	adminConn.QueryRow(queryGetPrevSQLID).Scan(&sqlID)
	adminConn.QueryRow(querySQLStats, sqlID).Scan(&fetches, &rowsProcessed)
	log.Printf("Slow Query SQLID: %s, fetches:%d , rowsProcessed: %d \n", sqlID, fetches, rowsProcessed)

	//Clean up
	ctx, cancel = context.WithTimeout(context.Background(), contextTimeout)
	stmt, err = db.PrepareContext(ctx, dropTblQuery)
	ctx, cancel = context.WithTimeout(context.Background(), contextTimeout)
	_, err = stmt.ExecContext(ctx)
	cancel()
	if err != nil {
		log.Fatalf("err:%v\n", err)
	}

}