Oracle - Why SELECT * FROM Foo; so slow?

Question

I am working on some Oracle performance issues with our web application. One thing I noticed that seems to confuse any tests is that simple queries that return a lot of results are still very slow. For example:

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 that the actual request took 203 ms, but it took 59 seconds for this data to be returned to the client, is there a “fetch” in this case?

I do not have access to a direct connection to the database machine and the query is executed locally, but can it be assumed that the actual network bandwidth is the culprit? This makes sense since I'm in Seattle and the server is in New York, but still the minute for 5800 lines seems pretty slow end-to-end.

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

Several comments-based updates:

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 to select only one column:

CHOOSE A DESIGNER FROM TPM_PROJECTWORKGROUPS;

Results:

5825 records selected [Fetch MetaData: 0ms] [Fetch Data: 1m 0s]

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

Table layout:

PROJECTID (NUMBER) WORKGROUPID (NUMBER)

Answer 1

What API do you use to interact with the database (SQL * Plus, JDBC, ODBC, etc.)? Any API will have some function that determines how many rows (or how much data) will be selected on one network in both directions. For example, in SQL * Plus, this is set arraysize N In JDBC, this is setFetchSize . Other APIs will have similar features. If you are on a global network, you usually want to minimize how much more chatting your application is by increasing the number of lines received with each trip over the network.

On the same lines, you are likely to benefit from moving less data over the network and increasing server logic. Are you actually showing the user a grid with 5800 data series? Or do you extract this data and then do some processing in the application (i.e., you order data and show the first 100 rows)? If you can move this processing to the database and reduce the amount of data that needs to be transferred across the database, you will be much better.

Oracle has options for configuring SDU and TDU, as well as several other network parameters in SQL * Net. However, I would not look at these parameters until you optimized the sample size and guaranteed that you would drop the least amount of data.

Answer 2

Since you are dealing with a web application, it is important to get something fast quickly. Explore the tip FIRST_ROWS. This may be useful for your situation.

Answer 3

Selecting several thousand rows from a table should not take almost a minute. I assume that you have performance problems elsewhere on your system. There may be other activity in the database or problems with the server / network / storage. Is performance with other queries in your database just as slow?

Answer 4

Please check the fragmentation of the table. Typically, the Fragmentaion table causes more I / O, and the query is slow. you can check it using the oracle segment advisor, and there are two ways to solve it first by using the atacle shrink table command: slow down and also lock the table, secondly, using the move move move command, this one is too fast, especially if using the parallel oracle option , the only point in the movement table is unused indexes.