This content has been marked as final. Show 31 replies
A full table scan doesn't say any thing by itself, you could be performing a full table scan against a table that sizes just a couple of blocks and it would even be more efficient than performing the access by means of the index. Or you could be accessing a table against thousands, tens of thousands or more blocks and performance would be severely degraded. This fact doesn't have anything to do with the oracle version, it has always been the same. It is not a feature or a 10g bug, I would rather say it is an application programming bug between the chair and the keyboard.
Seriously, you will have to analyze the execution plan of the problematic query and use realistic metrics to evaluate the workload generated by the query execution, and have this query optimized.
We are runing in 10g (10.2.0.3), when using fulluser460521,
table scan, the perfromance is very slow. Is a bug in
ASM or SQL program problem? How can I vertify the
problem come from? I have runing health check in
oracle but found nothing.
Take a look at generating a 10046 trace at level 8 or 12 for the SQL program running the query. It takes a little practice to learn how to manually read a 10046 trace file, but the wait events and CPU/elapsed time present in the trace files provide a significant amount of detail to determine the source of the performance problem.
Enabling a 10046 trace file for a session:
Re: SQL_TRACE help to newbie in oracle
Sample of interpretting a 10046 trace file:
For more information on using a 10046 trace, read the book "Optimizing Oracle Performance".
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Usually you don't want your applications to access a very large part of a large table. This will be very slow and could deteriorate the performance of your application severely.
Will a user wait for several minutes/hours while the query is running?
You can usually do this smarter by creating MVs or summery tables which refresh every X intervals or have you application perform multiple inserts to keep the summery tables updated.
Of course unless you are talking about very specific reports running on DSS systems here.
Keyword here of course is usually. However, this could be a DSS or reporting system, could be an overnight batch process, could be an infrequent requirement not worthy of the general overheads in maintaining a MV or index, etc. etc.
Point being a FTS is not necessarily a bad thing and might be "cheaper" than using an index or maintaining a MV...
We had the same issues with full table scans in 10.2.0.3 where we had gathered system stats (have you gathered system stats?) and the optimizer seemed to think the disks could return data faster than actual. After modifications to weight the disks more expensive; index access path plans returned.
Loads of patches for 10.2.0.3 look through the patch database and you may find a description that is spookily similar to your issue.
Yep, many times FTS is cheaper then using an index. More than most people think. If I'm not mistaken, Oracle says that if you query 7.5% of the table rows and above you are usually better off with an FTS.
Its true that you shouldn't avoid an FTS just because it is an FTS, I was just commenting that you CAN almost always avoid an FTS if you need too. But hack, this goes for large index scans as well.
I would be surprised if Oracle actually said that.Life is surprising :)
"Queries that retrieve less than 7 percent of the table data blocks
should use an index range scan. Conversely, queries that read more than 7 percent of the table data blocks will probably be faster with a full-table scan. "
Not true for IOTs, of course.
I've read this in an Oracle University Press book seven or eight years ago and it has stuck with me ever since.