There was a performance issue in our system.
I isolated the offending query and was able to tune it but am curious as to why such a dramatic difference.
The only difference in the select statements is the 2nd query adds the count(*) to the select list.
The 1st query takes minutes to run, the 2nd query runs in about 3 seconds.
I obviously will use the 2nd query but am very curious.
Query 1 (The bad one).
SELECT plan_id,plan_updt_no, woi
FROM pwui_oh_plan_v a
where engine_type = :vi_engine_type
AND engine_model = :vi_engine_model
AND module_code = :vi_module_code
AND work_scope = :vi_work_scope
AND plnd_work_loc = :vi_work_loc
AND SYSDATE >= eff_from_date
AND SYSDATE <=
NVL(eff_thru_date,TO_DATE(Sffnd_Date_Get('MaxDate'),
Sffnd_Date_Get('DateMask')))
AND woi is not null
AND act_no||nvl(sub_act_no,'0000') in
(
select act_no||nvl(sub_act_no,'0000')
from pwui_int_sap_order
where sales_order = :vi_sales_order and
engine_type = :vi_engine_type and
engine_model = :vi_engine_model and
module_code = :vi_module_code and
work_scope = :vi_work_scope)
AND (upper(cust_id) = upper(:vi_customer)
OR (upper(cust_id) = 'ALL'
and upper(:vi_customer) not in (select upper(e.exception_cust_id)
from PWUI_DTL_PL_CUST_EXCEPTIONS e
where e.plan_id = a.plan_id and
e.plan_updt_no = a.plan_updt_no and
e.subject_no = a.subject_no
and e.cust_id = a.cust_id) -- 0003
)
)
group by plan_id,plan_updt_no, woi
Here is the explain plan:
SELECT STATEMENT, GOAL = ALL_ROWS Cost=10 Cardinality=1 Bytes=557
SORT GROUP BY Cost=10 Cardinality=1 Bytes=557
FILTER
NESTED LOOPS Cost=8 Cardinality=1 Bytes=557
MERGE JOIN CARTESIAN Cost=8 Cardinality=1 Bytes=535
MERGE JOIN CARTESIAN Cost=6 Cardinality=1 Bytes=506
NESTED LOOPS Cost=6 Cardinality=1 Bytes=459
FILTER
NESTED LOOPS OUTER
NESTED LOOPS Cost=6 Cardinality=1 Bytes=331
NESTED LOOPS Cost=6 Cardinality=1 Bytes=266
MERGE JOIN CARTESIAN Cost=6 Cardinality=1 Bytes=201
NESTED LOOPS Cost=4 Cardinality=1 Bytes=127
MERGE JOIN CARTESIAN Cost=4 Cardinality=1 Bytes=89
TABLE ACCESS FULL Object owner=SFMFG Object name=PWUI_INT_SAP_ORDER Cost=2 Cardinality=1 Bytes=47
BUFFER SORT Cost=2 Cardinality=1 Bytes=42
TABLE ACCESS FULL Object owner=SFMFG Object name=PW_SFPL_PLAN_DESC_EXT Cost=2 Cardinality=1 Bytes=42
TABLE ACCESS BY INDEX ROWID Object owner=SFMFG Object name=SFPL_PLAN_DESC Cardinality=1 Bytes=38
INDEX UNIQUE SCAN Object owner=SFMFG Object name=SFPL_PLAN_DESC_PK Cardinality=1
BUFFER SORT Cost=6 Cardinality=1 Bytes=74
TABLE ACCESS FULL Object owner=SFMFG Object name=PW_SFPL_PLAN_SUBJECT Cost=2 Cardinality=1 Bytes=74
TABLE ACCESS BY INDEX ROWID Object owner=SFMFG Object name=PWUI_DTL_PL_WORK_SCOPE Cardinality=1 Bytes=65
INDEX UNIQUE SCAN Object owner=SFMFG Object name=PWUI_DTL_PL_WORK_SCOPE_PK Cardinality=1
TABLE ACCESS BY INDEX ROWID Object owner=SFMFG Object name=PWUI_DTL_PL_NETWORK_ACTIVITY Cardinality=1 Bytes=65
INDEX RANGE SCAN Object owner=SFMFG Object name=PWUI_DTL_PL_NETWORKACTIVITY_PK Cardinality=1
INDEX RANGE SCAN Object owner=SFMFG Object name=PWUI_DTL_PL_SUBNETACTIVITY_PK Cardinality=1 Bytes=58
INDEX RANGE SCAN Object owner=SFMFG Object name=PWUI_DTL_PL_CUSTOMER_PK Cardinality=1 Bytes=70
BUFFER SORT Cost=6 Cardinality=1 Bytes=47
INDEX FULL SCAN Object owner=SFMFG Object name=PWUI_PL_DTL_ENGINE_MODEL_PK Cardinality=1 Bytes=47
BUFFER SORT Cost=8 Cardinality=3 Bytes=87
TABLE ACCESS FULL Object owner=SFMFG Object name=SFPL_PLAN_EFF Cost=2 Cardinality=3 Bytes=87
TABLE ACCESS BY INDEX ROWID Object owner=SFMFG Object name=SFPL_PLAN_REV Cardinality=1 Bytes=22
INDEX UNIQUE SCAN Object owner=SFMFG Object name=SFPL_PLAN_REV_PK Cardinality=1
INDEX RANGE SCAN Object owner=SFMFG Object name=PWUI_DTL_PL_CUST_EXCEPTIONS_PK Cost=1 Cardinality=1 Bytes=23
Here is the explain plan from the "good" query with the only difference being count(*) added to the select list.
SELECT STATEMENT, GOAL = ALL_ROWS Cost=12 Cardinality=1 Bytes=518
SORT GROUP BY Cost=12 Cardinality=1 Bytes=518
FILTER
HASH JOIN Cost=11 Cardinality=1 Bytes=518
NESTED LOOPS Cost=6 Cardinality=1 Bytes=510
NESTED LOOPS OUTER Cost=4 Cardinality=1 Bytes=481
NESTED LOOPS Cost=4 Cardinality=1 Bytes=423
NESTED LOOPS Cost=4 Cardinality=1 Bytes=358
NESTED LOOPS Cost=4 Cardinality=1 Bytes=288
NESTED LOOPS Cost=4 Cardinality=1 Bytes=223
NESTED LOOPS Cost=3 Cardinality=1 Bytes=149
NESTED LOOPS Cost=3 Cardinality=1 Bytes=107
NESTED LOOPS Cost=2 Cardinality=1 Bytes=69
INDEX SKIP SCAN Object owner=SFMFG Object name=PWUI_PL_DTL_ENGINE_MODEL_PK Cardinality=1 Bytes=47
INDEX RANGE SCAN Object owner=SFMFG Object name=SFPL_PLAN_REV_INDX001 Cost=1 Cardinality=1 Bytes=22
TABLE ACCESS BY INDEX ROWID Object owner=SFMFG Object name=SFPL_PLAN_DESC Cost=1 Cardinality=1 Bytes=38
INDEX UNIQUE SCAN Object owner=SFMFG Object name=SFPL_PLAN_DESC_PK Cardinality=1
TABLE ACCESS BY INDEX ROWID Object owner=SFMFG Object name=PW_SFPL_PLAN_DESC_EXT Cardinality=1 Bytes=42
INDEX UNIQUE SCAN Object owner=SFMFG Object name=PW_SFPL_PLAN_DESC_EXT_PK Cardinality=1
TABLE ACCESS BY INDEX ROWID Object owner=SFMFG Object name=PW_SFPL_PLAN_SUBJECT Cost=1 Cardinality=1 Bytes=74
INDEX RANGE SCAN Object owner=SFMFG Object name=PW_SFPL_PLAN_SUBJECT_PK Cost=1 Cardinality=1
TABLE ACCESS BY INDEX ROWID Object owner=SFMFG Object name=PWUI_DTL_PL_WORK_SCOPE Cardinality=1 Bytes=65
INDEX UNIQUE SCAN Object owner=SFMFG Object name=PWUI_DTL_PL_WORK_SCOPE_PK Cardinality=1
INDEX RANGE SCAN Object owner=SFMFG Object name=PWUI_DTL_PL_CUSTOMER_PK Cardinality=1 Bytes=70
TABLE ACCESS BY INDEX ROWID Object owner=SFMFG Object name=PWUI_DTL_PL_NETWORK_ACTIVITY Cardinality=1 Bytes=65
INDEX RANGE SCAN Object owner=SFMFG Object name=PWUI_DTL_PL_NETWORKACTIVITY_PK Cardinality=1
INDEX RANGE SCAN Object owner=SFMFG Object name=PWUI_DTL_PL_SUBNETACTIVITY_PK Cardinality=1 Bytes=58
TABLE ACCESS BY INDEX ROWID Object owner=SFMFG Object name=SFPL_PLAN_EFF Cost=2 Cardinality=1 Bytes=29
INDEX RANGE SCAN Object owner=SFMFG Object name=SFPL_PLAN_EFF_PK Cost=1 Cardinality=1
VIEW Object owner=SYS Object name=VW_NSO_1 Cost=4 Cardinality=1 Bytes=8
SORT UNIQUE Cost=4 Cardinality=1 Bytes=47
TABLE ACCESS FULL Object owner=SFMFG Object name=PWUI_INT_SAP_ORDER Cost=2 Cardinality=1 Bytes=47
INDEX RANGE SCAN Object owner=SFMFG Object name=PWUI_DTL_PL_CUST_EXCEPTIONS_PK Cost=1 Cardinality=1 Bytes=23
Sorry for the length of this stuff.
[Updated on: Wed, 14 November 2007 10:38]
Report message to a moderator
] 
Blog
Search
Help
Members
Register
Login
Home
