Select query on dba_mviews takes too long

Who should tune "select * from dba_mviews" query? 

I’ve moved my blog from https://insanedba.blogspot.com to https://dincosman.com Please update your bookmarks and follow/subscribe at the new address for all the latest updates and content. More up-to-date content of this post may be available there.

In one of our database, whenever "Select * from dba_mviews" statement, with no other filter, gets executed,  it takes 45 seconds to complete.  Actually this is not an acceptable length and requires a detailed investigation.

Basically "dba_mviews" is a data dictionary view. Its query is as shown below, a really complex one, just skip it.

	SELECT
	s.sowner AS
	owner,
	s.vname AS
	mview_name,
	s.tname AS
	container_name,
	s.query_txt AS
	query,
	s.query_len AS
	query_len,
	decode(bitand(s.flag, 2), 0, 'N', 'Y') AS
	updatable, /* updatable */
	s.uslog AS
	update_log,
	s.mas_roll_seg AS
	master_rollback_seg,
	s.mlink AS
	master_link,
	decode(w.mflags, '', '', /* missing summary */ decode(bitand(w.mflags, 4), 4, 'N', 'Y')) AS
	rewrite_enabled,
	/* rewrite capability
	* KKQS_NOGR_PFLAGS:
	* QSMG_SUM_PART_EXT_NAME + QSMG_SUM_CONNECT_BY +
	* QSMG_SUM_RAW_OUTPUT + QSMG_SUM_SUBQUERY_HAVING +
	* QSMG_SUM_SUBQUERY_WHERE + QSMG_SUM_SET_OPERATOR +
	* QSMG_SUM_NESTED_CURSOR + QSMG_SUM_OUT_MISSING_GRPCOL +
	* QSMG_SUM_AGGREGATE_NOT_TOP
	*
	* KKQS_NOGR_XPFLAGS:
	* QSMG_SUM_WCLS
	*
	* QSMG_SUM_DATA_IGNORE - 2nd-class summary
	*/
	decode(w.pflags, '', '', /* missing summary */ decode(bitand(w.pflags, 1073741824), /* 2nd-class summary */
	1073741824, 'NONE',
	/* 2152929292 = 2147483648 + 2048 + 4096 + 65536 + 131072 +
	* 1048576 + 4194304 + 8 + 4
	*/ decode(bitand(w.pflags, 2152929292), 0, decode(bitand(w.xpflags, 8192), 8192, 'TEXTMATCH', 'GENERAL'), 'TEXTMATCH'))) AS
	rewrite_capability,
	decode(s.auto_fast, 'N', 'NEVER', decode(bitand(s.flag, 32768), 0, decode(bitand(s.flag3, 67108864), 0, 'DEMAND', 'STATEMENT'), 'COMMIT')) AS
	refresh_mode,
	decode(s.auto_fast, /* refresh method */ 'C', 'COMPLETE', 'F', 'FAST',
	'?', 'FORCE', 'N', 'NEVER', NULL,
	'FORCE', 'ERROR') AS
	refresh_method,
	decode(bitand(s.flag, 131072), /* build mode */ 131072, 'PREBUILT', decode(bitand(s.flag, 524288), 0, 'IMMEDIATE',
	'DEFERRED')) AS build_mode,
	/* fast refreshable
	* rowid+primary key+object id+subquery+complex+MAV+MJV+MAV1
	* 536900016 = 16+32+536870912+128+256+4096+8192+16384
	*/
	decode(bitand(s.flag2, 67108864), 67108864, /* if primary CUBE MV, use its secondary MV's flag value to
	* determine its FAST REFRESHABILITY. */(decode(bitand((
	SELECT
	s2.flag
	FROM
	sys.snap$ s2
	WHERE
	s2.parent_sowner = s.sowner
	AND s2.parent_vname = s.vname
	), 536900016), 16, 'DIRLOAD_DML', /* rowid */ 32, 'DIRLOAD_DML', /* primary key */
	536870912, 'DIRLOAD_DML', /* object id */ 160, 'DIRLOAD_DML', /* subquery - has both the primary key */
	/* bit and the subquery bit (32+128) */ 536871040,
	'DIRLOAD_DML', /* subquery - has both the object id bit */
	/* and the subquery bit (536870912+128) */ 256, 'NO', /* complex */ 4096, decode(
	bitand(s.flag2, 23), /* KKZFAGG_INSO */ 0, 'DIRLOAD_DML', /* regular MAV */ 'DIRLOAD_LIMITEDDML'), /* insert only MAV */
	8192, 'DIRLOAD_DML', /* MJV */ 16384, 'DIRLOAD_DML', /* MAV1 */
	decode(bitand(s.flag2, 16384), 16384, 'DIRLOAD_DML', /* UNION_ALL MV */ 'ERROR'))),
	decode(bitand(s.flag, 536900016), 16, 'DIRLOAD_DML', /* rowid */
	32, 'DIRLOAD_DML', /* primary key */
	536870912,
	'DIRLOAD_DML', /* object id */
	160, 'DIRLOAD_DML', /* subquery - has both the primary key */
	/* bit and the subquery bit (32+128) */ 536871040,
	'DIRLOAD_DML', /* subquery - has both the object id bit */
	/* and the subquery bit (536870912+128) */ 256, 'NO', /* complex */ 4096, decode(
	bitand(
	s.flag2,
	23), /* KKZFAGG_INSO */
	0, 'DIRLOAD_DML', /* regular MAV */
	'DIRLOAD_LIMITEDDML'), /* insert only MAV */
	8192, 'DIRLOAD_DML', /* MJV */
	16384,
	'DIRLOAD_DML', /* MAV1 */
	decode(
	bitand(
	s.flag2,
	16384),
	16384,
	'DIRLOAD_DML', /* UNION_ALL MV */
	'ERROR'))) AS
	fast_refreshable,
	/* fixing bug 923186 */
	decode(w.mflags, /*last refresh type */ '', '', /*missing summary */
	decode(bitand(w.mflags, 16384 + 32768 + 4194304 + 1073741824), 0, 'NA', 16384, 'COMPLETE',
	32768, 'FAST', 4194304, 'FAST_PCT', 1073741824,
	'FAST_CS', 'ERROR')) AS
	last_refresh_type,
	/* end fixing bug 923186 */
	/* the last refresh date should be of date type and not varchar,
	** SO BE CAREFUL WITH CHANGES IN THE FOLLOWING DECODE
	*/
	decode(w.lastrefreshdate, /* last refresh date */ NULL, to_date(NULL, 'DD-MON-YYYY'), /* missing summary */
	decode(to_char(w.lastrefreshdate, 'DD-MM-YYYY'), '01-01-1950', to_date(NULL, 'DD-MON-YYYY'), w.lastrefreshdate)) AS
	last_refresh_date,
	/* fixing bug 14116743 */
	decode(w.mflags, /*last refresh end time */ NULL, to_date(NULL, 'DD-MON-YYYY'), decode(bitand(w.mflags,
	16384 + 32768 + 4194304 + 1073741824), 0, to_date(NULL, 'DD-MON-YYYY'),
	/* complete refresh */ 16384, w.lastrefreshdate + w.fullrefreshtim /(24 * 60 * 60),
	/* fast refresh */ 32768, w.lastrefreshdate + w.increfreshtim /(24 * 60 * 60),
	/* PCT refresh */
	4194304, w.lastrefreshdate + w.increfreshtim /(24 * 60 * 60),
	/* cube fastsolve refresh (treated as complete refresh) */ 1073741824, w.lastrefreshdate + w.fullrefreshtim /(24 *
	60 * 60), to_date(NULL, 'DD-MON-YYYY'))) AS
	last_refresh_end_time,
	/* staleness */
	decode(nvl(s.mlink, 'null'), /* not null implies remote */ 'null', decode(bitand(s.status, 4), /* snapshot-invalid */
	4, 'UNUSABLE', decode(o.status, 1, decode(w.mflags, '', '', /* missing summary */ decode(bitand(w.mflags, 8388608), 8388608,
	'IMPORT', /* mv imported */ decode(bitand(w.mflags, 64), /* wh-unusable */ 64, 'UNUSABLE', /* unusable */ decode(bitand(w.mflags,
	32), 0, /* unknown */
	/* known stale */ decode(bitand(w.mflags, 1), 0, 'FRESH', 'STALE'), 'UNKNOWN')))),
	2, 'AUTHORIZATION_ERROR', 3, 'COMPILATION_ERROR',
	5, 'NEEDS_COMPILE', 'ERROR')), 'UNDEFINED') AS
	staleness, /* remote MV */
	/* after fast refresh */
	/* in the decode for after fast refresh, we only have to check
	* whether w.mflags is null once. all of the other occurences
	* fall under the first check. if the summary information is not
	* null, we need to check for the warehouse unusable condition
	* before we check to see if the MV is complex. if the summary
	* information is null, we still need to check whether the MV
	* is complex.
	*/
	decode(nvl(s.mlink, 'null'), /* remote */ 'null', decode(s.auto_fast, /* never refresh */
	'N', 'NA', decode(bitand(s.flag, 32768), /* on commit */ 32768, 'NA', decode(bitand(s.status, 4), /* snap-invalid */ 4,
	'NA', decode(w.mflags, /* missing summary */ '', decode(bitand(s.flag, 256), /* complex */ 256, 'NA', ''), decode(o.status,
	1, decode(bitand(w.mflags, 8388608), 8388608, 'UNKNOWN', /* imported */
	/* warehouse unusable */ decode(bitand(w.mflags, 64), 64, 'NA', decode(bitand(s.flag,
	256), /*complex*/ 256, 'NA',
	/* unknown */ decode(bitand(w.mflags, 32), 32, 'UNKNOWN',
	/* known stale */ decode(bitand(w.mflags, 1), 0, 'FRESH',
	/* stale states (on-demand only)
	* (This decode is the default clause for the known-stale
	* decode statement. It should be indented there, but there
	* isn't enough room.)
	*/ decode(bitand(s.flag, 176), /* ri+pk+sq */
	/* 16+32+128 */ 0, decode(bitand(s.flag, 28672), /* mjv+mav1+mav */
	/* 8192+16384+4096 */ 0, 'ERROR', /* no mv type */
	/* mjv/mav/mav1 MV */ decode(bitand(w.mflags, 1576832),
	/* 1576832 = 128+256+512+1024+2048+524288+1048576*/
	/*si + su + lsi + lsu + sf + sp + spu */ 128, 'FRESH', /* si */
	256, 'UNKNOWN', /* su */
	512, 'STALE', /* sf */ 1024, 'FRESH', /* lsi */ 2048,
	'UNKNOWN', /* lsu */ 524288, 'FRESH', /* sp */ 1048576, 'UNKNOWN', /* spu */
	/* 128+1024 */
	1152, 'FRESH', /* si+lsi*/
	/* 256+2048 */ 2304, 'UNKNOWN', /* su+lsu*/ 'ERROR')),
	/* ri or pk or sq MV */ decode(bitand(w.mflags, 1576832),
	/* 1576832 = 128+256+512+1024+2048+524288+1048576 */ 128, 'STALE', /* si */
	256, 'STALE', /* su */
	512, 'STALE', /* sf */ 1024, 'FRESH', /* lsi */ 2048,
	'UNKNOWN', /* lsu */ 524288, 'FRESH', /* sp */ 1048576, 'UNKNOWN', /* spu */
	/* 128+1024 */
	1152, 'STALE', /* si+lsi*/
	/* 256+2048 */ 2304, 'STALE', /* su+lsu*/ 'ERROR'))))))), 2,
	'AUTHORIZATION_ERROR', 3, 'COMPILATION_ERROR',
	5, 'NEEDS_COMPILE', 'ERROR'))))), 'UNDEFINED') AS
	after_fast_refresh, /* remote mv */
	/* UNKNOWN_PREBUILT */
	decode(w.pflags, '', '', /* missing summary */ decode(bitand(s.flag, 131072), 131072, 'Y', 'N')) AS
	unknown_prebuilt,
	/* UNKNOWN_PLSQL_FUNC */
	decode(w.pflags, '', '', /* missing summary */ decode(bitand(w.pflags, 268435456), 268435456, 'Y',
	'N')) AS unknown_plsql_func,
	/* UNKNOWN_EXTERNAL_TABLE */
	decode(w.xpflags, '', '', /* missing summary */ decode(bitand(w.xpflags, 32768), 32768, 'Y', 'N')) AS
	unknown_external_table,
	/* UNKNOWN_CONSIDER_FRESH */
	decode(w.mflags, '', '', /* missing summary */ decode(bitand(w.mflags, 8192), 8192, 'Y', 'N')) AS
	unknown_consider_fresh,
	/* UNKNOWN_IMPORT */
	decode(w.mflags, '', '', /* missing summary */ decode(bitand(w.mflags, 8388608), 8388608, 'Y', 'N')) AS
	unknown_import,
	/* UNKNOWN_TRUSTED_FD */
	decode(w.mflags, '', '', /* missing summary */ decode(bitand(w.mflags, 33554432), 33554432, 'Y', 'N')) AS
	unknown_trusted_fd,
	decode(o.status, 1, 'VALID', 2, 'AUTHORIZATION_ERROR',
	3, 'COMPILATION_ERROR', 5, 'NEEDS_COMPILE', 'ERROR') AS
	compile_state, /* compile st*/
	decode(bitand(s.flag2, 1024), 0, 'N', 'Y') AS
	use_no_index, /* USE NO INDEX ? */
	(
	SELECT
	MIN(time_dp)
	FROM
	sys.smon_scn_time
	WHERE
	( scn_wrp * 4294967296 + scn_bas ) > (
	SELECT
	MIN(t.spare3)
	FROM
	tab$ t,
	dependency$ d
	WHERE
	t.obj# = d.p_obj#
	AND w.obj# = d.d_obj#
	AND t.spare3 > w.lastrefreshscn
	)
	) AS
	stale_since,
	/* whether this is a PCT refresh enabled primary CUBE MV */
	( decode(bitand(w.xpflags, 8589934592), 0,(
	SELECT
	COUNT(*) AS num_pct_tables
	FROM
	(
	SELECT
	wd.sumobj#, wd.detailobj#
	FROM
	sys.sumdetail$ wd
	WHERE
	wd.detaileut > 0
	) wdd
	WHERE
	wdd.sumobj# = w.obj#
	),(
	SELECT
	COUNT(*) AS num_pct_tables
	FROM
	(
	SELECT
	wd.sumobj#,
	wd.detailobj#
	FROM
	sys.sumdetail$ wd
	WHERE
	wd.detaileut > 2147483648
	) /* special secondary cube row */ wdd
	WHERE
	wdd.sumobj# = w.obj#
	)) ) AS
	num_pct_tables,
	(
	SELECT
	num_fresh_partns
	FROM
	(
	SELECT
	sumobj#,
	SUM(num_fresh_partitions) AS num_fresh_partns,
	SUM(num_stale_partitions) AS num_stale_partns
	FROM
	(
	SELECT
	sumobj#,
	decode(partn_state, 'FRESH', partn_count, 0) AS num_fresh_partitions,
	decode(partn_state, 'STALE', partn_count, 0) AS num_stale_partitions
	FROM
	(
	SELECT
	sumobj#,
	partn_state,
	COUNT(*) AS partn_count
	FROM
	(
	SELECT
	sumobj#,
	(
	CASE
	WHEN partn_scn IS NULL THEN
	'FRESH'
	WHEN partn_scn < mv_scn THEN
	'FRESH'
	ELSE
	'STALE'
	END
	) partn_state
	FROM
	(
	SELECT
	sumobj#,
	mv_scn,
	sub_pobj#,
	MAX(partn_scn) partn_scn
	FROM /* from tabpart$ */
	(
	SELECT
	s.obj# AS sumobj#,
	s.lastrefreshscn AS mv_scn,
	t.obj# pobj#,
	t.obj# AS sub_pobj#,
	t.spare1 AS partn_scn
	FROM
	sys.sum$ s,
	sys.sumdetail$ sd,
	sys.tabpart$ t
	WHERE
	s.obj# = sd.sumobj#
	AND sd.detailobj# = t.bo#
	AND bitand(sd.detaileut, 2147483648) = 0
	/* NO secondary CUBE MV rows */
	UNION /* from sumdelta$ */
	SELECT
	s.obj# AS sumobj#,
	s.lastrefreshscn AS mv_scn,
	t.tableobj# pobj#,
	t.spare2 AS sub_pobj#,
	t.scn AS partn_scn
	FROM
	sys.sum$ s,
	sys.sumdetail$ sd,
	sys.sumdelta$ t
	WHERE
	s.obj# = sd.sumobj#
	AND sd.detailobj# = t.tableobj#
	AND bitand(sd.detaileut, 2147483648) = 0
	/* NO secondary CUBE MV rows */
	UNION /* from tabsubpart$ */
	SELECT
	s.sumobj#,
	s.mv_scn,
	s.pobj# pobj#,
	t.obj# AS sub_pobj#,
	t.spare1 AS partn_scn
	FROM
	tabsubpart$ t,
	(
	SELECT
	s.obj# AS sumobj#,
	s.lastrefreshscn AS mv_scn,
	t.obj# pobj#,
	t.spare1 AS partn_scn
	FROM
	sys.sum$ s,
	sys.sumdetail$ sd,
	sys.tabcompart$ t
	WHERE
	s.obj# = sd.sumobj#
	AND sd.detailobj# = t.bo#
	AND bitand(sd.detaileut, 2147483648) = 0/* NO secondary CUBE MV rows */
	) s
	WHERE
	t.pobj# = s.pobj#
	)
	GROUP BY
	sumobj#,
	mv_scn,
	sub_pobj#
	)
	)
	GROUP BY
	sumobj#,
	partn_state
	)
	)
	GROUP BY
	sumobj#
	) nfsp
	WHERE
	nfsp.sumobj# = w.obj#
	) AS
	num_fresh_pct_regions,
	(
	SELECT
	num_stale_partns
	FROM
	(
	SELECT
	sumobj#,
	SUM(num_fresh_partitions) AS num_fresh_partns,
	SUM(num_stale_partitions) AS num_stale_partns
	FROM
	(
	SELECT
	sumobj#,
	decode(partn_state, 'FRESH', partn_count, 0) AS num_fresh_partitions,
	decode(partn_state, 'STALE', partn_count, 0) AS num_stale_partitions
	FROM
	(
	SELECT
	sumobj#,
	partn_state,
	COUNT(*) AS partn_count
	FROM
	(
	SELECT
	sumobj#,
	(
	CASE
	WHEN partn_scn IS NULL THEN
	'FRESH'
	WHEN partn_scn < mv_scn THEN
	'FRESH'
	ELSE
	'STALE'
	END
	) partn_state
	FROM
	(
	SELECT
	sumobj#,
	mv_scn,
	sub_pobj#,
	MAX(partn_scn) partn_scn
	FROM /* from tabpart$ */
	(
	SELECT
	s.obj# AS sumobj#,
	s.lastrefreshscn AS mv_scn,
	t.obj# pobj#,
	t.obj# AS sub_pobj#,
	t.spare1 AS partn_scn
	FROM
	sys.sum$ s,
	sys.sumdetail$ sd,
	sys.tabpart$ t
	WHERE
	s.obj# = sd.sumobj#
	AND sd.detailobj# = t.bo#
	AND bitand(sd.detaileut, 2147483648) = 0
	/* NO secondary CUBE MV rows */
	UNION /* from sumdelta$ */
	SELECT
	s.obj# AS sumobj#,
	s.lastrefreshscn AS mv_scn,
	t.tableobj# pobj#,
	t.spare2 AS sub_pobj#,
	t.scn AS partn_scn
	FROM
	sys.sum$ s,
	sys.sumdetail$ sd,
	sys.sumdelta$ t
	WHERE
	s.obj# = sd.sumobj#
	AND sd.detailobj# = t.tableobj#
	AND bitand(sd.detaileut, 2147483648) = 0
	/* NO secondary CUBE MV rows */
	UNION /* from tabsubpart$ */
	SELECT
	s.sumobj#,
	s.mv_scn,
	s.pobj# pobj#,
	t.obj# AS sub_pobj#,
	t.spare1 AS partn_scn
	FROM
	tabsubpart$ t,
	(
	SELECT
	s.obj# AS sumobj#,
	s.lastrefreshscn AS mv_scn,
	t.obj# pobj#,
	t.spare1 AS partn_scn
	FROM
	sys.sum$ s,
	sys.sumdetail$ sd,
	sys.tabcompart$ t
	WHERE
	s.obj# = sd.sumobj#
	AND sd.detailobj# = t.bo#
	AND bitand(sd.detaileut, 2147483648) = 0/* NO secondary CUBE MV rows */
	) s
	WHERE
	t.pobj# = s.pobj#
	)
	GROUP BY
	sumobj#,
	mv_scn,
	sub_pobj#
	)
	)
	GROUP BY
	sumobj#,
	partn_state
	)
	)
	GROUP BY
	sumobj#
	) nfsp
	WHERE
	nfsp.sumobj# = w.obj#
	) AS
	num_stale_pct_regions,
	decode(bitand(t.property, 17179869184), 17179869184, 'NO', decode(bitand(t.property, 32), 32, 'N/A', 'YES')),
	s.eval_edition,
	CASE
	WHEN w.unusablebefore# IS NULL THEN
	NULL
	ELSE
	(
	SELECT
	name
	FROM
	obj$
	WHERE
	obj# = w.unusablebefore#
	)
	END,
	CASE
	WHEN w.unusablebeginning# IS NULL THEN
	NULL
	ELSE
	(
	SELECT
	name
	FROM
	obj$
	WHERE
	obj# = w.unusablebeginning#
	)
	END,
	nls_collation_name(nvl(o.dflcollid, 16382)),
	/* ON_QUERY_COMPUTATION */
	decode(bitand(s.flag3, 2097152), 0, 'N', 'Y') AS
	on_query_computation
	FROM
	sys.user$ u,
	sys.sum$ w,
	sys."_CURRENT_EDITION_OBJ" o,
	sys.snap$ s,
	sys.tab$ t,
	sys.obj$ co
	WHERE
	w.containernam (+) = s.vname
	AND w.containerobj# = co.obj# (+)
	AND co.obj# = t.obj# (+)
	AND o.obj# (+) = w.obj#
	AND o.owner# = u.user# (+)
	AND ( ( u.name = s.sowner )
	OR ( u.name IS NULL ) )
	AND s.instsite = 0
	AND NOT ( bitand(s.flag, 268435456) > 0 /* MV with user-defined types */
	AND bitand(s.objflag, 32) > 0 ) /* secondary MV */
	AND NOT ( bitand(s.flag2, 33554432) > 0 ) /* secondary CUBE MV */
	AND NOT ( bitand(s.flag3, 512) > 0 ) /* zonemap */;

