从Oracle获取Blob数据时的限制 [英] The limitation when getting blob data from oracle
问题描述
我正在尝试使用JDBC从oracle下载BLOB数据. 为了知道获取Blob数据的平均响应时间， 我正在使用JMeter调用Get Blob Data Java程序， 一个或多个线程.但是我得到了奇怪的响应时间，我无法 明白.
I am trying the download the BLOB data from oracle with JDBC . In order to know the average response time of getting the blob data, I am using JMeter to call the Getting blob data Java program with one or multiply threads.But I got the strange response time I could not understand.
我在下面尝试了一些测试.(blob数据大小为1M)
I tried some tests below.(the blob data size is 1M)
1.使用Jmeter在一个线程中获取blob数据
1.Getting blob data with Jmeter in one thread
2.使用Jmeter在两个线程中获取blob数据
2.Getting blob data with Jmeter in two thread
3.使用JMeter在两个线程中在同一表中获取不同的blob数据
3.Getting different blob data in the same table with JMeter in two thread
4.在两个线程中使用Jmeter在不同的表中获取不同的Blob数据.
4.Getting different blob data in different tables with Jmeter in two thread.
.从测试1和2中，我发现测试2的响应时间几乎是测试1的两倍 我不明白这一点(我的服务器有四个核心，没有人使用它). 我怀疑当我获得相同的Blob数据时是否存在锁，因此我尝试了测试3. 但是响应时间几乎与test2相同，然后我尝试了test 4 确保访问同一张表时是否存在锁，但响应时间 测试4的值也与test3相同.
.From the test 1 and 2,I found the response time of test 2 is almost twice of test 1 I can not understand this(My server has four cores and nobody is using that). I doubt that there is a lock when I get the same blob data so I tried the test 3. But the response time is almost the same as test2.And then I tried the test 4 to make sure whether there is a lock when I access the same table,but the response time of test 4 is also the same as the test3.
我认为oracle可以同时处理很多用户的请求，所以我不能 了解我从测试中得到的结果. 同时获取blob数据时，oracle是否有一些限制?
I think oracle can make deal with a lot the users' request at the same time,so I cannot understand the result I got from my tests. Does oracle have some limitation when getting the blob data at the same time?
这是获取Blob数据的代码
here is the code for getting the blob data
public SampleResult runTest(JavaSamplerContext arg0) {
////
try {
Class.forName(JDBC_CLASS_NAME);
conn=DriverManager.getConnection(JDBC_THIN+dbserverIp+":"+port+":"+sid,dbuser,userPwd);
statement=conn.createStatement();
rs=statement.executeQuery(sqlText);
responseStr=RESPONSE_DATA_OK_HEAD+KAIGYOU_CODE;
if(rs.next()){
String fileId=rs.getString(FILE_ID_COLUMN_NAME);
oracle.sql.BLOB blob=((OracleResultSet)rs).getBLOB(BLOB_COLUMN_NAME);
in=blob.getBinaryStream();
int size=blob.getBufferSize();
byte[] buffer=new byte[size];
int len=-1;
bos=new ByteArrayOutputStream();
while((len=in.read(buffer))!=-1){
readCount=readCount+1;
bos.write(buffer,0,len);
}
byte[] blobValues=bos.toByteArray();
responseStr=responseStr+fileId+","+blobValues.length+","+readCount+KAIGYOU_CODE;
}else{
responseStr=RESPONSE_DATA_ZERO;
}
} catch (Exception e) {
e.printStackTrace();
getNGSampleResult(e,sr);
return sr;
}finally{
try {
if(rs!=null){
rs.close();
rs=null;
}
if(statement!=null){
statement.close();
statement=null;
}
if(conn!=null){
conn.close();
conn=null;
}
if(in!=null){
in.close();
in=null;
}
if(bos!=null){
bos.close();
bos=null;
}
} catch (Exception e) {
e.printStackTrace();
getNGSampleResult(e,sr);
return sr;
}
}
sr.sampleEnd();
sr.setSuccessful(true);
sr.setResponseCodeOK();
sr.setResponseMessageOK();
sr.setResponseData(responseStr,"UTF-8");
sr.setDataType(SampleResult.TEXT);
return sr;
}
这是测试计划
<?xml version="1.0" encoding="UTF-8"?>
<jmeterTestPlan version="1.2" properties="2.1">
<hashTree>
<TestPlan guiclass="TestPlanGui" testclass="TestPlan" testname="Test Plan" enabled="true">
<stringProp name="TestPlan.comments"></stringProp>
<boolProp name="TestPlan.functional_mode">false</boolProp>
<boolProp name="TestPlan.serialize_threadgroups">false</boolProp>
<elementProp name="TestPlan.user_defined_variables" elementType="Arguments" guiclass="ArgumentsPanel" testclass="Arguments" testname="User Parameter" enabled="true">
<collectionProp name="Arguments.arguments">
<elementProp name="env_test_date" elementType="Argument">
<stringProp name="Argument.name">env_test_date</stringProp>
<stringProp name="Argument.value">20130202</stringProp>
<stringProp name="Argument.metadata">=</stringProp>
</elementProp>
<elementProp name="env_test_case" elementType="Argument">
<stringProp name="Argument.name">env_test_case</stringProp>
<stringProp name="Argument.value">Run at the same time</stringProp>
<stringProp name="Argument.metadata">=</stringProp>
</elementProp>
<elementProp name="env_test_filesize" elementType="Argument">
<stringProp name="Argument.name">env_test_filesize</stringProp>
<stringProp name="Argument.value">1M</stringProp>
<stringProp name="Argument.metadata">=</stringProp>
</elementProp>
<elementProp name="env_test_thread" elementType="Argument">
<stringProp name="Argument.name">env_test_thread</stringProp>
<stringProp name="Argument.value">2users</stringProp>
<stringProp name="Argument.metadata">=</stringProp>
</elementProp>
</collectionProp>
</elementProp>
<stringProp name="TestPlan.user_define_classpath"></stringProp>
</TestPlan>
<hashTree>
<ThreadGroup guiclass="ThreadGroupGui" testclass="ThreadGroup" testname="ULDLtest" enabled="true">
<stringProp name="ThreadGroup.on_sample_error">continue</stringProp>
<elementProp name="ThreadGroup.main_controller" elementType="LoopController" guiclass="LoopControlPanel" testclass="LoopController" testname="loop controller" enabled="true">
<boolProp name="LoopController.continue_forever">false</boolProp>
<intProp name="LoopController.loops">-1</intProp>
</elementProp>
<stringProp name="ThreadGroup.num_threads">2</stringProp>
<stringProp name="ThreadGroup.ramp_time">1</stringProp>
<longProp name="ThreadGroup.start_time">1358319600000</longProp>
<longProp name="ThreadGroup.end_time">1358328499000</longProp>
<boolProp name="ThreadGroup.scheduler">true</boolProp>
<stringProp name="ThreadGroup.duration">300</stringProp>
<stringProp name="ThreadGroup.delay"></stringProp>
</ThreadGroup>
<hashTree>
<JavaSampler guiclass="JavaTestSamplerGui" testclass="JavaSampler" testname="Java DL BLOB Request" enabled="true">
<elementProp name="arguments" elementType="Arguments" guiclass="ArgumentsPanel" testclass="Arguments" enabled="true">
<collectionProp name="Arguments.arguments">
<elementProp name="SQL_TEXT" elementType="Argument">
<stringProp name="Argument.name">SQL_TEXT</stringProp>
<stringProp name="Argument.value">select * from t_download_file_data t where t.file_id='testdata1M.csv'</stringProp>
<stringProp name="Argument.metadata">=</stringProp>
</elementProp>
<elementProp name="DB_USER" elementType="Argument">
<stringProp name="Argument.name">DB_USER</stringProp>
<stringProp name="Argument.value">xxxx</stringProp>
<stringProp name="Argument.metadata">=</stringProp>
</elementProp>
<elementProp name="USER_PWD" elementType="Argument">
<stringProp name="Argument.name">USER_PWD</stringProp>
<stringProp name="Argument.value">xxxx</stringProp>
<stringProp name="Argument.metadata">=</stringProp>
</elementProp>
<elementProp name="SID" elementType="Argument">
<stringProp name="Argument.name">SID</stringProp>
<stringProp name="Argument.value">xxxxx</stringProp>
<stringProp name="Argument.metadata">=</stringProp>
</elementProp>
<elementProp name="DBSERVER_IP" elementType="Argument">
<stringProp name="Argument.name">DBSERVER_IP</stringProp>
<stringProp name="Argument.value">xxx.xx.xx.xx</stringProp>
<stringProp name="Argument.metadata">=</stringProp>
</elementProp>
<elementProp name="PORT" elementType="Argument">
<stringProp name="Argument.name">PORT</stringProp>
<stringProp name="Argument.value">1521</stringProp>
<stringProp name="Argument.metadata">=</stringProp>
</elementProp>
</collectionProp>
</elementProp>
<stringProp name="classname">sample.TestSamplerClient</stringProp>
</JavaSampler>
<hashTree/>
<ResultCollector guiclass="TableVisualizer" testclass="ResultCollector" testname="Reust for table" enabled="false">
<boolProp name="ResultCollector.error_logging">false</boolProp>
<objProp>
<name>saveConfig</name>
<value class="SampleSaveConfiguration">
<time>true</time>
<latency>true</latency>
<timestamp>true</timestamp>
<success>true</success>
<label>true</label>
<code>true</code>
<message>true</message>
<threadName>true</threadName>
<dataType>true</dataType>
<encoding>false</encoding>
<assertions>true</assertions>
<subresults>true</subresults>
<responseData>false</responseData>
<samplerData>false</samplerData>
<xml>true</xml>
<fieldNames>false</fieldNames>
<responseHeaders>false</responseHeaders>
<requestHeaders>false</requestHeaders>
<responseDataOnError>false</responseDataOnError>
<saveAssertionResultsFailureMessage>false</saveAssertionResultsFailureMessage>
<assertionsResultsToSave>0</assertionsResultsToSave>
<bytes>true</bytes>
</value>
</objProp>
<stringProp name="filename"></stringProp>
</ResultCollector>
<hashTree/>
<ResultCollector guiclass="ViewResultsFullVisualizer" testclass="ResultCollector" testname="Result for graph" enabled="false">
<boolProp name="ResultCollector.error_logging">false</boolProp>
<objProp>
<name>saveConfig</name>
<value class="SampleSaveConfiguration">
<time>true</time>
<latency>true</latency>
<timestamp>true</timestamp>
<success>true</success>
<label>true</label>
<code>true</code>
<message>true</message>
<threadName>true</threadName>
<dataType>true</dataType>
<encoding>false</encoding>
<assertions>true</assertions>
<subresults>true</subresults>
<responseData>false</responseData>
<samplerData>false</samplerData>
<xml>true</xml>
<fieldNames>false</fieldNames>
<responseHeaders>false</responseHeaders>
<requestHeaders>false</requestHeaders>
<responseDataOnError>false</responseDataOnError>
<saveAssertionResultsFailureMessage>false</saveAssertionResultsFailureMessage>
<assertionsResultsToSave>0</assertionsResultsToSave>
<bytes>true</bytes>
</value>
</objProp>
<stringProp name="filename"></stringProp>
</ResultCollector>
<hashTree/>
<ResultCollector guiclass="StatVisualizer" testclass="ResultCollector" testname="Report" enabled="true">
<boolProp name="ResultCollector.error_logging">false</boolProp>
<objProp>
<name>saveConfig</name>
<value class="SampleSaveConfiguration">
<time>true</time>
<latency>true</latency>
<timestamp>true</timestamp>
<success>true</success>
<label>true</label>
<code>true</code>
<message>true</message>
<threadName>true</threadName>
<dataType>true</dataType>
<encoding>false</encoding>
<assertions>true</assertions>
<subresults>true</subresults>
<responseData>false</responseData>
<samplerData>false</samplerData>
<xml>true</xml>
<fieldNames>false</fieldNames>
<responseHeaders>false</responseHeaders>
<requestHeaders>false</requestHeaders>
<responseDataOnError>false</responseDataOnError>
<saveAssertionResultsFailureMessage>false</saveAssertionResultsFailureMessage>
<assertionsResultsToSave>0</assertionsResultsToSave>
<bytes>true</bytes>
</value>
</objProp>
<stringProp name="filename">log\ULDLPerformance_${env_test_case}_${env_test_thread}_${env_test_filesize}_${env_test_date}.jtl</stringProp>
</ResultCollector>
<hashTree/>
</hashTree>
</hashTree>
</hashTree>
</jmeterTestPlan>
推荐答案
在编码时，您正在测量: -连接到数据库 -斑点读取 -连接关闭
As you coded it You are measuring : - connection to db - blob read - connection close
您也许可以使用JDBC Configuration元素:
You could maybe use JDBC Configuration element:
http://jmeter.apache.org/usermanual/component_reference.html#JDBC_Connection_Configuration
因此在您采样之前就建立了连接.
So that connection are established before you sample.
然后通过查看jdbc请求如何工作来访问数据库连接来修改代码:
Then modify your code by looking at how jdbc request works to get access to DB connection:
conn = DataSourceElement.getConnection("Variable Name used in JDBC_Connection_Configuration");
这篇关于从Oracle获取Blob数据时的限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
