Thanks for the question, vinesh.
Asked: October 22, 2021 - 8:13 am UTC
Last updated: October 27, 2021 - 2:14 am UTC
Viewed 100+ times
let us consider our normal scott schema for this concern.
Let us consider emp table.
REM INSERTING into emp
SET DEFINE OFF;
Insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','CLERK',7902,to_date('17-12-80 00:00:00','DD-MM-RR HH24:MI:SS'),800,null,20);
We have two concerns here.
In few scenarios ,Insert is taking too long in our prod DB . The insert statement in our Prod is different than the one provided above, though the syntax of insert stmt is same.(I meant to say that it's a single record insert stmt.)
We would like to know what could be the possible reasons for the insert to take too long time.
In few scenarios, the SQL commit is taking lot of time.
We would like to know what could be the possible reasons for the commit to take too long time.
Any of your suggestions are most welcome !!
Note: Edited for 2 typos.
Thanks & Regards,
and Connor said...
Concern 1; We would like to know what could be the possible reasons for the insert to take too long time.
- exec dbms_monitor.session_trace_enable(waits=>true)
- run your insert
- exec dbms_monitor.session_trace_disable
and check the trace file...it shows where all the time is lost. Common possible causes:
- excessive indexes
Concern 2; In few scenarios, the SQL commit is taking lot of time.
Same thing - trace it. But a slow commit is very rare because we write redo asynchronously. Common causes
- poor IO infrastructure
- machine CPU max'd out
- on-commit materialized views being refreshed
but a trace reveals the cause