Thanks Jonatan for the clarification.
The table is created with ON COMMIT DELETE ROWS and therefore, we start with an
empty segment.
I have a follow-up question. Under what circumstances a GTT will do "direct
path reads/write" apart from when reading and writing for sorting?
Thanks
-----Original Message-----
From: Jonathan Lewis [mailto:jonathan@xxxxxxxxxxxxxxxxxx] ;
Sent: Wednesday, June 07, 2017 4:39 AM
To: Hameed, Amir <Amir.Hameed@xxxxxxxxx>; 'ORACLE-L' <oracle-l@xxxxxxxxxxxxx>
Subject: Re: "free buffer waits" when inserting into a GTT
No.
Just because a segment is in the temporary tablespace that doesn't mean it is
subject only to direct path reads; in most cases a segment representing a GTT
is likely to be treated (in terms of reads and writes) just like any other data
segment.
Moreover, I'd guess that you're inserting data into an empty segment, rather
than trying to reuse the space produce by deleting previously inserted data (I
say that because that's the common use case for GTTs - insert, report, forget);
which means you don't have any blocks (apart from space management blocks,
eventually) on disc to read into memory to do the insert - Oracle can just
format new blocks for the object in memory - that's what the comment about
'using a buffer to "new" a block in memory' was about.
Regards
Jonathan Lewis
________________________________________
From: Hameed, Amir <Amir.Hameed@xxxxxxxxx>
Sent: 07 June 2017 01:32:23
To: Jonathan Lewis; 'ORACLE-L'
Subject: RE: "free buffer waits" when inserting into a GTT
Thanks Jonathan.
Wouldn't those reads be "direct path reads" because the table is a GTT?
Thanks
Amir
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Jonathan Lewis
Sent: Tuesday, June 06, 2017 8:23 PM
To: 'ORACLE-L' <oracle-l@xxxxxxxxxxxxx>
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@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
--
//www.freelists.org/webpage/oracle-l
