How to tune this query - it involves MView full scan and takes more than 2.5 hrs to finish
Hi DBAs:
Here is the query statement -:
Select REQ_ORDER_DATE,decode(REQ_DX_FLAG,1,'DX',' ') DX, 'SP' || REQ_OID ORDER_NUM,REQ_SVC_START,order_stat(req_status) ORDER_STAT, valid_response DATE_VALID_RESP, SVC_CAT_NAME, SVC_PROV_NAME, "RES_FIRST_NAME" || ' ' || "RES_LAST_NAME" RES_NAME, RES_EMAIL, RES_PHONE, WORK_PHONE, FLR.SERVICE_ADDRESS SERVICE_ADDRESS, FLR.RES_EXT_SYSTEM_ID, APT_NUMBER, SVC_ADDRESS_ZIPCODE, cmn.NAME CMN_NAME, nvl("SUBMIT_AGENT_FIRST_NAME",' ') || ' ' || nvl("SUBMIT_AGENT_LAST_NAME", ' ') SUBMIT_AGENT_NAME, "AGENT_FIRST_NAME" || ' ' || "AGENT_LAST_NAME" AGENT_NAME, decode(SUBMIT_AGENT_OID,null,' ',ua.USERNAME) USERNAME, REQ_PAYMENT_METHOD, MARKET_NICK_NAME, SVC_ADDRESS_STATE, 'Orders' ORDERS FROM FLAT_REQUEST, customer, (select service_address, leaseres_oid LEASERESIDENT_OID, RES_EXT_SYSTEM_ID, CM_OID FLR_CM_OID from flat_lease_res) FLR, cm_user cmu, user_perm_set ups, useraccount ua, configuration_manager_new cmn where customer.oid_useraccount = flat_request.user_account_oid and REQ_STATUS >=0 and req_TYPE=1 and FLAT_REQUEST.LEASERES_OID = FLR.LEASERESIDENT_OID and flat_request.CM_OID = FLR.FLR_CM_OID and nvl(flat_request.SUBMIT_AGENT_OID, flat_request.AGENT_OID) = cmu.oid and cmu.OID_PERMSET
The following query gives us a headache and I'm not very sure if it can be tunned or not - it takes more than 2.5 hours to finish; 2 "MAT_VIEW ACCESS FULL" in its execution plan. However, both are unique 'oid' in Mviews and doesn't make sense to create index on them.
Here is the query statement -:
Select REQ_ORDER_DATE,decode(REQ_DX_FLAG,1,'DX',' ') DX, 'SP' || REQ_OID ORDER_NUM,REQ_SVC_START,order_stat(req_status) ORDER_STAT, valid_response DATE_VALID_RESP, SVC_CAT_NAME, SVC_PROV_NAME, "RES_FIRST_NAME" || ' ' || "RES_LAST_NAME" RES_NAME, RES_EMAIL, RES_PHONE, WORK_PHONE, FLR.SERVICE_ADDRESS SERVICE_ADDRESS, FLR.RES_EXT_SYSTEM_ID, APT_NUMBER, SVC_ADDRESS_ZIPCODE, cmn.NAME CMN_NAME, nvl("SUBMIT_AGENT_FIRST_NAME",' ') || ' ' || nvl("SUBMIT_AGENT_LAST_NAME", ' ') SUBMIT_AGENT_NAME, "AGENT_FIRST_NAME" || ' ' || "AGENT_LAST_NAME" AGENT_NAME, decode(SUBMIT_AGENT_OID,null,' ',ua.USERNAME) USERNAME, REQ_PAYMENT_METHOD, MARKET_NICK_NAME, SVC_ADDRESS_STATE, 'Orders' ORDERS FROM FLAT_REQUEST, customer, (select service_address, leaseres_oid LEASERESIDENT_OID, RES_EXT_SYSTEM_ID, CM_OID FLR_CM_OID from flat_lease_res) FLR, cm_user cmu, user_perm_set ups, useraccount ua, configuration_manager_new cmn where customer.oid_useraccount = flat_request.user_account_oid and REQ_STATUS >=0 and req_TYPE=1 and FLAT_REQUEST.LEASERES_OID = FLR.LEASERESIDENT_OID and flat_request.CM_OID = FLR.FLR_CM_OID and nvl(flat_request.SUBMIT_AGENT_OID, flat_request.AGENT_OID) = cmu.oid and cmu.OID_PERMSET
0