| Table scan in only 1 of many partitions? [message #163625] |
Fri, 17 March 2006 09:27  |
kulickd
Messages: 2
Registered: March 2006
|
Junior Member |
|
|
My application uses several tables partitioned on one field of their multi-part keys.
In certain partitions full table scans are being performed during some operations - severely degrading the performance (4 min job now takes 5 hours).
The statistics for this partition are up to date, and I've even recomputed them just to be sure. However, this does not solve the problem.
Other partitions running the same jobs run quickly with no table scans. As a matter of fact, if I copy the data in the problem partition into a new partition, it does not exhibit the same behavior.
We have recently upgraded from 10.1.0.4 to 10.2.0.1 and have been seeing these problems in specific partitions since.
Any ideas about what might be wrong are greatly appreciated.
Thanks
[Updated on: Fri, 17 March 2006 09:29] Report message to a moderator |
|
|
|
| Re: Table scan in only 1 of many partitions? [message #163678 is a reply to message #163625] |
Fri, 17 March 2006 19:11  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
It could be the HWM problem. Was the partition once much bigger but has now had most of its rows deleted? If so, you need to rebuild it. If you are on 10g, you can use the SHRINK clause in the ALTER TABLE command. Otherwise you need to rebuild the partition as a separate table and then use the EXCHANGE PARTITION clause to swap the inefficient partition out.
Best way to find out is to use SQL*Trace and TK*Prof. ALTER SESSION SET SQL_TRACE = true;
-- run slow SQL
-- run fast sql
ALTER SESSION SET SQL_TRACE = false;
No go find the trace file and run TK*Prof. Details in the Oracle Performance Tuning manual. If the slow SQL has the same plan as the fast one, and they read the same number of rows (roughly), but the slow SQL has much higher disk reads, then you almost certainly have the HWM problem.
If the difference is not clear from the tk*prof output, post it here for advice.
_____________
Ross Leishman
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
