SELECT TO_CHAR(A.DATA_DT, 'YYYY-MM-DD') AS DATA_DT,
A.MAGR_NO,
A.MAGR_NAME,
A.ORG_ID,
A.ORG_NAME,
sum(A.ACCT_BAL / 10000) as acct_bal,
sum(A.ACCT_AVG) as ACCT_AVG,
sum(A.SUM_BAL) as SUM_BAL,
sum(A.ASSETS_PROP) as ASSETS_PROP,
A.SEG_TYPE,
A.SEG_ID,
D.pty_type,
c.SEC_ORG_NAME ,
1 flag
FROM M_ASSETS_MAGR_PROP A
LEFT JOIN (SELECT ORG_ID,
PARENT_ORG_ID,
PTY_ID,
DATA_DT,
PTY_NAME,
PTY_SEX,
DEPT_NAME,
EMP_POSITION,
PTY_TYPE
FROM (SELECT ORG_ID,
PARENT_ORG_ID,
PTY_ID,
DATA_DT,
PTY_NAME,
PTY_SEX,
DEPT_NAME,
EMP_POSITION,
PTY_TYPE,
ROW_NUMBER() OVER(PARTITION BY PTY_ID ORDER BY DATA_DT DESC) ROW_NO
FROM DWM.M_MANAGE_LIST)
WHERE ROW_NO = 1
AND PTY_TYPE = 'רְ') D
ON A.MAGR_NO = D.PTY_ID
left join V_M_ORG_LEVEL c
on a.org_id = c.ORG_ID
where a.data_dt in
(select t.tx_date
from dw_sys_date t
where substr(to_char(t.tx_date, 'yyyy-mm-dd'), 1, 4) = '2014'
and t.is_mon_end = 'Y')
AND D.PTY_ID IS NOT NULL
and A.ORG_ID in (SELECT /*+ no_unnest */ t.Org_Id
FROM b_m_Sys_Branch t
WHERE t.Status = 1
AND t.Dept_Flag != '2'
CONNECT BY PRIOR t.Id = t.Parent_Id
START WITH t.Org_Id = '10000')
group by TO_CHAR(A.DATA_DT, 'YYYY-MM-DD'),
A.MAGR_NO,
A.MAGR_NAME,
A.ORG_ID,
A.ORG_NAME,
A.SEG_TYPE,
A.SEG_ID,
D.pty_type,
c.SEC_ORG_NAME;
Plan hash value: 1885001837
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2384 |00:00:44.71 | 1495K| | | |
| 1 | HASH GROUP BY | | 1 | 52 | 2384 |00:00:44.71 | 1495K| 38M| 4144K| 1362K (0)|
|* 2 | HASH JOIN RIGHT OUTER | | 1 | 201 | 175K|00:00:44.38 | 1495K| 1393K| 1393K| 1255K (0)|
| 3 | VIEW | V_M_ORG_LEVEL | 1 | 78 | 122 |00:00:00.01 | 24 | | | |
|* 4 | HASH JOIN OUTER | | 1 | 78 | 122 |00:00:00.01 | 24 | 1055K| 1055K| 520K (0)|
|* 5 | HASH JOIN OUTER | | 1 | 78 | 122 |00:00:00.01 | 18 | 1301K| 1301K| 890K (0)|
|* 6 | HASH JOIN OUTER | | 1 | 78 | 122 |00:00:00.01 | 12 | 1696K| 1696K| 1585K (0)|
|* 7 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 78 | 122 |00:00:00.01 | 6 | | | |
| 8 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 172 | 172 |00:00:00.01 | 6 | | | |
| 9 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 172 | 172 |00:00:00.01 | 6 | | | |
| 10 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 172 | 172 |00:00:00.01 | 6 | | | |
|* 11 | HASH JOIN | | 1 | 201 | 175K|00:00:44.29 | 1495K| 1599K| 1599K| 1241K (0)|
|* 12 | VIEW | | 1 | 226 | 53 |00:00:00.01 | 3 | | | |
|* 13 | WINDOW SORT PUSHED RANK | | 1 | 226 | 226 |00:00:00.01 | 3 | 18432 | 18432 |16384 (0)|
|* 14 | TABLE ACCESS FULL | M_MANAGE_LIST | 1 | 226 | 226 |00:00:00.01 | 3 | | | |
| 15 | NESTED LOOPS | | 1 | 5687 | 4479K|00:00:43.19 | 1495K| | | |
| 16 | NESTED LOOPS | | 1 | 16019 | 4479K|00:00:39.50 | 1334K| | | |
|* 17 | TABLE ACCESS FULL | DW_SYS_DATE | 1 | 1 | 12 |00:00:00.01 | 21 | | | |
|* 18 | INDEX RANGE SCAN | PK_M_ASSETS_MAGR_PROP | 12 | 16019 | 4479K|00:00:38.95 | 1334K| | | |
|* 19 | FILTER | | 216K| | 201K|00:00:36.60 | 1298K| | | |
|* 20 | CONNECT BY NO FILTERING WITH SW (UNIQUE)| | 216K| | 10M|00:00:35.53 | 1298K| | | |
| 21 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 216K| 172 | 37M|00:00:05.85 | 1298K| | | |
| 22 | TABLE ACCESS BY INDEX ROWID | M_ASSETS_MAGR_PROP | 4479K| 16019 | 4479K|00:00:02.23 | 160K| | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."ORG_ID"="C"."ORG_ID")
4 - access("C"."PARENT_ID"="D"."ID")
5 - access("B"."PARENT_ID"="C"."ID")
6 - access("A"."PARENT_ID"="B"."ID")
7 - filter((TO_NUMBER("A"."ORG_TYPE")=1 AND "A"."DEPT_FLAG"<>'2'))
11 - access("A"."MAGR_NO"="PTY_ID")
12 - filter(("ROW_NO"=1 AND "PTY_TYPE"='רְ'))
13 - filter(ROW_NUMBER() OVER ( PARTITION BY "PTY_ID" ORDER BY INTERNAL_FUNCTION("DATA_DT") DESC )<=1)
14 - filter("PTY_ID" IS NOT NULL)
17 - filter(("T"."IS_MON_END"='Y' AND SUBSTR(TO_CHAR(INTERNAL_FUNCTION("T"."TX_DATE"),'yyyy-mm-dd'),1,4)='2014'))
18 - access("A"."DATA_DT"="T"."TX_DATE")
filter( IS NOT NULL)
19 - filter(("T"."ORG_ID"=:B1 AND "T"."STATUS"=1 AND "T"."DEPT_FLAG"<>'2'))
20 - access("T"."PARENT_ID"=PRIOR NULL)
filter("T"."ORG_ID"='10000')
67 rows selected.
Plan hash value: 646284600
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 67 | 9313 | 6042 (1)| 00:01:13 |
| 1 | HASH GROUP BY | | 67 | 9313 | 6042 (1)| 00:01:13 |
|* 2 | HASH JOIN RIGHT OUTER | | 253 | 35167 | 5736 (1)| 00:01:09 |
| 3 | VIEW | V_M_ORG_LEVEL | 76 | 2584 | 18 (12)| 00:00:01 |
|* 4 | HASH JOIN OUTER | | 76 | 5852 | 18 (12)| 00:00:01 |
|* 5 | HASH JOIN OUTER | | 76 | 5016 | 13 (8)| 00:00:01 |
|* 6 | HASH JOIN OUTER | | 76 | 2812 | 9 (12)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 76 | 1140 | 4 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 168 | 3696 | 4 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 168 | 4872 | 4 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 168 | 1848 | 4 (0)| 00:00:01 |
|* 11 | HASH JOIN | | 253 | 26565 | 5718 (1)| 00:01:09 |
|* 12 | VIEW | | 53 | 1272 | 5 (20)| 00:00:01 |
|* 13 | WINDOW SORT PUSHED RANK | | 53 | 1007 | 5 (20)| 00:00:01 |
|* 14 | TABLE ACCESS FULL | M_MANAGE_LIST | 53 | 1007 | 4 (0)| 00:00:01 |
| 15 | NESTED LOOPS | | | | | |
| 16 | NESTED LOOPS | | 31341 | 2479K| 5712 (1)| 00:01:09 |
|* 17 | TABLE ACCESS FULL | DW_SYS_DATE | 2 | 20 | 20 (0)| 00:00:01 |
|* 18 | INDEX RANGE SCAN | PK_M_ASSETS_MAGR_PROP | 16281 | | 2557 (1)| 00:00:31 |
|* 19 | FILTER | | | | | |
|* 20 | CONNECT BY NO FILTERING WITH SW (UNIQUE)| | | | | |
| 21 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 168 | 3360 | 4 (0)| 00:00:01 |
| 22 | TABLE ACCESS BY INDEX ROWID | M_ASSETS_MAGR_PROP | 16281 | 1128K| 3135 (1)| 00:00:38 |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."ORG_ID"="C"."ORG_ID"(+))
4 - access("C"."PARENT_ID"="D"."ID"(+))
5 - access("B"."PARENT_ID"="C"."ID"(+))
6 - access("A"."PARENT_ID"="B"."ID"(+))
7 - filter(TO_NUMBER("A"."ORG_TYPE")=1 AND "A"."DEPT_FLAG"<>'2')
11 - access("A"."MAGR_NO"="PTY_ID")
12 - filter("ROW_NO"=1 AND "PTY_TYPE"='רְ')
13 - filter(ROW_NUMBER() OVER ( PARTITION BY "PTY_ID" ORDER BY INTERNAL_FUNCTION("DATA_DT") DESC )<=1)
14 - filter("PTY_ID" IS NOT NULL)
17 - filter("T"."IS_MON_END"='Y' AND SUBSTR(TO_CHAR(INTERNAL_FUNCTION("T"."TX_DATE"),'yyyy-mm-dd'),1,4)='2014')
18 - access("A"."DATA_DT"="T"."TX_DATE")
filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "B_M_SYS_BRANCH" "T" WHERE "T"."ORG_ID"=:B1 AND
"T"."STATUS"=1 AND "T"."DEPT_FLAG"<>'2' START WITH "T"."ORG_ID"='10000' CONNECT BY "T"."PARENT_ID"=PRIOR
"T"."ID"))
19 - filter("T"."ORG_ID"=:B1 AND "T"."STATUS"=1 AND "T"."DEPT_FLAG"<>'2')
20 - access("T"."PARENT_ID"=PRIOR "T"."ID")
filter("T"."ORG_ID"='10000')
SQL_ID 5admn3j93pmcp, child number 0
-------------------------------------
SELECT /*+no_push_pred(c) */ TO_CHAR(A.DATA_DT, 'YYYY-MM-DD') AS
DATA_DT, A.MAGR_NO, A.MAGR_NAME, A.ORG_ID, A.ORG_NAME,
sum(A.ACCT_BAL / 10000) as acct_bal, sum(A.ACCT_AVG) as ACCT_AVG,
sum(A.SUM_BAL) as SUM_BAL, sum(A.ASSETS_PROP) as ASSETS_PROP,
A.SEG_TYPE, A.SEG_ID, D.pty_type, c.SEC_ORG_NAME , 1 flag FROM
M_ASSETS_MAGR_PROP A LEFT JOIN (SELECT ORG_ID,
PARENT_ORG_ID, PTY_ID, DATA_DT,
PTY_NAME, PTY_SEX,
DEPT_NAME, EMP_POSITION,
PTY_TYPE FROM (SELECT ORG_ID,
PARENT_ORG_ID, PTY_ID,
DATA_DT, PTY_NAME,
PTY_SEX, DEPT_NAME,
EMP_POSITION, PTY_TYPE,
ROW_N
Plan hash value: 646284600
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6042 (100)| |
| 1 | HASH GROUP BY | | 67 | 9313 | 6042 (1)| 00:01:13 |
|* 2 | HASH JOIN RIGHT OUTER | | 253 | 35167 | 5736 (1)| 00:01:09 |
| 3 | VIEW | V_M_ORG_LEVEL | 76 | 2584 | 18 (12)| 00:00:01 |
|* 4 | HASH JOIN OUTER | | 76 | 5852 | 18 (12)| 00:00:01 |
|* 5 | HASH JOIN OUTER | | 76 | 5016 | 13 (8)| 00:00:01 |
|* 6 | HASH JOIN OUTER | | 76 | 2812 | 9 (12)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 76 | 1140 | 4 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 168 | 3696 | 4 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 168 | 4872 | 4 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 168 | 1848 | 4 (0)| 00:00:01 |
|* 11 | HASH JOIN | | 253 | 26565 | 5718 (1)| 00:01:09 |
|* 12 | VIEW | | 53 | 1272 | 5 (20)| 00:00:01 |
|* 13 | WINDOW SORT PUSHED RANK | | 53 | 1007 | 5 (20)| 00:00:01 |
|* 14 | TABLE ACCESS FULL | M_MANAGE_LIST | 53 | 1007 | 4 (0)| 00:00:01 |
| 15 | NESTED LOOPS | | | | | |
| 16 | NESTED LOOPS | | 31341 | 2479K| 5712 (1)| 00:01:09 |
|* 17 | TABLE ACCESS FULL | DW_SYS_DATE | 2 | 20 | 20 (0)| 00:00:01 |
|* 18 | INDEX RANGE SCAN | PK_M_ASSETS_MAGR_PROP | 16281 | | 2557 (1)| 00:00:31 |
|* 19 | FILTER | | | | | |
|* 20 | CONNECT BY NO FILTERING WITH SW (UNIQUE)| | | | | |
| 21 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 168 | 3360 | 4 (0)| 00:00:01 |
| 22 | TABLE ACCESS BY INDEX ROWID | M_ASSETS_MAGR_PROP | 16281 | 1128K| 3135 (1)| 00:00:38 |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."ORG_ID"="C"."ORG_ID")
4 - access("C"."PARENT_ID"="D"."ID")
5 - access("B"."PARENT_ID"="C"."ID")
6 - access("A"."PARENT_ID"="B"."ID")
7 - filter((TO_NUMBER("A"."ORG_TYPE")=1 AND "A"."DEPT_FLAG"<>'2'))
11 - access("A"."MAGR_NO"="PTY_ID")
12 - filter(("ROW_NO"=1 AND "PTY_TYPE"='רְ'))
13 - filter(ROW_NUMBER() OVER ( PARTITION BY "PTY_ID" ORDER BY INTERNAL_FUNCTION("DATA_DT") DESC )<=1)
14 - filter("PTY_ID" IS NOT NULL)
17 - filter(("T"."IS_MON_END"='Y' AND SUBSTR(TO_CHAR(INTERNAL_FUNCTION("T"."TX_DATE"),'yyyy-mm-dd'),1,4)='2014'
))
18 - access("A"."DATA_DT"="T"."TX_DATE")
filter( IS NOT NULL)
19 - filter(("T"."ORG_ID"=:B1 AND "T"."STATUS"=1 AND "T"."DEPT_FLAG"<>'2'))
20 - access("T"."PARENT_ID"=PRIOR NULL)
filter("T"."ORG_ID"='10000')
SELECT /*+no_push_pred(c) */
TO_CHAR(A.DATA_DT, 'YYYY-MM-DD') AS DATA_DT,
A.MAGR_NO,
A.MAGR_NAME,
A.ORG_ID,
A.ORG_NAME,
sum(A.ACCT_BAL / 10000) as acct_bal,
sum(A.ACCT_AVG) as ACCT_AVG,
sum(A.SUM_BAL) as SUM_BAL,
sum(A.ASSETS_PROP) as ASSETS_PROP,
A.SEG_TYPE,
A.SEG_ID,
D.pty_type,
c.SEC_ORG_NAME,
1 flag
FROM M_ASSETS_MAGR_PROP A
LEFT JOIN (SELECT ORG_ID,
PARENT_ORG_ID,
PTY_ID,
DATA_DT,
PTY_NAME,
PTY_SEX,
DEPT_NAME,
EMP_POSITION,
PTY_TYPE
FROM (SELECT ORG_ID,
PARENT_ORG_ID,
PTY_ID,
DATA_DT,
PTY_NAME,
PTY_SEX,
DEPT_NAME,
EMP_POSITION,
PTY_TYPE,
ROW_NUMBER() OVER(PARTITION BY PTY_ID ORDER BY DATA_DT DESC) ROW_NO
FROM DWM.M_MANAGE_LIST)
WHERE ROW_NO = 1
AND PTY_TYPE = '专职') D ON A.MAGR_NO = D.PTY_ID
left join V_M_ORG_LEVEL c on a.org_id = c.ORG_ID
where a.data_dt in
(select t.tx_date
from dw_sys_date t
where substr(to_char(t.tx_date, 'yyyy-mm-dd'), 1, 4) = '2014'
and t.is_mon_end = 'Y')
AND D.PTY_ID IS NOT NULL
and A.ORG_ID in (SELECT /*+ no_unnest */
t.Org_Id
FROM b_m_Sys_Branch t
WHERE t.Status = 1
AND t.Dept_Flag != '2'
CONNECT BY PRIOR t.Id = t.Parent_Id
START WITH t.Org_Id = '10000')
group by TO_CHAR(A.DATA_DT, 'YYYY-MM-DD'),
A.MAGR_NO,
A.MAGR_NAME,
A.ORG_ID,
A.ORG_NAME,
A.SEG_TYPE,
A.SEG_ID,
D.pty_type,
c.SEC_ORG_NAME;
Plan hash value: 646284600
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3186 |00:02:43.40 | 2111K| | | |
| 1 | HASH GROUP BY | | 1 | 67 | 3186 |00:02:43.40 | 2111K| 1212K| 970K| 1351K (0)|
|* 2 | HASH JOIN RIGHT OUTER | | 1 | 253 | 237K|00:02:42.50 | 2111K| 1035K| 1035K| 1302K (0)|
| 3 | VIEW | V_M_ORG_LEVEL | 1 | 76 | 124 |00:00:00.01 | 24 | | | |
|* 4 | HASH JOIN OUTER | | 1 | 76 | 124 |00:00:00.01 | 24 | 869K| 869K| 656K (0)|
|* 5 | HASH JOIN OUTER | | 1 | 76 | 124 |00:00:00.01 | 18 | 990K| 990K| 775K (0)|
|* 6 | HASH JOIN OUTER | | 1 | 76 | 124 |00:00:00.01 | 12 | 1180K| 1180K| 1257K (0)|
|* 7 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 76 | 124 |00:00:00.01 | 6 | | | |
| 8 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 168 | 174 |00:00:00.01 | 6 | | | |
| 9 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 168 | 174 |00:00:00.01 | 6 | | | |
| 10 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 168 | 174 |00:00:00.01 | 6 | | | |
|* 11 | HASH JOIN | | 1 | 253 | 237K|00:02:42.22 | 2111K| 1134K| 1134K| 1276K (0)|
|* 12 | VIEW | | 1 | 53 | 53 |00:00:00.01 | 6 | | | |
|* 13 | WINDOW SORT PUSHED RANK | | 1 | 53 | 53 |00:00:00.01 | 6 | 4096 | 4096 | 4096 (0)|
|* 14 | TABLE ACCESS FULL | M_MANAGE_LIST | 1 | 53 | 53 |00:00:00.01 | 6 | | | |
| 15 | NESTED LOOPS | | 1 | | 6349K|00:02:37.88 | 2111K| | | |
| 16 | NESTED LOOPS | | 1 | 31341 | 6349K|00:02:23.93 | 1974K| | | |
|* 17 | TABLE ACCESS FULL | DW_SYS_DATE | 1 | 2 | 12 |00:00:00.01 | 67 | | | |
|* 18 | INDEX RANGE SCAN | PK_M_ASSETS_MAGR_PROP | 12 | 16281 | 6349K|00:02:21.88 | 1974K| | | |
|* 19 | FILTER | | 314K| | 292K|00:02:14.33 | 1885K| | | |
|* 20 | CONNECT BY NO FILTERING WITH SW (UNIQUE)| | 314K| | 15M|00:02:09.24 | 1885K| | | |
| 21 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 314K| 168 | 54M|00:00:21.41 | 1885K| | | |
| 22 | TABLE ACCESS BY INDEX ROWID | M_ASSETS_MAGR_PROP | 6349K| 16281 | 6349K|00:00:07.66 | 136K| | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."ORG_ID"="C"."ORG_ID")
4 - access("C"."PARENT_ID"="D"."ID")
5 - access("B"."PARENT_ID"="C"."ID")
6 - access("A"."PARENT_ID"="B"."ID")
7 - filter((TO_NUMBER("A"."ORG_TYPE")=1 AND "A"."DEPT_FLAG"<>'2'))
11 - access("A"."MAGR_NO"="PTY_ID")
12 - filter(("ROW_NO"=1 AND "PTY_TYPE"='专职'))
13 - filter(ROW_NUMBER() OVER ( PARTITION BY "PTY_ID" ORDER BY INTERNAL_FUNCTION("DATA_DT") DESC )<=1)
14 - filter("PTY_ID" IS NOT NULL)
17 - filter(("T"."IS_MON_END"='Y' AND SUBSTR(TO_CHAR(INTERNAL_FUNCTION("T"."TX_DATE"),'yyyy-mm-dd'),1,4)='2014'))
18 - access("A"."DATA_DT"="T"."TX_DATE")
filter( IS NOT NULL)
19 - filter(("T"."ORG_ID"=:B1 AND "T"."STATUS"=1 AND "T"."DEPT_FLAG"<>'2'))
20 - access("T"."PARENT_ID"=PRIOR NULL)
filter("T"."ORG_ID"='10000')
67 rows selected.
Plan hash value: 1885001837
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2384 |00:00:44.71 | 1495K| | | |
| 1 | HASH GROUP BY | | 1 | 52 | 2384 |00:00:44.71 | 1495K| 38M| 4144K| 1362K (0)|
|* 2 | HASH JOIN RIGHT OUTER | | 1 | 201 | 175K|00:00:44.38 | 1495K| 1393K| 1393K| 1255K (0)|
| 3 | VIEW | V_M_ORG_LEVEL | 1 | 78 | 122 |00:00:00.01 | 24 | | | |
|* 4 | HASH JOIN OUTER | | 1 | 78 | 122 |00:00:00.01 | 24 | 1055K| 1055K| 520K (0)|
|* 5 | HASH JOIN OUTER | | 1 | 78 | 122 |00:00:00.01 | 18 | 1301K| 1301K| 890K (0)|
|* 6 | HASH JOIN OUTER | | 1 | 78 | 122 |00:00:00.01 | 12 | 1696K| 1696K| 1585K (0)|
|* 7 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 78 | 122 |00:00:00.01 | 6 | | | |
| 8 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 172 | 172 |00:00:00.01 | 6 | | | |
| 9 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 172 | 172 |00:00:00.01 | 6 | | | |
| 10 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 1 | 172 | 172 |00:00:00.01 | 6 | | | |
|* 11 | HASH JOIN | | 1 | 201 | 175K|00:00:44.29 | 1495K| 1599K| 1599K| 1241K (0)|
|* 12 | VIEW | | 1 | 226 | 53 |00:00:00.01 | 3 | | | |
|* 13 | WINDOW SORT PUSHED RANK | | 1 | 226 | 226 |00:00:00.01 | 3 | 18432 | 18432 |16384 (0)|
|* 14 | TABLE ACCESS FULL | M_MANAGE_LIST | 1 | 226 | 226 |00:00:00.01 | 3 | | | |
| 15 | NESTED LOOPS | | 1 | 5687 | 4479K|00:00:43.19 | 1495K| | | |
| 16 | NESTED LOOPS | | 1 | 16019 | 4479K|00:00:39.50 | 1334K| | | |
|* 17 | TABLE ACCESS FULL | DW_SYS_DATE | 1 | 1 | 12 |00:00:00.01 | 21 | | | |
|* 18 | INDEX RANGE SCAN | PK_M_ASSETS_MAGR_PROP | 12 | 16019 | 4479K|00:00:38.95 | 1334K| | | |
|* 19 | FILTER | | 216K| | 201K|00:00:36.60 | 1298K| | | |
|* 20 | CONNECT BY NO FILTERING WITH SW (UNIQUE)| | 216K| | 10M|00:00:35.53 | 1298K| | | |
| 21 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 216K| 172 | 37M|00:00:05.85 | 1298K| | | |
| 22 | TABLE ACCESS BY INDEX ROWID | M_ASSETS_MAGR_PROP | 4479K| 16019 | 4479K|00:00:02.23 | 160K| | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."ORG_ID"="C"."ORG_ID")
4 - access("C"."PARENT_ID"="D"."ID")
5 - access("B"."PARENT_ID"="C"."ID")
6 - access("A"."PARENT_ID"="B"."ID")
7 - filter((TO_NUMBER("A"."ORG_TYPE")=1 AND "A"."DEPT_FLAG"<>'2'))
11 - access("A"."MAGR_NO"="PTY_ID")
12 - filter(("ROW_NO"=1 AND "PTY_TYPE"='רְ'))
13 - filter(ROW_NUMBER() OVER ( PARTITION BY "PTY_ID" ORDER BY INTERNAL_FUNCTION("DATA_DT") DESC )<=1)
14 - filter("PTY_ID" IS NOT NULL)
17 - filter(("T"."IS_MON_END"='Y' AND SUBSTR(TO_CHAR(INTERNAL_FUNCTION("T"."TX_DATE"),'yyyy-mm-dd'),1,4)='2014'))
18 - access("A"."DATA_DT"="T"."TX_DATE")
filter( IS NOT NULL)
19 - filter(("T"."ORG_ID"=:B1 AND "T"."STATUS"=1 AND "T"."DEPT_FLAG"<>'2'))
20 - access("T"."PARENT_ID"=PRIOR NULL)
filter("T"."ORG_ID"='10000')
67 rows selected.
select SAMPLE_TIME,
SESSION_ID,
NAME,
P1,
P2,
P3,
WAIT_TIME,
CURRENT_OBJ#,
CURRENT_FILE#,
CURRENT_BLOCK#
from v$active_session_history ash, v$event_name enm
where ash.event# = enm.event#
and SESSION_ID=1280
select a.session_id,
a.sql_id,
a.blocking_session,
a.sample_time,
a.module,
a.PROGRAM,
a.event,
b.SQL_TEXT
from v$active_session_history a, v$sqlarea b
where a.sql_id = b.sql_id
SQL_ID bxy8345b81x3h, child number 0
-------------------------------------
SELECT /*+no_push_pred(c) */ TO_CHAR(A.DATA_DT, 'YYYY-MM-DD') AS
DATA_DT, A.MAGR_NO, A.MAGR_NAME, A.ORG_ID, A.ORG_NAME,
sum(A.ACCT_BAL / 10000) as acct_bal, sum(A.ACCT_AVG) as ACCT_AVG,
sum(A.SUM_BAL) as SUM_BAL, sum(A.ASSETS_PROP) as ASSETS_PROP,
A.SEG_TYPE, A.SEG_ID, D.pty_type, c.SEC_ORG_NAME, 1 flag FROM
M_ASSETS_MAGR_PROP A LEFT JOIN (SELECT ORG_ID,
PARENT_ORG_ID, PTY_ID, DATA_DT,
PTY_NAME, PTY_SEX,
DEPT_NAME, EMP_POSITION,
PTY_TYPE FROM (SELECT ORG_ID,
PARENT_ORG_ID, PTY_ID,
DATA_DT, PTY_NAME,
PTY_SEX, DEPT_NAME,
EMP_POSITION, PTY_TYPE,
ROW_NU
Plan hash value: 646284600
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6042 (100)| |
| 1 | HASH GROUP BY | | 67 | 9313 | 6042 (1)| 00:01:13 |
|* 2 | HASH JOIN RIGHT OUTER | | 253 | 35167 | 5736 (1)| 00:01:09 |
| 3 | VIEW | V_M_ORG_LEVEL | 76 | 2584 | 18 (12)| 00:00:01 |
|* 4 | HASH JOIN OUTER | | 76 | 5852 | 18 (12)| 00:00:01 |
|* 5 | HASH JOIN OUTER | | 76 | 5016 | 13 (8)| 00:00:01 |
|* 6 | HASH JOIN OUTER | | 76 | 2812 | 9 (12)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 76 | 1140 | 4 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 168 | 3696 | 4 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 168 | 4872 | 4 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 168 | 1848 | 4 (0)| 00:00:01 |
|* 11 | HASH JOIN | | 253 | 26565 | 5718 (1)| 00:01:09 |
|* 12 | VIEW | | 53 | 1272 | 5 (20)| 00:00:01 |
|* 13 | WINDOW SORT PUSHED RANK | | 53 | 1007 | 5 (20)| 00:00:01 |
|* 14 | TABLE ACCESS FULL | M_MANAGE_LIST | 53 | 1007 | 4 (0)| 00:00:01 |
| 15 | NESTED LOOPS | | | | | |
| 16 | NESTED LOOPS | | 31341 | 2479K| 5712 (1)| 00:01:09 |
|* 17 | TABLE ACCESS FULL | DW_SYS_DATE | 2 | 20 | 20 (0)| 00:00:01 |
|* 18 | INDEX RANGE SCAN | PK_M_ASSETS_MAGR_PROP | 16281 | | 2557 (1)| 00:00:31 |
|* 19 | FILTER | | | | | |
|* 20 | CONNECT BY NO FILTERING WITH SW (UNIQUE)| | | | | |
| 21 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 168 | 3360 | 4 (0)| 00:00:01 |
| 22 | TABLE ACCESS BY INDEX ROWID | M_ASSETS_MAGR_PROP | 16281 | 1128K| 3135 (1)| 00:00:38 |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."ORG_ID"="C"."ORG_ID")
4 - access("C"."PARENT_ID"="D"."ID")
5 - access("B"."PARENT_ID"="C"."ID")
6 - access("A"."PARENT_ID"="B"."ID")
7 - filter((TO_NUMBER("A"."ORG_TYPE")=1 AND "A"."DEPT_FLAG"<>'2'))
11 - access("A"."MAGR_NO"="PTY_ID")
12 - filter(("ROW_NO"=1 AND "PTY_TYPE"='רְ'))
13 - filter(ROW_NUMBER() OVER ( PARTITION BY "PTY_ID" ORDER BY INTERNAL_FUNCTION("DATA_DT") DESC )<=1)
14 - filter("PTY_ID" IS NOT NULL)
17 - filter(("T"."IS_MON_END"='Y' AND SUBSTR(TO_CHAR(INTERNAL_FUNCTION("T"."TX_DATE"),'yyyy-mm-dd'),1,4)='2014'
))
18 - access("A"."DATA_DT"="T"."TX_DATE")
filter( IS NOT NULL)
19 - filter(("T"."ORG_ID"=:B1 AND "T"."STATUS"=1 AND "T"."DEPT_FLAG"<>'2'))
20 - access("T"."PARENT_ID"=PRIOR NULL)
filter("T"."ORG_ID"='10000')
Plan hash value: 646284600
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 67 | 9313 | 6042 (1)| 00:01:13 |
| 1 | HASH GROUP BY | | 67 | 9313 | 6042 (1)| 00:01:13 |
|* 2 | HASH JOIN RIGHT OUTER | | 253 | 35167 | 5736 (1)| 00:01:09 |
| 3 | VIEW | V_M_ORG_LEVEL | 76 | 2584 | 18 (12)| 00:00:01 |
|* 4 | HASH JOIN OUTER | | 76 | 5852 | 18 (12)| 00:00:01 |
|* 5 | HASH JOIN OUTER | | 76 | 5016 | 13 (8)| 00:00:01 |
|* 6 | HASH JOIN OUTER | | 76 | 2812 | 9 (12)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 76 | 1140 | 4 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 168 | 3696 | 4 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 168 | 4872 | 4 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 168 | 1848 | 4 (0)| 00:00:01 |
|* 11 | HASH JOIN | | 253 | 26565 | 5718 (1)| 00:01:09 |
|* 12 | VIEW | | 53 | 1272 | 5 (20)| 00:00:01 |
|* 13 | WINDOW SORT PUSHED RANK | | 53 | 1007 | 5 (20)| 00:00:01 |
|* 14 | TABLE ACCESS FULL | M_MANAGE_LIST | 53 | 1007 | 4 (0)| 00:00:01 |
| 15 | NESTED LOOPS | | | | | |
| 16 | NESTED LOOPS | | 31341 | 2479K| 5712 (1)| 00:01:09 |
|* 17 | TABLE ACCESS FULL | DW_SYS_DATE | 2 | 20 | 20 (0)| 00:00:01 |
|* 18 | INDEX RANGE SCAN | PK_M_ASSETS_MAGR_PROP | 16281 | | 2557 (1)| 00:00:31 |
|* 19 | FILTER | | | | | |
|* 20 | CONNECT BY NO FILTERING WITH SW (UNIQUE)| | | | | |
| 21 | TABLE ACCESS FULL | B_M_SYS_BRANCH | 168 | 3360 | 4 (0)| 00:00:01 |
| 22 | TABLE ACCESS BY INDEX ROWID | M_ASSETS_MAGR_PROP | 16281 | 1128K| 3135 (1)| 00:00:38 |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."ORG_ID"="C"."ORG_ID"(+))
4 - access("C"."PARENT_ID"="D"."ID"(+))
5 - access("B"."PARENT_ID"="C"."ID"(+))
6 - access("A"."PARENT_ID"="B"."ID"(+))
7 - filter(TO_NUMBER("A"."ORG_TYPE")=1 AND "A"."DEPT_FLAG"<>'2')
11 - access("A"."MAGR_NO"="PTY_ID")
12 - filter("ROW_NO"=1 AND "PTY_TYPE"='רְ')
13 - filter(ROW_NUMBER() OVER ( PARTITION BY "PTY_ID" ORDER BY INTERNAL_FUNCTION("DATA_DT") DESC )<=1)
14 - filter("PTY_ID" IS NOT NULL)
17 - filter("T"."IS_MON_END"='Y' AND SUBSTR(TO_CHAR(INTERNAL_FUNCTION("T"."TX_DATE"),'yyyy-mm-dd'),1,4)='2014')
18 - access("A"."DATA_DT"="T"."TX_DATE")
filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "B_M_SYS_BRANCH" "T" WHERE "T"."ORG_ID"=:B1 AND
"T"."STATUS"=1 AND "T"."DEPT_FLAG"<>'2' START WITH "T"."ORG_ID"='10000' CONNECT BY "T"."PARENT_ID"=PRIOR
"T"."ID"))
19 - filter("T"."ORG_ID"=:B1 AND "T"."STATUS"=1 AND "T"."DEPT_FLAG"<>'2')
20 - access("T"."PARENT_ID"=PRIOR "T"."ID")
filter("T"."ORG_ID"='10000')
SELECT /*+no_push_pred(c) */
TO_CHAR(A.DATA_DT, 'YYYY-MM-DD') AS DATA_DT,
A.MAGR_NO,
A.MAGR_NAME,
A.ORG_ID,
A.ORG_NAME,
sum(A.ACCT_BAL / 10000) as acct_bal,
sum(A.ACCT_AVG) as ACCT_AVG,
sum(A.SUM_BAL) as SUM_BAL,
sum(A.ASSETS_PROP) as ASSETS_PROP,
A.SEG_TYPE,
A.SEG_ID,
D.pty_type,
c.SEC_ORG_NAME,
1 flag
FROM dwm.M_ASSETS_MAGR_PROP A
LEFT JOIN (SELECT ORG_ID,
PARENT_ORG_ID,
PTY_ID,
DATA_DT,
PTY_NAME,
PTY_SEX,
DEPT_NAME,
EMP_POSITION,
PTY_TYPE
FROM (SELECT ORG_ID,
PARENT_ORG_ID,
PTY_ID,
DATA_DT,
PTY_NAME,
PTY_SEX,
DEPT_NAME,
EMP_POSITION,
PTY_TYPE,
ROW_NUMBER() OVER(PARTITION BY PTY_ID ORDER BY DATA_DT DESC) ROW_NO
FROM DWM.M_MANAGE_LIST)
WHERE ROW_NO = 1
AND PTY_TYPE = 'רְ') D ON A.MAGR_NO = D.PTY_ID
left join dwm.V_M_ORG_LEVEL c on a.org_id = c.ORG_ID
where a.data_dt in
(select t.tx_date
from dwm.dw_sys_date t
where substr(to_char(t.tx_date, 'yyyy-mm-dd'), 1, 4) = '2014'
and t.is_mon_end = 'Y')
AND D.PTY_ID IS NOT NULL
and A.ORG_ID in (SELECT
t.Org_Id
FROM dwm.b_m_Sys_Branch t
WHERE t.Status = 1
AND t.Dept_Flag != '2'
CONNECT BY PRIOR t.Id = t.Parent_Id
START WITH t.Org_Id = '10000')
group by TO_CHAR(A.DATA_DT, 'YYYY-MM-DD'),
A.MAGR_NO,
A.MAGR_NAME,
A.ORG_ID,
A.ORG_NAME,
A.SEG_TYPE,
A.SEG_ID,
D.pty_type,
c.SEC_ORG_NAME;
SAMPLE_TIME SESSION_ID NAME P1 P2 P3 WAIT_TIME CURRENT_OBJ# CURRENT_FILE# CURRENT_BLOCK#
1 10-2月 -15 01.56.53.052 下午 998 db file parallel read 2 2 2 0 93238 24 2402552
2 10-2月 -15 01.56.52.052 下午 998 db file parallel read 2 2 2 0 93238 24 2402351
3 10-2月 -15 01.56.51.051 下午 998 db file sequential read 46 503382 1 0 93238 46 503382
4 10-2月 -15 01.56.50.051 下午 998 db file parallel read 2 2 2 0 93238 46 503089
5 10-2月 -15 01.56.49.050 下午 998 db file parallel read 2 2 2 0 93238 54 1248234
6 10-2月 -15 01.56.48.050 下午 998 db file parallel read 1 2 2 0 93238 54 1245407
7 10-2月 -15 01.56.47.049 下午 998 db file parallel read 2 3 3 0 93238 54 1244878
8 10-2月 -15 01.56.46.049 下午 998 db file parallel read 2 2 2 0 93238 61 3734485
9 10-2月 -15 01.56.45.048 下午 998 db file parallel read 2 2 2 0 93238 54 1247857
10 10-2月 -15 01.56.44.048 下午 998 db file parallel read 1 2 2 0 93238 54 1245166
11 10-2月 -15 01.56.43.047 下午 998 db file parallel read 2 2 2 0 93238 46 501130
12 10-2月 -15 01.56.42.047 下午 998 db file parallel read 2 3 3 0 93238 61 3734456
db file parallel read 等待事件是Oracle 可以对多个数据文件实施并行地物理读取并加载到不连续的内存空间中(可能是PGA也可能是Buffer Cache)。 该db file parallel read 往往出现在 recovery操作或者 buffer prefetch以优化多个单块读的操作中。若该等待事件很突出，一般可以参考db file sequential read的优化方法来调优。
该等待事件的 P1、P2、P3如下：
Parameters:
P1 files Number of files being requested
P2 blocks Total number of blocks being requested
P3 requests Number of actual AIO requests
高聚簇因子 index range scan -----> 引发灰常多的 rowid 回表扫描离散的block ------>buffer prefetching ------------> db file parallel read...
analyze table M_ASSETS_MAGR_PROP compute statistics;
SQL> set linesize 200
SQL> select
2 owner ,
3 table_name ,
4 pct_free ,
5 pct_used ,
6 avg_row_len ,
7 num_rows ,
8 chain_cnt ,
9 chain_cnt/num_rows
10 from
11 dba_tables
12 where
13 table_name='M_ASSETS_MAGR_PROP'
14 order by
15 chain_cnt desc;
OWNER TABLE_NAME PCT_FREE PCT_USED AVG_ROW_LEN NUM_ROWS CHAIN_CNT CHAIN_CNT/NUM_ROWS
------------------------------ ------------------------------ ---------- ---------- ----------- ---------- ---------- ------------------
DWM M_ASSETS_MAGR_PROP 10 97 49995072 0 0