一、概述

dba在工作中避不開的兩個(gè)問(wèn)題，sql使用綁定變量到底會(huì)有多少的性能提升？數(shù)據(jù)庫(kù)的審計(jì)功能如果打開對(duì)數(shù)據(jù)庫(kù)的性能會(huì)產(chǎn)生多大的影響？最近恰好都碰到了，索性做個(gè)實(shí)驗(yàn)。

sql使用綁定變量對(duì)性能的影響 開通數(shù)據(jù)庫(kù)審計(jì)功能對(duì)性能的影響

實(shí)驗(yàn)采用的辦法很簡(jiǎn)單，就是通過(guò)python讀取csv文件，然后將其導(dǎo)入到數(shù)據(jù)庫(kù)中，最后統(tǒng)計(jì)程序執(zhí)行完成所需要的時(shí)間

二、準(zhǔn)備腳本

python腳本dataimporttest.py

# author: yangbao# function: 通過(guò)導(dǎo)入csv，測(cè)試數(shù)據(jù)庫(kù)性能import cx_Oracleimport time# 數(shù)據(jù)庫(kù)連接串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) # 使用綁定變量的方式插入數(shù)據(jù) else: # print(self.insert_sql.format(*data)) curs.execute(self.insert_sql.format(*data)) # 使用非綁定變量的方式插入數(shù)據(jù) 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()

csv文件test.csv(內(nèi)容略)

三、測(cè)試sql使用綁定變量對(duì)性能的影響a. 使用綁定變量對(duì)庫(kù)進(jìn)行重啟，目的是清空數(shù)據(jù)庫(kù)內(nèi)的所有緩存，避免對(duì)實(shí)驗(yàn)結(jié)果產(chǎn)生干擾

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

運(yùn)行腳本python dataimporttest.py

結(jié)果：test.csv, import rows: 227795, use_bind: 1, elapsed: 260.31

b. 不使用綁定變量對(duì)庫(kù)進(jìn)行重啟

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

將腳本的最后一行CsvDataImport(use_bind=1).data_import()改為CsvDataImport(use_bind=0).data_import()

運(yùn)行腳本python dataimporttest.py

結(jié)果：test.csv, import rows: 227795, use_bind: 0, elapsed: 662.82

可以看到同樣的條件下，程序運(yùn)行的時(shí)間，不使用綁定變量是使用綁定變量的2.54倍

四、測(cè)試數(shù)據(jù)庫(kù)開啟審計(jì)功能對(duì)性能的影響查看數(shù)據(jù)庫(kù)審計(jì)功能是否開啟

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

統(tǒng)計(jì)sys.aud$這張表的行數(shù)

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

所以可以直接拿第三步中的（a. 使用綁定變量）的結(jié)果作為沒(méi)開通審計(jì)功能程序運(yùn)行的時(shí)間

對(duì)庫(kù)開通審計(jì)功能，并進(jìn)行重啟

SQL> alter system set audit_trail=db,extended scope=spfile; # 如果設(shè)置成db，那么在sys.aud$里面sqltext將為空，也就是說(shuō)看不到用戶執(zhí)行的sql語(yǔ)句，審計(jì)毫無(wú)意義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; # 開通對(duì)用戶yang的insert操作審計(jì)

將腳本的最后一行CsvDataImport(use_bind=0).data_import()改為CsvDataImport(use_bind=1).data_import()

運(yùn)行腳本python dataimporttest.py

結(jié)果：test.csv, import rows: 227795, use_bind: 1, elapsed: 604.23

與前面使用綁定變量但沒(méi)有開通數(shù)據(jù)庫(kù)審計(jì)功能，程序運(yùn)行的時(shí)間，開通數(shù)據(jù)庫(kù)審計(jì)功能是不開通數(shù)據(jù)庫(kù)審計(jì)功能的2.32倍

再來(lái)看看sys.aud$這張表的大小

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

因sys.aud$這張表中的sqltext與sqlbind都是clob字段，因此需要通過(guò)下面的sql去統(tǒng)計(jì)該表所占用的空間

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

查看testtb這張表占用的空間

SQL> select sum(bytes) from dba_extents where segment_name in (’TESTTB’);SUM(BYTES)---------- 37748736

可以看到對(duì)一個(gè)22萬(wàn)行的csv數(shù)據(jù)導(dǎo)入到數(shù)據(jù)庫(kù)，審計(jì)的表占用的空間就達(dá)到了驚人的360M，而testtb這張表本身也才37M而已

通過(guò)上面的實(shí)驗(yàn)可以得出，對(duì)于數(shù)據(jù)庫(kù)的審計(jì)功能，開通后會(huì)嚴(yán)重拖慢數(shù)據(jù)庫(kù)的性能以及消耗system表空間！

五、總結(jié)

代碼中盡量使用綁定變量 最好不要開通數(shù)據(jù)庫(kù)的審計(jì)，可以通過(guò)堡壘機(jī)去實(shí)現(xiàn)對(duì)用戶操作審計(jì)（ps：還請(qǐng)大家推薦個(gè)堡壘機(jī)廠商，這個(gè)才是本文最主要的目的_）

實(shí)驗(yàn)存在不嚴(yán)謹(jǐn)?shù)牡胤剑嚓P(guān)對(duì)比數(shù)據(jù)也僅作為參考

以上就是用python對(duì)oracle進(jìn)行簡(jiǎn)單性能測(cè)試的示例的詳細(xì)內(nèi)容，更多關(guān)于python 對(duì)Oracle進(jìn)行性能測(cè)試的資料請(qǐng)關(guān)注好吧啦網(wǎng)其它相關(guān)文章！