12.2 RAC
AWR中，在每个pdb的SQL ordered by Version Count中都会出现这一条SQL，应该是非业务的系统SQL，这种系统内部的SQL的排查思路。谢谢

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.OBJ# = 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(SRC.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.CHGTIME, 1)
复制