RE: Optimizer issue - cost of full table scans
- From: "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 14 Sep 2010 20:49:41 +0200
Hi
I sent the following email but it seems that it didn't get through... So, let's
try again.
Cheers,
Chris
> -----Original Message-----
> From: Christian Antognini
> Sent: Monday, September 13, 2010 11:32 AM
> To: 'Brad Peek'
> Cc: oracle-l digest users
> Subject: Re: Optimizer issue - cost of full table scans
>
> Hi Brad
>
> > I feel the fact that we are on Exadata is likely compounding the issue since
> > full scans are much faster that non-Exadata while single-block reads are
> about
> > the same (discounting the potential speed-up from flash cache).
>
> Greg already gave you good inputs. Let just me add one thing...
>
> The cost of FTS depends on the configuration. Specifically:
> - object statistics (number of blocks)
> - system statistics (both CPU and I/O related statistics)
> - db_file_multiblock_read_count (only when using noworkload system statistics)
>
> Since I frequently see databases using noworkload system statistics, my
> questions are:
> - Do you use noworkload system statistics?
> - Do you let the database engine automatically configure
> db_file_multiblock_read_count?
>
> If you reply with "yes" to both questions, IMO it is likely that you have a
> problem. In fact, for the cost computation, a too low MBRC (8) will be used.
> As a result the cost associated to the FTS is way too small. Especially for a
> system having a good throughput.
>
> It goes without saying that you can set optimizer_index_cost_adj to increase
> the cost of index range/unique scans. But, honestly, I prefer to 1) use
> workload system statistics 2) set db_file_multiblock_read_count.
>
>
> HTH
> Chris Antognini
>
> Troubleshooting Oracle Performance, Apress 2008
> http://top.antognini.ch
Other related posts:
