daily pastebin goal
88%
SHARE
TWEET

Untitled

a guest Jan 20th, 2019 84 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*
  2. All these OS environment variables to specify how to connect.
  3.  * DB_USERNAME
  4.  * DB_PASSWORD
  5.  * DB_PORT
  6.  * DB_HOSTNAME
  7.  * DB_SERVICENAME
  8.  * DB_PREFETCHMEMORY
  9.  * DB_PREFETCHROWS
  10. DB Privileges need
  11.  * create session
  12.  * create table
  13.  * select on v$session
  14.  * select on v§sql
  15.  * And some quota on default tablespace for the ues
  16. */
  17. package main
  18.  
  19. import (
  20.     "context"
  21.     "database/sql"
  22.     "fmt"
  23.     "log"
  24.     "os"
  25.     "time"
  26.  
  27.     _ "github.com/mattn/go-oci8"
  28. )
  29.  
  30. const (
  31.     tblQuery = `
  32. CREATE TABLE PREFETH_ROWS_CLOB_TEST (
  33.     C1 INTEGER,
  34.     C2 varchar2(300),
  35.     C3 CLOB
  36. )
  37. `
  38.     dropTblQuery = `
  39. DROP TABLE PREFETH_ROWS_CLOB_TEST
  40. `
  41.     loadTestData = `
  42. BEGIN
  43. for rec in 1..1000
  44. loop
  45.  insert into PREFETH_ROWS_CLOB_TEST values(rec,rpad(' ',255,'X'),rpad(' ',3000,'X'));
  46.  end loop;
  47.  end;
  48.  `
  49.     queryClob = `
  50. SELECT /*clobQuery*/
  51.     C1
  52.     ,C2
  53.     ,C3
  54. FROM PREFETH_ROWS_CLOB_TEST
  55. `
  56.     querySQLStats = `
  57. select
  58.     fetches,
  59.     rows_processed
  60. from v$sql where sql_id = :1
  61. `
  62.     queryGetPrevSQLID = `
  63. SELECT
  64.     prev_sql_id
  65. FROM v$session s
  66. WHERE username = 'SYSTEM'
  67. AND s.module = 'PREFETCHROWS_TEST'
  68. `
  69.     contextTimeout = 10 * time.Second
  70. )
  71.  
  72. func main() {
  73.     var dbUsername, dbPassword, dbHostname, dbPort, dbServiceName, prefetchMemory, prefetchRows string
  74.     if dbUsername = os.Getenv("DB_USERNAME"); dbUsername == "" {
  75.         log.Printf("Set OS environment variable DB_USERNAME\n")
  76.     }
  77.     if dbPassword = os.Getenv("DB_PASSWORD"); dbPassword == "" {
  78.         log.Printf("Set OS environment variable DB_PASSWORD\n")
  79.     }
  80.     if dbHostname = os.Getenv("DB_HOSTNAME"); dbHostname == "" {
  81.         log.Printf("Set OS environment variable DB_HOSTNAME\n")
  82.     }
  83.     if dbPort = os.Getenv("DB_PORT"); dbPort == "" {
  84.         log.Printf("Set OS environment variable DB_USERNAME\n")
  85.     }
  86.     if dbServiceName = os.Getenv("DB_SERVICENAME"); dbServiceName == "" {
  87.         log.Printf("Set OS environment variable DB_SERVICENAME\n")
  88.     }
  89.     if prefetchMemory = os.Getenv("DB_PREFETCHMEMORY"); prefetchMemory == "" {
  90.         log.Printf("Set OS environment variable DB_PREFETCHMEMORY\n")
  91.     }
  92.     if prefetchRows = os.Getenv("DB_PREFETCHROWS"); prefetchRows == "" {
  93.         log.Printf("Set OS environment variable DB_PREFETCHROWS\n")
  94.     }
  95.  
  96.     if dbUsername == "" || dbPassword == "" || dbHostname == "" || dbPort == "" || dbServiceName == "" || prefetchMemory == "" || prefetchRows == "" {
  97.         log.Fatalf("All these OS environment variables DB_USERNAME, DB_PASSWORD, DB_PORT, DB_HOSTNAME, DB_SERVICENAME, DB_PREFETCHMEMORY DB_PREFETCHROWS must be set")
  98.     }
  99.  
  100.     conn := fmt.Sprintf("%s/%s@%s:%s/%s?prefetch_rows=%s&prefetch_memory=%s", dbUsername, dbPassword, dbHostname, dbPort, dbServiceName, prefetchRows, prefetchMemory)
  101.     log.Printf("conn: %s\n", conn)
  102.     db, err := sql.Open("oci8", conn)
  103.     if err != nil {
  104.         log.Fatalf("db connect failed with error: %v\n", err)
  105.     }
  106.     err = db.Ping()
  107.     if err != nil {
  108.         log.Fatalf("db ping failed with error: %v\n", err)
  109.     }
  110.     adminConn, err := sql.Open("oci8", conn)
  111.     if err != nil {
  112.         log.Fatalf("db connect failed with error: %v\n", err)
  113.     }
  114.     err = db.Ping()
  115.     if err != nil {
  116.         log.Fatalf("db ping failed with error: %v\n", err)
  117.     }
  118.  
  119.     db.Exec(`
  120.     begin
  121.     DBMS_APPLICATION_INFO.SET_MODULE (
  122.         module_name => 'PREFETCHROWS_TEST',
  123.         action_name => 'SETUP');
  124.     `)
  125.     //Setup test table
  126.     ctx, cancel := context.WithTimeout(context.Background(), contextTimeout)
  127.     stmt, err := db.PrepareContext(ctx, tblQuery)
  128.     cancel()
  129.     ctx, cancel = context.WithTimeout(context.Background(), contextTimeout)
  130.     _, err = stmt.ExecContext(ctx)
  131.     cancel()
  132.     if err != nil {
  133.         log.Fatalf("err:%v\n", err)
  134.     }
  135.     db.Exec(`
  136.     begin
  137.         DBMS_APPLICATION_INFO.SET_MODULE (
  138.             module_name => 'PREFETCHROWS_TEST',
  139.             action_name => 'LOAD_DATA');
  140.     end;
  141.     `)
  142.     //Load data into test table
  143.     ctx, cancel = context.WithTimeout(context.Background(), contextTimeout)
  144.     stmt, err = db.PrepareContext(ctx, loadTestData)
  145.     ctx, cancel = context.WithTimeout(context.Background(), contextTimeout)
  146.     _, err = stmt.ExecContext(ctx)
  147.     cancel()
  148.     if err != nil {
  149.         log.Fatalf("err:%v\n", err)
  150.     }
  151.     db.Exec(`
  152.     begin
  153.         DBMS_APPLICATION_INFO.SET_MODULE (
  154.             module_name => 'PREFETCHROWS_TEST',
  155.             action_name => 'CLOB_QUERY');
  156.     end;
  157.     `)
  158.     // Execute test with one query
  159.     var rows *sql.Rows
  160.     ctx, cancel = context.WithTimeout(context.Background(), contextTimeout)
  161.     defer cancel()
  162.     stmt, err = db.PrepareContext(ctx, queryClob)
  163.     rows, err = stmt.QueryContext(ctx)
  164.     if err != nil {
  165.         log.Fatal(err)
  166.     }
  167.     log.Printf("Start looping result\n")
  168.     defer rows.Close()
  169.     start := time.Now()
  170.     for rows.Next() {
  171.         var data1 int64
  172.  
  173.         rows.Scan(
  174.             &data1,
  175.         )
  176.     }
  177.     elapsed := time.Since(start)
  178.     log.Printf("Query with clob took %s", elapsed)
  179.  
  180.     var sqlID string
  181.     adminConn.QueryRow(queryGetPrevSQLID).Scan(&sqlID)
  182.     var fetches, rowsProcessed int64
  183.     adminConn.QueryRow(querySQLStats, sqlID).Scan(&fetches, &rowsProcessed)
  184.     log.Printf("Query with clob SQLID: %s, fetches:%d , rowsProcessed: %d \n", sqlID, fetches, rowsProcessed)
  185.  
  186.     //Clean up
  187.     ctx, cancel = context.WithTimeout(context.Background(), contextTimeout)
  188.     stmt, err = db.PrepareContext(ctx, dropTblQuery)
  189.     ctx, cancel = context.WithTimeout(context.Background(), contextTimeout)
  190.     _, err = stmt.ExecContext(ctx)
  191.     cancel()
  192.     if err != nil {
  193.         log.Fatalf("err:%v\n", err)
  194.     }
  195. }
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top