script taking time to run on DB

[Roshan]

Oracle Database 10g
RHEL 6

Hi Erman,

We have executed a script(run_debtors.sh) since yesterday. It will run the 3 scripts below.

oracle_linux>cat /exec/products/oracle/ccontrol/run_debtors.sh
#! /usr/bin/ksh

sh /exec/products/oracle/ccontrol/create.sh (already executed)

sh /exec/products/oracle/ccontrol/insert_detail.sh

sh /exec/products/oracle/ccontrol/up_sum_dun1.sh

I see the script is taking time to complete.


Commit complete.


329667 rows updated.


Commit complete.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Jul 24 15:31:53 2018

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options


70562 rows created.


Commit complete.


354228 rows updated.


Commit complete.

i have checked v$session for username 'govinden' and found blocking sessions.

SQL> select sid,blocking_session,username,sql_id,event,machine,osuser,program,last_call_et from v$session where username='GOVINDEN';

       SID BLOCKING_SESSION USERNAME                       SQL_ID        EVENT                                                            MACHINE                                              OSUSER                          PROGRAM                                          LAST_CALL_ET
---------- ---------------- ------------------------------ ------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ------------------------------ ------------------------------------------------ ------------
       534                  GOVINDEN                       556gjzya0h0my latch: cache buffers chains                                      RHIS-CCBS-BILLDB-01                                  oracle                          sqlplus@RHIS-CCBS-BILLDB-01 (TNS V1-V3)                 37702
       535              534 GOVINDEN                       556gjzya0h0my enq: TX - row lock contention                                    RHIS-CCBS-BILLDB-01                                  oracle                          sqlplus@RHIS-CCBS-BILLDB-01 (TNS V1-V3)                 57197
       548              534 GOVINDEN                       556gjzya0h0my enq: TX - row lock contention                                    RHIS-CCBS-BILLDB-01                                  oracle                          sqlplus@RHIS-CCBS-BILLDB-01 (TNS V1-V3)                 20577


I have generated the execution plan for the SQL ID.

SQL> select * from table(dbms_xplan.display_cursor('556gjzya0h0my'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  556gjzya0h0my, child number 0
-------------------------------------
update cbs_sum_dun2 s set s.over_30 = (select sum(c.amt_due) from
dun_details c  where s.account_code_n = c.account_code --and
s.account_code_n = '1032028' --and trunc(c.INVOICE_DATE)  <= '30-JUN-17'
and substr(trans_num,1,4) = 'FINV' and (sysdate  - trunc(c.INVOICE_DATE)) >
30 and (sysdate - trunc(c.INVOICE_DATE)) <= 60) where s.LAST_BILL_DATE is
null

Plan hash value: 3951617410


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT    |              |       |       |    48M(100)|          |
|   1 |  UPDATE             | CBS_SUM_DUN2 |       |       |            |          |
|*  2 |   TABLE ACCESS FULL | CBS_SUM_DUN2 | 66342 |  1295K|  3974   (2)| 00:01:12 |
|   3 |   SORT AGGREGATE    |              |     1 |    53 |            |          |
|*  4 |    TABLE ACCESS FULL| DUN_DETAILS  |   313 | 16589 |   731   (1)| 00:00:14 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------

   2 - filter("S"."LAST_BILL_DATE" IS NULL)
   4 - filter(("C"."ACCOUNT_CODE"=TO_NUMBER(:B1) AND
              SUBSTR("TRANS_NUM",1,4)='FINV' AND SYSDATE@!-TRUNC(INTERNAL_FUNCTION("C"."IN
              VOICE_DATE"))>30 AND SYSDATE@!-TRUNC(INTERNAL_FUNCTION("C"."INVOICE_DATE"))<
              =60))

Note
-----
   - dynamic sampling used for this statement

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


34 rows selected.

Please advise how I can proceed.

Regards,

Roshan

[Roshan]

Hi Erman,

I have checked the script running containing the SQL ID which is blocking.

insert into cbs_sum_dun2
(account_code_n,
tot_inv_amt,
tot_tax_amt,
TOT_AMT_CLR,
tot_bills_due
)
select account_code,sum(invoice_amt),sum(inv_tax_amt),sum(inv_amt_clr),sum(amt_due)
from dun_details
--where account_code = '879751'
group by account_code;

commit;

update cbs_sum_dun2 s
set s.cnt =
(select
count(*)
from dun_details c
where s.account_code_n = c.account_code
group by c.account_code)
where s.LAST_BILL_DATE is null;

commit;


update cbs_sum_dun2 s
set s.over_30 =
(select sum(c.amt_due) from dun_details c
 where s.account_code_n = c.account_code
--and s.account_code_n = '1032028'
--and trunc(c.INVOICE_DATE)  <= '30-JUN-17'
and substr(trans_num,1,4) = 'FINV'
and (sysdate  - trunc(c.INVOICE_DATE)) > 30 and (sysdate - trunc(c.INVOICE_DATE)) <= 60)
where s.LAST_BILL_DATE is null
/

commit;

update cbs_sum_dun2 s
set s.over_60 =
(select sum(c.amt_due) from dun_details c
 where s.account_code_n = c.account_code
--and s.account_code_n = '1032028'
--and ( (trunc(c.INVOICE_DATE)  <= '01-JUN-17')
and substr(trans_num,1,4) = 'FINV'
and (sysdate  - trunc(c.INVOICE_DATE)) > 60 and (sysdate - trunc(c.INVOICE_DATE)) <= 90)
where s.LAST_BILL_DATE is null

As shown above, it is inserting into table 'cbs_sum_dun2'. It is then doing 3 consecutive updates.

[ErmanArslansOracleBlog]

Your blocked sessions are waiting for blocking session with SID 534.
SID 534 is doing an update and its wait is  latch: cache buffers chains.

latch: cache buffers chains" contention is typically encountered because SQL statements read more buffers than they need to and multiple sessions are waiting to read the same block.          

So concantrate on that update.. concantrate on the where condition..