Stats on partitioned table

[Bala, Prakash]

Hello, Data-warehouse application using Oracle 8.1.7.4 on HP-UX 11i Since our ETL process is running for a long time, have been given the task of partitioning some of huge tables and check the results. So picked the top 5 sessions (in terms of running time) and got the sql used by Informatica. Test results were excellent: 48 minutes against the original table vs 15 seconds on the partitioned table to select about 4000 rows from a 5 million row table. The test was done using a separate schema on the same production database. However, after implementing the same in the production schema, the same query is taking about 100 seconds. Automatic_parallel_tuning is set to TRUE. Parallelism is set to 5 at the table level. The only diference that I can see is that the table in production schema is analyzed (using analyze table ... estimate statistics 20 percent) whereas my test table was not analyzed. Would appeciate any input. TIA Prakash