view raw dba_mviews.sql hosted with ❤ by GitHub

A great number of subquery runs behind the scenes. As this database serves in an air-gapped environment, I do not have any execution plans of the real-time scenario. Below is the execution plan in my test environment. Access paths are similar.

	Execution Plan
	------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
	| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
	--------------------------------------------------------------------------------------------------------------------
	| 0 | SELECT STATEMENT | | 1 | 260 | 171 (5)| 00:00:01 |
	| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | SNAP$ | 3 | 366 | 0 (0)| 00:00:01 |
	|* 2 | INDEX RANGE SCAN | I_SNAP2 | 1 | | 0 (0)| 00:00:01 |
	| 3 | SORT AGGREGATE | | 1 | 16 | | |
	|* 4 | FILTER | | | | | |
	| 5 | TABLE ACCESS FULL | SMON_SCN_TIME | 1932 | 30912 | 102 (0)| 00:00:01 |
	| 6 | SORT AGGREGATE | | 1 | 28 | | |
	| 7 | NESTED LOOPS | | 4 | 112 | 5 (0)| 00:00:01 |
	| 8 | TABLE ACCESS BY INDEX ROWID BATCHED | DEPENDENCY$ | 4 | 40 | 4 (0)| 00:00:01 |
	|* 9 | INDEX RANGE SCAN | I_DEPENDENCY1 | 4 | | 3 (0)| 00:00:01 |
	|* 10 | TABLE ACCESS CLUSTER | TAB$ | 1 | 18 | 1 (0)| 00:00:01 |
	|* 11 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 (0)| 00:00:01 |
	| 12 | SORT AGGREGATE | | 1 | 8 | | |
	|* 13 | TABLE ACCESS FULL | SUMDETAIL$ | 1 | 8 | 2 (0)| 00:00:01 |
	| 14 | SORT AGGREGATE | | 1 | 8 | | |
	|* 15 | TABLE ACCESS FULL | SUMDETAIL$ | 1 | 8 | 2 (0)| 00:00:01 |
	| 16 | SORT GROUP BY NOSORT | | 21 | 546 | 19 (22)| 00:00:01 |
	| 17 | VIEW | | 21 | 546 | 19 (22)| 00:00:01 |
	| 18 | SORT GROUP BY | | 21 | 420 | 19 (22)| 00:00:01 |
	| 19 | VIEW | | 21 | 420 | 18 (17)| 00:00:01 |
	| 20 | SORT GROUP BY | | 21 | 1092 | 18 (17)| 00:00:01 |
	| 21 | VIEW | | 21 | 1092 | 17 (12)| 00:00:01 |
	| 22 | SORT UNIQUE | | 21 | 2026 | 17 (12)| 00:00:01 |
	| 23 | UNION-ALL | | | | | |
	| 24 | NESTED LOOPS | | 19 | 893 | 6 (0)| 00:00:01 |
	| 25 | NESTED LOOPS | | 19 | 893 | 6 (0)| 00:00:01 |
	| 26 | NESTED LOOPS | | 2 | 48 | 2 (0)| 00:00:01 |
	| 27 | TABLE ACCESS BY INDEX ROWID | SUM$ | 1 | 11 | 1 (0)| 00:00:01 |
	|* 28 | INDEX UNIQUE SCAN | I_SUM$_1 | 1 | | 0 (0)| 00:00:01 |
	|* 29 | TABLE ACCESS BY INDEX ROWID | SUMDETAIL$ | 2 | 26 | 1 (0)| 00:00:01 |
	|* 30 | INDEX RANGE SCAN | I_SUMDETAIL$_1 | 2 | | 0 (0)| 00:00:01 |
	|* 31 | INDEX RANGE SCAN | I_TABPART_BOPART$ | 2 | | 1 (0)| 00:00:01 |
	| 32 | TABLE ACCESS BY INDEX ROWID | TABPART$ | 9 | 207 | 2 (0)| 00:00:01 |
	| 33 | NESTED LOOPS | | 1 | 63 | 4 (0)| 00:00:01 |
	| 34 | NESTED LOOPS | | 2 | 63 | 4 (0)| 00:00:01 |
	| 35 | NESTED LOOPS | | 1 | 50 | 3 (0)| 00:00:01 |
	| 36 | TABLE ACCESS BY INDEX ROWID | SUM$ | 1 | 11 | 1 (0)| 00:00:01 |
	|* 37 | INDEX UNIQUE SCAN | I_SUM$_1 | 1 | | 0 (0)| 00:00:01 |
	| 38 | TABLE ACCESS FULL | SUMDELTA$ | 1 | 39 | 2 (0)| 00:00:01 |
	|* 39 | INDEX RANGE SCAN | I_SUMDETAIL$_2 | 2 | | 0 (0)| 00:00:01 |
	|* 40 | TABLE ACCESS BY INDEX ROWID | SUMDETAIL$ | 1 | 13 | 1 (0)| 00:00:01 |
	|* 41 | HASH JOIN | | 1 | 57 | 5 (0)| 00:00:01 |
	| 42 | NESTED LOOPS | | 1 | 34 | 3 (0)| 00:00:01 |
	| 43 | NESTED LOOPS | | 2 | 34 | 3 (0)| 00:00:01 |
	| 44 | NESTED LOOPS | | 1 | 21 | 3 (0)| 00:00:01 |
	| 45 | TABLE ACCESS BY INDEX ROWID | SUM$ | 1 | 11 | 1 (0)| 00:00:01 |
	|* 46 | INDEX UNIQUE SCAN | I_SUM$_1 | 1 | | 0 (0)| 00:00:01 |
	| 47 | TABLE ACCESS FULL | TABCOMPART$ | 1 | 10 | 2 (0)| 00:00:01 |
	|* 48 | INDEX RANGE SCAN | I_SUMDETAIL$_2 | 2 | | 0 (0)| 00:00:01 |
	|* 49 | TABLE ACCESS BY INDEX ROWID | SUMDETAIL$ | 1 | 13 | 0 (0)| 00:00:01 |
	| 50 | TABLE ACCESS FULL | TABSUBPART$ | 32 | 736 | 2 (0)| 00:00:01 |
	| 51 | SORT GROUP BY NOSORT | | 21 | 546 | 19 (22)| 00:00:01 |
	| 52 | VIEW | | 21 | 546 | 19 (22)| 00:00:01 |
	| 53 | SORT GROUP BY | | 21 | 420 | 19 (22)| 00:00:01 |
	| 54 | VIEW | | 21 | 420 | 18 (17)| 00:00:01 |
	| 55 | SORT GROUP BY | | 21 | 1092 | 18 (17)| 00:00:01 |
	| 56 | VIEW | | 21 | 1092 | 17 (12)| 00:00:01 |
	| 57 | SORT UNIQUE | | 21 | 2026 | 17 (12)| 00:00:01 |
	| 58 | UNION-ALL | | | | | |
	| 59 | NESTED LOOPS | | 19 | 893 | 6 (0)| 00:00:01 |
	| 60 | NESTED LOOPS | | 19 | 893 | 6 (0)| 00:00:01 |
	| 61 | NESTED LOOPS | | 2 | 48 | 2 (0)| 00:00:01 |
	| 62 | TABLE ACCESS BY INDEX ROWID | SUM$ | 1 | 11 | 1 (0)| 00:00:01 |
	|* 63 | INDEX UNIQUE SCAN | I_SUM$_1 | 1 | | 0 (0)| 00:00:01 |
	|* 64 | TABLE ACCESS BY INDEX ROWID | SUMDETAIL$ | 2 | 26 | 1 (0)| 00:00:01 |
	|* 65 | INDEX RANGE SCAN | I_SUMDETAIL$_1 | 2 | | 0 (0)| 00:00:01 |
	|* 66 | INDEX RANGE SCAN | I_TABPART_BOPART$ | 2 | | 1 (0)| 00:00:01 |
	| 67 | TABLE ACCESS BY INDEX ROWID | TABPART$ | 9 | 207 | 2 (0)| 00:00:01 |
	| 68 | NESTED LOOPS | | 1 | 63 | 4 (0)| 00:00:01 |
	| 69 | NESTED LOOPS | | 2 | 63 | 4 (0)| 00:00:01 |
	| 70 | NESTED LOOPS | | 1 | 50 | 3 (0)| 00:00:01 |
	| 71 | TABLE ACCESS BY INDEX ROWID | SUM$ | 1 | 11 | 1 (0)| 00:00:01 |
	|* 72 | INDEX UNIQUE SCAN | I_SUM$_1 | 1 | | 0 (0)| 00:00:01 |
	| 73 | TABLE ACCESS FULL | SUMDELTA$ | 1 | 39 | 2 (0)| 00:00:01 |
	|* 74 | INDEX RANGE SCAN | I_SUMDETAIL$_2 | 2 | | 0 (0)| 00:00:01 |
	|* 75 | TABLE ACCESS BY INDEX ROWID | SUMDETAIL$ | 1 | 13 | 1 (0)| 00:00:01 |
	|* 76 | HASH JOIN | | 1 | 57 | 5 (0)| 00:00:01 |
	| 77 | NESTED LOOPS | | 1 | 34 | 3 (0)| 00:00:01 |
	| 78 | NESTED LOOPS | | 2 | 34 | 3 (0)| 00:00:01 |
	| 79 | NESTED LOOPS | | 1 | 21 | 3 (0)| 00:00:01 |
	| 80 | TABLE ACCESS BY INDEX ROWID | SUM$ | 1 | 11 | 1 (0)| 00:00:01 |
	|* 81 | INDEX UNIQUE SCAN | I_SUM$_1 | 1 | | 0 (0)| 00:00:01 |
	| 82 | TABLE ACCESS FULL | TABCOMPART$ | 1 | 10 | 2 (0)| 00:00:01 |
	|* 83 | INDEX RANGE SCAN | I_SUMDETAIL$_2 | 2 | | 0 (0)| 00:00:01 |
	|* 84 | TABLE ACCESS BY INDEX ROWID | SUMDETAIL$ | 1 | 13 | 0 (0)| 00:00:01 |
	| 85 | TABLE ACCESS FULL | TABSUBPART$ | 32 | 736 | 2 (0)| 00:00:01 |
	| 86 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 39 | 3 (0)| 00:00:01 |
	|* 87 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 2 (0)| 00:00:01 |
	| 88 | TABLE ACCESS BY INDEX ROWID BATCHED | OBJ$ | 1 | 39 | 3 (0)| 00:00:01 |
	|* 89 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 2 (0)| 00:00:01 |
	| 90 | NESTED LOOPS OUTER | | 1 | 260 | 16 (0)| 00:00:01 |
	| 91 | NESTED LOOPS OUTER | | 1 | 247 | 15 (0)| 00:00:01 |
	|* 92 | FILTER | | | | | |
	| 93 | NESTED LOOPS OUTER | | 1 | 242 | 14 (0)| 00:00:01 |
	| 94 | NESTED LOOPS OUTER | | 1 | 225 | 13 (0)| 00:00:01 |
	|* 95 | HASH JOIN OUTER | | 1 | 184 | 4 (0)| 00:00:01 |
	|* 96 | TABLE ACCESS FULL | SNAP$ | 1 | 122 | 2 (0)| 00:00:01 |
	| 97 | TABLE ACCESS FULL | SUM$ | 3 | 186 | 2 (0)| 00:00:01 |
	| 98 | VIEW PUSHED PREDICATE | _CURRENT_EDITION_OBJ | 1 | 41 | 9 (0)| 00:00:01 |
	|* 99 | FILTER | | | | | |
	| 100 | NESTED LOOPS | | 1 | 52 | 4 (0)| 00:00:01 |
	| 101 | TABLE ACCESS BY INDEX ROWID BATCHED| OBJ$ | 1 | 27 | 3 (0)| 00:00:01 |
	|*102 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 2 (0)| 00:00:01 |
	|*103 | INDEX RANGE SCAN | I_USER2 | 1 | 25 | 1 (0)| 00:00:01 |
	|*104 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 1 | 6 | 2 (0)| 00:00:01 |
	|*105 | INDEX RANGE SCAN | I_USER_EDITIONING | 2 | | 1 (0)| 00:00:01 |
	|*106 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 1 | 6 | 2 (0)| 00:00:01 |
	|*107 | INDEX RANGE SCAN | I_USER_EDITIONING | 2 | | 1 (0)| 00:00:01 |
	| 108 | NESTED LOOPS SEMI | | 1 | 29 | 2 (0)| 00:00:01 |
	|*109 | INDEX SKIP SCAN | I_USER2 | 1 | 20 | 1 (0)| 00:00:01 |
	|*110 | INDEX RANGE SCAN | I_OBJ4 | 1 | 9 | 1 (0)| 00:00:01 |
	| 111 | TABLE ACCESS CLUSTER | USER$ | 1 | 17 | 1 (0)| 00:00:01 |
	|*112 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| 00:00:01 |
	|*113 | INDEX RANGE SCAN | I_OBJ1 | 1 | 5 | 1 (0)| 00:00:01 |
	| 114 | TABLE ACCESS CLUSTER | TAB$ | 1 | 13 | 1 (0)| 00:00:01 |
	|*115 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 (0)| 00:00:01 |
	--------------------------------------------------------------------------------------------------------------------

