Home » RDBMS Server » Performance Tuning » Simple select is not executed (Oracle 10g, Solaris 10)
Simple select is not executed [message #427348] Thu, 22 October 2009 03:06 Go to next message
gym963
Messages: 4
Registered: October 2009
Location: Hungary
Junior Member
Hi,

In our system there is one table where a simple select does not execute. (Other tables work well.)

Let's say:

table name = TABLE1
id = TABLE1_ID
Primary key index = TABLE1_PK1 (composed index)
Unique index on TABLE1_ID = TABLE1_UK1

select * from TABLE1;

command freezes the system where it was issued (sqlplus, TOAD). Even an explain plan request on this select freezes the tool.

select * from TABLE1 where TABLE1_ID = 'existing_value';

executes without delay and returns the appropriate result set.

If statistcis are gathered for this table, then it works properly.

Any idea about the reason?

Thanks



Re: Simple select is not executed [message #427354 is a reply to message #427348] Thu, 22 October 2009 03:34 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
select * from TABLE1 


When you do a single select on a table without any preicates it goes for Full Table Scan (FTS).
May be you have huge amount of data on your table and the reason why tools like sqlplus got hanged.
You know your data..


How many rows are there in this table?

What are the index available for this table
I believe that there is an index on TABLE1_ID.

Quote:

If statistcis are gathered for this table, then it works properly.

Read the Performance tuning sticky in Performance tuning section
Re: Simple select is not executed [message #427356 is a reply to message #427354] Thu, 22 October 2009 03:39 Go to previous messageGo to next message
gym963
Messages: 4
Registered: October 2009
Location: Hungary
Junior Member
Hi,

The table contains about 100,000 rows. There are bigger tables, and the simple select returns the first rows without problem.

Yes, there is an index, as I wrote.
Re: Simple select is not executed [message #427362 is a reply to message #427356] Thu, 22 October 2009 04:04 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
You an also have a look at session wait .
v$session_wait

What happened when you select a single column for the table?
How many columns are there in the table?
Re: Simple select is not executed [message #427407 is a reply to message #427362] Thu, 22 October 2009 08:08 Go to previous messageGo to next message
gym963
Messages: 4
Registered: October 2009
Location: Hungary
Junior Member
These are the wait data after executing "explain plan for select * from TABLE1;":

EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED
log file sync 1 0 0
db file sequential read 674847 0 2372
SQL*Net message to client 19 0 0
SQL*Net message from client 18 0 4459

For a single column the behaviour is the same as for select *, even if the single columns id the TABLE1_ID which is indexed.

There are 82 columns in the table.
Re: Simple select is not executed [message #427411 is a reply to message #427407] Thu, 22 October 2009 08:27 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Have a look at V$SESSION_LONGOPS
V$SESSION [ what is the value of the STATUS]
V$PROCESS

Re: Simple select is not executed [message #427424 is a reply to message #427411] Thu, 22 October 2009 09:30 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's quite possible that the high watermark for this table is far higher than the total number of rows in the table.
Quote:
Even an explain plan request on this select freezes the tool.
This is quite worrying - I'd consider dropping and recreating the table, or opening a SR with Oracle Support on this.
I suspect that the reason the SELECT is hanging is that the CBO is trying to create a plan for the query, and that this is hanging (as it does when you do an explain plan).


[Updated on: Thu, 22 October 2009 09:31]

Report message to a moderator

Re: Simple select is not executed [message #427542 is a reply to message #427424] Fri, 23 October 2009 03:35 Go to previous messageGo to next message
gym963
Messages: 4
Registered: October 2009
Location: Hungary
Junior Member
Ok, thanks, we will ask Oracle Support. I will report the result.
Re: Simple select is not executed [message #427556 is a reply to message #427542] Fri, 23 October 2009 04:34 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Before you can just try to restart the instance, it may be possible that internal SGA structure is corrupted and then a simple restart fixes it.

Regards
Michel
Re: Simple select is not executed [message #427890 is a reply to message #427348] Mon, 26 October 2009 05:40 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Try to reorganize the table.
The problem may be caused by a lot of chained rows in a table.

HTH.
Re: Simple select is not executed [message #427915 is a reply to message #427890] Mon, 26 October 2009 06:34 Go to previous message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe it should then FIRST check if there is such rows (using ANALYZE TABLE) BEFORE reorganizing anything.

Regards
Michel
Previous Topic: explain plan difference between 9i and 10g
Next Topic: INTERNAL_FUNCTION in execution plan
Goto Forum:
  


Current Time: Wed Sep 28 17:44:31 CDT 2016

Total time taken to generate the page: 0.17186 seconds