Re: Problems with a plan
Date: Mon, 4 Apr 2011 18:41:11 +0000 (UTC)
Message-ID: <pan.2011.04.04.18.41.10_at_email.here.invalid>
On Mon, 04 Apr 2011 18:53:36 +0100, Jonathan Lewis wrote:
>
> It looks to me as if there's an error somewhere in the statistics.
It looks like that. I will try analyzing the tables involved tonight.
>
> You have an index unique scan which gets one rowid from the index, then
> finds 16,198 rows in the table - the rest of the plan is the natural
> consequence of that error. (16,198 times round the nested loop is more
> expensive than the index fast full scan).
>
> The num_rows < 30 / first_rows(30) is irrelevant - you have an order by
> clause inline, so the optimizer has to collect all the data and sort it
> before delivering the first 30 rows, so the plan is implicitly going to
> fall back to all_rows.
Hmmm, thanks. I didn't realize that.
>
> What's your setting for db_file_multiblock_read_count ? Setting your
> optimizer_index_caching to 100 might "help" - it should be 0 by default
> anyway.
SQL> show parameter db_file_multiblo
NAME TYPE
------------------------------------ --------------------------------VALUE
db_file_multiblock_read_count integer 64
SQL> show parameter db_block_size
NAME TYPE
------------------------------------ --------------------------------VALUE
db_block_size integer
16384
>
> What have you got in sys.aux_stats$ for your system statistics ?
SQL> select pname,pval1 from sys.aux_stats$;
PNAME PVAL1
------------------------------ ----------STATUS
DSTART
DSTOP
FLAGS 1 CPUSPEEDNW 1319.45496 IOSEEKTIM 10 IOTFRSPEED 4096 SREADTIM 3 MREADTIM 19.428 CPUSPEED 1232 MBRC 16
-- http://mgogala.byethost5.comReceived on Mon Apr 04 2011 - 13:41:11 CDT