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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Hameed, Amir <Amir.Hameed@xxxxxxxxx>
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
--
//www.freelists.org/webpage/oracle-l
