Re: CBO - default num_rows in a table

!! Please do not post Off Topic to this List !!

Can't recall at the moment, but if you take a look at the tuning manual, it will tell you what the defaults are.

Jared

On Friday 14 September 2001 03:20, Hallas John wrote:
> 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
>
> -----Original Message-----
> Sent: 13 September 01 22:22
> To: Multiple recipients of list ORACLE-L
>
>
> !! Please do not post Off Topic to this List !!
>
> Cherie - We have been using the autoextend feature for 6 months now and
> have been really pleased with it. I am now studying the Oracle White Papers
> on the locally managed and uniform extent philosophy and beginning to
> follow that scheme. I would recommend studying it carefully. We have had a
> couple of runaways that ate up a lot of disk, that is the most obvious
> downside. The upside is obvious in the title of the Oracle white paper
> "Stop Defragging and Start Living". Here are my procedures so far:
>
> 1. Use Oracle's new uniform extent recommendations to eliminate free extent
> fragmentation. Since all extents are the same size, no fragmentation can
> occur.
> 2. Use locally-managed tablespaces per Oracle's recommendation.
> 3. Set all extents in a tablespace to the same size. There are no unusable
> small free extents, free space is usable by any segment, and administration
> is minimized.
> 4. Use only 3 extent sizes: 128K, 4M, and 128M
> 5. All segments should have less than 1,024 extents. When a table
> approaches 1,024 extents, it should be moved to the next larger extent size
> tablespace. 6. Monitor archive log space.
> 7. Temporary and rollback tablespaces should be divided into 1,024 extents
> for optimal performance.
> 8. Export the table before moving it.
> 9. Use the Oracle "alter table XXX move" command.
> 10. Use the Oracle "alter index XXX rebuild" command.
>
> Let me know if you have any more questions, and please share your ideas.
>
> Dennis Williams
> DBA
> Lifetouch, Inc.
> dwilliams_at_lifetouch.com

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: jkstill_at_cybcon.com

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