Home -> Mailing Lists -> Oracle-L -> RE: "free buffer waits" when inserting into a GTT

RE: "free buffer waits" when inserting into a GTT

From: Hameed, Amir <Amir.Hameed_at_xerox.com>
Date: Wed, 7 Jun 2017 00:32:23 +0000
Message-ID: <AF02C941134B1A4AB5F61A726D08DCED20239D37_at_USA7109MB012.na.xerox.net> 






Thanks Jonathan.


Wouldn't those reads be "direct path reads" because the table is a GTT?




Thanks


Amir


-----Original Message-----




From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis
Sent: Tuesday, June 06, 2017 8:23 PM


To: 'ORACLE-L' <oracle-l_at_freelists.org>
Subject: Re: "free buffer waits" when inserting into a GTT




If you're going to insert data into blocks you need those block in the buffer cache unless you're going to do direct path inserts (which your code doesn't).



If there are no free buffers you have to make some free which may mean writing some dirty blocks to disc or waiting for some sessions to unpin some clean block s before the current content can be discarded and the buffer re-used to "new" a block in memory.



The wait isn't about competition for the temporary table block, it's about waiting for space in the buffer cache so that an image of the temporary table block can be created.



Regards


Jonathan Lewis






From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Hameed, Amir <Amir.Hameed_at_xerox.com>
Sent: 07 June 2017 01:07:19


To: 'ORACLE-L'


Subject: "free buffer waits" when inserting into a GTT



Hi,


I am trying to understand what would cause "free buffer waits" for blocks from temporary data files when inserting into a GTT. The SQL statement is shown below:



INSERT INTO QP_NPREQ_LINE_ATTRS_TMP (LINE_INDEX,

  LINE_DETAIL_INDEX, ATTRIBUTE_LEVEL, ATTRIBUTE_TYPE, LIST_HEADER_ID,

  LIST_LINE_ID, CONTEXT, ATTRIBUTE, VALUE_FROM, SETUP_VALUE_FROM,

  SETUP_VALUE_TO, GROUPING_NUMBER, COMPARISON_OPERATOR_TYPE_CODE,


  VALIDATED_FLAG, APPLIED_FLAG, PRICING_STATUS_CODE, PRICING_STATUS_TEXT,
  QUALIFIER_PRECEDENCE, DATATYPE, PRICING_ATTR_FLAG, QUALIFIER_TYPE,
  PRODUCT_UOM_CODE, EXCLUDER_FLAG, PRICING_PHASE_ID, INCOMPATABILITY_GRP_CODE,


   LINE_DETAIL_TYPE_CODE, MODIFIER_LEVEL_CODE, PRIMARY_UOM_FLAG ) VALUES

(:B1 , :B2 , :B3 , :B4 , :B5 , :B6 , :B7 , :B8 , :B9 , :B10 , :B11 , :B12 ,
  :B13 , :B14 , :B15 , :B16 , :B17 , :B18 , :B19 , :B20 , :B21 , :B22 , :B23 ,
   :B24 , :B25 , :B26 , :B27 , :B28 )




WAIT #140724984204080: nam='free buffer waits' ela= 10688 file#=1037 block#=109265 set-id#=93 obj#=1457973 tim=18360969167241 WAIT #140724984204080: nam='free buffer waits' ela= 10525 file#=1037 block#=109292 set-id#=93 obj#=1457973 tim=18360969190136 WAIT #140724984204080: nam='free buffer waits' ela= 10052 file#=1037 block#=11273 set-id#=93 obj#=1457973 tim=18360969216037 WAIT #140724984204080: nam='free buffer waits' ela= 10669 file#=1037 block#=11300 set-id#=93 obj#=1457973 tim=18360969243133 WAIT #140724984204080: nam='free buffer waits' ela= 11009 file#=1037 block#=11327 set-id#=93 obj#=1457973 tim=18360969255133 WAIT #140724984204080: nam='free buffer waits' ela= 10140 file#=1037 block#=11354 set-id#=93 obj#=1457973 tim=18360969269134 WAIT #140724984204080: nam='free buffer waits' ela= 1338 file#=1037 block#=11381 set-id#=93 obj#=1457973 tim=18360969272586




Thank you,


Amir


--



http://www.freelists.org/webpage/oracle-l




--



http://www.freelists.org/webpage/oracle-l
Received on Wed Jun 07 2017 - 02:32:23 CEST












Original text of this message