This post is also available in:
Português
Starting today I will quarterly write 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 170117?
VERSION PATCH OWNER TYPE TOTAL -------------------- -------- ------------------------------ ------------------------------ ---------- 11.2.0.4 BP SYS PACKAGE BODY 1 11.2.0.4 PSU SYS PACKAGE BODY 1 12.1.0.2 BP SYS PACKAGE BODY 3 12.1.0.2 BP SYS PROCEDURE 1 12.1.0.2 PSU & BP SYS PACKAGE BODY 1 12.2.0.1 RU CTXSYS PACKAGE BODY 3 12.2.0.1 RU LBACSYS PACKAGE BODY 1 12.2.0.1 RU SYS PACKAGE 1 12.2.0.1 RU SYS PACKAGE BODY 4 12.2.0.1 RUR SYS PACKAGE BODY 2
And created?
VERSION PATCH OWNER TYPE TOTAL -------------------- -------- ------------------------------ ------------------------------ ---------- 11.2.0.4 OJVM PUBLIC SYNONYM 49 12.1.0.2 BP SYS VIEW 5 12.1.0.2 OJVM PUBLIC SYNONYM 49 12.2.0.1 RU SYS PACKAGE 1 12.2.0.1 RU SYS PACKAGE BODY 1 12.2.0.1 RUR GSMADMIN_INTERNAL PACKAGE 2 12.2.0.1 RUR GSMADMIN_INTERNAL PACKAGE BODY 2 12.2.0.1 RUR PUBLIC SYNONYM 1 12.2.0.1 RUR SYS PACKAGE 1 12.2.0.1 RUR SYS PACKAGE BODY 1 12.2.0.1 RUR SYS PROCEDURE 3
So which are exactly the objects modified by 170117?
VERSION PATCH OWNER TYPE NAME -------------------- -------- ------------------------------ ------------------------------ ------------------------------ 11.2.0.4 BP SYS PACKAGE BODY DBMS_RCVMAN 11.2.0.4 PSU SYS PACKAGE BODY DBMS_RCVMAN 12.1.0.2 BP SYS PACKAGE BODY PRVTEMX_CELL 12.1.0.2 BP SYS PACKAGE BODY DBMS_OPTIM_BUNDLE 12.1.0.2 BP SYS PROCEDURE DBMS_FEATURE_ADAPTIVE_PLANS 12.1.0.2 BP SYS PACKAGE BODY DBMS_STATS 12.1.0.2 PSU & BP SYS PACKAGE BODY DBMS_AQADM_SYS 12.2.0.1 RU CTXSYS PACKAGE BODY DRVXTAB 12.2.0.1 RU CTXSYS PACKAGE BODY DRIPARSE 12.2.0.1 RU CTXSYS PACKAGE BODY DRIXMD 12.2.0.1 RU LBACSYS PACKAGE BODY LBAC_EVENTS 12.2.0.1 RU SYS PACKAGE BODY PRVTEMX_CELL 12.2.0.1 RU SYS PACKAGE BODY LOGMNR_DICT_CACHE 12.2.0.1 RU SYS PACKAGE BODY DBMS_RCVMAN 12.2.0.1 RU SYS PACKAGE DBMS_RCVMAN 12.2.0.1 RU SYS PACKAGE BODY DBMS_AWR_REPORT_LAYOUT 12.2.0.1 RUR SYS PACKAGE BODY PRVTEMX_CELL 12.2.0.1 RUR SYS PACKAGE BODY DBMS_RCVMAN
11.2.0.4
What changed for each?
DBMS_RCVMAN (BP):
7766,7767c7766 < SELECT /*+ LEADING(bs bp bdf) USE_HASH(bp) USE_HASH(bdf) */ < BACKUPSET_CON_T TYPE_CON, --- > SELECT BACKUPSET_CON_T TYPE_CON, 13528,13529c13527 < SELECT /*+ LEADING(bs bp bdf) USE_HASH(bp) USE_HASH(bdf) */ < BDF.CHECKPOINT_CHANGE# BCKSCN, BP.TAG --- > SELECT BDF.CHECKPOINT_CHANGE# BCKSCN, BP.TAG 13555,13556c13553 < (SELECT /*+ LEADING(bs bp bdf) USE_HASH(bp) USE_HASH(bdf) */ < BCF.CHECKPOINT_CHANGE# CFSCN --- > (SELECT BCF.CHECKPOINT_CHANGE# CFSCN
DBMS_RCVMAN (PSU):
7766,7767c7766 < SELECT /*+ LEADING(bs bp bdf) USE_HASH(bp) USE_HASH(bdf) */ < BACKUPSET_CON_T TYPE_CON, --- > SELECT BACKUPSET_CON_T TYPE_CON, 13526,13527c13525 < SELECT /*+ LEADING(bs bp bdf) USE_HASH(bp) USE_HASH(bdf) */ < BDF.CHECKPOINT_CHANGE# BCKSCN, BP.TAG --- > SELECT BDF.CHECKPOINT_CHANGE# BCKSCN, BP.TAG 13553,13554c13551 < (SELECT /*+ LEADING(bs bp bdf) USE_HASH(bp) USE_HASH(bdf) */ < BCF.CHECKPOINT_CHANGE# CFSCN --- > (SELECT BCF.CHECKPOINT_CHANGE# CFSCN
P.S: DBMS_RCVMAN looks similar for BP and PSU, however the code are not exactly the same for the original package (note the modified line number)
12.1.0.2
What changed for each?
DBMS_AQADM_SYS:
9715d9714
< ROWID_SIZE NUMBER;
9830,9843d9828
<
<
<
< STMT_BUF := 'select (SUM(column_length + 3) + 7) FROM ' ||
< ' dba_ind_columns ic, ' ||
< ' dba_indexes i WHERE ic.index_name = i.index_name AND' ||
< ' ic.index_owner = ' || DBMS_ASSERT.ENQUOTE_LITERAL(QT_SCHEMA) ||
< ' and ic.index_owner = i.owner AND ' ||
< ' i.index_type = ' || DBMS_ASSERT.ENQUOTE_LITERAL( 'IOT - TOP') ||
< ' AND ic.table_name = ' ||
< DBMS_ASSERT.ENQUOTE_LITERAL('AQ$_' || QT_NAME || '_H');
<
< EXECUTE IMMEDIATE STMT_BUF INTO ROWID_SIZE;
<
9845,9847c9830,9831
< ' (row_id urowid(' || ROWID_SIZE || ' ) PRIMARY KEY, ' ||
< ' dequeue_user ' || OLD_TYPE || ')';
<
---
> ' (row_id urowid PRIMARY KEY, dequeue_user ' ||
> OLD_TYPE || ')';
9921,9924c9905,9907
< STMT_BUF := ' CREATE TABLE ' || TEMP_TAB ||
< ' (row_id rowid PRIMARY KEY, dequeue_user ' ||
< OLD_TYPE || ')';
<
---
> STMT_BUF := 'CREATE TABLE ' || TEMP_TAB ||
> ' (row_id urowid PRIMARY KEY, dequeue_user ' ||
> OLD_TYPE || ')';
DBMS_OPTIM_BUNDLE:
1549c1549 < VALID BOOLEAN := FALSE; --- > VALID NUMBER := 0; 1566c1566 < VALID := TRUE; --- > VALID := 1; 1576c1576 < VALID := TRUE; --- > VALID := 1; 1581,1584c1581,1582 < IF VALID = FALSE THEN < DBMS_OUTPUT.PUT_LINE(CHR(10) || 'Either invalid bundle-id or no fixes ' < || 'with fix_controls added in corresponding bundle'); < CLEANUP; --- > IF VALID = 0 THEN > RAISE_APPLICATION_ERROR(-20001, 'Invalid BundleId');
DBMS_STATS:
323,329d322
<
<
<
< DSC_22652097_INACTIVE CONSTANT BINARY_INTEGER := 1;
< DSC_21171382_INACTIVE CONSTANT BINARY_INTEGER := 2;
< DSC_20243268_INACTIVE CONSTANT BINARY_INTEGER := 4;
<
2808,2809d2800
< INACTIVE_PATCH NUMBER := DBMS_SQLDIAG.GET_FIX_CONTROL(26664361);
< AUTO_STAT_EXT_DEF SYS.OPTSTAT_HIST_CONTROL$.SPARE4%TYPE;
2812,2822d2802
<
<
<
<
<
< IF (BITAND(INACTIVE_PATCH, DSC_21171382_INACTIVE) != 0) THEN
< AUTO_STAT_EXT_DEF := 'OFF';
< ELSE
< AUTO_STAT_EXT_DEF := 'ON';
< END IF;
<
2920,2934c2900
< PARRECCONSTRUCT('STAT_CATEGORY', 'OBJECT_STATS', NULL, 1, TRUE),
<
<
<
<
<
<
<
<
<
<
<
<
<
< PARRECCONSTRUCT('AUTO_STAT_EXTENSIONS', AUTO_STAT_EXT_DEF, NULL, 1, TRUE)
---
> PARRECCONSTRUCT('STAT_CATEGORY', 'OBJECT_STATS', NULL, 1, TRUE)
3194,3242d3159
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
< PROCEDURE DUMP_PREFS(OWNER VARCHAR2, TABNAMEU VARCHAR2) IS
< PARAMS PARARRAY;
< PVAL VARCHAR2(32000);
< BEGIN
<
<
< IF (BITAND(DBMS_STATS_INTERNAL.TRACE_LEVEL, DSC_TAB_TRC)
< != DSC_TAB_TRC) THEN
< RETURN;
< END IF;
<
<
< FILL_IN_PARAMS(PARAMS);
<
<
< DBMS_STATS_INTERNAL.DUMP_TRACE('Preferences for table ' ||
< OWNER || '.' || TABNAMEU);
<
< DBMS_STATS_INTERNAL.DUMP_TRACE(RPAD('=', 80, '='));
<
<
< FOR I IN 1..PARAMS.COUNT LOOP
< DBMS_STATS_INTERNAL.GET_PREFS(PARAMS(I).PNAME,
< PVAL, OWNER, TABNAMEU, NULL);
<
< DBMS_STATS_INTERNAL.DUMP_TRACE(
< RPAD(PARAMS(I).PNAME, 50) || '- ' || PVAL);
< END LOOP;
<
< END DUMP_PREFS;
<
33767d33683
< AUTO_STAT_EXTENSIONS VARCHAR2(50);
34063,34065d33978
<
< DUMP_PREFS(OWNER, TABNAMEU);
<
34181,34184c34094,34095
<
<
< DBMS_STATS_INTERNAL.GET_PREFS('AUTO_STAT_EXTENSIONS',
< AUTO_STAT_EXTENSIONS, NULL, NULL, TOBJN);
---
> EXTN_CNT := CREATE_EXTENDED_STATS_FOR_TAB(OWNER, TABNAMEU, FALSE, 'f',
> COMMITFREE, EXTN_REPORT);
34186,34190c34097
< IF (AUTO_STAT_EXTENSIONS = 'ON') THEN
< EXTN_CNT := CREATE_EXTENDED_STATS_FOR_TAB(OWNER, TABNAMEU, FALSE,
< 'f', COMMITFREE, EXTN_REPORT);
< TRACE_TABLE('Created ' || EXTN_CNT || ' monitored extensions');
< END IF;
---
> TRACE_TABLE('Created ' || EXTN_CNT || ' monitored extensions');
43657d43563
< GLOBAL_AUTO_STAT_EXTENSIONS VARCHAR2(50) := NULL;
43666,43668d43571
<
< GLOBAL_AUTO_STAT_EXTENSIONS := GET_PARAM('AUTO_STAT_EXTENSIONS');
<
43802,43806c43705,43706
< FROM SYS.COL_GROUP_USAGE$ CGU, OPTSTAT_USER_PREFS$ P1
< WHERE BITAND(CGU.FLAGS, 8) = 0 AND
<
< CGU.OBJ# = P1.OBJ#(+) AND P1.PNAME(+)='AUTO_STAT_EXTENSIONS' AND
< NVL(P1.VALCHAR, GLOBAL_AUTO_STAT_EXTENSIONS) = 'ON'
---
> FROM SYS.COL_GROUP_USAGE$ CGU
> WHERE BITAND(CGU.FLAGS, 8) = 0
44877,44900d44776
<
<
<
<
<
<
<
<
<
<
<
<
<
<
< PROCEDURE VALIDATE_AUTO_STAT_EXTENSIONS(
< AUTO_STAT_EXTENSIONS_VCHAR IN VARCHAR2) IS
< BEGIN
< IF (AUTO_STAT_EXTENSIONS_VCHAR NOT IN ('ON', 'OFF'))
< THEN
< RAISE_APPLICATION_ERROR(-20001, 'Illegal auto_stat_extensions ' ||
< AUTO_STAT_EXTENSIONS_VCHAR || ': must be ON | OFF');
< END IF;
< END VALIDATE_AUTO_STAT_EXTENSIONS;
<
44993,44994d44868
< WHEN 'AUTO_STAT_EXTENSIONS' THEN
< VALIDATE_AUTO_STAT_EXTENSIONS(PVALU);
45168,45171d45041
< WHEN 'AUTO_STAT_EXTENSIONS' THEN
< IF (VALCHARU IS NOT NULL) THEN
< VALIDATE_AUTO_STAT_EXTENSIONS(VALCHARU);
< END IF;
PRVTEMX_CELL:
146,174d145
< G_CC_METADATA CONSTANT CLOB := q'[<stats>
< <stat id="301" name="ccelig" type="reqs" cat="01-cc_ureads" in_cat_sum="Y"/>
< <stat id="302" name="ccelig" type="bytes" cat="01-cc_ureads" in_cat_sum="Y"/>
< <stat id="401" name="cc2ch" type="reqs" cat="01-cc_ureads"/>
< <stat id="402" name="cc2qh" type="reqs" cat="01-cc_ureads"/>
< <stat id="403" name="cc1h" type="reqs" cat="01-cc_ureads"/>
< <stat id="306" name="cciowpop" type="reqs" cat="02-cc_iwrites" in_cat_sum="Y"/>
< <stat id="307" name="cciowpop" type="bytes" cat="02-cc_iwrites"/>
< <stat id="404" name="cc1rw" type="reqs" curr="Y" cat="02-cc_iwrites"/>
< <stat id="405" name="cc2qrw" type="reqs" curr="Y" cat="02-cc_iwrites"/>
< <stat id="406" name="cc2crw" type="reqs" curr="Y" cat="02-cc_iwrites"/>
< <stat id="407" name="cc1rw" type="bytes" curr="Y" cat="02-cc_iwrites"/>
< <stat id="408" name="cc2qrw" type="bytes" curr="Y" cat="02-cc_iwrites"/>
< <stat id="409" name="cc2crw" type="bytes" curr="Y" cat="02-cc_iwrites"/>
< <stat id="410" name="cc1rw" type="reqs" cat="02-cc_iwrites"/>
< <stat id="411" name="cc2qrw" type="reqs" cat="02-cc_iwrites"/>
< <stat id="412" name="cc2crw" type="reqs" cat="02-cc_iwrites"/>
< <stat id="413" name="cc1rw" type="bytes" cat="02-cc_iwrites"/>
< <stat id="414" name="cc2qrw" type="bytes" cat="02-cc_iwrites"/>
< <stat id="415" name="cc2crw" type="bytes" cat="02-cc_iwrites"/>
< <stat id="416" name="ccevt" type="reqs" cat="02-cc_iwrites"/>
< <stat id="417" name="ccevt" type="bytes" cat="02-cc_iwrites"/>
< <stat id="418" name="ccbgpop" type="reqs" curr="Y" cat="02-cc_iwrites"/>
< <stat id="419" name="ccfail" type="reqs" curr="Y" cat="02-cc_iwrites"/>
< </stats>]';
<
<
<
<
8828,9138d8798
< FUNCTION I_COLUMNAR_CACHE_XML(
< P_DBID IN NUMBER,
< P_BID IN NUMBER,
< P_EID IN NUMBER,
< P_DURATION IN NUMBER,
< P_TOP_N IN NUMBER DEFAULT G_MAX_CELLS,
< P_SHOW_SQL IN NUMBER DEFAULT 0)
< RETURN XMLTYPE
< IS
< L_QRY CLOB;
< L_XML XMLTYPE;
< L_REPORT_SQL CLOB;
<
< SQLSTMT_HEADERS CONSTANT VARCHAR2(32767) := q'[
< var dbid number;
< var bid number;
< var eid number;
< var top_cells number;
< var duration number;
< var cc_metadata clob;
< begin
< :dbid := #DBID#;
< :bid := #BID#;
< :eid := #EID#;
< :top_cells := #TOPN#;
< :duration := #DURATION#;
< :cc_metadata := '#CC_METADATA#';
< end;
< /]';
<
< SQLSTMT_COLUMNAR_CACHE CONSTANT VARCHAR2(32767) := q'[
< with cell_stats as (
< select s.dbid, s.cell_hash, stat_category, stat_name, cell_name,
< nullif(rq,0) rq, nullif(rqps,0) rqps, nullif(rqcurr,0) rqcurr,
< nullif(bytes,0)/#BTOMB# mb,
< nullif(byps,0)/#BTOMB# mbps,
< nullif(bycurr,0)/#BTOMB# mbcurr,
< -- calculate efficiency
< case when stat_name in ('cc2ch','cc2qh', 'cc1h')
< then 100*(rq/decode(cat_rq,0,null,cat_rq))
< end eff,
< -- category totals
< nullif(cat_rq,0) cat_rq,
< nullif(cat_rqps,0) cat_rqps,
< nullif(cat_rqcurr,0) cat_rqcurr,
< -- get 'All' node
< nullif(rq_s,0) rq_s, nullif(rqps_s,0) rqps_s,
< nullif(rqcurr_s,0) rqcurr_s,
< nullif(by_s,0)/#BTOMB# mb_s,
< nullif(byps_s,0)/#BTOMB# mbps_s,
< nullif(bycurr_s,0)/#BTOMB# mbcurr_s,
< -- calculate efficiency
< case when stat_name in ('cc2ch','cc2qh', 'cc1h')
< then 100*(rq_s/decode(cat_rq_s,0,null,cat_rq_s))
< end eff_s,
< nullif(cat_rq_s,0) cat_rq_s,
< nullif(cat_rqps_s,0) cat_rqps_s,
< nullif( cat_rqcurr_s,0) cat_rqcurr_s,
< nullif(cat_by_s,0)/#BTOMB# cat_mb_s,
< nullif(cat_byps_s,0)/#BTOMB# cat_mbps_s,
< nullif(cat_bycurr_s,0)/#BTOMB# cat_curr_s,
< -- cell rank by cat_value
< dense_rank() over (partition by stat_category
< order by cat_rq desc nulls last,
< cat_by desc nulls last,
< cn.cell_name)
< rn,
< -- first occurrence of stat
< row_number()
< over (partition by stat_name
< order by cat_rq desc nulls last, cell_name) rn_stat,
< num_cells
< from dba_hist_cell_name cn,
< ( -- get category totals (denominator for efficiency)
< select dbid, cell_hash, stat_name,
< stat_category,
< rq, rqps, rqcurr, bytes, byps, bycurr,
< -- get per cell total for the category
< sum(cat_rq)
< over (partition by cell_hash, stat_category) cat_rq,
< sum(cat_rqps)
< over (partition by cell_hash, stat_category)
< cat_rqps,
< sum(cat_rqcurr)
< over (partition by cell_hash, stat_category)
< cat_rqcurr,
< sum(cat_by)
< over (partition by cell_hash, stat_category) cat_by,
< sum(cat_byps)
< over (partition by cell_hash, stat_category)
< cat_byps,
< sum(cat_bycurr)
< over (partition by cell_hash, stat_category)
< cat_bycurr,
< -- get total for 'All' node
< sum(rq) over (partition by stat_name) rq_s,
< sum(rqps) over (partition by stat_name) rqps_s,
< sum(rqcurr) over (partition by stat_name) rqcurr_s,
< sum(bytes) over (partition by stat_name) by_s,
< sum(byps) over (partition by stat_name) byps_s,
< sum(bycurr) over (partition by stat_name) bycurr_s,
< -- get category total for 'All' node
< sum(cat_rq) over (partition by stat_category) cat_rq_s,
< sum(cat_rqps) over (partition by stat_category) cat_rqps_s,
< sum(cat_rqcurr) over (partition by stat_category)
< cat_rqcurr_s,
< sum(cat_by) over (partition by stat_category) cat_by_s,
< sum(cat_byps) over (partition by stat_category) cat_byps_s,
< sum(cat_bycurr) over (partition by stat_category)
< cat_bycurr_s,
< -- number of cells
< count(distinct cell_hash) over () num_cells
< from (-- partial pivot and create categories
< select dbid, cell_hash, stat_name,
< max(stat_category) stat_category,
< -- create columns for reqs/bytes
< sum(decode(stat_type,'reqs',value,0)) rq,
< sum(decode(stat_type,'reqs',persec_value,0)) rqps,
< sum(decode(stat_type,'reqs',current_value,0)) rqcurr,
< sum(decode(stat_type,'bytes',value,0)) bytes,
< sum(decode(stat_type,'bytes',persec_value,0)) byps,
< sum(decode(stat_type,'bytes',current_value,0)) bycurr,
< -- get category totals which is also denom for %hit
< sum(case when in_cat_sum='Y' and stat_type = 'reqs'
< then value
< end) cat_rq,
< sum(case when in_cat_sum='Y' and stat_type = 'reqs'
< then persec_value
< end) cat_rqps,
< sum(case when in_cat_sum='Y' and stat_type = 'reqs'
< then current_value
< end) cat_rqcurr,
< sum(case when in_cat_sum='Y' and stat_type = 'bytes'
< then value
< end) cat_by,
< sum(case when in_cat_sum='Y' and stat_type = 'bytes'
< then persec_value
< end) cat_byps,
< sum(case when in_cat_sum='Y' and stat_type = 'bytes'
< then current_value
< end) cat_bycurr
< from (
< select e.dbid, e.cell_hash, e.metric_id,
< -- get stat_name
< st.stat_name,
< st.stat_type,
< st.stat_category,
< st.in_cat_sum,
< e.metric_value - nvl(b.metric_value,0) value,
< -- per second value
< (e.metric_value - nvl(b.metric_value,0))/:duration
< persec_value,
< decode(st.is_current,'Y',e.metric_value,null)
< current_value
< from dba_hist_cell_global b,
< dba_hist_cell_global e,
< xmltable('/stats/stat'
< passing xmltype(:cc_metadata) columns
< id number path '@id',
< stat_name varchar2(32) path '@name',
< stat_type varchar2(10) path '@type',
< is_current varchar2(10) path '@curr',
< stat_category varchar2(32) path '@cat',
< in_cat_sum varchar2(1) path '@in_cat_sum') st
< where e.dbid = :dbid
< and b.snap_id (+) = :bid
< and e.snap_id = :eid
< and b.dbid (+) = e.dbid
< and b.cell_hash (+) = e.cell_hash
< and b.incarnation_num (+) = e.incarnation_num
< and b.metric_id (+) = e.metric_id
< and (e.metric_id between 301 and 302
< or e.metric_id between 306 and 307
< or e.metric_id between 401 and 419)
< and e.metric_id = st.id)
< group by dbid, cell_hash, stat_name)) s
< where s.dbid = cn.dbid
< and s.cell_hash = cn.cell_hash
< and cn.snap_id = :eid)
< select xmlelement("statsgroup",
< xmlagg(cells_xml order by stat_category))
< from (
< select stat_category,
< xmlelement("cellstats",
< xmlattributes(substr(stat_category,4) as "type"),
< all_xml, cell_xml) cells_xml
< from (
< select stat_category,
< -- any non-null value for 'All' node
< case when max(rq_s) is not null
< or max(mb_s) is not null
< or max(rqcurr_s) is not null
< or max(mbcurr_s) is not null
< then xmlelement("cell",
< xmlattributes('All' as "name",
< max(num_cells) as "num_cells"),
< xmlagg(all_xml order by rn))
< end all_xml,
< -- aggregate all cells
< xmlagg(cell_xml order by rn) cell_xml
< from (
< select cat.category stat_category,
< cell_name, rn,
< max(rq_s) rq_s, max(mb_s) mb_s,
< max(rqcurr_s) rqcurr_s, max(mbcurr_s) mbcurr_s,
< max(num_cells) num_cells,
< xmlagg(-- first occurrence of stat
< case when rn_stat = 1
< and (rq_s is not null or mb_s is not null
< or rqcurr_s is not null
< or mbcurr_s is not null)
< then xmlelement("stat",
< xmlattributes(stat_name as "name",
< round(rq_s,2) as "rq",
< round(rqps_s,2) as "rqps",
< round(rqcurr_s,2) as "rqcurr",
< round(mb_s,2) as "mb",
< round(mbps_s,2) as "mbps",
< round(mbcurr_s,2) as "mbcurr",
< round(eff_s,2) as "ratio"))
< end order by stat_name) all_xml,
< case -- when we have at least one non-null value
< when max(rq) is not null
< or max(mb) is not null
< or max(rqcurr) is not null
< or max(mbcurr) is not null
< then xmlelement("cell",
< xmlattributes(cell_name as "name",
< rn as "rn"),
< xmlagg(
< case
< when rq is not null
< or mb is not null
< or rqcurr is not null
< or mbcurr is not null
< then xmlelement("stat",
< xmlattributes(stat_name as "name",
< round(rq,2) as "rq",
< round(rqps,2) as "rqps",
< round(rqcurr,2) as "rqcurr",
< round(mb,2) as "mb",
< round(mbps,2) as "mbps",
< round(mbcurr,2) as "mbcurr",
< round(eff,2) as "ratio"))
< end
< order by stat_name))
< end cell_xml
< from cell_stats s,
< (select distinct category
< from xmltable('/stats/stat'
< passing xmltype(:cc_metadata) columns
< category varchar2(32) path '@cat')) cat
< where s.stat_category (+) = cat.category
< and nvl(s.rn,0) <= :top_cells
< group by cat.category, rn, cell_name)
< group by stat_category))]';
<
< BEGIN
< L_QRY := SQLSTMT_COLUMNAR_CACHE;
< L_QRY := REPLACE(L_QRY,'#BTOMB#',TO_CHAR(BTOMB));
<
< IF (P_SHOW_SQL = 1) THEN
<
< L_REPORT_SQL := SQLSTMT_HEADERS;
<
< L_REPORT_SQL := REPLACE(L_REPORT_SQL,'#DBID#',P_DBID);
< L_REPORT_SQL := REPLACE(L_REPORT_SQL,'#BID#',P_BID);
< L_REPORT_SQL := REPLACE(L_REPORT_SQL,'#EID#',P_EID);
< L_REPORT_SQL := REPLACE(L_REPORT_SQL,'#DURATION#',ROUND(P_DURATION,2));
< L_REPORT_SQL := REPLACE(L_REPORT_SQL,'#TOPN#',P_TOP_N);
< L_REPORT_SQL := REPLACE(L_REPORT_SQL,'#CC_METADATA#',G_CC_METADATA);
<
<
< L_REPORT_SQL := L_REPORT_SQL || L_QRY;
<
<
< L_XML := I_SHOW_SQL_XML('columnar_cache',
< L_REPORT_SQL);
<
< ELSE
< EXECUTE IMMEDIATE L_QRY
< INTO L_XML
< USING P_DURATION, G_CC_METADATA,
< P_DBID, P_BID, P_EID, P_EID,
< G_CC_METADATA, P_TOP_N;
< END IF;
<
< RETURN L_XML;
<
< END I_COLUMNAR_CACHE_XML;
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
10108d9767
< L_COLUMNAR_CACHE_XML XMLTYPE;
10795a10455
>
10891,10902d10550
<
< DBMS_REPORT.GET_TIMING_INFO(0,L_XML_ELAPSED,L_XML_CPU);
< L_COLUMNAR_CACHE_XML := I_COLUMNAR_CACHE_XML(
< P_DBID => L_DBID,
< P_BID => L_BEGIN_SNAP,
< P_EID => L_END_SNAP,
< P_DURATION => L_DURATION,
< P_TOP_N => G_MAX_CELLS,
< P_SHOW_SQL => P_SHOW_SQL);
< DBMS_REPORT.GET_TIMING_INFO(1,L_XML_ELAPSED,L_XML_CPU);
< I_APPEND_TIME('columnar_cache',L_XML_ELAPSED,L_XML_CPU,L_TIMING_XML);
<
10971d10618
< L_REPORT_XML := L_REPORT_XML.APPENDCHILDXML(XML_ROOT,L_COLUMNAR_CACHE_XML);
DBMS_FEATURE_ADAPTIVE_PLANS:
22c22 < ksppinm = 'optimizer_adaptive_plans'; --- > ksppinm = '_optimizer_adaptive_plans';
12.2.0.1
What changed for each?
CTXSYS.DRVXTAB:
2381,2384c2381
<
(blank lines)
<
<
< IF (SLAVEID IS NOT NULL AND PART_ID IS NOT NULL) THEN
---
> IF (SLAVEID IS NOT NULL) THEN
2564,2569c2561
< L_TABLE_NAME DRVUTL.DR_QLID := DBMS_ASSERT.ENQUOTE_NAME(IDX.IDX_TABLE,
< FALSE);
< BASE_OBJ_NAME DRVUTL.DR_MEDBUF;
< IDXOWNER DRVUTL.DR_MEDBUF;
< OBJ_NAME DRVUTL.DR_QLID2;
< CNT_OBJ NUMBER;
---
> L_TABLE_NAME DRVUTL.DR_QLID := DBMS_ASSERT.ENQUOTE_NAME(IDX.IDX_TABLE, FALSE);
2571,2581d2562
<
<
<
< OBJ_NAME:= GET_OBJECT_NAME(IDX_OWNER, IDX_NAME, IDX.IDX_ID, PART_ID, 'G', SEP);
< IF(SUBSTR(OBJ_NAME, 1, 1) = '"') THEN
< DRIUTL.PARSE_OBJECT_NAME(OBJ_NAME, IDXOWNER, BASE_OBJ_NAME);
< OBJ_NAME := BASE_OBJ_NAME;
< END IF;
< SELECT COUNT(*) INTO CNT_OBJ FROM SYS.USER_OBJECTS WHERE OBJECT_NAME=
< TRIM('"' FROM OBJ_NAME);
< IF (CNT_OBJ = 0) THEN
2597d2577
< END IF;
CTXSYS.DRIPARSE:
1288,1290c1288 < IF (OPTS.SYNC_TYPE IS NULL) THEN < OPTS.SYNC_TYPE := 'ON COMMIT'; < END IF; --- > OPTS.SYNC_TYPE := 'ON COMMIT';
CTXSYS.DRIXMD:
885,888c885,886 < < IF (O_CLAUSE(STO_RT) = '1') THEN < IF (L_IDX.IDX_TYPE != IDX_TYPE_CONTEXT AND < L_IDX.IDX_TYPE != IDX_TYPE_CONTEXT2) THEN --- > IF (L_IDX.IDX_TYPE != IDX_TYPE_CONTEXT) THEN > IF (O_CLAUSE(STO_RT) = '1') THEN 892,893d889 < END IF; < IF (L_IDX.IDX_TYPE != IDX_TYPE_CONTEXT) THEN
LBACSYS.LBAC_EVENTS:
< IF (OBJECT_TYPE = 'TABLE' OR < OBJECT_TYPE = 'SNAPSHOT') AND --- > IF OBJECT_TYPE = 'TABLE' AND
SYS.PRVTEMX_CELL (RU):
46d45
< NSTOMS CONSTANT NUMBER := 1000000;
147,175d145
< G_CC_METADATA CONSTANT CLOB := q'[<stats>
< <stat id="301" name="ccelig" type="reqs" cat="01-cc_ureads" in_cat_sum="Y"/>
< <stat id="302" name="ccelig" type="bytes" cat="01-cc_ureads" in_cat_sum="Y"/>
< <stat id="401" name="cc2ch" type="reqs" cat="01-cc_ureads"/>
< <stat id="402" name="cc2qh" type="reqs" cat="01-cc_ureads"/>
< <stat id="403" name="cc1h" type="reqs" cat="01-cc_ureads"/>
< <stat id="306" name="cciowpop" type="reqs" cat="02-cc_iwrites" in_cat_sum="Y"/>
< <stat id="307" name="cciowpop" type="bytes" cat="02-cc_iwrites"/>
< <stat id="404" name="cc1rw" type="reqs" curr="Y" cat="02-cc_iwrites"/>
< <stat id="405" name="cc2qrw" type="reqs" curr="Y" cat="02-cc_iwrites"/>
< <stat id="406" name="cc2crw" type="reqs" curr="Y" cat="02-cc_iwrites"/>
< <stat id="407" name="cc1rw" type="bytes" curr="Y" cat="02-cc_iwrites"/>
< <stat id="408" name="cc2qrw" type="bytes" curr="Y" cat="02-cc_iwrites"/>
< <stat id="409" name="cc2crw" type="bytes" curr="Y" cat="02-cc_iwrites"/>
< <stat id="410" name="cc1rw" type="reqs" cat="02-cc_iwrites"/>
< <stat id="411" name="cc2qrw" type="reqs" cat="02-cc_iwrites"/>
< <stat id="412" name="cc2crw" type="reqs" cat="02-cc_iwrites"/>
< <stat id="413" name="cc1rw" type="bytes" cat="02-cc_iwrites"/>
< <stat id="414" name="cc2qrw" type="bytes" cat="02-cc_iwrites"/>
< <stat id="415" name="cc2crw" type="bytes" cat="02-cc_iwrites"/>
< <stat id="416" name="ccevt" type="reqs" cat="02-cc_iwrites"/>
< <stat id="417" name="ccevt" type="bytes" cat="02-cc_iwrites"/>
< <stat id="418" name="ccbgpop" type="reqs" curr="Y" cat="02-cc_iwrites"/>
< <stat id="419" name="ccfail" type="reqs" curr="Y" cat="02-cc_iwrites"/>
< </stats>]';
<
<
<
<
6851,7007d6820
< FUNCTION I_GET_BASE_QUERY_CC(P_ID IN NUMBER DEFAULT NULL)
< RETURN VARCHAR2
< IS
<
<
< L_QRY CONSTANT VARCHAR2(32767) := q'[
< select #IDCOL# id,
< s.dbid, s.cell_hash, stat_category, stat_name, cell_name,
< nullif(rq,0) rq, nullif(rqps,0) rqps, nullif(rqcurr,0) rqcurr,
< nullif(bytes,0)/#BTOMB# mb,
< nullif(byps,0)/#BTOMB# mbps,
< nullif(bycurr,0)/#BTOMB# mbcurr,
< -- calculate efficiency
< case when stat_name in ('cc2ch','cc2qh', 'cc1h')
< then 100*(rq/decode(cat_rq,0,null,cat_rq))
< end eff,
< -- category totals
< nullif(cat_rq,0) cat_rq,
< nullif(cat_rqps,0) cat_rqps,
< nullif(cat_rqcurr,0) cat_rqcurr,
< -- get 'All' node
< nullif(rq_s,0) rq_s, nullif(rqps_s,0) rqps_s,
< nullif(rqcurr_s,0) rqcurr_s,
< nullif(by_s,0)/#BTOMB# mb_s,
< nullif(byps_s,0)/#BTOMB# mbps_s,
< nullif(bycurr_s,0)/#BTOMB# mbcurr_s,
< -- calculate efficiency
< case when stat_name in ('cc2ch','cc2qh', 'cc1h')
< then 100*(rq_s/decode(cat_rq_s,0,null,cat_rq_s))
< end eff_s,
< nullif(cat_rq_s,0) cat_rq_s,
< nullif(cat_rqps_s,0) cat_rqps_s,
< nullif( cat_rqcurr_s,0) cat_rqcurr_s,
< nullif(cat_by_s,0)/#BTOMB# cat_mb_s,
< nullif(cat_byps_s,0)/#BTOMB# cat_mbps_s,
< nullif(cat_bycurr_s,0)/#BTOMB# cat_curr_s,
< -- cell rank by cat_value
< dense_rank() over (partition by stat_category
< order by cat_rq desc nulls last,
< cat_by desc nulls last,
< cn.cell_name)
< rn,
< -- first occurrence of stat
< row_number()
< over (partition by stat_name
< order by cat_rq desc nulls last, cell_name) rn_stat,
< num_cells
< from dba_hist_cell_name cn,
< ( -- get category totals (denominator for efficiency)
< select dbid, cell_hash, stat_name,
< stat_category,
< rq, rqps, rqcurr, bytes, byps, bycurr,
< -- get per cell total for the category
< sum(cat_rq)
< over (partition by cell_hash, stat_category) cat_rq,
< sum(cat_rqps)
< over (partition by cell_hash, stat_category)
< cat_rqps,
< sum(cat_rqcurr)
< over (partition by cell_hash, stat_category)
< cat_rqcurr,
< sum(cat_by)
< over (partition by cell_hash, stat_category) cat_by,
< sum(cat_byps)
< over (partition by cell_hash, stat_category)
< cat_byps,
< sum(cat_bycurr)
< over (partition by cell_hash, stat_category)
< cat_bycurr,
< -- get total for 'All' node
< sum(rq) over (partition by stat_name) rq_s,
< sum(rqps) over (partition by stat_name) rqps_s,
< sum(rqcurr) over (partition by stat_name) rqcurr_s,
< sum(bytes) over (partition by stat_name) by_s,
< sum(byps) over (partition by stat_name) byps_s,
< sum(bycurr) over (partition by stat_name) bycurr_s,
< -- get category total for 'All' node
< sum(cat_rq) over (partition by stat_category) cat_rq_s,
< sum(cat_rqps) over (partition by stat_category) cat_rqps_s,
< sum(cat_rqcurr) over (partition by stat_category)
< cat_rqcurr_s,
< sum(cat_by) over (partition by stat_category) cat_by_s,
< sum(cat_byps) over (partition by stat_category) cat_byps_s,
< sum(cat_bycurr) over (partition by stat_category)
< cat_bycurr_s,
< -- number of cells
< count(distinct cell_hash) over () num_cells
< from (-- partial pivot and create categories
< select dbid, cell_hash, stat_name,
< max(stat_category) stat_category,
< -- create columns for reqs/bytes
< sum(decode(stat_type,'reqs',value,0)) rq,
< sum(decode(stat_type,'reqs',persec_value,0)) rqps,
< sum(decode(stat_type,'reqs',current_value,0)) rqcurr,
< sum(decode(stat_type,'bytes',value,0)) bytes,
< sum(decode(stat_type,'bytes',persec_value,0)) byps,
< sum(decode(stat_type,'bytes',current_value,0)) bycurr,
< -- get category totals which is also denom for %hit
< sum(case when in_cat_sum='Y' and stat_type = 'reqs'
< then value
< end) cat_rq,
< sum(case when in_cat_sum='Y' and stat_type = 'reqs'
< then persec_value
< end) cat_rqps,
< sum(case when in_cat_sum='Y' and stat_type = 'reqs'
< then current_value
< end) cat_rqcurr,
< sum(case when in_cat_sum='Y' and stat_type = 'bytes'
< then value
< end) cat_by,
< sum(case when in_cat_sum='Y' and stat_type = 'bytes'
< then persec_value
< end) cat_byps,
< sum(case when in_cat_sum='Y' and stat_type = 'bytes'
< then current_value
< end) cat_bycurr
< from (
< select e.dbid, e.cell_hash, e.metric_id,
< -- get stat_name
< st.stat_name,
< st.stat_type,
< st.stat_category,
< st.in_cat_sum,
< e.metric_value - nvl(b.metric_value,0) value,
< -- per second value
< (e.metric_value - nvl(b.metric_value,0))/:duration
< persec_value,
< decode(st.is_current,'Y',e.metric_value,null)
< current_value
< from dba_hist_cell_global b,
< dba_hist_cell_global e,
< xmltable('/stats/stat'
< passing xmltype(:cc_metadata) columns
< id number path '@id',
< stat_name varchar2(32) path '@name',
< stat_type varchar2(10) path '@type',
< is_current varchar2(10) path '@curr',
< stat_category varchar2(32) path '@cat',
< in_cat_sum varchar2(1) path '@in_cat_sum') st
< where e.dbid = :dbid#ID#
< and b.snap_id (+) = :bid#ID#
< and e.snap_id = :eid#ID#
< and b.dbid (+) = e.dbid
< and b.cell_hash (+) = e.cell_hash
< and b.incarnation_num (+) = e.incarnation_num
< and b.metric_id (+) = e.metric_id
< and (e.metric_id between 301 and 302
< or e.metric_id between 306 and 307
< or e.metric_id between 401 and 419)
< and e.metric_id = st.id)
< group by dbid, cell_hash, stat_name)) s
< where s.dbid = cn.dbid
< and s.cell_hash = cn.cell_hash
< and cn.snap_id = :eid#ID#]';
< BEGIN
< RETURN REPLACE(REPLACE(L_QRY,'#ID#',P_ID),'#IDCOL#',NVL(P_ID,0));
< END I_GET_BASE_QUERY_CC;
7134,7136d6946
<
<
<
7152,7171d6961
< e.disk_small_io_reqs - nvl(b.disk_small_io_reqs,0) dsrq,
< e.disk_large_io_reqs - nvl(b.disk_large_io_reqs,0) dlrq,
< e.flash_small_io_reqs - nvl(b.flash_small_io_reqs,0) fsrq,
< e.flash_large_io_reqs - nvl(b.flash_large_io_reqs,0) flrq,
< e.disk_small_io_service_time -
< nvl(b.disk_small_io_service_time,0) dslt,
< e.disk_small_io_queue_time -
< nvl(b.disk_small_io_queue_time,0) dsqt,
< e.disk_large_io_service_time -
< nvl(b.disk_large_io_service_time,0) dllt,
< e.disk_large_io_queue_time -
< nvl(b.disk_large_io_queue_time,0) dlqt,
< e.flash_small_io_service_time -
< nvl(b.flash_small_io_service_time,0) fslt,
< e.flash_small_io_queue_time -
< nvl(b.flash_small_io_queue_time,0) fsqt,
< e.flash_large_io_service_time -
< nvl(b.flash_large_io_service_time,0) fllt,
< e.flash_large_io_queue_time -
< nvl(b.flash_large_io_queue_time,0) flqt,
7234,7301d7023
< case when b.src_dbid is not null
< then ((e.snap_id - :bid#ID#) / (e.snap_id - b.snap_id)) *
< (e.disk_small_io_reqs - nvl(b.disk_small_io_reqs,0))
< else 0
< end dsrq,
< case when b.src_dbid is not null
< then ((e.snap_id - :bid#ID#) / (e.snap_id - b.snap_id)) *
< (e.disk_large_io_reqs - nvl(b.disk_large_io_reqs,0))
< else 0
< end dlrq,
< case when b.src_dbid is not null
< then ((e.snap_id - :bid#ID#) / (e.snap_id - b.snap_id)) *
< (e.flash_small_io_reqs - nvl(b.flash_small_io_reqs,0))
< else 0
< end fsrq,
< case when b.src_dbid is not null
< then ((e.snap_id - :bid#ID#) / (e.snap_id - b.snap_id)) *
< (e.flash_large_io_reqs - nvl(b.flash_large_io_reqs,0))
< else 0
< end flrq,
< case when b.src_dbid is not null
< then ((e.snap_id - :bid#ID#) / (e.snap_id - b.snap_id)) *
< (e.disk_small_io_service_time -
< nvl(b.disk_small_io_service_time,0))
< else 0
< end dslt,
< case when b.src_dbid is not null
< then ((e.snap_id - :bid#ID#) / (e.snap_id - b.snap_id)) *
< (e.disk_small_io_queue_time -
< nvl(b.disk_small_io_queue_time,0))
< else 0
< end dsqt,
< case when b.src_dbid is not null
< then ((e.snap_id - :bid#ID#) / (e.snap_id - b.snap_id)) *
< (e.disk_large_io_service_time -
< nvl(b.disk_large_io_service_time,0))
< else 0
< end dllt,
< case when b.src_dbid is not null
< then ((e.snap_id - :bid#ID#) / (e.snap_id - b.snap_id)) *
< (e.disk_large_io_queue_time -
< nvl(b.disk_large_io_queue_time,0))
< else 0
< end dlqt,
< case when b.src_dbid is not null
< then ((e.snap_id - :bid#ID#) / (e.snap_id - b.snap_id)) *
< (e.flash_small_io_service_time -
< nvl(b.flash_small_io_service_time,0))
< else 0
< end fslt,
< case when b.src_dbid is not null
< then ((e.snap_id - :bid#ID#) / (e.snap_id - b.snap_id)) *
< (e.flash_small_io_queue_time -
< nvl(b.flash_small_io_queue_time,0))
< else 0
< end fsqt,
< case when b.src_dbid is not null
< then ((e.snap_id - :bid#ID#) / (e.snap_id - b.snap_id)) *
< (e.flash_large_io_service_time -
< nvl(b.flash_large_io_service_time,0))
< else 0
< end fllt,
< case when b.src_dbid is not null
< then ((e.snap_id - :bid#ID#) / (e.snap_id - b.snap_id)) *
< (e.flash_large_io_queue_time -
< nvl(b.flash_large_io_queue_time,0))
< else 0
< end flqt,
7312,7318c7034
< flash_requests, flash_bytes,
< disk_small_io_reqs, disk_large_io_reqs,
< flash_small_io_reqs, flash_large_io_reqs,
< disk_small_io_service_time, disk_small_io_queue_time,
< disk_large_io_service_time, disk_large_io_queue_time,
< flash_small_io_service_time, flash_small_io_queue_time,
< flash_large_io_service_time, flash_large_io_queue_time
---
> flash_requests, flash_bytes
7327,7332d7042
< disk_small_io_reqs, disk_large_io_reqs,
< flash_small_io_reqs, flash_large_io_reqs,
< disk_small_io_service_time, disk_small_io_queue_time,
< disk_large_io_service_time, disk_large_io_queue_time,
< flash_small_io_service_time, flash_small_io_queue_time,
< flash_large_io_service_time, flash_large_io_queue_time,
7381d7090
<
7389,7403d7097
< dsrqps, dlrqps, fsrqps, flrqps,
< nullif(srqps,0) srqps, nullif(lrqps,0) lrqps,
< -- calculate % flash vs % disk
< 100*(fsrqps/decode(srqps,0,null,srqps)) fspct,
< 100*(dsrqps/decode(srqps,0,null,srqps)) dspct,
< 100*(flrqps/decode(lrqps,0,null,lrqps)) flpct,
< 100*(dlrqps/decode(lrqps,0,null,lrqps)) dlpct,
< nullif(dsltprq,0)/#NSTOMS# dsltprq,
< nullif(dsqtprq,0)/#NSTOMS# dsqtprq,
< nullif(dlltprq,0)/#NSTOMS# dlltprq,
< nullif(dlqtprq,0)/#NSTOMS# dlqtprq,
< nullif(fsltprq,0)/#NSTOMS# fsltprq,
< nullif(fsqtprq,0)/#NSTOMS# fsqtprq,
< nullif(flltprq,0)/#NSTOMS# flltprq,
< nullif(flqtprq,0)/#NSTOMS# flqtprq,
7410,7449d7103
< db_dsrq_s, db_dlrq_s, db_fsrq_s, db_flrq_s,
< db_dsrqps_s, db_dlrqps_s, db_fsrqps_s, db_flrqps_s,
< -- calculate total small IOs
< nullif(nvl(db_dsrqps_s,0) + nvl(db_fsrqps_s,0),0) db_srqps_s,
< nullif(nvl(db_dlrqps_s,0) + nvl(db_flrqps_s,0),0) db_lrqps_s,
< -- calculate % flash vs % disk
< 100*(db_fsrqps_s/decode(nvl(db_fsrqps_s,0) + nvl(db_dsrqps_s,0),
< 0, null,
< nvl(db_fsrqps_s,0) + nvl(db_dsrqps_s,0)))
< db_fspct,
< 100*(db_dsrqps_s/decode(nvl(db_fsrqps_s,0) + nvl(db_dsrqps_s,0),
< 0, null,
< nvl(db_fsrqps_s,0) + nvl(db_dsrqps_s,0)))
< db_dspct,
< 100*(db_flrqps_s/decode(nvl(db_flrqps_s,0) + nvl(db_dlrqps_s,0),
< 0, null,
< nvl(db_flrqps_s,0) + nvl(db_dlrqps_s,0)))
< db_flpct,
< 100*(db_dlrqps_s/decode(nvl(db_flrqps_s,0) + nvl(db_dlrqps_s,0),
< 0, null,
< nvl(db_flrqps_s,0) + nvl(db_dlrqps_s,0)))
< db_dlpct,
< -- compute latency per db over all cells
< (nullif(db_dslt_s,0)/
< decode(db_dsrq_s,0,null,db_dsrq_s))/#NSTOMS# db_dsltprq,
< (nullif(db_dsqt_s,0)/
< decode(db_dsrq_s,0,null,db_dsrq_s))/#NSTOMS# db_dsqtprq,
< (nullif(db_dllt_s,0)/
< decode(db_dlrq_s,0,null,db_dlrq_s))/#NSTOMS# db_dlltprq,
< (nullif(db_dlqt_s,0)/
< decode(db_dlrq_s,0,null,db_dlrq_s))/#NSTOMS# db_dlqtprq,
< (nullif(db_fslt_s,0)/
< decode(db_fsrq_s,0,null,db_fsrq_s))/#NSTOMS# db_fsltprq,
< (nullif(db_fsqt_s,0)/
< decode(db_fsrq_s,0,null,db_fsrq_s))/#NSTOMS# db_fsqtprq,
< (nullif(db_fllt_s,0)/
< decode(db_flrq_s,0,null,db_flrq_s))/#NSTOMS# db_flltprq,
< (nullif(db_flqt_s,0)/
< decode(db_flrq_s,0,null,db_flrq_s))/#NSTOMS# db_flqtprq,
< -- cell totals
7479,7487d7132
< dsrq, dlrq, fsrq, flrq,
< dsrqps, dlrqps, fsrqps, flrqps,
< -- calculate total small IOs and large IOs
< nvl(dsrqps,0) + nvl(fsrqps,0) srqps,
< nvl(dlrqps,0) + nvl(flrqps,0) lrqps,
< dslt, dsqt, dllt, dlqt,
< fslt, fsqt, fllt, flqt,
< dsltprq, dsqtprq, dlltprq, dlqtprq,
< fsltprq, fsqtprq, flltprq, flqtprq,
7502,7518d7146
< sum(dsrqps) over (partition by src_dbid) db_dsrqps_s,
< sum(dlrqps) over (partition by src_dbid) db_dlrqps_s,
< sum(fsrqps) over (partition by src_dbid) db_fsrqps_s,
< sum(flrqps) over (partition by src_dbid) db_flrqps_s,
< -- get totals over all cells in order to compute latency
< sum(dsrq) over (partition by src_dbid) db_dsrq_s,
< sum(dlrq) over (partition by src_dbid) db_dlrq_s,
< sum(fsrq) over (partition by src_dbid) db_fsrq_s,
< sum(flrq) over (partition by src_dbid) db_flrq_s,
< sum(dslt) over (partition by src_dbid) db_dslt_s,
< sum(dsqt) over (partition by src_dbid) db_dsqt_s,
< sum(dllt) over (partition by src_dbid) db_dllt_s,
< sum(dlqt) over (partition by src_dbid) db_dlqt_s,
< sum(fslt) over (partition by src_dbid) db_fslt_s,
< sum(fsqt) over (partition by src_dbid) db_fsqt_s,
< sum(fllt) over (partition by src_dbid) db_fllt_s,
< sum(flqt) over (partition by src_dbid) db_flqt_s,
7546,7563d7173
< -- get requests
< dsrq, dlrq, fsrq, flrq,
< dsrq/:duration#ID# dsrqps,
< dlrq/:duration#ID# dlrqps,
< fsrq/:duration#ID# fsrqps,
< flrq/:duration#ID# flrqps,
< -- get latencies and queue times
< dslt, dsqt, dllt, dlqt,
< fslt, fsqt, fllt, flqt,
< -- compute latency for each db per cell
< dslt/decode(dsrq,0,null,dsrq) dsltprq,
< dsqt/decode(dsrq,0,null,dsrq) dsqtprq,
< dllt/decode(dlrq,0,null,dlrq) dlltprq,
< dlqt/decode(dlrq,0,null,dlrq) dlqtprq,
< fslt/decode(fsrq,0,null,fsrq) fsltprq,
< fsqt/decode(fsrq,0,null,fsrq) fsqtprq,
< fllt/decode(flrq,0,null,flrq) flltprq,
< flqt/decode(flrq,0,null,flrq) flqtprq,
9801,9929d9410
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
< FUNCTION I_COLUMNAR_CACHE_XML(
< P_DBID IN NUMBER,
< P_BID IN NUMBER,
< P_EID IN NUMBER,
< P_DURATION IN NUMBER,
< P_TOP_N IN NUMBER DEFAULT G_MAX_CELLS,
< P_SHOW_SQL IN NUMBER DEFAULT 0)
< RETURN XMLTYPE
< IS
< L_QRY CLOB;
< L_XML XMLTYPE;
< L_REPORT_SQL CLOB;
<
< SQLSTMT_HEADERS CONSTANT VARCHAR2(32767) := q'[
< var dbid number;
< var bid number;
< var eid number;
< var top_cells number;
< var duration number;
< var cc_metadata clob;
< begin
< :dbid := #DBID#;
< :bid := #BID#;
< :eid := #EID#;
< :top_cells := #TOPN#;
< :duration := #DURATION#;
< :cc_metadata := '#CC_METADATA#';
< end;
< /]';
<
< SQLSTMT_COLUMNAR_CACHE CONSTANT VARCHAR2(32767) := q'[
< with cell_stats as ( #BASE_QUERY# )
< select xmlelement("statsgroup",
< xmlagg(cells_xml order by stat_category))
< from (
< select stat_category,
< xmlelement("cellstats",
< xmlattributes(substr(stat_category,4) as "type"),
< all_xml, cell_xml) cells_xml
< from (
< select stat_category,
< -- any non-null value for 'All' node
< case when max(rq_s) is not null
< or max(mb_s) is not null
< or max(rqcurr_s) is not null
< or max(mbcurr_s) is not null
< then xmlelement("cell",
< xmlattributes('All' as "name",
< max(num_cells) as "num_cells"),
< xmlagg(all_xml order by rn))
< end all_xml,
< -- aggregate all cells
< xmlagg(cell_xml order by rn) cell_xml
< from (
< select cat.category stat_category,
< cell_name, rn,
< max(rq_s) rq_s, max(mb_s) mb_s,
< max(rqcurr_s) rqcurr_s, max(mbcurr_s) mbcurr_s,
< max(num_cells) num_cells,
< xmlagg(-- first occurrence of stat
< case when rn_stat = 1
< and (rq_s is not null or mb_s is not null
< or rqcurr_s is not null
< or mbcurr_s is not null)
< then xmlelement("stat",
< xmlattributes(stat_name as "name",
< round(rq_s,2) as "rq",
< round(rqps_s,2) as "rqps",
< round(rqcurr_s,2) as "rqcurr",
< round(mb_s,2) as "mb",
< round(mbps_s,2) as "mbps",
< round(mbcurr_s,2) as "mbcurr",
< round(eff_s,2) as "ratio"))
< end order by stat_name) all_xml,
< case -- when we have at least one non-null value
< when max(rq) is not null
< or max(mb) is not null
< or max(rqcurr) is not null
< or max(mbcurr) is not null
< then xmlelement("cell",
< xmlattributes(cell_name as "name",
< rn as "rn"),
< xmlagg(
< case
< when rq is not null
< or mb is not null
< or rqcurr is not null
< or mbcurr is not null
< then xmlelement("stat",
< xmlattributes(stat_name as "name",
< round(rq,2) as "rq",
< round(rqps,2) as "rqps",
< round(rqcurr,2) as "rqcurr",
< round(mb,2) as "mb",
< round(mbps,2) as "mbps",
< round(mbcurr,2) as "mbcurr",
< round(eff,2) as "ratio"))
< end
< order by stat_name))
< end cell_xml
< from cell_stats s,
< (select distinct category
< from xmltable('/stats/stat'
< passing xmltype(:cc_metadata) columns
< category varchar2(32) path '@cat')) cat
< where s.stat_category (+) = cat.category
< and nvl(s.rn,0) <= :top_cells
< group by cat.category, rn, cell_name)
< group by stat_category))]';
9931,9969d9411
< BEGIN
< L_QRY := SQLSTMT_COLUMNAR_CACHE;
<
< L_QRY := REPLACE(L_QRY,'#BASE_QUERY#',I_GET_BASE_QUERY_CC(NULL));
< L_QRY := REPLACE(L_QRY,'#BTOMB#',TO_CHAR(BTOMB));
<
< I_REPLACE_DBA_HIST_CLOB(L_QRY, P_DBID);
<
< IF (P_SHOW_SQL = 1) THEN
<
< L_REPORT_SQL := SQLSTMT_HEADERS;
<
< L_REPORT_SQL := REPLACE(L_REPORT_SQL,'#DBID#',P_DBID);
< L_REPORT_SQL := REPLACE(L_REPORT_SQL,'#BID#',P_BID);
< L_REPORT_SQL := REPLACE(L_REPORT_SQL,'#EID#',P_EID);
< L_REPORT_SQL := REPLACE(L_REPORT_SQL,'#DURATION#',ROUND(P_DURATION,2));
< L_REPORT_SQL := REPLACE(L_REPORT_SQL,'#TOPN#',P_TOP_N);
< L_REPORT_SQL := REPLACE(L_REPORT_SQL,'#CC_METADATA#',G_CC_METADATA);
<
<
< L_REPORT_SQL := L_REPORT_SQL || L_QRY;
<
<
< L_XML := I_SHOW_SQL_XML('columnar_cache',
< L_REPORT_SQL);
<
< ELSE
< EXECUTE IMMEDIATE L_QRY
< INTO L_XML
< USING P_DURATION, G_CC_METADATA,
< P_DBID, P_BID, P_EID, P_EID,
< G_CC_METADATA, P_TOP_N;
< END IF;
<
< RETURN L_XML;
<
< END I_COLUMNAR_CACHE_XML;
<
<
10190,10221c9632
< round(max(db_iorqps_s),2) as "psec"),
< case
< when max(db_srqps_s) is not null
< or max(db_lrqps_s) is not null
< then xmlelement("rqs",
< nvl2(max(db_srqps_s),
< xmlelement("rq",
< xmlattributes('small' as "type",
< round(max(db_srqps_s),2) as "psec",
< round(max(db_dsrqps_s),2) as "hdps",
< round(max(db_fsrqps_s),2) as "fcps",
< round(max(db_dsltprq),6) as "hdlt",
< round(max(db_dsqtprq),6) as "hdqt",
< round(max(db_fsltprq),6) as "fclt",
< round(max(db_fsqtprq),6) as "fcqt",
< round(max(db_fspct),2) as "fcpct",
< round(max(db_dspct),2) as "hdpct")),
< null),
< nvl2(max(db_lrqps_s),
< xmlelement("rq",
< xmlattributes('large' as "type",
< round(max(db_lrqps_s),2) as "psec",
< round(max(db_dlrqps_s),2) as "hdps",
< round(max(db_flrqps_s),2) as "fcps",
< round(max(db_dlltprq),6) as "hdlt",
< round(max(db_dlqtprq),6) as "hdqt",
< round(max(db_flltprq),6) as "fclt",
< round(max(db_flqtprq),6) as "fcqt",
< round(max(db_flpct),2) as "fcpct",
< round(max(db_dlpct),2) as "hdpct")),
< null))
< end),
---
> round(max(db_iorqps_s),2) as "psec")),
10255,10286c9666
< round(iorqps,2) as "psec"),
< case
< when srqps is not null
< or lrqps is not null
< then xmlelement("rqs",
< nvl2(srqps,
< xmlelement("rq",
< xmlattributes('small' as "type",
< round(srqps,2) as "psec",
< round(dsrqps,2) as "hdps",
< round(fsrqps,2) as "fcps",
< round(dsltprq,6) as "hdlt",
< round(dsqtprq,6) as "hdqt",
< round(fsltprq,6) as "fclt",
< round(fsqtprq,6) as "fcqt",
< round(fspct,2) as "fcpct",
< round(dspct,2) as "hdpct")),
< null),
< nvl2(lrqps,
< xmlelement("rq",
< xmlattributes('large' as "type",
< round(lrqps,2) as "psec",
< round(dlrqps,2) as "hdps",
< round(flrqps,2) as "fcps",
< round(dlltprq,6) as "hdlt",
< round(dlqtprq,6) as "hdqt",
< round(flltprq,6) as "fclt",
< round(flqtprq,6) as "fcqt",
< round(flpct,2) as "fcpct",
< round(dlpct,2) as "hdpct")),
< null))
< end),
---
> round(iorqps,2) as "psec")),
10295c9675
< order by rn_iorqps)) cell_xml
---
> order by rn_iorqps)) cell_xml
10335d9714
< L_QRY := REPLACE(L_QRY,'#NSTOMS#',TO_CHAR(NSTOMS));
10363,10364c9742
< P_DURATION, P_DURATION, P_DURATION, P_DURATION,
< P_DURATION, P_DURATION, P_DURATION, P_DURATION,
---
> P_DURATION, P_DURATION, P_DURATION, P_DURATION,
10372,10374d9749
< P_BID, P_BID, P_BID, P_BID,
< P_BID, P_BID, P_BID, P_BID,
< P_BID, P_BID, P_BID, P_BID,
10377,10378c9752
< P_DURATION, P_DURATION, P_DURATION, P_DURATION,
< P_DURATION, P_DURATION, P_DURATION, P_DURATION,
---
> P_DURATION, P_DURATION, P_DURATION, P_DURATION,
17677d17050
< var fc_metadata clob;
17688d17060
< :fc_metadata := '#FC_METADATA#';
19045d18416
< L_QRY := REPLACE(L_QRY,'#NSTOMS#',TO_CHAR(NSTOMS));
19076d18446
< P_DURATION1, P_DURATION1, P_DURATION1, P_DURATION1,
19080d18449
< P_DURATION1, P_DURATION1, P_DURATION1, P_DURATION1,
19086,19088d18454
< P_BID1, P_BID1, P_BID1, P_BID1,
< P_BID1, P_BID1, P_BID1, P_BID1,
< P_BID1, P_BID1, P_BID1, P_BID1,
19092,19094d18457
< P_BID2, P_BID2, P_BID2, P_BID2,
< P_BID2, P_BID2, P_BID2, P_BID2,
< P_BID2, P_BID2, P_BID2, P_BID2,
19099d18461
< P_DURATION1, P_DURATION1, P_DURATION1, P_DURATION1,
19103d18464
< P_DURATION2, P_DURATION2, P_DURATION2, P_DURATION2,
19404d18764
< L_COLUMNAR_CACHE_XML XMLTYPE;
19995,20006d19354
<
< DBMS_REPORT.GET_TIMING_INFO(0,L_XML_ELAPSED,L_XML_CPU);
< L_COLUMNAR_CACHE_XML := I_COLUMNAR_CACHE_XML(
< P_DBID => L_DBID,
< P_BID => L_BEGIN_SNAP,
< P_EID => L_END_SNAP,
< P_DURATION => L_DURATION,
< P_TOP_N => G_MAX_CELLS,
< P_SHOW_SQL => P_SHOW_SQL);
< DBMS_REPORT.GET_TIMING_INFO(1,L_XML_ELAPSED,L_XML_CPU);
< I_APPEND_TIME('columnar_cache',L_XML_ELAPSED,L_XML_CPU,L_TIMING_XML);
<
20076d19423
< L_REPORT_XML := L_REPORT_XML.APPENDCHILDXML(XML_ROOT,L_COLUMNAR_CACHE_XML);
SYS.LOGMNR_DICT_CACHE:
16,18d15
< ORA_08181 EXCEPTION;
< PRAGMA EXCEPTION_INIT(ORA_08181, -8181);
<
177c174
< IF (-1555 <> SQLCODE) AND (-8181 <> SQLCODE) THEN
---
> IF (SQLCODE <> -1555) THEN
188c185
< 'locate_obj_at_scn:9 1555 or 8181 ('||AS_OF_SCN||','||
---
> 'locate_obj_at_scn:9 1555 ('||AS_OF_SCN||','||
4028,4031d4024
< WHEN ORA_08181 THEN
< IF 2 = TRY_COUNT THEN
< RAISE;
< END IF;
8084,8089c8077
< RETRYLOOKUP := TRUE;
< WHEN ORA_08181 THEN
< IF 2 = TRY_COUNT OR NOT IS_FAST_START THEN
< RAISE;
< END IF;
< RETRYLOOKUP := TRUE;
---
> RETRYLOOKUP := FALSE;
8590,8593c8578,8581
< UNSUPPORTEDCOLS_LOCAL NUMBER := 0;
< UNSUPPORTEDCOLS_LOCAL2 NUMBER := 0;
< COMPLEXTYPECOLS_LOCAL NUMBER := 0;
< TLOFLAGS_LOCAL NUMBER := 0;
---
> UNSUPPORTEDCOLS_LOCAL NUMBER := NULL;
> UNSUPPORTEDCOLS_LOCAL2 NUMBER := NULL;
> COMPLEXTYPECOLS_LOCAL NUMBER := NULL;
> TLOFLAGS_LOCAL NUMBER := NULL;
8597c8585
< LONG_IDEN NUMBER := 0;
---
> LONG_IDEN NUMBER := NULL;
10599c10587
< DECODE(BITAND(NVL(LOGMNRTLOFLAGS,0), 14), 0, 'NO', 'YES')
---
> DECODE(BITAND(LOGMNRTLOFLAGS, 14), 0, 'NO', 'YES')
10611,10615c10599,10600
< LOGMNR_SPARE9, UNSUPPORTEDCOLS,
< NVL(COMPLEXTYPECOLS,0) COMPLEXTYPECOLS,
< NTPARENTOBJNUM,
< NTPARENTOBJVERSION, NTPARENTINTCOLNUM,
< NVL(LOGMNRTLOFLAGS,0) LOGMNRTLOFLAGS,
---
> LOGMNR_SPARE9, UNSUPPORTEDCOLS, COMPLEXTYPECOLS, NTPARENTOBJNUM,
> NTPARENTOBJVERSION, NTPARENTINTCOLNUM, LOGMNRTLOFLAGS,
SYS.DBMS_RCVMAN (RU - Body):
195,207d194 < < < < < < < < < SKIPOFFLINERANGEABOVESCN NUMBER := NULL; < < < < 11629,11634d11615 < END IF; < < IF (ACTION.TYPE_CON = OFFLINERANGEREC_CON_T AND < (FROMSCN > SKIPOFFLINERANGEABOVESCN OR < TOSCN > SKIPOFFLINERANGEABOVESCN)) THEN < RETURN ACTION_SKIP; 29648,29656d29628 < PROCEDURE SETSKIPOFFLINERANGEABOVESCN(MAXCHECKPOINTSCN IN NUMBER) < IS < BEGIN < DEB(DEB_ENTER, 'setSkipOfflineRangeAboveSCN'); < SKIPOFFLINERANGEABOVESCN := MAXCHECKPOINTSCN; < DEB(DEB_EXIT); < END SETSKIPOFFLINERANGEABOVESCN; < <
SYS.DBMS_RCVMAN (RU - Spec):
3561,3562d3560 < PROCEDURE setSkipOfflineRangeAboveSCN(maxCheckpointSCN IN NUMBER); <
SYS.DBMS_AWR_REPORT_LAYOUT:
44171,44173c44171 < else to_number( < extractvalue(c.column_value, ''/stat/@value''), < ''999999999.99'') * 100 --- > else extractvalue(c.column_value, ''/stat/@value'') * 100
SYS.PRVTEMX_CELL (RUR):
46,48c46,47
< USTOMS CONSTANT NUMBER := 1000;
< USTOS CONSTANT NUMBER := 1000000;
< DTOS CONSTANT NUMBER := 86400;
---
> USTOMS CONSTANT NUMBER := 1000;
> DTOS CONSTANT NUMBER := 86400;
105,110d103
< <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"/>
426c419
<
---
>
524d516
<
530d521
< P_EVENT_NAME2 IN VARCHAR2 DEFAULT NULL,
543d533
< var event_name2 varchar2(128);
549d538
< :event_name2 := '#EVENT_NAME2#';
597c586
< and e.event_name in (:event_name, :event_name2)
---
> and e.event_name = :event_name
602,603c591
< select xmlagg(
< nvl2(twt,
---
> select nvl2(twt,
615c603
< null))
---
> null)
634d621
< L_REPORT_SQL := REPLACE(L_REPORT_SQL,'#EVENT_NAME2#',P_EVENT_NAME2);
643c630
< USING P_EVENT_NAME, P_EVENT_NAME2, P_DBID, P_EID, P_BID;
---
> USING P_EVENT_NAME, P_DBID, P_EID, P_BID;
1467,1476c1454
< and conftype = 'CELL'),
< fc_status as (
< select dbid, cellhash cell_hash,
< lower(extractvalue(
< xmltype(confval),
< '/cli-output/not-set/flashCacheStatus')) status
< from dba_hist_cell_config_detail
< where dbid = :dbid
< and snap_id = :eid
< and conftype = 'AWRXML')
---
> and conftype = 'CELL')
1485,1495c1463,1471
< case when fc_mode is not null
< or status is not null
< then xmlelement("fc",
< xmlattributes(
< decode(fc_mode,'WRITEBACK','WriteBack',
< 'WRITETHROUGH','WriteThrough',
< fc_mode) as "mode",
< fc_comp as "compress",
< status as "status"),
< cell_xml)
< end) fc_xml
---
> nvl2(fc_mode,
> xmlelement("fc",
> xmlattributes(
> decode(fc_mode,'WRITEBACK','WriteBack',
> 'WRITETHROUGH','WriteThrough',
> fc_mode) as "mode",
> fc_comp as "compress"),
> cell_xml),
> null)) fc_xml
1497,1498c1473
< select fc_mode, fc_comp, status,
< count(*) num_cells,
---
> select fc_mode, fc_comp, count(*) num_cells,
1507c1482
< select fc.cell_hash, fc.cell_name, fc_status.status,
---
> select fc.cell_hash, fc.cell_name,
1519c1494
< from fc, alloc, fc_status
---
> from fc, alloc
1521,1524c1496,1498
< and fc.cell_hash = alloc.cell_hash (+)
< and fc.dbid = fc_status.dbid (+)
< and fc.cell_hash = fc_status.cell_hash (+))
< group by fc_mode, fc_comp, status))]';
---
> and fc.cell_hash = alloc.cell_hash (+))
> group by fc_mode, fc_comp))]';
>
1548c1522
< USING P_DBID, P_EID, P_DBID, P_EID, P_DBID, P_EID;
---
> USING P_DBID, P_EID, P_DBID, P_EID;
3646d3619
< max_iops, max_mbps, max_disk_iops, max_disk_mbps,
3918d3890
< max_iops, max_mbps, max_disk_iops, max_disk_mbps,
4019d3990
< max_iops, max_mbps, max_disk_iops, max_disk_mbps,
4159,4172d4129
< decode(s.disk,
< 'HardDisk', dt.max_cell_disk_iops,
< 'FlashDisk',dt.max_cell_flash_iops)
< max_iops,
< decode(s.disk,
< 'HardDisk',dt.max_cell_disk_mbps,
< 'FlashDisk',dt.max_cell_flash_mbps)
< max_mbps,
< decode(s.disk,'HardDisk',dt.max_disk_iops,
< 'FlashDisk',dt.max_flash_iops)
< max_disk_iops,
< decode(s.disk,'HardDisk',dt.max_disk_mbps,
< 'FlashDisk',dt.max_flash_mbps)
< max_disk_mbps,
4202d4158
< max_iops,
4210,4212d4165
< case when c_iorq_t > max_iops then 'Y' end is_max_iops,
< case when dt_iorq_m > max_disk_iops then 'Y' end
< is_max_disk_iops,
4248d4200
< max_mbps,
4255,4257d4206
< case when c_iomb_t > max_mbps then 'Y' end is_max_mbps,
< case when dt_iomb_m > max_disk_mbps then 'Y' end
< is_max_disk_mbps,
4443,4447c4392
< iorq_margin as "mgn",
< is_max_iops as "is_max",
< decode(is_max_iops,
< 'Y', round(max_iops,2),
< null) as "cap")),
---
> iorq_margin as "mgn")),
4488,4489c4433,4434
< round(cd_iomb_a,2) as "value",
< round(c_iomb_t,2) as "tot",
---
> round(cd_iomb_a,2) as "value",
> round(c_iomb_t,2) as "tot",
4491,4495c4436
< iomb_margin as "mgn",
< is_max_mbps as "is_max",
< decode(is_max_mbps,
< 'Y', round(max_mbps,2),
< null) as "cap")),
---
> iomb_margin as "mgn")),
4569,4574c4510
< as "tot",
< is_max_disk_iops as "is_max",
< decode(is_max_disk_iops,
< 'Y',
< round(max_iops,2)*num_cells,
< null) as "cap")),
---
> as "tot")),
4620,4625c4556
< as "tot",
< is_max_disk_mbps as "is_max",
< decode(is_max_disk_mbps,
< 'Y',
< round(max_mbps,2)*num_cells,
< null) as "cap")),
---
> as "tot")),
4839d4769
< max_iops, max_mbps,
5025d4954
< max_iops, max_mbps,
5120d5048
< max_iops, max_mbps,
5258,5263d5185
< decode(s.disk,'HardDisk',dt.max_disk_iops,
< 'FlashDisk',dt.max_flash_iops)
< max_iops,
< decode(s.disk,'HardDisk',dt.max_disk_mbps,
< 'FlashDisk',dt.max_flash_mbps)
< max_mbps,
5383,5385c5305
< iorq_bid, max_iops,
< case when d_iorq_a > max_iops then 'Y' end is_max_iops,
< case when dt_iorq_m > max_iops then 'Y' end is_max_disk_iops,
---
> iorq_bid,
5405,5407c5325
< iomb_bid, max_mbps,
< case when d_iomb_a > max_mbps then 'Y' end is_max_mbps,
< case when dt_iomb_m > max_mbps then 'Y' end is_max_disk_mbps,
---
> iomb_bid,
5550,5554c5468
< round(d_iorq_pct,2) as "pct",
< is_max_iops as "is_max",
< decode(is_max_iops,
< 'Y', round(max_iops,2),
< null) as "cap")),
---
> round(d_iorq_pct,2) as "pct")),
5587,5591c5501
< round(d_iomb_pct,2) as "pct",
< is_max_mbps as "is_max",
< decode(is_max_mbps,
< 'Y', round(max_mbps,2),
< null) as "cap")),
---
> round(d_iomb_pct,2) as "pct")),
5651,5655c5561
< round(dt_iorq_h,2) as "hi",
< is_max_disk_iops as "is_max",
< decode(is_max_disk_iops,
< 'Y', round(max_iops,2),
< null) as "cap")),
---
> round(dt_iorq_h,2) as "hi")),
5691,5695c5597
< round(dt_iomb_h,2) as "hi",
< is_max_disk_mbps as "is_max",
< decode(is_max_disk_mbps,
< 'Y', round(max_mbps,2),
< null) as "cap")),
---
> round(dt_iomb_h,2) as "hi")),
6577,6593c6479,6485
< -- make sure we do not get negative numbers
< case when stat_name = 'fciow' and rq > tmp_rq
< then rq - tmp_rq
< else null
< end fciorqwp,
< (case when stat_name = 'fciow' and bytes > tmp_by
< then bytes - tmp_by
< else null
< end)/#BTOMB# fciombwp,
< case when stat_name = 'fciow' and rqps > tmp_rqps
< then rqps - tmp_rqps
< else null
< end fciorqwp_ps,
< (case when stat_name = 'fciow' and byps > tmp_byps
< then byps - tmp_byps
< else null
< end)/#BTOMB# fciombwp_ps,
---
> nullif(decode(stat_name,'fciow', rq - tmp_rq, null),0) fciorqwp,
> nullif(decode(stat_name,'fciow', bytes - tmp_by, null),0)/#BTOMB#
> fciombwp,
> nullif(decode(stat_name,'fciow', rqps - tmp_rqps, null),0)
> fciorqwp_ps,
> nullif(decode(stat_name,'fciow', byps - tmp_byps, null),0)/
> #BTOMB# fciombwp_ps,
6620,6636c6512,6519
< -- make sure we do not get negative numbers
< case when stat_name = 'fciow' and rq_s > tmp_rq_s
< then rq_s - tmp_rq_s
< else null
< end fciorqwp_s,
< (case when stat_name = 'fciow' and bytes_s > tmp_by_s
< then bytes_s - tmp_by_s
< else null
< end)/#BTOMB# fciombwp_s,
< case when stat_name = 'fciow' and rqps_s > tmp_rqps_s
< then rqps_s - tmp_rqps_s
< else null
< end fciorqwp_ps_s,
< (case when stat_name = 'fciow' and byps_s > tmp_byps_s
< then byps_s - tmp_byps_s
< else null
< end)/#BTOMB# fciombwp_ps_s,
---
> nullif(decode(stat_name,'fciow', rq_s - tmp_rq_s, null),0)
> fciorqwp_s,
> nullif(decode(stat_name,'fciow', bytes_s - tmp_by_s, null),0)/
> #BTOMB# fciombwp_s,
> nullif(decode(stat_name,'fciow', rqps_s - tmp_rqps_s, null),0)
> fciorqwp_ps_s,
> nullif(decode(stat_name,'fciow', byps_s - tmp_byps_s, null),0)/
> #BTOMB# fciombwp_ps_s,
6810,6811c6693
< or e.metric_id between 314 and 317
< or e.metric_id between 370 and 380)
---
> or e.metric_id between 314 and 317)
6843,6844d6724
<
<
6858c6738
< order by bytes desc, rq desc, reason_name) rn_by,
---
> order by bytes desc, rq desc, cell_hash) rn_by,
6861c6741
< order by rq desc, bytes desc, reason_name) rn_rq,
---
> order by rq desc, bytes desc, cell_hash) rn_rq,
6874,6934c6754,6773
< from (-- calculate cell total and per second rates
< select dbid, cell_hash, reason_name,
< bytes, rq,
< bytes/:duration#ID# byps,
< rq/:duration#ID# rqps,
< sum(rq) over (partition by dbid, cell_hash) cell_rq,
< sum(bytes) over (partition by dbid, cell_hash) cell_by
< from (-- remove scrub from internal IO
< select dbid, cell_hash, reason_name,
< case when reason_id = 0
< then greatest(0,
< bytes -
< sum(scrub_by)
< over (partition by dbid, cell_hash))
< else bytes
< end bytes,
< case when reason_id = 0
< then greatest(0,
< rq -
< sum(scrub_rq)
< over (partition by dbid, cell_hash))
< else rq
< end rq
< from (-- get delta values and scrub IO
< select e.dbid, e.cell_hash,
< e.reason_id, e.reason_name,
< e.bytes - nvl(b.bytes,0) bytes,
< e.requests - nvl(b.requests,0) rq,
< 0 scrub_by, 0 scrub_rq
< from dba_hist_cell_ioreason b,
< dba_hist_cell_ioreason e
< where e.dbid = :dbid#ID#
< and b.snap_id (+) = :bid#ID#
< and e.snap_id = :eid#ID#
< and e.dbid = b.dbid (+)
< and e.cell_hash = b.cell_hash (+)
< and e.incarnation_num = b.incarnation_num (+)
< and e.reason_id = b.reason_id (+)
< union all
< select e.dbid, e.cell_hash,
< -1 reason_id, 'scrub IO' reason_name,
< sum(decode(e.metric_id,
< 361, e.metric_value, 0)) bytes,
< sum(decode(e.metric_id,
< 360, e.metric_value, 0)) rq,
< -- add columns so se can recalculate internal IO
< sum(decode(e.metric_id,
< 361, e.metric_value, 0)) scrub_by,
< sum(decode(e.metric_id,
< 360, e.metric_value, 0)) scrub_rq
< from dba_hist_cell_global b,
< dba_hist_cell_global e
< where e.dbid = :dbid
< and b.snap_id (+) = :bid
< and e.snap_id = :eid
< and e.dbid = b.dbid (+)
< and e.cell_hash = b.cell_hash (+)
< and e.incarnation_num = b.incarnation_num (+)
< and e.metric_id = b.metric_id (+)
< and e.metric_id between 360 and 362
< group by e.dbid, e.cell_hash)))) s
---
> from (-- get delta values along with sum per cell
> select e.dbid, e.cell_hash,
> e.reason_name,
> e.bytes - nvl(b.bytes,0) bytes,
> e.requests - nvl(b.requests,0) rq,
> (e.bytes - nvl(b.bytes,0))/:duration#ID# byps,
> (e.requests - nvl(b.requests,0))/:duration#ID# rqps,
> sum(e.requests - nvl(b.requests,0))
> over (partition by e.dbid, e.cell_hash) cell_rq,
> sum(e.bytes - nvl(b.bytes,0))
> over (partition by e.dbid, e.cell_hash) cell_by
> from dba_hist_cell_ioreason b,
> dba_hist_cell_ioreason e
> where e.dbid = :dbid#ID#
> and b.snap_id (+) = :bid#ID#
> and e.snap_id = :eid#ID#
> and e.dbid = b.dbid (+)
> and e.cell_hash = b.cell_hash (+)
> and e.incarnation_num = b.incarnation_num (+)
> and e.reason_id = b.reason_id (+))) s
7701c7540
< :top_cells := #TOPN#;
---
> :top_cells := #TOPN#;
7972,8011d7810
< select dbid, cell_hash,
< nullif(twt,0) twt,
< nullif(tmio,0)/#USTOMS# tmio,
< nullif(tmrq,0)/#USTOMS# tmrq,
< nullif(tmio/decode(twt,0,null,twt),0)/#USTOMS# avtmio,
< nullif(tmrq/decode(twt,0,null,twt),0)/#USTOMS# avtmrq,
< nullif(twt_s,0) twt_s,
< nullif(tmio_s,0)/#USTOMS# tmio_s,
< nullif(tmrq_s,0)/#USTOMS# tmrq_s,
< nullif(tmio_s/decode(twt_s,0,null,twt_s),0)/#USTOMS# avtmio_s,
< nullif(tmrq_s/decode(twt_s,0,null,twt_s),0)/#USTOMS# avtmrq_s
< from (
< select dbid, cell_hash, twt, tmio, tmrq,
< sum(twt) over () twt_s,
< sum(tmio) over () tmio_s,
< sum(tmrq) over () tmrq_s
< from (-- get deltas for stats, and pivot for easier calc
< -- values are per cell already
< select e.dbid, e.cell_hash,
< sum(decode(e.metric_id, 239,
< e.metric_value - nvl(b.metric_value,0),
< 0)) twt,
< sum(decode(e.metric_id, 350,
< e.metric_value - nvl(b.metric_value,0),
< 0)) tmio,
< sum(decode(e.metric_id, 351,
< e.metric_value - nvl(b.metric_value,0),
< 0)) tmrq
< from dba_hist_cell_global b,
< dba_hist_cell_global e
< where e.dbid = :dbid
< and b.snap_id (+) = :bid
< and e.snap_id = :eid
< and e.dbid = b.dbid (+)
< and e.cell_hash = b.cell_hash (+)
< and e.incarnation_num = b.incarnation_num (+)
< and e.metric_id = b.metric_id (+)
< and e.metric_id in (239, 350, 351)
< group by e.dbid, e.cell_hash))),
< redo_hist as (
8085,8090c7884
< max(t_value_s) as "twt",
< max(w_t_value_s) as "twt_w",
< round(max(tmio_s),6) as "tmio",
< round(max(tmrq_s),6) as "tmrq",
< round(max(avtmio_s),6) as "avtmio",
< round(max(avtmrq_s),6) as "avtmrq"),
---
> max(t_value_s) as "twt"),
8097,8101d7890
< max(w.twt_s) w_t_value_s,
< max(w.tmio_s) tmio_s,
< max(w.tmrq_s) tmrq_s,
< max(w.avtmio_s) avtmio_s,
< max(w.avtmrq_s) avtmrq_s,
8113,8120c7902,7904
< xmlattributes(cell_name as "name",
< max(c_value) as "twt",
< max(c_value_o512) as "wtout",
< max(w.twt) as "twt_w",
< round(max(w.tmio),6) as "tmio",
< round(max(w.tmrq),6) as "tmrq",
< round(max(w.avtmio),6) as "avtmio",
< round(max(w.avtmrq),6) as "avtmrq"),
---
> xmlattributes(cell_name as "name",
> max(c_value) as "twt",
> max(c_value_o512) as "wtout"),
8130,8134c7914,7915
< from redo_hist h,
< redo_writes w
< where h.dbid = w.dbid (+)
< and h.cell_hash = w.cell_hash (+)
< and rn <= :top_cells
---
> from redo_writes
> where rn <= :top_cells
8170,8172d7950
< L_QRY := REPLACE(L_QRY,'#USTOS#', USTOS);
< L_QRY := REPLACE(L_QRY,'#USTOMS#', USTOMS);
<
8188c7966
< L_XML := I_SHOW_SQL_XML('flhist',
---
> L_XML := I_SHOW_SQL_XML('flash_log',
8195,8196c7973
< USING P_DBID, P_BID, P_EID,
< P_DBID, P_BID, P_EID, P_EID, P_TOP_N;
---
> USING P_DBID, P_BID, P_EID, P_EID, P_TOP_N;
8245,8249d8021
< L_TMP_SUMMARY_XML XMLTYPE;
< L_TMP_WAITS_XML XMLTYPE;
< L_TMP_HISTOGRAM_XML XMLTYPE;
<
<
8258,8259d8029
< L_TRRQ NUMBER;
< L_TWRQ NUMBER;
8311,8312c8081
<
< SQLSTMT_IOSTAT_DETAIL_SUMMARY CONSTANT VARCHAR2(32767) := q'[
---
> SQLSTMT_IOSTAT_SUMMARY CONSTANT VARCHAR2(32767) := q'[
8341,8411d8109
<
<
< SQLSTMT_IOSTAT_FILE_SUMMARY CONSTANT VARCHAR2(32767) := q'[
< select sum(e.small_read_reqs - nvl(b.small_read_reqs,0) +
< e.large_read_reqs - nvl(b.large_read_reqs,0)) trrq,
< sum(e.small_write_reqs - nvl(b.small_write_reqs,0) +
< e.large_write_reqs - nvl(b.large_write_reqs,0)) twrq
< from dba_hist_iostat_filetype b,
< dba_hist_iostat_filetype e
< where e.dbid = b.dbid (+)
< and e.instance_number = b.instance_number (+)
< and e.con_dbid = b.con_dbid (+)
< and e.filetype_id = b.filetype_id (+)
< and e.filetype_name = 'Temp File'
< and e.dbid = :p_dbid
< and b.snap_id (+) = :p_bid
< and e.snap_id = :p_eid]';
<
<
< SQLSTMT_WAITS_SUMMARY CONSTANT VARCHAR2(32767) := q'[
< select nvl2(waits_xml,
< xmlelement("waits",waits_xml),
< null)
< from (
< select xmlagg(xmlelement("wait",
< xmlattributes(event_name as "name",
< twt_fg as "fgwt",
< ttm_fg as "fgtm",
< avtm_fg as "avtm",
< twt as "twt",
< ttm as "ttm",
< avtm as "tavtm"))) waits_xml
< from (
< select event_name,
< sum(twt) twt,
< sum(ttm)/1000 ttm,
< -- true average
< round(
< sum(ttm)/decode(sum(twt),0,null,sum(twt)) / 1000,6) avtm,
< -- average of averages
< round(avg(ttm/decode(twt,0,null,twt)) / 1000, 6) aavtm,
< sum(twt_fg) twt_fg,
< sum(ttm_fg)/1000 ttm_fg,
< -- true average
< round(
< sum(ttm_fg)/
< decode(sum(twt_fg),0,null,sum(twt_fg))/1000,6) avtm_fg,
< -- average of averages
< round(
< avg(ttm_fg/decode(twt_fg,0,null,twt_fg))/1000, 6) aavtm_fg
< from (
< select e.event_name,
< e.instance_number,
< e.total_waits - nvl(b.total_waits,0) twt,
< e.time_waited_micro - nvl(b.time_waited_micro,0) ttm,
< e.total_waits_fg - nvl(b.total_waits_fg,0) twt_fg,
< e.time_waited_micro_fg - nvl(b.time_waited_micro_fg,0)
< ttm_fg
< from dba_hist_system_event b,
< dba_hist_system_event e
< where e.dbid = b.dbid (+)
< and e.instance_number = b.instance_number (+)
< and e.con_dbid = b.con_dbid (+)
< and e.event_id = b.event_id (+)
< and e.event_name in ('direct path read temp',
< 'direct path write temp')
< and e.dbid = :p_dbid
< and e.snap_id = :p_eid
< and b.snap_id (+) = :p_bid)
< group by event_name))]';
<
8418c8116
< L_QRY := I_REPLACE_DBA_HIST(SQLSTMT_IOSTAT_DETAIL_SUMMARY, P_DBID);
---
> L_QRY := I_REPLACE_DBA_HIST(SQLSTMT_IOSTAT_SUMMARY, P_DBID);
8422,8427d8119
<
< L_QRY := I_REPLACE_DBA_HIST(SQLSTMT_IOSTAT_FILE_SUMMARY, P_DBID);
< EXECUTE IMMEDIATE L_QRY
< INTO L_TRRQ, L_TWRQ
< USING P_DBID, P_BID, P_EID;
<
8473,8496d8164
< L_QRY := I_REPLACE_DBA_HIST(SQLSTMT_WAITS_SUMMARY, P_DBID);
< EXECUTE IMMEDIATE L_QRY
< INTO L_TMP_WAITS_XML
< USING P_DBID, P_EID, P_BID;
<
<
< L_TMP_HISTOGRAM_XML := I_HISTOGRAM_XML(P_DBID=> P_DBID,
< P_BID => P_BID,
< P_EID => P_EID,
< P_EVENT_NAME => 'direct path read temp',
< P_EVENT_NAME2 => 'direct path write temp',
< P_SHOW_SQL => P_SHOW_SQL);
<
<
< IF (L_TMP_WAITS_XML IS NOT NULL OR L_TMP_HISTOGRAM_XML IS NOT NULL)
< THEN
< SELECT XMLELEMENT("summary",
< XMLATTRIBUTES('fc_temp' AS "type"),
< L_TMP_WAITS_XML, L_TMP_HISTOGRAM_XML)
< INTO L_TMP_SUMMARY_XML
< FROM DUAL;
< END IF;
<
<
8501,8502c8169,8170
< XMLATTRIBUTES(L_TWT_FG AS "fgwt",
< L_TTM_FG AS "fgtm",
---
> XMLATTRIBUTES(L_TWT_FG AS "fgwt",
> L_TTM_FG AS "fgtm",
8504c8172
< L_HISTOGRAM_XML, L_TMP_SUMMARY_XML)
---
> L_HISTOGRAM_XML)
8531c8199
< P_TOP_N IN NUMBER DEFAULT G_MAX_CELLS,
---
> P_TOP_N IN NUMBER DEFAULT G_TOP_N_DISKS,
8629,8632c8297
< fksiobyal/#BTOMB# fksiobyal,
< fclwmrw/#BTOMB# fclwmrw,
< fclwnrw/#BTOMB# fclwnrw,
< fclwrow/#BTOMB# fclwrow,
---
> fksiobyal/#BTOMB# fksiobyal,
8648,8650d8312
< fclwmrw_s/#BTOMB# fclwmrw_s,
< fclwnrw_s/#BTOMB# fclwnrw_s,
< fclwrow_s/#BTOMB# fclwrow_s,
8659d8320
< fclwmrw_p, fclwnrw_p, fclwrow_p,
8666,8667c8327
< fkciobyal_s_p, fksiobyal_s_p,
< fclwmrw_s_p, fclwnrw_s_p, fclwrow_s_p,
---
> fkciobyal_s_p, fksiobyal_s_p,
8681,8683c8341
< fkciobyal, fksiobyal,
< fclwmrw, fclwnrw, fclwrow,
< fcffial, fcfclal,
---
> fkciobyal, fksiobyal, fcffial, fcfclal,
8688,8690c8346
< fkciobyal_s, fksiobyal_s,
< fclwmrw_s, fclwnrw_s, fclwrow_s,
< fcffial_s, fcfclal_s,
---
> fkciobyal_s, fksiobyal_s, fcffial_s, fcfclal_s,
8714,8719d8369
< 100*(fclwmrw/decode(fctiobyal,0,1,fctiobyal))
< fclwmrw_p,
< 100*(fclwnrw/decode(fctiobyal,0,1,fctiobyal))
< fclwnrw_p,
< 100*(fclwrow/decode(fctiobyal,0,1,fctiobyal))
< fclwrow_p,
8747,8752d8396
< 100*(fclwmrw_s/decode(fctiobyal_s,0,1,fctiobyal_s))
< fclwmrw_s_p,
< 100*(fclwnrw_s/decode(fctiobyal_s,0,1,fctiobyal_s))
< fclwnrw_s_p,
< 100*(fclwrow_s/decode(fctiobyal_s,0,1,fctiobyal_s))
< fclwrow_s_p,
8782,8784d8425
< nullif(fclwmrw,0) fclwmrw,
< nullif(fclwnrw,0) fclwnrw,
< nullif(fclwrow,0) fclwrow,
8802,8804d8442
< nullif(sum(fclwmrw) over (),0) fclwmrw_s,
< nullif(sum(fclwnrw) over (),0) fclwnrw_s,
< nullif(sum(fclwrow) over (),0) fclwrow_s,
8816,8819c8454,8455
< -- scan = total - (oltp+columnar+keep+LW)
< greatest(0,
< fctiobyal - (fcoiobyal + fcciobyal + fkiobyal +
< fclwmrw + fclwnrw + fclwrow))
---
> -- scan = total - (oltp+columnar+keep)
> fctiobyal - (fcoiobyal + fcciobyal + fkiobyal)
8821,8822d8456
< -- lw components
< fclwmrw, fclwnrw, fclwrow,
8830,8832c8464
< greatest(0,
< fkiobyal - (fkoiobyal + fkciobyal))
< fksiobyal,
---
> fkiobyal - (fkoiobyal + fkciobyal) fksiobyal,
8841c8473
< -- remove keep components and LW
---
> -- remove keep components
8843,8847c8475,8476
< greatest(0,
< fcoiobyald -
< (fkoiobyald +
< fclwmrw + fclwnrw + fclwrow)) fcoiobyald,
< fcciobyal - fkciobyal fcciobyal,
---
> fcoiobyald - fkoiobyald fcoiobyald,
> fcciobyal - fkciobyal fcciobyal,
8851,8852d8479
< -- LW components
< fclwmrw, fclwnrw, fclwrow,
8881,8889d8507
< -- flash cache LW mostly read (MRW)
< sum(decode(metric_id,370,metric_value,0))
< fclwmrw,
< -- flash cache LW write only (NRW)
< sum(decode(metric_id,371,metric_value,0))
< fclwnrw,
< -- flash cache LW write once (ROW)
< sum(decode(metric_id,372,metric_value,0))
< fclwrow,
8899,8900c8517
< or metric_id between 308 and 315
< or metric_id between 370 and 372)
---
> or metric_id between 308 and 315)
8931,8933d8547
< round(max(fclwmrw_s),2) as "lwmrw",
< round(max(fclwnrw_s),2) as "lwnrw",
< round(max(fclwrow_s),2) as "lwrow",
8953,8955d8566
< round(max(fclwmrw_s_p),2) as "lwmrw",
< round(max(fclwnrw_s_p),2) as "lwnrw",
< round(max(fclwrow_s_p),2) as "lwrow",
8981,8983d8591
< round(fclwmrw,2) as "lwmrw",
< round(fclwnrw,2) as "lwnrw",
< round(fclwrow,2) as "lwrow",
8998,9000d8605
< round(fclwmrw_p,2) as "lwmrw",
< round(fclwnrw_p,2) as "lwnrw",
< round(fclwrow_p,2) as "lwrow",
9126d8730
<
9411d9014
<
9494c9097
<
---
>
9525c9128
< P_DBID, P_BID, P_EID, P_DBID, P_BID, P_EID, P_EID;
---
> P_DBID, P_BID, P_EID, P_EID;
9632c9235
< round(max(db_iorqps_s),2) as "psec")),
---
> round(max(db_iorqps_s),2) as "psec")),
9675c9278
< order by rn_iorqps)) cell_xml
---
> order by rn_iorqps)) cell_xml
9742c9345
< P_DURATION, P_DURATION, P_DURATION, P_DURATION,
---
> P_DURATION, P_DURATION, P_DURATION, P_DURATION,
9744c9347
< L_DB_DIFF_CNT;
---
> L_DB_DIFF_CNT;
9752c9355
< P_DURATION, P_DURATION, P_DURATION, P_DURATION,
---
> P_DURATION, P_DURATION, P_DURATION, P_DURATION,
9756d9358
<
9884c9486
<
---
>
15921,15922c15523
< least(nvl(sp1.rn_stat,sp2.rn_stat),
< nvl(sp2.rn_stat,sp1.rn_stat)) rn_stat,
---
> least(sp1.rn_stat,sp2.rn_stat) rn_stat,
16112,16113c15713,15714
< xmlagg(stat_all_xml order by stat_name),
< xmlagg(eff_all_xml order by stat_name))
---
> xmlagg(stat_all_xml order by rn),
> xmlagg(eff_all_xml order by rn))
16286,16287c15887
< least(nvl(sp1.rn_stat, sp2.rn_stat),
< nvl(sp2.rn_stat, sp1.rn_stat)) rn_stat,
---
> least(sp1.rn_stat, sp2.rn_stat) rn_stat,
16462,16474d16061
< or (dbid = :dbid2 and snap_id = :eid2))),
< fc_status as (
< select case when dbid = :dbid1 and snap_id = :eid1
< then 1
< when dbid = :dbid2 and snap_id = :eid2
< then 2
< end id,
< dbid, cellhash cell_hash,
< lower(extractvalue(xmltype(confval),
< '/cli-output/not-set/flashCacheStatus')) status
< from dba_hist_cell_config_detail
< where conftype = 'AWRXML'
< and ((dbid = :dbid1 and snap_id = :eid1)
16489,16490c16076
< fc_comp as "compress",
< status as "status"),
---
> fc_comp as "compress"),
16495c16081
< select id, fc_mode, fc_comp, status,
---
> select id, fc_mode, fc_comp,
16510,16512c16096
< select fc.id, fc.cell_hash, fc.cell_name,
< fc_status.status,
< alloc.metric_value,
---
> select fc.id, fc.cell_hash, fc.cell_name, alloc.metric_value,
16523c16107
< from fc, alloc, fc_status
---
> from fc, alloc
16526,16531c16110,16112
< and fc.cell_hash = alloc.cell_hash (+)
< and fc.id = fc_status.id (+)
< and fc.dbid = fc_status.dbid (+)
< and fc.cell_hash = fc_status.cell_hash (+))
< group by id, fc_mode, fc_comp, status)
< group by fc_mode, fc_comp, status)]';
---
> and fc.cell_hash = alloc.cell_hash (+))
> group by id, fc_mode, fc_comp)
> group by fc_mode, fc_comp)]';
16559,16562c16140
< P_DBID1, P_EID1, P_DBID2, P_EID2,
< P_DBID1, P_EID1, P_DBID2, P_EID2,
< P_DBID1, P_EID1, P_DBID2, P_EID2;
<
---
> P_DBID1, P_EID1, P_DBID2, P_EID2;
16652,16653c16230,16231
< xmlagg(all_stat_t_xml order by stat_name),
< xmlagg(all_stat_xml order by stat_name))
---
> xmlagg(all_stat_t_xml order by rn),
> xmlagg(all_stat_xml order by rn))
16669,16670c16247,16248
< xmlagg(stat_t_xml order by stat_name),
< xmlagg(stat_xml order by stat_name)) cell_xml
---
> xmlagg(stat_t_xml order by rn),
> xmlagg(stat_xml order by rn)) cell_xml
16869c16447
< -- remove keep and LW
---
> -- remove keep
16871,16872c16449
< fcoiobyald - (fkoiobyald +
< fclwmrw + fclwnrw + fclwrow) fcoiobyald,
---
> fcoiobyald - fkoiobyald fcoiobyald,
16874,16878c16451,16452
< -- oltp clean = oltp - oltp dirty (with no keep/LW)
< (fcoiobyal - fkoiobyal) -
< greatest(0,
< (fcoiobyald -
< (fkoiobyald + fclwmrw + fclwnrw + fclwrow)))
---
> -- oltp clean = oltp - oltp dirty (with no keep)
> (fcoiobyal - fkoiobyal) - (fcoiobyald - fkoiobyald)
16880c16454
< -- scan = total - (oltp + columnar + keep + LW)
---
> -- scan = total - (oltp + columnar + keep)
16882,16883c16456
< greatest(0,
< (fctiobyal - ((fcoiobyal - fkoiobyal) +
---
> (fctiobyal - ((fcoiobyal - fkoiobyal) +
16885,16886c16458
< (fclwmrw + fclwnrw + fclwrow) +
< (fkiobyal)))) fcsiobyal,
---
> (fkiobyal))) fcsiobyal,
16892,16895c16464
< greatest(0,
< (fkiobyal - (fkoiobyal + fkciobyal))) fksiobyal,
< -- LW
< fclwmrw, fclwnrw, fclwrow,
---
> (fkiobyal - (fkoiobyal + fkciobyal)) fksiobyal,
16919,16927d16487
< -- flash cache LW mostly read (MRW)
< sum(decode(metric_id,370,metric_value,0))
< fclwmrw,
< -- flash cache LW write only (NRW)
< sum(decode(metric_id,371,metric_value,0))
< fclwnrw,
< -- flash cache LW write once (ROW)
< sum(decode(metric_id,372,metric_value,0))
< fclwrow,
16944,16945c16504
< or metric_id between 308 and 315
< or metric_id between 370 and 372)
---
> or metric_id between 308 and 315)
16958,16960d16516
< fclwmrw,
< fclwnrw,
< fclwrow,
17456,17457c17012
< least(nvl(sp1.rn_stat,sp2.rn_stat),
< nvl(sp2.rn_stat,sp1.rn_stat)) rn_stat,
---
> least(sp1.rn_stat, sp2.rn_stat) rn_stat,
17716,17718c17271
< -- break ties for lrg difs
< xmlagg(stat_xml order by abs(pct_rq_diff) desc,
< reason_name)) cell_xml
---
> xmlagg(stat_xml order by abs(pct_rq_diff) desc)) cell_xml
17720c17273
< select cell_rn_rq, reason_name,
---
> select cell_rn_rq,
17787,17788c17340
< xmlagg(stat_xml order by abs(pct_by_diff) desc,
< reason_name)) cell_xml
---
> xmlagg(stat_xml order by abs(pct_by_diff) desc)) cell_xml
17790c17342
< select cell_rn_by, reason_name,
---
> select cell_rn_by,
17877,17880c17429,17430
< USING P_DURATION1, P_DURATION1,
< P_DBID1, P_BID1, P_EID1, P_DBID1, P_BID1, P_EID1, P_EID1,
< P_DURATION2, P_DURATION2,
< P_DBID2, P_BID2, P_EID2, P_DBID2, P_BID2, P_EID2, P_EID2;
---
> USING P_DURATION1, P_DURATION1, P_DBID1, P_BID1, P_EID1, P_EID1,
> P_DURATION2, P_DURATION2, P_DBID2, P_BID2, P_EID2, P_EID2;
17992c17542
< xmlelement("cellstats",
---
> xmlelement("cellstts",
SYS.DBMS_RCVMAN (RUR):
9155,9156c9155 < SELECT /*+ LEADING(bs bp bdf) USE_HASH(bp) USE_HASH(bdf) */ < BACKUPSET_CON_T TYPE_CON, --- > SELECT BACKUPSET_CON_T TYPE_CON, 15242,15243c15241 < SELECT /*+ LEADING(bs bp bdf) USE_HASH(bp) USE_HASH(bdf) */ < BDF.CHECKPOINT_CHANGE# BCKSCN, BP.TAG --- > SELECT BDF.CHECKPOINT_CHANGE# BCKSCN, BP.TAG 15269,15270c15267 < (SELECT /*+ LEADING(bs bp bdf) USE_HASH(bp) USE_HASH(bdf) */ < BCF.CHECKPOINT_CHANGE# CFSCN --- > (SELECT BCF.CHECKPOINT_CHANGE# CFSCN
Useful Links:
- Download Assistant: MOS Note: 2118136.2
- 2017 October CPU: MOS Note: 2296870.1
- Risk Matrix: http://www.oracle.com/technetwork/security-advisory/cpuoct2017-3236626.html#AppendixDB
Have you enjoyed? Please leave a comment or give a 👍!
1 comment
Hi Rodrigo,
That's a very good idea you have. Always good to get an idea about the changes included in bundle patches.
Thanks, Franck.