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
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Bala, Prakash
INET: prakash.bala_at_hp.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Mon Nov 24 2003 - 10:54:27 CST
