SQL & PL/SQL

Hi everyone. I've got a problem in my production database and I'm at a loss. I have a query that is taking about 12 seconds to run when it used to take about a half a second. I cloned a test instance from production. The query runs fine in the test instance but it's still slower in prod. For some reason, the query is taking a drastically different execution path.

Any suggestions?

Here's the query:

SELECT a.pk1_value header_id,
b.media_id,
a.seq_num,
c.category_description,
b.description,
d.short_text,
oeh.order_number,
cfeh.quote_number,
cfeh.quote_header_id
FROM applsys.fnd_attached_documents a,
applsys.fnd_documents_tl b,
apps.fnd_attached_docs_form_vl c,
applsys.fnd_documents_short_text d,
ont.oe_order_headers_all oeh,
xxrot.rot_cfe_quote_header cfeh
WHERE a.entity_name = 'OE_ORDER_HEADERS'
AND c.document_id = b.document_id
AND b.document_id = a.document_id
--AND c.pk1_value = a.pk1_value
AND c.function_name = 'ROTOEORD'
AND c.datatype_name IN( 'Short Text', 'Long Text' )
AND b.LANGUAGE = 'US'
AND b.file_name IS NULL
AND d.media_id = b.media_id
AND oeh.header_id = a.pk1_value
AND oeh.orig_sys_document_ref = cfeh.quote_number
and cfeh.quote_number = '2387818'


Now the trace from prod:

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.09 0.09 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 11.59 11.31 0 2900254 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 11.68 11.41 0 2900254 0 0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 173 (APPS)

Rows Row Source Operation
------- ---------------------------------------------------
0 NESTED LOOPS
0 NESTED LOOPS
0 NESTED LOOPS
0 NESTED LOOPS
0 NESTED LOOPS
355648 NESTED LOOPS
355648 NESTED LOOPS
355648 HASH JOIN
1 TABLE ACCESS BY INDEX ROWID OBJ#(33789)
1 INDEX RANGE SCAN OBJ#(33801) (object id 33801)
965272 HASH JOIN
878 TABLE ACCESS FULL OBJ#(33690)
358602 HASH JOIN
2 TABLE ACCESS BY INDEX ROWID OBJ#(41875)
5 NESTED LOOPS
2 NESTED LOOPS
1 TABLE ACCESS BY INDEX ROWID OBJ#(465688)
1 INDEX UNIQUE SCAN OBJ#(465704) (object id 465704)
2 INDEX FULL SCAN OBJ#(33782) (object id 33782)
2 INDEX RANGE SCAN OBJ#(99629) (object id 99629)
358608 TABLE ACCESS FULL OBJ#(33462)
355648 INDEX UNIQUE SCAN OBJ#(33552) (object id 33552)
355648 TABLE ACCESS BY INDEX ROWID OBJ#(33513)
355648 INDEX UNIQUE SCAN OBJ#(33552) (object id 33552)
0 TABLE ACCESS BY INDEX ROWID OBJ#(33973)
454906 INDEX RANGE SCAN OBJ#(34002) (object id 34002)
0 TABLE ACCESS BY INDEX ROWID OBJ#(34016)
0 INDEX UNIQUE SCAN OBJ#(34048) (object id 34048)
0 TABLE ACCESS BY INDEX ROWID OBJ#(33958)
0 INDEX UNIQUE SCAN OBJ#(33968) (object id 33968)
0 TABLE ACCESS BY INDEX ROWID OBJ#(33973)
0 INDEX RANGE SCAN OBJ#(34002) (object id 34002)
0 INDEX UNIQUE SCAN OBJ#(34088) (object id 34088)

And the trace from test:

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.10 0.09 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 138 0.41 0.39 3956 12939 0 2043
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 140 0.51 0.48 3956 12939 0 2043

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 173 (APPS)

Rows Row Source Operation
------- ---------------------------------------------------
2043 NESTED LOOPS
2043 NESTED LOOPS
2043 NESTED LOOPS
1 NESTED LOOPS
1 NESTED LOOPS
1 NESTED LOOPS
2 NESTED LOOPS
1 NESTED LOOPS
1 NESTED LOOPS
1 NESTED LOOPS
1 HASH JOIN
1 NESTED LOOPS
1 TABLE ACCESS BY INDEX ROWID OBJ#(465688)
1 INDEX UNIQUE SCAN OBJ#(465704) (object id 465704)
1 TABLE ACCESS BY INDEX ROWID OBJ#(41875)
1 INDEX RANGE SCAN OBJ#(99629) (object id 99629)
164065 TABLE ACCESS FULL OBJ#(33973)
1 TABLE ACCESS BY INDEX ROWID OBJ#(33513)
1 INDEX UNIQUE SCAN OBJ#(33552) (object id 33552)
1 TABLE ACCESS BY INDEX ROWID OBJ#(33462)
1 INDEX UNIQUE SCAN OBJ#(33512) (object id 33512)
1 INLIST ITERATOR
1 INDEX RANGE SCAN OBJ#(33782) (object id 33782)
2 TABLE ACCESS BY INDEX ROWID OBJ#(33690)
2 INDEX RANGE SCAN OBJ#(33734) (object id 33734)
1 TABLE ACCESS BY INDEX ROWID OBJ#(33789)
2 INDEX UNIQUE SCAN OBJ#(33821) (object id 33821)
1 INDEX UNIQUE SCAN OBJ#(33552) (object id 33552)
1 TABLE ACCESS BY INDEX ROWID OBJ#(33958)
1 INDEX UNIQUE SCAN OBJ#(33968) (object id 33968)
2043 TABLE ACCESS BY INDEX ROWID OBJ#(33973)
2043 INDEX RANGE SCAN OBJ#(34002) (object id 34002)
2043 TABLE ACCESS BY INDEX ROWID OBJ#(34016)
2043 INDEX UNIQUE SCAN OBJ#(34048) (object id 34048)
2043 INDEX UNIQUE SCAN OBJ#(34088) (object id 34088)