Huge number of Buffer Gets in 10g
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Huge number of Buffer Gets in 10g

  1. #1
    Join Date
    Feb 2008
    Posts
    31

    Huge number of Buffer Gets in 10g

    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

  2. #2
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    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?
    http://www.perf-engg.com
    A performance engineering forum

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width