Home » SQL & PL/SQL » SQL & PL/SQL » Performance issue on this query
Performance issue on this query [message #218283] Wed, 07 February 2007 10:43 Go to next message
senthilram
Messages: 4
Registered: November 2005
Location: india
Junior Member
Guru Have a look the below query and give me your valuable suggestion

Actually the Oracle using the Insert statement Like this
INSERT INTO ibe_ct_imedia_search
(IBE_CT_IMEDIA_SEARCH_ID,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
CATEGORY_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
LANGUAGE,
DESCRIPTION,
LONG_DESCRIPTION,
INDEXED_SEARCH,
CATEGORY_SET_ID,
WEB_STATUS,

) VALUES(
--l_sequence,
ibe_ct_imedia_search_s1.nextval,
l_version_number_tbl(l_count),
l_created_by_tbl(l_count),
l_creation_date_tbl(l_count),
l_last_updated_by_tbl(l_count),
l_last_updated_date_tbl(l_count),
l_last_update_login_tbl(l_count),
l_category_id_tbl(l_count),
l_organization_id_tbl(l_count),
l_inventory_item_id_tbl(l_count),
l_language_tbl(l_count),
l_description_tbl(l_count),
l_long_description_tbl(l_count),
xxssi_ibe_search_setup_pvt.WriteToLob(l_description_tbl(l_count), l_long_description_tbl(l_count), l_concatenated_segments_tbl(l_count),
l_category_set_id_tbl(l_count),
l_web_status_tbl(l_count),
);
we write a function to concatenated Catalog number and store is value in table.But this Performing very slow (it takes 4 hours to complete).
INSERT INTO ibe_ct_imedia_search
(IBE_CT_IMEDIA_SEARCH_ID,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
CATEGORY_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
LANGUAGE,
DESCRIPTION,
LONG_DESCRIPTION,
INDEXED_SEARCH,
CATEGORY_SET_ID,
WEB_STATUS,
XXSSI_CATALOG_NUMBER
) VALUES(
--l_sequence,
ibe_ct_imedia_search_s1.nextval,
l_version_number_tbl(l_count),
l_created_by_tbl(l_count),
l_creation_date_tbl(l_count),
l_last_updated_by_tbl(l_count),
l_last_updated_date_tbl(l_count),
l_last_update_login_tbl(l_count),
l_category_id_tbl(l_count),
l_organization_id_tbl(l_count),
l_inventory_item_id_tbl(l_count),
l_language_tbl(l_count),
l_description_tbl(l_count),
l_long_description_tbl(l_count),
xxssi_ibe_search_setup_pvt.WriteToLob(l_description_tbl(l_count), l_long_description_tbl(l_count), l_concatenated_segments_tbl(l_count)||xxssi_ibe_search_setup_pvt.Get_Catalog_Number( l_inventory_item_id_tbl(l_count))),
l_category_set_id_tbl(l_count),
l_web_status_tbl(l_count),
l_catalog_no);


here is the function what we r calling

FUNCTION Get_Catalog_Number(p_inventory_id IN VARCHAR2)
RETURN VARCHAR2 IS

CURSOR curcatalognumber(p_inventory_id VARCHAR2) IS
SELECT distinct catalog_item_number
FROM xxssi_ibe_price_list_attr_v
where inventory_id=p_inventory_id;
g_catalog_no varchar2(100);
BEGIN

-- printdebuglog('inside function : ');
FOR reccurcatalognumber IN curcatalognumber(p_inventory_id)
LOOP

g_catalog_no := g_catalog_no ||' '||reccurcatalognumber.catalog_item_number;

END LOOP;
l_catalog_no := g_catalog_no;
RETURN g_catalog_no;
END;

here is the View Script xxssi_ibe_price_list_attr_v

SELECT qpll.attribute1 catalog_page_number,
qpll.attribute2 catalog_item_number,
DECODE (UPPER (qppr.product_attr_value),
'ALL', NULL,
qppr.product_attr_value
) inventory_id,
lkup.attribute1 minisite_id, lkup.tag tag
FROM qp_list_lines qpll,
qp_pricing_attributes qppr,
fnd_lookup_values lkup
WHERE lkup.attribute_category = 'XXSSI_IBE_STORE_PRICE_LIST'
AND lkup.lookup_type = 'XXSSI_IBE_ITEM_EXCLUSION'
AND qpll.list_header_id = lkup.attribute2
AND qppr.list_header_id = lkup.attribute2
AND lkup.attribute1 IS NOT NULL
AND qpll.attribute2 IS NOT NULL
--AND lkup.tag IS NOT NULL
AND lkup.enabled_flag = 'Y'
AND SYSDATE BETWEEN lkup.start_date_active
AND NVL (lkup.end_date_active, SYSDATE)
--AND SYSDATE BETWEEN qpll.start_date_active
--AND NVL (qpll.end_date_active, SYSDATE)
AND qpll.list_line_type_code IN ('PLL', 'PBH')
AND qppr.pricing_phase_id = 1
AND qppr.qualification_ind IN (4, 6, 20, 22)
AND qpll.pricing_phase_id = 1
AND qpll.qualification_ind IN (4, 6, 20, 22)
AND qppr.list_line_id = qpll.list_line_id
AND qppr.pricing_attribute_context IS NULL


Help me it very critical


Thanks,
Senthil
Re: Performance issue on this query [message #218295 is a reply to message #218283] Wed, 07 February 2007 11:30 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
The query for xxssi_ibe_price_list_attr_v is probably quite slow. You need to tune that using explain plan or sql*trace. For a quicker solution, you could just dump the view out to a temporary table and put an index on inventory_id, then amend the procedure to use the temporary table instead of the view.
Re: Performance issue on this query [message #218326 is a reply to message #218283] Wed, 07 February 2007 14:51 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Post TKPROF.
Re: Performance issue on this query [message #218362 is a reply to message #218283] Wed, 07 February 2007 19:27 Go to previous message
senthilram
Messages: 4
Registered: November 2005
Location: india
Junior Member
I Fixed This issue using the COLLECT function......
Thanks For All your Replies...
Previous Topic: some questions ?????
Next Topic: Duplicate Records
Goto Forum:
  


Current Time: Fri Dec 09 09:25:38 CST 2016

Total time taken to generate the page: 0.28013 seconds