view raw execution_plan_dba_mviews.txt hosted with ❤ by GitHub

The execution plan has some full table scans such as SYS.SUMDELTA$, SYS.SUMDETAIL$, SYS.SMON_SCN_TIME, SYS.TABCOMPART$ etc. When we exclude the three columns (NUM_STALE_PCT_REGIONS, NUM_FRESH_PCT_REGIONS, STALE_SINCE) from query instead of selecting all columns (*), it runs super fast.

I will inspect why adding these three columns to query costs that much within two sections. 

Section 1 (NUM_STALE_PCT_REGIONS, NUM_FRESH_PCT_REGIONS) 

NUM_FRESH_PCT_REGIONS and NUM_STALE_PCT_REGIONS are added to dba_mviews from 11g onwards. These two columns hold the number of fresh/stale PCT partition regions.

What is Partition Change Tracking?

Partition Change Tracking (PCT) is a cool feature for fast refresh of materialized views when one of the base tables is partitioned. By using this feature, It is possible and advantageous to track freshness to a finer grain than the entire materialized view. The ability to identify which rows in a materialized view are affected by a certain detail table partition, is known as Partition Change Tracking. When one or more of the detail tables are partitioned, it may be possible to identify the specific rows in the materialized view that correspond to a modified detail partition(s); those rows become stale when a partition is modified while all other rows remain fresh.

