프린트 하기

내맘대로긍정이 알려주는

Oracle 23ai 신기능
무료 세미나 발표자료

다운로드 trending_flat

OS환경 : Oracle Linux 6.8 (64bit)


DB 환경 : Oracle Database 11.2.0.4


에러 : IO 오류 : Got minus one from a read call, connect lapse 18ms., Authentication lapse 0 ms.

sqldeveloper 접속 시 발생한 에러 메세지


해결 방법 : processes 파라미터 값 증설 또는 was connection pool 확인

v$resource_limit 뷰 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
SQL> set lines 200
SQL> set pages 1000
SQL> select * from v$resource_limit;
 
RESOURCE_NAME               CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION    LIMIT_VALUE
------------------------------ ------------------- --------------- -------------------- --------------------
processes                    148        150      150               150
sessions                    130        166      247               247
enqueue_locks                    22        33     3070              3070
enqueue_resources                20        45     1304         UNLIMITED
ges_procs                     0         0        0             0
ges_ress                     0         0        0         UNLIMITED
ges_locks                     0         0        0         UNLIMITED
ges_cache_ress                     0         0        0         UNLIMITED
ges_reg_msgs                     0         0        0         UNLIMITED
ges_big_msgs                     0         0        0         UNLIMITED
ges_rsv_msgs                     0         0        0             0
gcs_resources                     0         0  UNLIMITED         UNLIMITED
gcs_shadows                     0         0  UNLIMITED         UNLIMITED
smartio_overhead_memory              0         71704        0         UNLIMITED
smartio_buffer_memory                 0         0        0         UNLIMITED
smartio_metadata_memory              0         0        0         UNLIMITED
smartio_sessions                 0         1        0         UNLIMITED
dml_locks                     0        23     1084         UNLIMITED
temporary_table_locks                 0        37  UNLIMITED         UNLIMITED
transactions                     0         3      271         UNLIMITED
branches                     0         0      271         UNLIMITED
cmtcallbk                     0         2      271         UNLIMITED
max_rollback_segments                11        11      271             65535
sort_segment_locks                 0         5  UNLIMITED         UNLIMITED
k2q_locks                     0         0      494         UNLIMITED
max_shared_servers                 0         0  UNLIMITED         UNLIMITED
parallel_max_servers                 0         0       40              3600
 
27 rows selected.

processes 의 LIMIT_VALUE(최대치)가 150인데 MAX_UTILIZATION(Instance가 시작된 이후 최대 사용된 수)도 150임

CURRENT_UTILIZATION (현재값)은 148


processes 파라미터의 값이 150이 최대값인데 그 이상을 넘은 세션이 붙으려고 해서 발생한 오류로

processes 파라미터값을 늘려주면됨


spfile 사용확인

1
2
3
4
5
SQL> show parameter spfile
 
NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
spfile                     string     /oracle/app/oracle/product/11.2.0/db_1/dbs/spfileORCL11.ora



process 파라미터 값 변경

1
2
3
SQL> alter system set processes=200 scope=spfile;
 
System altered.



재기동

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
 
Total System Global Area 1553305600 bytes
Fixed Size            2253544 bytes
Variable Size         1174408472 bytes
Database Buffers      369098752 bytes
Redo Buffers            7544832 bytes
Database mounted.
Database opened.



다시 v$resource_limit 조회

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
SQL> set lines 200
SQL> set pages 1000
SQL> select * from v$resource_limit;
 
RESOURCE_NAME               CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION    LIMIT_VALUE
------------------------------ ------------------- --------------- -------------------- --------------------
processes                    148        150      200               200
sessions                    130        166      322               322
enqueue_locks                    21        24     3920              3920
enqueue_resources                20        20     1636         UNLIMITED
ges_procs                     0         0        0             0
ges_ress                     0         0        0         UNLIMITED
ges_locks                     0         0        0         UNLIMITED
ges_cache_ress                     0         0        0         UNLIMITED
ges_reg_msgs                     0         0        0         UNLIMITED
ges_big_msgs                     0         0        0         UNLIMITED
ges_rsv_msgs                     0         0        0             0
gcs_resources                     0         0  UNLIMITED         UNLIMITED
gcs_shadows                     0         0  UNLIMITED         UNLIMITED
smartio_overhead_memory              0         0        0         UNLIMITED
smartio_buffer_memory                 0         0        0         UNLIMITED
smartio_metadata_memory              0         0        0         UNLIMITED
smartio_sessions                 0         0        0         UNLIMITED
dml_locks                     0         0     1416         UNLIMITED
temporary_table_locks                 0         0  UNLIMITED         UNLIMITED
transactions                     0         0      354         UNLIMITED
branches                     0         0      354         UNLIMITED
cmtcallbk                     0         0      354         UNLIMITED
max_rollback_segments                11        11      354             65535
sort_segment_locks                 0         1  UNLIMITED         UNLIMITED
k2q_locks                     0         0      644         UNLIMITED
max_shared_servers                 0         0  UNLIMITED         UNLIMITED
parallel_max_servers                 0         0       40              3600
 
27 rows selected.

SESSIONS 파라미터의 값은 따로 지정해주지 않았는데 증가함

SESSIONS 파라미터의 값은 자동으로 [(PROCESSES * 1.5) + 22] = 322 으로 설됨

ENQUEUE_RESOURCES 와 TRANSACTIONS도 같이 증가했는데 이 두개의 기본값은 SESSIONS 값에 의해 정해짐

ENQUEUE_RESOURCES 값을 구하는 정확한 공식은 찾지 못함

하지만 enqueue_resources는 Oracle Database 10g 릴리스 2 (10.2)부터 사용되지 않는다고함

(Note that ENQUEUE_RESOURCES is obsolete as of Oracle Database 10g release 2 (10.2).)



SESSIONS 값 버전별 참고자료

- 11g 이전

Sessions=(1.1 * processes) + 5 

Transactions = (1.1 * Sessions)

- 11g 이후 

Sessions=(1.5 * processes) + 22 (-> 동시 유저 및 백그라운드 프로세스, 대략 10%정도의 반복적인 세션을 고려한 값) 

Transactions = (1.1 * Sessions)

- 12c  ~ 19c

Sessions=(1.5 * processes) + 22 (-> 동시 유저 및 백그라운드 프로세스, 대략 10%정도의 반복적인 세션을 고려한 값) 

Transactions = (1.1 * Sessions)

최대값 설정은 1 에서 65536 까지 가능함



원인 : process 파라미터의 값이 150이 최대값인데 그 이상으로 접속해 발생한 오류

오라클 process 가 이미 큰 값인데 문제가 발생했다면 was connection pool도 확인해봐야함



참조 : 

https://pat98.tistory.com/905

http://www.gurubee.net/article/59711

How to calculate the proper value from processes, sessions, and transactions (Doc ID 1682295.1)

Oracle Initialization Parameters Pocket Reference 책
Oracle Real Application Clusters 책