Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, vinesh.

Asked: October 22, 2021 - 8:13 am UTC

Last updated: October 27, 2021 - 2:14 am UTC

Version: 19c

Viewed 1000+ times

You Asked

Team,

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.

Concern 1;

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.


Concern 2;

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,
Vinesh


and Connor said...

Concern 1; We would like to know what could be the possible reasons for the insert to take too long time.

Trace it.

- 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
- triggers

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

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thank You Note

vinesh, October 26, 2021 - 2:59 pm UTC

Thanks for your quick responses.
Connor McDonald
October 27, 2021 - 2:14 am UTC

glad we could help

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.