This is not outdated -- the Oracle queries against the data dictionary
tables are tuned for RULE and so analyzing the SYS tables wreaks havoc
in the database.
--- Amar Kumar Padhi <[EMAIL PROTECTED]> wrote:
> My information on this may be a bit outdated. I had read it somewhere
> that
> statistics are not to be generated for SYS tables. Oracle will handle
> the
> dictionary information access, irrespective of the optimizer mode
> being
> used. The setting of the optimizer_mode should affect the application
> queries being generated for other schemas. So if you are using CBO,
> setting
> the Optimizer_mode to first_rows looks more applicable than doing an
> alter
> session every time. I am not able to comment on the poor performance
> when
> accessing the dict.
>
> rgds
> amar
>
> -----Original Message-----
> Sent: Friday, December 14, 2001 11:00 PM
> To: Multiple recipients of list ORACLE-L
>
>
> FYI,
>
> I just logged a call with Oracle re. Designer 6.0 vs.
> all_cons_columns.
>
> A developer noticed that whenever he queries that data dictionary
> table as
> part of generating database from server models, the process slows
> down to a
> crawl.
>
> If the target doesn't have any constraints, no problem.
>
> I am starting to wonder if it isn't because FIRST_ROWS was specified
> on this
> database, most of the developers are developing forms (6i) so I set
> the
> init.ora parameter accordingly.
>
> The Oracle documentation however implies that the CBO only switches
> to rule
> when there are no stats if optimizer_mode had been set to CHOOSE, if
> it's
> either ALL_ROWS or FIRST_ROWS it tries to use the CBO against the
> data
> dictionary anyway. How the CBO can run when there are no stats
> probably
> means it defaults to full table scans (?).
>
> OPTIMIZER_MODE = { all_rows | first_rows | rule | choose }
>
> The OPTIMIZER_MODE parameter specifies the approach and mode of the
> optimizer for your session.
>
>
> See Also: Oracle8i Concepts <../../server.817/a76965/toc.htm> and
> Oracle8i
> Performance Guide and Reference <../../server.817/a76992/toc.htm>
> for
> information on how to choose a goal for the cost-based approach based
> on the
> characteristics of your application
>
>
>
> * all_rows specifies the cost-based approach and optimizes for best
> throughput.
> * first_rows specifies the cost-based approach and optimizes for best
> response time.
> * rule specifies the rule-based approach. (The rule-based optimizer
> does not use function-based indexes.)
> * choose causes the optimizer to choose an optimization approach
> based
> on the presence of statistics in the data dictionary.
>
> Is this correct? If so, then I suppose I should set the init.ora
> parameter
> back to optimizer_mode=choose, and tell each developer to put an
> alter
> session statement on their clients to alter their sessions
> automatically to
> first_rows. The developer using designer will then be able to run it
> in a
> timely manner.
>
> Please tell me if my suspicion is correct.
>
> If correct, it begs the question: why are first_rows and all_rows
> available
> for the init.ora file???
>
> Regards,
> Patrice Boivin
> Systems Analyst (Oracle Certified DBA)
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Boivin, Patrice J
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing
> Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
__________________________________________________
Do You Yahoo!?
Check out Yahoo! Shopping and Yahoo! Auctions for all of
your unique holiday gifts! Buy at http://shopping.yahoo.com
or bid at http://auctions.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rachel Carmichael
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
