Home » RDBMS Server » Performance Tuning » Slow Running Query
| Slow Running Query [message #482185] |
Wed, 10 November 2010 04:36  |
sameek1211
Messages: 30
Registered: October 2007
Location: India
|
Member |
|
|
dear all,
i am facing a problem running the following query.although my explain plan shows 99% usage of indexes but still the query take about 5-6 minutes in giving output.i am not able to find the bottlenck .
Please help
Regards
Sameek
i m using oracle 10gR2 10.2.0.1.0
below is the code of the query:
select distinct pm.party_code,
pm.party_name,
pm.party_code status_cust,
pm.defaultpurchaser
from pr_indent_det id_det,
pr_indent_mast id_mast,
item itm,
employee emp,
( select poi.indent_no,
poi.indent_date,
poi.item_code,
poi.qty_ord - nvl (t.extended_qty, 0) qty_ord
from purchase_order po,
purchase_order_item poi,
( select po_no,
po_date,
item_code,
sum (nvl (extended_qty, 0)) extended_qty,
indent_no,
indent_date
from tbl_po_adjustment
group by po_no,
po_date,
item_code,
indent_no,
indent_date) t
where po.po_no = poi.po_no
and po.po_date = poi.po_date
and poi.po_no = t.po_no(+)
and poi.po_date = t.po_date(+)
and poi.indent_no = t.indent_no(+)
and poi.indent_date = t.indent_date(+)
and poi.item_code = t.item_code(+)
and po.canc_status = 'n'
and po.ccode = 'al'
and nvl (poi.approval_status, 'a') = 'a'
order by 2 desc) poi,
party_master pm,
account a,
(select q.quot_comp_no,
q.quot_comp_date,
q.supp_code,
item.part_no,
item.ccode
from quot_comparison_dtl q,
quot_comparison_mst qm,
( select max (q.quot_comp_no) quot_comp_no,
q.quot_comp_date,
q.item_code,
m.ccode
from quot_comparison_dtl q,
quot_comparison_mst m,
( select max (qm.quot_comp_date) quot_comp_date,
item_code,
ccode
from quot_comparison_dtl qd,
quot_comparison_mst qm
where qm.quot_comp_no = qd.quot_comp_no
and qm.quot_comp_date =
qd.quot_comp_date
and qm.ccode = 'al'
group by qd.item_code, qm.ccode) max_date
where m.quot_comp_no = q.quot_comp_no
and m.quot_comp_date = q.quot_comp_date
and q.quot_comp_date = max_date.quot_comp_date
and q.item_code = max_date.item_code
and m.ccode = 'al'
group by q.quot_comp_date, q.item_code, m.ccode) max_no,
item
where qm.quot_comp_no = q.quot_comp_no
and qm.quot_comp_date = q.quot_comp_date
and qm.ccode = 'al'
and q.quot_comp_no = max_no.quot_comp_no
and q.quot_comp_date = max_no.quot_comp_date
and q.item_code = max_no.item_code
and supp_type = 'v1'
and q.item_code = item.item_code
and item.ccode = 'al') ivd
where id_mast.indent_no = id_det.indent_no
and a.supp_code = pm.party_code
and a.ccode = pm.ccode
and a.freeze = 'n'
and id_mast.indent_date = id_det.indent_date
and id_det.item_code = itm.item_code
and id_mast.ccode = 'al'
and itm.ccode = 'al'
and id_mast.approval_status = 'a'
and id_mast.fycode = '10-11'
and id_mast.employee_code = emp.employee_code
and itm.part_no = ivd.part_no
and ivd.supp_code = pm.party_code
and id_det.indent_no = poi.indent_no(+)
and id_det.indent_date = poi.indent_date(+)
and id_det.item_code = poi.item_code(+)
and itm.part_no in (select part_no
from item
where store_code = '0001')
and itm.store_code = '0001'
group by id_det.ind_seq_no,
id_det.indent_no,
id_det.indent_date,
id_det.item_code,
id_det.item_rate,
id_det.ind_qty,
id_mast.employee_code,
itm.item_name,
emp.employee_name,
id_det.ind_purpose,
ivd.supp_code,
itm.store_code,
pm.party_code,
pm.party_name,
pm.defaultpurchaser
having (id_det.ind_qty - nvl (sum (poi.qty_ord), 0)) > 0
order by 2
the explain plan for the query is as :
Execution Plan
----------------------------------------------------------
Plan hash value: 4223086863
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 443 | | 146 (4)| 00:00:02 |
| 1 | SORT UNIQUE | | 1 | 443 | | 146 (4)| 00:00:02 |
|* 2 | FILTER | | | | | | |
| 3 | HASH GROUP BY | | 1 | 443 | | 146 (4)| 00:00:02 |
| 4 | NESTED LOOPS OUTER | | 1 | 443 | | 145 (3)| 00:00:02 |
| 5 | NESTED LOOPS | | 1 | 418 | | 39 (6)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 393 | | 38 (6)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 351 | | 37 (6)| 00:00:01 |
| 8 | NESTED LOOPS SEMI | | 1 | 260 | | 35 (6)| 00:00:01 |
| 9 | NESTED LOOPS | | 1 | 247 | | 34 (6)| 00:00:01 |
| 10 | NESTED LOOPS | | 1 | 178 | | 33 (7)| 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 154 | | 32 (7)| 00:00:01 |
| 12 | NESTED LOOPS | | 1 | 147 | | 31 (7)| 00:00:01 |
| 13 | NESTED LOOPS | | 1 | 105 | | 30 (7)| 00:00:01 |
| 14 | NESTED LOOPS | | 1 | 81 | | 29 (7)| 00:00:01 |
| 15 | VIEW | | 1 | 33 | | 28 (8)| 00:00:01 |
| 16 | HASH GROUP BY | | 1 | 34 | | 28 (8)| 00:00:01 |
| 17 | VIEW | | 1 | 34 | | 27 (4)| 00:00:01 |
|* 18 | FILTER | | | | | | |
| 19 | HASH GROUP BY | | 1 | 142 | | 27 (4)| 00:00:01 |
| 20 | NESTED LOOPS | | 1 | 142 | | 26 (0)| 00:00:01 |
| 21 | NESTED LOOPS | | 1 | 107 | | 25 (0)| 00:00:01 |
| 22 | MERGE JOIN CARTESIAN| | 1 | 60 | | 24 (0)| 00:00:01 |
|* 23 | INDEX FULL SCAN | IDX_QUOT_COMP_MST | 1 | 36 | | 12 (0)| 00:00:01 |
| 24 | BUFFER SORT | | 1 | 24 | | 12 (0)| 00:00:01 |
|* 25 | INDEX FULL SCAN | IDX_QUOT_COMP_MST | 1 | 24 | | 12 (0)| 00:00:01 |
|* 26 | INDEX RANGE SCAN | PK_COMPNO_COMPDT_ITEM | 4 | 188 | | 1 (0)| 00:00:01 |
|* 27 | INDEX RANGE SCAN | PK_COMPNO_COMPDT_ITEM | 1 | 35 | | 1 (0)| 00:00:01 |
|* 28 | TABLE ACCESS BY INDEX ROWID| QUOT_COMPARISON_DTL | 1 | 48 | | 1 (0)| 00:00:01 |
|* 29 | INDEX RANGE SCAN | IDX_QUOT_COMP_DTL | 1 | | | 1 (0)| 00:00:01 |
|* 30 | INDEX RANGE SCAN | IDX_QUOT_COMP_MST | 1 | 24 | | 1 (0)| 00:00:01 |
| 31 | TABLE ACCESS BY INDEX ROWID | PARTY_MASTER | 1 | 42 | | 1 (0)| 00:00:01 |
|* 32 | INDEX UNIQUE SCAN | PK_PARTY_CODE | 1 | | | 1 (0)| 00:00:01 |
|* 33 | TABLE ACCESS BY INDEX ROWID | ACCOUNT | 1 | 7 | | 1 (0)| 00:00:01 |
|* 34 | INDEX RANGE SCAN | IDX_ACCT_SUPP_ACODE | 1 | | | 1 (0)| 00:00:01 |
| 35 | TABLE ACCESS BY INDEX ROWID | ITEM | 1 | 24 | | 1 (0)| 00:00:01 |
|* 36 | INDEX UNIQUE SCAN | PK_ITEM | 1 | | | 1 (0)| 00:00:01 |
|* 37 | TABLE ACCESS BY INDEX ROWID | ITEM | 1 | 69 | | 1 (0)| 00:00:01 |
|* 38 | INDEX RANGE SCAN | INDX_PART_NO | 2 | | | 1 (0)| 00:00:01 |
|* 39 | TABLE ACCESS BY INDEX ROWID | ITEM | 27768 | 352K| | 1 (0)| 00:00:01 |
|* 40 | INDEX RANGE SCAN | IDX_ITEM_STORE_CODE | 1 | | | 1 (0)| 00:00:01 |
| 41 | TABLE ACCESS BY INDEX ROWID | PR_INDENT_DET | 11 | 1001 | | 2 (0)| 00:00:01 |
|* 42 | INDEX RANGE SCAN | IDX$$_46570001 | 11 | | | 1 (0)| 00:00:01 |
|* 43 | TABLE ACCESS BY INDEX ROWID | PR_INDENT_MAST | 1 | 42 | | 1 (0)| 00:00:01 |
|* 44 | INDEX UNIQUE SCAN | PK_PR_INDENT_MAST | 1 | | | 1 (0)| 00:00:01 |
| 45 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 1 | 25 | | 1 (0)| 00:00:01 |
|* 46 | INDEX UNIQUE SCAN | PK_EMPLOYEE | 1 | | | 1 (0)| 00:00:01 |
| 47 | VIEW PUSHED PREDICATE | | 1 | 25 | | 106 (2)| 00:00:02 |
|* 48 | HASH JOIN OUTER | | 1 | 181 | | 106 (2)| 00:00:02 |
| 49 | NESTED LOOPS | | 1 | 123 | | 2 (0)| 00:00:01 |
|* 50 | TABLE ACCESS BY INDEX ROWID | PURCHASE_ORDER_ITEM | 1 | 97 | | 1 (0)| 00:00:01 |
|* 51 | INDEX RANGE SCAN | IDX$$_46570002 | 1 | | | 1 (0)| 00:00:01 |
|* 52 | TABLE ACCESS BY INDEX ROWID | PURCHASE_ORDER | 1 | 26 | | 1 (0)| 00:00:01 |
|* 53 | INDEX UNIQUE SCAN | PK_PO | 1 | | | 1 (0)| 00:00:01 |
|* 54 | VIEW | | 7017 | 397K| | 104 (2)| 00:00:02 |
| 55 | SORT GROUP BY | | 7017 | 294K| 840K| 104 (2)| 00:00:02 |
| 56 | TABLE ACCESS FULL | TBL_PO_ADJUSTMENT | 7017 | 294K| | 24 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID_DET"."IND_QTY"-NVL(SUM("POI"."QTY_ORD"),0)>0)
18 - filter("Q"."QUOT_COMP_DATE"=MAX("QM"."QUOT_COMP_DATE"))
23 - access("M"."CCODE"='al')
filter("M"."CCODE"='al')
25 - access("QM"."CCODE"='al')
filter("QM"."CCODE"='al')
26 - access("M"."QUOT_COMP_NO"="Q"."QUOT_COMP_NO" AND "M"."QUOT_COMP_DATE"="Q"."QUOT_COMP_DATE")
27 - access("QM"."QUOT_COMP_NO"="QD"."QUOT_COMP_NO" AND "QM"."QUOT_COMP_DATE"="QD"."QUOT_COMP_DATE" AND
"Q"."ITEM_CODE"="ITEM_CODE")
filter("Q"."ITEM_CODE"="ITEM_CODE")
28 - filter("SUPP_TYPE"='v1' AND "Q"."ITEM_CODE"="MAX_NO"."ITEM_CODE")
29 - access("Q"."QUOT_COMP_NO"="MAX_NO"."QUOT_COMP_NO" AND "Q"."QUOT_COMP_DATE"="MAX_NO"."QUOT_COMP_DATE")
30 - access("QM"."QUOT_COMP_NO"="Q"."QUOT_COMP_NO" AND "QM"."QUOT_COMP_DATE"="Q"."QUOT_COMP_DATE" AND
"QM"."CCODE"='al')
32 - access("Q"."SUPP_CODE"="PM"."PARTY_CODE")
33 - filter("A"."FREEZE"='n' AND "A"."CCODE"="PM"."CCODE")
34 - access("A"."SUPP_CODE"="PM"."PARTY_CODE")
filter("A"."SUPP_CODE" IS NOT NULL)
36 - access("Q"."ITEM_CODE"="ITEM"."ITEM_CODE" AND "ITEM"."CCODE"='al')
37 - filter("ITM"."CCODE"='al' AND "ITM"."STORE_CODE"='0001')
38 - access("ITM"."PART_NO"="ITEM"."PART_NO")
39 - filter("ITM"."PART_NO"="PART_NO")
40 - access("STORE_CODE"='0001')
42 - access("ID_DET"."ITEM_CODE"="ITM"."ITEM_CODE")
43 - filter("ID_MAST"."CCODE"='al' AND "ID_MAST"."FYCODE"='10-11' AND "ID_MAST"."APPROVAL_STATUS"='a')
44 - access("ID_MAST"."INDENT_NO"="ID_DET"."INDENT_NO" AND "ID_MAST"."INDENT_DATE"="ID_DET"."INDENT_DATE")
46 - access("ID_MAST"."EMPLOYEE_CODE"="EMP"."EMPLOYEE_CODE")
48 - access("POI"."PO_NO"="T"."PO_NO"(+) AND "POI"."PO_DATE"="T"."PO_DATE"(+) AND
"POI"."INDENT_NO"="T"."INDENT_NO"(+) AND "POI"."INDENT_DATE"="T"."INDENT_DATE"(+) AND
"POI"."ITEM_CODE"="T"."ITEM_CODE"(+))
50 - filter("POI"."INDENT_NO"="ID_DET"."INDENT_NO" AND "POI"."INDENT_DATE"="ID_DET"."INDENT_DATE")
51 - access("POI"."ITEM_CODE"="ID_DET"."ITEM_CODE")
filter(NVL("POI"."APPROVAL_STATUS",'a')='a')
52 - filter("PO"."CCODE"='al' AND "PO"."CANC_STATUS"='n')
53 - access("PO"."PO_NO"="POI"."PO_NO" AND "PO"."PO_DATE"="POI"."PO_DATE")
54 - filter("T"."ITEM_CODE"(+)="ID_DET"."ITEM_CODE" AND "T"."INDENT_DATE"(+)="ID_DET"."INDENT_DATE" AND
"T"."INDENT_NO"(+)="ID_DET"."INDENT_NO")
CM: removed lots of excess white space.
[Updated on: Wed, 10 November 2010 04:57] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
| Re: Slow Running Query [message #482196 is a reply to message #482191] |
Wed, 10 November 2010 05:07  |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
I'm getting an odd sense of deja vu. Is narang a colleague of yours?
Is that explain plan taken from the DB that's running slow?
If yes are the statistics up to date on that DB?
Should the query really have some bind variables in it (narangs version indicates it does)? If so try getting the explain plan with binds in.
So instead of:
and itm.store_code ='0001'
Do
and itm.store_code = :bind
EDIT: fixed tags
[Updated on: Wed, 10 November 2010 05:08] Report message to a moderator |
|
|
|
|
|
| Re: Slow Running Query [message #482199 is a reply to message #482197] |
Wed, 10 November 2010 05:30 |
sameek1211
Messages: 30
Registered: October 2007
Location: India
|
Member |
|
|
the new explain plan is :
Plan hash value: 249547396
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 441 | | 303 (4)| 00:00:04 |
| 1 | SORT UNIQUE | | 1 | 441 | | 303 (4)| 00:00:04 |
|* 2 | FILTER | | | | | | |
| 3 | HASH GROUP BY | | 1 | 441 | | 303 (4)| 00:00:04 |
| 4 | NESTED LOOPS OUTER | | 1 | 441 | | 302 (3)| 00:00:04 |
| 5 | NESTED LOOPS | | 1 | 416 | | 195 (4)| 00:00:03 |
| 6 | NESTED LOOPS | | 1 | 391 | | 194 (4)| 00:00:03 |
| 7 | NESTED LOOPS | | 1 | 349 | | 193 (4)| 00:00:03 |
| 8 | NESTED LOOPS SEMI | | 1 | 258 | | 191 (4)| 00:00:03 |
| 9 | NESTED LOOPS | | 1 | 245 | | 190 (4)| 00:00:03 |
| 10 | NESTED LOOPS | | 1 | 176 | | 189 (4)| 00:00:03 |
| 11 | NESTED LOOPS | | 1 | 152 | | 188 (4)| 00:00:03 |
| 12 | NESTED LOOPS | | 1 | 145 | | 187 (4)| 00:00:03 |
| 13 | NESTED LOOPS | | 10 | 1030 | | 185 (4)| 00:00:03 |
| 14 | NESTED LOOPS | | 42 | 3318 | | 177 (4)| 00:00:03 |
| 15 | VIEW | | 129 | 3999 | | 125 (5)| 00:00:02 |
| 16 | HASH GROUP BY | | 129 | 4386 | | 125 (5)| 00:00:02 |
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------
| 17 | VIEW | | 256 | 8704 | | 124 (5)| 00:00:02 |
|* 18 | FILTER | | | | | | |
| 19 | HASH GROUP BY | | 256 | 36352 | | 124 (5)| 00:00:02 |
|* 20 | HASH JOIN | | 26072 | 3615K| | 122 (3)| 00:00:02 |
|* 21 | INDEX FULL SCAN | IDX_QUOT_COMP_MST | 2949 | 103K| | 12 (0)| 00:00:01 |
|* 22 | HASH JOIN | | 104K| 10M| | 109 (2)| 00:00:02 |
| 23 | NESTED LOOPS | | 12686 | 730K| | 13 (0)| 00:00:01 |
|* 24 | INDEX FULL SCAN | IDX_QUOT_COMP_MST | 2949 | 70776 | | 12 (0)| 00:00:01 |
|* 25 | INDEX RANGE SCAN | PK_COMPNO_COMPDT_ITEM | 4 | 140 | | 1 (0)| 00:00:01 |
| 26 | INDEX FULL SCAN | PK_COMPNO_COMPDT_ITEM | 50745 | 2329K| | 94 (0)| 00:00:02 |
|* 27 | TABLE ACCESS BY INDEX ROWID| QUOT_COMPARISON_DTL | 1 | 48 | | 1 (0)| 00:00:01 |
|* 28 | INDEX RANGE SCAN | IDX_QUOT_COMP_DTL | 1 | | | 1 (0)| 00:00:01 |
|* 29 | INDEX RANGE SCAN | IDX_QUOT_COMP_MST | 1 | 24 | | 1 (0)| 00:00:01 |
| 30 | TABLE ACCESS BY INDEX ROWID | PARTY_MASTER | 1 | 42 | | 1 (0)| 00:00:01 |
|* 31 | INDEX UNIQUE SCAN | PK_PARTY_CODE | 1 | | | 1 (0)| 00:00:01 |
|* 32 | TABLE ACCESS BY INDEX ROWID | ACCOUNT | 1 | 7 | | 1 (0)| 00:00:01 |
|* 33 | INDEX RANGE SCAN | IDX_ACCT_SUPP_ACODE | 1 | | | 1 (0)| 00:00:01 |
| 34 | TABLE ACCESS BY INDEX ROWID | ITEM | 1 | 24 | | 1 (0)| 00:00:01 |
|* 35 | INDEX UNIQUE SCAN | PK_ITEM | 1 | | | 1 (0)| 00:00:01 |
|* 36 | TABLE ACCESS BY INDEX ROWID | ITEM | 1 | 69 | | 1 (0)| 00:00:01 |
|* 37 | INDEX RANGE SCAN | INDX_PART_NO | 2 | | | 1 (0)| 00:00:01 |
|* 38 | TABLE ACCESS BY INDEX ROWID | ITEM | 7720 | 98K| | 1 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------
|* 39 | INDEX RANGE SCAN | IDX_ITEM_STORE_CODE | 4 | | | 1 (0)| 00:00:01 |
| 40 | TABLE ACCESS BY INDEX ROWID | PR_INDENT_DET | 11 | 1001 | | 2 (0)| 00:00:01 |
|* 41 | INDEX RANGE SCAN | IDX$$_46570001 | 11 | | | 1 (0)| 00:00:01 |
|* 42 | TABLE ACCESS BY INDEX ROWID | PR_INDENT_MAST | 1 | 42 | | 1 (0)| 00:00:01 |
|* 43 | INDEX UNIQUE SCAN | PK_PR_INDENT_MAST | 1 | | | 1 (0)| 00:00:01 |
| 44 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 1 | 25 | | 1 (0)| 00:00:01 |
|* 45 | INDEX UNIQUE SCAN | PK_EMPLOYEE | 1 | | | 1 (0)| 00:00:01 |
| 46 | VIEW PUSHED PREDICATE | | 1 | 25 | | 106 (2)| 00:00:02 |
| 47 | NESTED LOOPS | | 1 | 181 | | 106 (2)| 00:00:02 |
|* 48 | HASH JOIN OUTER | | 1 | 155 | | 105 (2)| 00:00:02 |
|* 49 | TABLE ACCESS BY INDEX ROWID | PURCHASE_ORDER_ITEM | 1 | 97 | | 1 (0)| 00:00:01 |
|* 50 | INDEX RANGE SCAN | IDX$$_46570002 | 1 | | | 1 (0)| 00:00:01 |
|* 51 | VIEW | | 7017 | 397K| | 104 (2)| 00:00:02 |
| 52 | SORT GROUP BY | | 7017 | 294K| 840K| 104 (2)| 00:00:02 |
| 53 | TABLE ACCESS FULL | TBL_PO_ADJUSTMENT | 7017 | 294K| | 24 (0)| 00:00:01 |
|* 54 | TABLE ACCESS BY INDEX ROWID | PURCHASE_ORDER | 1 | 26 | | 1 (0)| 00:00:01 |
|* 55 | INDEX UNIQUE SCAN | PK_PO | 1 | | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------
2 - filter("ID_DET"."IND_QTY"-NVL(SUM("POI"."QTY_ORD"),0)>0)
18 - filter("Q"."QUOT_COMP_DATE"=MAX("QM"."QUOT_COMP_DATE"))
20 - access("M"."QUOT_COMP_NO"="Q"."QUOT_COMP_NO" AND "M"."QUOT_COMP_DATE"="Q"."QUOT_COMP_DATE")
21 - access("M"."CCODE"=:CCODE)
filter("M"."CCODE"=:CCODE)
22 - access("Q"."ITEM_CODE"="ITEM_CODE")
24 - access("QM"."CCODE"=:CCODE)
filter("QM"."CCODE"=:CCODE)
25 - access("QM"."QUOT_COMP_NO"="QD"."QUOT_COMP_NO" AND "QM"."QUOT_COMP_DATE"="QD"."QUOT_COMP_DATE")
27 - filter("SUPP_TYPE"=:CCODE AND "Q"."ITEM_CODE"="MAX_NO"."ITEM_CODE")
28 - access("Q"."QUOT_COMP_NO"="MAX_NO"."QUOT_COMP_NO" AND "Q"."QUOT_COMP_DATE"="MAX_NO"."QUOT_COMP_DATE")
29 - access("QM"."QUOT_COMP_NO"="Q"."QUOT_COMP_NO" AND "QM"."QUOT_COMP_DATE"="Q"."QUOT_COMP_DATE" AND
"QM"."CCODE"=:CCODE)
31 - access("Q"."SUPP_CODE"="PM"."PARTY_CODE")
32 - filter("A"."FREEZE"=:FREEZE AND "A"."CCODE"="PM"."CCODE")
33 - access("A"."SUPP_CODE"="PM"."PARTY_CODE")
filter("A"."SUPP_CODE" IS NOT NULL)
35 - access("Q"."ITEM_CODE"="ITEM"."ITEM_CODE" AND "ITEM"."CCODE"=:CCODE)
36 - filter("ITM"."CCODE"=:CCODE AND "ITM"."STORE_CODE"=:STORE)
37 - access("ITM"."PART_NO"="ITEM"."PART_NO")
38 - filter("ITM"."PART_NO"="PART_NO")
39 - access("STORE_CODE"=:STORE)
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------
41 - access("ID_DET"."ITEM_CODE"="ITM"."ITEM_CODE")
42 - filter("ID_MAST"."CCODE"=:CCODE AND "ID_MAST"."FYCODE"=:FYCODE AND "ID_MAST"."APPROVAL_STATUS"='a')
43 - access("ID_MAST"."INDENT_NO"="ID_DET"."INDENT_NO" AND "ID_MAST"."INDENT_DATE"="ID_DET"."INDENT_DATE")
45 - access("ID_MAST"."EMPLOYEE_CODE"="EMP"."EMPLOYEE_CODE")
48 - access("POI"."PO_NO"="T"."PO_NO"(+) AND "POI"."PO_DATE"="T"."PO_DATE"(+) AND
"POI"."INDENT_NO"="T"."INDENT_NO"(+) AND "POI"."INDENT_DATE"="T"."INDENT_DATE"(+) AND
"POI"."ITEM_CODE"="T"."ITEM_CODE"(+))
49 - filter("POI"."INDENT_NO"="ID_DET"."INDENT_NO" AND "POI"."INDENT_DATE"="ID_DET"."INDENT_DATE")
50 - access("POI"."ITEM_CODE"="ID_DET"."ITEM_CODE")
filter(NVL("POI"."APPROVAL_STATUS",'a')='a')
51 - filter("T"."ITEM_CODE"(+)="ID_DET"."ITEM_CODE" AND "T"."INDENT_DATE"(+)="ID_DET"."INDENT_DATE" AND
"T"."INDENT_NO"(+)="ID_DET"."INDENT_NO")
54 - filter("PO"."CCODE"=:CCODE AND "PO"."CANC_STATUS"=:STATUS)
55 - access("PO"."PO_NO"="POI"."PO_NO" AND "PO"."PO_DATE"="POI"."PO_DATE")
102 rows selected.
|
|
|
|
|
|
|
|
| Re: Slow Running Query [message #482203 is a reply to message #482201] |
Wed, 10 November 2010 05:36 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
*Sigh*
It's different from the old explain plan. I'm asking what you changed to cause the difference.
Or do you expect us to guess what you've done?
|
|
|
|
|
|
|
|
| Re: Slow Running Query [message #482285 is a reply to message #482208] |
Wed, 10 November 2010 21:50 |
sameek1211
Messages: 30
Registered: October 2007
Location: India
|
Member |
|
|
here is the tkprof output of the session traced
Trace file: orcl_ora_2812.trc
Sort options: prsdsk exedsk fchdsk execpu fchcpu
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
SELECT DISTINCT PM.PARTY_CODE,
PM.PARTY_NAME,
PM.PARTY_CODE STATUS_CUST,
PM.DEFAULTPURCHASER
FROM PR_INDENT_DET ID_DET,
PR_INDENT_MAST ID_MAST,
ITEM ITM,
EMPLOYEE EMP,
( SELECT POI.INDENT_NO,
POI.INDENT_DATE,
POI.ITEM_CODE,
POI.QTY_ORD - NVL (T.EXTENDED_QTY, :"SYS_B_00") QTY_ORD
FROM PURCHASE_ORDER PO,
PURCHASE_ORDER_ITEM POI,
( SELECT PO_NO,
PO_DATE,
ITEM_CODE,
SUM (NVL (EXTENDED_QTY, :"SYS_B_01")) EXTENDED_QTY,
INDENT_NO,
INDENT_DATE
FROM TBL_PO_ADJUSTMENT
GROUP BY PO_NO,
PO_DATE,
ITEM_CODE,
INDENT_NO,
INDENT_DATE) T
WHERE PO.PO_NO = POI.PO_NO
AND PO.PO_DATE = POI.PO_DATE
AND POI.PO_NO = T.PO_NO(+)
AND POI.PO_DATE = T.PO_DATE(+)
AND POI.INDENT_NO = T.INDENT_NO(+)
AND POI.INDENT_DATE = T.INDENT_DATE(+)
AND POI.ITEM_CODE = T.ITEM_CODE(+)
AND PO.CANC_STATUS = :"SYS_B_02"
AND PO.CCODE = :"SYS_B_03"
AND NVL (POI.APPROVAL_STATUS, :"SYS_B_04") = :"SYS_B_05"
ORDER BY :"SYS_B_06" DESC) POI,
PARTY_MASTER PM,
ACCOUNT A,
(SELECT Q.QUOT_COMP_NO,
Q.QUOT_COMP_DATE,
Q.SUPP_CODE,
ITEM.PART_NO,
ITEM.CCODE
FROM QUOT_COMPARISON_DTL Q,
QUOT_COMPARISON_MST QM,
( SELECT MAX (Q.QUOT_COMP_NO) QUOT_COMP_NO,
Q.QUOT_COMP_DATE,
Q.ITEM_CODE,
M.CCODE
FROM QUOT_COMPARISON_DTL Q,
QUOT_COMPARISON_MST M,
( SELECT MAX (QM.QUOT_COMP_DATE) QUOT_COMP_DATE,
ITEM_CODE,
CCODE
FROM QUOT_COMPARISON_DTL QD,
QUOT_COMPARISON_MST QM
WHERE QM.QUOT_COMP_NO = QD.QUOT_COMP_NO
AND QM.QUOT_COMP_DATE =
QD.QUOT_COMP_DATE
AND QM.CCODE = :"SYS_B_07"
GROUP BY QD.ITEM_CODE, QM.CCODE) MAX_DATE
WHERE M.QUOT_COMP_NO = Q.QUOT_COMP_NO
AND M.QUOT_COMP_DATE = Q.QUOT_COMP_DATE
AND Q.QUOT_COMP_DATE = MAX_DATE.QUOT_COMP_DATE
AND Q.ITEM_CODE = MAX_DATE.ITEM_CODE
AND M.CCODE = :"SYS_B_08"
GROUP BY Q.QUOT_COMP_DATE, Q.ITEM_CODE, M.CCODE) MAX_NO,
ITEM
WHERE QM.QUOT_COMP_NO = Q.QUOT_COMP_NO
AND QM.QUOT_COMP_DATE = Q.QUOT_COMP_DATE
AND QM.CCODE = :"SYS_B_09"
AND Q.QUOT_COMP_NO = MAX_NO.QUOT_COMP_NO
AND Q.QUOT_COMP_DATE = MAX_NO.QUOT_COMP_DATE
AND Q.ITEM_CODE = MAX_NO.ITEM_CODE
AND SUPP_TYPE = :"SYS_B_10"
AND Q.ITEM_CODE = ITEM.ITEM_CODE
AND ITEM.CCODE = :"SYS_B_11") IVD
WHERE ID_MAST.INDENT_NO = ID_DET.INDENT_NO
AND A.SUPP_CODE = PM.PARTY_CODE
AND A.CCODE = PM.CCODE
AND A.FREEZE = :"SYS_B_12"
AND ID_MAST.INDENT_DATE = ID_DET.INDENT_DATE
AND ID_DET.ITEM_CODE = ITM.ITEM_CODE
AND ID_MAST.CCODE = :"SYS_B_13"
AND ITM.CCODE = :"SYS_B_14"
AND ID_MAST.APPROVAL_STATUS = :"SYS_B_15"
AND ID_MAST.FYCODE = :"SYS_B_16"
AND ID_MAST.EMPLOYEE_CODE = EMP.EMPLOYEE_CODE
AND ITM.PART_NO = IVD.PART_NO
AND IVD.SUPP_CODE = PM.PARTY_CODE
AND ID_DET.INDENT_NO = POI.INDENT_NO(+)
AND ID_DET.INDENT_DATE = POI.INDENT_DATE(+)
AND ID_DET.ITEM_CODE = POI.ITEM_CODE(+)
AND ITM.PART_NO IN (SELECT PART_NO
FROM ITEM
WHERE STORE_CODE = :"SYS_B_17")
AND ITM.STORE_CODE = :"SYS_B_18"
GROUP BY ID_DET.IND_SEQ_NO,
ID_DET.INDENT_NO,
ID_DET.INDENT_DATE,
ID_DET.ITEM_CODE,
ID_DET.ITEM_RATE,
ID_DET.IND_QTY,
ID_MAST.EMPLOYEE_CODE,
ITM.ITEM_NAME,
EMP.EMPLOYEE_NAME,
ID_DET.IND_PURPOSE,
IVD.SUPP_CODE,
ITM.STORE_CODE,
PM.PARTY_CODE,
PM.PARTY_NAME,
PM.DEFAULTPURCHASER
HAVING (ID_DET.IND_QTY - NVL (SUM (POI.QTY_ORD), :"SYS_B_19")) > :"SYS_B_20"
ORDER BY :"SYS_B_21"
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 0 0 28
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 28
Misses in library cache during parse: 0
Parsing user id: 56 (???)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 3947 0.24 17.72
latch: cache buffers chains 1 0.00 0.00
latch free 1 0.00 0.00
direct path write temp 12 0.00 0.00
direct path read temp 12 0.00 0.07
SQL*Net message from client 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 0 0 28
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 28
Misses in library cache during parse: 0
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 3947 0.24 17.72
latch: cache buffers chains 1 0.00 0.00
latch free 1 0.00 0.00
direct path write temp 12 0.00 0.00
direct path read temp 12 0.00 0.07
SQL*Net message from client 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
1 user SQL statements in session.
0 internal SQL statements in session.
1 SQL statements in session.
0 statements EXPLAINed in this session.
********************************************************************************
Trace file: orcl_ora_2812.trc
Trace file compatibility: 10.01.00
Sort options: prsdsk exedsk fchdsk execpu fchcpu
1 session in tracefile.
1 user SQL statements in trace file.
0 internal SQL statements in trace file.
1 SQL statements in trace file.
1 unique SQL statements in trace file.
4140 lines in trace file.
0 elapsed seconds in trace file.
|
|
|
|
|
|
| Re: Slow Running Query [message #482295 is a reply to message #482292] |
Wed, 10 November 2010 23:06 |
sameek1211
Messages: 30
Registered: October 2007
Location: India
|
Member |
|
|
not yet . it is true that trace is showing nothing nor the explain plan.
still i am wondered why the query is taking 5-6 minutes in producing output.
|
|
|
|
|
|
|
|
| Re: Slow Running Query [message #482303 is a reply to message #482302] |
Wed, 10 November 2010 23:56 |
sameek1211
Messages: 30
Registered: October 2007
Location: India
|
Member |
|
|
I am in dilemma whether it is a memory issue or not. for this i performed the following scenario
1. set autotrace on
2. run the query
the query takes 4-5 minutes.
open another session and run the following
1. "Alter system flush buffer_cache;"
2. "Alter system flush Shared_pool;"
again it takes the same time.
As per my knowledge when i have flushed the memory parameter , for the first time it has to cache the query in memory so it is taking time.
I m not sure whether i am right. If so then do i have to increase the SGA of the database
|
|
|
|
|
|
|
|
|
|
| Re: Slow Running Query [message #482639 is a reply to message #482185] |
Mon, 15 November 2010 03:00 |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
1. Why do you need to access ITEM table:
...AND ITM.PART_NO IN (SELECT PART_NO
FROM ITEM
WHERE STORE_CODE = :"SYS_B_17")
When on the next line you have:
AND ITM.STORE_CODE = :"SYS_B_18"
2. Why are you using ORDER BY clause inside POI in-line view?
|
|
|
|
Goto Forum:
Current Time: Sat Apr 27 21:31:59 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
.
