Oracle - Why is SELECT * FROM Foo; so slow?

Question

I'm working on some Oracle performance issues with our web app. One thing I've noticed that's seems to obfuscate any sort of tests is that simple queries that return a lot of results are still very slow. One example is:

select * from TPM_PROJECTWORKGROUPS;

When I run it, I get:

 5825 record(s) selected [Fetch MetaData: 0ms] [Fetch Data: 59s] 

 [Executed: 9/22/2011 1:52:38 PM] [Execution: 203ms] 

If I understand this correctly, it means the actual query took 203ms to run but it took 59 seconds for that data to be returned to the client, is that was "Fetch" means in this case?

I don't have access to connect to the database machine directly and run the query locally, but is it safe to assume that the culprit is the actual network bandwidth itself? This makes sense since I'm in Seattle and the server is in New York, but still a minute for 5800 rows seems like pretty slow through-put.

Is there any quick advice for a) confirming that network bandwidth is indeed the problem and b) any "gotchas" or things to check for why serializing the data over the wire is so slow? Thanks!

A Few Updates Based On Comments:

SELECT COUNT(*) FROM (select * from TPM_PROJECTWORKGROUPS) t;

Results:

 1 record(s) selected [Fetch MetaData: 0ms] [Fetch Data: 0ms] 

 [Executed: 9/22/2011 2:16:08 PM] [Execution: 219ms] 

And if I try selecting only one column:

SELECT PROJECTID FROM TPM_PROJECTWORKGROUPS;

Results:

5825 record(s) selected [Fetch MetaData: 0ms] [Fetch Data: 1m 0s]

[Executed: 9/22/2011 2:17:20 PM] [Execution: 203ms]

Table schema:

PROJECTID (NUMBER) WORKGROUPID (NUMBER)

Answer 1

What API are you using to interact with the database (SQL*Plus, JDBC, ODBC, etc.)? Any API will have some function that specifies how many rows (or how much data) to fetch in a single network round-trip. For example, in SQL*Plus, it's set arraysize N. In JDBC, it's setFetchSize. Other APIs will have similar functions. If you're on a WAN, you generally want to minimize how chatty your application is by increasing the number of rows fetched with each network round trip.

Along the same lines, you'll probably benefit from moving less data over the network and pushing more logic to the server. Do you actually display a grid with 5800 rows of data to the user? Or do you do fetch that data and then perform some processing in the application (i.e. order the data and display the first 100 rows)? If you can push that processing to the database and reduce the amount of data that has to be transmitted over the database, you'll be much better off.

Oracle has options to configure the SDU and TDU as well as a few other networking parameters in SQL*Net. I wouldn't start looking at those options, though, until you've optimized the fetch size and ensured that you're pulling back the least amount of data possible.

Answer 2

As you are dealing with a web app, getting something back quick is important. Investigate the FIRST_ROWS hint. That may be of value to your situation.