Huge number of Buffer Gets in 10g
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
heap_table_1 = > 100 MB, 2 mln rows
heap_table_2 = > 47 rows
heap_table_3 => 47 rows
Excerpt from statistics :
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
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?
NOT EXIST condition on in-line view does not helps either.
Pablo (Paul) Berzukov
Author of Understanding Database Administration
available at amazon and other bookstores.
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.
Click Here to Expand Forum to Full Width