sample - prefetch_lob_size

gistfile1.txt

package main

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

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

const (
	tblQuery = `
CREATE TABLE PREFETH_ROWS_CLOB_TEST (
	C1 INTEGER, 
	C2 varchar2(300),
	C3 CLOB
)
`
	dropTblQuery = `
DROP TABLE PREFETH_ROWS_CLOB_TEST
`
	loadTestData = `
BEGIN
for rec in 1..1000 
loop
 insert into PREFETH_ROWS_CLOB_TEST values(rec,rpad(' ',255,'X'),rpad(' ',3000,'X'));
 end loop;
 end;
 `
	queryClob = `
SELECT /*clobQuery*/
	C1
	,C2
	,C3
FROM PREFETH_ROWS_CLOB_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, prefetchLobSize 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 prefetchLobSize = os.Getenv("DB_PREFETCHLOBSIZE"); prefetchLobSize == "" {
		log.Printf("Set OS environment variable DB_PREFETCHLOBSIZE\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&prefetch_lob_size=%s", dbUsername, dbPassword, dbHostname, dbPort, dbServiceName, prefetchRows, prefetchMemory, prefetchLobSize)
	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 => 'SETUP'); 
	`)
	//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 => 'LOAD_DATA'); 
    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)
	}
	db.Exec(`
	begin
		DBMS_APPLICATION_INFO.SET_MODULE ( 
			module_name => 'PREFETCHROWS_TEST', 
			action_name => 'CLOB_QUERY'); 
    end;
	`)
	// Execute test with one query
	var rows *sql.Rows
	ctx, cancel = context.WithTimeout(context.Background(), contextTimeout)
	defer cancel()
	stmt, err = db.PrepareContext(ctx, queryClob)
	rows, err = stmt.QueryContext(ctx)
	if err != nil {
		log.Fatal(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("Query with clob took %s", elapsed)

	var sqlID string
	adminConn.QueryRow(queryGetPrevSQLID).Scan(&sqlID)
	var fetches, rowsProcessed int64
	adminConn.QueryRow(querySQLStats, sqlID).Scan(&fetches, &rowsProcessed)
	log.Printf("Query with clob 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)
	}
}