Insert Query issue
Date: Thu, 29 Jul 2021 21:40:32 +0530
Message-ID: <CAEjw_fiZE-mXLovF56+fJTKy2Kj7ceJ6RCk7wXB5eLY6+w876g_at_mail.gmail.com>
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_at_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(_at_"DEL$1") ROWID(_at_"DEL$1" "TAB1"_at_"DEL$1") END_OUTLINE_DATA
*/
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jul 29 2021 - 18:10:32 CEST