Thanks for your answers, although I have an example of where I changed db_cache_size and saw a big improvement in a table read. My test instance has only two tables. Table one (190 millions records) does a left outer join to table two (20 million records), creating table three.
Before changing anything, the initial table scan on table one was over 21,000 minutes. Once I changed db_cache_size to 10G, The table read went down to 90 minutes. Perhaps I am missing something, but changing that db_cache_size seemed to reduce I/O. There is nothing else running on the system/instance. I am the only one accessing it.
Edited by: 964470 on Oct 10, 2012 8:06 AM
You let the query run for more than 2 weeks?!
1* select (21000/60)/24 DAYS from dual SQL> / DAYS ---------- 14.5833333
964470 wrote:ORA-01555 can result from COMMIT inside loop
I am still not convinced the AMM is working correctly on my system. While running a large query, I received the dreaded ORA-01555 error. Before the query died, it read a specific table in 22 minutes. When I started the query again, the estimate time to read the same table is 120 minutes. I would really appreciate any feedback. If the AMM is indeed working correctly, what would make my table read speed decrease?