1

Topic: Oracle 12c, sequence, library cache: mutex X

There is a basis of Oracle 12.1.0.2.0
In basis some tables to which there is an intensive insertion of the data (as a group about 3 billion lines a day) are created
In each table the NUMBER-field which on the trigger is filled from one sequence (1 sequence on some tables) is created.
In a top of waitings are visible "library cache: mutex X" on requests of obtaining of values from sequence (select <sequence_name>.nextval from dual).
CACHE in sequence it is installed (100000).
Prompt, whether it is possible to reduce somehow waitings "library cache: mutex X" at frequent reversal to sequence, or it is an expected situation and somehow to affect on it does not turn out?
Tried to apply DBMS_SHARED_POOL.KEEP to sequence - results did not bring (waitings did not decrease).
DBMS_SHARED_POOL.MARKHOT To sequence it was not possible to apply - object type not supported.

2

Re: Oracle 12c, sequence, library cache: mutex X

Kompromiss;
And if without the trigger? Directly in INSERT statement to register <sequence_name>.nextval?

3

Re: Oracle 12c, sequence, library cache: mutex X

efendi;
And can explain logic? Why you suppose, what trigger can cause waitings?

4

Re: Oracle 12c, sequence, library cache: mutex X

Kompromiss;
How many sessions simultaneously fill tables?

5

Re: Oracle 12c, sequence, library cache: mutex X

XMLer;
In total 300 (a different amount for different tables).

6

Re: Oracle 12c, sequence, library cache: mutex X

Kompromiss wrote:

efendi;
And can explain logic? Why you suppose, what trigger can cause waitings?

c the trigger and without, unambiguously difference is (in a case if the trigger is necessary only for autonumber)
.....
stax

7

Re: Oracle 12c, sequence, library cache: mutex X

efendi wrote:

Kompromiss;
And if without the trigger? Directly in INSERT statement to register <sequence_name>.nextval?

Well here, . Truth there an example on JAVA, but does not change an essence.
It is loaded given

8

Re: Oracle 12c, sequence, library cache: mutex X

Kompromiss wrote:

on the trigger

Kompromiss wrote:

12.1.0.2.0

you can default simply make for a field of type

create table tseq (id number default seq.nextval...

https://docs.oracle.com/database/121/NE … URENO09966

Kompromiss wrote:

it is filled from one sequence (1 sequence on some tables

for a solution of a problem you should different  use for different tables.

Kompromiss wrote:

In total 300

they though the long? Not podkljuchajutsja/are disconnected after each insertion of several lines?

9

Re: Oracle 12c, sequence, library cache: mutex X

efendi wrote:

Well here, . Truth there an example on JAVA, but does not change an essence.
It is loaded given

Thanks, esteemed about switching of contexts SQL and PL/SQL.
Transfer <seq>.nextval from the trigger in insert completely solved a problem, waitings "library cache: mutex X" in a top is not present more.

xtender wrote:

you can default simply make for a field of type

create table tseq (id number default seq.nextval...

It agree, it is possible and so.

xtender wrote:

for a solution of a problem you should different  use for different tables.

No, the task consisted in usage of one  for all tables.

xtender wrote:

they though the long? Not podkljuchajutsja/are disconnected after each insertion of several lines?

Everyone interposes on the average till 10.000 lines then completes operation.
All thanks, the problem is solved, the subject can be closed.

10

Re: Oracle 12c, sequence, library cache: mutex X

Kompromiss wrote:

Prompt, whether it is possible to reduce somehow waitings "library cache: mutex X" at frequent reversal to sequence

And why you solved, what exactly in it the reason of your problems?
Look that write here
Troubleshooting ' library cache: mutex X ' Waits. (Doc ID 1357946.1)