RE: Insert Query issue
Date: Fri, 30 Jul 2021 13:07:11 -0400
Message-ID: <010c01d78565$57f3b720$07db2560$_at_rsiz.com>
What is your current PK?
Does a unique row have exactly one create timestamp (and as the name implies) is that timestamp column value set at the time the row is inserted?
Can you make the insert routine insert rows in the attribute order of the PK (like attribute clustering in later releases)? That is, do you own that code?
IF you ruled out delayed block clean out, the next odds on favorite is the index now has to read more blocks to get the same number of rows referenced because blocks became insert candidates from the deletions.
There are a couple ways (at least) to prevent that.
- You can set your percent used so blocks don’t become free full and come off the available for insert list or bitmap, they don’t go back on until they are empty
- You can set your inserts to append, so all new rows go high. That *might* make your size get bigger, especially if you insert little chunks of new rows leaving many half empty rows. If your creation order doesn’t match your index reference order, that will also get you a bad actual cluster factor. IF you are inserting big chunks of data so that many blocks are completely full and just the last is part filled, that’s good. Even better if you are inserting rows in the most important index order for queries. IF making one index perfect for actual cluster factor makes any other important indexes have a lousy actual cluster factor that’s not great.
(This is theoretically possible and pretty easy to create in a lab demonstration.
[Very likely JL has a demo of this on his site, creating a dataset where one column is ordered and another column is distributed by mod or something else non-random in an anti-pattern to the first column, and then index them both.]
I have seen significant degradation outside the lab exactly zero times. For the contemplated and clearly possible degradation of one index actual cluster factor from making another perfect, the index keys would have to be anti-correlated significantly. Usually what happens is you get one perfect and the remaining n-1 indexes are about the same average cluster factors as doing nothing to the ordered index.)
Does your creation timestamp correlate well with your queries? That is, are all the actual queries by other predicates usually on one day? I’m actually not sure it matters much if you’re only keeping 3 days. Three local index scans for small numbers of rows probably aren’t going to matter much. If you had many partitions to scan (where many is a slippery slope), that could become an issue. Three range scan lookups of an index is probably not significant unless your total number of rows returned is very small. And then we’re probably splitting hairs on the query time. If the number of rows per partition of the index is large, then the overhead of reading 3 btrees instead of 1 is probably also insignificant.
Your mileage may vary, so yeah, you should test unless you can analytically convince yourself that your partitioned form is not going to cause query problems.
But *probably* that is what you should do. You don’t even have to move the current stuff if you can tolerate 3 days for the new partitioning added and going forward plus your exchange to empty is good enough. Just stop purging and that first exchange out will be the three day lump, leaving you with 3 clean partitions remaining.
IF your partitions per day are big enough compared to your queries to justify subpartitioning and your read queries without change seem like they would do partition pruning to a single subpartition, then consider doing that. Depending on whether or not your queries have the CBO do a partition pruning plan, that can vary from not good to something looking like a magic trick. Likewise if small positive bloom filtering or zonemaps work in your favor. (Zonemaps with attribute clustering can be sweet.) All things to consider, but then I remember you’re on an old release.
Do be careful not to get into the range of Compulsive Tuning Disorder (CTD) (Thanks for naming this syndrome, Gaja Krishna Vaidaynatha) .
Set your goal. Open up thinking about the best way, reduce that to ways that are achievable, test the implementation, and then if that meets your goal, put it away until and unless it is a familiar test case for overall comprehensive revision templates. In that last case, tune the heck out of it, so the comprehensive revisions are as good as possible and are done ONCE for a long time.
I don’t think CTD is likely in your case because it seems as if someone complained about the new slower query times after you introduced the deletes.
mwf
From: Pap [mailto:oracle.developer35_at_gmail.com]
Sent: Friday, July 30, 2021 7:13 AM
To: Mark W. Farnham
Cc: Lothar Flatz; Oracle L
Subject: Re: Insert Query issue
Thank you Mark.
I was wondering if it's a good idea to partition this table just to help it purge in a cleaner fashion. As because, if we just partition it to make our purging better we also have to see the impact on the read queries. To have the table partitioned with the delete key(i.e. create_timestamp), the primary key also has to include the delete key in it or else it will not allow it to make it a local index. And in case of global index we will have to go through index invalidation/rebuild stuff both in case of drop or partition exchange etc. And also the read queries now have to go through multiple local index segments scan vs the current one segment scan.
I was trying to see/prove if it's really delayed block cleanout impacting INSERT, So i tried to collect the stats from v$sesstat for two different executions/data loads for close to ~10minutes duration though it's not exact(i think i monitored the bad execution for a bit longer duration). And as v$sesstat accumulates the statistics so I collected the delta value for each run. As per application, the first execution was running slow and the next one was running with better response. Attached are the stats for the same. But surprisingly I see the delta value of statistics 'transaction table consistent read - Undo records applied' is zero in both the cases. So I'm wondering if there are some other phenomena(other than delayed block cleanout) which are happening here and how can we fix? In both the cases I see from ASH the wait event (cell single block physical read) was highest logged against the table followed by the PK index.
On Fri, Jul 30, 2021 at 3:10 AM Mark W. Farnham <mwf_at_rsiz.com> wrote:
That’s right.
But what you probably *should* do is partition the table by your delete key. And if you can change the insert into append, then you just partition exchange out and drop the unwanted rows with nary a delete seen. That also gives you a cheap ability to archive the swapped out partition to a table to back up as a stand alone period of time.
Good luck. IF Lothar’s suggestion of delayed block cleanout is correct, either of these things should fix it. IF the deleted space being used dramatically increasing the number of blocks you need to read via an indexed access path by fubaring the actual cluster factor, the partitioning fixes that.
From: Pap [mailto:oracle.developer35_at_gmail.com]
Sent: Thursday, July 29, 2021 3:49 PM
Thanks Mark. If I get it correct , you are suggesting a method to scan the blocks in the buffer cache which are getting deleted. But considering we are going to hold ~3days worth of data at any point in time, I was thinking if it's easy one to just scan or gather stats with auto sample size after delete to get the blocks cleaned. but before that is it the same effect i.e. delayed block cleanout which we are suffering from here? I thought Lothar might be pointing to some other similar effect but not exactly delayed block cleanout. Please correct me if I'm wrong.
On Fri, Jul 30, 2021 at 12:41 AM Mark W. Farnham <mwf_at_rsiz.com> wrote:
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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Pap
Sent: Thursday, July 29, 2021 2:34 PM
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_at_bluewin.ch> <l.flatz_at_bluewin.ch>
An:
oracle-l_at_freelists.org
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)
To: Mark W. Farnham
Cc: Lothar Flatz; Oracle L
Subject: Re: Insert Query issue
To: Lothar Flatz
Cc: Oracle L
Subject: Re: Insert Query issue
| 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 Fri Jul 30 2021 - 19:07:11 CEST