sorry,好像那个执行计划上传的时候出了点问题,请看新附件。
[code class=\"\"]DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2009
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool
******************** EXPLAIN INSTANCE ********************
DB2_VERSION: 09.07.B
SOURCE_NAME: SQLC2H23
SOURCE_SCHEMA: NULLID
SOURCE_VERSION:
EXPLAIN_TIME: 2017-03-24-10.02.33.982759
EXPLAIN_REQUESTER: DB2INST1
Database Context:
----------------
Parallelism: None
CPU Speed: 4.211736e-07
Comm Speed: 100
Buffer Pool size: 7000
Sort Heap size: 296
Database Heap size: 2672
Lock List size: 23312
Maximum Lock List: 97
Average Applications: 1
Locks Available: 723604
Package Context:
---------------
SQL Type: Dynamic
Optimization Level: 5
Blocking: Block All Cursors
Isolation Level: Cursor Stability
---------------- STATEMENT 1 SECTION 201 ----------------
QUERYNO: 1
QUERYTAG: CLP
Statement Type: Select
Updatable: No
Deletable: No
Query Degree: 1
Original Statement:
------------------
SELECT a.cnum, b.emp_email_addr_txt, b.emp_name, a.rpt_family_id, a.dim_id,
a.dim_src_id, a.dim_lvl_id,
CASE a.dim_lvl_val_id
WHEN \'GBSAdminNA\'
THEN \'GBS Admin\'
WHEN \'GBSAdminEU\'
THEN \'GBS Admin\'
WHEN \'GBSAdminGMU\'
THEN \'GBS Admin\'
WHEN \'ITSAdminEU\'
THEN \'ITS Admin\'
WHEN \'ITSAdminGMU\'
THEN \'ITS Admin\'
WHEN \'ITSAdminNA\'
THEN \'ITS Admin\'
WHEN \'SWGSAdminEU\'
THEN \'SWGS Admin\'
WHEN \'SWGSAdminGMU\'
THEN \'SWGS Admin\'
WHEN \'SWGSAdminNA\'
THEN \'SWGS Admin\'
ELSE a.dim_lvl_val_id END
CASE, a.authorized_by_id, a.authorized_ts, a.last_updt_ts, c.rpt_fam_req_id,
c.req_ad_hoc, c.req_ad_hoc_mvsid, c.req_ad_hoc_appr_ts
FROM entitle.dim_auth a left join
(SELECT Max(rpt_fam_req_id) rpt_fam_req_id, rpt_family_id, req_cnum,
max(req_ad_hoc) req_ad_hoc, max(req_ad_hoc_mvsid) req_ad_hoc_mvsid,
max(req_ad_hoc_appr_ts) req_ad_hoc_appr_ts
FROM entitle.rpt_fam_acc_req
WHERE req_fin_ts IS NOT NULL
GROUP BY rpt_family_id, req_cnum) c on a.cnum = c.req_cnum AND
a.rpt_family_id = c.rpt_family_id, usercntl.user_profile b,
entitle.sa_customer d, entitle.sa_rpt_fam_mapping e,
entitle.user_sa_mapping f
WHERE a.cnum = b.cnum AND d.customer = \'SDM\' AND f.user_id = CURRENT USER AND
f.customer = d.customer AND e.customer = d.customer AND
c.rpt_family_id = e.rpt_family_id
Optimized Statement:
-------------------
SELECT Q2.CNUM AS \"CNUM\", Q3.EMP_EMAIL_ADDR_TXT AS \"EMP_EMAIL_ADDR_TXT\",
Q3.EMP_NAME AS \"EMP_NAME\", Q2.RPT_FAMILY_ID AS \"RPT_FAMILY_ID\",
Q2.DIM_ID AS \"DIM_ID\", Q2.DIM_SRC_ID AS \"DIM_SRC_ID\", Q2.DIM_LVL_ID
AS \"DIM_LVL_ID\",
CASE
WHEN (Q2.DIM_LVL_VAL_ID = \'GBSAdminNA \')
THEN \'GBS Admin\'
WHEN (Q2.DIM_LVL_VAL_ID = \'GBSAdminEU \')
THEN \'GBS Admin\'
WHEN (Q2.DIM_LVL_VAL_ID = \'GBSAdminGMU \')
THEN \'GBS Admin\'
WHEN (Q2.DIM_LVL_VAL_ID = \'ITSAdminEU \')
THEN \'ITS Admin\'
WHEN (Q2.DIM_LVL_VAL_ID = \'ITSAdminGMU \')
THEN \'ITS Admin\'
WHEN (Q2.DIM_LVL_VAL_ID = \'ITSAdminNA \')
THEN \'ITS Admin\'
WHEN (Q2.DIM_LVL_VAL_ID = \'SWGSAdminEU \')
THEN \'SWGS Admin\'
WHEN (Q2.DIM_LVL_VAL_ID = \'SWGSAdminGMU \')
THEN \'SWGS Admin\'
WHEN (Q2.DIM_LVL_VAL_ID = \'SWGSAdminNA \')
THEN \'SWGS Admin\'
ELSE $INTERNAL_FUNC$() END AS \"CASE\", Q2.AUTHORIZED_BY_ID AS
\"AUTHORIZED_BY_ID\", Q2.AUTHORIZED_TS AS \"AUTHORIZED_TS\",
Q2.LAST_UPDT_TS AS \"LAST_UPDT_TS\", Q6.$C0 AS \"RPT_FAM_REQ_ID\", Q6.$C3
AS \"REQ_AD_HOC\", Q6.$C4 AS \"REQ_AD_HOC_MVSID\", Q6.$C5 AS
\"REQ_AD_HOC_APPR_TS\"
FROM ENTITLE.USER_SA_MAPPING AS Q1, ENTITLE.DIM_AUTH AS Q2,
USERCNTL.USER_PROFILE AS Q3,
(SELECT MAX(Q5.RPT_FAM_REQ_ID), Q5.RPT_FAMILY_ID, Q5.REQ_CNUM,
MAX(Q5.REQ_AD_HOC), MAX(Q5.REQ_AD_HOC_MVSID),
MAX(Q5.REQ_AD_HOC_APPR_TS)
FROM
(SELECT Q4.RPT_FAMILY_ID, Q4.REQ_CNUM, Q4.RPT_FAM_REQ_ID, Q4.REQ_AD_HOC,
Q4.REQ_AD_HOC_MVSID, Q4.REQ_AD_HOC_APPR_TS
FROM ENTITLE.RPT_FAM_ACC_REQ AS Q4
WHERE Q4.REQ_FIN_TS IS NOT NULL) AS Q5
GROUP BY Q5.REQ_CNUM, Q5.RPT_FAMILY_ID) AS Q6, ENTITLE.SA_RPT_FAM_MAPPING
AS Q7
WHERE (\'SDM \' = Q1.CUSTOMER) AND (CURRENT_USER =
Q1.USER_ID) AND (Q3.CNUM = Q2.CNUM) AND (Q2.CNUM = Q6.REQ_CNUM) AND
(Q2.RPT_FAMILY_ID = Q6.RPT_FAMILY_ID) AND (Q6.RPT_FAMILY_ID =
Q7.RPT_FAMILY_ID) AND (Q7.CUSTOMER = \'SDM \')
Access Plan:
-----------
Total Cost: 341561
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
782.335
NLJOIN
( 2)
341561
31019.5
/----------+-----------\\
1 782.335
IXSCAN HSJOIN
( 3) ( 5)
25.0236 341536
1 31018.5
| /-----------+-----------\\
182 83869 782.335
INDEX: ENTITLE TBSCAN ^MSJOIN
PK_USAMAP ( 6) ( 7)
Q1 42361.7 299165
5049 25969.5
| /------------+------------\\
83869 98448.4 0.00794665
TABLE: USERCNTL NLJOIN FILTER
USER_PROFILE ( 8) ( 12)
Q3 120739 118797
12606.5 12831
/------+-------\\ |
14 7032.03 189650
IXSCAN FETCH GRPBY
( 9) ( 10) ( 13)
25.0337 7803.07 118761
1 814.704 12831
| /---+----\\ |
132 7032.03 1.75801e+06 189650
INDEX: ENTITLE IXSCAN TABLE: ENTITLE TBSCAN
PK_SARFMAP ( 11) DIM_AUTH ( 14)
Q7 575.143 Q2 118741
22.796 12831
| |
1.75801e+06 189650
INDEX: ENTITLE SORT
IX_RPT_FAMILY_ID ( 15)
Q2 111446
11016
|
189650
TBSCAN
( 16)
65696.4
9201
|
190814
TABLE: ENTITLE
RPT_FAM_ACC_REQ
Q4
Extended Diagnostic Information:
--------------------------------
No extended Diagnostic Information for this statement.
Plan Details:
-------------
1) RETURN: (Return Result)
Cumulative Total Cost: 341561
Cumulative CPU Cost: 1.12504e+11
Cumulative I/O Cost: 31019.5
Cumulative Re-Total Cost: 47339.4
Cumulative Re-CPU Cost: 1.12399e+11
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 341561
Estimated Bufferpool Buffers: 5051
Arguments:
---------
BLDLEVEL: (Build level)
DB2 v9.7.0.11 : special_35740
HEAPUSE : (Maximum Statement Heap Usage)
272 Pages
PREPTIME: (Statement prepare time)
186 milliseconds
STMTHEAP: (Statement heap size)
2048
Input Streams:
-------------
20) From Operator #2
Estimated number of rows: 782.335
Number of columns: 15
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q8.REQ_AD_HOC_APPR_TS+Q8.REQ_AD_HOC_MVSID
+Q8.REQ_AD_HOC+Q8.RPT_FAM_REQ_ID
+Q8.LAST_UPDT_TS+Q8.AUTHORIZED_TS
+Q8.AUTHORIZED_BY_ID+Q8.CASE+Q8.DIM_LVL_ID
+Q8.DIM_SRC_ID+Q8.DIM_ID+Q8.RPT_FAMILY_ID
+Q8.EMP_NAME+Q8.EMP_EMAIL_ADDR_TXT+Q8.CNUM
2) NLJOIN: (Nested Loop Join)
Cumulative Total Cost: 341561
Cumulative CPU Cost: 1.12504e+11
Cumulative I/O Cost: 31019.5
Cumulative Re-Total Cost: 47339.2
Cumulative Re-CPU Cost: 1.12398e+11
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 341561
Estimated Bufferpool Buffers: 5051
Arguments:
---------
EARLYOUT: (Early Out flag)
NONE
FETCHMAX: (Override for FETCH MAXPAGES)
IGNORE
ISCANMAX: (Override for ISCAN MAXPAGES)
IGNORE
Predicates:
----------
2) Predicate used in Join,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 1
Predicate Text:
--------------
(Q1.CUSTOMER = Q7.CUSTOMER)
Input Streams:
-------------
2) From Operator #3
Estimated number of rows: 1
Number of columns: 1
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q1.CUSTOMER
19) From Operator #5
Estimated number of rows: 782.335
Number of columns: 18
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2.LAST_UPDT_TS+Q2.AUTHORIZED_TS
+Q2.AUTHORIZED_BY_ID+Q2.DIM_LVL_VAL_ID
+Q2.DIM_LVL_ID+Q2.DIM_SRC_ID+Q2.DIM_ID
+Q2.RPT_FAMILY_ID+Q2.CNUM+Q3.EMP_NAME
+Q3.EMP_EMAIL_ADDR_TXT+Q3.CNUM+Q6.$C5+Q6.$C4
+Q6.$C3+Q6.REQ_CNUM+Q6.$C0+Q7.CUSTOMER
Output Streams:
--------------
20) To Operator #1
Estimated number of rows: 782.335
Number of columns: 15
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q8.REQ_AD_HOC_APPR_TS+Q8.REQ_AD_HOC_MVSID
+Q8.REQ_AD_HOC+Q8.RPT_FAM_REQ_ID
+Q8.LAST_UPDT_TS+Q8.AUTHORIZED_TS
+Q8.AUTHORIZED_BY_ID+Q8.CASE+Q8.DIM_LVL_ID
+Q8.DIM_SRC_ID+Q8.DIM_ID+Q8.RPT_FAMILY_ID
+Q8.EMP_NAME+Q8.EMP_EMAIL_ADDR_TXT+Q8.CNUM
3) IXSCAN: (Index Scan)
Cumulative Total Cost: 25.0236
Cumulative CPU Cost: 56108.7
Cumulative I/O Cost: 1
Cumulative Re-Total Cost: 0.00322101
Cumulative Re-CPU Cost: 7647.7
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 25.0236
Estimated Bufferpool Buffers: 2
Arguments:
---------
JN INPUT: (Join input leg)
OUTER
LCKAVOID: (Lock Avoidance)
TRUE
MAXPAGES: (Maximum pages for prefetch)
1
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
SHARE (CS/RS)
SCANDIR : (Scan Direction)
FORWARD
TABLOCK : (Table Lock intent)
INTENT SHARE
TBISOLVL: (Table access Isolation Level)
CURSOR STABILITY
Predicates:
----------
5) Start Key Predicate,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.0659341
Predicate Text:
--------------
(\'SDM \' = Q1.CUSTOMER)
5) Stop Key Predicate,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.0659341
Predicate Text:
--------------
(\'SDM \' = Q1.CUSTOMER)
6) Start Key Predicate,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.02
Predicate Text:
--------------
(CURRENT_USER = Q1.USER_ID)
6) Stop Key Predicate,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.02
Predicate Text:
--------------
(CURRENT_USER = Q1.USER_ID)
Input Streams:
-------------
1) From Object ENTITLE.PK_USAMAP
Estimated number of rows: 182
Number of columns: 3
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q1.$RID$+Q1.CUSTOMER+Q1.USER_ID
Output Streams:
--------------
2) To Operator #2
Estimated number of rows: 1
Number of columns: 1
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q1.CUSTOMER
5) HSJOIN: (Hash Join)
Cumulative Total Cost: 341536
Cumulative CPU Cost: 1.12503e+11
Cumulative I/O Cost: 31018.5
Cumulative Re-Total Cost: 341536
Cumulative Re-CPU Cost: 1.12503e+11
Cumulative Re-I/O Cost: 31018.5
Cumulative First Row Cost: 341536
Estimated Bufferpool Buffers: 5049
Arguments:
---------
BITFLTR : (Hash Join Bit Filter used)
800
EARLYOUT: (Early Out flag)
NONE
HASHCODE: (Hash Code Size)
24 BIT
HASHTBSZ: (Number of hash table entries)
782
JN INPUT: (Join input leg)
INNER
TEMPSIZE: (Temporary Table Page Size)
4096
TUPBLKSZ: (Tuple Block Size (bytes))
4000
Predicates:
----------
4) Predicate used in Join,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 1.05592e-05
Predicate Text:
--------------
(Q6.REQ_CNUM = Q3.CNUM)
Input Streams:
-------------
4) From Operator #6
Estimated number of rows: 83869
Number of columns: 3
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q3.EMP_NAME+Q3.EMP_EMAIL_ADDR_TXT+Q3.CNUM
18) From Operator #7
Estimated number of rows: 782.335
Number of columns: 17
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2.LAST_UPDT_TS+Q2.AUTHORIZED_TS
+Q2.AUTHORIZED_BY_ID+Q2.DIM_LVL_VAL_ID
+Q2.DIM_LVL_ID+Q2.DIM_SRC_ID+Q2.DIM_ID
+Q2.RPT_FAMILY_ID+Q2.CNUM+Q6.$C5+Q6.$C4+Q6.$C3
+Q6.REQ_CNUM+Q6.RPT_FAMILY_ID+Q6.$C0
+Q7.RPT_FAMILY_ID+Q7.CUSTOMER
Output Streams:
--------------
19) To Operator #2
Estimated number of rows: 782.335
Number of columns: 18
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2.LAST_UPDT_TS+Q2.AUTHORIZED_TS
+Q2.AUTHORIZED_BY_ID+Q2.DIM_LVL_VAL_ID
+Q2.DIM_LVL_ID+Q2.DIM_SRC_ID+Q2.DIM_ID
+Q2.RPT_FAMILY_ID+Q2.CNUM+Q3.EMP_NAME
+Q3.EMP_EMAIL_ADDR_TXT+Q3.CNUM+Q6.$C5+Q6.$C4
+Q6.$C3+Q6.REQ_CNUM+Q6.$C0+Q7.CUSTOMER
6) TBSCAN: (Table Scan)
Cumulative Total Cost: 42361.7
Cumulative CPU Cost: 1.82936e+08
Cumulative I/O Cost: 5049
Cumulative Re-Total Cost: 60.8276
Cumulative Re-CPU Cost: 1.44424e+08
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 25.0257
Estimated Bufferpool Buffers: 5049
Arguments:
---------
JN INPUT: (Join input leg)
OUTER
LCKAVOID: (Lock Avoidance)
TRUE
MAXPAGES: (Maximum pages for prefetch)
ALL
PREFETCH: (Type of Prefetch)
SEQUENTIAL
ROWLOCK : (Row Lock intent)
SHARE (CS/RS)
SCANDIR : (Scan Direction)
FORWARD
SPEED : (Assumed speed of scan, in sharing structures)
FAST
TABLOCK : (Table Lock intent)
INTENT SHARE
TBISOLVL: (Table access Isolation Level)
CURSOR STABILITY
THROTTLE: (Scan may be throttled, for scan sharing)
TRUE
VISIBLE : (May be included in scan sharing structures)
TRUE
WRAPPING: (Scan may start anywhere and wrap)
TRUE
Input Streams:
-------------
3) From Object USERCNTL.USER_PROFILE
Estimated number of rows: 83869
Number of columns: 4
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q3.$RID$+Q3.EMP_NAME+Q3.EMP_EMAIL_ADDR_TXT
+Q3.CNUM
Output Streams:
--------------
4) To Operator #5
Estimated number of rows: 83869
Number of columns: 3
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q3.EMP_NAME+Q3.EMP_EMAIL_ADDR_TXT+Q3.CNUM
7) MSJOIN: (Merge Scan Join)
Cumulative Total Cost: 299165
Cumulative CPU Cost: 1.12299e+11
Cumulative I/O Cost: 25969.5
Cumulative Re-Total Cost: 299165
Cumulative Re-CPU Cost: 1.12299e+11
Cumulative Re-I/O Cost: 25969.5
Cumulative First Row Cost: 112227
Estimated Bufferpool Buffers: 14010.9
Arguments:
---------
EARLYOUT: (Early Out flag)
LEFT
INNERCOL: (Inner Order Columns)
1: Q6.RPT_FAMILY_ID(A)
JN INPUT: (Join input leg)
INNER
OUTERCOL: (Outer Order columns)
1: Q2.RPT_FAMILY_ID(A)
TEMPSIZE: (Temporary Table Page Size)
4096
Predicates:
----------
8) Residual Predicate,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 1.05592e-05
Predicate Text:
--------------
(Q2.CNUM = Q6.REQ_CNUM)
9) Predicate used in Join,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.00396825
Predicate Text:
--------------
(Q2.RPT_FAMILY_ID = Q6.RPT_FAMILY_ID)
Input Streams:
-------------
11) From Operator #8
Estimated number of rows: 98448.4
Number of columns: 11
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q7.RPT_FAMILY_ID(A)+Q2.LAST_UPDT_TS
+Q2.AUTHORIZED_TS+Q2.AUTHORIZED_BY_ID
+Q2.DIM_LVL_VAL_ID+Q2.DIM_LVL_ID+Q2.DIM_SRC_ID
+Q2.DIM_ID+Q2.RPT_FAMILY_ID+Q2.CNUM+Q7.CUSTOMER
17) From Operator #12
Estimated number of rows: 0.00794665
Number of columns: 6
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q6.RPT_FAMILY_ID(A)+Q6.REQ_CNUM(A)+Q6.$C5
+Q6.$C4+Q6.$C3+Q6.$C0
Output Streams:
--------------
18) To Operator #5
Estimated number of rows: 782.335
Number of columns: 17
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2.LAST_UPDT_TS+Q2.AUTHORIZED_TS
+Q2.AUTHORIZED_BY_ID+Q2.DIM_LVL_VAL_ID
+Q2.DIM_LVL_ID+Q2.DIM_SRC_ID+Q2.DIM_ID
+Q2.RPT_FAMILY_ID+Q2.CNUM+Q6.$C5+Q6.$C4+Q6.$C3
+Q6.REQ_CNUM+Q6.RPT_FAMILY_ID+Q6.$C0
+Q7.RPT_FAMILY_ID+Q7.CUSTOMER
8) NLJOIN: (Nested Loop Join)
Cumulative Total Cost: 120739
Cumulative CPU Cost: 3.42789e+08
Cumulative I/O Cost: 12606.5
Cumulative Re-Total Cost: 29305.4
Cumulative Re-CPU Cost: 3.12199e+08
Cumulative Re-I/O Cost: 3904.04
Cumulative First Row Cost: 100.061
Estimated Bufferpool Buffers: 11407.9
Arguments:
---------
EARLYOUT: (Early Out flag)
NONE
FETCHMAX: (Override for FETCH MAXPAGES)
IGNORE
ISCANMAX: (Override for ISCAN MAXPAGES)
IGNORE
JN INPUT: (Join input leg)
OUTER
Predicates:
----------
3) Predicate used in Join,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.004
Predicate Text:
--------------
(Q7.RPT_FAMILY_ID = Q2.RPT_FAMILY_ID)
Input Streams:
-------------
6) From Operator #9
Estimated number of rows: 14
Number of columns: 2
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q7.RPT_FAMILY_ID(A)+Q7.CUSTOMER
10) From Operator #10
Estimated number of rows: 7032.03
Number of columns: 9
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2.RPT_FAMILY_ID(A)+Q2.LAST_UPDT_TS
+Q2.AUTHORIZED_TS+Q2.AUTHORIZED_BY_ID
+Q2.DIM_LVL_VAL_ID+Q2.DIM_LVL_ID+Q2.DIM_SRC_ID
+Q2.DIM_ID+Q2.CNUM
Output Streams:
--------------
11) To Operator #7
Estimated number of rows: 98448.4
Number of columns: 11
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q7.RPT_FAMILY_ID(A)+Q2.LAST_UPDT_TS
+Q2.AUTHORIZED_TS+Q2.AUTHORIZED_BY_ID
+Q2.DIM_LVL_VAL_ID+Q2.DIM_LVL_ID+Q2.DIM_SRC_ID
+Q2.DIM_ID+Q2.RPT_FAMILY_ID+Q2.CNUM+Q7.CUSTOMER
9) IXSCAN: (Index Scan)
Cumulative Total Cost: 25.0337
Cumulative CPU Cost: 80032.5
Cumulative I/O Cost: 1
Cumulative Re-Total Cost: 0.0132971
Cumulative Re-CPU Cost: 31571.5
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 25.0236
Estimated Bufferpool Buffers: 2
Arguments:
---------
JN INPUT: (Join input leg)
OUTER
LCKAVOID: (Lock Avoidance)
TRUE
MAXPAGES: (Maximum pages for prefetch)
1
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
SHARE (CS/RS)
SCANDIR : (Scan Direction)
FORWARD
TABLOCK : (Table Lock intent)
INTENT SHARE
TBISOLVL: (Table access Isolation Level)
CURSOR STABILITY
Predicates:
----------
11) Start Key Predicate,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.106061
Predicate Text:
--------------
(Q7.CUSTOMER = \'SDM \')
11) Stop Key Predicate,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.106061
Predicate Text:
--------------
(Q7.CUSTOMER = \'SDM \')
Input Streams:
-------------
5) From Object ENTITLE.PK_SARFMAP
Estimated number of rows: 132
Number of columns: 3
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q7.RPT_FAMILY_ID(A)+Q7.$RID$+Q7.CUSTOMER
Output Streams:
--------------
6) To Operator #8
Estimated number of rows: 14
Number of columns: 2
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q7.RPT_FAMILY_ID(A)+Q7.CUSTOMER
10) FETCH : (Fetch)
Cumulative Total Cost: 7803.07
Cumulative CPU Cost: 2.50766e+07
Cumulative I/O Cost: 814.704
Cumulative Re-Total Cost: 5402.23
Cumulative Re-CPU Cost: 2.38029e+07
Cumulative Re-I/O Cost: 721.584
Cumulative First Row Cost: 75.0374
Estimated Bufferpool Buffers: 69508.6
Arguments:
---------
JN INPUT: (Join input leg)
INNER
LCKAVOID: (Lock Avoidance)
TRUE
MAXPAGES: (Maximum pages for prefetch)
699
PREFETCH: (Type of Prefetch)
SEQUENTIAL
ROWLOCK : (Row Lock intent)
SHARE (CS/RS)
SPEED : (Assumed speed of scan, in sharing structures)
SLOW
TABLOCK : (Table Lock intent)
INTENT SHARE
TBISOLVL: (Table access Isolation Level)
CURSOR STABILITY
THROTTLE: (Scan may be throttled, for scan sharing)
FALSE
VISIBLE : (May be included in scan sharing structures)
FALSE
WRAPPING: (Scan may start anywhere and wrap)
FALSE
Input Streams:
-------------
8) From Operator #11
Estimated number of rows: 7032.03
Number of columns: 2
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2.RPT_FAMILY_ID(A)+Q2.$RID$
9) From Object ENTITLE.DIM_AUTH
Estimated number of rows: 1.75801e+06
Number of columns: 8
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2.LAST_UPDT_TS+Q2.AUTHORIZED_TS
+Q2.AUTHORIZED_BY_ID+Q2.DIM_LVL_VAL_ID
+Q2.DIM_LVL_ID+Q2.DIM_SRC_ID+Q2.DIM_ID+Q2.CNUM
Output Streams:
--------------
10) To Operator #8
Estimated number of rows: 7032.03
Number of columns: 9
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2.RPT_FAMILY_ID(A)+Q2.LAST_UPDT_TS
+Q2.AUTHORIZED_TS+Q2.AUTHORIZED_BY_ID
+Q2.DIM_LVL_VAL_ID+Q2.DIM_LVL_ID+Q2.DIM_SRC_ID
+Q2.DIM_ID+Q2.CNUM
11) IXSCAN: (Index Scan)
Cumulative Total Cost: 575.143
Cumulative CPU Cost: 1.24474e+07
Cumulative I/O Cost: 22.796
Cumulative Re-Total Cost: 550.122
Cumulative Re-CPU Cost: 1.23989e+07
Cumulative Re-I/O Cost: 21.796
Cumulative First Row Cost: 50.0316
Estimated Bufferpool Buffers: 5316.49
Arguments:
---------
LCKAVOID: (Lock Avoidance)
TRUE
MAXPAGES: (Maximum pages for prefetch)
21
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
SHARE (CS/RS)
SCANDIR : (Scan Direction)
FORWARD
TABLOCK : (Table Lock intent)
INTENT SHARE
TBISOLVL: (Table access Isolation Level)
CURSOR STABILITY
Predicates:
----------
3) Start Key Predicate,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.004
Predicate Text:
--------------
(Q7.RPT_FAMILY_ID = Q2.RPT_FAMILY_ID)
3) Stop Key Predicate,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.004
Predicate Text:
--------------
(Q7.RPT_FAMILY_ID = Q2.RPT_FAMILY_ID)
Input Streams:
-------------
7) From Object ENTITLE.IX_RPT_FAMILY_ID
Estimated number of rows: 1.75801e+06
Number of columns: 2
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2.RPT_FAMILY_ID(A)+Q2.$RID$
Output Streams:
--------------
8) To Operator #10
Estimated number of rows: 7032.03
Number of columns: 2
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q2.RPT_FAMILY_ID(A)+Q2.$RID$
12) FILTER: (Filter)
Cumulative Total Cost: 118797
Cumulative CPU Cost: 1.95444e+09
Cumulative I/O Cost: 12831
Cumulative Re-Total Cost: 7351.06
Cumulative Re-CPU Cost: 5.86115e+08
Cumulative Re-I/O Cost: 1815
Cumulative First Row Cost: 111658
Estimated Bufferpool Buffers: 2598
Arguments:
---------
JN INPUT: (Join input leg)
INNER
Predicates:
----------
8) Residual Predicate,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 1.05592e-05
Predicate Text:
--------------
(Q2.CNUM = Q6.REQ_CNUM)
9) Residual Predicate,
Comparison Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.00396825
Predicate Text:
--------------
(Q2.RPT_FAMILY_ID = Q6.RPT_FAMILY_ID)
Input Streams:
-------------
16) From Operator #13
Estimated number of rows: 189650
Number of columns: 6
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q6.RPT_FAMILY_ID(A)+Q6.REQ_CNUM(A)+Q6.$C5
+Q6.$C4+Q6.$C3+Q6.$C0
Output Streams:
--------------
17) To Operator #7
Estimated number of rows: 0.00794665
Number of columns: 6
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q6.RPT_FAMILY_ID(A)+Q6.REQ_CNUM(A)+Q6.$C5
+Q6.$C4+Q6.$C3+Q6.$C0
13) GRPBY : (Group By)
Cumulative Total Cost: 118761
Cumulative CPU Cost: 1.87004e+09
Cumulative I/O Cost: 12831
Cumulative Re-Total Cost: 7315.51
Cumulative Re-CPU Cost: 5.0172e+08
Cumulative Re-I/O Cost: 1815
Cumulative First Row Cost: 111658
Estimated Bufferpool Buffers: 2598
Arguments:
---------
AGGMODE : (Aggregation Mode)
COMPLETE
GROUPBYC: (Group By columns)
TRUE
GROUPBYN: (Number of Group By columns)
2
GROUPBYR: (Group By requirement)
1: Q5.RPT_FAMILY_ID
2: Q5.REQ_CNUM
ONEFETCH: (One Fetch flag)
FALSE
Input Streams:
-------------
15) From Operator #14
Estimated number of rows: 189650
Number of columns: 6
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q5.RPT_FAMILY_ID(A)+Q5.REQ_CNUM(A)
+Q5.REQ_AD_HOC_APPR_TS+Q5.REQ_AD_HOC_MVSID
+Q5.REQ_AD_HOC+Q5.RPT_FAM_REQ_ID
Output Streams:
--------------
16) To Operator #12
Estimated number of rows: 189650
Number of columns: 6
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q6.RPT_FAMILY_ID(A)+Q6.REQ_CNUM(A)+Q6.$C5
+Q6.$C4+Q6.$C3+Q6.$C0
14) TBSCAN: (Table Scan)
Cumulative Total Cost: 118741
Cumulative CPU Cost: 1.82263e+09
Cumulative I/O Cost: 12831
Cumulative Re-Total Cost: 7295.54
Cumulative Re-CPU Cost: 4.54307e+08
Cumulative Re-I/O Cost: 1815
Cumulative First Row Cost: 111658
Estimated Bufferpool Buffers: 2598
Arguments:
---------
MAXPAGES: (Maximum pages for prefetch)
ALL
PREFETCH: (Type of Prefetch)
SEQUENTIAL
SCANDIR : (Scan Direction)
FORWARD
SPEED : (Assumed speed of scan, in sharing structures)
SLOW
THROTTLE: (Scan may be throttled, for scan sharing)
FALSE
VISIBLE : (May be included in scan sharing structures)
FALSE
WRAPPING: (Scan may start anywhere and wrap)
FALSE
Input Streams:
-------------
14) From Operator #15
Estimated number of rows: 189650
Number of columns: 6
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q5.RPT_FAMILY_ID(A)+Q5.REQ_CNUM(A)
+Q5.REQ_AD_HOC_APPR_TS+Q5.REQ_AD_HOC_MVSID
+Q5.REQ_AD_HOC+Q5.RPT_FAM_REQ_ID
Output Streams:
--------------
15) To Operator #13
Estimated number of rows: 189650
Number of columns: 6
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q5.RPT_FAMILY_ID(A)+Q5.REQ_CNUM(A)
+Q5.REQ_AD_HOC_APPR_TS+Q5.REQ_AD_HOC_MVSID
+Q5.REQ_AD_HOC+Q5.RPT_FAM_REQ_ID
15) SORT : (Sort)
Cumulative Total Cost: 111446
Cumulative CPU Cost: 1.36829e+09
Cumulative I/O Cost: 11016
Cumulative Re-Total Cost: 0
Cumulative Re-CPU Cost: 0
Cumulative Re-I/O Cost: 1815
Cumulative First Row Cost: 111446
Estimated Bufferpool Buffers: 11799
Arguments:
---------
DUPLWARN: (Duplicates Warning flag)
FALSE
NUMROWS : (Estimated number of rows)
189650
ROWWIDTH: (Estimated width of rows)
52
SORTKEY : (Sort Key column)
1: Q5.RPT_FAMILY_ID(A)
2: Q5.REQ_CNUM(A)
SPILLED : (Pages spilled to bufferpool or disk)
2598
TEMPSIZE: (Temporary Table Page Size)
4096
UNIQUE : (Uniqueness required flag)
FALSE
Input Streams:
-------------
13) From Operator #16
Estimated number of rows: 189650
Number of columns: 6
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q5.REQ_AD_HOC_APPR_TS+Q5.REQ_AD_HOC_MVSID
+Q5.REQ_AD_HOC+Q5.RPT_FAM_REQ_ID+Q5.REQ_CNUM
+Q5.RPT_FAMILY_ID
Output Streams:
--------------
14) To Operator #14
Estimated number of rows: 189650
Number of columns: 6
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q5.RPT_FAMILY_ID(A)+Q5.REQ_CNUM(A)
+Q5.REQ_AD_HOC_APPR_TS+Q5.REQ_AD_HOC_MVSID
+Q5.REQ_AD_HOC+Q5.RPT_FAM_REQ_ID
16) TBSCAN: (Table Scan)
Cumulative Total Cost: 65696.4
Cumulative CPU Cost: 4.799e+08
Cumulative I/O Cost: 9201
Cumulative Re-Total Cost: 65696.4
Cumulative Re-CPU Cost: 4.79862e+08
Cumulative Re-I/O Cost: 9201
Cumulative First Row Cost: 25.0243
Estimated Bufferpool Buffers: 9201
Arguments:
---------
LCKAVOID: (Lock Avoidance)
TRUE
MAXPAGES: (Maximum pages for prefetch)
ALL
PREFETCH: (Type of Prefetch)
SEQUENTIAL
ROWLOCK : (Row Lock intent)
SHARE (CS/RS)
SCANDIR : (Scan Direction)
FORWARD
SPEED : (Assumed speed of scan, in sharing structures)
FAST
TABLOCK : (Table Lock intent)
INTENT SHARE
TBISOLVL: (Table access Isolation Level)
CURSOR STABILITY
THROTTLE: (Scan may be throttled, for scan sharing)
TRUE
VISIBLE : (May be included in scan sharing structures)
TRUE
WRAPPING: (Scan may start anywhere and wrap)
TRUE
Predicates:
----------
14) Sargable Predicate,
Comparison Operator: Is Not Null
Subquery Input Required: No
Filter Factor: 0.9939
Predicate Text:
--------------
Q4.REQ_FIN_TS IS NOT NULL
Input Streams:
-------------
12) From Object ENTITLE.RPT_FAM_ACC_REQ
Estimated number of rows: 190814
Number of columns: 8
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q4.$RID$+Q4.REQ_AD_HOC_APPR_TS
+Q4.REQ_AD_HOC_MVSID+Q4.REQ_AD_HOC
+Q4.RPT_FAM_REQ_ID+Q4.REQ_CNUM
+Q4.RPT_FAMILY_ID+Q4.REQ_FIN_TS
Output Streams:
--------------
13) To Operator #15
Estimated number of rows: 189650
Number of columns: 6
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q5.REQ_AD_HOC_APPR_TS+Q5.REQ_AD_HOC_MVSID
+Q5.REQ_AD_HOC+Q5.RPT_FAM_REQ_ID+Q5.REQ_CNUM
+Q5.RPT_FAMILY_ID
Objects Used in Access Plan:
---------------------------
Schema: ENTITLE
Name: SA_CUSTOMER
Type: Table (reference only)
Schema: ENTITLE
Name: SA_RPT_FAM_MAPPING
Type: Table (reference only)
Schema: ENTITLE
Name: USER_SA_MAPPING
Type: Table (reference only)
Schema: ENTITLE
Name: IX_RPT_FAMILY_ID
Type: Index
Time of creation: 2014-08-02-15.34.43.317656
Last statistics update: 2017-03-22-09.28.39.532532
Number of columns: 1
Number of rows: 1758007
Width of rows: -1
Number of buffer pool pages: 65096
Distinct row values: No
Tablespace name: ENTD4K1
Tablespace overhead: 24.100000
Tablespace transfer rate: 0.900000
Source for statistics: Single Node
Prefetch page count: 4
Container extent page count: 4
Index clustering statistic: 0.929583
Index leaf pages: 5449
Index tree levels: 3
Index full key cardinality: 250
Index first key cardinality: 250
Index first 2 keys cardinality: -1
Index first 3 keys cardinality: -1
Index first 4 keys cardinality: -1
Index sequential pages: 465
Index page density: 100
Index avg sequential pages: 77
Index avg gap between sequences:885
Index avg random pages: 97
Fetch avg sequential pages: -1
Fetch avg gap between sequences:-1
Fetch avg random pages: -1
Index RID count: 2053888
Index deleted RID count: 277301
Index empty leaf pages: 82
Base Table Schema: ENTITLE
Base Table Name: DIM_AUTH
Columns in index:
RPT_FAMILY_ID(A)
Schema: ENTITLE
Name: PK_SARFMAP
Type: Index
Time of creation: 2009-11-14-09.14.17.031797
Last statistics update: 2017-03-20-08.56.15.948201
Number of columns: 2
Number of rows: 132
Width of rows: -1
Number of buffer pool pages: 2
Distinct row values: Yes
Tablespace name: ENTI4K1
Tablespace overhead: 24.100000
Tablespace transfer rate: 0.900000
Source for statistics: Single Node
Prefetch page count: 4
Container extent page count: 4
Index clustering statistic: 100.000000
Index leaf pages: 2
Index tree levels: 2
Index full key cardinality: 132
Index first key cardinality: 26
Index first 2 keys cardinality: 132
Index first 3 keys cardinality: -1
Index first 4 keys cardinality: -1
Index sequential pages: 0
Index page density: 0
Index avg sequential pages: 0
Index avg gap between sequences:0
Index avg random pages: 2
Fetch avg sequential pages: -1
Fetch avg gap between sequences:-1
Fetch avg random pages: -1
Index RID count: 145
Index deleted RID count: 13
Index empty leaf pages: 0
Base Table Schema: ENTITLE
Base Table Name: SA_RPT_FAM_MAPPING
Columns in index:
CUSTOMER(A)
RPT_FAMILY_ID(A)
Schema: ENTITLE
Name: PK_USAMAP
Type: Index
Time of creation: 2009-11-14-09.14.17.324379
Last statistics update: 2017-03-20-08.56.21.329356
Number of columns: 2
Number of rows: 182
Width of rows: -1
Number of buffer pool pages: 3
Distinct row values: Yes
Tablespace name: ENTI4K1
Tablespace overhead: 24.100000
Tablespace transfer rate: 0.900000
Source for statistics: Single Node
Prefetch page count: 4
Container extent page count: 4
Index clustering statistic: 100.000000
Index leaf pages: 3
Index tree levels: 2
Index full key cardinality: 182
Index first key cardinality: 30
Index first 2 keys cardinality: 182
Index first 3 keys cardinality: -1
Index first 4 keys cardinality: -1
Index sequential pages: 0
Index page density: 0
Index avg sequential pages: 0
Index avg gap between sequences:0
Index avg random pages: 3
Fetch avg sequential pages: -1
Fetch avg gap between sequences:-1
Fetch avg random pages: -1
Index RID count: 195
Index deleted RID count: 13
Index empty leaf pages: 0
Base Table Schema: ENTITLE
Base Table Name: USER_SA_MAPPING
Columns in index:
CUSTOMER(A)
USER_ID(A)
Schema: ENTITLE
Name: DIM_AUTH
Type: Table
Time of creation: 2014-08-02-15.20.27.463149
Last statistics update: 2017-03-22-09.28.39.532532
Number of columns: 14
Number of rows: 1758007
Width of rows: 107
Number of buffer pool pages: 65096
Number of data partitions: 1
Distinct row values: No
Tablespace name: ENTD4K1
Tablespace overhead: 24.100000
Tablespace transfer rate: 0.900000
Source for statistics: Single Node
Prefetch page count: 4
Container extent page count: 4
Table overflow record count: 23030
Table Active Blocks: -1
Average Row Compression Ratio: 0
Percentage Rows Compressed: 0
Average Compressed Row Size: 0
Schema: ENTITLE
Name: RPT_FAM_ACC_REQ
Type: Table
Time of creation: 2008-11-13-07.02.14.824740
Last statistics update: 2017-03-20-08.56.06.375609
Number of columns: 14
Number of rows: 190814
Width of rows: 86
Number of buffer pool pages: 9103
Number of data partitions: 1
Distinct row values: No
Tablespace name: ENTD4K1
Tablespace overhead: 24.100000
Tablespace transfer rate: 0.900000
Source for statistics: Single Node
Prefetch page count: 4
Container extent page count: 4
Table overflow record count: 98
Table Active Blocks: -1
Average Row Compression Ratio: 0
Percentage Rows Compressed: 0
Average Compressed Row Size: 0
Schema: USERCNTL
Name: USER_PROFILE
Type: Table
Time of creation: 2008-11-13-07.02.12.527107
Last statistics update: 2017-03-20-08.55.42.983215
Number of columns: 9
Number of rows: 83869
Width of rows: 83
Number of buffer pool pages: 4644
Number of data partitions: 1
Distinct row values: No
Tablespace name: ENTD4K1
Tablespace overhead: 24.100000
Tablespace transfer rate: 0.900000
Source for statistics: Single Node
Prefetch page count: 4
Container extent page count: 4
Table overflow record count: 405
Table Active Blocks: -1
Average Row Compression Ratio: 0
Percentage Rows Compressed: 0
Average Compressed Row Size: 0
Base Table For Index Not Already Shown:
---------------------------------------
Schema: ENTITLE
Name: SA_RPT_FAM_MAPPING
Time of creation: 2009-11-14-09.14.16.993890
Last statistics update: 2017-03-20-08.56.15.948201
Number of data partitions: 1
Number of columns: 2
Number of rows: 132
Number of pages: 2
Number of pages with rows: 2
Table overflow record count: 0
Indexspace name: ENTI4K1
Tablespace name: ENTD4K1
Tablespace overhead: 24.100000
Tablespace transfer rate: 0.900000
Prefetch page count: 4
Container extent page count: 4
Long tablespace name: ENTD4K1
Schema: ENTITLE
Name: USER_SA_MAPPING
Time of creation: 2009-11-14-09.14.17.287094
Last statistics update: 2017-03-20-08.56.21.329356
Number of data partitions: 1
Number of columns: 2
Number of rows: 182
Number of pages: 3
Number of pages with rows: 3
Table overflow record count: 0
Indexspace name: ENTI4K1
Tablespace name: ENTD4K1
Tablespace overhead: 24.100000
Tablespace transfer rate: 0.900000
Prefetch page count: 4
Container extent page count: 4
Long tablespace name: ENTD4K1
[/code]
收起