Path: news.easynews.com!core-easynews!newsfeed1.easynews.com!easynews.com!easynews!cyclone1.gnilink.net!gnilink.net!news.glorb.com!postnews2.google.com!not-for-mail From: streib@cs.indiana.edu (Allan Streib) Newsgroups: comp.databases.oracle.server Subject: Re: gather stats slows performance? Date: 8 Aug 2004 18:41:59 -0700 Organization: http://groups.google.com Lines: 54 Message-ID: References: <4115ef4c$1@post.usenet.com> NNTP-Posting-Host: 156.56.122.185 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Trace: posting.google.com 1092015719 17019 127.0.0.1 (9 Aug 2004 01:41:59 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Mon, 9 Aug 2004 01:41:59 +0000 (UTC) Xref: core-easynews comp.databases.oracle.server:227829 X-Received-Date: Sun, 08 Aug 2004 18:40:57 MST (news.easynews.com) "Christian Antognini" wrote in message news:<4115ef4c$1@post.usenet.com>... > With defualt options no index statistics are generated! Therefore set > CASCADE=>TRUE. Ah-ha. I did not make that assumption -- my reading of the explanation of dbms_stats.gather_schema_stats() was that it gathered stats on all the objects in the schema, which I took to include indexes. I will try your suggestion. > 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.... Yes this was just a "first impression" observation, nothing quantitative. It was just unexpected. > 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! I recall this from Tom Kyte's book -- however I would rather fix things properly than enable a potentially troublesome option. Thanks for the suggestions -- I don't have much experience with Oracle tuning so this is good info for me. Allan