| Simple select is not executed [message #427348] |
Thu, 22 October 2009 03:06  |
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  |
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
|
Senior Member |
|
|
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 |
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 #427407 is a reply to message #427362] |
Thu, 22 October 2009 08:08 |
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 #427424 is a reply to message #427411] |
Thu, 22 October 2009 09:30 |
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 |
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
