!! Please do not post Off Topic to this List !!
John et al,
The figures below for CBO defaults was quoted by Mogens Norgaard in another
list (OAUG-DBA) in reply to a query about the CBO on 8.0.6. Apparently, this
is the most current value from a public technical note (public at least
sometime (??) back).
John Kanagaraj
Oracle Applications DBA
DB Soft Inc
Work : (408) 970 7002
Listen to great, commercial-free christian music 24x7x365 at
http://www.klove.com <http://www.klove.com/>
** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **
-- Quoted note ---
If the CBO doesn't have stats on a given table/index/column it will use
default values hard-coded in Oracle. It will not
perform any analyze...estimate operations on the objects. Here's the most
current list of hard-coded values I have seen:
=========================================================
/* defaults */
/* Default selectivities are set low to
1. keep cost values low for future resource limiter use
2. keep cost values low for permutation cutoff in kko
Defaults are used for bind variables, general expressions and
unanalyzed tables, except for equality where defaults are not
needed for bind variables.
*/
#define KKEDSREL 0.05 /* default selectivity for < <= > >= */
#define KKEDSEQ 0.01 /* default selectivity for = */
#define KKEDSNE 0.05 /* default selectivity for != */
#define KKEDSDF 0.05 /* default selectivity for all other ops */
#define KKEDSIRL 0.009 /* default selectivity for relation on indexed col */
#define KKEDSBRL 0.009 /* def sel for relation with bind var on index col*/
#define KKEDSIEQ 0.004 /* default selectivity for = on indexed col */
#define KKEDMBR 8 /* default multiblock read factor */
#define KKEDMBW 8 /* default multiblock write factor */
#define KKEDFNR 100.0 /* default - fixed table cardinality */
#define KKEDFRL 20 /* default - fixed table row length */
#define KKEDDNR 2000.0 /* default - remote table cardinality */
#define KKEDDRL 100 /* default - remote table avg row length */
#define KKEDDNB 100 /* default - default # of blocks */
#define KKEDDSC 13.0 /* default - default scan cost */
#define KKEDILV 1 /* default - default index levels */
#define KKEDILB 25 /* default - number of index leaf blocks */
#define KKEDLBK 1 /* default - number leaf blocks/key */
#define KKEDDBK 1 /* default - number of data blocks/key */
#define KKEDKEY 100 /* default - number of distinct keys */
#define KKEDCLF (KKEDDNB*8) /* default - clustering factor */
#define KKECRI 1.5 /* remote table access cost increase factor */
#define KKECFSC 1.0 /* fixed table scan cost */
#define KKECFNB 0 /* fixed table number of blocks */
#define KKECMXB 15 /* maximum byte length for normalization */
#define KKECBBS 256.0 /* base for byte sequence normalization */
#define KKECSPC ' ' /* space byte value */
#define KKECSPD 86400.0 /* seconds per day */
#define KKESROH 10.0 /* sort per row overhead in bytes */
#define KKESAUT 0.75 /* sort area utilization */
#define KKESROP 0.10 /* sort row overhead percent */
#define KKESRML 2.0 /* sort run multiple */
#define KKESTP 0x01 /* single table predicate */
#define KKETEQ 0x02 /* equi join */
#define KKETBCPJ 0x04 /* Cartesian product join */
#define KKESOK 0x08 /* input swap ok */
#define KKESWP 0x10 /* inputs swapped */
#define KKEEQP 0x20 /* equipartitioned */
#define KKELKNWC 0x01 /* LIKE no wild card */
#define KKELKTWC 0x02 /* LIKE trailing wild card */
#define KKELKEWC 0x04 /* LIKE embedded wild card */
#define KKELKLWC 0x08 /* LIKE leading wild card */
#define KKELKOWC 0x10 /* LIKE only wild card */
==================================================================
-----Original Message-----
Sent: Friday, September 14, 2001 3:25 AM
To: Multiple recipients of list ORACLE-L
My question of the day is :-
What value does the CBO use as a default number of rows for a table.
Background: -
We all know that if any tables in a query have been analyzed then CBO is
used for the query not RBO (couple of caveats I know but let's continue).
So if 3 tables are used in a query and table a has 500 rows (analyzed) table
b has 50 rows (never analyzed) and table c has 3500000 rows (never analyzed)
all things being equal then CBO is used but what values does the CBO use for
tables b or c to decide which execution plan is best.
On a development system yesterday a query was running slow. I realised that
we had put in a very large data load (3.5M rows). I analyzed the table and
indexes and the query came back in sub second response time. I am trying to
figure what value was being used for num_rows prior to the analyze. It
cannot be based on blocks allocated/used as thet would have increased after
the dataload
Thanks
John
Oracle DBA
BTcellnet
* [EMAIL PROTECTED]
* 0113 388 6062 Desk
* 07713 066194 BT Mobile
**********************************************************************
This email and any attachments may be confidential and the subject of
legal professional privilege. Any disclosure, use, storage or copying
of this email without the consent of the sender is strictly prohibited.
Please notify the sender immediately if you are not the intended
recipient and then delete the email from your inbox and do not
disclose the contents to another person, use, copy or store the
information in any medium.
**********************************************************************
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: John Kanagaraj
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).
