Home » RDBMS Server » Performance Tuning » Query is taking exceptionally large amount of time (Oracle Database 10g Enterprise Edition Release 10.1.0.4.0,PL/SQL Release 10.1.0.4.0)
Query is taking exceptionally large amount of time [message #321478] Tue, 20 May 2008 07:08 Go to next message
sreenadhaj
Messages: 5
Registered: May 2008
Junior Member
Dear All,
I have a view which follows.


create or replace view v_tip_shipment as
select distinct ar.org_id org_id,
ar.id ar_inv_no,
trunc(ar.transaction_date) ar_inv_date,
decode(po.ext_sys_ref_no,po.trans_id_1,po.id,po.ext_sys_ref_no) po_no,
po.customer_po_number,
(select trunc(presentation_date) from lc_negotiations where lc_org_id = lcnd.lcn_lc_org_id
and lc_id = lcnd.lcn_lc_id and rownum=1) presentation_date,
ari.trans_id_1 ppl_no,
trunc(pal.trans_date) ppl_rec_date,
trunc(tsch.eta_pol) vsl_eta,
trunc(erh.created_on) dtd_create_dt,
trunc(erh.ship_on_board_date) shpt_dt,
trunc(erh.reference_Date_3) dtd_sent_dt,
trunc(dpm.dispatch_date) shpg_docs_dsptch_dt,
trunc(si.created_on) si_issued_dt,
trunc(si.sched_insp_date) inspctn_appl_dt,
trunc(si.actual_insp_date) actual_inspctn_dt
from ar_invoices ar,
ari_items ari,
packing_lists pal,
palcs_items palcsi,
er_houses erh,
transport_orders si,
transport_schedules tsch,
job_orders jo,
lcn_details lcnd,
purchase_orders po,
dispatch_monitors dpm
where ar.org_id = ari.ari_org_id
and ar.id = ari.ari_id
and ar.org_id = pal.org_id
and ar.id = pal.trans_id_3
and pal.org_id = erh.er_org_id
and pal.trans_id_2 = erh.id
and pal.ext_sys_ref_org_id = si.org_id
and pal.trans_id_1 = si.trans_id_1
and si.org_id = jo.org_id
and si.trans_id_1 = jo.id
and jo.org_id = tsch.org_id
and jo.tsch_id = tsch.id
and ar.org_id = lcnd.lcn_lc_org_id(+)
and ar.id = lcnd.ari_id(+)
and pal.org_id = palcsi.palcs_pal_org_id
and pal.id = palcsi.palcs_pal_id
and palcsi.palcs_pal_org_id= po.org_id
and palcsi.trans_id_1 = po.id
and erh.er_org_id = dpm.erhserd_erhser_erh_er_org_id(+)
and erh.er_id = dpm.erhserd_erhser_erh_er_id(+)
and erh.id = dpm.erhserd_erhser_erh_id(+)




When i query this with
select count(1) from (SELECT * from v_tip_shipment WHERE ORG_ID='****')

It has to fetch 124169 records.its taking around 634 seconds.

All the tables used in this view are indexed.
Please can any one advise me on this, as what could be the problem causing this drastic peformance down.

Report message to a moderator

Re: Query is taking exceptionally large amount of time [message #321481 is a reply to message #321478] Tue, 20 May 2008 07:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Also read How to Identify Performance Problem and Bottleneck and OraFAQ Oracle SQL Tuning Guide.

Regards
Michel

Report message to a moderator

Re: Query is taking exceptionally large amount of time [message #321487 is a reply to message #321481] Tue, 20 May 2008 07:32 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Why are you using DISTINCT in the view? Are you not using the right join keys?

What do you get from your COUNT(*) when you remove the DISTINCT? That may unlock the secret to your problem.
Ross Leishman

Report message to a moderator

Re: Query is taking exceptionally large amount of time [message #321499 is a reply to message #321487] Tue, 20 May 2008 08:37 Go to previous messageGo to next message
sreenadhaj
Messages: 5
Registered: May 2008
Junior Member
If i dont use distinct, i am getting duplicate date which is not my requirement and some data in my view doesn't allow grouping also.

Report message to a moderator

Re: Query is taking exceptionally large amount of time [message #321689 is a reply to message #321487] Wed, 21 May 2008 01:30 Go to previous messageGo to next message
sreenadhaj
Messages: 5
Registered: May 2008
Junior Member
Here is explain plan results of my query, can any one help me in this regard.


SELECT STATEMENT, GOAL = ALL_ROWS Cost=136263 Cardinality=1 Bytes=2
SORT AGGREGATE Cardinality=1 Bytes=2
VIEW Object name=V_TIP_SHIPMENT Cost=136263 Cardinality=1749762 Bytes=3499524
SORT UNIQUE Cost=136263 Cardinality=1749762 Bytes=621165510
HASH JOIN Cost=3278 Cardinality=1749762 Bytes=621165510
TABLE ACCESS FULL Object name=PURCHASE_ORDERS Cost=307 Cardinality=16153 Bytes=759191
HASH JOIN Cost=2945 Cardinality=1747950 Bytes=538368600
TABLE ACCESS FULL Object name=TRANSPORT_SCHEDULES Cost=54 Cardinality=6262 Bytes=181598
HASH JOIN Cost=2864 Cardinality=1747950 Bytes=487678050
INDEX FAST FULL SCAN Object name=ARII_UNQ_I Cost=143 Cardinality=75807 Bytes=2274210
HASH JOIN Cost=1739 Cardinality=66960 Bytes=16673040
HASH JOIN Cost=1019 Cardinality=6446 Bytes=1424566
INDEX FAST FULL SCAN Object name=JOB_ORDERS_I_ORG_ID_ID_TSCH_ID Cost=10 Cardinality=6657 Bytes=6657
NESTED LOOPS OUTER Cost=1008 Cardinality=6440 Bytes=1416800
HASH JOIN Cost=984 Cardinality=6440 Bytes=1223600
TABLE ACCESS FULL Object name=TRANSPORT_ORDERS Cost=250 Cardinality=17645 Bytes=511705
HASH JOIN Cost=733 Cardinality=7080 Bytes=1139880
TABLE ACCESS FULL Object name=AR_INVOICES Cost=251 Cardinality=7143 Bytes=185718
HASH JOIN Cost=482 Cardinality=7582 Bytes=1023570
HASH JOIN RIGHT OUTER Cost=243 Cardinality=6261 Bytes=450792
TABLE ACCESS FULL Object name=DISPATCH_MONITORS Cost=3 Cardinality=21 Bytes=609
TABLE ACCESS FULL Object name=ER_HOUSES Cost=240 Cardinality=6261 Bytes=269223
TABLE ACCESS FULL Object name=PACKING_LISTS Cost=238 Cardinality=8023 Bytes=505449
TABLE ACCESS BY INDEX ROWID Object name=LCN_DETAILS Cost=1 Cardinality=1 Bytes=30
INDEX UNIQUE SCAN Object name=LCND_ARI_UK Cost=0 Cardinality=1
TABLE ACCESS FULL Object name=PALCS_ITEMS Cost=718 Cardinality=83038 Bytes=2325064

Report message to a moderator

Re: Query is taking exceptionally large amount of time [message #321791 is a reply to message #321487] Wed, 21 May 2008 05:54 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
rleishman wrote on Tue, 20 May 2008 22:32
What do you get from your COUNT(*) when you remove the DISTINCT? That may unlock the secret to your problem.

Report message to a moderator

Previous Topic: MView Complete Refresh Performance Issue
Next Topic: Finding HWM
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Jun 22 21:59:50 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.