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_at_gmail.com> 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
>
>
>
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Feb 03 2021 - 22:05:06 CET
