Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*
- 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_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 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 => '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)
- }
- }
Add Comment
Please, Sign In to add comment