Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Optimizer Mode

Re: Optimizer Mode

From: Kirti Deshpande <kirtid_at_gte.net>
Date: 1997/08/15
Message-ID: <5t0h18$okc$1@gte2.gte.net>#1/1

Ivan Boesing wrote:
>
> Hi Oracle gurus,
>
> I recently changed the optimizer mode of my database. I collect
> statistics with the analyze command for some tables (about 10). The
> optimizer works fine with this tables and statistics.
> Howewer, I have some doubts. Do I collect statistics for each table in
> database?? If so, how can I do it automatically (or I have do it manually,
> table per table). I should do this by a midnight script, but for every new
> table I would need to update the script..... By other hand, if
> statistics are necessary just for the most used tables I can collect it
> manually, but if the table change in size so frequently how the optimizer
> will work?
> Sumarizing, how can I proceed with statistics and cost based
> optimization method?
>
> Thanks in advance
>
> Ivan Boesing
> boesing_at_geocities.comYou can analyze all tables for a schema owner (and indexes) using
Oracle's analyze_schema package. It needs parameters for Schema Owner and analyze command options. It will analyze tables and indexes (prior to 7.3) for that schema owner. You can execute this package from a shell script at certain time intervals via cron facility (on UNIX). Or you may want to check another Oracle package (dbms_jobs). This jobs package is Oracle's 'cron' facility. Up-to-date statistics on objects will help the CBO to select correct execution plan. If you do not have stats on some tables and have it on others, then, in cases where these tables are invloved in a 'join' Oracle will use 'Rule' based optimization. Hope this will help. Received on Fri Aug 15 1997 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US