In a nutshell, PCT helps fast refresh of materialized views and in dba_mviews data dictionary, this information is shown, but it is expensive. 

In the MOS (https://support.oracle.com/epmos/faces/DocumentDisplay?id=1322308.1&displayIndex=1) There is a document similar to our case "Select from DBA_MVIEWs is very slow in 11g. (Doc ID 1322308.1)" According to the document, the cause of the problem and the solution is as stated below.

Cause: 

     The tables TABPART$, TABSUBPART$ and TABCOMPART$ are queried internally. If the data volume is high in these internal tables, then the query will be very slow." 

        This is expected behaviour with  high volumes of data in these tables.

Solution:

    If there is no specific reason for querying the columns NUM_FRESH_PCT_REGIONS & NUM_STALE_PCT_REGIONS then create an view on DBA_MVIEWS excluding these columns and query the new view.

To be honest, it is not a satisfying answer and there is a lot to say.

First of all;

In 19c Release, The query's execution plan is different than 11g. 

Based on the execution plan at line 38 and line 73, CBO accesses SYS.SUMDELTA$ table with full table scans. When i checked the statistics of this table, statistics were missing and  I thought that I found something worthy and i immediately gathered dictionary stats and fixed object stats, but statistics were still not gathered. I gathered stats of this table explicitly by using dbms_stats.gather_table_stats procedure and ran the query again but the execution plan did not change.  Considering the "Doc ID 1684072.1" Statistics are not gathered for the following objects:

    * IOT overflow segments

    * IOT mapping tables

    * snapshot logs

    * objects in the recycle bin 

    * external tables

    * context DR$/DR# tables

    * sys.sumdelta$:

    * secondary tables of domain indexes

There should be no statistics for the sys.sumdelta$ table and i dropped the statistics. In our ten years old database, SYS.SUMDELTA$ table was storing more than 200.000 rows. 

Actually, In an Oracle database, a materialized view log, associated with the master table of a materialized view and the SYS.SUMDELTA$ are equally responsible in the fast-refresh of materialized views.

When one of the base tables in the mview query undergoes DML changes (such as INSERT, UPDATE, or DELETE), the rows describing those changes are stored in the materialized view log. If these base tables undergo a bulk operation such as a direct-path insert (/*+ APPEND */) , the newly created blocks information is stored in SYS.SUMDELTA$ table and nothing gets recorded in  the materialized view logs.

Strangely, in this database, there was not any materialized view logs. All the materialized views were created with "REFRESH COMPLETE ON DEMAND" syntax and also always refreshed with complete method and atomic refresh set to false flag. As the atomic refresh flag set to false, Oracle optimizes refreshes by using parallel DML and direct path inserts. Nevertheless, sys.sumdelta$ still holds records in case of need for fast refresh method. 

Also, when i checked over the data stored in SYS.SUMDELTA$ table, i discovered that, many of materialized views were dependent to each other and refreshed on daily periods and their refresh times were not designed correctly. That faulty refresh period design was among the factors leading to high volumes of data on SYS.SUMDELTA$ table. Their refresh periods should had been designed according to the dependency to each other. Dba_dependencies dictionary view can be used as a starting point for that purpose. Moreover, lots of materialized views were dropped over time, but the bulk operation records of base tables of these dropped materialized views were not deleted. I truncated SYS.SUMDELTA$ table and refreshed all the materialized views and redesigned refresh periods. After that operation, table size reduced dramatically and full table scans benefitted from reduced table size. 

There is also a tracing event switch to disable direct inserts logging in sumdelta$ table. (Bug 16556250)

SQL> alter system set events '10978 trace name context forever, level 128';

view raw disable_direct_insertlogging_event.sql hosted with ❤ by GitHub

I would not recommend using that event switch, as this event is set, direct inserts to base tables wont be recorded anymore and when fast refresh is used, you will end up with wrong result.

Herethefore, we reduced the size of the `sys.sumdelta$` table to an ideal amount, but now the execution time for the query `select * from dba_mviews` takes 37 seconds. There is one more column to inspect.

Section 2 (STALE_SINCE)

When we exclude the `stale_since` column, the query runs fast now. The `stale_since` column stores "the time from when the materialized view became stale". Here is the abbreviated form of the query and the execution plan:

	SELECT
	(
	SELECT
	MIN(time_dp)
	FROM
	sys.smon_scn_time
	WHERE
	( scn_wrp * 4294967296 + scn_bas ) > (
	SELECT
	MIN(t.spare3)
	FROM
	tab$ t,
	dependency$ d
	WHERE
	t.obj# = d.p_obj#
	AND w.obj# = d.d_obj#
	AND t.spare3 > w.lastrefreshscn
	)
	) AS stale_since
	FROM
	sys.user$ u,
	sys.sum$ w,
	sys."_CURRENT_EDITION_OBJ" o,
	sys.snap$ s,
	sys.tab$ t,
	sys.obj$ co
	WHERE
	w.containernam (+) = s.vname
	AND w.containerobj# = co.obj# (+)
	AND co.obj# = t.obj# (+)
	AND o.obj# (+) = w.obj#
	AND o.owner# = u.user# (+)
	AND ( ( u.name = s.sowner )
	OR ( u.name IS NULL ) )
	AND s.instsite = 0
	AND NOT ( bitand(s.flag, 268435456) > 0 /* MV with user-defined types */
	AND bitand(s.objflag, 32) > 0 ) /* secondary MV */
	AND NOT ( bitand(s.flag2, 33554432) > 0 ) /* secondary CUBE MV */
	AND NOT ( bitand(s.flag3, 512) > 0 ) /* zonemap */;
	Execution Plan
	----------------------------------------------------------
	Plan hash value: 800194146
	--------------------------------------------------------------------------------------------------------------------
	| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
	--------------------------------------------------------------------------------------------------------------------
	| 0 | SELECT STATEMENT | | 1 | 112 | 118 (0)| 00:00:01 |
	| 1 | SORT AGGREGATE | | 1 | 16 | | |
	|* 2 | FILTER | | | | | |
	| 3 | TABLE ACCESS FULL | SMON_SCN_TIME | 1932 | 30912 | 102 (0)| 00:00:01 |
	| 4 | SORT AGGREGATE | | 1 | 28 | | |
	| 5 | NESTED LOOPS | | 4 | 112 | 5 (0)| 00:00:01 |
	| 6 | TABLE ACCESS BY INDEX ROWID BATCHED | DEPENDENCY$ | 4 | 40 | 4 (0)| 00:00:01 |
	|* 7 | INDEX RANGE SCAN | I_DEPENDENCY1 | 4 | | 3 (0)| 00:00:01 |
	|* 8 | TABLE ACCESS CLUSTER | TAB$ | 1 | 18 | 1 (0)| 00:00:01 |
	|* 9 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 (0)| 00:00:01 |
	| 10 | NESTED LOOPS OUTER | | 1 | 112 | 11 (0)| 00:00:01 |
	| 11 | NESTED LOOPS OUTER | | 1 | 107 | 10 (0)| 00:00:01 |
	|* 12 | FILTER | | | | | |
	| 13 | NESTED LOOPS OUTER | | 1 | 102 | 9 (0)| 00:00:01 |
	| 14 | NESTED LOOPS OUTER | | 1 | 85 | 8 (0)| 00:00:01 |
	|* 15 | HASH JOIN OUTER | | 1 | 70 | 4 (0)| 00:00:01 |
	|* 16 | TABLE ACCESS FULL | SNAP$ | 1 | 38 | 2 (0)| 00:00:01 |
	| 17 | TABLE ACCESS FULL | SUM$ | 3 | 96 | 2 (0)| 00:00:01 |
	| 18 | VIEW PUSHED PREDICATE | _CURRENT_EDITION_OBJ | 1 | 15 | 4 (0)| 00:00:01 |
	|* 19 | FILTER | | | | | |
	| 20 | NESTED LOOPS | | 1 | 46 | 4 (0)| 00:00:01 |
	| 21 | TABLE ACCESS BY INDEX ROWID BATCHED| OBJ$ | 1 | 21 | 3 (0)| 00:00:01 |
	|* 22 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 2 (0)| 00:00:01 |
	|* 23 | INDEX RANGE SCAN | I_USER2 | 1 | 25 | 1 (0)| 00:00:01 |
	|* 24 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 1 | 6 | 2 (0)| 00:00:01 |
	|* 25 | INDEX RANGE SCAN | I_USER_EDITIONING | 2 | | 1 (0)| 00:00:01 |
	|* 26 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 1 | 6 | 2 (0)| 00:00:01 |
	|* 27 | INDEX RANGE SCAN | I_USER_EDITIONING | 2 | | 1 (0)| 00:00:01 |
	| 28 | NESTED LOOPS SEMI | | 1 | 29 | 2 (0)| 00:00:01 |
	|* 29 | INDEX SKIP SCAN | I_USER2 | 1 | 20 | 1 (0)| 00:00:01 |
	|* 30 | INDEX RANGE SCAN | I_OBJ4 | 1 | 9 | 1 (0)| 00:00:01 |
	| 31 | TABLE ACCESS CLUSTER | USER$ | 1 | 17 | 1 (0)| 00:00:01 |
	|* 32 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| 00:00:01 |
	|* 33 | INDEX RANGE SCAN | I_OBJ1 | 1 | 5 | 1 (0)| 00:00:01 |
	| 34 | TABLE ACCESS CLUSTER | TAB$ | 1 | 5 | 1 (0)| 00:00:01 |
	|* 35 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 (0)| 00:00:01 |
	--------------------------------------------------------------------------------------------------------------------

view raw stale_since_query.sql hosted with ❤ by GitHub

Based on the execution plan at line 3, CBO accesses the `SYS.SMON_SCN_TIME` table with a full table scan. At this stage, basically an SCN to timestamp conversion takes place.  In "Doc Id 365536.1, How To Map SCN To Timestamp Before 10g?", SCN and time conversion are clearly explained. According to this document:

`SYS.SMON_SCN_TIME` will have a maximum of 1440 rows, and each record will be for a 5-minute period. Oracle maintains this information for a maximum of 5 days, after which the records will be recycled.

This means that data is stored 12 times per hour * 24 hours * 5 days = 1440 rows. 

SCN value is stored internally as :

    * SCN_wrap

    * SCN_base

But, if you are using the flashback time travel feature, the data stored in this table will depend on the Flashback Data Archive retention period parameter.

I also suggest DBAs administering databases with long retention period requirements to look into "Jonathan Lewis' Flashback Fail post" to get more on `SMON_SCN_TIME` and to avoid ORA-2475.

For example, for a two-year retention, `SMON_SCN_TIME` will store more than 200,000 rows. As the CBO accesses this table with a full table scan, for each row in `dba_mviews` view, each row of the `SMON_SCN_TIME` table is read in a sequential (serial) order. And that is the problem in a nutshell.

In general, the required steps to avoid a full table scan are "CREATING AN INDEX" or "REWRITING THE QUERY".

I can speed up this query easily by creating an index on `(scn_wrp * 4294967296 + scn_bas)`. In my demo environment, I will create it.

	SQL> CREATE INDEX SYS.SMON_SCN_TIME_TIM_IDX_TEMP ON SYS.SMON_SCN_TIME
	(SCN_WRP * 4294967296 + SCN_BAS)
	LOGGING
	TABLESPACE SYSAUX
	PCTFREE 10
	INITRANS 2
	MAXTRANS 255
	STORAGE (
	INITIAL 64K
	NEXT 1M
	MINEXTENTS 1
	MAXEXTENTS UNLIMITED
	PCTINCREASE 0
	BUFFER_POOL DEFAULT
	);

view raw index_on_smon_scn.sql hosted with ❤ by GitHub

The new execution plan, after the index has been created, is as follows. 

	Execution Plan
	----------------------------------------------------------
	Plan hash value: 1875754335
	--------------------------------------------------------------------------------------------------------------------------
	| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
	--------------------------------------------------------------------------------------------------------------------------
	| 0 | SELECT STATEMENT | | 1 | 112 | 17 (0)| 00:00:01 |
	| 1 | SORT AGGREGATE | | 1 | 21 | | |
	| 2 | TABLE ACCESS BY INDEX ROWID BATCHED | SMON_SCN_TIME | 97 | 2037 | 6 (0)| 00:00:01 |
	|* 3 | INDEX RANGE SCAN | SMON_SCN_TIME_TIM_IDX_TEMP | 19 | | 2 (0)| 00:00:01 |
	| 4 | SORT AGGREGATE | | 1 | 28 | | |
	| 5 | NESTED LOOPS | | 4 | 112 | 5 (0)| 00:00:01 |
	| 6 | TABLE ACCESS BY INDEX ROWID BATCHED | DEPENDENCY$ | 4 | 40 | 4 (0)| 00:00:01 |
	|* 7 | INDEX RANGE SCAN | I_DEPENDENCY1 | 4 | | 3 (0)| 00:00:01 |
	|* 8 | TABLE ACCESS CLUSTER | TAB$ | 1 | 18 | 1 (0)| 00:00:01 |
	|* 9 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 (0)| 00:00:01 |
	| 10 | NESTED LOOPS OUTER | | 1 | 112 | 11 (0)| 00:00:01 |
	| 11 | NESTED LOOPS OUTER | | 1 | 107 | 10 (0)| 00:00:01 |
	|* 12 | FILTER | | | | | |
	| 13 | NESTED LOOPS OUTER | | 1 | 102 | 9 (0)| 00:00:01 |
	| 14 | NESTED LOOPS OUTER | | 1 | 85 | 8 (0)| 00:00:01 |
	|* 15 | HASH JOIN OUTER | | 1 | 70 | 4 (0)| 00:00:01 |
	|* 16 | TABLE ACCESS FULL | SNAP$ | 1 | 38 | 2 (0)| 00:00:01 |
	| 17 | TABLE ACCESS FULL | SUM$ | 3 | 96 | 2 (0)| 00:00:01 |
	| 18 | VIEW PUSHED PREDICATE | _CURRENT_EDITION_OBJ | 1 | 15 | 4 (0)| 00:00:01 |
	|* 19 | FILTER | | | | | |
	| 20 | NESTED LOOPS | | 1 | 46 | 4 (0)| 00:00:01 |
	| 21 | TABLE ACCESS BY INDEX ROWID BATCHED| OBJ$ | 1 | 21 | 3 (0)| 00:00:01 |
	|* 22 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 2 (0)| 00:00:01 |
	|* 23 | INDEX RANGE SCAN | I_USER2 | 1 | 25 | 1 (0)| 00:00:01 |
	|* 24 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 1 | 6 | 2 (0)| 00:00:01 |
	|* 25 | INDEX RANGE SCAN | I_USER_EDITIONING | 2 | | 1 (0)| 00:00:01 |
	|* 26 | TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ | 1 | 6 | 2 (0)| 00:00:01 |
	|* 27 | INDEX RANGE SCAN | I_USER_EDITIONING | 2 | | 1 (0)| 00:00:01 |
	| 28 | NESTED LOOPS SEMI | | 1 | 29 | 2 (0)| 00:00:01 |
	|* 29 | INDEX SKIP SCAN | I_USER2 | 1 | 20 | 1 (0)| 00:00:01 |
	|* 30 | INDEX RANGE SCAN | I_OBJ4 | 1 | 9 | 1 (0)| 00:00:01 |
	| 31 | TABLE ACCESS CLUSTER | USER$ | 1 | 17 | 1 (0)| 00:00:01 |
	|* 32 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| 00:00:01 |
	|* 33 | INDEX RANGE SCAN | I_OBJ1 | 1 | 5 | 1 (0)| 00:00:01 |
	| 34 | TABLE ACCESS CLUSTER | TAB$ | 1 | 5 | 1 (0)| 00:00:01 |
	|* 35 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | 0 (0)| 00:00:01 |
	--------------------------------------------------------------------------------------------------------------------------

view raw stale_since_query2.sql hosted with ❤ by GitHub

Now the full table scan of `SMON_SCN_TIME` transformed to INDEX RANGE SCAN. 

I also tested this workaround on the snapshot standby database of the real production database, and the query ran within 1 second.

Wait a minute, folks, something is wrong here.

I will not be advising you to create an index on an internal table belonging to the `sys` user. That is up to Oracle developers, not the DBAs. Creating an index on such an internal table may have some negative unintended consequences, as the more indexes created, the more slowly inserts. It should be tested thoroughly.

OK then, What about rewriting the query?

On `SMON_SCN_TIME`, `TIME_MP` column is already indexed and it stores the time, calculated with "scn_wrp * 4294967296 + scn_bas". I still wonder, why this indexed column is not used in this dictionary view query.

I created another view called `dba_mviews2`, just replacing "scn_wrp * 4294967296 + scn_bas" statement with "time_mp". Now "select * from dba_mviews2" runs super fast just like FLASH.

Hope it helps.