If you stash (probably insert append, then after the subsequent read truncate)
into a table the rowid of all the rows you are about to delete (using the same
predicate), then do the delete, then commit, that will give you the list of the
candidate blocks. If you read from the table you deleted from where rowid in
(select rowid from the new table) from a different node on your Exadata, you’re
probably going to get that in sga. IF not, you can pl/sql loop them one block
at a time.
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Pap
Sent: Thursday, July 29, 2021 2:34 PM
To: Lothar Flatz
Cc: Oracle L
Subject: Re: Insert Query issue
Thank you Lothar.
It appears to be something matching to delayed block cleanout effect in which
post DML sometimes we have to perform a segment scan so that the commit scn can
be updated for all the blocks and for that sometimes we have to do it using
manual stats gathering or forced segment full scan to pass all the data blocks
through buffer cache. But for that we used to see one stats i.e. 'transaction
table consistent read undo records applied' on the SELECT query execution.
So is this tidying up of block post delete something similar as above and we
can get that verified and confirmed from some session statistics from
v$sesstat while the insert is running at run time? Also I am thinking what can
be done so as to tidying up the blocks after delete, if a force segment full
scan or stats gather post deletion can anyway help, so as to not impact the
insert performance?
Betreff:
Re: Insert Query issue
Datum:
Thu, 29 Jul 2021 19:59:25 +0200
Von:
Lothar Flatz <mailto:l.flatz@xxxxxxxxxx> <l.flatz@xxxxxxxxxx>
An:
oracle-l@xxxxxxxxxxxxx
Hi,
there reason should be that after a delete rows are marked as technical
deleted, but the block is not yet tidied up. (I suppose that would mean e.g.
that other rows are not yet shifted for contiguous space etc.)
The tidying is done on the next insert, which therefore will be substantially
slower for once.
I heard this explanation long ago , but suppose it still holds.
My whole understanding of this is a bit vague.
I guess Jonathan could fill in some details here.
Regards
Lothar
Am 29.07.2021 um 18:10 schrieb Pap:
This legacy database is on version 11.2.0.4 of oracle Exadata.
We are having below conventional insert query introduced and its a bulk insert
happening with an arraysize of ~352 from informatica. And the response time was
~3millisec/execution i.e. for one chunk or ~352 rows. But we noticed suddenly
the response time becomes tripled(~8-9millisec/execution) for the same set of
rows/array size insert. No changes done to the underlying table structure.
During digging into the AWR views we found that the increase in response time
for this insert query started after we introduced the delete script to purge
data from this table. The delete is also happening in bulk fashion with an
arraysize of 100K through plsql procedure and its deleting data with
CRE_TS<sysdate-3. And it's clear from dba_hist_sqlstat that it's the
cpu_time_delta and IOWAIT_DELTA which increased thus making the
ELAPSED_TIME_delta go up by three times for the INSERT query. No such increase
in BUFFER_GETS_delta observed. But one thing we notice is that the DELETE is
not running at exactly the same time as the insert/data load script runs. So
howcome this delete is impacting the INSERT query? Is this that as delete is
making some blocks empty so that now the INSERT has to now see through all to
find the empty blocks before insert and thus spending more time?
The table is ~215GB in size and is not partitioned holding ~1.7billion rows.
There exists only a primary key composite index on two number data type
columns. With Blevel-3 , and having index size ~65GB with ~7.3million leaf
blocks.
Below is the AWR plan for the INSERT and DELETE queries.
INSERT INTO TAB1(TAID,TID,AID,APP_NM,CRE_TS) VALUES ( :1, :2, :3,:4, :5)
-------------------------------------------------
| Id | Operation | Name | Cost |
-------------------------------------------------
| 0 | INSERT STATEMENT | | 1 |
| 1 | LOAD TABLE CONVENTIONAL | | |
-------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - INS$1
Note
-----
- cpu costing is off (consider enabling it)
DELETE FROM USER1.TAB1 WHERE ROWID = :1
Plan hash value: 2488984540
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | | |
1 (100)| |
| 1 | DELETE | TAB1 | | |
| |
| 2 | TABLE ACCESS BY USER ROWID| TAB1 | 1 | 25 |
1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - DEL$1
2 - DEL$1 / TAB1@DEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"DEL$1")
ROWID(@"DEL$1" "TAB1"@"DEL$1")
END_OUTLINE_DATA
*/
