| Analyze Help required!!! [message #71193] |
Mon, 23 September 2002 00:39  |
Suhail Ahmed
Messages: 2
Registered: July 2002
|
Junior Member |
|
|
Hi,
In my Oracle/D2K application a batch process was running on the client side with all the default parameter settings
i.e the cost base optimiser mode.
And the upload (that is one process in our Application to upload the data) of 20 lakh records was taking 1 or 1.30hrs only.
But user ran the Analyze command (with Compute Statistics option) one day and
the upload process become very slow it has taken app. 20hrs
in the upload of same file.
Now to reduce the time what user has done is changed the parameter(optimizer mode) from Cost Based to Rule based
and after that the same upload is happening in app 3hrs.
But when user has to run another process , say Process B(one of the Application Main Process) again
he has to change the Optimizer mode from Rule based to Cost based.
First I would like to know what counld be the reason that after Running the Analyze command
why the process become so slow and
what is the remedy now so the user need not to change the Optimizer mode again and agaian.
Analyze command was run for all the tables and indexes in the schema.
Thanks & Regards,
Suhail
|
|
|
|
| Re: Analyze Help required!!! [message #71196 is a reply to message #71193] |
Mon, 23 September 2002 12:36  |
Grant
Messages: 578
Registered: January 2002
|
Senior Member |
|
|
The first this is both are Cost Based Optimizer (CBT). The diffrence between the two is COST has all the objects analyzed and RULE does not. You do not need to change the mode each time. Set OPTIMIZER_MODE=CHOOSE.
To change to CHOOSE:
SQL> dbms_utility.analyze_schema('SCHEMA','COMPUTE');
To Change to RULE:
SQL> dbms_utility.analyze_schema('SCHEMA','DELETE');
SCHEMA would be the schema you want to change. You can also use hints to switch between them or force the use of an index.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
