Home » RDBMS Server » Performance Tuning » Need your help in tuning the query (11i)
| Need your help in tuning the query [message #595626] |
Thu, 12 September 2013 10:18  |
 |
nishant87
Messages: 53
Registered: September 2013
Location: india
|
Member |
|
|
Hi,
I am currently facing performance issue in one of the package.In trace the problematic sql appear as:
SELECT RTRIM (xbdi.assembly_item_name) assembly_item_name,
xbdi.organization_code,
NVL (xbdi.start_effective_date,
TRUNC (SYSDATE + 1)
) start_effective_date,
NVL (xbdi.operation_sequence_number, 1) operation_sequence_number,
RTRIM (xbdi.component_item_name) component_item_name,
xbdi.reference_designator_name, xbdi.ref_designator_comment,
xbdi.attribute1, xbdi.attribute2, xbdi.attribute3
FROM xxbom_designator_iface_va xbdi
WHERE RTRIM (xbdi.assembly_item_name) = :b4
AND xbdi.organization_code = :b3
AND RTRIM (xbdi.component_item_name) = :b2
AND NVL (xbdi.operation_sequence_number, -999) =
NVL (:b1, NVL (xbdi.operation_sequence_number, -999))
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 170211 5.57 5.89 0 0 0 0
Fetch 170211 15254.16 15312.80 5383 916926657 170211 32501
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 340423 15259.73 15318.70 5383 916926657 170211 32501
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: 173 (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 TABLE ACCESS FULL XXBOM_DESIGNATOR_IFACE_VA (cr=5387 pr=5383 pw=0 time=8562556 us cost=1450 size=880 card=11)
i have uploaded the trace file.Can anyone look into the issue and suggest how to tune it.
*BlackSwan added {code} tags. Please do so yourself in the future.
[Updated on: Thu, 12 September 2013 10:23] by Moderator Report message to a moderator |
|
|
|
| Re: Need your help in tuning the query [message #595627 is a reply to message #595626] |
Thu, 12 September 2013 10:22  |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
nishant87 wrote on Thu, 12 September 2013 08:18Hi,
I am currently facing performance issue in one of the package.In trace the problematic sql appear as:
SELECT RTRIM (xbdi.assembly_item_name) assembly_item_name,
xbdi.organization_code,
NVL (xbdi.start_effective_date,
TRUNC (SYSDATE + 1)
) start_effective_date,
NVL (xbdi.operation_sequence_number, 1) operation_sequence_number,
RTRIM (xbdi.component_item_name) component_item_name,
xbdi.reference_designator_name, xbdi.ref_designator_comment,
xbdi.attribute1, xbdi.attribute2, xbdi.attribute3
FROM xxbom_designator_iface_va xbdi
WHERE RTRIM (xbdi.assembly_item_name) = :b4
AND xbdi.organization_code = :b3
AND RTRIM (xbdi.component_item_name) = :b2
AND NVL (xbdi.operation_sequence_number, -999) =
NVL (:b1, NVL (xbdi.operation_sequence_number, -999))
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 170211 5.57 5.89 0 0 0 0
Fetch 170211 15254.16 15312.80 5383 916926657 170211 32501
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 340423 15259.73 15318.70 5383 916926657 170211 32501
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: 173 (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 TABLE ACCESS FULL XXBOM_DESIGNATOR_IFACE_VA (cr=5387 pr=5383 pw=0 time=8562556 us cost=1450 size=880 card=11)
i have uploaded the trace file.Can anyone look into the issue and suggest how to tune it.
Please read and follow the forum guidelines, to enable us to help you:
http://www.orafaq.com/forum/t/88153/0/
SQL statement completes in under 0.1.
The problem is that it is invoked FREQUENTLY.
The problem is NOT the SQL; but the application which calls it since it returns fewer than 1 row per invocation.
[Updated on: Thu, 12 September 2013 10:24] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: Need your help in tuning the query [message #595632 is a reply to message #595631] |
Thu, 12 September 2013 10:38 |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Create a single concatentated index on COMPONENT_ITEM_NAME, ORGANIZATION_CODE,OPERATION_SEQUENCE_NUMBER, and assembly_item_name.
--update: sorry, that really wasn't right.
Your use of functions would suppress use of the index. So your index needs to be on the RTRIM of the columns. Do you need to RTRIM? If the columns are VARCHAR2, certainly not.
And what is the purpose of that logic on operation_Sequence_number?
[Updated on: Thu, 12 September 2013 10:44] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Need your help in tuning the query [message #595635 is a reply to message #595633] |
Thu, 12 September 2013 10:48 |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
No, no - what I said was completely wrong.
Of your existing indexes, only the one on organization_code is usable, because you have functions around the other columns. And organization code probably isn't very selective. So either your index expression needs to include the functions, or you need to remove the functions. So ask yourself: "why am I using RTRIM? Is it necessary? And what is that NVL business there for?"
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Need your help in tuning the query [message #595679 is a reply to message #595673] |
Thu, 12 September 2013 13:45 |
|
nishant87
Messages: 53
Registered: September 2013
Location: india
|
Member |
|
|
CREATE OR REPLACE PROCEDURE APPS.XXBOM_IMPORT_VA_PRC (p_error_message OUT VARCHAR2,p_error_code OUT VARCHAR2 ) IS
-- ************ Local Variables Declaration **********-- */
-- Variable Declarations
v_bom_header_rec Bom_Bo_Pub.Bom_Head_Rec_Type := Bom_Bo_Pub.G_MISS_BOM_HEADER_REC;
v_bom_revision_tbl Bom_Bo_Pub.Bom_Revision_Tbl_Type := Bom_Bo_Pub.G_MISS_BOM_REVISION_TBL;
v_bom_component_tbl Bom_Bo_Pub.Bom_Comps_Tbl_Type := Bom_Bo_Pub.G_MISS_BOM_COMPONENT_TBL;
v_bom_ref_designator_tbl Bom_Bo_Pub.Bom_Ref_Designator_Tbl_type := Bom_Bo_Pub.G_MISS_BOM_REF_DESIGNATOR_TBL;
v_bom_sub_component_tbl Bom_Bo_Pub.Bom_Sub_Component_Tbl_Type := Bom_Bo_Pub.G_MISS_BOM_SUB_COMPONENT_TBL;
v_error_message_list Error_handler.error_tbl_type;
v_x_bom_header_rec Bom_Bo_Pub.bom_Head_Rec_Type;
v_x_bom_revision_tbl Bom_Bo_Pub.Bom_Revision_Tbl_Type;
v_x_bom_component_tbl Bom_Bo_pub.Bom_Comps_Tbl_Type;
v_x_bom_ref_designator_tbl Bom_Bo_Pub.Bom_Ref_Designator_Tbl_Type;
v_x_bom_sub_component_tbl Bom_Bo_Pub.Bom_Sub_Component_Tbl_Type;
v_assembly_item_name VARCHAR2(81);
v_organization_code VARCHAR2(3);
v_x_return_status VARCHAR2(2000);
v_x_msg_count NUMBER;
v_msg_text1 VARCHAR2(230);
v_msg_text2 VARCHAR2(230);
v_ii NUMBER;
v_jj NUMBER;
v_org_id NUMBER;
v_job_id NUMBER;
v_batch_num VARCHAR2(30);
v_record_type VARCHAR2(1);
v_item_id VARCHAR2(81);
v_component_id VARCHAR2(81);
v_error_flag VARCHAR2(1);
v_organization_id NUMBER;
v_error_message VARCHAR2(2000);
v_error VARCHAR2(2000);
v_error_msg VARCHAR2(2000);
CURSOR cur_bom_header_data IS
SELECT rtrim(xbomi.item_number) item_number
,xbomi.organization_code organization_code
,xbomi.assembly_type
,xbomi.SPECIFIC_ASSEMBLY_COMMENT
,xbomi.data_flow
,xbomi.status
,xbomi.record_type
FROM XXBOM_BILL_OF_MTLS_IFACE_VA xbomi
WHERE xbomi.status IN ('PICK', 'ERRSTG', 'ERRINT')
AND xbomi.data_flow = 'C'
AND xbomi.record_type = 'A';
CURSOR cur_bom_lines_data (p_item_name VARCHAR2, p_organization_code VARCHAR2) IS
SELECT rtrim(assembly_item_number) assembly_item_number
,organization_code
,NVL(effectivity_date, TRUNC(SYSDATE+1)) effectivity_date
,disable_date
,nvl(operation_seq_num,1) operation_seq_num
,component_item_number
,component_quantity
,component_sequence_id
,wip_supply_type
,supply_subinventory
,location_name
,optional
,mutually_exclusive_options
,low_quantity
,high_quantity
,check_atp
,shipping_allowed
,required_to_ship
,required_for_revenue
,include_on_ship_docs
FROM XXBOM_INV_COMPS_IFACE_VA
WHERE rtrim(assembly_item_number) = p_item_name
AND organization_code = p_organization_code;
CURSOR cur_bom_designator_data (p_item_name VARCHAR2, p_organization_code VARCHAR2
,p_component_item_number VARCHAR2,p_op_seq_num IN VARCHAR2) IS
SELECT rtrim(xbdi.assembly_item_name) assembly_item_name
,xbdi.organization_code
,NVL(xbdi.start_effective_date, TRUNC(SYSDATE+1)) start_effective_date
,nvl(xbdi.operation_sequence_number,1) operation_sequence_number
,rtrim(xbdi.component_item_name) component_item_name
,xbdi.reference_designator_name
,xbdi.ref_designator_comment
,xbdi.attribute1
,xbdi.attribute2
,xbdi.attribute3
FROM XXBOM_DESIGNATOR_IFACE_VA xbdi
WHERE rtrim(xbdi.assembly_item_name) = p_item_name
AND xbdi.organization_code = p_organization_code
AND rtrim(xbdi.component_item_name) = p_component_item_number
AND nvl(xbdi.operation_sequence_number,-999) = nvl(p_op_seq_num,nvl(xbdi.operation_sequence_number,-999));
BEGIN
fnd_file.put_line(fnd_file.LOG,'XXBOM_IMPORT Importing BOM ');
v_org_id := FND_PROFILE.VALUE('ORG_ID');
/* Getting Batch Number */
SELECT xxfnd_job_s.NEXTVAL
INTO v_job_id
FROM dual;
v_batch_num := 'XXBOMIMP'||v_job_id;
-- After Creating Batch
-- Need to initialize for calling BOM API
-- Each database table that the program writes to requires system information, such as who is
-- trying to update the current record. User must provide this information to the import program
-- initializing certain variables. To initialize the varables the user must call the following
-- procedure.
-- Arguments are : user_id (fnd_user), responsibility_id (), Responsibility_application_id (), security_group_id (0).
fnd_file.put_line (fnd_file.LOG, ' User Id is '|| fnd_profile.value('USER_ID')||
' Responsibility Id is '||fnd_profile.value('RESP_ID')||
' Appl Id is '||fnd_profile.value('RESP_APPL_ID'));
FND_GLOBAL.apps_initialize (TO_NUMBER(fnd_profile.value('USER_ID')), TO_NUMBER(fnd_profile.value('RESP_ID')), TO_NUMBER(fnd_profile.value('RESP_APPL_ID')), 0);
-- Read the BOM header lines having status = PICK
FOR h IN cur_bom_header_data
LOOP -- header loop
-- Initializing Message Variable.
v_error_message := NULL;
/* Validation for Assembly Item Name */
BEGIN
v_error_flag := 'N';
BEGIN
SELECT ood.organization_id, ood.organization_code
INTO v_organization_id, v_organization_code
FROM org_organization_definitions ood
WHERE ood.organization_code = h.organization_code;
EXCEPTION
WHEN OTHERS THEN
v_error_flag := 'Y';
v_error := h.organization_code||' is Not a Valid Organization '|| ' -Item ' || h.item_number||';';
v_error_message := v_error_message||v_error;
fnd_file.put_line(fnd_file.LOG, v_error);
END;
IF v_error_flag = 'Y' THEN
UPDATE XXBOM_BILL_OF_MTLS_IFACE_VA
SET STATUS = 'ERRSTG',
BATCH_NUM = v_batch_num,
orac_error_desc = v_error_message,
LAST_UPDATE_DATE =sysdate, --Added by Mahesh on 22-Jul-2008,RFC#456370
LAST_UPDATED_BY =fnd_global.user_id --Added by Mahesh on 22-Jul-2008,RFC#456370
WHERE organization_code = h.organization_code
AND rtrim(item_number) = h.item_number;
--AND LAST_UPDATE_DATE =sysdate --Commented by Mahesh on 22-Jul-2008,RFC#456370
--AND LAST_UPDATED_BY =fnd_global.user_id; --Commented by Mahesh on 22-Jul-2008,RFC#456370
GOTO ORG;
END IF;
/* Validation for Assembly Item Name */
BEGIN
SELECT msi.segment1
INTO v_item_id
FROM mtl_system_items msi
WHERE msi.segment1 = h.item_number
AND msi.organization_id = v_organization_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_error_flag := 'Y';
v_error := h.item_number||' is Not a Valid Item '|| 'Org -'|| h.organization_code || ';';
v_error_message := v_error_message||v_error;
fnd_file.put_line(fnd_file.LOG, v_error);
END;
IF v_error_flag = 'Y' THEN
UPDATE XXBOM_BILL_OF_MTLS_IFACE_VA
SET STATUS = 'ERRSTG',
BATCH_NUM = v_batch_num,
orac_error_desc = v_error_message,
LAST_UPDATE_DATE =sysdate, --Added by Mahesh on 22-Jul-2008,RFC#456370
LAST_UPDATED_BY =fnd_global.user_id --Added by Mahesh on 22-Jul-2008,RFC#456370
WHERE organization_code = h.organization_code
AND rtrim(item_number) = h.item_number;
--AND LAST_UPDATE_DATE =sysdate --Commented by Mahesh on 22-Jul-2008,RFC#456370
--AND LAST_UPDATED_BY =fnd_global.user_id; --Commented by Mahesh on 22-Jul-2008,RFC#456370
GOTO ORG;
END IF;
v_bom_header_rec.assembly_item_name := h.item_number;
v_bom_header_rec.organization_code := v_organization_code;
v_bom_header_rec.alternate_bom_code := NULL;
v_bom_header_rec.Assembly_type := 1;
v_bom_header_rec.Transaction_Type := 'Create';
v_bom_header_rec.Return_Status := NULL;
v_bom_header_rec.Assembly_Comment := h.SPECIFIC_ASSEMBLY_COMMENT;
v_assembly_item_name := h.item_number;
v_ii := 0;
v_bom_component_tbl.DELETE;
v_bom_ref_designator_tbl.DELETE;
v_jj := 0;
FOR l IN cur_bom_lines_data (h.item_number, h.organization_code)
LOOP -- lines loop
BEGIN -- Validating component Item Name
SELECT msi.segment1
INTO v_component_id
FROM mtl_system_items msi
WHERE msi.segment1 = l.component_item_number
AND msi.organization_id = v_organization_id;
IF (l.effectivity_date<SYSDATE+1) THEN /*added for past dates */
l.effectivity_date:=SYSDATE+1;
END IF;
--Component Item validation over
v_ii := v_ii + 1;
-- Filling Bom Components data
v_bom_component_tbl(v_ii).organization_code := v_organization_code;
v_bom_component_tbl(v_ii).Assembly_Item_name := h.item_number;
v_bom_component_tbl(v_ii).Start_effective_date := l.effectivity_date;
v_bom_component_tbl(v_ii).Disable_date := l.disable_date;
v_bom_component_tbl(v_ii).Component_Item_Name := l.component_item_number;
v_bom_component_tbl(v_ii).Alternate_bom_code := NULL;
v_bom_component_tbl(v_ii).projected_yield := 1;
v_bom_component_tbl(v_ii).planning_percent := NULL;
v_bom_component_tbl(v_ii).quantity_related := NULL;
v_bom_component_tbl(v_ii).check_atp := l.check_atp;
v_bom_component_tbl(v_ii).Include_In_Cost_Rollup := 1;
v_bom_component_tbl(v_ii).Wip_Supply_Type := l.wip_supply_type;
v_bom_component_tbl(v_ii).So_Basis := NULL;
v_bom_component_tbl(v_ii).Optional := l.optional;
v_bom_component_tbl(v_ii).Mutually_Exclusive := l.mutually_exclusive_options;
v_bom_component_tbl(v_ii).Shipping_Allowed := l.shipping_allowed;
v_bom_component_tbl(v_ii).Required_To_Ship := l.required_to_ship;
v_bom_component_tbl(v_ii).Required_For_Revenue := l.required_for_revenue ;
v_bom_component_tbl(v_ii).Include_On_Ship_Docs := l.include_on_ship_docs;
v_bom_component_tbl(v_ii).Supply_Subinventory := l.supply_subinventory;
v_bom_component_tbl(v_ii).Location_Name := l.location_name;
v_bom_component_tbl(v_ii).Minimum_Allowed_Quantity := l.low_quantity;
v_bom_component_tbl(v_ii).Maximum_Allowed_Quantity := l.high_quantity;
v_bom_component_tbl(v_ii).Comments := NULL;
v_bom_component_tbl(v_ii).from_end_item_unit_number := NULL;
v_bom_component_tbl(v_ii).to_end_item_unit_number := NULL;
v_bom_component_tbl(v_ii).Item_Sequence_Number := l.component_sequence_id;
v_bom_component_tbl(v_ii).operation_Sequence_Number := l.OPERATION_SEQ_NUM;
v_bom_component_tbl(v_ii).Transaction_Type := 'Create';
v_bom_component_tbl(v_ii).Quantity_Per_Assembly := l.component_quantity;
v_bom_component_tbl(v_ii).return_status := NULL;
v_bom_component_tbl(v_ii).attribute1 := NULL;
v_bom_component_tbl(v_ii).attribute2 := NULL;
v_bom_component_tbl(v_ii).attribute3 := NULL;
FOR d in cur_bom_designator_data (h.item_number, v_organization_code
, l.component_item_number, l.operation_seq_num)
LOOP -- designator loop
-- Filling Designator Data
--Into the designator loop
v_jj := v_jj + 1;
v_bom_ref_designator_tbl(v_jj).organization_code := v_organization_code;
v_bom_ref_designator_tbl(v_jj).Assembly_item_name := h.item_number;
v_bom_ref_designator_tbl(v_jj).Alternate_Bom_Code := NULL;
v_bom_ref_designator_tbl(v_jj).Start_effective_date := l.effectivity_date;
v_bom_ref_designator_tbl(v_jj).component_item_name := l.component_item_number;
v_bom_ref_designator_tbl(v_jj).reference_designator_name := d.reference_designator_name;
v_bom_ref_designator_tbl(v_jj).Transaction_Type := 'Create';
v_bom_ref_designator_tbl(v_jj).ref_designator_comment := d.ref_designator_comment;
v_bom_ref_designator_tbl(v_jj).return_status := NULL;
v_bom_ref_designator_tbl(v_jj).attribute1 := d.attribute1;
v_bom_ref_designator_tbl(v_jj).attribute2 := d.attribute2;
v_bom_ref_designator_tbl(v_jj).attribute3 := d.attribute3;
v_bom_ref_designator_tbl(v_jj).operation_sequence_number := d.operation_sequence_number;
END LOOP; -- designator loop
EXCEPTION
WHEN OTHERS THEN
v_error_flag := 'Y';
v_error := ' BOM Component '||l.component_item_number||' is invalid'|| 'Org ' || l.organization_code ||';';
v_error_message := v_error_message||v_error;
fnd_file.put_line(fnd_file.LOG, v_error);
END; -- Validating component Item Name block
/* Updating entire BOM header record as ERRSTG */
IF v_error_flag = 'Y' THEN
UPDATE XXBOM_BILL_OF_MTLS_IFACE_VA
SET STATUS = 'ERRSTG',
BATCH_NUM = v_batch_num,
orac_error_desc = v_error_message,
LAST_UPDATE_DATE =sysdate, --Added by Mahesh on 22-Jul-2008,RFC#456370
LAST_UPDATED_BY =fnd_global.user_id --Added by Mahesh on 22-Jul-2008,RFC#456370
WHERE organization_code = h.organization_code
AND rtrim(item_number) = h.item_number;
--AND LAST_UPDATE_DATE =sysdate --Commented by Mahesh on 22-Jul-2008,RFC#456370
--AND LAST_UPDATED_BY =fnd_global.user_id; --Commented by Mahesh on 22-Jul-2008,RFC#456370
EXIT;
END IF;
END LOOP; -- lines loop
IF v_error_flag != 'Y' THEN
Error_Handler.Initialize;
-- Call the Public API
-- The public API is the user's interface to the import program. The user must call it
-- programatically, while sending in one business object at a time. The public API returns
-- the processed business object, the business object status, and a count of all
-- associated error and warning messages.
BEGIN
v_x_return_status := NULL;
v_x_msg_count := NULL;
bom_bo_pub.Process_Bom
( p_bo_identifier => 'BOM'
,p_api_version_number => 1.0
,p_init_msg_list => TRUE
,p_bom_header_rec => v_bom_header_rec
,p_bom_revision_tbl => v_bom_revision_tbl
,p_bom_component_tbl => v_bom_component_tbl
,p_bom_ref_designator_tbl => v_bom_ref_designator_tbl
,p_bom_sub_component_tbl => v_bom_sub_component_tbl
,x_bom_header_rec => v_x_bom_header_rec
,x_bom_revision_tbl => v_x_bom_revision_tbl
,x_bom_component_tbl => v_x_bom_component_tbl
,x_bom_ref_designator_tbl => v_x_bom_ref_designator_tbl
,x_bom_sub_component_tbl => v_x_bom_sub_component_tbl
,x_return_status => v_x_return_status
,x_msg_count => v_x_msg_count
,p_debug => 'N' -- This should be 'N' to not to have Debug
,p_output_dir => NULL -- This should be NULL and can be
,p_debug_filename => '' -- This field to be NULL if we do not want any log file in tmp dir
);
EXCEPTION
WHEN OTHERS THEN
v_error_flag := 'Y';
fnd_file.put_line(fnd_file.LOG,'Return Status = '||v_x_return_status || ' Err '|| SQLERRM);
END;
fnd_file.new_line(fnd_file.LOG,1);
fnd_file.put_line(fnd_file.LOG,'Assembly Item: '||v_assembly_item_name);
fnd_file.put_line(fnd_file.LOG,'Return Status = '||v_x_return_status);
fnd_file.put_line(fnd_file.LOG,'Message Count = '||v_x_msg_count);
/**** Error messages ****/
--After API
Error_Handler.Get_message_list(v_error_message_list);
IF NVL(v_x_return_status, '-1') <> 'S' THEN
fnd_file.put_line(fnd_file.LOG,'error-arm1');
-- Error Processing
FOR v_ii IN 1..v_x_msg_count LOOP
fnd_file.put_line(fnd_file.LOG,TO_CHAR(v_ii)||' MESSAGE TYPE: '||v_error_message_list(v_ii).message_type);
fnd_file.put_line(fnd_file.LOG,'MESSAGE TEXT: '||v_error_message_list(v_ii).message_text);
v_error_msg:=trim(SUBSTR(v_error_msg||', '||v_error_message_list(v_ii).message_text,1,2000));
END LOOP;
-- The business object APIs do not issue commits or rollbacks. It is the responsibility of
-- the calling code to issue them. This ensures that parts of the transactions are not left
-- in the database. If an error occurs, the whole transaction is rolled back.
ROLLBACK;
UPDATE XXBOM_BILL_OF_MTLS_IFACE_VA
SET STATUS = 'ERRINT',
BATCH_NUM = v_batch_num,
ORAC_ERROR_DESC = v_error_msg,
LAST_UPDATE_DATE =sysdate, --Added by Mahesh on 22-Jul-2008,RFC#456370
LAST_UPDATED_BY =fnd_global.user_id --Added by Mahesh on 22-Jul-2008,RFC#456370
WHERE organization_code = h.organization_code
AND rtrim(item_number) = h.item_number;
--AND LAST_UPDATE_DATE =sysdate --Commented by Mahesh on 22-Jul-2008,RFC#456370
--AND LAST_UPDATED_BY =fnd_global.user_id; --Commented by Mahesh on 22-Jul-2008,RFC#456370
v_error_msg:=NULL;
ELSE
--After success
UPDATE XXBOM_BILL_OF_MTLS_IFACE_VA
SET STATUS = 'PROCESSED',
BATCH_NUM = v_batch_num,
LAST_UPDATE_DATE =sysdate, --Added by Mahesh on 22-Jul-2008,RFC#456370
LAST_UPDATED_BY =fnd_global.user_id --Added by Mahesh on 22-Jul-2008,RFC#456370
WHERE organization_code = h.organization_code
AND rtrim(item_number) = h.item_number;
--AND LAST_UPDATE_DATE =sysdate --Commented by Mahesh on 22-Jul-2008,RFC#456370
--AND LAST_UPDATED_BY =fnd_global.user_id; --Commented by Mahesh on 22-Jul-2008,RFC#456370
END IF;-- if NVL(v_x_return_status, '-1') <> 'S'
v_error_msg:=NULL;
COMMIT;
END IF;--if v_error_flag != 'Y'
<<ORG>>
NULL;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_error_flag := 'Y';
fnd_file.put_line(fnd_file.LOG,'Return Status = '||v_x_return_status);
UPDATE XXBOM_BILL_OF_MTLS_IFACE_VA
SET STATUS = 'ERRSTG',
BATCH_NUM = v_batch_num,
orac_error_desc = v_error_message,
LAST_UPDATE_DATE =sysdate, --Added by Mahesh on 22-Jul-2008,RFC#456370
LAST_UPDATED_BY =fnd_global.user_id --Added by Mahesh on 22-Jul-2008,RFC#456370
WHERE organization_code = h.organization_code
AND rtrim(item_number) = h.item_number;
--AND LAST_UPDATE_DATE =sysdate --Commented by Mahesh on 22-Jul-2008,RFC#456370
--AND LAST_UPDATED_BY =fnd_global.user_id; --Commented by Mahesh on 22-Jul-2008,RFC#456370
fnd_file.put_line(fnd_file.LOG, h.item_number||' Item is Invalid!' || ' Org'|| h.organization_code);
END;
END LOOP; -- header loop
fnd_file.new_line(fnd_file.LOG,1);
fnd_file.put_line(fnd_file.LOG,'Process Completed !');
END XXBOM_IMPORT_VA_PRC; /*End of the procedure*/
/
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Need your help in tuning the query [message #595703 is a reply to message #595700] |
Fri, 13 September 2013 00:00 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
SELECT RTRIM (xbdi.assembly_item_name) assembly_item_name,
xbdi.organization_code,
NVL (xbdi.start_effective_date, TRUNC (SYSDATE + 1) ) start_effective_date,
NVL (xbdi.operation_sequence_number, 1) operation_sequence_number,
RTRIM (xbdi.component_item_name) component_item_name,
xbdi.reference_designator_name, xbdi.ref_designator_comment,
xbdi.attribute1, xbdi.attribute2, xbdi.attribute3
FROM xxbom_designator_iface_va xbdi
WHERE RTRIM (xbdi.assembly_item_name) = :b4
AND xbdi.organization_code = :b3
AND RTRIM (xbdi.component_item_name) = :b2
AND NVL (xbdi.operation_sequence_number, -999) = NVL (:b1, NVL (xbdi.operation_sequence_number, -999))
if we are looking at things blindly, then the following index MIGHT maximize performance of this query. It will allow you to identify rows you want before visiting the table. This works well of course only if the % of rows identified is small and note also that the last column is only good for filter purposes not access so you fetch some index entries you don't want and have to filter them away.
create index i1 on xxbom_designator_iface_va
(
organization_code -- access
, RTRIM (assembly_item_name) -- access
, RTRIM (component_item_name) -- access
, operation_sequence_number -- filter
)
/
But the effectiveness of an index depends upon how many rows you actually want out of a table. For this I rely on the rough 2% rule. If I am fetching <2% of the rows in a table then an index may make sense. But if I am fetching >2% of the rows in a table then a table scan might make more sense. Obviously 2% is a varying number (though it seems to work well for all my non-exadata databases 9i/10g/11g databases). To know this, we need to know what parameter values you are using and/or we need to look at the generic averages of the relevant data. So try running this query for some insight.
select a.*
,ceil(a.rows_in_table/a.ndv_1) avg_rows_per_key_1
,ceil(a.rows_in_table/a.ndv_2) avg_rows_per_key_2
,ceil(a.rows_in_table/a.ndv_3) avg_rows_per_key_3
,ceil(a.rows_in_table/a.ndv_4) avg_rows_per_key_4
from (
select count(*) rows_in_table
,count(distinct organization_code) ndv_1
,count(distinct organization_code||','|| RTRIM (assembly_item_name)) ndv_2
,count(distinct organization_code||','|| RTRIM (assembly_item_name)||','||RTRIM (component_item_name)) ndv_3
,count(distinct organization_code||','|| RTRIM (assembly_item_name)||','||RTRIM (component_item_name||','|| NVL (xbdi.operation_sequence_number, -999))) ndv_4
from xxbom_designator_iface_va
) a
/
and then this one for any value combinations that give you trouble.
SELECT :b1,:b2,:b3,:b4,count(*) rowcount
FROM xxbom_designator_iface_va xbdi
WHERE RTRIM (xbdi.assembly_item_name) = :b4
AND xbdi.organization_code = :b3
AND RTRIM (xbdi.component_item_name) = :b2
AND NVL (xbdi.operation_sequence_number, -999) = NVL (:b1, NVL (xbdi.operation_sequence_number, -999))
/
DO NOT just create the index and then say thanks if your query goes fast. Part of the forum is to share so please run the queries I have provided so we can talk about them.
Kevin
|
|
|
|
Goto Forum:
Current Time: Thu Apr 18 16:41:46 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
