| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: CBO - default num_rows in a table
Jared,Christopher
The tuning manual states the following
"If the optimizer uses the cost-based approach for a SQL statement, and if some tables accessed by the statement have no statistics, then the optimizer uses internal information (such as the number of data blocks allocated to these tables) to estimate other statistics for these tables"
However in the exmaple I gave the no of blacks had been increased dramatically and yet the CBO did not know about it, therefore indicating to me that it uses something other than the no of blocks. I am just trying to get an insight into whatever that something is.
John
-----Original Message-----
From: Jared Still [mailto:jkstill_at_cybcon.com]
Sent: 14 September 01 16:37
To: ORACLE-L_at_fatcity.com; Hallas John
Subject: Re: CBO - default num_rows in a table
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: ---------------------------------------- **********************************************************************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.
 |
 |