Re: Better cardinality estimate when dialing optimizer_features_enable back
- From: Randolf Geist <info@xxxxxxxxxxxxxxxxxxxxx>
- To: Randolf Geist <info@xxxxxxxxxxxxxxxxxxxxx>, Neil Kodner <nkodner@xxxxxxxxx>
- Date: Tue, 24 Nov 2009 22:39:30 +0100
> Letters are processed for each individual employer on a program that
> runs on our app-server. I don't need to get into the evils of row-by-
> row processing. It's not my batch job but it's my problem now that
> the queries are no longer performing.
I was expecting such an answer - still it's important to raise the question.
> I dont recall ever saying that a FTS is faster. I'm saying that what
> used to take a fraction of a second per execution is now taking
> upwards of 30 seconds. This table has a pair of inline CLOBS so an
> FTS can sometimes take a while.
>
> Both execution plans can be found at
>
> http://www.neilkodner.com/allstats_both_queries.txt
But those plans don't show evidence that they perform differently - the only
difference is a hash join instead of a nested loop and a hash unique instead of
sort unique but the number of blocks processed is the same - these two examples
ought to perform almost equally.
The access to LETTER_BIN is the same operation and since PARTIES returns
exactly a single row I can't identify why these two plans should show a
significantly different run time behaviour - and according to the ALLSTATS LAST
output both completed in the same time (less than a second since no physical
reads were involved).
When comparing actual execution time you need to be very careful if physical
reads are involved or not - reading 2,000 blocks at 8ms per block will take
significantly more time than reading those 2,000 from memory.
Can you provide an example with measured different runtime behaviour?
By the way, the LETTER_BIN table seems to have a significant number of
migrated/chained rows - probably due to the large rows as you say with inline
CLOBs.
> As a trial, I also modified the query to use parties.party_id rather
> than letter_bin.prty_id - it had no effect on the plan's cardinality.
Sorry, my intention here was to have both in the query - a filter on
PARTIES.PRTY_ID _and_ LETTER_BIN.PRTY_ID to avoid the transitive closure from
taking place.
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
Co-author of the forthcoming "OakTable Expert Oracle Practices" book:
http://www.apress.com/book/view/1430226684
http://www.amazon.de/Expert-Oracle-Practices-Database-Administration/dp/1430226684
___________________________________________________________
Preisknaller: WEB.DE DSL Flatrate für nur 16,99 Euro/mtl.!
http://produkte.web.de/go/02/
--
//www.freelists.org/webpage/oracle-l
Other related posts:
