RE: FIRST_ROWS and the data dictionary

From: Reardon, Bruce (CALBBAY) <Bruce.Reardon_at_comalco.riotinto.com.au>
Date: Sun, 16 Dec 2001 13:33:58 -0800
Message-ID: <F001.003DD7FF.20011216132017@fatcity.com>

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).

Data dictonary queries are RULE optimized, the CBO should make no difference.
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?
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: Bruce.Reardon_at_comalco.riotinto.com.au

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: ListGuru_at_fatcity.com (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). Received on Sun Dec 16 2001 - 15:33:58 CST