Home » RDBMS Server » Performance Tuning » Query taking time (Oracle 11g HP-UX B11.23)
| Query taking time [message #488671] |
Mon, 10 January 2011 08:02  |
indrajit2002
Messages: 53
Registered: November 2007
Location: INDIA
|
Member |
|
|
We have this query which is taking a long time to execute. From the explain plan what i found out is there is a full table scan going on W_GL_OTHER_F. Please help in identifying the problem area and solutions.
The query is,
select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c4 as c4,
D1.c5 as c5,
D1.c6 as c6,
D1.c7 as c7,
D1.c8 as c8
from
(select distinct D1.c2 as c1,
D1.c3 as c2,
D1.c4 as c3,
D1.c5 as c4,
D1.c6 as c5,
D1.c7 as c6,
D1.c8 as c7,
D1.c1 as c8,
D1.c5 as c9
from
(select sum(case when T324628.OTHER_DOC_AMT is null then 0 else T324628.OTHER_DOC_AMT end ) as c1,
T91397.GL_ACCOUNT_NUM as c2,
T149255.SEGMENT_VAL_CODE as c3,
T148908.SEGMENT_VAL_DESC as c4,
T148543.HIER4_CODE as c5,
T148543.HIER4_NAME as c6,
T91707.ACCT_DOC_NUM as c7,
T91707.X_LINE_DESCRIPTION as c8
from
W_GL_OTHER_F T91707 /* Fact_W_GL_OTHER_F */ ,
W_GL_ACCOUNT_D T91397 /* Dim_W_GL_ACCOUNT_D */ ,
W_STATUS_D T96094 /* Dim_W_STATUS_D_Generic */ ,
WC_GL_OTHER_F_MV T324628 /* Fact_WC_GL_OTHER_MV */ ,
W_GL_SEGMENT_D T149255 /* Dim_W_GL_SEGMENT_D_Segment1 */ ,
W_GL_SEGMENT_D T148937 /* Dim_W_GL_SEGMENT_D_Segment3 */ ,
W_HIERARCHY_D T148543 /* Dim_W_HIERARCHY_D_Segment3 */ ,
W_GL_SEGMENT_D T148908 /* Dim_W_GL_SEGMENT_D_Segment2 */
where ( T91397.ROW_WID = T91707.GL_ACCOUNT_WID and T91707.DOC_STATUS_WID = T96094.ROW_WID and T96094.ROW_WID = T324628.DOC_STATUS_WID and T148543.HIER_CODE = T148937.SEGMENT_LOV_ID and T148543.HIER20_CODE = T148937.SEGMENT_VAL_CODE and T324628.DELETE_FLG = 'N' and T324628.X_CURRENCY_CODE = 'CAD' and T148543.HIER4_CODE <> '00000000000' and T91397.RECON_TYPE_CODE is not null and T91397.ROW_WID = T324628.GL_ACCOUNT_WID and T91397.ACCOUNT_SEG3_CODE = T148937.SEGMENT_VAL_CODE and T91397.ACCOUNT_SEG3_ATTRIB = T148937.SEGMENT_LOV_ID and T91397.ACCOUNT_SEG2_CODE = T148908.SEGMENT_VAL_CODE and T91397.ACCOUNT_SEG2_ATTRIB = T148908.SEGMENT_LOV_ID and T91397.ACCOUNT_SEG1_CODE = T149255.SEGMENT_VAL_CODE and T91397.ACCOUNT_SEG1_ATTRIB = T149255.SEGMENT_LOV_ID and (T96094.W_STATUS_CODE in ('POSTED', 'REVERSED')) and T91397.GL_ACCOUNT_NUM like '%98%' )
group by T91397.GL_ACCOUNT_NUM, T91707.ACCT_DOC_NUM, T91707.X_LINE_DESCRIPTION, T148543.HIER4_CODE, T148543.HIER4_NAME, T148908.SEGMENT_VAL_DESC, T149255.SEGMENT_VAL_CODE
) D1
) D1
order by c1, c2, c3, c4, c5, c6, c7
The plan is
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3196636288
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Psto
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 810K| 306M| | 266K (1)| 01:20:03 | | |
| 1 | HASH GROUP BY | | 810K| 306M| 320M| 266K (1)| 01:20:03 | | |
|* 2 | HASH JOIN | | 810K| 306M| 38M| 239K (1)| 01:11:56 | | |
|* 3 | MAT_VIEW ACCESS FULL | WC_GL_OTHER_F_MV | 1137K| 40M| | 9771 (2)| 00:0
|* 4 | HASH JOIN | | 531K| 189M| | 222K (1)| 01:06:38 | | |
| 5 | INLIST ITERATOR | | | | | | | | |
|* 6 | INDEX RANGE SCAN | W_STATUS_D_U2 | 4 | 56 | | 1 (0)| 00:00:01 |
|* 7 | HASH JOIN | | 607K| 208M| 8704K| 222K (1)| 01:06:38 | | |
|* 8 | HASH JOIN | | 40245 | 8214K| 2464K| 10843 (2)| 00:03:16 | | |
| 9 | VIEW | index$_join$_007 | 35148 | 2025K| | 122 (32)| 00:00:03 | |
|* 10 | HASH JOIN | | | | | | | | |
|* 11 | HASH JOIN | | | | | | | | |
|* 12 | HASH JOIN | | | | | | | | |
| 13 | BITMAP CONVERSION TO ROWIDS | | 35148 | 2025K| | 1 (0)| 00:00:01 | |
| 14 | BITMAP INDEX FULL SCAN | W_HIERARCHY_D_M2 | | | | | | |
| 15 | BITMAP CONVERSION TO ROWIDS | | 35148 | 2025K| | 24 (0)| 00:00:01 | |
| 16 | BITMAP INDEX FULL SCAN | W_HIERARCHY_D_M4 | | | | | | |
| 17 | BITMAP CONVERSION TO ROWIDS | | 35148 | 2025K| | 24 (0)| 00:00:01 | |
|* 18 | BITMAP INDEX FULL SCAN | X_W_HIERARCHY_D_M11 | | | | | | |
| 19 | BITMAP CONVERSION TO ROWIDS | | 35148 | 2025K| | 33 (0)| 00:00:01 | |
| 20 | BITMAP INDEX FULL SCAN | X_W_HIERARCHY_D_M12 | | | | | | |
|* 21 | HASH JOIN | | 40246 | 5895K| 4096K| 10430 (2)| 00:03:08 | |
| 22 | VIEW | index$_join$_008 | 65417 | 3321K| | 197 (14)| 00:00:04 |
|* 23 | HASH JOIN | | | | | | | | |
|* 24 | HASH JOIN | | | | | | | | |
| 25 | BITMAP CONVERSION TO ROWIDS | | 65417 | 3321K| | 3 (0)| 00:00:01 | |
| 26 | BITMAP INDEX FULL SCAN | W_GL_SEGMENT_D_M1 | | | | | | |
| 27 | BITMAP CONVERSION TO ROWIDS | | 65417 | 3321K| | 66 (2)| 00:00:02 | |
| 28 | BITMAP INDEX FULL SCAN | W_GL_SEGMENT_D_M2 | | | | | | |
| 29 | BITMAP CONVERSION TO ROWIDS | | 65417 | 3321K| | 100 (1)| 00:00:02 | |
| 30 | BITMAP INDEX FULL SCAN | W_GL_SEGMENT_D_M3 | | | | | | |
|* 31 | HASH JOIN | | 40246 | 3851K| | 9953 (1)| 00:03:00 | | |
| 32 | VIEW | index$_join$_006 | 65417 | 1149K| | 82 (18)| 00:00:02 | |
|* 33 | HASH JOIN | | | | | | | | |
| 34 | BITMAP CONVERSION TO ROWIDS | | 65417 | 1149K| | 3 (0)| 00:00:01 | |
| 35 | BITMAP INDEX FULL SCAN | W_GL_SEGMENT_D_M1 | | | | | | |
| 36 | BITMAP CONVERSION TO ROWIDS | | 65417 | 1149K| | 66 (2)| 00:00:02 | |
| 37 | BITMAP INDEX FULL SCAN | W_GL_SEGMENT_D_M2 | | | | | | |
|* 38 | HASH JOIN | | 40246 | 3144K| | 9870 (1)| 00:02:58 | | |
| 39 | VIEW | index$_join$_005 | 65417 | 1149K| | 82 (18)| 00:00:02 | |
|* 40 | HASH JOIN | | | | | | | | |
| 41 | BITMAP CONVERSION TO ROWIDS| | 65417 | 1149K| | 3 (0)| 00:00:01 | |
| 42 | BITMAP INDEX FULL SCAN | W_GL_SEGMENT_D_M1 | | | | | | |
| 43 | BITMAP CONVERSION TO ROWIDS| | 65417 | 1149K| | 66 (2)| 00:00:02 | |
| 44 | BITMAP INDEX FULL SCAN | W_GL_SEGMENT_D_M2 | | | | | | |
|* 45 | TABLE ACCESS FULL | W_GL_ACCOUNT_D | 40246 | 2436K| | 9788 (1)| 00:02:57
| 46 | PARTITION RANGE ALL | | 11M| 4261M| | 152K (2)| 00:45:43 | 1 |1048
| 47 | TABLE ACCESS FULL | W_GL_OTHER_F | 11M| 4261M| | 152K (2)| 00:45:43
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T96094"."ROW_WID"="T324628"."DOC_STATUS_WID" AND "T91397"."ROW_WID"="T324628"."GL_ACC
3 - filter("T324628"."X_CURRENCY_CODE"='CAD' AND "T324628"."DELETE_FLG"='N')
4 - access("T91707"."DOC_STATUS_WID"="T96094"."ROW_WID")
6 - access("T96094"."W_STATUS_CODE"='POSTED' OR "T96094"."W_STATUS_CODE"='REVERSED')
7 - access("T91397"."ROW_WID"="T91707"."GL_ACCOUNT_WID")
8 - access("T148543"."HIER_CODE"="T148937"."SEGMENT_LOV_ID" AND "T148543"."HIER20_CODE"="T148937"
10 - access(ROWID=ROWID)
11 - access(ROWID=ROWID)
12 - access(ROWID=ROWID)
18 - filter("T148543"."HIER4_CODE"<>'00000000000')
21 - access("T91397"."ACCOUNT_SEG2_CODE"="T148908"."SEGMENT_VAL_CODE" AND
"T91397"."ACCOUNT_SEG2_ATTRIB"="T148908"."SEGMENT_LOV_ID")
23 - access(ROWID=ROWID)
24 - access(ROWID=ROWID)
31 - access("T91397"."ACCOUNT_SEG3_CODE"="T148937"."SEGMENT_VAL_CODE" AND
"T91397"."ACCOUNT_SEG3_ATTRIB"="T148937"."SEGMENT_LOV_ID")
33 - access(ROWID=ROWID)
38 - access("T91397"."ACCOUNT_SEG1_CODE"="T149255"."SEGMENT_VAL_CODE" AND
"T91397"."ACCOUNT_SEG1_ATTRIB"="T149255"."SEGMENT_LOV_ID")
40 - access(ROWID=ROWID)
45 - filter("T91397"."GL_ACCOUNT_NUM" LIKE '%98%' AND "T91397"."RECON_TYPE_CODE" IS NOT NULL)
|
|
|
|
| Re: Query taking time [message #488673 is a reply to message #488671] |
Mon, 10 January 2011 08:09  |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
1) Multi-line the where clauses please. It's really hard to read as you posted it.
2) What are you trying to achieve with this (other than confuse people):
(select distinct D1.c2 as c1,
D1.c3 as c2,
D1.c4 as c3,
D1.c5 as c4,
D1.c6 as c5,
D1.c7 as c6,
D1.c8 as c7,
D1.c1 as c8,
D1.c5 as c9
Why is there a distinct there?
|
|
|
|
| Re: Query taking time [message #488676 is a reply to message #488673] |
Mon, 10 January 2011 08:20 |
indrajit2002
Messages: 53
Registered: November 2007
Location: INDIA
|
Member |
|
|
Sorry for the confusion. This is the query after multilining the where clause,
select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c4 as c4,
D1.c5 as c5,
D1.c6 as c6,
D1.c7 as c7,
D1.c8 as c8
from
(select distinct D1.c2 as c1,
D1.c3 as c2,
D1.c4 as c3,
D1.c5 as c4,
D1.c6 as c5,
D1.c7 as c6,
D1.c8 as c7,
D1.c1 as c8,
D1.c5 as c9
from
(select sum(case when T324628.OTHER_DOC_AMT is null then 0 else T324628.OTHER_DOC_AMT end ) as c1,
T91397.GL_ACCOUNT_NUM as c2,
T149255.SEGMENT_VAL_CODE as c3,
T148908.SEGMENT_VAL_DESC as c4,
T148543.HIER4_CODE as c5,
T148543.HIER4_NAME as c6,
T91707.ACCT_DOC_NUM as c7,
T91707.X_LINE_DESCRIPTION as c8
from
W_GL_OTHER_F T91707 /* Fact_W_GL_OTHER_F */ ,
W_GL_ACCOUNT_D T91397 /* Dim_W_GL_ACCOUNT_D */ ,
W_STATUS_D T96094 /* Dim_W_STATUS_D_Generic */ ,
WC_GL_OTHER_F_MV T324628 /* Fact_WC_GL_OTHER_MV */ ,
W_GL_SEGMENT_D T149255 /* Dim_W_GL_SEGMENT_D_Segment1 */ ,
W_GL_SEGMENT_D T148937 /* Dim_W_GL_SEGMENT_D_Segment3 */ ,
W_HIERARCHY_D T148543 /* Dim_W_HIERARCHY_D_Segment3 */ ,
W_GL_SEGMENT_D T148908 /* Dim_W_GL_SEGMENT_D_Segment2 */
where ( T91397.ROW_WID = T91707.GL_ACCOUNT_WID and T91707.DOC_STATUS_WID = T96094.ROW_WID
and T96094.ROW_WID = T324628.DOC_STATUS_WID and T148543.HIER_CODE = T148937.SEGMENT_LOV_ID
and T148543.HIER20_CODE = T148937.SEGMENT_VAL_CODE and T324628.DELETE_FLG = 'N' and T324628.X_CURRENCY_CODE = 'CAD'
and T148543.HIER4_CODE <> '00000000000' and T91397.RECON_TYPE_CODE is not null
and T91397.ROW_WID = T324628.GL_ACCOUNT_WID and T91397.ACCOUNT_SEG3_CODE = T148937.SEGMENT_VAL_CODE
and T91397.ACCOUNT_SEG3_ATTRIB = T148937.SEGMENT_LOV_ID and T91397.ACCOUNT_SEG2_CODE = T148908.SEGMENT_VAL_CODE
and T91397.ACCOUNT_SEG2_ATTRIB = T148908.SEGMENT_LOV_ID and T91397.ACCOUNT_SEG1_CODE = T149255.SEGMENT_VAL_CODE
and T91397.ACCOUNT_SEG1_ATTRIB = T149255.SEGMENT_LOV_ID and (T96094.W_STATUS_CODE in ('POSTED', 'REVERSED'))
and T91397.GL_ACCOUNT_NUM like '%98%' )
group by T91397.GL_ACCOUNT_NUM, T91707.ACCT_DOC_NUM, T91707.X_LINE_DESCRIPTION, T148543.HIER4_CODE, T148543.HIER4_NAME, T148908.SEGMENT_VAL_DESC, T149255.SEGMENT_VAL_CODE
) D1
) D1
order by c1, c2, c3, c4, c5, c6, c7
|
|
|
|
|
|
|
|
|
|
| Re: Query taking time [message #488684 is a reply to message #488682] |
Mon, 10 January 2011 09:42 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Do you want help or not? We don't have your tables, we don't know what columns they have or what indexes there are.
When we ask for indexes on a table we expect to be given the full list.
You also haven't answered my question about the distinct.
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Apr 24 01:08:47 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
