This post is also available in: English
Since Oct-2017 I am quarterly writing a post dissecting the changes implemented by Oracle CPUs so we can understand better the modifications implemented by Oracle in our Databases.
So what oracle internal objects were changed in 180417?
VERSION PATCH OWNER TYPE TOTAL -------------------- ---------- ------------------------------ ------------------------------ ---------- 12.1.0.2 BP SYS PACKAGE 1 12.1.0.2 BP SYS PACKAGE BODY 2 12.1.0.2 PSU SYS PACKAGE BODY 1 12.2.0.1 RU CTXSYS PACKAGE BODY 1 12.2.0.1 RU DVSYS TYPE 12 12.2.0.1 RU DVSYS VIEW 3 12.2.0.1 RU SYS PACKAGE 1 12.2.0.1 RU SYS PACKAGE BODY 4 12.2.0.1 RU SYS PROCEDURE 1 12.2.0.1 JAN2018RUR SYS PACKAGE BODY 2
And created?
VERSION PATCH OWNER TYPE TOTAL -------------------- ---------- ------------------------------ ------------------------------ ---------- 12.1.0.2 BP SYS PACKAGE 1 12.1.0.2 BP SYS PACKAGE BODY 2 12.1.0.2 PSU & BP SYS PACKAGE 1
P.S: Nothing was added or changed for 11.2.0.4 in Oracle objects, that's why its not included here.
So which are exactly the objects modified by 180417?
VERSION PATCH OWNER TYPE NAME -------------------- ---------- ------------------------------ ------------------------------ ------------------------------ 12.1.0.2 BP SYS PACKAGE DBMS_HADOOP 12.1.0.2 BP SYS PACKAGE BODY DBMS_HADOOP 12.1.0.2 BP SYS PACKAGE BODY DBMS_QOPATCH 12.1.0.2 PSU SYS PACKAGE BODY DBMS_QOPATCH 12.2.0.1 RU CTXSYS PACKAGE BODY DRIPARSE 12.2.0.1 RU DVSYS TYPE KU$_DV_COMM_RULE_ALTS_T 12.2.0.1 RU DVSYS TYPE KU$_DV_POLICY_OBJ_C_ALTS_T 12.2.0.1 RU DVSYS TYPE KU$_DV_POLICY_OBJ_C_T 12.2.0.1 RU DVSYS TYPE SYS_YOID0000072667$ 12.2.0.1 RU DVSYS TYPE SYS_YOID0000072667$ 12.2.0.1 RU DVSYS TYPE SYS_YOID0000072694$ 12.2.0.1 RU DVSYS TYPE SYS_YOID0000072694$ 12.2.0.1 RU DVSYS TYPE SYS_YOID0000072697$ 12.2.0.1 RU DVSYS TYPE SYS_YOID0000072697$ 12.2.0.1 RU DVSYS TYPE SYS_YOID0000072873$ 12.2.0.1 RU DVSYS TYPE SYS_YOID0000072900$ 12.2.0.1 RU DVSYS TYPE SYS_YOID0000072903$ 12.2.0.1 RU DVSYS VIEW KU$_DV_COMM_RULE_ALTS_V 12.2.0.1 RU DVSYS VIEW KU$_DV_POLICY_OBJ_C_ALTS_V 12.2.0.1 RU DVSYS VIEW KU$_DV_POLICY_OBJ_C_V 12.2.0.1 RU SYS PACKAGE DBMS_SQLPATCH 12.2.0.1 RU SYS PACKAGE BODY DBMS_LOCK 12.2.0.1 RU SYS PACKAGE BODY DBMS_QOPATCH 12.2.0.1 RU SYS PACKAGE BODY DBMS_SQLPATCH 12.2.0.1 RU SYS PACKAGE BODY PRVTEMX_CELL 12.2.0.1 RU SYS PROCEDURE DBMS_FEATURE_ADV_IDXCMP 12.2.0.1 JAN2018RUR SYS PACKAGE BODY DBMS_QOPATCH 12.2.0.1 JAN2018RUR SYS PACKAGE BODY DBMS_SQLPATCH
What changed for each (link to changes)?
- 12.1.0.2
- 12.2.0.1
- CTXSYS.DRIPARSE - PACKAGE BODY (RU)
- DVSYS.KU$_DV_COMM_RULE_ALTS_T - TYPE (RU)
- DVSYS.KU$_DV_POLICY_OBJ_C_ALTS_T - TYPE (RU)
- DVSYS.KU$_DV_POLICY_OBJ_C_T - TYPE (RU)
- DVSYS.SYS_YOID0000072667$ - TYPE (RU)
- DVSYS.SYS_YOID0000072667$ - TYPE (RU)
- DVSYS.SYS_YOID0000072694$ - TYPE (RU)
- DVSYS.SYS_YOID0000072694$ - TYPE (RU)
- DVSYS.SYS_YOID0000072697$ - TYPE (RU)
- DVSYS.SYS_YOID0000072697$ - TYPE (RU)
- DVSYS.SYS_YOID0000072873$ - TYPE (RU)
- DVSYS.SYS_YOID0000072900$ - TYPE (RU)
- DVSYS.SYS_YOID0000072903$ - TYPE (RU)
- DVSYS.KU$_DV_COMM_RULE_ALTS_V - VIEW (RU)
- DVSYS.KU$_DV_POLICY_OBJ_C_ALTS_V - VIEW (RU)
- DVSYS.KU$_DV_POLICY_OBJ_C_V - VIEW (RU)
- SYS.DBMS_SQLPATCH - PACKAGE (RU)
- SYS.DBMS_LOCK - PACKAGE BODY (RU)
- SYS.DBMS_QOPATCH - PACKAGE BODY (RU)
- SYS.DBMS_SQLPATCH - PACKAGE BODY (RU)
- SYS.PRVTEMX_CELL - PACKAGE BODY (RU)
- SYS.DBMS_FEATURE_ADV_IDXCMP - PROCEDURE (RU)
- SYS.DBMS_QOPATCH - PACKAGE BODY (JAN2018RUR)
- SYS.DBMS_SQLPATCH - PACKAGE BODY (JAN2018RUR)
P.S:
< = Added Lines
> = Removed Lines
Changed Objects
55,59d54 < FUNCTION GET_USER_MAP(cluster_name IN VARCHAR2 DEFAULT '[DEFAULT]', < current_database_user IN VARCHAR2) -- NOT NULL < RETURN VARCHAR2; < <
114,152c114 < < < < < < FUNCTION GET_USER_MAP(CLUSTER_NAME IN VARCHAR2 DEFAULT '[DEFAULT]', < CURRENT_DATABASE_USER IN VARCHAR2) < RETURN VARCHAR2 < IS < HDFS_USER_OUT VARCHAR2(128); < DEFAULT_CLUSTER_NAME CONSTANT VARCHAR2(9) := '[DEFAULT]'; < USE_THIS_CLUSTER_NAME VARCHAR2(128); < MAPPING VARCHAR2(128); < RC PLS_INTEGER; < TRUEORFALSE CHAR(5); < BEGIN < < SELECT SYS_CONTEXT('USERENV', 'ISDBA') INTO TRUEORFALSE FROM DUAL; < < IF TRUEORFALSE != 'TRUE' AND < NOT DBMS_SESSION.IS_ROLE_ENABLED('BDSQL_USER') THEN < < RETURN NULL; < END IF; < IF CLUSTER_NAME <> DEFAULT_CLUSTER_NAME THEN < USE_THIS_CLUSTER_NAME := CLUSTER_NAME; < ELSE < USE_THIS_CLUSTER_NAME := DEFAULT_CLUSTER_NAME; < END IF; < < HDFS_USER_OUT := NULL; < < DBMS_BDSQL.SEL_USERMAP(USE_THIS_CLUSTER_NAME, CURRENT_DATABASE_USER, < HDFS_USER_OUT, RC); < RETURN HDFS_USER_OUT; < < END GET_USER_MAP; < < END DBMS_HADOOP; --- > END;
398,405c398,401 < < SELECT HOST_NAME INTO NNAME FROM GV$INSTANCE WHERE INST_ID = INST_TAB(I).INST_NUMBER; < SELECT INSTANCE_NAME INTO INAME FROM GV$INSTANCE WHERE INST_ID = INST_TAB(I).INST_NUMBER; < IF DO_DEBUG THEN < DBMS_OUTPUT.PUT_LINE('QPI: fetching hostname from gv$instance :' || NNAME || ' for inst_number : ' || INST_TAB(I).INST_NUMBER); < DBMS_OUTPUT.PUT_LINE('QPI: fetching instance_name from gv$instance :' || INAME || ' for inst_number : ' || INST_TAB(I).INST_NUMBER); < END IF; < --- > TNAME := SUBSTR(INST_TAB(I).INST_NAME,1,INSTR(INST_TAB(I).INST_NAME,' ')-1); > NNAME := SUBSTR(TNAME,1,INSTR(TNAME,':')-1); > TNAME := REPLACE(TNAME,CHR(0),''); > INAME := SUBSTR(TNAME,INSTR(TNAME,':')+1); 1503,1509c1499,1502 < < SELECT HOST_NAME INTO NDNAME FROM GV$INSTANCE WHERE INST_ID = INST_TAB(I).INST_NUMBER; < SELECT INSTANCE_NAME INTO ISNAME FROM GV$INSTANCE WHERE INST_ID = INST_TAB(I).INST_NUMBER; < IF DO_DEBUG THEN < DBMS_OUTPUT.PUT_LINE('QPI: fetching hostname from gv$instance :' || NDNAME || ' for inst_number : ' || INST_TAB(I).INST_NUMBER); < DBMS_OUTPUT.PUT_LINE('QPI: fetching instance_name from gv$instance :' || ISNAME || ' for inst_number : ' || INST_TAB(I).INST_NUMBER); < END IF; --- > TNAME := SUBSTR(INST_TAB(I).INST_NAME,1,INSTR(INST_TAB(I).INST_NAME,' ')-1); > NDNAME := SUBSTR(TNAME,1,INSTR(TNAME,':')-1); > TNAME := REPLACE(TNAME,CHR(0),''); > ISNAME := SUBSTR(TNAME,INSTR(TNAME,':')+1); 1744,1750c1737,1740 < < SELECT HOST_NAME INTO NDNAME FROM GV$INSTANCE WHERE INST_ID = INST_TAB(I).INST_NUMBER; < SELECT INSTANCE_NAME INTO ISNAME FROM GV$INSTANCE WHERE INST_ID = INST_TAB(I).INST_NUMBER; < IF DO_DEBUG THEN < DBMS_OUTPUT.PUT_LINE('QPI: fetching hostname from gv$instance :' || NDNAME || ' for inst_number : ' || INST_TAB(I).INST_NUMBER); < DBMS_OUTPUT.PUT_LINE('QPI: fetching instance_name from gv$instance :' || ISNAME || ' for inst_number : ' || INST_TAB(I).INST_NUMBER); < END IF; --- > TNAME := SUBSTR(INST_TAB(I).INST_NAME,1,INSTR(INST_TAB(I).INST_NAME,' ')-1); > NDNAME := SUBSTR(TNAME,1,INSTR(TNAME,':')-1); > TNAME := REPLACE(TNAME,CHR(0),''); > ISNAME := SUBSTR(TNAME,INSTR(TNAME,':')+1);
398,405c398,401 < < SELECT HOST_NAME INTO NNAME FROM GV$INSTANCE WHERE INST_ID = INST_TAB(I).INST_NUMBER; < SELECT INSTANCE_NAME INTO INAME FROM GV$INSTANCE WHERE INST_ID = INST_TAB(I).INST_NUMBER; < IF DO_DEBUG THEN < DBMS_OUTPUT.PUT_LINE('QPI: fetching hostname from gv$instance :' || NNAME || ' for inst_number : ' || INST_TAB(I).INST_NUMBER); < DBMS_OUTPUT.PUT_LINE('QPI: fetching instance_name from gv$instance :' || INAME || ' for inst_number : ' || INST_TAB(I).INST_NUMBER); < END IF; < --- > TNAME := SUBSTR(INST_TAB(I).INST_NAME,1,INSTR(INST_TAB(I).INST_NAME,' ')-1); > NNAME := SUBSTR(TNAME,1,INSTR(TNAME,':')-1); > TNAME := REPLACE(TNAME,CHR(0),''); > INAME := SUBSTR(TNAME,INSTR(TNAME,':')+1); 1504,1510c1500,1503 < < SELECT HOST_NAME INTO NDNAME FROM GV$INSTANCE WHERE INST_ID = INST_TAB(I).INST_NUMBER; < SELECT INSTANCE_NAME INTO ISNAME FROM GV$INSTANCE WHERE INST_ID = INST_TAB(I).INST_NUMBER; < IF DO_DEBUG THEN < DBMS_OUTPUT.PUT_LINE('QPI: fetching hostname from gv$instance :' || NDNAME || ' for inst_number : ' || INST_TAB(I).INST_NUMBER); < DBMS_OUTPUT.PUT_LINE('QPI: fetching instance_name from gv$instance :' || ISNAME || ' for inst_number : ' || INST_TAB(I).INST_NUMBER); < END IF; --- > TNAME := SUBSTR(INST_TAB(I).INST_NAME,1,INSTR(INST_TAB(I).INST_NAME,' ')-1); > NDNAME := SUBSTR(TNAME,1,INSTR(TNAME,':')-1); > TNAME := REPLACE(TNAME,CHR(0),''); > ISNAME := SUBSTR(TNAME,INSTR(TNAME,':')+1); 1744,1750c1737,1740 < < SELECT HOST_NAME INTO NDNAME FROM GV$INSTANCE WHERE INST_ID = INST_TAB(I).INST_NUMBER; < SELECT INSTANCE_NAME INTO ISNAME FROM GV$INSTANCE WHERE INST_ID = INST_TAB(I).INST_NUMBER; < IF DO_DEBUG THEN < DBMS_OUTPUT.PUT_LINE('QPI: fetching hostname from gv$instance :' || NDNAME || ' for inst_number : ' || INST_TAB(I).INST_NUMBER); < DBMS_OUTPUT.PUT_LINE('QPI: fetching instance_name from gv$instance :' || ISNAME || ' for inst_number : ' || INST_TAB(I).INST_NUMBER); < END IF; --- > TNAME := SUBSTR(INST_TAB(I).INST_NAME,1,INSTR(INST_TAB(I).INST_NAME,' ')-1); > NDNAME := SUBSTR(TNAME,1,INSTR(TNAME,':')-1); > TNAME := REPLACE(TNAME,CHR(0),''); > ISNAME := SUBSTR(TNAME,INSTR(TNAME,':')+1);
1283d1282 < IF (OPTS.LEXER IS NULL) THEN 1285,1286c1284 < GETJSONLEXER(JSON_LANGUAGE, OPTS.LEXER); < END IF; --- > GETJSONLEXER(JSON_LANGUAGE, OPTS.LEXER);
5d4 < oidval raw(16), /* unique id */
5d4 < oidval raw(16), /* unique id */
5d4 < oidval raw(16), /* unique id */
1,2c1,4 < type "SYS_YOID0000072667$" as object( "SYS_NC00001$" RAW(16 < )) --- > type "SYS_YOID0000072667$" as object( "SYS_NC00001$" VARCHA > R2(128 BYTE), "SYS_NC00002$" VARCHAR2(100 BYTE), "SYS_NC00003$" VARCHAR2(128 BYT > E), "SYS_NC00004$" VARCHAR2(128 BYTE), "SYS_NC00005$" VARCHAR2(128 BYTE), "SYS_N > C00006$" VARCHAR2(128 BYTE), "SYS_NC00007$" NUMBER) 4,5c6,9 < type "SYS_YOID0000072667$" as object( "SYS_NC00001$" RAW(16 < )) --- > type "SYS_YOID0000072667$" as object( "SYS_NC00001$" VARCHA > R2(128 BYTE), "SYS_NC00002$" VARCHAR2(100 BYTE), "SYS_NC00003$" VARCHAR2(128 BYT > E), "SYS_NC00004$" VARCHAR2(128 BYTE), "SYS_NC00005$" VARCHAR2(128 BYTE), "SYS_N > C00006$" VARCHAR2(128 BYTE), "SYS_NC00007$" NUMBER)
1,2c1,4 < type "SYS_YOID0000072667$" as object( "SYS_NC00001$" RAW(16 < )) --- > type "SYS_YOID0000072667$" as object( "SYS_NC00001$" VARCHA > R2(128 BYTE), "SYS_NC00002$" VARCHAR2(100 BYTE), "SYS_NC00003$" VARCHAR2(128 BYT > E), "SYS_NC00004$" VARCHAR2(128 BYTE), "SYS_NC00005$" VARCHAR2(128 BYTE), "SYS_N > C00006$" VARCHAR2(128 BYTE), "SYS_NC00007$" NUMBER) 4,5c6,9 < type "SYS_YOID0000072667$" as object( "SYS_NC00001$" RAW(16 < )) --- > type "SYS_YOID0000072667$" as object( "SYS_NC00001$" VARCHA > R2(128 BYTE), "SYS_NC00002$" VARCHAR2(100 BYTE), "SYS_NC00003$" VARCHAR2(128 BYT > E), "SYS_NC00004$" VARCHAR2(128 BYTE), "SYS_NC00005$" VARCHAR2(128 BYTE), "SYS_N > C00006$" VARCHAR2(128 BYTE), "SYS_NC00007$" NUMBER)
1,2c1,3 < type "SYS_YOID0000072694$" as object( "SYS_NC00001$" RAW(16 < )) --- > type "SYS_YOID0000072694$" as object( "SYS_NC00001$" VARCHA > R2(128 BYTE), "SYS_NC00002$" VARCHAR2(128 BYTE), "SYS_NC00003$" VARCHAR2(128 BYT > E), "SYS_NC00004$" VARCHAR2(128 BYTE)) 4,5c5,7 < type "SYS_YOID0000072694$" as object( "SYS_NC00001$" RAW(16 < )) --- > type "SYS_YOID0000072694$" as object( "SYS_NC00001$" VARCHA > R2(128 BYTE), "SYS_NC00002$" VARCHAR2(128 BYTE), "SYS_NC00003$" VARCHAR2(128 BYT > E), "SYS_NC00004$" VARCHAR2(128 BYTE))
1,2c1,3 < type "SYS_YOID0000072694$" as object( "SYS_NC00001$" RAW(16 < )) --- > type "SYS_YOID0000072694$" as object( "SYS_NC00001$" VARCHA > R2(128 BYTE), "SYS_NC00002$" VARCHAR2(128 BYTE), "SYS_NC00003$" VARCHAR2(128 BYT > E), "SYS_NC00004$" VARCHAR2(128 BYTE)) 4,5c5,7 < type "SYS_YOID0000072694$" as object( "SYS_NC00001$" RAW(16 < )) --- > type "SYS_YOID0000072694$" as object( "SYS_NC00001$" VARCHA > R2(128 BYTE), "SYS_NC00002$" VARCHAR2(128 BYTE), "SYS_NC00003$" VARCHAR2(128 BYT > E), "SYS_NC00004$" VARCHAR2(128 BYTE))
1,2c1,4 < type "SYS_YOID0000072697$" as object( "SYS_NC00001$" RAW(16 < )) --- > type "SYS_YOID0000072697$" as object( "SYS_NC00001$" VARCHA > R2(128 BYTE), "SYS_NC00002$" VARCHAR2(128 BYTE), "SYS_NC00003$" VARCHAR2(100 BYT > E), "SYS_NC00004$" VARCHAR2(128 BYTE), "SYS_NC00005$" VARCHAR2(128 BYTE), "SYS_N > C00006$" VARCHAR2(128 BYTE), "SYS_NC00007$" VARCHAR2(128 BYTE)) 4,5c6,9 < type "SYS_YOID0000072697$" as object( "SYS_NC00001$" RAW(16 < )) --- > type "SYS_YOID0000072697$" as object( "SYS_NC00001$" VARCHA > R2(128 BYTE), "SYS_NC00002$" VARCHAR2(128 BYTE), "SYS_NC00003$" VARCHAR2(100 BYT > E), "SYS_NC00004$" VARCHAR2(128 BYTE), "SYS_NC00005$" VARCHAR2(128 BYTE), "SYS_N > C00006$" VARCHAR2(128 BYTE), "SYS_NC00007$" VARCHAR2(128 BYTE))
1,2c1,4 < type "SYS_YOID0000072697$" as object( "SYS_NC00001$" RAW(16 < )) --- > type "SYS_YOID0000072697$" as object( "SYS_NC00001$" VARCHA > R2(128 BYTE), "SYS_NC00002$" VARCHAR2(128 BYTE), "SYS_NC00003$" VARCHAR2(100 BYT > E), "SYS_NC00004$" VARCHAR2(128 BYTE), "SYS_NC00005$" VARCHAR2(128 BYTE), "SYS_N > C00006$" VARCHAR2(128 BYTE), "SYS_NC00007$" VARCHAR2(128 BYTE)) 4,5c6,9 < type "SYS_YOID0000072697$" as object( "SYS_NC00001$" RAW(16 < )) --- > type "SYS_YOID0000072697$" as object( "SYS_NC00001$" VARCHA > R2(128 BYTE), "SYS_NC00002$" VARCHAR2(128 BYTE), "SYS_NC00003$" VARCHAR2(100 BYT > E), "SYS_NC00004$" VARCHAR2(128 BYTE), "SYS_NC00005$" VARCHAR2(128 BYTE), "SYS_N > C00006$" VARCHAR2(128 BYTE), "SYS_NC00007$" VARCHAR2(128 BYTE))
1,2c1,4 < type "SYS_YOID0000072873$" as object( "SYS_NC00001$" RAW(16 < )) --- > type "SYS_YOID0000072873$" as object( "SYS_NC00001$" VARCHA > R2(128 BYTE), "SYS_NC00002$" VARCHAR2(100 BYTE), "SYS_NC00003$" VARCHAR2(128 BYT > E), "SYS_NC00004$" VARCHAR2(128 BYTE), "SYS_NC00005$" VARCHAR2(128 BYTE), "SYS_N > C00006$" VARCHAR2(128 BYTE), "SYS_NC00007$" NUMBER)
1,2c1,3 < type "SYS_YOID0000072900$" as object( "SYS_NC00001$" RAW(16 < )) --- > type "SYS_YOID0000072900$" as object( "SYS_NC00001$" VARCHA > R2(128 BYTE), "SYS_NC00002$" VARCHAR2(128 BYTE), "SYS_NC00003$" VARCHAR2(128 BYT > E), "SYS_NC00004$" VARCHAR2(128 BYTE))
1,2c1,4 < type "SYS_YOID0000072903$" as object( "SYS_NC00001$" RAW(16 < )) --- > type "SYS_YOID0000072903$" as object( "SYS_NC00001$" VARCHA > R2(128 BYTE), "SYS_NC00002$" VARCHAR2(128 BYTE), "SYS_NC00003$" VARCHAR2(100 BYT > E), "SYS_NC00004$" VARCHAR2(128 BYTE), "SYS_NC00005$" VARCHAR2(128 BYTE), "SYS_N > C00006$" VARCHAR2(128 BYTE), "SYS_NC00007$" VARCHAR2(128 BYTE))
1c1 < select '0','0',sys_guid(), --- > select '0','0',
1c1 < select '0','0',sys_guid(), --- > select '0','0',
1c1 < select '0','0',sys_guid(), --- > select '0','0',
66,67c66 < p_patch_directory IN BLOB := NULL, < p_installing_bundle IN NUMBER := NULL); --- > p_patch_directory IN BLOB := NULL);
17,26d16 < CLEANUP_LOCK_NAME CONSTANT VARCHAR2(14) := 'PrVtLoCk035592'; < < < < < < < < DEBUG_LOCK INTEGER := 0; < 219,336d208 < < PROCEDURE DBMSLOCK_DEBUG(L IN VARCHAR2) IS < BEGIN < IF DEBUG_LOCK = 2 THEN < DBMS_SYSTEM.KSDWRT(DBMS_SYSTEM.TRACE_FILE, < 'dbms_lock:' || L); < < END IF; < END DBMSLOCK_DEBUG; < < < < < < PROCEDURE INTERNAL_LOCK_CLEAN(CLEANUP IN BOOLEAN DEFAULT TRUE) IS < PRAGMA AUTONOMOUS_TRANSACTION; < < L_STATUS INTEGER := 0; < LOCK_ACQUIRED BOOLEAN := FALSE; < < BEGIN < < < < < < < < L_STATUS := DBMS_LOCK.REQUEST(DBMS_UTILITY.GET_HASH_VALUE( < CLEANUP_LOCK_NAME, 2000003064, 3064), < DBMS_LOCK.X_MODE, 0, < FALSE); < < IF L_STATUS = 0 < THEN < LOCK_ACQUIRED := TRUE; < < DBMSLOCK_DEBUG( < 'Proceeding on lock cleanup l_status '|| L_STATUS); < ELSE < < < < < IF L_STATUS IN (3,5) < THEN < DBMS_SYSTEM.KSDWRT(DBMS_SYSTEM.TRACE_FILE, < 'dbms_lock: error acquiring cleanup lock ' || TO_CHAR(L_STATUS)); < ELSE < DBMSLOCK_DEBUG( < 'cleanup lock already held. Cleanup skipped '|| < TO_CHAR(L_STATUS)); < END IF; < LOCK_ACQUIRED := FALSE; < END IF; < < IF LOCK_ACQUIRED < THEN < < < DECLARE < TYPE INTEGER_VARRAY IS TABLE OF INTEGER; < L_LOCKID INTEGER_VARRAY; < BEGIN < SELECT LOCKID BULK COLLECT INTO L_LOCKID < FROM DBMS_LOCK_ALLOCATED WHERE EXPIRATION < SYSDATE < FOR UPDATE SKIP LOCKED; < < FOR I IN 1..L_LOCKID.COUNT < LOOP < DELETE DBMS_LOCK_ALLOCATED < WHERE LOCKID = L_LOCKID(I); < END LOOP; < IF L_LOCKID.COUNT > 0 < THEN < DBMSLOCK_DEBUG('exp locks cleaned '|| L_LOCKID.COUNT); < ELSE < DBMSLOCK_DEBUG('exp locks nothing to clean'); < END IF; < < < L_STATUS := DBMS_LOCK.RELEASE(DBMS_UTILITY.GET_HASH_VALUE( < CLEANUP_LOCK_NAME, 2000003064, 3064)); < IF L_STATUS = 0 < THEN < DBMSLOCK_DEBUG('released cleanup lock'); < LOCK_ACQUIRED := FALSE; < ELSE < DBMS_SYSTEM.KSDWRT(DBMS_SYSTEM.TRACE_FILE, < 'error releasing cleanup lock ' || L_STATUS); < END IF; < < COMMIT; < < EXCEPTION < WHEN OTHERS THEN < DBMS_SYSTEM.KSDWRT(DBMS_SYSTEM.TRACE_FILE, < 'error deleting expired locks: ' || SQLERRM); < ROLLBACK; < < < IF LOCK_ACQUIRED < THEN < L_STATUS := DBMS_LOCK.RELEASE(DBMS_UTILITY.GET_HASH_VALUE( < CLEANUP_LOCK_NAME, 2000003064, 3064)); < IF L_STATUS = 0 < THEN < DBMSLOCK_DEBUG('released cleanup lock'); < ELSE < DBMS_SYSTEM.KSDWRT(DBMS_SYSTEM.TRACE_FILE, < 'error releasing cleanup lock ' || L_STATUS); < END IF; < END IF; < END; < END IF; < < END INTERNAL_LOCK_CLEAN; < 371,375d242 < PTYP BINARY_INTEGER; < INTVAL BINARY_INTEGER; < STRVAL VARCHAR2(256); < FORCE_CLEAN BOOLEAN := FALSE; < 382d248 < 384,408d249 < < < IF DEBUG_LOCK = 0 < THEN < PTYP := DBMS_UTILITY.GET_PARAMETER_VALUE('_qa_lrg_type', < INTVAL, STRVAL); < IF (BITAND(INTVAL, 32) = 32) < THEN < DEBUG_LOCK := 2; < DBMSLOCK_DEBUG('debug output enabled'); < ELSE < DEBUG_LOCK := 1; < END IF; < END IF; < < < < IF DEBUG_LOCK = 2 < THEN < IF LOCKNAME LIKE 'bye%' < THEN < FORCE_CLEAN := TRUE; < END IF; < END IF; < 421c262 < IF (FORCE_CLEAN = TRUE OR ((LOCKID MOD 100) = 0) ) --- > IF (LOCKID MOD 100) = 0 424d264 < DBMSLOCK_DEBUG('time to clean: '|| LOCKID); 451,452c291 < < IF DO_CLEANUP = TRUE --- > IF ( DO_CLEANUP = TRUE ) 454c293,294 < INTERNAL_LOCK_CLEAN(); --- > DELETE DBMS_LOCK_ALLOCATED WHERE EXPIRATION < SYSDATE; > COMMIT;
448,455c448,451 < < SELECT HOST_NAME INTO NNAME FROM GV$INSTANCE WHERE INST_ID = INST_TAB(I).INST_NUMBER; < SELECT INSTANCE_NAME INTO INAME FROM GV$INSTANCE WHERE INST_ID = INST_TAB(I).INST_NUMBER; < IF DO_DEBUG THEN < DBMS_OUTPUT.PUT_LINE('QPI: fetching hostname from gv$instance :' || NNAME || ' for inst_number : ' || INST_TAB(I).INST_NUMBER); < DBMS_OUTPUT.PUT_LINE('QPI: fetching instance_name from gv$instance :' || INAME || ' for inst_number : ' || INST_TAB(I).INST_NUMBER); < END IF; < --- > TNAME := SUBSTR(INST_TAB(I).INST_NAME,1,INSTR(INST_TAB(I).INST_NAME,' ')-1); > NNAME := SUBSTR(TNAME,1,INSTR(TNAME,':')-1); > TNAME := REPLACE(TNAME,CHR(0),''); > INAME := SUBSTR(TNAME,INSTR(TNAME,':')+1); 1622,1624c1618,1626 < < SELECT HOST_NAME INTO NDNAME FROM GV$INSTANCE WHERE INST_ID = INST_TAB(I).INST_NUMBER; < SELECT INSTANCE_NAME INTO ISNAME FROM GV$INSTANCE WHERE INST_ID = INST_TAB(I).INST_NUMBER; --- > TNAME := SUBSTR(INST_TAB(I).INST_NAME,1,INSTR(INST_TAB(I).INST_NAME,' ')-1); > IF DO_DEBUG THEN > DBMS_OUTPUT.PUT_LINE('QPI: tname:' || TNAME||':'); > END IF; > NDNAME := SUBSTR(TNAME,1,INSTR(TNAME,':')-1); > IF DO_DEBUG THEN > DBMS_OUTPUT.PUT_LINE('QPI: ndame:' || NDNAME||':'); > END IF; > TNAME := REPLACE(TNAME,CHR(0),''); 1626,1627c1628,1633 < DBMS_OUTPUT.PUT_LINE('QPI: fetching hostname from gv$instance :' || NDNAME || ' for inst_number : ' || INST_TAB(I).INST_NUMBER); < DBMS_OUTPUT.PUT_LINE('QPI: fetching instance_name from gv$instance :' || ISNAME || ' for inst_number : ' || INST_TAB(I).INST_NUMBER); --- > DBMS_OUTPUT.PUT_LINE('QPI: tname:' || TNAME||':'); > END IF; > ISNAME := SUBSTR(TNAME,INSTR(TNAME,':')+1); > IF DO_DEBUG THEN > DBMS_OUTPUT.PUT_LINE('QPI: isname:' || ISNAME||':'); > DBMS_OUTPUT.PUT_LINE('QPI: inst-number:' || INST_TAB(I).INST_NUMBER||':'); 1841,1847c1847,1850 < < SELECT HOST_NAME INTO NDNAME FROM GV$INSTANCE WHERE INST_ID = INST_TAB(I).INST_NUMBER; < SELECT INSTANCE_NAME INTO ISNAME FROM GV$INSTANCE WHERE INST_ID = INST_TAB(I).INST_NUMBER; < IF DO_DEBUG THEN < DBMS_OUTPUT.PUT_LINE('QPI: fetching hostname from gv$instance :' || NDNAME || ' for inst_number : ' || INST_TAB(I).INST_NUMBER); < DBMS_OUTPUT.PUT_LINE('QPI: fetching instance_name from gv$instance :' || ISNAME || ' for inst_number : ' || INST_TAB(I).INST_NUMBER); < END IF; --- > TNAME := SUBSTR(INST_TAB(I).INST_NAME,1,INSTR(INST_TAB(I).INST_NAME,' ')-1); > NDNAME := SUBSTR(TNAME,1,INSTR(TNAME,':')-1); > TNAME := REPLACE(TNAME,CHR(0),''); > ISNAME := SUBSTR(TNAME,INSTR(TNAME,':')+1); 2834,2840c2837,2840 < < SELECT HOST_NAME INTO NNAME FROM GV$INSTANCE WHERE INST_ID = INST_TAB(I).INST_NUMBER; < SELECT INSTANCE_NAME INTO INAME FROM GV$INSTANCE WHERE INST_ID = INST_TAB(I).INST_NUMBER; < IF DO_DEBUG THEN < DBMS_OUTPUT.PUT_LINE('QPI: fetching hostname from gv$instance :' || NNAME || ' for inst_number : ' || INST_TAB(I).INST_NUMBER); < DBMS_OUTPUT.PUT_LINE('QPI: fetching instance_name from gv$instance :' || INAME || ' for inst_number : ' || INST_TAB(I).INST_NUMBER); < END IF; --- > TNAME := SUBSTR(INST_TAB(I).INST_NAME,1,INSTR(INST_TAB(I).INST_NAME,' ')-1); > NNAME := SUBSTR(TNAME,1,INSTR(TNAME,':')-1); > TNAME := REPLACE(TNAME,CHR(0),''); > INAME := SUBSTR(TNAME,INSTR(TNAME,':')+1);
324,325c324 < S_APPLICATION_PATCH, S_PATCH_DIRECTORY_ZIP, < S_INSTALLING_BUNDLE --- > S_APPLICATION_PATCH, S_PATCH_DIRECTORY_ZIP 327,328c326 < L_APPLICATION_PATCH, PATCH_DIRECTORY_ZIP, < INSTALLING_BUNDLE --- > L_APPLICATION_PATCH, PATCH_DIRECTORY_ZIP 405a404 > 422c421 < INSTALLED_BUNDLE := REGISTRY_ID; --- > INSTALLED_BUNDLE := 0; 474c473,478 < DEBUG_PUT_LINE(' installing bundle: ' || INSTALLING_BUNDLE); --- > > > SELECT NVL(MAX(EXTRACT(COLUMN_VALUE, '/bundle/@id').GETNUMBERVAL()), 0) > INTO INSTALLING_BUNDLE > FROM XMLTABLE('/bundledata/bundle' > PASSING BUNDLEDATA); 977,978c981 < P_PATCH_DIRECTORY IN BLOB := NULL, < P_INSTALLING_BUNDLE IN NUMBER := NULL) IS --- > P_PATCH_DIRECTORY IN BLOB := NULL) IS 989,990c992 < S_PATCH_DIRECTORY_ZIP, < S_INSTALLING_BUNDLE) --- > S_PATCH_DIRECTORY_ZIP) 997,998c999 < P_PATCH_DIRECTORY, < P_INSTALLING_BUNDLE); --- > P_PATCH_DIRECTORY);
71,72d70 < < 75c73 < cat="01-fc_ureads" sub="fco" in_cat_sum="Y"/> --- > cat="1-ureads" sub="fco" in_cat_sum="Y"/> 77c75 < cat="01-fc_ureads" sub="fco" in_cat_sum="Y"/> --- > cat="1-ureads" sub="fco" in_cat_sum="Y"/> 79,81c77,79 < cat="01-fc_ureads" sub="fcs" in_cat_sum="Y"/> < <stat id="204" name="fcsior" type="reqs" < cat="01-fc_ureads" sub="fcs" in_cat_sum="Y"/> --- > cat="1-ureads" sub="fcs" in_cat_sum="Y"/> > <stat id="204" name="fcsior" type="reqs" > cat="1-ureads" sub="fcs" in_cat_sum="Y"/> 83c81 < cat="01-fc_ureads" sub="fcc" in_cat_sum="Y"/> --- > cat="1-ureads" sub="fcc" in_cat_sum="Y"/> 85c83 < cat="01-fc_ureads" sub="fcc" in_cat_sum="Y"/> --- > cat="1-ureads" sub="fcc" in_cat_sum="Y"/> 87c85 < cat="01-fc_ureads" sub="fckp" in_cat_sum="Y"/> --- > cat="1-ureads" sub="fckp" in_cat_sum="Y"/> 89,100c87,96 < cat="01-fc_ureads" sub="fckp" in_cat_sum="Y"/> < <stat id="201" name="fcoiorqrm" type="reqs" cat="01-fc_ureads" sub="fco"/> < <stat id="205" name="fcsiobyra" type="bytes" cat="01-fc_ureads" sub="fcs"/> < <stat id="302" name="fcciobyelig" type="bytes" < cat="01-fc_ureads" sub="fcc"/> < <stat id="303" name="fcciobysave" type="bytes" < cat="01-fc_ureads" sub="fcc"/> < <stat id="213" name="fckpiorqrm" type="reqs" cat="01-fc_ureads" sub="fckp"/> < <stat id="196" name="fciorash" type="bytes" cat="01-fc_ureads"/> < <stat id="194" name="fciorash" type="reqs" cat="01-fc_ureads"/> < <stat id="197" name="fciorasm" type="bytes" cat="01-fc_ureads"/> < <stat id="195" name="fciorasm" type="reqs" cat="01-fc_ureads"/> --- > cat="1-ureads" sub="fckp" in_cat_sum="Y"/> > <stat id="201" name="fcoiorqrm" type="reqs" cat="1-ureads" sub="fco"/> > <stat id="205" name="fcsiobyra" type="bytes" cat="1-ureads" sub="fcs"/> > <stat id="302" name="fcciobyelig" type="bytes" cat="1-ureads" sub="fcc"/> > <stat id="303" name="fcciobysave" type="bytes" cat="1-ureads" sub="fcc"/> > <stat id="213" name="fckpiorqrm" type="reqs" cat="1-ureads" sub="fckp"/> > <stat id="196" name="fciorash" type="bytes" cat="1-ureads"/> > <stat id="194" name="fciorash" type="reqs" cat="1-ureads"/> > <stat id="197" name="fciorasm" type="bytes" cat="1-ureads"/> > <stat id="195" name="fciorasm" type="reqs" cat="1-ureads"/> 102,117c98,111 < <stat id="181" name="fciow" type="bytes" < cat="02-fc_uwrites" in_cat_sum="Y"/> < <stat id="188" name="fciow" type="reqs" < cat="02-fc_uwrites" in_cat_sum="Y"/> < <stat id="185" name="fciowf" type="bytes" cat="02-fc_uwrites"/> < <stat id="189" name="fciowf" type="reqs" cat="02-fc_uwrites"/> < <stat id="186" name="fciowow" type="bytes" cat="02-fc_uwrites"/> < <stat id="190" name="fciowow" type="reqs" cat="02-fc_uwrites"/> < <stat id="216" name="fckpiow" type="bytes" cat="02-fc_uwrites"/> < <stat id="215" name="fckpiow" type="reqs" cat="02-fc_uwrites"/> < <stat id="373" name="fclwmrw" type="reqs" cat="02-fc_uwrites"/> < <stat id="374" name="fclwnrw" type="reqs" cat="02-fc_uwrites"/> < <stat id="375" name="fclwrow" type="reqs" cat="02-fc_uwrites"/> < <stat id="376" name="fclwmrw" type="bytes" cat="02-fc_uwrites"/> < <stat id="377" name="fclwnrw" type="bytes" cat="02-fc_uwrites"/> < <stat id="378" name="fclwrow" type="bytes" cat="02-fc_uwrites"/> --- > <stat id="181" name="fciow" type="bytes" cat="2-uwrites" in_cat_sum="Y"/> > <stat id="188" name="fciow" type="reqs" cat="2-uwrites" in_cat_sum="Y"/> > <stat id="185" name="fciowf" type="bytes" cat="2-uwrites"/> > <stat id="189" name="fciowf" type="reqs" cat="2-uwrites"/> > <stat id="186" name="fciowow" type="bytes" cat="2-uwrites"/> > <stat id="190" name="fciowow" type="reqs" cat="2-uwrites"/> > <stat id="216" name="fckpiow" type="bytes" cat="2-uwrites"/> > <stat id="215" name="fckpiow" type="reqs" cat="2-uwrites"/> > <stat id="373" name="fclwmrw" type="reqs" cat="2-uwrites"/> > <stat id="374" name="fclwnrw" type="reqs" cat="2-uwrites"/> > <stat id="375" name="fclwrow" type="reqs" cat="2-uwrites"/> > <stat id="376" name="fclwmrw" type="bytes" cat="2-uwrites"/> > <stat id="377" name="fclwnrw" type="bytes" cat="2-uwrites"/> > <stat id="378" name="fclwrow" type="bytes" cat="2-uwrites"/> 119,126c113,118 < <stat id="192" name="fciordw" type="bytes" < cat="03-fc_ireads" in_cat_sum="Y"/> < <stat id="193" name="fciordw" type="reqs" < cat="03-fc_ireads" in_cat_sum="Y"/> < <stat id="314" name="fciordkwr" type="reqs" cat="03-fc_ireads"/> < <stat id="315" name="fciordkwr" type="bytes" cat="03-fc_ireads"/> < <stat id="316" name="fciowdkwr" type="reqs" cat="03-fc_ireads"/> < <stat id="317" name="fciowdkwr" type="bytes" cat="03-fc_ireads"/> --- > <stat id="192" name="fciordw" type="bytes" cat="3-ireads" in_cat_sum="Y"/> > <stat id="193" name="fciordw" type="reqs" cat="3-ireads" in_cat_sum="Y"/> > <stat id="314" name="fciordkwr" type="reqs" cat="3-ireads"/> > <stat id="315" name="fciordkwr" type="bytes" cat="3-ireads"/> > <stat id="316" name="fciowdkwr" type="reqs" cat="3-ireads"/> > <stat id="317" name="fciowdkwr" type="bytes" cat="3-ireads"/> 129,135c121,126 < cat="04-fc_iwrites" in_cat_sum="Y"/> < <stat id="191" name="fciowpop" type="reqs" < cat="04-fc_iwrites" in_cat_sum="Y"/> < <stat id="218" name="fckpiowpop" type="bytes" cat="04-fc_iwrites"/> < <stat id="217" name="fckpiowpop" type="reqs" cat="04-fc_iwrites"/> < <stat id="307" name="fcciowpop" type="bytes" cat="04-fc_iwrites"/> < <stat id="306" name="fcciowpop" type="reqs" cat="04-fc_iwrites"/> --- > cat="4-iwrites" in_cat_sum="Y"/> > <stat id="191" name="fciowpop" type="reqs" cat="4-iwrites" in_cat_sum="Y"/> > <stat id="218" name="fckpiowpop" type="bytes" cat="4-iwrites"/> > <stat id="217" name="fckpiowpop" type="reqs" cat="4-iwrites"/> > <stat id="307" name="fcciowpop" type="bytes" cat="4-iwrites"/> > <stat id="306" name="fcciowpop" type="reqs" cat="4-iwrites"/> 138c129 < cat="05-fcs_pop" in_cat_sum="Y"/> --- > cat="5-fcsrep" in_cat_sum="Y"/> 140c131 < cat="05-fcs_pop" in_cat_sum="Y"/> --- > cat="5-fcsrep" in_cat_sum="Y"/> 142c133 < cat="05-fcs_pop" in_cat_sum="Y"/> --- > cat="5-fcsrep" in_cat_sum="Y"/> 144c135 < cat="05-fcs_pop" in_cat_sum="Y"/> --- > cat="5-fcsrep" in_cat_sum="Y"/> 146c137 < cat="05-fcs_pop" in_cat_sum="Y"/> --- > cat="5-fcsrep" in_cat_sum="Y"/> 148,166c139,141 < <stat id="184" name="fciobykpow" type="bytes" cat="06-others"/> < <stat id="198" name="fciorqspc" type="reqs" cat="06-others"/> < <stat id="199" name="fciorqspcf" type="reqs" cat="06-others"/> < <!-- ram cache --> < <stat id="390" name="rcior" type="reqs" < cat="07-rc_ureads" sub="rco" in_cat_sum="Y"/> < <stat id="391" name="rcior" type="bytes" < cat="07-rc_ureads" sub="rco" in_cat_sum="Y"/> < <stat id="392" name="rciorm" type="reqs" < cat="07-rc_ureads" sub="rco" in_cat_sum="Y"/> < <stat id="393" name="rciorm" type="bytes" < cat="07-rc_ureads" sub="rco" in_cat_sum="Y"/> < <stat id="394" name="rciowpop" type="reqs" < cat="08-rc_iwrites" in_cat_sum="Y"/> < <stat id="395" name="rciowpop" type="bytes" < cat="08-rc_iwrites" in_cat_sum="Y"/> < <stat id="396" name="rcby" type="space" < cat="09-rc_spc" in_cat_sum="Y"/> < <stat id="397" name="rcbyo" type="space" cat="09-rc_spc"/> --- > <stat id="184" name="fciobykpow" type="bytes" cat="6-others"/> > <stat id="198" name="fciorqspc" type="reqs" cat="6-others"/> > <stat id="199" name="fciorqspcf" type="reqs" cat="6-others"/> 6622d6596 < nullif(cat_spc,0)/#BTOMB# cat_spcmb, 6627d6600 < nullif(spc,0)/#BTOMB# spcmb, 6633d6605 < 100*spc/decode(cat_spc,0,null,cat_spc) spc_pct, 6654c6626 < case when stat_name in ('fcoior','rcior') --- > case when stat_name = 'fcoior' 6663,6664d6634 < when stat_name = 'rcbyo' < then 100*(spc/decode(eff_denom,0,null,eff_denom)) 6671d6640 < nullif(cat_spc_s,0)/#BTOMB# cat_spcmb_s, 6675d6643 < nullif(spc_s,0)/#BTOMB# spcmb_s, 6681d6648 < 100*spc_s/decode(cat_spc_s,0,null,cat_spc_s) spc_pct_s, 6702c6669 < case when stat_name in ('fcoior','rcior') --- > case when stat_name = 'fcoior' 6712,6713d6678 < when stat_name = 'rcbyo' < then 100*(spc_s/decode(eff_denom_s,0,null,eff_denom_s)) 6716,6726c6681,6684 < case when stat_category != '09-rc_spc' < then dense_rank() over (partition by stat_category < order by cat_rqps desc, < cat_byps desc, < cn.cell_name) < else dense_rank() over (partition by stat_category < order by cat_spc desc, < cat_rqps desc, < cat_byps desc, < cn.cell_name) < end rn, --- > dense_rank() over (partition by stat_category > order by cat_rqps desc, > cat_byps desc, > cn.cell_name) rn, 6731d6688 < cat_spc desc, 6735c6692 < rq, rqps, bytes, byps, spc, --- > rq, rqps, bytes, byps, 6762,6763d6718 < sum(cat_spc) < over (partition by cell_hash, stat_category) cat_spc, 6769d6723 < sum(spc ) over (partition by stat_name) spc_s, 6791,6792d6744 < sum(cat_spc) < over (partition by stat_category) cat_spc_s, 6804d6755 < sum(decode(stat_type,'space',current_value,0)) spc, 6827d6777 < -- also denominator for space usage to calculate % 6830,6831c6780 < 'fcoiorqrm','fckpiorqrm', < 'rcior','rciorm') --- > 'fcoiorqrm','fckpiorqrm') 6836,6838d6784 < when stat_type = 'space' < and stat_name = 'rcby' < then current_value 6861,6865c6807 < end) cat_byps, < sum(case when stat_type = 'space' < and in_cat_sum = 'Y' < then current_value < end) cat_spc --- > end) cat_byps 6873,6874d6814 < -- current value < e.metric_value current_value, 6901,6902c6841 < or e.metric_id between 370 and 380 < or e.metric_id between 390 and 397) --- > or e.metric_id between 370 and 380) 9626d9564 < or max(cat_spcmb_s) is not null 9632c9570 < when category = '01-fc_ureads' --- > when category = '1-ureads' 9644c9582 < case when category = '02-fc_uwrites' --- > case when category = '2-uwrites' 9677d9614 < max(cat_spcmb_s) cat_spcmb_s, 9687,9688c9624 < or mb_s is not null < or spcmb_s is not null) --- > or mb_s is not null) 9700d9635 < round(spcmb_s,2) as "spcmb", 9723,9725c9658 < 'fccior', < 'rcior', < 'rcbyo') --- > 'fccior') 9729,9730c9662,9663 < '02-fc_uwrites',round(rq_pct_s,2)) < as "rqpct", --- > '2-uwrites',round(rq_pct_s,2)) > as "rqpct", 9732,9733c9665,9666 < '02-fc_uwrites',round(by_pct_s,2)) < as "mbpct")) --- > '2-uwrites',round(by_pct_s,2)) > as "mbpct")) 9739,9740c9672 < or max(cat_mb) is not null < or max(cat_spcmb) is not null) --- > or max(cat_mb) is not null) 9746c9678 < when category = '01-fc_ureads' --- > when category = '1-ureads' 9759d9690 < or spcmb is not null 9771d9701 < round(spcmb,2) as "spcmb", 9793,9795c9723 < 'fccior', < 'rcior', < 'rcbyo') --- > 'fccior') 9799c9727 < '02-fc_uwrites',round(rq_pct,2)) --- > '2-uwrites',round(rq_pct,2)) 9802c9730 < '02-fc_uwrites',round(by_pct,2)) --- > '2-uwrites',round(by_pct,2)) 9808c9736 < when category = '02-fc_uwrites' --- > when category = '2-uwrites' 9833c9761,9766 < substr(category,4) cat_disp --- > decode(category,'1-ureads','fc_ureads', > '2-uwrites','fc_uwrites', > '3-ireads','fc_ireads', > '4-iwrites','fc_iwrites', > '5-fcsrep','fcs_pop', > substr(category,3)) cat_disp 17771,17921d17703 < SQLSTMT_FLASH_CACHE_BASE CONSTANT VARCHAR2(32767) := q'[ < select nvl(sp1.rn, sp2.rn) rn, < nvl(sp1.stat_name, sp2.stat_name) stat_name, < nvl(sp1.stat_category, sp2.stat_category) < stat_category, < substr(nvl(sp1.stat_category, < sp2.stat_category),4) disp_category, < least(nvl(sp1.rn_stat,sp2.rn_stat), < nvl(sp2.rn_stat,sp1.rn_stat)) rn_stat, < sp1.id sp1_id, sp2.id sp2_id, < sp1.cell_name sp1_cell_name, < sp2.cell_name sp2_cell_name, < sp1.num_cells sp1_num_cells, < sp2.num_cells sp2_num_cells, < sp1.rq sp1_rq, sp2.rq sp2_rq, < sp1.mb sp1_mb, sp2.mb sp2_mb, < sp1.rqps sp1_rqps, sp2.rqps sp2_rqps, < sp1.mbps sp1_mbps, sp2.mbps sp2_mbps, < sp1.spcmb sp1_spcmb, sp2.spcmb sp2_spcmb, < sp1.cat_rq sp1_cat_rq, < sp2.cat_rq sp2_cat_rq, < sp1.cat_rqps sp1_cat_rqps, < sp2.cat_rqps sp2_cat_rqps, < sp1.cat_mb sp1_cat_mb, < sp2.cat_mb sp2_cat_mb, < sp1.cat_mbps sp1_cat_mbps, < sp2.cat_mbps sp2_cat_mbps, < sp1.cat_spcmb sp1_cat_spcmb, < sp2.cat_spcmb sp2_cat_spcmb, < sp1.fciorqwp sp1_fciorqwp, < sp2.fciorqwp sp2_fciorqwp, < sp1.fciombwp sp1_fciombwp, < sp2.fciombwp sp2_fciombwp, < sp1.fciorqwp_ps sp1_fciorqwp_ps, < sp2.fciorqwp_ps sp2_fciorqwp_ps, < sp1.fciombwp_ps sp1_fciombwp_ps, < sp2.fciombwp_ps sp2_fciombwp_ps, < -- compute the values here < case when sp1.stat_name in ('fcoior','fckpior') < then sp1.eff_denom - sp1.rq < when sp1.stat_name in ('fcsior','fccior') < then sp1.eff_denom/#BTOMB# -- convert to mb < end sp1_eff_denom, < case when sp2.stat_name in ('fcoior','fckpior') < then sp2.eff_denom - sp2.rq < when sp2.stat_name in ('fcsior','fccior') < then sp2.eff_denom/#BTOMB# -- convert to mb < end sp2_eff_denom, < sp1.fcciobysave/#BTOMB# sp1_fcciombsave, < sp2.fcciobysave/#BTOMB# sp2_fcciombsave, < -- also get per second values < (case when sp1.stat_name in ('fcoior','fckpior') < then sp1.eff_denom - sp1.rq < when sp1.stat_name in ('fcsior','fccior') < then sp1.eff_denom/#BTOMB# -- convert to mb < end)/:duration1 sp1_eff_denom_ps, < (case when sp2.stat_name in ('fcoior','fckpior') < then sp2.eff_denom - sp2.rq < when sp2.stat_name in ('fcsior','fccior') < then sp2.eff_denom/#BTOMB# -- convert to mb < end)/:duration2 sp2_eff_denom_ps, < sp1.fcciobysave/#BTOMB#/:duration1 < sp1_fcciombsave_ps, < sp2.fcciobysave/#BTOMB#/:duration2 < sp2_fcciombsave_ps, < sp1.eff sp1_eff, sp2.eff sp2_eff, < -- calculate % diff for per second values < 100*(sp2.cat_rqps - sp1.cat_rqps)/ < decode(sp1.cat_rqps,0,null,sp1.cat_rqps) < cat_rqps_diff, < 100*(sp2.cat_mbps - sp1.cat_mbps)/ < decode(sp1.cat_mbps,0,null,sp1.cat_mbps) < cat_mbps_diff, < 100*(sp2.rqps - sp1.rqps)/ < decode(sp1.rqps,0,null,sp1.rqps) rqps_diff, < 100*(sp2.mbps - sp1.mbps)/ < decode(sp1.mbps,0,null,sp1.mbps) mbps_diff, < 100*(sp2.spcmb - sp1.spcmb)/ < decode(sp1.spcmb,0,null,sp1.spcmb) spcmb_diff, < sp2.eff - sp1.eff eff_diff, < -- get data for 'All' node < sp1.rq_s sp1_rq_s, sp2.rq_s sp2_rq_s, < sp1.mb_s sp1_mb_s, sp2.mb_s sp2_mb_s, < sp1.rqps_s sp1_rqps_s, sp2.rqps_s sp2_rqps_s, < sp1.mbps_s sp1_mbps_s, sp2.mbps_s sp2_mbps_s, < sp1.spcmb_s sp1_spcmb_s, sp2.spcmb_s sp2_spcmb_s, < sp1.cat_rq_s sp1_cat_rq_s, < sp2.cat_rq_s sp2_cat_rq_s, < sp1.cat_rqps_s sp1_cat_rqps_s, < sp2.cat_rqps_s sp2_cat_rqps_s, < sp1.cat_mb_s sp1_cat_mb_s, < sp2.cat_mb_s sp2_cat_mb_s, < sp1.cat_mbps_s sp1_cat_mbps_s, < sp2.cat_mbps_s sp2_cat_mbps_s, < sp1.cat_spcmb_s sp1_cat_spcmb_s, < sp2.cat_spcmb_s sp2_cat_spcmb_s, < sp1.fciorqwp_s sp1_fciorqwp_s, < sp2.fciorqwp_s sp2_fciorqwp_s, < sp1.fciombwp_s sp1_fciombwp_s, < sp2.fciombwp_s sp2_fciombwp_s, < sp1.fciorqwp_ps_s sp1_fciorqwp_ps_s, < sp2.fciorqwp_ps_s sp2_fciorqwp_ps_s, < sp1.fciombwp_ps_s sp1_fciombwp_ps_s, < sp2.fciombwp_ps_s sp2_fciombwp_ps_s, < -- compute values < case when sp1.stat_name in ('fcoior','fckpior') < then sp1.eff_denom_s - sp1.rq_s < when sp1.stat_name in ('fcsior','fccior') < then sp1.eff_denom_s/#BTOMB# < end sp1_eff_denom_s, < case when sp2.stat_name in ('fcoior','fckpior') < then sp2.eff_denom_s - sp2.rq_s < when sp2.stat_name in ('fcsior','fccior') < then sp2.eff_denom_s/#BTOMB# < end sp2_eff_denom_s, < sp1.fcciobysave_s/#BTOMB# sp1_fcciobysave_s, < sp2.fcciobysave_s/#BTOMB# sp2_fcciobysave_s, < (case when sp1.stat_name in ('fcoior','fckpior') < then sp1.eff_denom_s - sp1.rq_s < when sp1.stat_name in ('fcsior','fccior') < then sp1.eff_denom_s/#BTOMB# < end)/:duration1 sp1_eff_denom_ps_s, < (case when sp2.stat_name in ('fcoior','fckpior') < then sp2.eff_denom_s - sp2.rq_s < when sp2.stat_name in ('fcsior','fccior') < then sp2.eff_denom_s/#BTOMB# < end)/:duration2 sp2_eff_denom_ps_s, < sp1.fcciobysave_s/#BTOMB#/:duration1 < sp1_fcciombsave_ps_s, < sp2.fcciobysave_s/#BTOMB#/:duration2 < sp2_fcciombsave_ps_s, < sp1.eff sp1_eff_s, sp2.eff_s sp2_eff_s, < -- calculate % diff for per second values < 100*(sp2.cat_rqps_s - sp1.cat_rqps_s)/ < decode(sp1.cat_rqps_s,0,null,sp1.cat_rqps_s) < cat_rqps_s_diff, < 100*(sp2.cat_mbps_s - sp1.cat_mbps_s)/ < decode(sp1.cat_mbps_s,0,null,sp1.cat_mbps_s) < cat_mbps_s_diff, < 100*(sp2.rqps_s - sp1.rqps_s)/ < decode(sp1.rqps_s,0,null,sp1.rqps_s) rqps_s_diff, < 100*(sp2.mbps_s - sp1.mbps_s)/ < decode(sp1.mbps_s,0,null,sp1.mbps_s) mbps_s_diff, < 100*(sp2.spcmb_s - sp1.spcmb_s)/ < decode(sp1.spcmb_s,0,null,sp1.spcmb_s) < spcmb_s_diff, < sp2.eff_s - sp1.eff_s eff_s_diff < from sp1 full outer join sp2 < on sp1.rn = sp2.rn < and sp1.stat_category = sp2.stat_category < and sp1.stat_name = sp2.stat_name]'; 17925,17926c17707 < sp2 as ( #BASE_QUERY_SP2# ), < base_stats as (#BASE_STATS#) --- > sp2 as ( #BASE_QUERY_SP2# ) 17932c17713 < xmlattributes('dd_' || max(disp_category) as "type"), --- > xmlattributes('dd_fc_' || max(disp_category) as "type"), 17946,17948c17727 < or max(sp2_cat_mb_s) is not null < or max(sp1_cat_spcmb_s) is not null < or max(sp2_cat_spcmb_s) is not null) --- > or max(sp2_cat_mb_s) is not null) 17958,17960c17737 < or max(sp2_cat_mb_s) is not null < or max(sp1_cat_spcmb_s) is not null < or max(sp2_cat_spcmb_s) is not null) --- > or max(sp2_cat_mb_s) is not null) 17979c17756 < when stat_category = '01-fc_ureads' --- > when stat_category = '1-ureads' 18013,18022d17789 < end, < case < when max(cat_rqps_s_diff) is not null < or max(cat_mbps_s_diff) is not null < then xmlelement("period", < xmlattributes('diff' as "id", < round(max(cat_rqps_s_diff),2) < as "rqps", < round(max(cat_mbps_s_diff),2) < as "mbps")) 18028c17795 < when stat_category = '02-fc_uwrites' --- > when stat_category = '2-uwrites' 18070,18071d17836 < or max(sp1_cat_spcmb) is not null < or max(sp2_cat_spcmb) is not null 18088c17853 < when stat_category='01-fc_ureads' --- > when stat_category='1-ureads' 18122,18131d17886 < end, < case < when max(cat_rqps_diff) is not null < or max(cat_mbps_diff) is not null < then xmlelement("period", < xmlattributes('diff' as "id", < round(max(cat_rqps_diff),2) < as "rqps", < round(max(cat_mbps_diff),2) < as "mbps")) 18137c17892 < when stat_category='02-fc_uwrites' --- > when stat_category='2-uwrites' 18186,18188d17940 < sp1_cat_spcmb_s, sp2_cat_spcmb_s, < cat_rqps_s_diff, < cat_mbps_s_diff, 18199,18201c17951 < or sp2_mb_s is not null < or sp1_spcmb_s is not null < or sp2_spcmb_s is not null) --- > or sp2_mb_s is not null) 18207d17956 < or sp1_spcmb_s is not null 18214d17962 < round(sp1_spcmb_s,2) as "spcmb", 18232,18234c17980 < 'fckpior',sp1_eff_s, < 'rcior', sp1_eff_s, < 'rcbyo',sp1_eff_s),2) --- > 'fckpior',sp1_eff_s),2) 18240d17985 < or sp2_spcmb_s is not null 18247d17991 < round(sp2_spcmb_s,2) as "spcmb", 18265,18267c18009 < 'fckpior',sp2_eff_s, < 'rcior',sp2_eff_s, < 'rcbyo',sp2_eff_s),2) --- > 'fckpior',sp2_eff_s),2) 18269,18286d18010 < end, < case < when rqps_s_diff is not null < or mbps_s_diff is not null < or spcmb_s_diff is not null < then xmlelement("period", < xmlattributes('diff' as "id", < round(rqps_s_diff,2) as "rqps", < round(mbps_s_diff,2) as "mbps", < round(spcmb_s_diff,2) as "spcmb", < round(decode(stat_name, < 'fcoior',eff_s_diff, < 'fcsior',eff_s_diff, < 'fccior',eff_s_diff, < 'fckpior',eff_s_diff, < 'rcior',eff_s_diff, < 'rcbyo',eff_s_diff),2) < as "eff")) 18294,18296d18017 < sp1_cat_spcmb, sp2_cat_spcmb, < cat_rqps_diff, < cat_mbps_diff, 18307,18308d18027 < or sp1_spcmb is not null < or sp2_spcmb is not null 18314d18032 < or sp1_spcmb is not null 18321d18038 < round(sp1_spcmb,2) as "spcmb", 18339,18341c18056 < 'fckpior',sp1_eff, < 'rcior',sp1_eff, < 'rcbyo',sp1_eff),2) --- > 'fckpior',sp1_eff),2) 18347d18061 < or sp2_spcmb is not null 18354,18355c18068 < round(sp2_spcmb,2) as "spcmb", < -- additional efficiency --- > -- additional efficienacy 18372,18392c18085 < 'fckpior',sp2_eff, < 'rcior',sp2_eff, < 'rcbyo',sp2_eff),2) < as "eff")) < end, < case < when rqps_diff is not null < or mbps_diff is not null < or spcmb_diff is not null < then xmlelement("period", < xmlattributes('diff' as "id", < round(rqps_diff,2) as "rqps", < round(mbps_diff,2) as "mbps", < round(spcmb_diff,2) as "spcmb", < round(decode(stat_name, < 'fcoior',eff_diff, < 'fcsior',eff_diff, < 'fccior',eff_diff, < 'fckpior',eff_diff, < 'rcior',eff_diff, < 'rcbyo',eff_diff),2) --- > 'fckpior',sp2_eff),2) 18396c18089,18205 < from base_stats ) --- > from ( > select nvl(sp1.rn, sp2.rn) rn, > nvl(sp1.stat_name, sp2.stat_name) stat_name, > nvl(sp1.stat_category, sp2.stat_category) > stat_category, > substr(nvl(sp1.stat_category, > sp2.stat_category),3) disp_category, > least(nvl(sp1.rn_stat,sp2.rn_stat), > nvl(sp2.rn_stat,sp1.rn_stat)) rn_stat, > sp1.id sp1_id, sp2.id sp2_id, > sp1.cell_name sp1_cell_name, > sp2.cell_name sp2_cell_name, > sp1.num_cells sp1_num_cells, > sp2.num_cells sp2_num_cells, > sp1.rq sp1_rq, sp2.rq sp2_rq, > sp1.mb sp1_mb, sp2.mb sp2_mb, > sp1.rqps sp1_rqps, sp2.rqps sp2_rqps, > sp1.mbps sp1_mbps, sp2.mbps sp2_mbps, > sp1.cat_rq sp1_cat_rq, > sp2.cat_rq sp2_cat_rq, > sp1.cat_rqps sp1_cat_rqps, > sp2.cat_rqps sp2_cat_rqps, > sp1.cat_mb sp1_cat_mb, > sp2.cat_mb sp2_cat_mb, > sp1.cat_mbps sp1_cat_mbps, > sp2.cat_mbps sp2_cat_mbps, > sp1.fciorqwp sp1_fciorqwp, > sp2.fciorqwp sp2_fciorqwp, > sp1.fciombwp sp1_fciombwp, > sp2.fciombwp sp2_fciombwp, > sp1.fciorqwp_ps sp1_fciorqwp_ps, > sp2.fciorqwp_ps sp2_fciorqwp_ps, > sp1.fciombwp_ps sp1_fciombwp_ps, > sp2.fciombwp_ps sp2_fciombwp_ps, > -- compute the values here > case when sp1.stat_name in ('fcoior','fckpior') > then sp1.eff_denom - sp1.rq > when sp1.stat_name in ('fcsior','fccior') > then sp1.eff_denom/#BTOMB# -- convert to mb > end sp1_eff_denom, > case when sp2.stat_name in ('fcoior','fckpior') > then sp2.eff_denom - sp2.rq > when sp2.stat_name in ('fcsior','fccior') > then sp2.eff_denom/#BTOMB# -- convert to mb > end sp2_eff_denom, > sp1.fcciobysave/#BTOMB# sp1_fcciombsave, > sp2.fcciobysave/#BTOMB# sp2_fcciombsave, > -- also get per second values > (case when sp1.stat_name in ('fcoior','fckpior') > then sp1.eff_denom - sp1.rq > when sp1.stat_name in ('fcsior','fccior') > then sp1.eff_denom/#BTOMB# -- convert to mb > end)/:duration1 sp1_eff_denom_ps, > (case when sp2.stat_name in ('fcoior','fckpior') > then sp2.eff_denom - sp2.rq > when sp2.stat_name in ('fcsior','fccior') > then sp2.eff_denom/#BTOMB# -- convert to mb > end)/:duration2 sp2_eff_denom_ps, > sp1.fcciobysave/#BTOMB#/:duration1 > sp1_fcciombsave_ps, > sp2.fcciobysave/#BTOMB#/:duration2 > sp2_fcciombsave_ps, > sp1.eff sp1_eff, sp2.eff sp2_eff, > -- get data for 'All' node > sp1.rq_s sp1_rq_s, sp2.rq_s sp2_rq_s, > sp1.mb_s sp1_mb_s, sp2.mb_s sp2_mb_s, > sp1.rqps_s sp1_rqps_s, sp2.rqps_s sp2_rqps_s, > sp1.mbps_s sp1_mbps_s, sp2.mbps_s sp2_mbps_s, > sp1.cat_rq_s sp1_cat_rq_s, > sp2.cat_rq_s sp2_cat_rq_s, > sp1.cat_rqps_s sp1_cat_rqps_s, > sp2.cat_rqps_s sp2_cat_rqps_s, > sp1.cat_mb_s sp1_cat_mb_s, > sp2.cat_mb_s sp2_cat_mb_s, > sp1.cat_mbps_s sp1_cat_mbps_s, > sp2.cat_mbps_s sp2_cat_mbps_s, > sp1.fciorqwp_s sp1_fciorqwp_s, > sp2.fciorqwp_s sp2_fciorqwp_s, > sp1.fciombwp_s sp1_fciombwp_s, > sp2.fciombwp_s sp2_fciombwp_s, > sp1.fciorqwp_ps_s sp1_fciorqwp_ps_s, > sp2.fciorqwp_ps_s sp2_fciorqwp_ps_s, > sp1.fciombwp_ps_s sp1_fciombwp_ps_s, > sp2.fciombwp_ps_s sp2_fciombwp_ps_s, > -- compute values > case when sp1.stat_name in ('fcoior','fckpior') > then sp1.eff_denom_s - sp1.rq_s > when sp1.stat_name in ('fcsior','fccior') > then sp1.eff_denom_s/#BTOMB# > end sp1_eff_denom_s, > case when sp2.stat_name in ('fcoior','fckpior') > then sp2.eff_denom_s - sp2.rq_s > when sp2.stat_name in ('fcsior','fccior') > then sp2.eff_denom_s/#BTOMB# > end sp2_eff_denom_s, > sp1.fcciobysave_s/#BTOMB# sp1_fcciobysave_s, > sp2.fcciobysave_s/#BTOMB# sp2_fcciobysave_s, > (case when sp1.stat_name in ('fcoior','fckpior') > then sp1.eff_denom_s - sp1.rq_s > when sp1.stat_name in ('fcsior','fccior') > then sp1.eff_denom_s/#BTOMB# > end)/:duration1 sp1_eff_denom_ps_s, > (case when sp2.stat_name in ('fcoior','fckpior') > then sp2.eff_denom_s - sp2.rq_s > when sp2.stat_name in ('fcsior','fccior') > then sp2.eff_denom_s/#BTOMB# > end)/:duration2 sp2_eff_denom_ps_s, > sp1.fcciobysave_s/#BTOMB#/:duration1 > sp1_fcciombsave_ps_s, > sp2.fcciobysave_s/#BTOMB#/:duration2 > sp2_fcciombsave_ps_s, > sp1.eff sp1_eff_s, sp2.eff_s sp2_eff_s > from sp1 full outer join sp2 > on sp1.rn = sp2.rn > and sp1.stat_category = sp2.stat_category > and sp1.stat_name = sp2.stat_name > )) 18404d18212 < L_QRY := REPLACE(L_QRY,'#BASE_STATS#',SQLSTMT_FLASH_CACHE_BASE);
57,58c57 < ' where ob.type# in (1,20) AND ob.owner# not in ' || < '(select user# from user$ ' || --- > ' where ob.owner# not in (select user# from user$ ' || 93,94c92 < ' where ob.type# in (1,20) AND ob.owner# not in ' || < '(select user# from user$ ' || --- > ' where ob.owner# not in (select user# from user$ ' ||
448c448 < TNAME := TRIM(BOTH ' ' FROM INST_TAB(I).INST_NAME); --- > TNAME := SUBSTR(INST_TAB(I).INST_NAME,1,INSTR(INST_TAB(I).INST_NAME,' ')-1); 1618c1618 < TNAME := TRIM(BOTH ' ' FROM INST_TAB(I).INST_NAME); --- > TNAME := SUBSTR(INST_TAB(I).INST_NAME,1,INSTR(INST_TAB(I).INST_NAME,' ')-1); 1847c1847 < TNAME := TRIM(BOTH ' ' FROM INST_TAB(I).INST_NAME); --- > TNAME := SUBSTR(INST_TAB(I).INST_NAME,1,INSTR(INST_TAB(I).INST_NAME,' ')-1); 2837c2837 < TNAME := TRIM(BOTH ' ' FROM INST_TAB(I).INST_NAME); --- > TNAME := SUBSTR(INST_TAB(I).INST_NAME,1,INSTR(INST_TAB(I).INST_NAME,' ')-1);
403a404 > 420c421 < INSTALLED_BUNDLE := REGISTRY_ID; --- > INSTALLED_BUNDLE := 0;
Useful Links:
- Download Assistant: MOS Note: 2118136.2
- 2018 April CPU: http://www.oracle.com/technetwork/security-advisory/cpuapr2018-3678067.html
Have you enjoyed? Please leave a comment or give a 👍!