SQLTXPLAIN Report: sqlt_x0319p14_oaprd1_s0001.html

Identification
SQL Statement
Explain Plan
Object Depencency
Tables
Indexes
Table Columns
Index Columns
Metadata
Optimizer Trace (EVENT 10053)

Identification

Statement ID:

Input Filename:

Host Name:

CPUs:

Database Name:

Instance Name:

Platform:

Product Version:

RDBMS Version:

SQL Statement

SELECT 'Y'
FROM
TIME_ATTRIBUTES_TB WHERE ATTRIBUTE_ID IN (SELECT ATTRIBUTE_ID
FROM TIME_ATTRIBUTE_USAGES WHERE BUILDING_BLOCK_ID = :B3 AND
BUILDING_BLOCK_OVN = :B2 ) AND ATTRIBUTE_CATEGORY = 'REASON' AND
ATTRIBUTE7 = DECODE (:B1 , NULL, ATTRIBUTE7, :B1 )

Explain Plan

Id	Exec Order	Explain Plan Operation
0:	7	`SELECT STATEMENT`
1:	6	`NESTED LOOPS`
2:	3	`SORT UNIQUE`
3:	2	`TABLE ACCESS BY INDEX ROWID TIME_ATTRIBUTE_USAGES`
4:	1	`INDEX RANGE SCAN TIME_ATTRIBUTE_USAGES_FK2`
5:	5	`TABLE ACCESS BY INDEX ROWID TIME_ATTRIBUTES_TB`
6:	4	`INDEX UNIQUE SCAN TIME_ATTRIBUTES_TB_PK`

Id	Object Type	Parent Id	Pos	Obj Inst	Search Cols	Estim Card	Estim Bytes	Cost	CPU Cost	IO Cost	Time	Remarks
0:			13			1	40	13	7136529	12	1
1:		0	1			1	40	13	7136529	12	1	10.2.0.22963004813
2:		1	1			1	17	9	70453	9	1
3:	TABLE	2	1	2		1	17	9	70453	9	1
4:	INDEX	3	1		2	12		4	30886	4	1
5:	TABLE	1	2	1		1	23	3	23652	3	1
6:	INDEX (UNIQUE)	5	1		1	1		2	16143	2	1

