Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Getting results from oracle query suck!
The server machine has lots of RAM and plenty of processor. The NT
performance monitor shows CPU usage around 2% and lots of memory available
(re no swapping). There is no other activity on this server box, no logins,
shared drives, nothing. The data store on this server has 6 drives arranged
as raid 5 through hardware.
The java code is simple: create a connection to database, formulate and execute query, read results. The amount of data per row fetched is on average 90 bytes. Again, if you look at my previous posting you can see that the query execution time is great - reading the rows is terrible. Similar results are seen in a C++ program that uses DbTools and oracle OCI driver.
I was just kidding about Access - obviously there is no comparison. I am, however, using PostgreSql on Linux and have no problem executing queries that move more data than my NT queries in microseconds. I really think that I have a gross parameter error that has something to do with reading the result set out of the database.
import java.sql.*;
public class DbTestA
{
// Start of driver
//
public static void main(String[] args)
{
try
{
DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver ()); String[] Criteria = {"WILL", "ALA", "FRA","BAI"}; PartyName theTest = new PartyName(); for (int ii = 0; ii < Criteria.length; ii++) { theTest.setCriteria(Criteria[ii]); theTest.start(); } } catch (Exception e)
{
System.out.println("Failed to initial test driver: " + e); }
}
}
//////////////////////////////////////////////////////////////////////////// //////// // One inner class per test scenario ////////////////////////////////////////////////////////////////////////////////////
public PartyName() throws Exception
{
DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver ());
con = DriverManager.getConnection ("jdbc:oracle:oci8:@beteldev","orion",
"orion");
System.out.println("Made the connection"); System.out.println(con.getMetaData()); }
public void setCriteria(String theCriteria)
{
m_Criteria = theCriteria;
}
public void start()
{
// // Execute Query //
ResultSet dbResults = null;
Statement stmt = con.createStatement(); String SqlStmt = "select * from name where ptysurnm like '" + m_Criteria + "%'"; System.out.println("Executing :" + SqlStmt); // time how long it takes to execute the query long queryTime = System.currentTimeMillis(); dbResults = stmt.executeQuery(SqlStmt); queryTime = (System.currentTimeMillis() - queryTime); // Time how long it takes to drain the reader long drainTime = System.currentTimeMillis(); long recCnt = 0; String sColumn; try
{
while(dbResults.next()) { recCnt++; try sColumn = dbResults.getString("NAMEID"); sColumn = dbResults.getString("PTYSURNM"); sColumn = dbResults.getString("PTYFIRSTNM"); sColumn = dbResults.getString("PTYSUFFIXNM"); sColumn = dbResults.getString("PTYSALUTNDE"); sColumn = dbResults.getString("PTYTITLEDE"); sColumn = dbResults.getString("PTYMIDDLENM"); sColumn = dbResults.getString("BusinessNm"); } catch (Exception e) { System.out.println("getString Error: " + e); } } } catch (Exception e)
{
System.out.println("while(dbResults.next()) Error: " + e); } drainTime = System.currentTimeMillis() - drainTime; System.out.println("Criteria: " + m_Criteria); System.out.println("Query Time: " + queryTime); System.out.println("Drain Time: " + drainTime); System.out.println("Record Count: " + recCnt); System.out.println("=================================================");
}
catch (Exception e)
{
System.out.println(" executeQuery: Error: " + e); }
}
}
mpir_at_compuserve.com wrote in message <7gnjvm$rgf$1_at_nnrp1.dejanews.com>... >PMJI, but there are some environmental factors that can cause a major delay:
>1-swapping memory. I did not see what is doing the retrieval, sga size vs >available ram, etc. but that kind of delay can happen if the system has >started swapping buffers. > >2-Swapping can be aggrevated or caused if the i/o is to the same disk, thatis
>in SGA vs PGA buffers) > >3-You say you have no network but are NT. You may be running locally, butNT
>is still subject to assorted network and other priority interrupts if your >machine is on a network. > >4-How big are the rows? 1000 5K rows may take 5 seconds, but 1000 5 byterows
>shouldn't. NT's screen displays are not the fastest for text modes. > >In article <7gnemr$rho7_at_amber.alltel.com>, > "MScheuter" <mike.scheuter_at_alltel.com> wrote: >> Thanks for the response. >> >> The query is essentially: >> select * from CName where LastName like 'Nec%' >> Again the query execution time is great, in the range of 180 ms. Thetime
>> since this same operation is MS Access can once again be measured in the >> hundreds of milliseconds. >> >> I tend to think that there is a gross parameter error within the databaseon
>> us what we already know, that the query (build of the result set) works >> great. Explain plan does not tell us what is going on when reading the >> result set. Is there additional monitoring options that can be enabled >> within Oracle? >> >> thanks again >> -mike >> >> Sybrand Bakker wrote in message >> <925837438.21998.0.muttley.d4ee154e_at_news.demon.nl>... >> >Most likely this is a sql statement tuning issue. >> >We need to have your statement and details about the tables. >> >Then: are you using array fetching. This is possible in Pro*C andwithout
>> >doubt it is possible in OCI. Fetching a record is the costly operation >> there >> >is. Fetch your records in batches of 100 or even 1000. Also makes sure >> >Oracle traces this session. This will provide useful detail from theserver
>> >side. Make sure sql_trace = true and timed_statistics = true in >> >init<sid>.ora, bounce the database and see what happens >> > >> >Hth, >> > >> >Sybrand Bakker, Oracle DBA >> > >> > >> >MScheuter wrote in message <7gn1s6$rho6_at_amber.alltel.com>... >> >>I have a table that contains customer name information (about 10fields).
>> >>java using again the supplied oracle oci drivers (see oracle web site). >> In >> >>a test program I am timing the execution of the select statment andthen
>> >>timing how long it takes to 'read' (or drain) the rows of data from the >> >>database. The timing results are as follows: >> >> >> >>Rows in Result Set Query Time (ms) Read results time (ms) >> >>2824 187 11875 >> >>211 156 969 >> >>1330 187 4984 >> >>1740 78 6062>> >>
>> >>involved) and the results were similiar. >> >> >> >>The server is a 2 way 200 mhz intell box with 512 meg ram 80 gig hdspace.
>> >>Can someone please explain some areas that I can look at in order to >> >improve >> >>the performance of reading the result set? >> >> >> >>Thanks >> >>-mike >> >> >> >> >> > >> > >> >> > >-- >Joseph R.P. Maloney, CCP,CSP,CDP >MPiR, Inc. >502-451-7404 >some witty phrase goes here, I think. > >-----------== Posted via Deja News, The Discussion Network ==---------- >http://www.dejanews.com/ Search, Read, Discuss, or Start Your OwnReceived on Wed May 05 1999 - 07:58:02 CDT