Statement 'MERGE /*+ OPT_PARAM('_parallel_syspls_obey_force' 'false') */ INTO OPTSTAT_USER_PREFS' Taking High CPU
(Doc ID 2533703.1)
Last updated on JULY 20, 2024
Applies to:
Oracle Database - Standard Edition - Version 12.2.0.1 and laterInformation in this document applies to any platform.
Symptoms
- Observed that one process is using 100% CPU (one core):
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
26958 oracle 20 0 24.418g 72308 68040 R 100.0 0.1 55586:21 ora_ppa7_strn1 - Found that this process is following sql:
MERGE /*+ OPT_PARAM('_parallel_syspls_obey_force' 'false') */ INTO OPTSTAT_USER_PREFS$ D USING ( SELECT * FROM (SELECT O.OBJ#, SYSTIMESTAMP CHGTIME, ROUND(MAX(S.DELTA_READ_IO_BYTES
/S.DELTA_TIME), 3) SCANRATE FROM GV$ACTIVE_SESSION_HISTORY S, GV$SQL_PLAN P, OBJ$ O, USER$ U WHERE S.INST_ID = P.INST_ID AND S.SQL_ID = P.SQL_ID AND S.SQL_PLAN_HASH_VALUE = P.PLAN_
HASH_VALUE AND S.SQL_PLAN_LINE_ID = P.ID AND P.OBJECT_NAME = O.NAME AND (:B4 IS NULL OR O.OBJ# = :B4 ) AND O.OWNER# = U.USER# AND P.OBJECT_OWNER = U.NAME AND O.TYPE# = 2 AND S.CON_
ID = SYS_CONTEXT('userenv', 'con_id') AND P.CON_ID = SYS_CONTEXT('userenv', 'con_id') AND S.SQL_PLAN_OPERATION = 'TABLE ACCESS' AND P.OPERATION = 'TABLE ACCESS' AND S.DELTA_TIME >
0 AND S.DELTA_READ_IO_BYTES > 0 AND FROM_TZ(S.SAMPLE_TIME, :B3 ) > :B2 AND (:B1 = 'ON' OR (:B1 = 'HADOOP_ONLY' AND EXISTS ( SELECT NULL FROM SYS.EXTERNAL_TAB$ ET WHERE ET.OBJ# = O.
OBJ# AND TYPE$ IN ('ORACLE_HIVE', 'ORACLE_HDFS', 'ORACLE_BIGDATA')))) GROUP BY O.OBJ#) C WHERE NOT EXISTS (SELECT NULL FROM OPTSTAT_USER_PREFS$ PR WHERE PNAME = 'SCAN_RATE' AND C.O
BJ# = PR.OBJ# AND (PR.SPARE1 IS NULL OR C.SCANRATE <= TO_NUMBER(PR.VALCHAR))) ) SRC ON (D.OBJ#=SRC.OBJ# AND D.PNAME = 'SCAN_RATE') WHEN MATCHED THEN UPDATE SET D.VALCHAR= TO_CHAR(S
RC.SCANRATE), D.CHGTIME=SRC.CHGTIME, D.SPARE1=1 WHEN NOT MATCHED THEN INSERT (OBJ#, PNAME, VALCHAR, CHGTIME, SPARE1) VALUES(SRC.OBJ#, 'SCAN_RATE', TO_CHAR(SRC.SCANRATE), SRC.CHGTIM
E, 1)
Changes
none
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Symptoms |
Changes |
Cause |
Solution |
References |