CODEWITHSUNDEEP
sqldatabaseoracle-databasenetwork-programmingoracle11g
Mike Christensen
Mike Christensen

Reputation: 91734

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

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)

Upvotes: 7

Views: 7863

Answers (4)

mohsen.b
mohsen.b

Reputation: 436

Please check table fragmentation. Usually Table Fragmentaion cause more I/O and query goes slow. you can check it using oracle segment advisor and to solve it there are two ways first using oracle shrink table command: is to slow and also lock table, second using oracle move table command, this one is too fast specially if use by oracle parallel option. the only point about move table is it will unused indexes.

Upvotes: 0

Bart K
Bart K

Reputation: 732

Selecting a few thousand rows from a table shouldn't take almost a minute. My guess is that you have a performance problem elsewhere in your system. Could be other activity in the DB, or issues with the server/network/storage. Is performance with other queries in your DB similarly slow?

Upvotes: 1

Justin Cave
Justin Cave

Reputation: 231881

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.

Upvotes: 6

EvilTeach
EvilTeach

Reputation: 28882

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.

Upvotes: 1

Related Questions