script taking time to run on DB
Posted by Roshan on Jul 25, 2018; 8:42am
URL: http://erman-arslan-s-oracle-forum.124.s1.nabble.com/script-taking-time-to-run-on-DB-tp6492.html
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