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) ? :
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
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.