| Query running slow [message #483062] |
Thu, 18 November 2010 01:02  |
virmahi
Messages: 266
Registered: April 2008
Location: India
|
Senior Member |
|
|
Hi,
I have a query which is running very slow... It takes around 1 minute to exceute in production.
SELECT user_id, priority from(
SELECT user_id, priority, rank() over (order by priority asc) "RANK"
FROM (
SELECT distinct advisor.user_id user_id, pr.priority priority,
rank() over (order by pr.priority asc) "RANK",
(wip.wip_limit - SUM (DECODE (opp.lead_status, 'F1', 1, 0))
) available_wip
FROM adm_users au,
dm_opportunity_priority_detail pr,
adm_policy_wip_limit wip, adm_policy_master pol, dm_opportunity_details_fsc opp,
(SELECT distinct apm.user_id user_id
FROM adm_pincode_master p, adm_cluster_pincode c, adm_cluster_master cm,
adm_profile_cluster apc, adm_profile_master apm, adm_login_audit login
WHERE cm.OPERATION_TYPE_ID = 1
AND apm.operation_id = 1
AND apm.is_active = 1
AND p.pincode = 560001 --v_meeting_pin
AND p.pincode_id = c.PINCODE_ID
AND c.cluster_id = cm.cluster_id
AND cm.cluster_id= apc.cluster_id
AND apm.profile_id=apc.profile_id
AND login.login_name = apm.user_id
AND login.action= 'LOGIN_SUCCESSFULL'
AND (sysdate - login.CREATION_DATE) <= 30 -- V_DAYS_LOGGED_SINCE
) advisor
WHERE advisor.user_id = au.user_id
AND advisor.user_id = opp.ANH_FSC_CODE(+)
AND pol.policy_id = wip.policy_id
-- AND advisor.user_id <> nvl(v_old_anh_fsc_code,'NO_agent')
AND au.SUBROLE_TYPE_ID = pr.sub_role_id
AND au.is_active = 1
AND pr.ACTIVE_FLAG =1
AND pr.OPPORTUNITY_TYPE_ID = 127 --V_OPPORTUNITY_TYPE_ID
AND wip.opportunity_type_id = 127 --V_OPPORTUNITY_TYPE_ID
and opp.opportunity_type_id(+) = 127 --V_OPPORTUNITY_TYPE_ID
GROUP BY advisor.user_id, priority, wip_limit ) prt
WHERE available_wip > 0
)
WHERE rank = 1
ORDER by user_id ASC;
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 53 | 40 (8)|
| 1 | SORT ORDER BY | | 1 | 53 | 40 (8)|
|* 2 | VIEW | | 1 | 53 | 39 (6)|
|* 3 | WINDOW SORT PUSHED RANK | | 1 | 53 | 39 (6)|
|* 4 | VIEW | | 1 | 53 | 38 (3)|
| 5 | HASH UNIQUE | | 1 | 18 | 38 (3)|
| 6 | WINDOW NOSORT | | 1 | 18 | 38 (3)|
| 7 | SORT GROUP BY | | 1 | 18 | 38 (3)|
| 8 | VIEW | | 1 | 18 | 38 (3)|
| 9 | HASH UNIQUE | | 1 | 173 | 38 (3)|
|* 10 | TABLE ACCESS BY INDEX ROWID | ADM_POLICY_WIP_LIMIT | 1 | 9 | 1 (0)|
| 11 | NESTED LOOPS | | 1 | 173 | 37 (0)|
| 12 | MERGE JOIN CARTESIAN | | 1 | 164 | 36 (0)|
|* 13 | TABLE ACCESS BY INDEX ROWID | ADM_LOGIN_AUDIT | 3 | 102 | 7 (0)|
| 14 | NESTED LOOPS | | 1 | 148 | 35 (0)|
| 15 | NESTED LOOPS | | 1 | 114 | 28 (0)|
| 16 | NESTED LOOPS | | 1 | 95 | 26 (0)|
| 17 | NESTED LOOPS OUTER | | 1 | 69 | 25 (0)|
| 18 | NESTED LOOPS | | 1 | 48 | 23 (0)|
| 19 | NESTED LOOPS | | 3 | 96 | 20 (0)|
| 20 | NESTED LOOPS | | 1 | 25 | 19 (0)|
| 21 | NESTED LOOPS | | 2 | 34 | 18 (0)|
|* 22 | TABLE ACCESS FULL | ADM_PINCODE_MASTER | 1 | 11 | 17 (0)|
|* 23 | INDEX RANGE SCAN | PK_IDX_ACP | 2 | 12 | 1 (0)|
|* 24 | TABLE ACCESS BY INDEX ROWID| ADM_CLUSTER_MASTER | 1 | 8 | 1 (0)|
|* 25 | INDEX UNIQUE SCAN | SYS_C004094 | 1 | | 0 (0)|
|* 26 | INDEX RANGE SCAN | PK_IDX_APC | 10 | 70 | 1 (0)|
|* 27 | TABLE ACCESS BY INDEX ROWID | ADM_PROFILE_MASTER | 1 | 16 | 1 (0)|
|* 28 | INDEX UNIQUE SCAN | SYS_C004152 | 1 | | 0 (0)|
|* 29 | TABLE ACCESS BY INDEX ROWID | DM_OPPORTUNITY_DETAILS_FSC | 1 | 21 | 2 (0)|
|* 30 | INDEX RANGE SCAN | INDX_COMMON_FSC | 2 | | 1 (0)|
|* 31 | TABLE ACCESS BY INDEX ROWID | ADM_USERS | 1 | 26 | 1 (0)|
|* 32 | INDEX UNIQUE SCAN | PK_USER_ID | 1 | | 0 (0)|
|* 33 | TABLE ACCESS BY INDEX ROWID | DM_OPPORTUNITY_PRIORITY_DETAIL | 1 | 19 | 2 (0)
|* 34 | INDEX RANGE SCAN | PK_DM_OPP_PRIORITY_DTL | 1 | | 1 (0)|
|* 35 | INDEX RANGE SCAN | PK_IDX_LOGIN | 89 | | 1 (0)|
| 36 | BUFFER SORT | | 76 | 1216 | 29 (0)|
| 37 | INDEX FULL SCAN | PK_IDX_POLICY | 76 | 1216 | 1 (0)|
|* 38 | INDEX RANGE SCAN | PK_IDX | 1 | | 0 (0)|
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("RANK"=1)
3 - filter(RANK() OVER ( ORDER BY "PRIORITY")<=1)
4 - filter("AVAILABLE_WIP">0)
10 - filter("WIP"."OPPORTUNITY_TYPE_ID"=127)
13 - filter(SYSDATE@!-"LOGIN"."CREATION_DATE"<=30 AND "LOGIN"."ACTION"='LOGIN_SUCCESSFULL')
22 - filter(TO_NUMBER("P"."PINCODE")=560001)
23 - access("P"."PINCODE_ID"="C"."PINCODE_ID")
24 - filter("CM"."OPERATION_TYPE_ID"=1)
25 - access("C"."CLUSTER_ID"="CM"."CLUSTER_ID")
26 - access("CM"."CLUSTER_ID"="APC"."CLUSTER_ID")
27 - filter("APM"."OPERATION_ID"=1 AND "APM"."IS_ACTIVE"=1)
28 - access("APM"."PROFILE_ID"="APC"."PROFILE_ID")
29 - filter("OPP"."OPPORTUNITY_TYPE_ID"(+)=127)
30 - access("APM"."USER_ID"="OPP"."ANH_FSC_CODE"(+))
filter("OPP"."ANH_FSC_CODE"(+) IS NOT NULL)
31 - filter("AU"."SUBROLE_TYPE_ID" IS NOT NULL AND "AU"."IS_ACTIVE"=1)
32 - access("APM"."USER_ID"="AU"."USER_ID")
33 - filter("PR"."ACTIVE_FLAG"=1)
34 - access("PR"."OPPORTUNITY_TYPE_ID"=127 AND "AU"."SUBROLE_TYPE_ID"="PR"."SUB_ROLE_ID")
filter("AU"."SUBROLE_TYPE_ID"="PR"."SUB_ROLE_ID")
35 - access("LOGIN"."LOGIN_NAME"="APM"."USER_ID")
38 - access("POL"."POLICY_ID"="WIP"."POLICY_ID")
Note
-----
- 'PLAN_TABLE' is old version
74 rows selected.
Please give some advice so that I can optimize it.
Regards,
Mahi
|
|
|
|
| Re: Query running slow [message #483064 is a reply to message #483062] |
Thu, 18 November 2010 01:13  |
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
|
Senior Member |
|
|
Try rewriting the query.
There could be two possibilities of having cartesian,
1. when there is no joning of tables
2. When optimizer drops the filter(due to transitive closure).
You dont need Distinct when using group by.
Regards
Ved
[Updated on: Thu, 18 November 2010 01:21] Report message to a moderator |
|
|
|
| Re: Query running slow [message #483068 is a reply to message #483062] |
Thu, 18 November 2010 01:41 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
How many rows are there in the pincode_master table? It seems a bit odd that it will do FTS if you enter a pincode.
What indexes do you have on that table?
Are all statistics up to date?
|
|
|
|
|
|
| Re: Query running slow [message #483075 is a reply to message #483062] |
Thu, 18 November 2010 02:26 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
There are places where you have an index scan followed by a table access with a filter, for instance steps 25 and 24. You could try appending the filter column OPERATION_TYPE_ID to the index SYS_C004094, and then the optimizer could combine the steps? I might be completely wrong, because I don't know how your tables and indexes and constraints are designed, but that is the sort of thing I would try.
|
|
|
|
| Re: Query running slow [message #483081 is a reply to message #483075] |
Thu, 18 November 2010 04:38 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You've got rank at two different levels. Looks to me like the second one does nothing useful. Removing it should save some time.
Also do you really think aliasing columns to the same name as oracle functions is a good idea? (rank again)
|
|
|
|
| Re: Query running slow [message #483084 is a reply to message #483062] |
Thu, 18 November 2010 04:49 |
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
|
Senior Member |
|
|
AND p.pincode = 560001 --v_meeting_pin
Predicate Information
------------------------
22 - filter(TO_NUMBER("P"."PINCODE")=560001)
What is the data type of column pincode and v_meeting_pin?
If I am not wrong, The above is the reason for optimizer not to choose the index.
It appears that the query can be rewritten for better throughput. But its you who have idea about the data in your application.
Regards
Ved
[Updated on: Thu, 18 November 2010 04:56] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
