Home » RDBMS Server » Performance Tuning » simple select * from table is taking lot of time (11.2.0.1.0)
simple select * from table is taking lot of time [message #535238] Tue, 13 December 2011 01:56 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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 #535241 is a reply to message #535238] Tue, 13 December 2011 02:03 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Are the devs using tools which only return the first X rows of a query?
Re: simple select * from table is taking lot of time [message #535244 is a reply to message #535241] Tue, 13 December 2011 02:55 Go to previous messageGo to next message
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 #535248 is a reply to message #535244] Tue, 13 December 2011 03:21 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
What tools are being used?

Many tools only bring back, say...the first 50 rows. Even if there are a billion rows in the table. Those 50 come back fast, the rest, not so much.
Re: simple select * from table is taking lot of time [message #535250 is a reply to message #535248] Tue, 13 December 2011 03:25 Go to previous messageGo to next message
gogol_1987
Messages: 12
Registered: March 2011
Location: kolkata
Junior Member
the devs are using plsql developer..
in plsql developer, generally first 36 rows come in one page..
that is also not comming..it is getting hanged saying "Executing..."

then they tried to use command window...there also it is not comming..
Re: simple select * from table is taking lot of time [message #535252 is a reply to message #535250] Tue, 13 December 2011 03:32 Go to previous messageGo to next message
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 Smile



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 #535253 is a reply to message #535252] Tue, 13 December 2011 03:36 Go to previous messageGo to next message
gogol_1987
Messages: 12
Registered: March 2011
Location: kolkata
Junior Member
my table has only 7 lacs rows and i dont think its much higher for oracle..
actually earlier it was working fine...earlier means till yesterday night...

but from today morning i dont know what happenned to the database..

is it the problem with the SGA?? should i bounce the database or flush the buffer cache??
Re: simple select * from table is taking lot of time [message #535254 is a reply to message #535253] Tue, 13 December 2011 03:39 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Could depend on various factors, have you tried tracing the sessions?

Check this post (http://www.orafaq.com/forum/mv/msg/84315/433888/153040/#msg_433888) and fire up the requisite data
Re: simple select * from table is taking lot of time [message #535473 is a reply to message #535254] Wed, 14 December 2011 04:33 Go to previous message
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.
Previous Topic: How to improve performance
Next Topic: Situations where FAST REFRESH Materialized View does a COMPLETE REFRESH?
Goto Forum:
  


Current Time: Fri Mar 29 08:07:51 CDT 2024