Re: Better cardinality estimate when dialing optimizer_features_enable back

  • From: Neil Kodner <nkodner@xxxxxxxxx>
  • To: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
  • Date: Tue, 24 Nov 2009 17:40:14 -0500

>
> PARSING IN CURSOR #2 len=1011 dep=0 uid=352 oct=3 lid=352
> tim=21560198417308 hv=1943250271 ad='7e35d080'
>
> SELECT  LETTER_BIN.LTR_BIN_BARCODE_ID AS LTR_BIN_BARCODE_ID,
> LETTER_BIN.CREATED_BY AS CREATED_BY, LETTER_BIN.CREATED_DT AS CREATED_DT,
> LETTER_BIN.MAIL_LTR_FLAG AS MAIL_LTR_FLAG, LETTER_BIN.MAIL_DT AS MAIL_DT,
> LETTER_BIN.PROCESS_DT AS PROCESS_DT, LETTER_BIN.DOC_TEMPLATE_ID AS
> DOC_TEMPLATE_ID, LETTER_BIN.SEND AS SEND, LETTER_BIN.XML_NAME AS XML_NAME,
> LETTER_BIN.PRTY_ID AS PRTY_ID, LETTER_BIN.CLM_ID AS CLM_ID,
> LETTER_BIN.LTR_XML AS LTR_XML, LETTER_BIN.TITLE AS TITLE,
> LETTER_BIN.DOC_TYP_CD AS DOC_TYP_CD, LETTER_BIN.LETTER_DETAIL AS
> LETTER_DETAIL, LETTER_BIN.RES_ID AS RES_ID, SUBSTR(DESCR,1,35) AS DESCR,
> LETTER_BIN.CONTENT AS CONTENT, LETTER_BIN.MAIL_IND AS MAIL_IND,
> LETTER_BIN.QUE_ID AS QUE_ID FROM  DOCUMENT_TYPES DOCUMENT_TYPES,
> LETTER_BIN LETTER_BIN WHERE DOCUMENT_TYPES.DOC_TYP_CD =
> LETTER_BIN.DOC_TYP_CD AND (LETTER_BIN.SEND=1 AND LETTER_BIN.TITLE like
> '606%' AND LETTER_BIN.DOC_TYP_CD != '606A' AND LETTER_BIN.CLM_ID IN(SELECT
> CLM_ID FROM CLAIMS CS WHERE CS.CLMT_PRTY_ID=1183577) AND PRTY_ID=64437)
>
> END OF STMT
>
>
>
> In your good trace, it’s doing 0 physical reads (pr=0):
>
>
>
> STAT #2 id=1 cnt=0 pid=0 pos=1 obj=0 op='NESTED LOOPS  (cr=1327 pr=0 pw=0 
> time=23938 us)'
>
>
>
> In your bad trace, it’s doing 1180 physical reads (pr=1180) for the exact
> same explain plan:
>
>
>
> STAT #2 id=1 cnt=0 pid=0 pos=1 obj=0 op='NESTED LOOPS  (cr=1327 pr=1180 pw=0 
> time=11099934 us)'
>
>
>
> I haven’t read every post on this thread thoroughly, so please forgive if
> I’m missing something, but it looks like your just seeing the effect of
> caching.  Logging out does not drop your buffers from the cache.
>

I understand caching completely but I don't get the same effect when running
with a downgraded OFE, hence the initial question-I'm also curious about the
cardinality issue and whether I do or do not need a histogram.  My next step
is to adjust the density per Randolf's advice.

>
>
> Regards,
>
> Brandon
>
>
>
> *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
> oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Neil Kodner
>
>  Take a look at the 10046 files I just generated
>
> http://www.neilkodner.com/good_10046.txt
>
> and
>
> http://www.neilkodner.com/bad_10046.txt
>
>
>
> Pay attention to the second query.  It ran for 15 or so seconds, did lots
> of db file sequential read, and then subsequent executions did not.  I
> disconnected, reconnected again, and the query ran quickly.  I disconnected
> again, reconnected, and got a good plan once again.  I can provide that
> 10046 as well.
>
>
>
> ------------------------------
> Privileged/Confidential Information may be contained in this message or
> attachments hereto. Please advise immediately if you or your employer do not
> consent to Internet email for messages of this kind. Opinions, conclusions
> and other information in this message that do not relate to the official
> business of this company shall be understood as neither given nor endorsed
> by it.
>

Other related posts: