Home » RDBMS Server » Performance Tuning » Query running slow (oracle,10202,AIX)
| Query running slow [message #330898] |
Tue, 01 July 2008 13:22  |
areeb
Messages: 9
Registered: February 2008
Location: NJ
|
Junior Member |
|
|
Hi Guys,
I have to tune up the below query, can anybody please help me out with this.stats on the tables are upto dateSELECT j.cd_activity_minor, j.desc_activity_minor desc_activity_minor,
j.started_on, j.started_by, j.id_case, j.id_member,
spkg_common_gets.sf_get_member_name(j.id_member) cp_ncp_name,
j.id_worker_update, j.cd_status, j.seq_order, j.seq_major_int,
j.seq_minor_int, j.id_forum, j.id_topic,
DECODE('A', 'I', 10 - j.days_overdue, j.days_overdue) days_overdue,
j.dt_due, j.seq_txn_event, j.cd_activity_major,
(SELECT desc_activity
FROM vamjr b
WHERE b.cd_activity_major = j.cd_activity_major
AND b.dt_end_validity = '31-DEC-9999') desc_activity_major,
j.id_othp_source, j.cd_type_othp_source, j.cd_subsystem, j.row_count
FROM (SELECT z.cd_activity_minor cd_activity_minor,
z.desc_activity_minor desc_activity_minor,
z.started_on started_on, z.started_by started_by,
z.id_case id_case, z.id_member id_member,
z.id_worker_update id_worker_update, z.cd_status cd_status,
z.seq_order seq_order, z.seq_major_int seq_major_int,
z.seq_minor_int seq_minor_int, z.id_forum id_forum,
z.id_topic id_topic, z.days_overdue days_overdue,
z.dt_due dt_due, z.seq_txn_event seq_txn_event,
z.cd_activity_major cd_activity_major,
z.id_othp_source id_othp_source,
z.cd_type_othp_source cd_type_othp_source,
z.cd_subsystem cd_subsystem, z.id_docket id_docket, ROWNUM rnm,
row_count
FROM (SELECT a.cd_activity_minor cd_activity_minor,
a.desc_activity_minor desc_activity_minor,
a.started_on started_on, a.started_by started_by,
a.id_case id_case, a.id_member id_member,
a.id_worker_update id_worker_update,
a.cd_status cd_status, a.seq_order seq_order,
a.seq_major_int seq_major_int,
a.seq_minor_int seq_minor_int, a.id_forum id_forum,
a.id_topic id_topic, a.days_overdue days_overdue,
a.dt_due dt_due, a.seq_txn_event seq_txn_event,
a.cd_activity_major cd_activity_major,
a.id_othp_source id_othp_source,
a.cd_type_othp_source cd_type_othp_source,
a.cd_subsystem cd_subsystem, a.id_docket id_docket,
COUNT(1) OVER() row_count
FROM (SELECT d.cd_activity_minor cd_activity_minor,
a.desc_activity desc_activity_minor,
TRUNC(d.dt_entered) started_on,
d.id_worker_update started_by,
d.id_case id_case,
spkg_wrkl.sf_get_member_by_role
('XYZ',
d.id_case
) id_member,
c.id_docket id_docket,
d.id_worker_update id_worker_update,
d.cd_status cd_status, d.seq_order seq_order,
d.seq_major_int seq_major_int,
d.seq_minor_int seq_minor_int,
d.id_forum id_forum, d.id_topic id_topic,
CASE
WHEN SIGN
( TO_DATE('30-JUN-08')
- TO_DATE(d.dt_due)
) = -1 THEN NULL
WHEN SIGN( TO_DATE('30-JUN-08')
- TO_DATE(d.dt_due)
) = 0 THEN NULL
ELSE ROUND(( TO_DATE('30-JUN-08')
- TO_DATE(d.dt_due)
)
)
END days_overdue,
d.dt_due dt_due, d.seq_txn_event seq_txn_event,
j.cd_activity_major cd_activity_major,
j.id_othp_source id_othp_source,
j.cd_type_othp_source cd_type_othp_source,
j.cd_subsystem cd_subsystem
FROM vdmnr d, vdmjr j, vcase c, vamnr a
WHERE d.id_case = j.id_case
AND d.seq_order = j.seq_order
AND d.seq_major_int = j.seq_major_int
AND a.cd_activity_minor = d.cd_activity_minor
AND d.id_case = c.id_case
AND d.dt_alert_prior <= '30-JUN-2008'
AND d.cd_status = 'STRT'
AND a.ind_action_alert = 'A'
AND d.dt_entered BETWEEN '01-JAN-2008'
AND TO_DATE('01-AUG-2008',
'DD-MON-YYYY'
)
+ 0.9999
AND ( d.id_worker_delegate != ' '
AND d.id_worker_delegate IN(
SELECT 'XYZ'
FROM DUAL
UNION ALL
SELECT id_worker
FROM vusrl
WHERE id_worker_sub =
'XYZ'
AND dt_effective <= '30-JUN-2008'
AND dt_expire >= '30-JUN-08'
AND dt_end_validity = '31-DEC-9999')
)
UNION ALL
SELECT d.cd_activity_minor cd_activity_minor,
a.desc_activity desc_activity_minor,
TRUNC(d.dt_entered) started_on,
d.id_worker_update started_by,
d.id_case id_case,
spkg_wrkl.sf_get_member_by_role
('XYZ',
d.id_case
) id_member,
c.id_docket id_docket,
d.id_worker_update id_worker_update,
d.cd_status cd_status, d.seq_order seq_order,
d.seq_major_int seq_major_int,
d.seq_minor_int seq_minor_int,
d.id_forum id_forum, d.id_topic id_topic,
CASE
WHEN SIGN
( TO_DATE('30-JUN-08')
- TO_DATE(d.dt_due)
) = -1 THEN NULL
WHEN SIGN( TO_DATE('30-JUN-08')
- TO_DATE(d.dt_due)
) = 0 THEN NULL
ELSE ROUND(( TO_DATE('30-JUN-08')
- TO_DATE(d.dt_due)
)
)
END days_overdue,
d.dt_due dt_due, d.seq_txn_event seq_txn_event,
j.cd_activity_major cd_activity_major,
j.id_othp_source id_othp_source,
j.cd_type_othp_source cd_type_othp_source,
j.cd_subsystem cd_subsystem
FROM vdmnr d, vdmjr j, vcase c, vamnr a
WHERE d.id_case = j.id_case
AND d.seq_order = j.seq_order
AND d.seq_major_int = j.seq_major_int
AND a.cd_activity_minor = d.cd_activity_minor
AND d.id_case = c.id_case
AND d.dt_alert_prior <= '30-JUN-2008'
AND d.cd_status = 'STRT'
AND a.ind_action_alert = 'A'
AND d.dt_entered BETWEEN '01-JAN-2008'
AND TO_DATE('01-AUG-2008',
'DD-MON-YYYY'
)
+ 0.9999
AND ( d.id_worker_delegate = ' '
AND d.id_case IN(
SELECT id_case
FROM vcwrk
WHERE id_worker IN(
SELECT 'XYZ'
FROM DUAL
UNION ALL
SELECT id_worker
FROM vusrl
WHERE id_worker_sub =
'XYZ'
AND dt_effective <=
'30-JUN-2008'
AND dt_expire >=
'30-JUN-2008'
AND dt_end_validity =
'31-DEC-9999')
AND dt_effective <= '30-JUN-2008'
AND dt_expire >= '30-JUN-2008'
AND dt_end_validity = '31-DEC-9999')
)
ORDER BY dt_due DESC, id_case, seq_order, seq_major_int,
seq_minor_int) a) z
WHERE ((1 = 0) OR(ROWNUM <= 10))) j
WHERE rnm >= 1
Explain Plan:
Execution Plan
----------------------------------------------------------
Plan hash value: 2666051352
--------------------------------------------------------------------------------
--------------------------------
| Id | Operation | Name | Rows | Bytes |
TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------
--------------------------------
| 0 | SELECT STATEMENT | | 10 | 22380 |
| 27987 (2)| 00:05:36 |
|* 1 | TABLE ACCESS BY INDEX ROWID | REF_MAJOR_ACTIVITY | 1 | 39 |
| 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | PK_VAMJR | 1 | |
| 1 (0)| 00:00:01 |
|* 3 | VIEW | | 10 | 22380 |
| 27987 (2)| 00:05:36 |
|* 4 | COUNT STOPKEY | | | |
| | |
| 5 | VIEW | | 61920 | 131M|
| 27987 (2)| 00:05:36 |
| 6 | WINDOW BUFFER | | 61920 | 130M|
| 27987 (2)| 00:05:36 |
| 7 | VIEW | | 61920 | 130M|
| 27987 (2)| 00:05:36 |
| 8 | SORT ORDER BY | | 61920 | 12M|
26M| 27987 (2)| 00:05:36 |
|* 9 | HASH JOIN | | 61920 | 12M|
| 25214 (3)| 00:05:03 |
|* 10 | TABLE ACCESS FULL | REF_MINOR_ACTIVITY | 564 | 25944 |
| 5 (0)| 00:00:01 |
|* 11 | HASH JOIN | | 94311 | 14M|
12M| 25208 (3)| 00:05:03 |
|* 12 | HASH JOIN | | 94557 | 11M|
9M| 17578 (3)| 00:03:31 |
|* 13 | HASH JOIN RIGHT SEMI | | 94557 | 8957K|
| 12673 (3)| 00:02:33 |
| 14 | VIEW | VW_NSO_2 | 30175 | 353K|
| 1217 (4)| 00:00:15 |
|* 15 | HASH JOIN | | 30175 | 1738K|
| 1217 (4)| 00:00:15 |
| 16 | VIEW | VW_NSO_1 | 32 | 544 |
| 6 (0)| 00:00:01 |
| 17 | UNION-ALL | | | |
| | |
| 18 | FAST DUAL | | 1 | |
| 2 (0)| 00:00:01 |
|* 19 | TABLE ACCESS FULL| USER_OFFICE_ROLES | 31 | 1147 |
| 4 (0)| 00:00:01 |
|* 20 | TABLE ACCESS FULL | CASE_WORKER | 532K| 21M|
| 1205 (3)| 00:00:15 |
|* 21 | TABLE ACCESS FULL | MINOR_ACTIVITY_DIARY | 908K| 73M|
| 11445 (3)| 00:02:18 |
| 22 | TABLE ACCESS FULL | CASE_DETAILS | 531K| 14M|
| 3175 (3)| 00:00:39 |
| 23 | TABLE ACCESS FULL | MAJOR_ACTIVITY_DIARY | 1231K| 39M|
| 4308 (2)| 00:00:52 |
--------------------------------------------------------------------------------
--------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"."DT_END_VALIDITY"=TO_DATE('9999-12-31 00:00:00', 'yyyy-mm-dd h
h24:mi:ss'))
2 - access("B"."CD_ACTIVITY_MAJOR"=:B1)
3 - filter("RNM">=1)
4 - filter(ROWNUM<=10)
9 - access("A"."CD_ACTIVITY_MINOR"="D"."CD_ACTIVITY_MINOR")
10 - filter("A"."IND_ACTION_ALERT"='A')
11 - access("D"."ID_CASE"="J"."ID_CASE" AND "D"."SEQ_ORDER"="J"."SEQ_ORDER" AN
D
"D"."SEQ_MAJOR_INT"="J"."SEQ_MAJOR_INT")
12 - access("D"."ID_CASE"="C"."ID_CASE")
13 - access("D"."ID_CASE"="$nso_col_1")
15 - access("ID_WORKER"="$nso_col_1")
19 - filter("ID_WORKER_SUB"='XYZ' AND "DT_END_VALIDITY"=TO_DATE('9
999-12-31 00:00:00',
'yyyy-mm-dd hh24:mi:ss') AND "DT_EXPIRE">=TO_DATE('2008-06-30 00:0
0:00', 'yyyy-mm-dd hh24:mi:ss') AND
"DT_EFFECTIVE"<=TO_DATE('2008-06-30 00:00:00', 'yyyy-mm-dd hh24:mi
:ss'))
20 - filter("DT_END_VALIDITY"=TO_DATE('9999-12-31 00:00:00', 'yyyy-mm-dd hh24:
mi:ss') AND
"DT_EFFECTIVE"<=TO_DATE('2008-06-30 00:00:00', 'yyyy-mm-dd hh24:mi
:ss') AND
"DT_EXPIRE">=TO_DATE('2008-06-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss
'))
21 - filter("D"."CD_STATUS"='STRT' AND "D"."DT_ENTERED">=TO_DATE('2008-01-01 0
0:00:00', 'yyyy-mm-dd
hh24:mi:ss') AND "D"."DT_ALERT_PRIOR"<=TO_DATE('2008-06-30 00:00:0
0', 'yyyy-mm-dd hh24:mi:ss') AND
"D"."DT_ENTERED"<=TO_DATE('2008-08-01 23:59:51', 'yyyy-mm-dd hh24:
mi:ss') AND
"D"."ID_WORKER_DELEGATE"=' ')
Statistics
----------------------------------------------------------
12 recursive calls
0 db block gets
112903 consistent gets
61325 physical reads
0 redo size
1527 bytes sent via SQL*Net to client
5744 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
3 rows processed
|
|
|
|
| Re: Query running slow [message #330902 is a reply to message #330898] |
Tue, 01 July 2008 13:39  |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
I have some general comments.
The extra blank lines make this DIFFICULT to read.
(ab)using "TO_DATE('30-JUN-08')" without a mask is a disaster waiting to happen.
>AND d.dt_alert_prior <= '30-JUN-2008' comparing a date with a string is a disaster waiting to happen.
bizarre & useless SQL below
AND d.id_worker_delegate IN(
SELECT 'XYZ' FROM DUAL
UNION ALL
SELECT id_worker
what/why SELECT 'XYZ' FROM DUAL UNION ?????
[Updated on: Tue, 01 July 2008 13:41] by Moderator Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Fri Jan 24 16:14:24 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
