Python测试Oracle绑定变量与审计功能性能影响
本文通过Python脚本导入CSV数据至Oracle数据库，对比测试使用与不使用绑定变量时的执行耗时，以及开启数据库审计功能前后的性能差异。实验结果显示，未使用绑定变量会使运行时间增加约2.54倍，而开启审计功能后性能下降约2.32倍，且审计表sys.aud$占用大量表空间。建议生产环境尽量使用绑定变量并谨慎开启审计，可考虑通过堡垒机替代原生审计功能。
概述与实验目的
一、概述
数据库管理员在日常工作中常面临两个核心问题：SQL语句中使用绑定变量能带来多大的性能提升？开启数据库审计功能会对系统性能造成何种程度的影响？近期恰好遇到相关场景，因此决定通过实验进行验证。
- SQL使用绑定变量对性能的影响
- 开通数据库审计功能对性能的影响
实验方法较为直接：利用Python读取CSV文件，将数据导入数据库，并统计程序执行全过程所消耗的时间。
测试脚本准备
二、准备脚本
Python脚本dataimporttest.py
# 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()csv文件 test.csv(内容略)
绑定变量性能测试
三、测试sql使用绑定变量对性能的影响 a. 使用绑定变量 为排除缓存干扰，首先重启数据库以清空所有缓存。
SQL> startup force;
SQL> drop table yang.testtb purge;
SQL> create table yang.testtb as select * from yang.test where 1=0;运行脚本python dataimporttest.py
结果：test.csv, import rows: 227795, use_bind: 1, elapsed: 260.31
b. 不使用绑定变量 再次重启数据库。
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()
运行脚本python dataimporttest.py
结果：test.csv, import rows: 227795, use_bind: 0, elapsed: 662.82
对比可见，在相同条件下，不使用绑定变量的程序运行时间约为使用绑定变量的2.54倍。
数据库审计功能影响测试
四、测试数据库开启审计功能对性能的影响 首先检查数据库审计功能是否已启用。
SQL> show parameter audit
NAME TYPE VALUE
-------------- ----------- ----------
audit_trail string NONE统计sys.aud$这张表的行数。
SQL> select count(*) from sys.aud$;
COUNT(*)
----------
0因此，可以直接引用第三步中（a. 使用绑定变量）的结果，作为未开通审计功能时的程序运行时间基准。
开启数据库审计功能并重启数据库。
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操作审计将脚本最后一行CsvDataImport(use_bind=0).data_import()改回CsvDataImport(use_bind=1).data_import()
运行脚本python dataimporttest.py
结果：test.csv, import rows: 227795, use_bind: 1, elapsed: 604.23
与之前使用绑定变量但未开启审计的情况相比，开启审计功能后的运行时间约为未开启时的2.32倍。
接下来观察sys.aud$这张表的大小。
SQL> select count(*) from sys.aud$;
COUNT(*)
----------
227798由于sys.aud$表中的sqltext与sqlbind字段类型为CLOB，需通过以下SQL语句统计该表实际占用的空间。
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数据显示，将22万行CSV数据导入数据库时，审计表占用的空间高达360M，而目标表testtb本身仅占用37M。
实验表明，开启数据库审计功能会显著降低数据库性能，并大量消耗system表空间。
实验总结与建议
五、总结
- 代码中应尽可能使用绑定变量。
- 建议尽量避免开启数据库原生审计功能，可通过堡垒机实现用户操作审计（注：本文主要目的之一是寻求推荐的堡垒机厂商）。
本实验存在一定不严谨之处，相关对比数据仅供参考。
以上就是用python对oracle进行简单性能测试的示例的详细内容，更多关于python 对Oracle进行性能测试的资料请关注本站其它相关文章！
免责声明：本文内容仅供参考，不代表本站观点。转载或引用请注明出处。
