RE: Better cardinality estimate when dialing optimizer_features_enable back

From: Allen, Brandon <Brandon.Allen_at_OneNeck.com>
Date: Tue, 24 Nov 2009 15:37:37 -0700
Message-ID: <64BAF54438380142A0BF94A23224A31E112E9EF04B_at_ONEWS06.oneneck.corp>

Neil, just to make sure we're on the same page - are you talking about this query - hash_value=1943250271?

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.

Regards,
Brandon

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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.

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Nov 24 2009 - 16:37:37 CST