| Slow Retrieval of simple sql statement [message #375002] |
Wed, 18 July 2001 04:42  |
Mike Oakes
Messages: 25
Registered: December 2000
|
Junior Member |
|
|
Hello All,
I am running a simple sql statement such as
update table
set colum2 = 2
where column1 = '0010R0R1C01000';
I have a good index on column1 but its taking at least 10 minutes to execute. I have about 240,000 records in the table.
If i run the same query on an exactly same schema/database but a different environment i get the results lickedy split.
Could I have some type of index corruption. I'm not sure where to look.
Any help would be greatly appreciated.
Thanks,
Mike O.
|
|
|
|
| Re: Slow Retrieval of simple sql statement [message #375006 is a reply to message #375002] |
Wed, 18 July 2001 09:28  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
generally in a batch environment Oracle will use ALL_ROWS as an optimizer goal, whereas an online will use FIRST_ROWS. This could be one difference. In sqlplus, try "set autotrace on" to see the explain plan and metrics.
Make sure that the table is analyzed "analyze table ABC estimate statistics;" will analyze that table and index. Before doing that however, see what the current stats are in USER_TABLES and USER_INDEXES.
Select table_name, num_rows, chain_cnt, last_analyzed from user_tables where table_name = 'ABC'; (not sure of all the field names).
Do the same for user_indexes. Fields will include distinct_keys etc. If any of these stats are null or out of date you have likely found a problem.
"analyze table ABC validate structure cascade;" will check that the table and index entries match.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
