| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: gather stats slows performance?
Hi Allan
> dbms_stats.delete_schema_stats
> dbms_stats.gather_schema_stats (using defaults for all the options)
With defualt options no index statistics are generated! Therefore set CASCADE=>TRUE. You should gather system statistics as well.
If you migrate from RBO, a first row optimization is usually better (per default OPTIMIZER_MODE = CHOOSE, i.e. ALL_ROWS). Since you are working with 9.2, I suggest you to use the new first row optimizer, i.e. FIRST_ROWS_n.
> After stats were gathered, queries now seem to be using the CBO. A
> few particularly slow queries are now much faster, but overall,
> application responsiveness has slowed. To confirm this, I deleted
> stats and the overall performance picked back up while the few
> troublesome queries are slow again.
You should compare execution plans, just saying that it is slower doesn't
give the necessary information....
My guess is that they aren't the same, i.e. with your current statistics and
configuration the CBO isn't performing well => it is not generating the
optimal execution plan.
> My guess as to why this is, is that the CBO is slower than the RBO,
> even though it may come up with a better plan. And since we are not
> using bind variables, virtually every query has to be parsed from
> scratch. Is this a reasonable conjecture?
If you enable SQL trace you are able to confirm/deny your conjecture.
> Until we can get our applictation refactored to use more stored procs
> and bind variables, are we likely to be better off without using
> statistics and the CBO?
To workaround the bind variables problem I suggest you to set CURSOR_SHARING=FORCE. This setting must be carefully tested!
Chris
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
 |
 |