내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
다운로드
trending_flat
OS환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Database 12.2.0.1
에러 : WARNING: too many parse errors, count=500 SQL hash=0x750004bb
db 기동시 alert log에 다량 발생하는 메세지
|
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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
|
$ tail -f alert_ORCL12.log
2020-12-13T22:43:39.526114+09:00
WARNING: too many parse errors, count=100 SQL hash=0x750004bb
PARSE ERROR: ospid=24431, error=933 for statement:
2020-12-13T22:43:39.526240+09:00
DELETE FROM wri$_adv_sqlt_rtn_planWHERE task_id = :tid AND exec_name = :execution_name
Additional information: hd=0x63448050 phd=0x634497a8 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0
2020-12-13T22:43:39.526389+09:00
----- PL/SQL Call Stack -----
object line object
handle number name
0x635eae20 259 type body SYS.WRI$_ADV_SQLTUNE.SUB_DELETE_EXECUTION
0x63620920 2134 package body SYS.PRVT_ADVISOR.COMMON_DELETE_TASK
0x63620920 7342 package body SYS.PRVT_ADVISOR.DELETE_EXPIRED_TASKS
0x61eedaa0 1 anonymous block
WARNING: too many parse errors, count=200 SQL hash=0x750004bb
PARSE ERROR: ospid=24431, error=933 for statement:
2020-12-13T22:43:39.634693+09:00
DELETE FROM wri$_adv_sqlt_rtn_planWHERE task_id = :tid AND exec_name = :execution_name
Additional information: hd=0x63448050 phd=0x634497a8 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0
2020-12-13T22:43:39.634807+09:00
----- PL/SQL Call Stack -----
object line object
handle number name
0x635eae20 259 type body SYS.WRI$_ADV_SQLTUNE.SUB_DELETE_EXECUTION
0x63620920 2134 package body SYS.PRVT_ADVISOR.COMMON_DELETE_TASK
0x63620920 7342 package body SYS.PRVT_ADVISOR.DELETE_EXPIRED_TASKS
0x61eedaa0 1 anonymous block
WARNING: too many parse errors, count=300 SQL hash=0x750004bb
PARSE ERROR: ospid=24431, error=933 for statement:
2020-12-13T22:43:39.734166+09:00
DELETE FROM wri$_adv_sqlt_rtn_planWHERE task_id = :tid AND exec_name = :execution_name
Additional information: hd=0x63448050 phd=0x634497a8 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0
2020-12-13T22:43:39.734283+09:00
----- PL/SQL Call Stack -----
object line object
handle number name
0x635eae20 259 type body SYS.WRI$_ADV_SQLTUNE.SUB_DELETE_EXECUTION
0x63620920 2134 package body SYS.PRVT_ADVISOR.COMMON_DELETE_TASK
0x63620920 7342 package body SYS.PRVT_ADVISOR.DELETE_EXPIRED_TASKS
0x61eedaa0 1 anonymous block
WARNING: too many parse errors, count=400 SQL hash=0x750004bb
PARSE ERROR: ospid=24431, error=933 for statement:
2020-12-13T22:43:39.817447+09:00
DELETE FROM wri$_adv_sqlt_rtn_planWHERE task_id = :tid AND exec_name = :execution_name
Additional information: hd=0x63448050 phd=0x634497a8 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0
2020-12-13T22:43:39.817556+09:00
----- PL/SQL Call Stack -----
object line object
handle number name
0x635eae20 259 type body SYS.WRI$_ADV_SQLTUNE.SUB_DELETE_EXECUTION
0x63620920 2134 package body SYS.PRVT_ADVISOR.COMMON_DELETE_TASK
0x63620920 7342 package body SYS.PRVT_ADVISOR.DELETE_EXPIRED_TASKS
0x61eedaa0 1 anonymous block
WARNING: too many parse errors, count=500 SQL hash=0x750004bb
PARSE ERROR: ospid=24431, error=933 for statement:
2020-12-13T22:43:39.938096+09:00
DELETE FROM wri$_adv_sqlt_rtn_planWHERE task_id = :tid AND exec_name = :execution_name
Additional information: hd=0x63448050 phd=0x634497a8 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0
2020-12-13T22:43:39.938235+09:00
----- PL/SQL Call Stack -----
object line object
handle number name
0x635eae20 259 type body SYS.WRI$_ADV_SQLTUNE.SUB_DELETE_EXECUTION
0x63620920 2134 package body SYS.PRVT_ADVISOR.COMMON_DELETE_TASK
0x63620920 7342 package body SYS.PRVT_ADVISOR.DELETE_EXPIRED_TASKS
0x61eedaa0 1 anonymous block
|
WARNING: too many parse errors, count=100 부터 500까지 발생함
해결 방법 : sysdba 로 접속 후 prvtsqli.plb 스크립트 실행(스크립트 실행후에도 발생함)
sysdba 로 접속 후 prvtsqli.plb 스크립트 실행(스크립트 실행후에도 발생함)
|
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
|
SQL> conn / as sysdba
SQL> @?/rdbms/admin/prvtsqli.plb
Session altered.
Type body created.
No errors.
Package body created.
No errors.
Type body created.
No errors.
Type body created.
No errors.
Grant succeeded.
Session 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 1660944384 bytes
Fixed Size 8621376 bytes
Variable Size 989856448 bytes
Database Buffers 654311424 bytes
Redo Buffers 8155136 bytes
Database mounted.
Database opened.
|
alert log 확인
|
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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
|
$ tail -f alert_ORCL12.log
2020-12-13T22:48:20.565865+09:00
WARNING: too many parse errors, count=100 SQL hash=0x750004bb
PARSE ERROR: ospid=25007, error=933 for statement:
2020-12-13T22:48:20.565992+09:00
DELETE FROM wri$_adv_sqlt_rtn_planWHERE task_id = :tid AND exec_name = :execution_name
Additional information: hd=0x634418e0 phd=0x63442e40 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0
2020-12-13T22:48:20.566143+09:00
----- PL/SQL Call Stack -----
object line object
handle number name
0x63439790 259 type body SYS.WRI$_ADV_SQLTUNE.SUB_DELETE_EXECUTION
0x636207f0 2134 package body SYS.PRVT_ADVISOR.COMMON_DELETE_TASK
0x636207f0 7342 package body SYS.PRVT_ADVISOR.DELETE_EXPIRED_TASKS
0x61eef1a8 1 anonymous block
WARNING: too many parse errors, count=200 SQL hash=0x750004bb
PARSE ERROR: ospid=25007, error=933 for statement:
2020-12-13T22:48:20.679576+09:00
DELETE FROM wri$_adv_sqlt_rtn_planWHERE task_id = :tid AND exec_name = :execution_name
Additional information: hd=0x634418e0 phd=0x63442e40 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0
2020-12-13T22:48:20.680779+09:00
----- PL/SQL Call Stack -----
object line object
handle number name
0x63439790 259 type body SYS.WRI$_ADV_SQLTUNE.SUB_DELETE_EXECUTION
0x636207f0 2134 package body SYS.PRVT_ADVISOR.COMMON_DELETE_TASK
0x636207f0 7342 package body SYS.PRVT_ADVISOR.DELETE_EXPIRED_TASKS
0x61eef1a8 1 anonymous block
WARNING: too many parse errors, count=300 SQL hash=0x750004bb
PARSE ERROR: ospid=25007, error=933 for statement:
2020-12-13T22:48:20.783270+09:00
DELETE FROM wri$_adv_sqlt_rtn_planWHERE task_id = :tid AND exec_name = :execution_name
Additional information: hd=0x634418e0 phd=0x63442e40 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0
2020-12-13T22:48:20.784451+09:00
----- PL/SQL Call Stack -----
object line object
handle number name
0x63439790 259 type body SYS.WRI$_ADV_SQLTUNE.SUB_DELETE_EXECUTION
0x636207f0 2134 package body SYS.PRVT_ADVISOR.COMMON_DELETE_TASK
0x636207f0 7342 package body SYS.PRVT_ADVISOR.DELETE_EXPIRED_TASKS
0x61eef1a8 1 anonymous block
WARNING: too many parse errors, count=400 SQL hash=0x750004bb
PARSE ERROR: ospid=25007, error=933 for statement:
2020-12-13T22:48:20.894547+09:00
DELETE FROM wri$_adv_sqlt_rtn_planWHERE task_id = :tid AND exec_name = :execution_name
Additional information: hd=0x634418e0 phd=0x63442e40 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0
2020-12-13T22:48:20.894656+09:00
----- PL/SQL Call Stack -----
object line object
handle number name
0x63439790 259 type body SYS.WRI$_ADV_SQLTUNE.SUB_DELETE_EXECUTION
0x636207f0 2134 package body SYS.PRVT_ADVISOR.COMMON_DELETE_TASK
0x636207f0 7342 package body SYS.PRVT_ADVISOR.DELETE_EXPIRED_TASKS
0x61eef1a8 1 anonymous block
WARNING: too many parse errors, count=500 SQL hash=0x750004bb
PARSE ERROR: ospid=25007, error=933 for statement:
2020-12-13T22:48:21.044355+09:00
DELETE FROM wri$_adv_sqlt_rtn_planWHERE task_id = :tid AND exec_name = :execution_name
Additional information: hd=0x634418e0 phd=0x63442e40 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0
2020-12-13T22:48:21.045554+09:00
----- PL/SQL Call Stack -----
object line object
handle number name
0x63439790 259 type body SYS.WRI$_ADV_SQLTUNE.SUB_DELETE_EXECUTION
0x636207f0 2134 package body SYS.PRVT_ADVISOR.COMMON_DELETE_TASK
0x636207f0 7342 package body SYS.PRVT_ADVISOR.DELETE_EXPIRED_TASKS
0x61eef1a8 1 anonymous block
|
동일하게 발생함...
MOS에서 이 방법 외에 다른 해결방법은 찾지못함
하지만 alert log에만 해당 메세지가 나오고
DB 자체에 문제를 주는 로그는 아니라 넘겨도 될듯함
prvtsqli.plb 해당 스크립트를 열어서 내용을 확인하려 했지만
해당 쿼리가 암호화? 되어있어서 제대로 확인하지 못함
prvtsqli.plb 파일 일부 내용
|
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
|
$ vi $ORACLE_HOME/rdbms/admin/prvtsqli.plb
@@?/rdbms/admin/sqlsessstart.sql
CREATE OR REPLACE TYPE BODY sqlset_row wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
e
e09 436
C9qz4dhnw808JZn6/NibuHZJehowg5ArACAFfC/Nbg+v171p9X1Uax3pkH5h0ybxiba/qFAb
hDdmNbc/5HMZ9J9Z8uB+ldY4TlXaCb/G/l2fpEsJ6toz0h7B61Ugtc6l0FISr3VQl3vrmbCy
ChJsbncYMZbwRMHwvLyRdNYEOzCTLSkX2fC0uE4ih/il9ya83hnLR6P9+7xD3H4aoxLVsRiY
GaZAoGx1IboVGgCkaFP6QVGzQzBiOPybtufB/d6AkfKIZ9pyVmPlz5W
.
.
.
|
원인 : 이 건의 경우 해당버전 오라클 버그
특정 패치 후 필수 스크립트를 실행하지 않아서 발생하는 문제
스크립트 실행후에도 동일하게 해당 메세지가 alert log 에 발생함
추가
게시글처럼 버그가 아닌경우 아래 게시글 참고
https://positivemh.tistory.com/890
WARNING: too many parse errors
OS환경 : Oracle Linux 7.6 (64bit) DB 환경 : Oracle Database 19.3.0.0 에러 : WARNING: too many parse errors alert log 에 발생하는 WARNING: too many parse errors 메세지 1 2 3 4 5 6 7 8 9 10 11 12 13 14 $ vi alert.log 2023-05-01T03:54:39.882161+
positivemh.tistory.com
참조 :
2467646.1
2466404.1