Hi All/Balakrishna, Thanks for notifying it. Please find attached my explain plan and the SQL script. Please help. Reg, An On Thu, Oct 1, 2009 at 3:04 PM, Balakrishna Y <krishna000@xxxxxxxxx> wrote: > Hi, > > There is no evidence for your question at all .... just post you query and > statistics of your table & indexes ... > > Only then some one can help you out in this . > > Regards > > Bala > > > On 10/1/09, Shastry(DBA) <shastry17@xxxxxxxxx> wrote: >> >> Hi Gurus, >> I have Oracle query which runs fine in production and test1 server but >> taking more time in the recently cloned two more dev servers. i checked for >> explain plan and got that two tables are having FULL TABLE SCANS and didnt >> find anything FTS issue in production and test1 servers with the same data. >> I did analyze two databases completely to have latest stats collection but >> didnt help :(. >> How to tackle this issue please guide. >> Thanks in advance. >> >> Reg, >> An >> > >
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop SELECT STATEMENT Optimizer Mode=FIRST_ROWS 1 4236 SORT UNIQUE 1 457 4236 HASH JOIN OUTER 1 457 4235 HASH JOIN OUTER 1 435 3884 NESTED LOOPS 1 413 3534 HASH JOIN OUTER 1 401 3532 HASH JOIN OUTER 1 379 3182 NESTED LOOPS 1 357 2832 HASH JOIN OUTER 1 345 2828 HASH JOIN OUTER 1 323 2478 HASH JOIN OUTER 1 301 2128 HASH JOIN OUTER 1 279 1778 TABLE ACCESS BY INDEX ROWID TEST.PAGE_TWO 1 36 3 NESTED LOOPS 1 257 1427 NESTED LOOPS 1 221 1425 NESTED LOOPS 1 199 1421 NESTED LOOPS 1 176 1419 NESTED LOOPS 2 306 1416 NESTED LOOPS 36 4 K 1364 NESTED LOOPS 181 14 K 663 NESTED LOOPS 181 10 K 402 NESTED LOOPS 82 4 K 88 TABLE ACCESS FULL TEST.PANUFACTURERS 1 24 57 TABLE ACCESS BY INDEX ROWID TEST.PANU_PARTS 82 2 K 32 INDEX RANGE SCAN TEST.PANUPART_IDX1 82 2 TABLE ACCESS BY INDEX ROWID TEST.PANU_BY 2 22 4 INDEX RANGE SCAN TEST.PANUBY_IDX1 2 3 TABLE ACCESS BY INDEX ROWID TEST.ITEM 1 20 2 INDEX RANGE SCAN TEST.ITEM_PK 1 1 TABLE ACCESS BY INDEX ROWID TEST.REV 1 39 4 INDEX RANGE SCAN TEST.REV_IDX1 2 3 TABLE ACCESS BY INDEX ROWID TEST.CHANGE 1 33 2 INDEX RANGE SCAN TEST.CHANGE_PK 1 1 TABLE ACCESS BY INDEX ROWID TEST.NODETABLE 1 23 2 INDEX UNIQUE SCAN TEST.NODETABLE_PK 1 1 TABLE ACCESS BY INDEX ROWID TEST.NODETABLE 1 23 2 INDEX UNIQUE SCAN TEST.NODETABLE_PK 1 1 TABLE ACCESS BY INDEX ROWID TEST.ITEM 1 22 4 INDEX RANGE SCAN TEST.ITEM_NUMBER_CLASS_IDX 1 3 INDEX RANGE SCAN TEST.PAGE_TWO_PK 1 2 TABLE ACCESS FULL TEST.PASSTRY 24 K 520 K 281 TABLE ACCESS FULL TEST.PASSTRY 24 K 520 K 281 TABLE ACCESS FULL TEST.PASSTRY 24 K 520 K 281 TABLE ACCESS FULL TEST.PASSTRY 24 K 520 K 281 TABLE ACCESS BY INDEX ROWID TEST.PAGE_TWO 1 12 4 INDEX RANGE SCAN TEST.PAGE_TWO_PK 1 3 TABLE ACCESS FULL TEST.PASSTRY 24 K 520 K 281 TABLE ACCESS FULL TEST.PASSTRY 24 K 520 K 281 TABLE ACCESS BY INDEX ROWID TEST.PAGE_THREE 1 12 3 INDEX RANGE SCAN TEST.PAGE_THREE_PK 1 2 TABLE ACCESS FULL TEST.PASSTRY 24 K 520 K 281 TABLE ACCESS FULL TEST.PASSTRY 24 K 520 K 281