22gb seems like a lot of tracking.
Could it be a backup stopped running causing changed blocks accumulation ?
Albert
On Thu, Feb 4, 2021, 1:54 AM Lok P <loknath.73@xxxxxxxxx> wrote:
One thing we noticed from the wait event pattern , there was a very low
amount of those waits(block change tracking buffer space) in the past . but
gradually it started increasing and now it's enough to impact the
application queries. How to debug/fix this one? or it can be related to the
slowness of the underlying disk on which the BCT file lies?
The BCT file size is 22GB.
On Thu, Feb 4, 2021 at 2:35 AM Lok P <loknath.73@xxxxxxxxx> wrote:
Below is a sample batch insert query with its sql monitor and it shows
almost all the time is in OTHERs wait which is nothing but "block change
tracking buffer space".
SQL Text
------------------------------
INSERT INTO TABLE1 (.....) VALUES(:1 , :2 , :3 , :4 , :5 ,
:6 , :7 , :8 , :9 , :10 , :11 , :12 , :13 , :14 , :15 , :16 , :17 , :18 ,
:19 , :20 , :21 , :22 , :23 , :24 , :25 , :26 , :27 , :28 , :29 , :30 , :31
, :32 , :33 , :34 , :35 , :36 , :37 , :38 , :39 , :40 , :41 , :42 , :43 ,
:44 , :45 , :46 ,:47 , :48 , :49 , :50 , :51 , :52 , :53 , :54 , :55 , :56
, :57 , :58 , :59 , :60 , :61 , :62 , :63 , :64 , :65 , :66 , :67 , :68 ,
:69 , :70 , :71 ,:72 ,:73 ,:74 ,:75 )
Global Information
------------------------------
Status : DONE
Instance ID : 2
SQL Execution ID : 36104551
Execution Started : 02/03/2021 15:56:51
First Refresh Time : 02/03/2021 15:56:55
Last Refresh Time : 02/03/2021 15:58:43
Duration : 112s
Module/Action : JDBC Thin Client/-
Program : JDBC Thin Client
Global Stats
============================================================================================
| Elapsed | Cpu | IO | Concurrency | Cluster | Other |
Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) |
Gets | Reqs | Bytes |
============================================================================================
| 112 | 0.19 | 1.23 | 0.00 | 0.00 | 111 |
17090 | 555 | 4MB |
============================================================================================
SQL Plan Monitoring Details (Plan Hash Value=0)
==============================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time |
Start | Execs | Rows | Read | Read | Activity | Activity Detail |
| | | | (Estim) | | Active(s) |
Active | | (Actual) | Reqs | Bytes | (%) | (# samples) |
==============================================================================================================================================
| 0 | INSERT STATEMENT | | | | |
| 1 | | | | | |
| 1 | LOAD TABLE CONVENTIONAL | | | | 1 |
+112 | 1 | 0 | 54 | 432KB | | |
==============================================================================================================================================
On Thu, Feb 4, 2021 at 2:25 AM Lok P <loknath.73@xxxxxxxxx> wrote:
Hi , We are on version 11.2.0.4 Oracle database and suddenly starting
the last 5-10 days we are seeing many queries(mostly Inserts) are running
longer and the waits it's showing as "block change tracking buffer space" .
and it's going worse day by day. We do have BCT ON on this database. So
wondering why these waits were not there previously as because we have the
database kept on BCT enabled since long back, but suddenly appeared since
the last few days and impacting application queries?
Even simple insert queries like insert into table(c1,c2, c3) values(:1,
:2, :3) (which are executing with batch size of ~1000 ) are suffering. What
can be the cause for this?
Regards
Lok
