Huge number of Buffer Gets in 10g

[amasny]

Hi,

We have application (pl/sql) from external vendor . I'm DBA.
Sometimes our process hungs and execute very long and has to be killed.
When it hungs i notice huge number of buffer gets.
What may be the reason of such huge number of buffer gets (huge , i mean relative to our database) ? :

query :

SELECT 'some plain text '|| ' plain text 1 (towar: '||T.INDEKS||')' FROM global_temporary_table_1 D, heap_table_1 P, heap_table_2 T WHERE NOT EXISTS (SELECT 1 FROM heap_table_3 S WHERE S.TOWAR_ID=P.TOWAR_ID AND S.MAGAZYN_KOD=D.MAGAZYN_KOD AND S.CZYJE=D.CZYJE) AND P.DOKUMENT_ID=D.ID AND D.TYP_RODZAJ='ROZ' AND T.ID=P.TOWAR_ID AND ROWNUM=1

heap_table_1 = > 100 MB, 2 mln rows
heap_table_2 = > 47 rows
heap_table_3 => 47 rows


Excerpt from statistics :

Executions 1
Buffer Gets 496,484,715 496,484,715.00
Rows 0 0.00


And plan :

SELECT STATEMENT 57 (100)
1 COUNT STOPKEY
2 NESTED LOOPS 1 73 57 (2) 00:00:01
3 NESTED LOOPS ANTI 4 236 54 (2) 00:00:01
4 NESTED LOOPS 4 180 54 (2) 00:00:01
5 INDEX FAST FULL SCAN heap_table_1_UK 455 4550 2 (0) 00:00:01
6 TABLE ACCESS FULL global_temporary_table_1 1 35 0 (0)
7 INDEX UNIQUE SCAN heap_table_3_PK 1 14 0 (0)
8 TABLE ACCESS BY INDEX ROWID heap_table_2 1 14 1 (0) 00:00:01
9 INDEX UNIQUE SCAN heap_table_2_PK 1


Best Regards Arek Masny

[malay_biswal]

I doubt it's due to this index INDEX FAST FULL SCAN heap_table_1_UK.
Try to figure out if you can use any other index.
BTW when is the last time table analyzed?

[PAVB]

NOT EXIST condition on in-line view does not helps either.