Home » SQL & PL/SQL » SQL & PL/SQL » Performance issue while runningthe query (oracle 10g)
Performance issue while runningthe query [message #386216] Fri, 13 February 2009 03:01 Go to next message
deepbans
Messages: 32
Registered: February 2009
Member
SELECT DISTINCT tmp.order_number order_number, tmp.header_id order_header_id,
tmp.org_id om_org_id, tmp.om_ou om_ou, tmp.NAME order_source,
tmp.orig_sys_document_ref ec_order_number,
tmp.cust_po_number customer_po_number,
tmp.contract_quote_number quote_number,
tmp.attribute4 revenue_type, tmp.line_id line_id,
tmp.lineid lineid, tmp.line_number line_number,
oet.NAME line_type_name, tmp.attribute3 contract_number,
msi.segment1 item, tmp.cancelled_quantity cancelled_quantity,
tmp.ordered_quantity ordered_quantity, bid_hl.country country,
tmp.account_number customer_number,
tmp.party_name invoice_to_customer_name,
tmp.invoice_to_org_id invoice_to_site_use_id,
tmp.flow_status_code line_status,
tmp.ordered_date ordered_date,
tmp.creation_date order_header_create_date,
tmp.last_update_date order_header_update_date,
tmp.booked_date booked_date, tmp.booked_flag booked_flag,
tmp.cisco_book_date cisco_booked_date,
tmp.cisco_book_result_code cisco_booked_result_code,
ohd.NAME hold_name, ohr.creation_date hold_release_date,
tmp.receivable_intf_date interfaced_to_ar_date,
rah.trx_number invoice_number,
rah.creation_date invoice_creation_date,
rat.NAME sales_territory,
rat.description sales_territory_description,
osct.NAME sales_credit_type, pctm.theater theater,
tmp.attribute8 deal_id
FROM xxcss_pbm_tmp_sttr_qtc_sjoe_tb tmp,
oe_sales_credit_types@xxcts_sjoe_ccais.cisco.com osct,
ra_territories@xxcts_sjoe_ccais.cisco.com rat,
csm_hierarchy@xxcts_sjoe_ccais.cisco.com ch,
oe_order_holds_all@xxcts_sjoe_ccais.cisco.com ooha,
oe_hold_releases@xxcts_sjoe_ccais.cisco.com ohr,
oe_hold_sources_all@xxcts_sjoe_ccais.cisco.com ohsa,
oe_hold_definitions@xxcts_sjoe_ccais.cisco.com ohd,
oe_transaction_types_tl@xxcts_sjoe_ccais.cisco.com oet,
hz_party_sites@xxcts_sjoe_ccais.cisco.com bid_hps,
hz_locations@xxcts_sjoe_ccais.cisco.com bid_hl,
xxcss_pbm_country_theater_map pctm,
ra_customer_trx_all@xxcts_sjoe_ccais.cisco.com rah,
mtl_system_items_b@xxcts_sjoe_ccais.cisco.com msi
WHERE 1 = 1
AND pctm.theater IN ('European Markets')
AND tmp.request_id = :req_id
AND tmp.sales_credit_type_id = osct.sales_credit_type_id
AND tmp.line_type_id = oet.transaction_type_id
AND tmp.territory_id = rat.territory_id
AND rat.territory_id = ch.territory_id
AND tmp.header_id = ooha.header_id
AND NVL (ooha.line_id, tmp.line_id) = tmp.line_id
AND ooha.hold_release_id = ohr.hold_release_id(+)
AND ooha.hold_source_id = ohsa.hold_source_id(+)
AND ohsa.hold_id = ohd.hold_id
AND tmp.party_id = bid_hps.party_id(+)
AND NVL (tmp.party_site_id, 1) = NVL (bid_hps.party_site_id, 1)
AND bid_hps.location_id = bid_hl.location_id(+)
AND bid_hl.country = pctm.country_code(+)
AND TO_CHAR (tmp.order_number) = rah.interface_header_attribute1(+)
AND tmp.ship_from_org_id = msi.organization_id
AND tmp.inventory_item_id = msi.inventory_item_id
AND NVL (tmp.header_seq_id, 1) =
(SELECT NVL (MAX (header_seq_id), 1)
FROM xxcss_pbm_tmp_sttr_qtc_sjoe_tb tmp1
WHERE tmp1.request_id = :req_id
AND tmp1.source_header_id = tmp.source_header_id)


This query is taking long time in execution more than 1 hour.
In production this query causing snapshot too old error.

Please help me on this as soon as possible.
Re: Performance issue while runningthe query [message #386219 is a reply to message #386216] Fri, 13 February 2009 03:09 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
dear deepbans,
i am really curious, how fast one can understand your querry in proper perspective ( due to length) and is going to reply. You, yourself would have identified some problamatic segment, if you could highlight or throw some lights on the portion of syntax, people will be happy to help so fast. It is only my opinion.
good luck,
yours
dr.s.raghunathan
Re: Performance issue while runningthe query [message #386223 is a reply to message #386219] Fri, 13 February 2009 03:25 Go to previous messageGo to next message
deepbans
Messages: 32
Registered: February 2009
Member
Hi Dr.Raghunathan,

Here we are accessing each table remotely and each table contain huge number of records.

The original query is even too long.I have break the query and have created a temporary physical table(not global temp table) on my local database and joining the my local table with other tables(remote database).

xxcss_pbm_tmp_sttr_qtc_sjoe_tb tmp is the table which i have created on my local database.I delete the records in our physical table weekly.

Please provide the alternate solution if you have .
And also let me know your doubt and any query


Regards,
Deepa Bansal.
Re: Performance issue while runningthe query [message #386230 is a reply to message #386223] Fri, 13 February 2009 03:37 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
dear Deepa Bansal,

why did you post the 1=1 condition.
Quote:

WHERE 1 = 1


what way this could help you in performance. I will patiently read the remaining portion and will come back
yours
dr.s.raghunathan

p.s. : Still briefing about your table and requirement in nut shell in words could help us to understand your table structure

[Updated on: Fri, 13 February 2009 03:39]

Report message to a moderator

Re: Performance issue while runningthe query [message #386231 is a reply to message #386230] Fri, 13 February 2009 03:42 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
1) Read the sticky post in the performance tuning forum first on how to tune a query.

2) Have a look a the EXPLAIN PLAN

3) Trace the session

Then you will know which part of the query is the bottleneck.

If not, we would need information about the tables and their statistics and indexes to know what could be the problem.

Re: Performance issue while runningthe query [message #386232 is a reply to message #386223] Fri, 13 February 2009 03:45 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
As most of the data is at the remote site, I'd look at the Driving_Site h hint
Re: Performance issue while runningthe query [message #386242 is a reply to message #386232] Fri, 13 February 2009 04:45 Go to previous messageGo to next message
deepbans
Messages: 32
Registered: February 2009
Member
Explain Plan

1 Rows in the bitmap index XXCSS_O.XXCSS_PBM_STTR_QTC_SJOE_IDX1 matching a specific value were accessed.

2 The bitmaps returned from steps 1 were converted to rowids.

3 Rows from table XXCSS_O.XXCSS_PBM_TMP_STTR_QTC_SJOE_TB were accessed using rowid got from an index.
4 The following SQL statement was sent to the remote site : SELECT "PARTY_SITE_ID","PARTY_ID","LOCATION_ID" FROM "HZ_PARTY_SITES" "BID_HPS" WHERE :1="PARTY_ID".
5 For each row returned by step 3 get the matching row from step 4 If there are not matching rows from step 4 return nulls for those columns.
6 For the rows returned by step 5, filter out rows depending on filter criteria.
7 The following SQL statement was sent to the remote site : SELECT "INVENTORY_ITEM_ID","ORGANIZATION_ID","SEGMENT1" FROM "MTL_SYSTEM_ITEMS_B" "MSI" WHERE :1="ORGANIZATION_ID" AND :2="INVENTORY_ITEM_ID".
8 For each row retrieved by step 6, the operation in step 7 was performed to find a matching row.
9 The following SQL statement was sent to the remote site : SELECT "LOCATION_ID","COUNTRY" FROM "HZ_LOCATIONS" "BID_HL" WHERE :1="LOCATION_ID".
10 For each row returned by step 8 get the matching row from step 9 If there are not matching rows from step 9 return nulls for those columns.
11 The following SQL statement was sent to the remote site : SELECT "TERRITORY_ID","NAME","DESCRIPTION" FROM "RA_TERRITORIES" "RAT" WHERE :1="TERRITORY_ID".
12 For each row retrieved by step 10, the operation in step 11 was performed to find a matching row.
13 The following SQL statement was sent to the remote site : SELECT "TERRITORY_ID" FROM "CSM_HIERARCHY" "CH" WHERE :1="TERRITORY_ID".
14 For each row retrieved by step 12, the operation in step 13 was performed to find a matching row.
15 The following SQL statement was sent to the remote site : SELECT "TRANSACTION_TYPE_ID","NAME" FROM "OE_TRANSACTION_TYPES_TL" "OET" WHERE :1="TRANSACTION_TYPE_ID".
16 For each row retrieved by step 14, the operation in step 15 was performed to find a matching row.
17 One or more rows were retrieved using index XXCSS_O.XXCSS_PBM_COUNTRY_CODE_IDX . The index was scanned in ascending order..
18 Rows from table XXCSS_O.XXCSS_PBM_COUNTRY_THEATER_MAP were accessed using rowid got from an index.
19 For each row returned by step 16 get the matching row from step 18 If there are not matching rows from step 18 return nulls for those columns.
20 For the rows returned by step 19, filter out rows depending on filter criteria.
21 The following SQL statement was sent to the remote site : SELECT "SALES_CREDIT_TYPE_ID","NAME" FROM "OE_SALES_CREDIT_TYPES" "OSCT" WHERE :1="SALES_CREDIT_TYPE_ID".
22 For each row retrieved by step 20, the operation in step 21 was performed to find a matching row.
23 The following SQL statement was sent to the remote site : SELECT "CREATION_DATE","TRX_NUMBER","INTERFACE_HEADER_ATTRIBUTE1" FROM "RA_CUSTOMER_TRX_ALL" "RAH" WHERE "INTERFACE_HEADER_ATTRIBUTE1"=:1.
24 For each row returned by step 22 get the matching row from step 23 If there are not matching rows from step 23 return nulls for those columns.
25 The following SQL statement was sent to the remote site : SELECT "HOLD_SOURCE_ID","HOLD_RELEASE_ID","HEADER_ID","LINE_ID" FROM "OE_ORDER_HOLDS_ALL" "OOHA" WHERE :1="HEADER_ID" AND NVL("LINE_ID",:2)=:3.
26 For each row retrieved by step 24, the operation in step 25 was performed to find a matching row.
27 The following SQL statement was sent to the remote site : SELECT "HOLD_SOURCE_ID","HOLD_ID" FROM "OE_HOLD_SOURCES_ALL" "OHSA" WHERE :1="HOLD_SOURCE_ID".
28 For each row retrieved by step 26, the operation in step 27 was performed to find a matching row.
29 The following SQL statement was sent to the remote site : SELECT "HOLD_ID","NAME" FROM "OE_HOLD_DEFINITIONS" "OHD" WHERE :1="HOLD_ID".
30 For each row retrieved by step 28, the operation in step 29 was performed to find a matching row.
31 The following SQL statement was sent to the remote site : SELECT "HOLD_RELEASE_ID","CREATION_DATE" FROM "OE_HOLD_RELEASES" "OHR" WHERE :1="HOLD_RELEASE_ID".
32 For each row returned by step 30 get the matching row from step 31 If there are not matching rows from step 31 return nulls for those columns.
33 Rows in the bitmap index XXCSS_O.XXCSS_PBM_STTR_QTC_SJOE_IDX1 matching a specific value were accessed.
34 The bitmaps returned from steps 33 were converted to rowids.
35 Rows from table XXCSS_O.XXCSS_PBM_TMP_STTR_QTC_SJOE_TB were accessed using rowid got from an index.
36 The rows were sorted to support a group operation (MAX,MIN,AVERAGE, SUM, etc).
37 For the rows returned by step 32, filter out rows depending on filter criteria.
38 HASH UNIQUE
39 Rows were returned by the SELECT statement.


This is the explain plan for my query.
Total cost is 139

We dont own the tables..So we cant create ant index or do anything on the table..Our main concern is only to retireve data very fast using any oracle features.
1.will breaking the query help us?
2. materialized view concept?
3. Join condition order?


Deepa Bansal.

Re: Performance issue while runningthe query [message #386297 is a reply to message #386242] Fri, 13 February 2009 08:24 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
This is of no good to us (atleast for me). I will give you the steps which are required to analyze what is happening.

Step 1 : Post your oracle version using the following
select * from v$version;


Step 2 : Post the explain plan using the following script. Assuming you are running oracle 10g. If not google or search for this forum how to extract the output from plan_table.

explain plan
for
<query>

set lines 133
set pages 600
select * from table(dbms_xplan.display());


This will be very much ideal
Step 3 : Run the query by turning on the trace on the oracle backend (from sqlplus).
alter session set tracefile_identifier = 'LONG_RUNNING_QUERY';
alter session set events '10046 trace name context forever, level 8';
<Execute your query>
show parameter user_dump_dest
exit 

Change your directory to user_dump_dest and search for the file containing the text 'LONG_RUNNING_QUERY'. Then run the following command

tkprof <tracefile_name> <outputtracefile_name>

Analyze the trace file and copy & paste the sql statement which you have executed along with the cardinality and the waits.

Atleast try to post the initial two steps.

Last but not least from next time please format your post. Otherwise it is very difficult to read and understand what you have posted.

On how to format the post read the sticky in this forum and also spend some time reading the sticky guidelines in the performance tuning section which explain in greater deatail on how to identify performance bottleneck.

Hope this helps.

Regards

Raj
Previous Topic: PRAGMA AUTONOMOUS_TRANSACTION (merged)
Next Topic: Error handling
Goto Forum:
  


Current Time: Sat Dec 10 03:07:15 CST 2016

Total time taken to generate the page: 0.08242 seconds