Community

Allow Oracle sequence's default cache value to be increased

219
0
12-06-2016 10:17 AM
Status: Open
Labels (1)
Labels
JohnKing4
by
New Contributor II

While researching some performance issues in our Oracle database, I found many "select nextval" queries showing up with extremely high parse counts.  Looking at the sequences, I noticed the default value was set to Oracle's default of 20 although the nextval was being fetched 100s of thousands of times.  I tried changing the default cache value from the backend, but during processing ESRI seems to recreate the sequences causing the value to revert back to 20.  Here are some examples of ones I tried to change that were reverted during processing:

 

ALTER SEQUENCE I1385 CACHE 1000;
ALTER SEQUENCE I1386 CACHE 1000;
ALTER SEQUENCE I1392 CACHE 1000;
ALTER SEQUENCE I1419 CACHE 1000;
ALTER SEQUENCE R812 CACHE 1000;
ALTER SEQUENCE R1395 CACHE 1000;
ALTER SEQUENCE R1396 CACHE 1000;
ALTER SEQUENCE R1402 CACHE 1000;
ALTER SEQUENCE R1449 CACHE 1000;
ALTER SEQUENCE R6758 CACHE 1000;
ALTER SEQUENCE R6759 CACHE 1000;
ALTER SEQUENCE R6760 CACHE 1000;
ALTER SEQUENCE R6761 CACHE 1000;
ALTER SEQUENCE R6762 CACHE 1000;
ALTER SEQUENCE R6763 CACHE 1000;
ALTER SEQUENCE R6764 CACHE 1000;
ALTER SEQUENCE R7841 CACHE 1000;
ALTER SEQUENCE R7844 CACHE 1000;
ALTER SEQUENCE R7848 CACHE 1000;
ALTER SEQUENCE R8204 CACHE 1000;
ALTER SEQUENCE R8603 CACHE 1000;

 

I inquired about this issue via support, but was told to create this enhancement request.  I suppose there are multiple ways to approach this.  You could allow us to maintain our preferred cache value somewhere, or when recreating the sequence you could just use the existing cache value so that any backend changes are retained.