Database Dead Slow after Gathering Statistics
Hi
We have an Oracle 11.1.0.7 database running on Windows 2003 32bit. As part of normal activities i gathered schema statistics after a bulk data load of about 20 GB. It is noticed that the performance of the database is dead slow after statistics gathering , for example a select query which used to take 2 seconds to execute takes 2-3 minutes after gathering the statistics. After deleting the statistics , the performance is back to normal . The parameter optimizer_dynamic_sampling is 2.
I checked the AWR report , significant difference is Direct Path Read Wait Event which was not there after deleting the statistics. After gathering the statistics Oracle is doing Full Table Scans instead of Nested Loop Joins .
We have an Oracle 11.1.0.7 database running on Windows 2003 32bit. As part of normal activities i gathered schema statistics after a bulk data load of about 20 GB. It is noticed that the performance of the database is dead slow after statistics gathering , for example a select query which used to take 2 seconds to execute takes 2-3 minutes after gathering the statistics. After deleting the statistics , the performance is back to normal . The parameter optimizer_dynamic_sampling is 2.
I checked the AWR report , significant difference is Direct Path Read Wait Event which was not there after deleting the statistics. After gathering the statistics Oracle is doing Full Table Scans instead of Nested Loop Joins .
0