| poor performance after 9i upgrade [message #57595] |
Wed, 25 June 2003 02:56  |
sender
Messages: 3
Registered: September 2002
|
Junior Member |
|
|
|
We recently upgrade from 8.1.7 to 9i. Our application run poorly afterward. Some OLTP type store proc took 2-3 times longer to complete. But some reporting store proc is significantly faster. Posting to Metalink, they asked us to "tune the DB". We have no experienced DBA, just have some class-room DBA training. Can anybody give us some hints where should we look at.
|
|
|
|
| Re: poor performance after 9i upgrade [message #57596 is a reply to message #57595] |
Wed, 25 June 2003 03:06  |
Siddharth Bahri
Messages: 18
Registered: March 2001
|
Junior Member |
|
|
Analyze the schema using the following package:
begin
DBMS_STATS.GATHER_SCHEMA_STATS(
ownname=>'--YOUR_SCHEMA_NAME--',
estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt=>'AUTO'
);
end;
or
begin
DBMS_STATS.GATHER_SCHEMA_STATS(
ownname=>'--YOUR_SCHEMA_NAME--',
estimate_percent=>25,
block_sample=>true
);
end;
This will generate all the stats for the schema and will help the cost based optimizer to select a more efficient execution plan which will make the queries faster.
-Siddharth
|
|
|
|
|
|
| Re: poor performance after 9i upgrade [message #57621 is a reply to message #57596] |
Thu, 26 June 2003 04:44 |
sender
Messages: 3
Registered: September 2002
|
Junior Member |
|
|
|
Thanks for the reply. But let me check if I understand it correctly. The package you provide will gather stats. So I should run it with 8.1.7. gather the stats and feed it to 9i so that the 9i optimizer will use the stats to determine the execution plan. Is this the whole idea?
|
|
|
|
| Re: poor performance after 9i upgrade [message #57626 is a reply to message #57621] |
Thu, 26 June 2003 06:41 |
Siddharth Bahri
Messages: 18
Registered: March 2001
|
Junior Member |
|
|
No. Just run the procedure with the given or your own parameters. This will gather stats for the 9i cost based optimizer. If you dont gather stats for your whole schema then by default the rule based obtimizer will be used to prepare the execution plans which may be not as efficient as plans prepared by the cost based optimizer.
Cost based optimizer uses the stats to select the most effiecint plan based on the costs it calculates for all the plans that can be used to execute this query. while the rule based optimizer just uses a set of rules to prepare a plan
HTH
-Siddharth
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
