用python对oracle进行简单性能测试

# author: yangbao
# function: 通过导入csv，测试数据库性能

import cx_Oracle
import time


# 数据库连接串
DATABASE_URL = 'user/password@ip:1521/servicename'


class CsvDataImport:

 def __init__(self, use_bind):
  self.csv_name = 'test.csv'
  self.use_bind = use_bind
  if use_bind == 1:
   self.insert_sql = "insert into testtb values(:0, " \
        "to_date(:1,'yyyy-mm-dd hh24:mi:ss'), " \
        "to_date(:2,'yyyy-mm-dd hh24:mi:ss'), " \
        ":3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, " \
        ":15, :16, :17, :18, :19, :20, :21)" # 使用绑定变量的sql
  else:
   self.insert_sql = "insert into testtb values({0}, " \
        "to_date('{1}','yyyy-mm-dd hh24:mi:ss'), " \
        "to_date('{2}','yyyy-mm-dd hh24:mi:ss'), " \
        "{3}, {4}, '{5}', {6}, '{7}', {8}, {9}, {10}, {11}, {12}, {13}, {14}, " \
        "{15}, {16}, {17}, {18}, {19}, {20}, {21})" # 不使用绑定变量的sql

 def data_import(self):

   begin_time = time.perf_counter()

   try:
    conn = cx_Oracle.connect(DATABASE_URL)
    curs = conn.cursor()

    with open(self.csv_name) as f:
     csv_contents = f.readlines()

    import_rows = 0

    message = '{} start to import'.format(self.csv_name)
    print(message)

    for line, csv_content in enumerate(csv_contents[1:]):

     data = csv_content.split(',')
     if self.use_bind == 1:
      data = map(lambda x: None if x == '' else x, data)
     else:
      data = map(lambda x: 'null' if x == '' else x, data)
     data = list(data)
     data[-1] = data[-1].replace('\n', '')

     if self.use_bind == 1:
      curs.execute(self.insert_sql, data) # 使用绑定变量的方式插入数据
     else:
      # print(self.insert_sql.format(*data))
      curs.execute(self.insert_sql.format(*data)) # 使用非绑定变量的方式插入数据
     import_rows += 1
     if import_rows % 10000 == 0:
      curs.execute('commit')
      message = '{} has imported {} lines'.format(self.csv_name, import_rows)
      print(message)

    conn.commit()
    curs.close()
    conn.close()

    end_time = time.perf_counter()

    elapsed = round(end_time - begin_time, 2)
    message = '{}, import rows: {}, use_bind: {}, elapsed: {}'.format(
     self.csv_name, import_rows, self.use_bind, elapsed)
    print(message)

   except Exception as e:
    message = '{} import failed, reason: {}'.format(self.csv_name, str(e))
    print(message)


if __name__ == '__main__':
 CsvDataImport(use_bind=1).data_import()

SQL> startup force;
SQL> drop table yang.testtb purge;
SQL> create table yang.testtb as select * from yang.test where 1=0;

SQL> startup force;
SQL> drop table yang.testtb purge;
SQL> create table yang.testtb as select * from yang.test where 1=0;

SQL> show parameter audit 
NAME   TYPE  VALUE
-------------- ----------- ----------
audit_trail string  NONE

SQL> select count(*) from sys.aud$;

 COUNT(*)
----------
   0

SQL> alter system set audit_trail=db,extended scope=spfile; # 如果设置成db，那么在sys.aud$里面sqltext将为空，也就是说看不到用户执行的sql语句，审计毫无意义
SQL> startup force;
SQL> drop table yang.testtb purge;
SQL> create table yang.testtb as select * from yang.test where 1=0;
SQL> audit insert table by yang; # 开通对用户yang的insert操作审计

SQL> select count(*) from sys.aud$;

 COUNT(*)
----------
 227798

SQL> select sum(bytes) from dba_extents where segment_name in (
select distinct name from (select table_name, segment_name from dba_lobs where table_name='AUD$') 
unpivot(name for i in(table_name, segment_name)));

SUM(BYTES)
----------
 369229824

SQL> select sum(bytes) from dba_extents where segment_name in ('TESTTB');

SUM(BYTES)
----------
 37748736