Cost Baseed Optimizer and Schema Stats

Hi there,

  I am running Oracle9i under Windows 2000 Server and I have the following performance problem in connection with cost based optimization (CBO) and the schema statistics:

  One of the applications talking to my Oracle server performs a lengthy import of data over night. I am not referring to Oracle's imp tool here, the application imports data through SQL (OCI) commands while reading from import files.

  The whole import is supposed to take around 2.5 to 3.5 hours. Since a while ago the schema had no partitioned tables, so rule based optimization was used in all SQL statements. Back then performance was as described above: 2.5-3.5 hours import time. Now partitioned tables have been introduced into the data model, so the SQL commands during the import are now CBO because partitioned tables require CBO. I get the following behaviour:

  After starting the import in the evening, performance looks good and users go home in good spirits. Estimated remaining time is normal. After returning the next moring the import isnt even half way done and says it needs another 11 hours or so. Client, Oracle Server and network connection are idle. (By the way no schema statistics available at that point of time). Now when I create a Schema statistics (dbms_stats.gather_schema_stats('schema_name')) while the import is running, it suddenly gets accelerated as the statistics becomes available. Estimated remaining time drops significantly. Immediately after that I delete the statistics I just gathered but the estimated remaining time drops further... to a value expected. The import is "alive" again.

  why is that and how can I make sure the import DOES NOT SLOW DOWN over night ?

Thanks a lot,

AH
:( Received on Fri May 07 2004 - 02:50:18 CDT