Id	Column Name	Column Value
1:	Projection	(#keys=0)
2:	Projection	(#keys=1) "ATTRIBUTE_ID"[NUMBER,22]
3:	Projection	"ATTRIBUTE_ID"[NUMBER,22]
4:	Access Predicates	"BUILDING_BLOCK_ID"=TO_NUMBER(:B3) AND "BUILDING_BLOCK_OVN"=TO_NUMBER(:B2)
4:	Projection	"TIME_ATTRIBUTE_USAGES".ROWID[ROWID,10]
5:	Filter Predicates	"ATTRIBUTE_CATEGORY"='REASON' AND "ATTRIBUTE7"=DECODE(:B1,NULL,"ATTRIBUTE7",:B1)
6:	Access Predicates	"ATTRIBUTE_ID"="ATTRIBUTE_ID"
6:	Projection	"TIME_ATTRIBUTES_TB".ROWID[ROWID,10]

Object Depencency

hide

Type	Object Owner.Name	Hash Value	Address	Metadata
TABLE	TIME_ATTRIBUTES_TB	1304389938	0000000439BBFAA8
TABLE	TIME_ATTRIBUTE_USAGES	1746193827	0000000439BE0E80
SYNONYM	TIME_ATTRIBUTES_TB	3918164333	000000045C8ADA30	Metadata
SYNONYM	TIME_ATTRIBUTE_USAGES	441867442	000000045C8AD1C0	Metadata

Tables

hide

Owner.Table Name	Current COUNT	Num^* Rows	Sample^* Size	Last^* Analyzed	Avg^* Row Len	Chain^* Cnt	Blocks^*	Empty^* Blocks	Avg^* Space	Global^* Stats	Columns	Metadata
TIME_ATTRIBUTES_TB	749217143	706053520	35302676	29-SEP-07 05:20:49	56	0	7164508	0	0	YES	Columns
TIME_ATTRIBUTE_USAGES	754405384	710932460	35546623	29-SEP-07 05:16:34	40	0	4864950	0	0	YES	Columns

(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics

Owner.Table Name	Temporary	Partitioned	Logging	Degree	Cache	IOT Type	Ini Trans	Max Trans	Freelist Groups	Freelists
TIME_ATTRIBUTES_TB	N	NO	YES	1	N		10	255	4	4
TIME_ATTRIBUTE_USAGES	N	NO	YES	1	N		10	255	4	4

Indexes

hide

Table Owner.Table Name	Index Owner.Index Name	Used	Index Type	Uniqueness	Indexed Columns	Columns
TIME_ATTRIBUTES_TB	TIME_ATTRIBUTES_TB_F2		FUNCTION-BASED NORMAL	NONUNIQUE	SYS_NC00037$	Columns
TIME_ATTRIBUTES_TB	TIME_ATTRIBUTES_TB_FK1		NORMAL	NONUNIQUE	BLD_BLK_INFO_TYPE_ID	Columns
TIME_ATTRIBUTES_TB	TIME_ATTRIBUTES_TB_FK2		NORMAL	NONUNIQUE	ATTRIBUTE_CATEGORY	Columns
TIME_ATTRIBUTES_TB	TIME_ATTRIBUTES_TB_FK3		NORMAL	NONUNIQUE	ATTRIBUTE1	Columns
TIME_ATTRIBUTES_TB	TIME_ATTRIBUTES_TB_FK4		NORMAL	NONUNIQUE	ATTRIBUTE2	Columns
TIME_ATTRIBUTES_TB	TIME_ATTRIBUTES_TB_FK5		NORMAL	NONUNIQUE	ATTRIBUTE3	Columns
TIME_ATTRIBUTES_TB	TIME_ATTRIBUTES_TB_N1		NORMAL	NONUNIQUE	DATA_SET_ID	Columns
TIME_ATTRIBUTES_TB	TIME_ATTRIBUTES_TB_PK	6	NORMAL	UNIQUE	ATTRIBUTE_ID	Columns
TIME_ATTRIBUTES_TB	NOHR.TIME_ATTRIBUTES_TB_N001		NORMAL	NONUNIQUE	ATTRIBUTE15	Columns
TIME_ATTRIBUTE_USAGES	TIME_ATTRIBUTE_USAGES_FK1		NORMAL	NONUNIQUE	ATTRIBUTE_ID	Columns
TIME_ATTRIBUTE_USAGES	TIME_ATTRIBUTE_USAGES_FK2	4	NORMAL	NONUNIQUE	BUILDING_BLOCK_ID BUILDING_BLOCK_OVN	Columns
TIME_ATTRIBUTE_USAGES	TIME_ATTRIBUTE_USAGES_N1		NORMAL	NONUNIQUE	DATA_SET_ID	Columns
TIME_ATTRIBUTE_USAGES	TIME_ATTRIBUTE_USAGES_PK		NORMAL	UNIQUE	TIME_ATTRIBUTE_USAGE_ID	Columns

Table Owner.Table Name	Index Owner.Index Name	Num^* Rows	Sample^* Size	Last^* Analyzed	Distinct^* Keys	Blevel^*	Leaf^* Blocks	Avg^* Leaf Blocks per Key	Avg^* Data Blocks per Key	Clustering^* Factor	Global^* Stats
TIME_ATTRIBUTES_TB	TIME_ATTRIBUTES_TB_F2	710325240	35516262	29-SEP-07 05:30:19	1	3	2091280	2091280	8361560	8361560	YES
TIME_ATTRIBUTES_TB	TIME_ATTRIBUTES_TB_FK1	706413800	35320690	29-SEP-07 05:26:44	6	3	3939860	656643	5187026	31122160	YES
TIME_ATTRIBUTES_TB	TIME_ATTRIBUTES_TB_FK2	710059900	35502995	29-SEP-07 05:29:15	11	3	5602980	509361	2996421	32960640	YES
TIME_ATTRIBUTES_TB	TIME_ATTRIBUTES_TB_FK3	558066820	27903341	29-SEP-07 05:22:23	18	3	3336500	185361	1574143	28334580	YES
TIME_ATTRIBUTES_TB	TIME_ATTRIBUTES_TB_FK4	429676140	21483807	29-SEP-07 05:24:48	228	3	2359420	10348	223462	50949420	YES
TIME_ATTRIBUTES_TB	TIME_ATTRIBUTES_TB_FK5	15990060	799503	29-SEP-07 05:24:52	366	2	108120	295	3090	1131280	YES
TIME_ATTRIBUTES_TB	TIME_ATTRIBUTES_TB_N1	0	0	29-SEP-07 05:20:50	0	0	0	0	0	0	YES
TIME_ATTRIBUTES_TB	TIME_ATTRIBUTES_TB_PK	708239480	35411974	29-SEP-07 05:23:24	708239480	3	2110620	1	1	78157640	YES
TIME_ATTRIBUTES_TB	NOHR.TIME_ATTRIBUTES_TB_N001	130819060	6540953	29-SEP-07 05:23:41	3707	3	835180	225	11315	41946720	YES
TIME_ATTRIBUTE_USAGES	TIME_ATTRIBUTE_USAGES_FK1	716067320	35803366	29-SEP-07 05:21:07	710681160	3	3347280	1	1	85764140	YES
TIME_ATTRIBUTE_USAGES	TIME_ATTRIBUTE_USAGES_FK2	715634900	35781745	29-SEP-07 05:18:40	60135854	3	3091760	1	15	265197800	YES
TIME_ATTRIBUTE_USAGES	TIME_ATTRIBUTE_USAGES_N1	0	0	29-SEP-07 05:16:34	0	0	0	0	0	0	YES
TIME_ATTRIBUTE_USAGES	TIME_ATTRIBUTE_USAGES_PK	717756460	35887823	29-SEP-07 05:22:13	717756460	3	2321920	1	1	75501020	YES

(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics

Table Owner.Table Name	Index Owner.Index Name	Temporary	Partitioned	Logging	Degree	Ini Trans	Max Trans	Freelist Groups	Freelists	Status	Funcidx Status
TIME_ATTRIBUTES_TB	TIME_ATTRIBUTES_TB_F2	N	NO	YES	1	11	255	4	4	VALID	ENABLED
TIME_ATTRIBUTES_TB	TIME_ATTRIBUTES_TB_FK1	N	NO	YES	1	11	250	4	4	VALID
TIME_ATTRIBUTES_TB	TIME_ATTRIBUTES_TB_FK2	N	NO	YES	1	11	250	4	4	VALID
TIME_ATTRIBUTES_TB	TIME_ATTRIBUTES_TB_FK3	N	NO	YES	1	11	250	4	4	VALID
TIME_ATTRIBUTES_TB	TIME_ATTRIBUTES_TB_FK4	N	NO	YES	1	11	250	4	4	VALID
TIME_ATTRIBUTES_TB	TIME_ATTRIBUTES_TB_FK5	N	NO	YES	1	11	250	4	4	VALID
TIME_ATTRIBUTES_TB	TIME_ATTRIBUTES_TB_N1	N	NO	YES	1	11	250	4	4	VALID
TIME_ATTRIBUTES_TB	TIME_ATTRIBUTES_TB_PK	N	NO	YES	1	11	250	4	4	VALID
TIME_ATTRIBUTES_TB	NOHR.TIME_ATTRIBUTES_TB_N001	N	NO	YES	1	30	255	4	15	VALID
TIME_ATTRIBUTE_USAGES	TIME_ATTRIBUTE_USAGES_FK1	N	NO	YES	1	11	250	4	4	VALID
TIME_ATTRIBUTE_USAGES	TIME_ATTRIBUTE_USAGES_FK2	N	NO	YES	1	11	250	4	4	VALID
TIME_ATTRIBUTE_USAGES	TIME_ATTRIBUTE_USAGES_N1	N	NO	YES	1	11	250	4	4	VALID
TIME_ATTRIBUTE_USAGES	TIME_ATTRIBUTE_USAGES_PK	N	NO	YES	1	11	250	4	4	VALID

Table: TIME_ATTRIBUTE_USAGES

hide

Column ID	Column Name	Data Type	Data Length	Data Precision	Data Scale	Nullable	Character Set Name	Char Length	Char Used
1	TIME_ATTRIBUTE_USAGE_ID	NUMBER	22	15	0	N		0
2	ATTRIBUTE_ID	NUMBER	22	15	0	Y
3	BUILDING_BLOCK_ID	NUMBER	22	15	0	Y
4	CREATED_BY	VARCHAR2	15			Y	CHAR_CS	15	B
5	CREATION_DATE	DATE	7			Y
6	LAST_UPDATED_BY	VARCHAR2	15			Y	CHAR_CS	15	B
7	LAST_UPDATE_DATE	DATE	7			Y
8	LAST_UPDATE_LOGIN	VARCHAR2	15			Y	CHAR_CS	15	B
9	OBJECT_VERSION_NUMBER	NUMBER	22			Y
10	BUILDING_BLOCK_OVN	NUMBER	22	9	0	Y
11	DATA_SET_ID	NUMBER	22	15	0	Y		0

hide

Column ID	Column Name	Num^* Rows	Sample^* Size	Last^* Analyzed	Global^* Stats	User^* Stats	Num^* Nulls	Num^* Distinct	Density^*	Avg^* Col Len
1	TIME_ATTRIBUTE_USAGE_ID	710932460	35546623	29-SEP-07 05:16:34	YES	NO	0	710932460	1.4066e-09	7
2	ATTRIBUTE_ID	710932460	35546623	29-SEP-07 05:16:34	YES	NO	0	710681160	1.4071e-09	7
3	BUILDING_BLOCK_ID	710932460	35546611	29-SEP-07 05:16:34	YES	NO	240	60135854	1.6629e-08	7
4	CREATED_BY	710932460		29-SEP-07 05:16:34	YES	NO	710932460	0	0.0000e+00	0
5	CREATION_DATE	710932460	35510680	29-SEP-07 05:16:34	YES	NO	718860	13624096	7.3399e-08	8
6	LAST_UPDATED_BY	710932460		29-SEP-07 05:16:34	YES	NO	710932460	0	0.0000e+00	0
7	LAST_UPDATE_DATE	710932460	35510680	29-SEP-07 05:16:34	YES	NO	718860	13624096	7.3399e-08	8
8	LAST_UPDATE_LOGIN	710932460		29-SEP-07 05:16:34	YES	NO	710932460	0	0.0000e+00	0
9	OBJECT_VERSION_NUMBER	710932460	35943	29-SEP-07 05:16:34	YES	NO	710213600	1	1.0000e+00	2
10	BUILDING_BLOCK_OVN	710932460	35545120	29-SEP-07 05:16:34	YES	NO	30060	56	1.7857e-02	3
11	DATA_SET_ID	710932460		29-SEP-07 05:16:34	YES	NO	710932460	0	0.0000e+00	0

(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics

hide

Column ID	Column Name	Low Value^*	High Value^*	Histogram^*	Num^* Buckets
1	TIME_ATTRIBUTE_USAGE_ID	5	714839025	NONE	1
2	ATTRIBUTE_ID	5	709912023	NONE	1
3	BUILDING_BLOCK_ID	0	306935448	NONE	1
4	CREATED_BY			NONE	0
5	CREATION_DATE	2004-05-23 00:41:41	2007-09-29 03:11:11	NONE	1
6	LAST_UPDATED_BY			NONE	0
7	LAST_UPDATE_DATE	2004-05-23 00:41:41	2007-09-29 03:11:11	NONE	1
8	LAST_UPDATE_LOGIN			NONE	0
9	OBJECT_VERSION_NUMBER	1	1	NONE	1
10	BUILDING_BLOCK_OVN	1	56	NONE	1
11	DATA_SET_ID			NONE	0

(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics

Table: TIME_ATTRIBUTES_TB

hide

Column ID	Column Name	Data Type	Data Length	Data Precision	Data Scale	Nullable	Character Set Name	Char Length	Char Used
1	ATTRIBUTE_ID	NUMBER	22	15	0	N		0
2	ATTRIBUTE_CATEGORY	VARCHAR2	30			Y	CHAR_CS	30	B
3	ATTRIBUTE1	VARCHAR2	150			Y	CHAR_CS	150	B
4	ATTRIBUTE2	VARCHAR2	150			Y	CHAR_CS	150	B
5	ATTRIBUTE3	VARCHAR2	150			Y	CHAR_CS	150	B
6	ATTRIBUTE4	VARCHAR2	150			Y	CHAR_CS	150	B
7	ATTRIBUTE5	VARCHAR2	150			Y	CHAR_CS	150	B
8	ATTRIBUTE6	VARCHAR2	150			Y	CHAR_CS	150	B
9	ATTRIBUTE7	VARCHAR2	150			Y	CHAR_CS	150	B
10	ATTRIBUTE8	VARCHAR2	150			Y	CHAR_CS	150	B
11	ATTRIBUTE9	VARCHAR2	150			Y	CHAR_CS	150	B
12	ATTRIBUTE10	VARCHAR2	150			Y	CHAR_CS	150	B
13	ATTRIBUTE11	VARCHAR2	150			Y	CHAR_CS	150	B
14	ATTRIBUTE12	VARCHAR2	150			Y	CHAR_CS	150	B
15	ATTRIBUTE13	VARCHAR2	150			Y	CHAR_CS	150	B
16	ATTRIBUTE14	VARCHAR2	150			Y	CHAR_CS	150	B
17	ATTRIBUTE15	VARCHAR2	150			Y	CHAR_CS	150	B
18	ATTRIBUTE16	VARCHAR2	150			Y	CHAR_CS	150	B
19	ATTRIBUTE17	VARCHAR2	150			Y	CHAR_CS	150	B
20	ATTRIBUTE18	VARCHAR2	150			Y	CHAR_CS	150	B
21	ATTRIBUTE19	VARCHAR2	150			Y	CHAR_CS	150	B
22	ATTRIBUTE20	VARCHAR2	150			Y	CHAR_CS	150	B
23	ATTRIBUTE21	VARCHAR2	150			Y	CHAR_CS	150	B
24	ATTRIBUTE22	VARCHAR2	150			Y	CHAR_CS	150	B
25	ATTRIBUTE23	VARCHAR2	150			Y	CHAR_CS	150	B
26	ATTRIBUTE24	VARCHAR2	150			Y	CHAR_CS	150	B
27	ATTRIBUTE25	VARCHAR2	150			Y	CHAR_CS	150	B
28	ATTRIBUTE26	VARCHAR2	150			Y	CHAR_CS	150	B
29	ATTRIBUTE27	VARCHAR2	150			Y	CHAR_CS	150	B
30	ATTRIBUTE28	VARCHAR2	150			Y	CHAR_CS	150	B
31	ATTRIBUTE29	VARCHAR2	150			Y	CHAR_CS	150	B
32	ATTRIBUTE30	VARCHAR2	150			Y	CHAR_CS	150	B
33	BLD_BLK_INFO_TYPE_ID	NUMBER	22	30	0	N
34	OBJECT_VERSION_NUMBER	NUMBER	22	9	0	Y
35	CONSOLIDATED_FLAG	VARCHAR2	1			Y	CHAR_CS	1	B
36	DATA_SET_ID	NUMBER	22	15	0	Y		0

hide

Column ID	Column Name	Num^* Rows	Sample^* Size	Last^* Analyzed	Global^* Stats	User^* Stats	Num^* Nulls	Num^* Distinct	Density^*	Avg^* Col Len
1	ATTRIBUTE_ID	706053520	35302676	29-SEP-07 05:20:49	YES	NO	0	706053520	1.4163e-09	7
2	ATTRIBUTE_CATEGORY	706053520	35302292	29-SEP-07 05:20:49	YES	NO	7680	11	9.0909e-02	14
3	ATTRIBUTE1	706053520	27953785	29-SEP-07 05:20:49	YES	NO	146977820	18	5.5556e-02	4
4	ATTRIBUTE2	706053520	21636189	29-SEP-07 05:20:49	YES	NO	273329740	228	4.3860e-03	3
5	ATTRIBUTE3	706053520	804186	29-SEP-07 05:20:49	YES	NO	689969800	366	2.7322e-03	2
6	ATTRIBUTE4	706053520	7229091	29-SEP-07 05:20:49	YES	NO	561471700	775	1.2903e-03	3
7	ATTRIBUTE5	706053520	787273	29-SEP-07 05:20:49	YES	NO	690308060	2	5.0000e-01	2
8	ATTRIBUTE6	706053520	1734476	29-SEP-07 05:20:49	YES	NO	671364000	218	4.5872e-03	2
9	ATTRIBUTE7	706053520	6688569	29-SEP-07 05:20:49	YES	NO	572282140	226	4.4248e-03	2
10	ATTRIBUTE8	706053520	7807885	29-SEP-07 05:20:49	YES	NO	549895820	1973	5.0684e-04	3
11	ATTRIBUTE9	706053520	6728828	29-SEP-07 05:20:49	YES	NO	571476960	948	1.0549e-03	2
12	ATTRIBUTE10	706053520	441298	29-SEP-07 05:20:49	YES	NO	697227560	1	1.0000e+00	2
13	ATTRIBUTE11	706053520	190252	29-SEP-07 05:20:49	YES	NO	702248480	2	5.0000e-01	2
14	ATTRIBUTE12	706053520	12670	29-SEP-07 05:20:49	YES	NO	705800120	1414	7.0721e-04	2
15	ATTRIBUTE13	706053520		29-SEP-07 05:20:49	YES	NO	706053520	0	0.0000e+00	0
16	ATTRIBUTE14	706053520	6560240	29-SEP-07 05:20:49	YES	NO	574848720	190	5.2632e-03	2
17	ATTRIBUTE15	706053520	6560253	29-SEP-07 05:20:49	YES	NO	574848460	3707	2.6976e-04	3
18	ATTRIBUTE16	706053520	2006166	29-SEP-07 05:20:49	YES	NO	665930200	4	2.5000e-01	2
19	ATTRIBUTE17	706053520	217131	29-SEP-07 05:20:49	YES	NO	701710900	4	2.5000e-01	2
20	ATTRIBUTE18	706053520	331246	29-SEP-07 05:20:49	YES	NO	699428600	3	3.3333e-01	2
21	ATTRIBUTE19	706053520	1699362	29-SEP-07 05:20:49	YES	NO	672066280	195640	5.1114e-06	2
22	ATTRIBUTE20	706053520		29-SEP-07 05:20:49	YES	NO	706053520	0	0.0000e+00	0
23	ATTRIBUTE21	706053520		29-SEP-07 05:20:49	YES	NO	706053520	0	0.0000e+00	0
24	ATTRIBUTE22	706053520		29-SEP-07 05:20:49	YES	NO	706053520	0	0.0000e+00	0
25	ATTRIBUTE23	706053520		29-SEP-07 05:20:49	YES	NO	706053520	0	0.0000e+00	0
26	ATTRIBUTE24	706053520		29-SEP-07 05:20:49	YES	NO	706053520	0	0.0000e+00	0
27	ATTRIBUTE25	706053520		29-SEP-07 05:20:49	YES	NO	706053520	0	0.0000e+00	0
28	ATTRIBUTE26	706053520		29-SEP-07 05:20:49	YES	NO	706053520	0	0.0000e+00	0
29	ATTRIBUTE27	706053520		29-SEP-07 05:20:49	YES	NO	706053520	0	0.0000e+00	0
30	ATTRIBUTE28	706053520		29-SEP-07 05:20:49	YES	NO	706053520	0	0.0000e+00	0
31	ATTRIBUTE29	706053520		29-SEP-07 05:20:49	YES	NO	706053520	0	0.0000e+00	0
32	ATTRIBUTE30	706053520		29-SEP-07 05:20:49	YES	NO	706053520	0	0.0000e+00	0
33	BLD_BLK_INFO_TYPE_ID	706053520	35302676	29-SEP-07 05:20:49	YES	NO	0	6	1.6667e-01	3
34	OBJECT_VERSION_NUMBER	706053520	35302676	29-SEP-07 05:20:49	YES	NO	0	1	1.0000e+00	3
35	CONSOLIDATED_FLAG	706053520		29-SEP-07 05:20:49	YES	NO	706053520	0	0.0000e+00	0
36	DATA_SET_ID	706053520		29-SEP-07 05:20:49	YES	NO	706053520	0	0.0000e+00	0

(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics

hide

Column ID	Column Name	Low Value^*	High Value^*	Histogram^*	Num^* Buckets
1	ATTRIBUTE_ID	62	709911995	NONE	1
2	ATTRIBUTE_CATEGORY	Cost Approval	SECURITY	NONE	1
3	ATTRIBUTE1	1	55812	NONE	1
4	ATTRIBUTE2	0	5410	NONE	1
5	ATTRIBUTE3	10352	9839	NONE	1
6	ATTRIBUTE4	33987	75605	NONE	1
7	ATTRIBUTE5	20003	800	NONE	1
8	ATTRIBUTE6	0	Woodfield Rack	NONE	1
9	ATTRIBUTE7	0000	N	NONE	1
10	ATTRIBUTE8	0000	via C	NONE	1
11	ATTRIBUTE9	1000	9732	NONE	1
12	ATTRIBUTE10	Y	Y	NONE	1
13	ATTRIBUTE11	55813	55814	NONE	1
14	ATTRIBUTE12	11.50	97.81	NONE	1
15	ATTRIBUTE13			NONE	0
16	ATTRIBUTE14	0000	0950	NONE	1
17	ATTRIBUTE15	01-APR-2007	9TZR	NONE	1
18	ATTRIBUTE16	TZA	TZD	NONE	1
19	ATTRIBUTE17	IM	OM	NONE	1
20	ATTRIBUTE18	E	Y	NONE	1
21	ATTRIBUTE19	.	zvp4_ras.zvp4_ras	NONE	1
22	ATTRIBUTE20			NONE	0
23	ATTRIBUTE21			NONE	0
24	ATTRIBUTE22			NONE	0
25	ATTRIBUTE23			NONE	0
26	ATTRIBUTE24			NONE	0
27	ATTRIBUTE25			NONE	0
28	ATTRIBUTE26			NONE	0
29	ATTRIBUTE27			NONE	0
30	ATTRIBUTE28			NONE	0
31	ATTRIBUTE29			NONE	0
32	ATTRIBUTE30			NONE	0
33	BLD_BLK_INFO_TYPE_ID	1	61	NONE	1
34	OBJECT_VERSION_NUMBER	1	1	NONE	1
35	CONSOLIDATED_FLAG			NONE	0
36	DATA_SET_ID			NONE	0

(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics

Index Columns

Index: TIME_ATTRIBUTES_TB_FK3
Index: TIME_ATTRIBUTE_USAGES_PK
Index: TIME_ATTRIBUTES_TB_FK2
Index: TIME_ATTRIBUTE_USAGES_N1
Index: TIME_ATTRIBUTE_USAGES_FK2
Index: NOHR.TIME_ATTRIBUTES_TB_N001
Index: TIME_ATTRIBUTES_TB_FK1
Index: TIME_ATTRIBUTES_TB_F2
Index: TIME_ATTRIBUTES_TB_N1
Index: TIME_ATTRIBUTES_TB_FK4
Index: TIME_ATTRIBUTES_TB_FK5
Index: TIME_ATTRIBUTE_USAGES_FK1
Index: TIME_ATTRIBUTES_TB_PK

Index: TIME_ATTRIBUTES_TB_FK4 on TIME_ATTRIBUTES_TB

hide

Column Pos	Column Name	Descend	Data Type	Data Type Mod	Data Type Owner	Data Length	Data Precision	Data Scale	Nullable	Character Set Name	Char Length	Char Used
1	ATTRIBUTE2	ASC	VARCHAR2			150			Y	CHAR_CS	150	B

hide

Column Pos	Column Name	Num^* Rows	Sample^* Size	Last^* Analyzed	Global^* Stats	User^* Stats	Num^* Nulls	Num^* Distinct	Density^*	Avg^* Col Len
1	ATTRIBUTE2	706053520	21636189	29-SEP-07 05:20:49	YES	NO	273329740	228	4.3860e-03	3

(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics

hide

Column Pos	Column Name	Low Value^*	High Value^*	Histogram^*	Num^* Buckets	Buckets
1	ATTRIBUTE2	0	5410	NONE	1

(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics

Index: TIME_ATTRIBUTES_TB_FK5 on TIME_ATTRIBUTES_TB

hide

Column Pos	Column Name	Descend	Data Type	Data Type Mod	Data Type Owner	Data Length	Data Precision	Data Scale	Nullable	Character Set Name	Char Length	Char Used
1	ATTRIBUTE3	ASC	VARCHAR2			150			Y	CHAR_CS	150	B

hide

Column Pos	Column Name	Num^* Rows	Sample^* Size	Last^* Analyzed	Global^* Stats	User^* Stats	Num^* Nulls	Num^* Distinct	Density^*	Avg^* Col Len
1	ATTRIBUTE3	706053520	804186	29-SEP-07 05:20:49	YES	NO	689969800	366	2.7322e-03	2

(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics

hide

Column Pos	Column Name	Low Value^*	High Value^*	Histogram^*	Num^* Buckets	Buckets
1	ATTRIBUTE3	10352	9839	NONE	1

(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics

Index: TIME_ATTRIBUTE_USAGES_FK2 on TIME_ATTRIBUTE_USAGES

hide

Column Pos	Column Name	Descend	Data Type	Data Type Mod	Data Type Owner	Data Length	Data Precision	Data Scale	Nullable	Character Set Name	Char Length	Char Used
1	BUILDING_BLOCK_ID	ASC	NUMBER			22	15	0	Y
2	BUILDING_BLOCK_OVN	ASC	NUMBER			22	9	0	Y

hide

Column Pos	Column Name	Num^* Rows	Sample^* Size	Last^* Analyzed	Global^* Stats	User^* Stats	Num^* Nulls	Num^* Distinct	Density^*	Avg^* Col Len
1	BUILDING_BLOCK_ID	710932460	35546611	29-SEP-07 05:16:34	YES	NO	240	60135854	1.6629e-08	7
2	BUILDING_BLOCK_OVN	710932460	35545120	29-SEP-07 05:16:34	YES	NO	30060	56	1.7857e-02	3

(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics

hide

Column Pos	Column Name	Low Value^*	High Value^*	Histogram^*	Num^* Buckets	Buckets
1	BUILDING_BLOCK_ID	0	306935448	NONE	1
2	BUILDING_BLOCK_OVN	1	56	NONE	1

(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics

Index: TIME_ATTRIBUTES_TB_FK1 on TIME_ATTRIBUTES_TB

hide

Column Pos	Column Name	Descend	Data Type	Data Type Mod	Data Type Owner	Data Length	Data Precision	Data Scale	Nullable	Character Set Name	Char Length	Char Used
1	BLD_BLK_INFO_TYPE_ID	ASC	NUMBER			22	30	0	N

hide

Column Pos	Column Name	Num^* Rows	Sample^* Size	Last^* Analyzed	Global^* Stats	User^* Stats	Num^* Nulls	Num^* Distinct	Density^*	Avg^* Col Len
1	BLD_BLK_INFO_TYPE_ID	706053520	35302676	29-SEP-07 05:20:49	YES	NO	0	6	1.6667e-01	3

(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics

hide

Column Pos	Column Name	Low Value^*	High Value^*	Histogram^*	Num^* Buckets	Buckets
1	BLD_BLK_INFO_TYPE_ID	1	61	NONE	1

(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics

Index: TIME_ATTRIBUTES_TB_FK2 on TIME_ATTRIBUTES_TB

hide

Column Pos	Column Name	Descend	Data Type	Data Type Mod	Data Type Owner	Data Length	Data Precision	Data Scale	Nullable	Character Set Name	Char Length	Char Used
1	ATTRIBUTE_CATEGORY	ASC	VARCHAR2			30			Y	CHAR_CS	30	B

hide

Column Pos	Column Name	Num^* Rows	Sample^* Size	Last^* Analyzed	Global^* Stats	User^* Stats	Num^* Nulls	Num^* Distinct	Density^*	Avg^* Col Len
1	ATTRIBUTE_CATEGORY	706053520	35302292	29-SEP-07 05:20:49	YES	NO	7680	11	9.0909e-02	14

(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics

hide

Column Pos	Column Name	Low Value^*	High Value^*	Histogram^*	Num^* Buckets	Buckets
1	ATTRIBUTE_CATEGORY	Cost Approval	SECURITY	NONE	1

(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics

Index: TIME_ATTRIBUTES_TB_FK3 on TIME_ATTRIBUTES_TB

hide

Column Pos	Column Name	Descend	Data Type	Data Type Mod	Data Type Owner	Data Length	Data Precision	Data Scale	Nullable	Character Set Name	Char Length	Char Used
1	ATTRIBUTE1	ASC	VARCHAR2			150			Y	CHAR_CS	150	B

hide

Column Pos	Column Name	Num^* Rows	Sample^* Size	Last^* Analyzed	Global^* Stats	User^* Stats	Num^* Nulls	Num^* Distinct	Density^*	Avg^* Col Len
1	ATTRIBUTE1	706053520	27953785	29-SEP-07 05:20:49	YES	NO	146977820	18	5.5556e-02	4

(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics

hide

Column Pos	Column Name	Low Value^*	High Value^*	Histogram^*	Num^* Buckets	Buckets
1	ATTRIBUTE1	1	55812	NONE	1

(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics

Index: NOHR.TIME_ATTRIBUTES_TB_N001 on TIME_ATTRIBUTES_TB

hide

Column Pos	Column Name	Descend	Data Type	Data Type Mod	Data Type Owner	Data Length	Data Precision	Data Scale	Nullable	Character Set Name	Char Length	Char Used
1	ATTRIBUTE15	ASC	VARCHAR2			150			Y	CHAR_CS	150	B

hide

Column Pos	Column Name	Num^* Rows	Sample^* Size	Last^* Analyzed	Global^* Stats	User^* Stats	Num^* Nulls	Num^* Distinct	Density^*	Avg^* Col Len
1	ATTRIBUTE15	706053520	6560253	29-SEP-07 05:20:49	YES	NO	574848460	3707	2.6976e-04	3

(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics

hide

Column Pos	Column Name	Low Value^*	High Value^*	Histogram^*	Num^* Buckets	Buckets
1	ATTRIBUTE15	01-APR-2007	9TZR	NONE	1

(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics

Index: TIME_ATTRIBUTE_USAGES_PK on TIME_ATTRIBUTE_USAGES

hide

Column Pos	Column Name	Descend	Data Type	Data Type Mod	Data Type Owner	Data Length	Data Precision	Data Scale	Nullable	Character Set Name	Char Length	Char Used
1	TIME_ATTRIBUTE_USAGE_ID	ASC	NUMBER			22	15	0	N		0

hide

Column Pos	Column Name	Num^* Rows	Sample^* Size	Last^* Analyzed	Global^* Stats	User^* Stats	Num^* Nulls	Num^* Distinct	Density^*	Avg^* Col Len
1	TIME_ATTRIBUTE_USAGE_ID	710932460	35546623	29-SEP-07 05:16:34	YES	NO	0	710932460	1.4066e-09	7

(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics

hide

Column Pos	Column Name	Low Value^*	High Value^*	Histogram^*	Num^* Buckets	Buckets
1	TIME_ATTRIBUTE_USAGE_ID	5	714839025	NONE	1

(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics

Index: TIME_ATTRIBUTES_TB_N1 on TIME_ATTRIBUTES_TB

hide

Column Pos	Column Name	Descend	Data Type	Data Type Mod	Data Type Owner	Data Length	Data Precision	Data Scale	Nullable	Character Set Name	Char Length	Char Used
1	DATA_SET_ID	ASC	NUMBER			22	15	0	Y		0

hide

Column Pos	Column Name	Num^* Rows	Sample^* Size	Last^* Analyzed	Global^* Stats	User^* Stats	Num^* Nulls	Num^* Distinct	Density^*	Avg^* Col Len
1	DATA_SET_ID	706053520		29-SEP-07 05:20:49	YES	NO	706053520	0	0.0000e+00	0

(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics

hide

Column Pos	Column Name	Low Value^*	High Value^*	Histogram^*	Num^* Buckets	Buckets
1	DATA_SET_ID			NONE	0

(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics

Index: TIME_ATTRIBUTES_TB_PK on TIME_ATTRIBUTES_TB

hide

Column Pos	Column Name	Descend	Data Type	Data Type Mod	Data Type Owner	Data Length	Data Precision	Data Scale	Nullable	Character Set Name	Char Length	Char Used
1	ATTRIBUTE_ID	ASC	NUMBER			22	15	0	N		0

hide

Column Pos	Column Name	Num^* Rows	Sample^* Size	Last^* Analyzed	Global^* Stats	User^* Stats	Num^* Nulls	Num^* Distinct	Density^*	Avg^* Col Len
1	ATTRIBUTE_ID	706053520	35302676	29-SEP-07 05:20:49	YES	NO	0	706053520	1.4163e-09	7

(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics

hide

Column Pos	Column Name	Low Value^*	High Value^*	Histogram^*	Num^* Buckets	Buckets
1	ATTRIBUTE_ID	62	709911995	NONE	1

(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics

Index: TIME_ATTRIBUTES_TB_F2 on TIME_ATTRIBUTES_TB

hide

Column Pos	Column Name	Descend	Data Type	Data Type Mod	Data Type Owner	Data Length	Data Precision	Data Scale	Nullable	Character Set Name	Char Length	Char Used

hide

Column Pos	Column Name	Num^* Rows	Sample^* Size	Last^* Analyzed	Global^* Stats	User^* Stats	Num^* Nulls	Num^* Distinct	Density^*	Avg^* Col Len

(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics

hide

Column Pos	Column Name	Low Value^*	High Value^*	Histogram^*	Num^* Buckets	Buckets

(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics

Index: TIME_ATTRIBUTE_USAGES_FK1 on TIME_ATTRIBUTE_USAGES

hide

Column Pos	Column Name	Descend	Data Type	Data Type Mod	Data Type Owner	Data Length	Data Precision	Data Scale	Nullable	Character Set Name	Char Length	Char Used
1	ATTRIBUTE_ID	ASC	NUMBER			22	15	0	Y

hide

Column Pos	Column Name	Num^* Rows	Sample^* Size	Last^* Analyzed	Global^* Stats	User^* Stats	Num^* Nulls	Num^* Distinct	Density^*	Avg^* Col Len
1	ATTRIBUTE_ID	710932460	35546623	29-SEP-07 05:16:34	YES	NO	0	710681160	1.4071e-09	7

(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics

hide

Column Pos	Column Name	Low Value^*	High Value^*	Histogram^*	Num^* Buckets	Buckets
1	ATTRIBUTE_ID	5	709912023	NONE	1

(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics

Index: TIME_ATTRIBUTE_USAGES_N1 on TIME_ATTRIBUTE_USAGES

hide

Column Pos	Column Name	Descend	Data Type	Data Type Mod	Data Type Owner	Data Length	Data Precision	Data Scale	Nullable	Character Set Name	Char Length	Char Used
1	DATA_SET_ID	ASC	NUMBER			22	15	0	Y		0

hide

Column Pos	Column Name	Num^* Rows	Sample^* Size	Last^* Analyzed	Global^* Stats	User^* Stats	Num^* Nulls	Num^* Distinct	Density^*	Avg^* Col Len
1	DATA_SET_ID	710932460		29-SEP-07 05:16:34	YES	NO	710932460	0	0.0000e+00	0

(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics

hide

Column Pos	Column Name	Low Value^*	High Value^*	Histogram^*	Num^* Buckets	Buckets
1	DATA_SET_ID			NONE	0

(*) Columns marked with an asterisk are refreshed only when collecting CBO statistics

Metadata

Synonym: TIME_ATTRIBUTES_TB
Synonym: TIME_ATTRIBUTE_USAGES

Synonym: TIME_ATTRIBUTES_TB

hide


     1
     2    CREATE OR REPLACE SYNONYM "TIME_ATTRIBUTES_TB" FOR ."TIME_ATTRIBUTES_TB"
     3

Synonym: TIME_ATTRIBUTE_USAGES

hide

     1
     2    CREATE OR REPLACE SYNONYM "TIME_ATTRIBUTE_USAGES" FOR ."TIME_ATTRIBUTE_USAGES"
     3

Optimizer Trace (EVENT 10053)

hide

/u01/app/oracle/admin/OAPRD1/udump/oaprd1_ora_22674.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/product/10.2.0
System name:	SunOS
Node name:	x0319p14
Release:	5.9
Version:	Generic_118558-19
Machine:	sun4u
Instance name: OAPRD1
Redo thread mounted by this instance: 1
Oracle process number: 1760
Unix process pid: 22674, image: oracle@x0319p14 (TNS V1-V3)
*** ACTION NAME:() 2007-12-17 09:51:56.304
*** MODULE NAME:(SQL*Plus) 2007-12-17 09:51:56.304
*** SERVICE NAME:(SYS$USERS) 2007-12-17 09:51:56.304
*** SESSION ID:(295.30626) 2007-12-17 09:51:56.304
Registered qb: SEL$1 0x125c7d38 (PARSER)
  signature (): qb_name=SEL$1 nbfros=1 flg=0
    fro(0): flg=4 objn=216996 hint_alias="TIME_ATTRIBUTES_TB"@"SEL$1"
Registered qb: SEL$2 0x357ad9c0 (PARSER)
  signature (): qb_name=SEL$2 nbfros=1 flg=0
    fro(0): flg=4 objn=217367 hint_alias="TIME_ATTRIBUTE_USAGES"@"SEL$2"
**************************
Predicate Move-Around (PM)
**************************
PM: Considering predicate move-around in SEL$1 (#0).
PM:   Checking validity of predicate move-around in SEL$1 (#0).
CBQT: Validity checks passed for 9q70ydnbyryjn.
apadrv-start: call(in-use=888, alloc=16344), compile(in-use=39408, alloc=40552)
******************************************
Current SQL statement for this session:
EXPLAIN PLAN SET statement_id = '1' INTO sqlt$_plan_table FOR
SELECT 'Y'
FROM
TIME_ATTRIBUTES_TB WHERE ATTRIBUTE_ID IN (SELECT ATTRIBUTE_ID
FROM TIME_ATTRIBUTE_USAGES WHERE BUILDING_BLOCK_ID = :B3 AND
BUILDING_BLOCK_OVN = :B2 ) AND ATTRIBUTE_CATEGORY = 'REASON' AND
ATTRIBUTE7 = DECODE (:B1 , NULL, ATTRIBUTE7, :B1 )
*******************************************
Legend
The following abbreviations are used by optimizer trace.
CBQT - cost-based query transformation
JPPD - join predicate push-down
FPD - filter push-down
PM - predicate move-around
CVM - complex view merging
SPJ - select-project-join
SJC - set join conversion
SU - subquery unnesting
OBYE - order by elimination
ST - star transformation
qb - query block
LB - leaf blocks
DK - distinct keys
LB/K - average number of leaf blocks per key
DB/K - average number of data blocks per key
CLUF - clustering factor
NDV - number of distinct values
Resp - response cost
Card - cardinality
Resc - resource cost
NL - nested loops (join)
SM - sort merge (join)
HA - hash (join)
CPUCSPEED - CPU Speed
IOTFRSPEED - I/O transfer speed
IOSEEKTIM - I/O seek time
SREADTIM - average single block read time
MREADTIM - average multiblock read time
MBRC - average multiblock read count
MAXTHR - maximum I/O system throughput
SLAVETHR - average slave I/O throughput
dmeth - distribution method
  1: no partitioning required
  2: value partitioned
  4: right is random (round-robin)
  512: left is random (round-robin)
  8: broadcast right and partition left
  16: broadcast left and partition right
  32: partition left using partitioning of right
  64: partition right using partitioning of left
  128: use hash partitioning dimension
  256: use range partitioning dimension
  2048: use list partitioning dimension
  1024: run the join in serial
  0: invalid distribution method
sel - selectivity
ptn - partition
*******************************************
Peeked values of the binds in SQL statement
*******************************************
kkscoacd
 Bind#0
  oacdty=01 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1010000 frm=01 csi=01 siz=128 off=0
  kxsbbbfp=ffffffff7a7fee20  bln=32  avl=00  flg=05
 Bind#1
  oacdty=01 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1010000 frm=01 csi=01 siz=0 off=32
  kxsbbbfp=ffffffff7a7fee40  bln=32  avl=00  flg=01
 Bind#2
  oacdty=01 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1010000 frm=01 csi=01 siz=0 off=64
  kxsbbbfp=ffffffff7a7fee60  bln=32  avl=00  flg=01
 Bind#3
  oacdty=01 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=1010000 frm=01 csi=01 siz=0 off=96
  kxsbbbfp=ffffffff7a7fee80  bln=32  avl=00  flg=01
***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
  *************************************
  PARAMETERS WITH ALTERED VALUES
  ******************************
  _sort_elimination_cost_ratio        = 5
  _pga_max_size                       = 2097152 KB
  _b_tree_bitmap_plans                = false
  _fast_full_scan_enabled             = false
  _like_with_bind_as_equality         = true
  optimizer_secure_view_merging       = false
  *********************************
  Bug Fix Control Environment
  ***************************
  fix  4611850 = enabled
  fix  4663804 = enabled
  fix  4663698 = enabled
  fix  4545833 = enabled
  fix  3499674 = disabled
  fix  4584065 = enabled
  fix  4602374 = enabled
  fix  4569940 = enabled
  fix  4631959 = enabled
  fix  4519340 = enabled
  fix  4550003 = enabled
  fix  4488689 = enabled
  fix  3118776 = enabled
  fix  4519016 = enabled
  fix  4487253 = enabled
  fix  4556762 = 15
  fix  4728348 = enabled
  fix  4723244 = enabled
  fix  4554846 = enabled
  fix  2492766 = enabled
  fix  4175830 = enabled
  fix  4722900 = enabled
  fix  4711525 = enabled
  fix  4273361 = enabled
  fix  4483240 = enabled
  fix  4904838 = enabled
  fix  5084239 = enabled
  fix  5838613 = enabled
  fix  5385629 = enabled
  fix  5449488 = enabled
  fix  5483301 = enabled
  fix  5680702 = enabled
  fix  5694984 = enabled
  fix  5705257 = enabled
  fix  5741121 = enabled
  fix  5762598 = enabled
  fix  5884780 = enabled
  fix  5949981 = enabled
  fix  5976822 = enabled
  fix  6122894 = enabled
  *************************************
  PARAMETERS WITH DEFAULT VALUES
  ******************************
  optimizer_mode_hinted               = false
  optimizer_features_hinted           = 0.0.0
  parallel_execution_enabled          = true
  parallel_query_forced_dop           = 0
  parallel_dml_forced_dop             = 0
  parallel_ddl_forced_degree          = 0
  parallel_ddl_forced_instances       = 0
  _query_rewrite_fudge                = 90
  optimizer_features_enable           = 10.2.0.2
  _optimizer_search_limit             = 5
  cpu_count                           = 28
  active_instance_count               = 1
  parallel_threads_per_cpu            = 2
  hash_area_size                      = 131072
  bitmap_merge_area_size              = 1048576
  sort_area_size                      = 65536
  sort_area_retained_size             = 0
  _optimizer_block_size               = 8192
  _sort_multiblock_read_count         = 2
  _hash_multiblock_io_count           = 0
  _db_file_optimizer_read_count       = 8
  _optimizer_max_permutations         = 2000
  pga_aggregate_target                = 20971520 KB
  _query_rewrite_maxdisjunct          = 257
  _smm_auto_min_io_size               = 56 KB
  _smm_auto_max_io_size               = 248 KB
  _smm_min_size                       = 1024 KB
  _smm_max_size                       = 1048576 KB
  _smm_px_max_size                    = 10485760 KB
  _cpu_to_io                          = 0
  _optimizer_undo_cost_change         = 10.2.0.2
  parallel_query_mode                 = enabled
  parallel_dml_mode                   = disabled
  parallel_ddl_mode                   = enabled
  optimizer_mode                      = all_rows
  sqlstat_enabled                     = false
  _optimizer_percent_parallel         = 101
  _always_anti_join                   = choose
  _always_semi_join                   = choose
  _optimizer_mode_force               = true
  _partition_view_enabled             = true
  _always_star_transformation         = false
  _query_rewrite_or_error             = false
  _hash_join_enabled                  = true
  cursor_sharing                      = exact
  star_transformation_enabled         = false
  _optimizer_cost_model               = choose
  _new_sort_cost_estimate             = true
  _complex_view_merging               = true
  _unnest_subquery                    = true
  _eliminate_common_subexpr           = true
  _pred_move_around                   = true
  _convert_set_to_join                = false
  _push_join_predicate                = true
  _push_join_union_view               = true
  _optim_enhance_nnull_detection      = true
  _parallel_broadcast_enabled         = true
  _px_broadcast_fudge_factor          = 100
  _ordered_nested_loop                = true
  _no_or_expansion                    = false
  optimizer_index_cost_adj            = 100
  optimizer_index_caching             = 0
  _system_index_caching               = 0
  _disable_datalayer_sampling         = false
  query_rewrite_enabled               = true
  query_rewrite_integrity             = enforced
  _query_cost_rewrite                 = true
  _query_rewrite_2                    = true
  _query_rewrite_1                    = true
  _query_rewrite_expression           = true
  _query_rewrite_jgmigrate            = true
  _query_rewrite_fpc                  = true
  _query_rewrite_drj                  = true
  _full_pwise_join_enabled            = true
  _partial_pwise_join_enabled         = true
  _left_nested_loops_random           = true
  _improved_row_length_enabled        = true
  _index_join_enabled                 = true
  _enable_type_dep_selectivity        = true
  _improved_outerjoin_card            = true
  _optimizer_adjust_for_nulls         = true
  _optimizer_degree                   = 0
  _use_column_stats_for_function      = true
  _subquery_pruning_enabled           = true
  _subquery_pruning_mv_enabled        = false
  _or_expand_nvl_predicate            = true
  _table_scan_cost_plus_one           = true
  _cost_equality_semi_join            = true
  _default_non_equality_sel_check     = true
  _new_initial_join_orders            = true
  _oneside_colstat_for_equijoins      = true
  _optim_peek_user_binds              = true
  _minimal_stats_aggregation          = true
  _force_temptables_for_gsets         = false
  workarea_size_policy                = auto
  _smm_auto_cost_enabled              = true
  _gs_anti_semi_join_allowed          = true
  _optim_new_default_join_sel         = true
  optimizer_dynamic_sampling          = 2
  _pre_rewrite_push_pred              = true
  _optimizer_new_join_card_computation = true
  _union_rewrite_for_gs               = yes_gset_mvs
  _generalized_pruning_enabled        = true
  _optim_adjust_for_part_skews        = true
  _force_datefold_trunc               = false
  statistics_level                    = typical
  _optimizer_system_stats_usage       = true
  skip_unusable_indexes               = true
  _remove_aggr_subquery               = true
  _optimizer_push_down_distinct       = 0
  _dml_monitoring_enabled             = true
  _optimizer_undo_changes             = false
  _predicate_elimination_enabled      = true
  _nested_loop_fudge                  = 100
  _project_view_columns               = true
  _local_communication_costing_enabled = true
  _local_communication_ratio          = 50
  _query_rewrite_vop_cleanup          = true
  _slave_mapping_enabled              = true
  _optimizer_cost_based_transformation = linear
  _optimizer_mjc_enabled              = true
  _right_outer_hash_enable            = true
  _spr_push_pred_refspr               = true
  _optimizer_cache_stats              = false
  _optimizer_cbqt_factor              = 50
  _optimizer_squ_bottomup             = true
  _fic_area_size                      = 131072
  _optimizer_skip_scan_enabled        = true
  _optimizer_cost_filter_pred         = false
  _optimizer_sortmerge_join_enabled   = true
  _optimizer_join_sel_sanity_check    = true
  _mmv_query_rewrite_enabled          = true
  _bt_mmv_query_rewrite_enabled       = true
  _add_stale_mv_to_dependency_list    = true
  _distinct_view_unnesting            = false
  _optimizer_dim_subq_join_sel        = true
  _optimizer_disable_strans_sanity_checks = 0
  _optimizer_compute_index_stats      = true
  _push_join_union_view2              = true
  _optimizer_ignore_hints             = false
  _optimizer_random_plan              = 0
  _query_rewrite_setopgrw_enable      = true
  _optimizer_correct_sq_selectivity   = true
  _disable_function_based_index       = false
  _optimizer_join_order_control       = 3
  _optimizer_cartesian_enabled        = true
  _optimizer_starplan_enabled         = true
  _extended_pruning_enabled           = true
  _optimizer_push_pred_cost_based     = true
  _sql_model_unfold_forloops          = run_time
  _enable_dml_lock_escalation         = false
  _bloom_filter_enabled               = true
  _update_bji_ipdml_enabled           = 0
  _optimizer_extended_cursor_sharing  = udo
  _dm_max_shared_pool_pct             = 1
  _optimizer_cost_hjsmj_multimatch    = true
  _optimizer_transitivity_retain      = true
  _px_pwg_enabled                     = true
  _optimizer_join_elimination_enabled = true
  flashback_table_rpi                 = non_fbt
  _optimizer_cbqt_no_size_restriction = true
  _optimizer_enhanced_filter_push     = true
  _optimizer_filter_pred_pullup       = true
  _rowsrc_trace_level                 = 0
  _simple_view_merging                = true
  _optimizer_rownum_pred_based_fkr    = true
  _optimizer_better_inlist_costing    = all
  _optimizer_self_induced_cache_cost  = false
  _optimizer_min_cache_blocks         = 10
  _optimizer_or_expansion             = depth
  _optimizer_order_by_elimination_enabled = true
  _optimizer_outer_to_anti_enabled    = true
  _selfjoin_mv_duplicates             = true
  _dimension_skip_null                = true
  _force_rewrite_enable               = false
  _optimizer_star_tran_in_with_clause = true
  _optimizer_complex_pred_selectivity = true
  _optimizer_connect_by_cost_based    = true
  _gby_hash_aggregation_enabled       = true
  _globalindex_pnum_filter_enabled    = true
  _fix_control_key                    = 0
  _optimizer_skip_scan_guess          = false
  _enable_row_shipping                = false
  *********************************
  Bug Fix Control Environment
  ***************************
  fix  4611850 = enabled
  fix  4663804 = enabled
  fix  4663698 = enabled
  fix  4545833 = enabled
  fix  3499674 = disabled
  fix  4584065 = enabled
  fix  4602374 = enabled
  fix  4569940 = enabled
  fix  4631959 = enabled
  fix  4519340 = enabled
  fix  4550003 = enabled
  fix  4488689 = enabled
  fix  3118776 = enabled
  fix  4519016 = enabled
  fix  4487253 = enabled
  fix  4556762 = 15
  fix  4728348 = enabled
  fix  4723244 = enabled
  fix  4554846 = enabled
  fix  2492766 = enabled
  fix  4175830 = enabled
  fix  4722900 = enabled
  fix  4711525 = enabled
  fix  4273361 = enabled
  fix  4483240 = enabled
  fix  4904838 = enabled
  fix  5084239 = enabled
  fix  5838613 = enabled
  fix  5385629 = enabled
  fix  5449488 = enabled
  fix  5483301 = enabled
  fix  5680702 = enabled
  fix  5694984 = enabled
  fix  5705257 = enabled
  fix  5741121 = enabled
  fix  5762598 = enabled
  fix  5884780 = enabled
  fix  5949981 = enabled
  fix  5976822 = enabled
  fix  6122894 = enabled
  ***************************************
  PARAMETERS IN OPT_PARAM HINT
  ****************************
***************************************
Column Usage Monitoring is ON: tracking level = 1
***************************************
********************************
COST-BASED QUERY TRANSFORMATIONS
********************************
FPD: Considering simple filter push (pre rewrite) in SEL$1 (#0)
FPD:   Current where clause predicates in SEL$1 (#0) :

"TIME_ATTRIBUTES_TB"."ATTRIBUTE_ID"=ANY (SELECT "TIME_ATTRIBUTE_USAGES"."ATTRIBUTE_ID" FROM ."TIME_ATTRIBUTE_USAGES" "TIME_ATTRIBUTE_USAGES")
 AND "TIME_ATTRIBUTES_TB"."ATTRIBUTE_CATEGORY"='REASON' AND "TIME_ATTRIBUTES_TB"."ATTRIBUTE7"=DECODE(:B1,NULL,"TIME_ATTRIBUTES_TB"."ATTRIBUTE7",:B2)
Registered qb: SEL$1 0x2167a878 (COPY SEL$1)
  signature(): NULL
Registered qb: SEL$2 0xdcd96658 (COPY SEL$2)
  signature(): NULL
*****************************
Cost-Based Subquery Unnesting
*****************************
SU: No subqueries to consider in query block SEL$2 (#2).
SU: Considering subquery unnesting in query block SEL$1 (#1)
SU: Performing unnesting that does not require costing.
SU: Considering subquery unnest on SEL$1 (#1).
SU:   Checking validity of unnesting subquery SEL$2 (#2)
SU:   Passed validity checks.
SU:   Transforming ANY subquery to a join.
Registered qb: SEL$5DA710D3 0x2167a878 (SUBQUERY UNNEST SEL$1; SEL$2)
  signature (): qb_name=SEL$5DA710D3 nbfros=2 flg=0
    fro(0): flg=0 objn=216996 hint_alias="TIME_ATTRIBUTES_TB"@"SEL$1"
    fro(1): flg=0 objn=217367 hint_alias="TIME_ATTRIBUTE_USAGES"@"SEL$2"
*******************************
Cost-Based Complex View Merging
*******************************
CVM: Finding query blocks in SEL$5DA710D3 (#1) that are valid to merge.
Query block (42167a878) before join elimination:
SQL:
Query block (42167a878) unchanged
SU:   Transforming ANY subquery to a join.
*************************
Set-Join Conversion (SJC)
*************************
SJC: Considering set-join conversion in SEL$5DA710D3 (#1).
Query block (4125c7d38) before join elimination:
SQL:******* UNPARSED QUERY IS *******
SELECT 'Y' "'Y'" FROM ."TIME_ATTRIBUTE_USAGES" "TIME_ATTRIBUTE_USAGES",."TIME_ATTRIBUTES_TB" "TIME_ATTRIBUTES_TB" WHERE "TIME_ATTRIBUTES_TB"."ATTRIBUTE_CATEGORY"='REASON' AND "TIME_ATTRIBUTES_TB"."ATTRIBUTE7"=DECODE(:B1,NULL,
"TIME_ATTRIBUTES_TB"."ATTRIBUTE7",:B2)
 AND "TIME_ATTRIBUTES_TB"."ATTRIBUTE_ID"="TIME_ATTRIBUTE_USAGES"."ATTRIBUTE_ID" AND "TIME_ATTRIBUTE_USAGES"."BUILDING_BLOCK_ID"=:B3 AND
"TIME_ATTRIBUTE_USAGES"."BUILDING_BLOCK_OVN"=:B4
Query block (4125c7d38) unchanged
**************************
Predicate Move-Around (PM)
**************************
PM: Considering predicate move-around in SEL$5DA710D3 (#1).
PM:   Checking validity of predicate move-around in SEL$5DA710D3 (#1).
PM:     PM bypassed: Outer query contains no views.
Registered qb: SEL$5DA710D3 0xaef098d8 (COPY SEL$5DA710D3)
  signature(): NULL
***********************************
Cost-Based Filter Predicate Pull-Up
***********************************
***********************************
Cost-Based Join Predicate Push-down
***********************************
JPPD: Checking validity of push-down in query block SEL$5DA710D3 (#1)
JPPD:   No view found to push predicate into.
JPPD: Applying transformation directives
JPPD: Checking validity of push-down in query block SEL$5DA710D3 (#1)
JPPD:   No view found to push predicate into.
FPD: Considering simple filter push in SEL$5DA710D3 (#1)
FPD:   Current where clause predicates in SEL$5DA710D3 (#1) :

"TIME_ATTRIBUTES_TB"."ATTRIBUTE_CATEGORY"='REASON' AND "TIME_ATTRIBUTES_TB"."ATTRIBUTE7"=DECODE(:B1,NULL,"TIME_ATTRIBUTES_TB"."ATTRIBUTE7",:B2)
 AND "TIME_ATTRIBUTES_TB"."ATTRIBUTE_ID"="TIME_ATTRIBUTE_USAGES"."ATTRIBUTE_ID" AND "TIME_ATTRIBUTE_USAGES"."BUILDING_BLOCK_ID"=TO_NUMBER(:B3) AND "TIME_ATTRIBUTE_USAGES"."BUILDING_BLOCK_OVN"=TO_NUMBER(:B4)
kkogcp: try to generate transitive predicate from check constraints for SEL$5DA710D3 (#1)
predicates with check contraints:
"TIME_ATTRIBUTES_TB"."ATTRIBUTE_CATEGORY"='REASON' AND "TIME_ATTRIBUTES_TB"."ATTRIBUTE7"=DECODE(:B1,NULL,"TIME_ATTRIBUTES_TB"."ATTRIBUTE7",:B2)
 AND "TIME_ATTRIBUTES_TB"."ATTRIBUTE_ID"="TIME_ATTRIBUTE_USAGES"."ATTRIBUTE_ID" AND "TIME_ATTRIBUTE_USAGES"."BUILDING_BLOCK_ID"=TO_NUMBER(:B3) AND "TIME_ATTRIBUTE_USAGES"."BUILDING_BLOCK_OVN"=TO_NUMBER(:B4)
after transitive predicate generation:
"TIME_ATTRIBUTES_TB"."ATTRIBUTE_CATEGORY"='REASON' AND "TIME_ATTRIBUTES_TB"."ATTRIBUTE7"=DECODE(:B1,NULL,"TIME_ATTRIBUTES_TB"."ATTRIBUTE7",:B2)
 AND "TIME_ATTRIBUTES_TB"."ATTRIBUTE_ID"="TIME_ATTRIBUTE_USAGES"."ATTRIBUTE_ID" AND "TIME_ATTRIBUTE_USAGES"."BUILDING_BLOCK_ID"=TO_NUMBER(:B3) AND "TIME_ATTRIBUTE_USAGES"."BUILDING_BLOCK_OVN"=TO_NUMBER(:B4)
finally:
"TIME_ATTRIBUTES_TB"."ATTRIBUTE_CATEGORY"='REASON' AND "TIME_ATTRIBUTES_TB"."ATTRIBUTE7"=DECODE(:B1,NULL,"TIME_ATTRIBUTES_TB"."ATTRIBUTE7",:B2)
 AND "TIME_ATTRIBUTES_TB"."ATTRIBUTE_ID"="TIME_ATTRIBUTE_USAGES"."ATTRIBUTE_ID" AND "TIME_ATTRIBUTE_USAGES"."BUILDING_BLOCK_ID"=TO_NUMBER(:B3) AND "TIME_ATTRIBUTE_USAGES"."BUILDING_BLOCK_OVN"=TO_NUMBER(:B4)
kkoqbc-start
            : call(in-use=2048, alloc=16344), compile(in-use=95624, alloc=147008)
****************
QUERY BLOCK TEXT
****************
SELECT 'Y'
FROM
TIME_ATTRIBUTES_TB WHERE ATTRIBUTE_ID IN (SELECT ATTRIBUTE_ID
FROM TIME_ATTRIBUTE_USAGES WHERE BUILDING_BLOCK_ID = :B3 AND
BUILDING_BLOCK_OVN = :B2 ) AND ATTRIBUTE_CATEGORY = 'REASON' AND
ATTRIBUTE7 = DECODE (:B1 , NULL, ATTRIBUTE7, :B1 )
*********************
QUERY BLOCK SIGNATURE
*********************
qb name was generated
signature (optimizer): qb_name=SEL$5DA710D3 nbfros=2 flg=0
  fro(0): flg=0 objn=216996 hint_alias="TIME_ATTRIBUTES_TB"@"SEL$1"
  fro(1): flg=0 objn=217367 hint_alias="TIME_ATTRIBUTE_USAGES"@"SEL$2"
*****************************
SYSTEM STATISTICS INFORMATION
*****************************
  Using NOWORKLOAD Stats
  CPUSPEED: 587 millions instruction/sec
  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
  IOSEEKTIM: 10 milliseconds (default is 10)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: TIME_ATTRIBUTES_TB  Alias: TIME_ATTRIBUTES_TB
    #Rows: 706053520  #Blks:  7164508  AvgRowLen:  56.00
  Column (#1): ATTRIBUTE_ID(NUMBER)
    AvgLen: 7.00 NDV: 706053520 Nulls: 0 Density: 1.4163e-09 Min: 62 Max: 709911995
Index Stats::
  Index: TIME_ATTRIBUTES_TB_F2  Col#: 37
    LVLS: 3  #LB: 2091280  #DK: 1  LB/K: 2091280.00  DB/K: 8361560.00  CLUF: 8361560.00
  Index: TIME_ATTRIBUTES_TB_FK1  Col#: 33
    LVLS: 3  #LB: 3939860  #DK: 6  LB/K: 656643.00  DB/K: 5187026.00  CLUF: 31122160.00
  Index: TIME_ATTRIBUTES_TB_FK2  Col#: 2
    LVLS: 3  #LB: 5602980  #DK: 11  LB/K: 509361.00  DB/K: 2996421.00  CLUF: 32960640.00
  Index: TIME_ATTRIBUTES_TB_FK3  Col#: 3
    LVLS: 3  #LB: 3336500  #DK: 18  LB/K: 185361.00  DB/K: 1574143.00  CLUF: 28334580.00
  Index: TIME_ATTRIBUTES_TB_FK4  Col#: 4
    LVLS: 3  #LB: 2359420  #DK: 228  LB/K: 10348.00  DB/K: 223462.00  CLUF: 50949420.00
  Index: TIME_ATTRIBUTES_TB_FK5  Col#: 5
    LVLS: 2  #LB: 108120  #DK: 366  LB/K: 295.00  DB/K: 3090.00  CLUF: 1131280.00
  Index: TIME_ATTRIBUTES_TB_N001  Col#: 17
    LVLS: 3  #LB: 835180  #DK: 3707  LB/K: 225.00  DB/K: 11315.00  CLUF: 41946720.00
  Index: TIME_ATTRIBUTES_TB_N1  Col#: 36
    LVLS: 0  #LB: 0  #DK: 0  LB/K: 0.00  DB/K: 0.00  CLUF: 0.00
  Index: TIME_ATTRIBUTES_TB_PK  Col#: 1
    LVLS: 3  #LB: 2110620  #DK: 708239480  LB/K: 1.00  DB/K: 1.00  CLUF: 78157640.00
***********************
Table Stats::
  Table: TIME_ATTRIBUTE_USAGES  Alias: TIME_ATTRIBUTE_USAGES
    #Rows: 710932460  #Blks:  4864950  AvgRowLen:  40.00
  Column (#2): ATTRIBUTE_ID(NUMBER)
    AvgLen: 7.00 NDV: 710681160 Nulls: 0 Density: 1.4071e-09 Min: 5 Max: 709912023
Index Stats::
  Index: TIME_ATTRIBUTE_USAGES_FK1  Col#: 2
    LVLS: 3  #LB: 3347280  #DK: 710681160  LB/K: 1.00  DB/K: 1.00  CLUF: 85764140.00
  Index: TIME_ATTRIBUTE_USAGES_FK2  Col#: 3 10
    LVLS: 3  #LB: 3091760  #DK: 60135854  LB/K: 1.00  DB/K: 15.00  CLUF: 265197800.00
  Index: TIME_ATTRIBUTE_USAGES_N1  Col#: 11
    LVLS: 0  #LB: 0  #DK: 0  LB/K: 0.00  DB/K: 0.00  CLUF: 0.00
  Index: TIME_ATTRIBUTE_USAGES_PK  Col#: 1
    LVLS: 3  #LB: 2321920  #DK: 717756460  LB/K: 1.00  DB/K: 1.00  CLUF: 75501020.00
***************************************
SINGLE TABLE ACCESS PATH
  Column (#3): BUILDING_BLOCK_ID(NUMBER)
    AvgLen: 7.00 NDV: 60135854 Nulls: 240 Density: 1.6629e-08 Min: 0 Max: 306935448
  Column (#10): BUILDING_BLOCK_OVN(NUMBER)
    AvgLen: 3.00 NDV: 56 Nulls: 30060 Density: 0.017857 Min: 1 Max: 56
  Table: TIME_ATTRIBUTE_USAGES  Alias: TIME_ATTRIBUTE_USAGES
    Card: Original: 710932460  Rounded: 1  Computed: 0.21  Non Adjusted: 0.21
  Access Path: TableScan
    Cost:  1370967.52  Resp: 1370967.52  Degree: 0
      Cost_io: 1317592.00  Cost_cpu: 375893032101
      Resp_io: 1317592.00  Resp_cpu: 375893032101
  Access Path: index (AllEqRange)
    Index: TIME_ATTRIBUTE_USAGES_FK2
    resc_io: 9.00  resc_cpu: 70453
    ix_sel: 1.6628e-08  ix_sel_with_filters: 1.6628e-08
    Cost: 9.01  Resp: 9.01  Degree: 1
******** Begin index join costing ********
  ****** trying bitmap/domain indexes ******
  Access Path: index (AllEqRange)
    Index: TIME_ATTRIBUTE_USAGES_FK2
    resc_io: 4.00  resc_cpu: 30886
    ix_sel: 1.6628e-08  ix_sel_with_filters: 1.6628e-08
    Cost: 4.00  Resp: 4.00  Degree: 0
******** End index join costing ********
  Best:: AccessPath: IndexRange  Index: TIME_ATTRIBUTE_USAGES_FK2
         Cost: 9.01  Degree: 1  Resp: 9.01  Card: 0.21  Bytes: 0
***************************************
SINGLE TABLE ACCESS PATH
  Column (#2): ATTRIBUTE_CATEGORY(VARCHAR2)
    AvgLen: 14.00 NDV: 11 Nulls: 7680 Density: 0.090909
  Column (#9): ATTRIBUTE7(VARCHAR2)
    AvgLen: 2.00 NDV: 226 Nulls: 572282140 Density: 0.0044248
  Table: TIME_ATTRIBUTES_TB  Alias: TIME_ATTRIBUTES_TB
    Card: Original: 706053520  Rounded: 284009  Computed: 284008.79  Non Adjusted: 284008.79
  Access Path: TableScan
    Cost:  1986460.76  Resp: 1986460.76  Degree: 0
      Cost_io: 1940389.00  Cost_cpu: 324456886142
      Resp_io: 1940389.00  Resp_cpu: 324456886142
  Access Path: index (AllEqRange)
    Index: TIME_ATTRIBUTES_TB_FK2
    resc_io: 3505750.00  resc_cpu: 77251648617
    ix_sel: 0.090908  ix_sel_with_filters: 0.090908
    Cost: 3516719.47  Resp: 3516719.47  Degree: 1
  Best:: AccessPath: TableScan
         Cost: 1986460.76  Degree: 1  Resp: 1986460.76  Card: 284008.79  Bytes: 0
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
***********************
Join order[1]:  TIME_ATTRIBUTES_TB[TIME_ATTRIBUTES_TB]#0  TIME_ATTRIBUTE_USAGES[TIME_ATTRIBUTE_USAGES]#1
***************
Now joining: TIME_ATTRIBUTE_USAGES[TIME_ATTRIBUTE_USAGES]#1
***************
NL Join
  Outer table: Card: 284008.79  Cost: 1986460.76  Resp: 1986460.76  Degree: 1  Bytes: 23
  Inner table: TIME_ATTRIBUTE_USAGES  Alias: TIME_ATTRIBUTE_USAGES
  Access Path: TableScan
    NL Join:  Cost: 389368709373.65  Resp: 389368709373.65  Degree: 1
      Cost_io: 374209536206.00  Cost_cpu: 106757328610948784
      Resp_io: 374209536206.00  Resp_cpu: 106757328610948784
  Access Path: index (AllEqJoinGuess)
    Index: TIME_ATTRIBUTE_USAGES_FK1
    resc_io: 4.00  resc_cpu: 30547
    ix_sel: 1.4071e-09  ix_sel_with_filters: 1.4071e-09
    NL Join: Cost: 3123728.67  Resp: 3123728.67  Degree: 1
      Cost_io: 3076425.00  Cost_cpu: 333132479765
      Resp_io: 3076425.00  Resp_cpu: 333132479765
  Access Path: index (AllEqJoin)
    Index: TIME_ATTRIBUTE_USAGES_FK2
    resc_io: 8.00  resc_cpu: 64182
    ix_sel: 1.6628e-08  ix_sel_with_filters: 1.6628e-08
    NL Join: Cost: 4261121.09  Resp: 4261121.09  Degree: 1
      Cost_io: 4212461.00  Cost_cpu: 342685015456
      Resp_io: 4212461.00  Resp_cpu: 342685015456
  Best NL cost: 3123728.67
          resc: 3123728.67 resc_io: 3076425.00 resc_cpu: 333132479765
          resp: 3123728.67 resp_io: 3076425.00 resp_cpu: 333132479765
Semi Join Card:  1.00 = outer (284008.79) * sel (3.5210e-06)
Join Card - Rounded: 1 Computed: 1.00
SM Join
  Outer table:
    resc: 1986460.76  card 284008.79  bytes: 23  deg: 1  resp: 1986460.76
  Inner table: TIME_ATTRIBUTE_USAGES  Alias: TIME_ATTRIBUTE_USAGES
    resc: 9.01  card: 0.21  bytes: 17  deg: 1  resp: 9.01
    using dmeth: 2  #groups: 1
    SORT resource      Sort statistics
      Sort width:        6142 Area size:     1048576 Max Area size:  1073741824
      Degree:               1
      Blocks to Sort:    1252 Row size:           36 Total Rows:         284009
      Initial runs:         2 Merge passes:        1 IO Cost / pass:        680
      Total IO sort cost: 1932      Total CPU sort cost: 269679334
      Total Temp space used: 20603000
    SORT resource      Sort statistics
      Sort width:        6142 Area size:     1048576 Max Area size:  1073741824
      Degree:               1
      Blocks to Sort:       1 Row size:           29 Total Rows:              1
      Initial runs:         1 Merge passes:        0 IO Cost / pass:          0
      Total IO sort cost: 0      Total CPU sort cost: 7042424
      Total Temp space used: 0
  SM join: Resc: 1988441.06  Resp: 1988441.06  [multiMatchCost=0.00]
  SM cost: 1988441.06
     resc: 1988441.06 resc_io: 1942330.00 resc_cpu: 324733678353
     resp: 1988441.06 resp_io: 1942330.00 resp_cpu: 324733678353
SM Join (with index on outer)
  Access Path: index (FullScan)
    Index: TIME_ATTRIBUTES_TB_PK
    resc_io: 80268263.00  resc_cpu: 987555349841
    ix_sel: 1  ix_sel_with_filters: 1
    Cost: 80408492.46  Resp: 80408492.46  Degree: 1
  Outer table:
    resc: 80408492.46  card 284008.79  bytes: 23  deg: 1  resp: 80408492.46
  Inner table: TIME_ATTRIBUTE_USAGES  Alias: TIME_ATTRIBUTE_USAGES
    resc: 9.01  card: 0.21  bytes: 17  deg: 1  resp: 9.01
    using dmeth: 2  #groups: 1
    SORT resource      Sort statistics
      Sort width:        6142 Area size:     1048576 Max Area size:  1073741824
      Degree:               1
      Blocks to Sort:       1 Row size:           29 Total Rows:              1
      Initial runs:         1 Merge passes:        0 IO Cost / pass:          0
      Total IO sort cost: 0      Total CPU sort cost: 7042424
      Total Temp space used: 0
  SM join: Resc: 80408502.47  Resp: 80408502.47  [multiMatchCost=0.00]
HA Join
  Outer table:
    resc: 1986460.76  card 284008.79  bytes: 23  deg: 1  resp: 1986460.76
  Inner table: TIME_ATTRIBUTE_USAGES  Alias: TIME_ATTRIBUTE_USAGES
    resc: 9.01  card: 0.21  bytes: 17  deg: 1  resp: 9.01
    using dmeth: 2  #groups: 1
    Cost per ptn: 478.26  #ptns: 1
    hash_area: 0 (max=256)   Hash join: Resc: 1986948.03  Resp: 1986948.03  [multiMatchCost=0.00]
HA Join (swap)
  Outer table:
    resc: 9.01  card 0.21  bytes: 17  deg: 1  resp: 9.01
  Inner table: TIME_ATTRIBUTES_TB  Alias: TIME_ATTRIBUTES_TB
    resc: 1986460.76  card: 284008.79  bytes: 23  deg: 1  resp: 1986460.76
    using dmeth: 2  #groups: 1
    Cost per ptn: 4.53  #ptns: 1
    hash_area: 0 (max=256)   Hash join: Resc: 1986474.30  Resp: 1986474.30  [multiMatchCost=0.00]
  HA cost: 1986474.30
     resc: 1986474.30 resc_io: 1940398.00 resc_cpu: 324488878858
     resp: 1986474.30 resp_io: 1940398.00 resp_cpu: 324488878858
Best:: JoinMethod: HashSemi
       Cost: 1986474.30  Degree: 1  Resp: 1986474.30  Card: 1.00  Bytes: 40
***********************
Best so far: Table#: 0  cost: 1986460.7609  card: 284008.7852  bytes: 6532207
             Table#: 1  cost: 1986474.3038  card: 1.0000  bytes: 40
***********************
Join order[2]:  TIME_ATTRIBUTE_USAGES[TIME_ATTRIBUTE_USAGES]#1  TIME_ATTRIBUTES_TB[TIME_ATTRIBUTES_TB]#0
    SORT resource      Sort statistics
      Sort width:        6142 Area size:     1048576 Max Area size:  1073741824
      Degree:               1
      Blocks to Sort:       1 Row size:           29 Total Rows:              1
      Initial runs:         1 Merge passes:        0 IO Cost / pass:          0
      Total IO sort cost: 0      Total CPU sort cost: 7042424
      Total Temp space used: 0
***************
Now joining: TIME_ATTRIBUTES_TB[TIME_ATTRIBUTES_TB]#0
***************
NL Join
  Outer table: Card: 0.21  Cost: 10.01  Resp: 10.01  Degree: 1  Bytes: 17
  Inner table: TIME_ATTRIBUTES_TB  Alias: TIME_ATTRIBUTES_TB
  Access Path: TableScan
    NL Join:  Cost: 1986470.77  Resp: 1986470.77  Degree: 1
      Cost_io: 1940398.00  Cost_cpu: 324463999020
      Resp_io: 1940398.00  Resp_cpu: 324463999020
  Access Path: index (UniqueScan)
    Index: TIME_ATTRIBUTES_TB_PK
    resc_io: 3.00  resc_cpu: 23652
    ix_sel: 1.4163e-09  ix_sel_with_filters: 1.4163e-09
    NL Join: Cost: 13.01  Resp: 13.01  Degree: 1
      Cost_io: 12.00  Cost_cpu: 7136529
      Resp_io: 12.00  Resp_cpu: 7136529
  Access Path: index (AllEqJoin)
    Index: TIME_ATTRIBUTES_TB_FK2
    resc_io: 3505749.00  resc_cpu: 77251642346
    ix_sel: 0.090908  ix_sel_with_filters: 0.090908
    NL Join: Cost: 3516728.48  Resp: 3516728.48  Degree: 1
      Cost_io: 3505758.00  Cost_cpu: 77258755223
      Resp_io: 3505758.00  Resp_cpu: 77258755223
  Access Path: index (AllEqUnique)
    Index: TIME_ATTRIBUTES_TB_PK
    resc_io: 3.00  resc_cpu: 23652
    ix_sel: 1.4163e-09  ix_sel_with_filters: 1.4163e-09
    NL Join: Cost: 13.01  Resp: 13.01  Degree: 1
      Cost_io: 12.00  Cost_cpu: 7136529
      Resp_io: 12.00  Resp_cpu: 7136529
  Best NL cost: 13.01
          resc: 13.01 resc_io: 12.00 resc_cpu: 7136529
          resp: 13.01 resp_io: 12.00 resp_cpu: 7136529
Join Card:  0.21 = outer (0.21) * inner (284008.79) * sel (3.5210e-06)
Join Card - Rounded: 1 Computed: 0.21
SM Join
  Outer table:
    resc: 10.01  card 0.21  bytes: 17  deg: 1  resp: 10.01
  Inner table: TIME_ATTRIBUTES_TB  Alias: TIME_ATTRIBUTES_TB
    resc: 1986460.76  card: 284008.79  bytes: 23  deg: 1  resp: 1986460.76
    using dmeth: 2  #groups: 1
    SORT resource      Sort statistics
      Sort width:        6142 Area size:     1048576 Max Area size:  1073741824
      Degree:               1
      Blocks to Sort:       1 Row size:           29 Total Rows:              1
      Initial runs:         1 Merge passes:        0 IO Cost / pass:          0
      Total IO sort cost: 0      Total CPU sort cost: 7042424
      Total Temp space used: 0
    SORT resource      Sort statistics
      Sort width:        6142 Area size:     1048576 Max Area size:  1073741824
      Degree:               1
      Blocks to Sort:    1252 Row size:           36 Total Rows:         284009
      Initial runs:         2 Merge passes:        1 IO Cost / pass:        680
      Total IO sort cost: 1932      Total CPU sort cost: 269679334
      Total Temp space used: 20603000
  SM join: Resc: 1988442.06  Resp: 1988442.06  [multiMatchCost=0.00]
  SM cost: 1988442.06
     resc: 1988442.06 resc_io: 1942330.00 resc_cpu: 324740720778
     resp: 1988442.06 resp_io: 1942330.00 resp_cpu: 324740720778
HA Join
  Outer table:
    resc: 10.01  card 0.21  bytes: 17  deg: 1  resp: 10.01
  Inner table: TIME_ATTRIBUTES_TB  Alias: TIME_ATTRIBUTES_TB
    resc: 1986460.76  card: 284008.79  bytes: 23  deg: 1  resp: 1986460.76
    using dmeth: 2  #groups: 1
    Cost per ptn: 4.53  #ptns: 1
    hash_area: 0 (max=256)   Hash join: Resc: 1986475.30  Resp: 1986475.30  [multiMatchCost=0.00]
  HA cost: 1986475.30
     resc: 1986475.30 resc_io: 1940398.00 resc_cpu: 324495921282
     resp: 1986475.30 resp_io: 1940398.00 resp_cpu: 324495921282
Plan cardinality mismatch: best card= 0.99999924368   curr card= 0.21109988440
Best:: JoinMethod: NestedLoop
       Cost: 13.01  Degree: 1  Resp: 13.01  Card: 0.21  Bytes: 40
***********************
Best so far: Table#: 1  cost: 10.0100  card: 0.2111  bytes: 17
             Table#: 0  cost: 13.0134  card: 0.2111  bytes: 40
(newjo-stop-1) k:0, spcnt:0, perm:2, maxperm:2000
*********************************
Number of join permutations tried: 2
*********************************
    SORT resource      Sort statistics
      Sort width:        6142 Area size:     1048576 Max Area size:  1073741824
      Degree:               1
      Blocks to Sort:       1 Row size:           29 Total Rows:              1
      Initial runs:         1 Merge passes:        0 IO Cost / pass:          0
      Total IO sort cost: 0      Total CPU sort cost: 7042424
      Total Temp space used: 0
(newjo-save)    [1 0 ]
Final - All Rows Plan:  Best join order: 2
  Cost: 13.0134  Degree: 1  Card: 1.0000  Bytes: 40
  Resc: 13.0134  Resc_io: 12.0000  Resc_cpu: 7136529
  Resp: 13.0134  Resp_io: 12.0000  Resc_cpu: 7136529
kkoipt: Query block SEL$5DA710D3 (#1)
******* UNPARSED QUERY IS *******
SELECT 'Y' "'Y'" FROM ."TIME_ATTRIBUTE_USAGES" "TIME_ATTRIBUTE_USAGES",."TIME_ATTRIBUTES_TB" "TIME_ATTRIBUTES_TB" WHERE "TIME_ATTRIBUTE_USAGES"."BUILDING_BLOCK_OVN"=TO_NUMBER(:B1)
 AND "TIME_ATTRIBUTE_USAGES"."BUILDING_BLOCK_ID"=TO_NUMBER(:B2)
 AND "TIME_ATTRIBUTES_TB"."ATTRIBUTE_ID"="TIME_ATTRIBUTE_USAGES"."ATTRIBUTE_ID" AND "TIME_ATTRIBUTES_TB"."ATTRIBUTE_CATEGORY"='REASON' AND "TIME_ATTRIBUTES_TB"."ATTRIBUTE7"=DECODE(:B3,NULL,"TIME_ATTRIBUTES_TB"."ATTRIBUTE7",:B4)
kkoqbc-end
          : call(in-use=63712, alloc=81816), compile(in-use=102784, alloc=147008)
apadrv-end: call(in-use=63712, alloc=81816), compile(in-use=103640, alloc=147008)

sql_id=9q70ydnbyryjn.
Current SQL statement for this session:
EXPLAIN PLAN SET statement_id = '1' INTO sqlt$_plan_table FOR
SELECT 'Y'
FROM
TIME_ATTRIBUTES_TB WHERE ATTRIBUTE_ID IN (SELECT ATTRIBUTE_ID
FROM TIME_ATTRIBUTE_USAGES WHERE BUILDING_BLOCK_ID = :B3 AND
BUILDING_BLOCK_OVN = :B2 ) AND ATTRIBUTE_CATEGORY = 'REASON' AND
ATTRIBUTE7 = DECODE (:B1 , NULL, ATTRIBUTE7, :B1 )

============
Plan Table
============
----------------------------------------------------------------------+-----------------------------------+
| Id  | Operation                      | Name                         | Rows  | Bytes | Cost  | Time      |
----------------------------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT               |                              |       |       |    13 |           |
| 1   |  NESTED LOOPS                  |                              |     1 |    40 |    13 |  00:00:01 |
| 2   |   SORT UNIQUE                  |                              |     1 |    17 |     9 |  00:00:01 |
| 3   |    TABLE ACCESS BY INDEX ROWID | TIME_ATTRIBUTE_USAGES    |     1 |    17 |     9 |  00:00:01 |
| 4   |     INDEX RANGE SCAN           | TIME_ATTRIBUTE_USAGES_FK2|    12 |       |     4 |  00:00:01 |
| 5   |   TABLE ACCESS BY INDEX ROWID  | TIME_ATTRIBUTES_TB          |     1 |    23 |     3 |  00:00:01 |
| 6   |    INDEX UNIQUE SCAN           | TIME_ATTRIBUTES_TB_PK       |     1 |       |     2 |  00:00:01 |
----------------------------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
4 - access("BUILDING_BLOCK_ID"=TO_NUMBER(:B3) AND "BUILDING_BLOCK_OVN"=TO_NUMBER(:B2))
5 - filter(("ATTRIBUTE_CATEGORY"='REASON' AND "ATTRIBUTE7"=DECODE(:B1,NULL,"ATTRIBUTE7",:B1)))
6 - access("ATTRIBUTE_ID"="ATTRIBUTE_ID")

Content of other_xml column
===========================
  db_version     : 10.2.0.2

  plan_hash      : 2963004813
  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.2')
      OPT_PARAM('_b_tree_bitmap_plans' 'false')
      OPT_PARAM('_fast_full_scan_enabled' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$5DA710D3")
      UNNEST(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      INDEX(@"SEL$5DA710D3" "TIME_ATTRIBUTE_USAGES"@"SEL$2" ("TIME_ATTRIBUTE_USAGES"."BUILDING_BLOCK_ID" "TIME_ATTRIBUTE_USAGES"."BUILDING_BLOCK_OVN"))
      INDEX(@"SEL$5DA710D3" "TIME_ATTRIBUTES_TB"@"SEL$1" ("TIME_ATTRIBUTES_TB"."ATTRIBUTE_ID"))
      LEADING(@"SEL$5DA710D3" "TIME_ATTRIBUTE_USAGES"@"SEL$2" "TIME_ATTRIBUTES_TB"@"SEL$1")
      USE_NL(@"SEL$5DA710D3" "TIME_ATTRIBUTES_TB"@"SEL$1")
    END_OUTLINE_DATA
  */

Optimizer environment:
  optimizer_mode_hinted               = false
  optimizer_features_hinted           = 0.0.0
  parallel_execution_enabled          = true
  parallel_query_forced_dop           = 0
  parallel_dml_forced_dop             = 0
  parallel_ddl_forced_degree          = 0
  parallel_ddl_forced_instances       = 0
  _query_rewrite_fudge                = 90
  optimizer_features_enable           = 10.2.0.2
  _optimizer_search_limit             = 5
  cpu_count                           = 28
  active_instance_count               = 1
  parallel_threads_per_cpu            = 2
  hash_area_size                      = 131072
  bitmap_merge_area_size              = 1048576
  sort_area_size                      = 65536
  sort_area_retained_size             = 0
  _sort_elimination_cost_ratio        = 5
  _optimizer_block_size               = 8192
  _sort_multiblock_read_count         = 2
  _hash_multiblock_io_count           = 0
  _db_file_optimizer_read_count       = 8
  _optimizer_max_permutations         = 2000
  pga_aggregate_target                = 20971520 KB
  _pga_max_size                       = 2097152 KB
  _query_rewrite_maxdisjunct          = 257
  _smm_auto_min_io_size               = 56 KB
  _smm_auto_max_io_size               = 248 KB
  _smm_min_size                       = 1024 KB
  _smm_max_size                       = 1048576 KB
  _smm_px_max_size                    = 10485760 KB
  _cpu_to_io                          = 0
  _optimizer_undo_cost_change         = 10.2.0.2
  parallel_query_mode                 = enabled
  parallel_dml_mode                   = disabled
  parallel_ddl_mode                   = enabled
  optimizer_mode                      = all_rows
  sqlstat_enabled                     = false
  _optimizer_percent_parallel         = 101
  _always_anti_join                   = choose
  _always_semi_join                   = choose
  _optimizer_mode_force               = true
  _partition_view_enabled             = true
  _always_star_transformation         = false
  _query_rewrite_or_error             = false
  _hash_join_enabled                  = true
  cursor_sharing                      = exact
  _b_tree_bitmap_plans                = false
  star_transformation_enabled         = false
  _optimizer_cost_model               = choose
  _new_sort_cost_estimate             = true
  _complex_view_merging               = true
  _unnest_subquery                    = true
  _eliminate_common_subexpr           = true
  _pred_move_around                   = true
  _convert_set_to_join                = false
  _push_join_predicate                = true
  _push_join_union_view               = true
  _fast_full_scan_enabled             = false
  _optim_enhance_nnull_detection      = true
  _parallel_broadcast_enabled         = true
  _px_broadcast_fudge_factor          = 100
  _ordered_nested_loop                = true
  _no_or_expansion                    = false
  optimizer_index_cost_adj            = 100
  optimizer_index_caching             = 0
  _system_index_caching               = 0
  _disable_datalayer_sampling         = false
  query_rewrite_enabled               = true
  query_rewrite_integrity             = enforced
  _query_cost_rewrite                 = true
  _query_rewrite_2                    = true
  _query_rewrite_1                    = true
  _query_rewrite_expression           = true
  _query_rewrite_jgmigrate            = true
  _query_rewrite_fpc                  = true
  _query_rewrite_drj                  = true
  _full_pwise_join_enabled            = true
  _partial_pwise_join_enabled         = true
  _left_nested_loops_random           = true
  _improved_row_length_enabled        = true
  _index_join_enabled                 = true
  _enable_type_dep_selectivity        = true
  _improved_outerjoin_card            = true
  _optimizer_adjust_for_nulls         = true
  _optimizer_degree                   = 0
  _use_column_stats_for_function      = true
  _subquery_pruning_enabled           = true
  _subquery_pruning_mv_enabled        = false
  _or_expand_nvl_predicate            = true
  _like_with_bind_as_equality         = true
  _table_scan_cost_plus_one           = true
  _cost_equality_semi_join            = true
  _default_non_equality_sel_check     = true
  _new_initial_join_orders            = true
  _oneside_colstat_for_equijoins      = true
  _optim_peek_user_binds              = true
  _minimal_stats_aggregation          = true
  _force_temptables_for_gsets         = false
  workarea_size_policy                = auto
  _smm_auto_cost_enabled              = true
  _gs_anti_semi_join_allowed          = true
  _optim_new_default_join_sel         = true
  optimizer_dynamic_sampling          = 2
  _pre_rewrite_push_pred              = true
  _optimizer_new_join_card_computation = true
  _union_rewrite_for_gs               = yes_gset_mvs
  _generalized_pruning_enabled        = true
  _optim_adjust_for_part_skews        = true
  _force_datefold_trunc               = false
  statistics_level                    = typical
  _optimizer_system_stats_usage       = true
  skip_unusable_indexes               = true
  _remove_aggr_subquery               = true
  _optimizer_push_down_distinct       = 0
  _dml_monitoring_enabled             = true
  _optimizer_undo_changes             = false
  _predicate_elimination_enabled      = true
  _nested_loop_fudge                  = 100
  _project_view_columns               = true
  _local_communication_costing_enabled = true
  _local_communication_ratio          = 50
  _query_rewrite_vop_cleanup          = true
  _slave_mapping_enabled              = true
  _optimizer_cost_based_transformation = linear
  _optimizer_mjc_enabled              = true
  _right_outer_hash_enable            = true
  _spr_push_pred_refspr               = true
  _optimizer_cache_stats              = false
  _optimizer_cbqt_factor              = 50
  _optimizer_squ_bottomup             = true
  _fic_area_size                      = 131072
  _optimizer_skip_scan_enabled        = true
  _optimizer_cost_filter_pred         = false
  _optimizer_sortmerge_join_enabled   = true
  _optimizer_join_sel_sanity_check    = true
  _mmv_query_rewrite_enabled          = true
  _bt_mmv_query_rewrite_enabled       = true
  _add_stale_mv_to_dependency_list    = true
  _distinct_view_unnesting            = false
  _optimizer_dim_subq_join_sel        = true
  _optimizer_disable_strans_sanity_checks = 0
  _optimizer_compute_index_stats      = true
  _push_join_union_view2              = true
  _optimizer_ignore_hints             = false
  _optimizer_random_plan              = 0
  _query_rewrite_setopgrw_enable      = true
  _optimizer_correct_sq_selectivity   = true
  _disable_function_based_index       = false
  _optimizer_join_order_control       = 3
  _optimizer_cartesian_enabled        = true
  _optimizer_starplan_enabled         = true
  _extended_pruning_enabled           = true
  _optimizer_push_pred_cost_based     = true
  _sql_model_unfold_forloops          = run_time
  _enable_dml_lock_escalation         = false
  _bloom_filter_enabled               = true
  _update_bji_ipdml_enabled           = 0
  _optimizer_extended_cursor_sharing  = udo
  _dm_max_shared_pool_pct             = 1
  _optimizer_cost_hjsmj_multimatch    = true
  _optimizer_transitivity_retain      = true
  _px_pwg_enabled                     = true
  optimizer_secure_view_merging       = false
  _optimizer_join_elimination_enabled = true
  flashback_table_rpi                 = non_fbt
  _optimizer_cbqt_no_size_restriction = true
  _optimizer_enhanced_filter_push     = true
  _optimizer_filter_pred_pullup       = true
  _rowsrc_trace_level                 = 0
  _simple_view_merging                = true
  _optimizer_rownum_pred_based_fkr    = true
  _optimizer_better_inlist_costing    = all
  _optimizer_self_induced_cache_cost  = false
  _optimizer_min_cache_blocks         = 10
  _optimizer_or_expansion             = depth
  _optimizer_order_by_elimination_enabled = true
  _optimizer_outer_to_anti_enabled    = true
  _selfjoin_mv_duplicates             = true
  _dimension_skip_null                = true
  _force_rewrite_enable               = false
  _optimizer_star_tran_in_with_clause = true
  _optimizer_complex_pred_selectivity = true
  _optimizer_connect_by_cost_based    = true
  _gby_hash_aggregation_enabled       = true
  _globalindex_pnum_filter_enabled    = true
  _fix_control_key                    = 0
  _optimizer_skip_scan_guess          = false
  _enable_row_shipping                = false
  *********************************
  Bug Fix Control Environment
  ***************************
  fix  4611850 = enabled
  fix  4663804 = enabled
  fix  4663698 = enabled
  fix  4545833 = enabled
  fix  3499674 = disabled
  fix  4584065 = enabled
  fix  4602374 = enabled
  fix  4569940 = enabled
  fix  4631959 = enabled
  fix  4519340 = enabled
  fix  4550003 = enabled
  fix  4488689 = enabled
  fix  3118776 = enabled
  fix  4519016 = enabled
  fix  4487253 = enabled
  fix  4556762 = 15
  fix  4728348 = enabled
  fix  4723244 = enabled
  fix  4554846 = enabled
  fix  2492766 = enabled
  fix  4175830 = enabled
  fix  4722900 = enabled
  fix  4711525 = enabled
  fix  4273361 = enabled
  fix  4483240 = enabled
  fix  4904838 = enabled
  fix  5084239 = enabled
  fix  5838613 = enabled
  fix  5385629 = enabled
  fix  5449488 = enabled
  fix  5483301 = enabled
  fix  5680702 = enabled
  fix  5694984 = enabled
  fix  5705257 = enabled
  fix  5741121 = enabled
  fix  5762598 = enabled
  fix  5884780 = enabled
  fix  5949981 = enabled
  fix  5976822 = enabled
  fix  6122894 = enabled
Query Block Registry:
*********************
SEL$2 0x357ad9c0 (PARSER)
  SEL$5DA710D3 0x125c7d38 (SUBQUERY UNNEST SEL$1; SEL$2) [FINAL]
SEL$1 0x125c7d38 (PARSER)
  SEL$5DA710D3 0x125c7d38 (SUBQUERY UNNEST SEL$1; SEL$2) [FINAL]
Optimizer State Dump: call(in-use=73416, alloc=98184), compile(in-use=144656, alloc=192728)
Registered qb: MISC$1 0x7ac19910 (PARSER)
  signature(): NULL
**************************
Predicate Move-Around (PM)
**************************
PM: Considering predicate move-around in MISC$1 (#0).
PM:   Checking validity of predicate move-around in MISC$1 (#0).
CBQT: Validity checks failed for 3jxypd2xqf4r9.
CVM: Considering view merge in query block MISC$1 (#0)
CBQT: Validity checks failed for 3jxypd2xqf4r9.
***************
Subquery Unnest
***************
SU: Considering subquery unnesting in query block MISC$1 (#0)
*************************
Set-Join Conversion (SJC)
*************************
SJC: Considering set-join conversion in MISC$1 (#0).
**************************
Predicate Move-Around (PM)
**************************
PM: Considering predicate move-around in MISC$1 (#0).
PM:   Checking validity of predicate move-around in MISC$1 (#0).
PM:     PM bypassed: Outer query contains no views.
FPD: Considering simple filter push in MISC$1 (#0)
FPD:   Current where clause predicates in MISC$1 (#0) :
         apadrv-start: call(in-use=34704, alloc=35592), compile(in-use=33200, alloc=33840)

sql_id=3jxypd2xqf4r9.
Current SQL statement for this session:
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF'

============
Plan Table
============
---------------------------------+-----------------------------------+
| Id  | Operation      | Name    | Rows  | Bytes | Cost  | Time      |
---------------------------------+-----------------------------------+
| 0   | DDL STATEMENT  |         |       |       |     0 |           |
---------------------------------+-----------------------------------+
Predicate Information:
----------------------

Optimizer environment:
  optimizer_mode_hinted               = false
  optimizer_features_hinted           = 0.0.0
  parallel_execution_enabled          = true
  parallel_query_forced_dop           = 0
  parallel_dml_forced_dop             = 0
  parallel_ddl_forced_degree          = 0
  parallel_ddl_forced_instances       = 0
  _query_rewrite_fudge                = 90
  optimizer_features_enable           = 10.2.0.2
  _optimizer_search_limit             = 5
  cpu_count                           = 28
  active_instance_count               = 1
  parallel_threads_per_cpu            = 2
  hash_area_size                      = 131072
  bitmap_merge_area_size              = 1048576
  sort_area_size                      = 65536
  sort_area_retained_size             = 0
  _sort_elimination_cost_ratio        = 5
  _optimizer_block_size               = 8192
  _sort_multiblock_read_count         = 2
  _hash_multiblock_io_count           = 0
  _db_file_optimizer_read_count       = 8
  _optimizer_max_permutations         = 2000
  pga_aggregate_target                = 20971520 KB
  _pga_max_size                       = 2097152 KB
  _query_rewrite_maxdisjunct          = 257
  _smm_auto_min_io_size               = 56 KB
  _smm_auto_max_io_size               = 248 KB
  _smm_min_size                       = 1024 KB
  _smm_max_size                       = 1048576 KB
  _smm_px_max_size                    = 10485760 KB
  _cpu_to_io                          = 0
  _optimizer_undo_cost_change         = 10.2.0.2
  parallel_query_mode                 = enabled
  parallel_dml_mode                   = disabled
  parallel_ddl_mode                   = enabled
  optimizer_mode                      = all_rows
  sqlstat_enabled                     = false
  _optimizer_percent_parallel         = 101
  _always_anti_join                   = choose
  _always_semi_join                   = choose
  _optimizer_mode_force               = true
  _partition_view_enabled             = true
  _always_star_transformation         = false
  _query_rewrite_or_error             = false
  _hash_join_enabled                  = true
  cursor_sharing                      = exact
  _b_tree_bitmap_plans                = false
  star_transformation_enabled         = false
  _optimizer_cost_model               = choose
  _new_sort_cost_estimate             = true
  _complex_view_merging               = true
  _unnest_subquery                    = true
  _eliminate_common_subexpr           = true
  _pred_move_around                   = true
  _convert_set_to_join                = false
  _push_join_predicate                = true
  _push_join_union_view               = true
  _fast_full_scan_enabled             = false
  _optim_enhance_nnull_detection      = true
  _parallel_broadcast_enabled         = true
  _px_broadcast_fudge_factor          = 100
  _ordered_nested_loop                = true
  _no_or_expansion                    = false
  optimizer_index_cost_adj            = 100
  optimizer_index_caching             = 0
  _system_index_caching               = 0
  _disable_datalayer_sampling         = false
  query_rewrite_enabled               = true
  query_rewrite_integrity             = enforced
  _query_cost_rewrite                 = true
  _query_rewrite_2                    = true
  _query_rewrite_1                    = true
  _query_rewrite_expression           = true
  _query_rewrite_jgmigrate            = true
  _query_rewrite_fpc                  = true
  _query_rewrite_drj                  = true
  _full_pwise_join_enabled            = true
  _partial_pwise_join_enabled         = true
  _left_nested_loops_random           = true
  _improved_row_length_enabled        = true
  _index_join_enabled                 = true
  _enable_type_dep_selectivity        = true
  _improved_outerjoin_card            = true
  _optimizer_adjust_for_nulls         = true
  _optimizer_degree                   = 0
  _use_column_stats_for_function      = true
  _subquery_pruning_enabled           = true
  _subquery_pruning_mv_enabled        = false
  _or_expand_nvl_predicate            = true
  _like_with_bind_as_equality         = true
  _table_scan_cost_plus_one           = true
  _cost_equality_semi_join            = true
  _default_non_equality_sel_check     = true
  _new_initial_join_orders            = true
  _oneside_colstat_for_equijoins      = true
  _optim_peek_user_binds              = true
  _minimal_stats_aggregation          = true
  _force_temptables_for_gsets         = false
  workarea_size_policy                = auto
  _smm_auto_cost_enabled              = true
  _gs_anti_semi_join_allowed          = true
  _optim_new_default_join_sel         = true
  optimizer_dynamic_sampling          = 2
  _pre_rewrite_push_pred              = true
  _optimizer_new_join_card_computation = true
  _union_rewrite_for_gs               = yes_gset_mvs
  _generalized_pruning_enabled        = true
  _optim_adjust_for_part_skews        = true
  _force_datefold_trunc               = false
  statistics_level                    = typical
  _optimizer_system_stats_usage       = true
  skip_unusable_indexes               = true
  _remove_aggr_subquery               = true
  _optimizer_push_down_distinct       = 0
  _dml_monitoring_enabled             = true
  _optimizer_undo_changes             = false
  _predicate_elimination_enabled      = true
  _nested_loop_fudge                  = 100
  _project_view_columns               = true
  _local_communication_costing_enabled = true
  _local_communication_ratio          = 50
  _query_rewrite_vop_cleanup          = true
  _slave_mapping_enabled              = true
  _optimizer_cost_based_transformation = linear
  _optimizer_mjc_enabled              = true
  _right_outer_hash_enable            = true
  _spr_push_pred_refspr               = true
  _optimizer_cache_stats              = false
  _optimizer_cbqt_factor              = 50
  _optimizer_squ_bottomup             = true
  _fic_area_size                      = 131072
  _optimizer_skip_scan_enabled        = true
  _optimizer_cost_filter_pred         = false
  _optimizer_sortmerge_join_enabled   = true
  _optimizer_join_sel_sanity_check    = true
  _mmv_query_rewrite_enabled          = true
  _bt_mmv_query_rewrite_enabled       = true
  _add_stale_mv_to_dependency_list    = true
  _distinct_view_unnesting            = false
  _optimizer_dim_subq_join_sel        = true
  _optimizer_disable_strans_sanity_checks = 0
  _optimizer_compute_index_stats      = true
  _push_join_union_view2              = true
  _optimizer_ignore_hints             = false
  _optimizer_random_plan              = 0
  _query_rewrite_setopgrw_enable      = true
  _optimizer_correct_sq_selectivity   = true
  _disable_function_based_index       = false
  _optimizer_join_order_control       = 3
  _optimizer_cartesian_enabled        = true
  _optimizer_starplan_enabled         = true
  _extended_pruning_enabled           = true
  _optimizer_push_pred_cost_based     = true
  _sql_model_unfold_forloops          = run_time
  _enable_dml_lock_escalation         = false
  _bloom_filter_enabled               = true
  _update_bji_ipdml_enabled           = 0
  _optimizer_extended_cursor_sharing  = udo
  _dm_max_shared_pool_pct             = 1
  _optimizer_cost_hjsmj_multimatch    = true
  _optimizer_transitivity_retain      = true
  _px_pwg_enabled                     = true
  optimizer_secure_view_merging       = false
  _optimizer_join_elimination_enabled = true
  flashback_table_rpi                 = non_fbt
  _optimizer_cbqt_no_size_restriction = true
  _optimizer_enhanced_filter_push     = true
  _optimizer_filter_pred_pullup       = true
  _rowsrc_trace_level                 = 0
  _simple_view_merging                = true
  _optimizer_rownum_pred_based_fkr    = true
  _optimizer_better_inlist_costing    = all
  _optimizer_self_induced_cache_cost  = false
  _optimizer_min_cache_blocks         = 10
  _optimizer_or_expansion             = depth
  _optimizer_order_by_elimination_enabled = true
  _optimizer_outer_to_anti_enabled    = true
  _selfjoin_mv_duplicates             = true
  _dimension_skip_null                = true
  _force_rewrite_enable               = false
  _optimizer_star_tran_in_with_clause = true
  _optimizer_complex_pred_selectivity = true
  _optimizer_connect_by_cost_based    = true
  _gby_hash_aggregation_enabled       = true
  _globalindex_pnum_filter_enabled    = true
  _fix_control_key                    = 0
  _optimizer_skip_scan_guess          = false
  _enable_row_shipping                = false
  *********************************
  Bug Fix Control Environment
  ***************************
  fix  4611850 = enabled
  fix  4663804 = enabled
  fix  4663698 = enabled
  fix  4545833 = enabled
  fix  3499674 = disabled
  fix  4584065 = enabled
  fix  4602374 = enabled
  fix  4569940 = enabled
  fix  4631959 = enabled
  fix  4519340 = enabled
  fix  4550003 = enabled
  fix  4488689 = enabled
  fix  3118776 = enabled
  fix  4519016 = enabled
  fix  4487253 = enabled
  fix  4556762 = 15
  fix  4728348 = enabled
  fix  4723244 = enabled
  fix  4554846 = enabled
  fix  2492766 = enabled
  fix  4175830 = enabled
  fix  4722900 = enabled
  fix  4711525 = enabled
  fix  4273361 = enabled
  fix  4483240 = enabled
  fix  4904838 = enabled
  fix  5084239 = enabled
  fix  5838613 = enabled
  fix  5385629 = enabled
  fix  5449488 = enabled
  fix  5483301 = enabled
  fix  5680702 = enabled
  fix  5694984 = enabled
  fix  5705257 = enabled
  fix  5741121 = enabled
  fix  5762598 = enabled
  fix  5884780 = enabled
  fix  5949981 = enabled
  fix  5976822 = enabled
  fix  6122894 = enabled
Query Block Registry:
*********************
MISC$1 0x7ac19910 (PARSER) [FINAL]
Optimizer State Dump: call(in-use=97816, alloc=97816), compile(in-use=43208, alloc=94112)