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 Go to next message
nishant87
Messages: 38
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 Go to previous messageGo to next message
BlackSwan
Messages: 22515
Registered: January 2009
Senior Member
nishant87 wrote on Thu, 12 September 2013 08:18
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.


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 #595628 is a reply to message #595627] Thu, 12 September 2013 10:25 Go to previous messageGo to next message
nishant87
Messages: 38
Registered: September 2013
Location: india
Member
But that query take longer time in completion
Re: Need your help in tuning the query [message #595629 is a reply to message #595628] Thu, 12 September 2013 10:26 Go to previous messageGo to next message
BlackSwan
Messages: 22515
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

>But that query take longer time in completion
post SQL & results that proves above is true.

Re: Need your help in tuning the query [message #595630 is a reply to message #595626] Thu, 12 September 2013 10:32 Go to previous messageGo to next message
John Watson
Messages: 4400
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and please read How to use [code] tags and make your code easier to read

You are scanning xxbom_designator_iface_va every time, 5000 blocks to retrieve one row (or less), are there no indexes on that table?
Re: Need your help in tuning the query [message #595631 is a reply to message #595630] Thu, 12 September 2013 10:35 Go to previous messageGo to next message
nishant87
Messages: 38
Registered: September 2013
Location: india
Member
There are indexes on COMPONENT_ITEM_NAME, ORGANIZATION_CODE,OPERATION_SEQUENCE_NUMBER
Re: Need your help in tuning the query [message #595632 is a reply to message #595631] Thu, 12 September 2013 10:38 Go to previous messageGo to next message
John Watson
Messages: 4400
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 #595633 is a reply to message #595632] Thu, 12 September 2013 10:44 Go to previous messageGo to next message
nishant87
Messages: 38
Registered: September 2013
Location: india
Member
CREATE INDEX XXBOM_DESIGNATOR_IFACE_VA_X111 ON XXBOM_DESIGNATOR_IFACE_VA
(ASSEMBLY_ITEM_NAME || COMPONENT_ITEM_NAME || ORGANIZATION_CODE || OPERATION_SEQUENCE_NUMBER)
LOGGING
TABLESPACE EMRBOMX
PCTFREE 5
INITRANS 4
MAXTRANS 255
STORAGE (
INITIAL 16K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;

IS this fine or do i need to do some modification in upper part
Re: Need your help in tuning the query [message #595634 is a reply to message #595633] Thu, 12 September 2013 10:45 Go to previous messageGo to next message
BlackSwan
Messages: 22515
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Need your help in tuning the query [message #595635 is a reply to message #595633] Thu, 12 September 2013 10:48 Go to previous messageGo to next message
John Watson
Messages: 4400
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 #595638 is a reply to message #595635] Thu, 12 September 2013 10:52 Go to previous messageGo to next message
nishant87
Messages: 38
Registered: September 2013
Location: india
Member
If i am not wrong you mean to say we have to either go ahead with function based index or in another we have to see if rtrim is not necessary then we can remove it?
Re: Need your help in tuning the query [message #595640 is a reply to message #595638] Thu, 12 September 2013 10:56 Go to previous messageGo to next message
John Watson
Messages: 4400
Registered: January 2010
Location: Global Village
Senior Member
Exactly.
And don't use ||, name the columns as a comma separated list.
Re: Need your help in tuning the query [message #595656 is a reply to message #595640] Thu, 12 September 2013 11:55 Go to previous messageGo to next message
nishant87
Messages: 38
Registered: September 2013
Location: india
Member
assembly_item_name,component_item_name,organization_code and operation_sequence_number all are varchar2.i also don't think rtrim is required,but coming to NVL i am not sure about it.maybe business requirement
Re: Need your help in tuning the query [message #595658 is a reply to message #595656] Thu, 12 September 2013 11:57 Go to previous messageGo to next message
BlackSwan
Messages: 22515
Registered: January 2009
Senior Member
>But that query take longer time in completion
post SQL & results that proves above is true.
Re: Need your help in tuning the query [message #595673 is a reply to message #595658] Thu, 12 September 2013 13:26 Go to previous messageGo to next message
nishant87
Messages: 38
Registered: September 2013
Location: india
Member
can i paste the whole package here or can i attach it?
Re: Need your help in tuning the query [message #595674 is a reply to message #595673] Thu, 12 September 2013 13:31 Go to previous messageGo to next message
BlackSwan
Messages: 22515
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Need your help in tuning the query [message #595679 is a reply to message #595673] Thu, 12 September 2013 13:45 Go to previous messageGo to next message
nishant87
Messages: 38
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 #595680 is a reply to message #595679] Thu, 12 September 2013 13:45 Go to previous messageGo to next message
nishant87
Messages: 38
Registered: September 2013
Location: india
Member
please check the package and the query
Re: Need your help in tuning the query [message #595690 is a reply to message #595680] Thu, 12 September 2013 14:13 Go to previous messageGo to next message
Michel Cadot
Messages: 58605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.

Regards
Michel
Re: Need your help in tuning the query [message #595697 is a reply to message #595690] Thu, 12 September 2013 14:42 Go to previous messageGo to next message
nishant87
Messages: 38
Registered: September 2013
Location: india
Member
i have uploaded .txt file
Re: Need your help in tuning the query [message #595698 is a reply to message #595697] Thu, 12 September 2013 14:51 Go to previous messageGo to next message
BlackSwan
Messages: 22515
Registered: January 2009
Senior Member
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: Need your help in tuning the query [message #595700 is a reply to message #595698] Thu, 12 September 2013 15:06 Go to previous messageGo to next message
nishant87
Messages: 38
Registered: September 2013
Location: india
Member
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------
Plan hash value: 900675135

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 880 | 1450 (2)| 00:00:18 |
|* 1 | TABLE ACCESS FULL| XXBOM_DESIGNATOR_IFACE_VA | 11 | 880 | 1450 (2)| 00:00:18 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(RTRIM("XBDI"."ASSEMBLY_ITEM_NAME")=:B4 AND
RTRIM("XBDI"."COMPONENT_ITEM_NAME")=:B2 AND "XBDI"."ORGANIZATION_CODE"=:B3 AND
NVL("XBDI"."OPERATION_SEQUENCE_NUMBER",(-999))=NVL(:B1,NVL("XBDI"."OPERATION_SEQUENCE_N
UMBER",(-999))))

16 rows selected.
Re: Need your help in tuning the query [message #595703 is a reply to message #595700] Fri, 13 September 2013 00:00 Go to previous message
Kevin Meade
Messages: 1934
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
Previous Topic: Call from specified Machine not assigned to Database resource consumer.
Next Topic: snapshot skipped in between the scheduled time (AWR)
Goto Forum:
  


Current Time: Mon Jul 28 16:38:08 CDT 2014

Total time taken to generate the page: 0.27711 seconds