RE: FIRST_ROWS and the data dictionary
Patrice, Oracle's response is a bit different to my recent experiences with first_rows. When we had our database set to first_rows, I discovered that queries against data dictionary tables (eg all_objects, all_synonyms) are given different execution plans if you are in first_rows vs in choose. We ended up changing our database to choose. If you want to search the archives, the previous discussion was under the subject of "Synonyms can be VERY bad for performance". A reply from Anita Bardeen on this subject was: "-Original Message- Sent: Thursday, 1 November 2001 10:30 To: Multiple recipients of list ORACLE-L Greg, Hey! I resemble that remark ;) The data dictionary views are optimized to use the RBO or are heavily hinted to force a specific access path. ALL_ROWS and FIRST_ROWS force the CBO to be used so a different access path may be taken resulting in poor performance. Obligatory notes: Note: 35272.1 "Is ANALYZE on the Data Dictionary Supported (TABLES OWNED BY SYS)?" Note: 35934.1 "TECH: Cost Based Optimizer - Common Misconceptions and Issues" Note: 66484.1 "Which Optimizer is used" HTH, -- Anita " Whilst searching for the notes suggested by Anita, I came across a good forum discussion (see http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_datab ase_id=FOR&p_id=279251.999 ) This describes how the ODBC driver 8.1.7.4 has been fixed / improved to use rule hints when accessing the data dictionary. Maybe you could request Designer to be fixed / improved in the same way as the ODBC driver. Or setup a logon trigger that only fires if the program is Designer and have the logon trigger alter the optimizer_mode to choose (or rule). HTH, Bruce Reardon -Original Message- Sent: Sunday, 16 December 2001 11:50 There are no stats for our data dictionary, we never analyzed the data dictionary. According to Oracle Support, the rule-based optimizer kicks in whenever we query the data dictionary, regardless of optimizer_mode in init.ora: RESPONSE == Q. How does the CBO behave when set to first_rows, and there are no statistics for the data dictionary? (there are not supposed to be statistics on the data dictionary, and none exist on this particular database). A. Data dictonary queries are RULE optimized, the CBO should make no difference. Q. Does the CBO do full table scans when there are no stats and the optimizer_mode is set to first_rows or all_rows instead of CHOOSE? A. Depends on the rules and the availability of indexes. Most of what we see are views of much deeper tables, but the baisc answer to the question is that setting the OPTIMIZER_MODE does not affect how the database accesses the data dictionary. I am doing tests locally on the server to see what explain plan I get. Thanks. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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).
Re: FIRST_ROWS and the data dictionary
I don't see any references to generating statistics on SYSTEM tables.
Why did you bring it up?
As I asked Patrice in my earlier response, have statistics been generated
recently from the RAU?
If not, they should be, as designer is notorious for poor performance
with stale stats. The good performance in the absence of generating
a database server model without constraints seems to suggest poor
join performance with a data dictionary table when the constraints
are generated.
Assuming the developer is correct in his assessment of the situation.
Jared
On Friday 14 December 2001 19:20, Amar Kumar Padhi 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)
Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1"
Content-Transfer-Encoding: quoted-printable
Content-Description:
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jared Still
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).
RE: FIRST_ROWS and the data dictionary
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).
RE: FIRST_ROWS and the data dictionary
Title: RE: FIRST_ROWS and the data dictionary
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-
From: Boivin, Patrice J [mailto:[EMAIL PROTECTED]]
Sent: Friday, December 14, 2001 11:00 PM
To: Multiple recipients of list ORACLE-L
Subject: FIRST_ROWS and the data dictionary
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).
Re: FIRST_ROWS and the data dictionary
Have you recently generated stats from RAU?
Jared
"Boivin, Patrice
J" To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
<[EMAIL PROTECTED] cc:
mpo.gc.ca> Subject: FIRST_ROWS and the data
dictionary
Sent by:
[EMAIL PROTECTED]
12/14/01 11:00 AM
Please respond to
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
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 PRO
