| simple select * from table is taking lot of time [message #535238] |
Tue, 13 December 2011 01:56  |
 |
gogol_1987
Messages: 12
Registered: March 2011
Location: kolkata
|
Junior Member |
|
|
hi gems...
my table has 7267563 rows..
and i am doing a simple select * from table;
but it is taking a lot of time nearly 25minutes but not completed...
when i did select count(1) from table...
then it gave the result instantly...
also select * from table where rownum < 10 is also fine...
even when i am selecting all the records using rownum i.e. select * from table where rownum < 7267563 is also giving result instantly...
but the entire table is not getting result i.e.
select * from table...
also there is no lock in the table(though i know that select is nothing to do with lock)..
not only a particular schema...in all schemas in that database i am experiencing the same problem...
the developers are complainning that database is very slow...for time being i requested them to use rownum condition...
thanks in advance..please help..
|
|
|
|
| Re: simple select * from table is taking lot of time [message #535240 is a reply to message #535238] |
Tue, 13 December 2011 02:01  |
 |
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
This is just the time to acquire and display the complete result.
If you want to display all the records then you have to admit the time.
Quote:select * from table where rownum < 7267563 is also giving result instantly...
No, it gives you the FIRST rows quickly not ALL the rows.
Quote:select count(1) from table
Use count(*) NOT count(1).
Regards
Michel
[Updated on: Tue, 13 December 2011 02:02] Report message to a moderator |
|
|
|
|
|
| Re: simple select * from table is taking lot of time [message #535244 is a reply to message #535241] |
Tue, 13 December 2011 02:55 |
|
gogol_1987
Messages: 12
Registered: March 2011
Location: kolkata
|
Junior Member |
|
|
thanks a lot gems for your replies...
i admit that fetching all the rows will take a bit of time but earlier there was no problem with the tables.
earlier select * from table were giving results within expected range of time.
now the problem is that...select * from table where rownum< 7265753 is also hanging...
i doubt it is some problem regarding buffer cache or SGA or analyzing table issues.
should i bounce my database???
but bouncing the database may solve the problem but in future it may well again appear...
[Updated on: Tue, 13 December 2011 02:56] Report message to a moderator |
|
|
|
|
|
|
|
| Re: simple select * from table is taking lot of time [message #535252 is a reply to message #535250] |
Tue, 13 December 2011 03:32 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
Its very probably only bringing back those rows first.
I just did a select * from a table in PL/SQL dev and had results back in .156 seconds. But it was only 36 rows of a table containing 576,147,300 (according to stats, it will be higher now)
If I returned them all, it'd take a heck of a lot longer that 1 second 
I'm not convinced the original quick returns were 'genuine'
[Updated on: Tue, 13 December 2011 03:33] Report message to a moderator |
|
|
|
|
|
|
|
| Re: simple select * from table is taking lot of time [message #535473 is a reply to message #535254] |
Wed, 14 December 2011 04:33 |
|
gogol_1987
Messages: 12
Registered: March 2011
Location: kolkata
|
Junior Member |
|
|
hi gems...
sorry for my late reply...
i traced the session by alter session set sql_trace=TRUE;
i am uploading the tkprof output of that trace file. please have a look. the execute phase is taking a lot of time.
i cancelled my request(select * from table) after several minutes because it just hanged.
Also i noticed another thing...i cant take the simple export dump of those schemas. when the export advances to take dump of those tables, it gets hanged.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
