> > 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. >