Home » RDBMS Server » Performance Tuning » Performance tuning
| Performance tuning [message #249395] |
Wed, 04 July 2007 05:00  |
ammishra
Messages: 179
Registered: January 2007
Location: india
|
Senior Member |
|
|
Hi,
This query is working fine (3 sec) with out using the distinct key word. after using the distinct keyword this query is going to hang. Any body can suggest why is going to happened or any other alternative for the same
select distinct " +
"nvl(a.sample_request_no,''), " +
"c.cisco_log, " +
"nvl(c.mcn_priority_value,0), " +
"b.case_id, " +
" nvl(d.new_mfr_part_no,' '), " +
//"b.mfg_part_num, " +
"b.cisco_part, " +
"b.assembly_item, " +
"nvl(a.quantity,'0'), " +
"nvl(a.REQUEST_DATE,''), " +
"nvl(to_char(c.sample_availability_date,'DD-MON-YYYY'),' '), " +
"nvl(a.sample_owner,'montoya'), " +
"nvl(a.SPECIAL_REQUREMENTS,' '), " +
"nvl(to_char(a.sample_order_date,'DD-MON-YYYY'),' '), " +
"nvl(to_char(b.bu_mcn_creation_date + 14,'DD-MON-YYYY'),' '), " +
"nvl(a.requestor,' ') " +
"from " +
"CCM_CCN_SAMPLE_DATA a, " +
"ccm_ccn_header c, " +
" ccm_ccn_cisco_part_details d, " +
"ccm_ccn_assembly_affected b " +
"where ( a.requestor = ? or b.assembly_mgr = ?) " +
"and c.cisco_log = b.cisco_log " +
//"and b.case_id = a.case_id (+) " + //commented for 9i
" and b.case_id = a.case_id " +
" and d.cisco_log = b.cisco_log (+) " +
" and d.current_item_no = b.cisco_part (+) " +
" and a.status in('Open','Rejected') and nvl(deleted_flag,'N') = 'N'")
--Yash
|
|
|
|
|
|
|
|
| Re: Performance tuning [message #249430 is a reply to message #249406] |
Wed, 04 July 2007 08:17  |
ammishra
Messages: 179
Registered: January 2007
Location: india
|
Senior Member |
|
|
|
Michel!! i am asking when I am running this query without using 'distinct' keyword , query has executed within 3 sec and return the duplicate record . When i am using distinct keyword for eliminating duplicate record in result of this query, it is going to hang..Know let me still you are not able to understand my problem..
|
|
|
|
|
|
| Re: Performance tuning [message #249436 is a reply to message #249430] |
Wed, 04 July 2007 08:29 |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
What I am asking is you show a little respect towards those who help you.
Remove all these Java stuff and format the query.
You want an answer, first post correctly.
It is useless I post you the links to the stickies you should have read far long ago as you are "senior" member.
Regards
Michel
[Updated on: Wed, 04 July 2007 08:31] Report message to a moderator |
|
|
|
| Re: Performance tuning [message #249445 is a reply to message #249436] |
Wed, 04 July 2007 09:01 |
ammishra
Messages: 179
Registered: January 2007
Location: india
|
Senior Member |
|
|
I am sorry!! Actually this query was given by my team mate. He doesn’t have account in this forum. so I have posted this query behalf of him ..That is my fault I have to chk properly. i have removed all the java stuff on this query but I was run this query on toad ..now how can I terminate this query bcz If I close the toad then I have to reformat the query …
|
|
|
|
| Re: Performance tuning [message #249448 is a reply to message #249445] |
Wed, 04 July 2007 09:34 |
ammishra
Messages: 179
Registered: January 2007
Location: india
|
Senior Member |
|
|
Hi Michel!!
this is formatted query.
select distinct nvl(a.sample_request_no,''),
c.cisco_log,
nvl(c.mcn_priority_value,0),
b.case_id,
nvl(d.new_mfr_part_no,' '),
b.cisco_part,
b.assembly_item,
nvl(a.quantity,'0'),
nvl(a.REQUEST_DATE,''),
nvl(to_char(c.sample_availability_date,'DD-MON-YYYY'),' '),
nvl(a.sample_owner,'montoya'),
nvl(a.SPECIAL_REQUREMENTS,' '),
nvl(to_char(a.sample_order_date,'DD-MON-YYYY'),' '),
nvl(to_char(b.bu_mcn_creation_date + 14,'DD-MON-YYYY'),' '),
nvl(a.requestor,' ')
from
CCM_CCN_SAMPLE_DATA a,
ccm_ccn_header c,
ccm_ccn_cisco_part_details d,
ccm_ccn_assembly_affected b
where ( a.requestor = 'parner' or b.assembly_mgr = 'shykumar1')
and c.cisco_log = b.cisco_log
and b.case_id = a.case_id
and d.cisco_log = b.cisco_log (+)
and d.current_item_no = b.cisco_part (+)
and a.status in('Open','Rejected') and nvl(deleted_flag,'N') = 'N'
table descriptions -:
CREATE TABLE CCM_CCN_SAMPLE_DATA
(
SAMPLE_REQUEST_NO NUMBER NOT NULL,
CISCO_LOG NUMBER NOT NULL,
CASE_ID NUMBER NOT NULL,
BUSINESS_UNIT VARCHAR2(40 BYTE),
QUANTITY NUMBER,
REQUEST_DATE DATE,
SAMPLE_OWNER VARCHAR2(40 BYTE),
SPECIAL_REQUREMENTS VARCHAR2(2000 BYTE),
EXP_DELIVERY_DATE DATE,
BU_RECEIPT_DATE DATE,
STATUS VARCHAR2(40 BYTE),
CREATION_DATE DATE,
CREATED_BY VARCHAR2(40 BYTE),
LAST_UPDATE_DATE DATE,
LAST_UPDATED_LOGIN VARCHAR2(40 BYTE),
DELETED_FLAG VARCHAR2(10 BYTE),
ITEM_ID NUMBER,
REJECT_COMMENTS VARCHAR2(2000 BYTE),
REQUESTOR VARCHAR2(40 BYTE),
SAMPLE_ORDER_DATE DATE,
CE_SAMPLES_DEADLINE_DATE DATE
)
CREATE TABLE CCM_CCN_HEADER
(
PCN_NUMBER VARCHAR2(100 BYTE) NOT NULL,
CISCO_LOG NUMBER NOT NULL,
PCN_TYPE VARCHAR2(20 BYTE) NOT NULL,
STATUS VARCHAR2(15 BYTE) NOT NULL,
PCN_OWNER VARCHAR2(20 BYTE) NOT NULL,
PCN_DATE DATE,
CLOSED_DATE DATE,
LAST_ORDER_DATE DATE,
MATERIAL_MANAGER VARCHAR2(30 BYTE),
COMPONENT_ENGINEER VARCHAR2(30 BYTE),
RECOMMENDATION VARCHAR2(2000 BYTE),
EFFECTIVITY_DATE DATE,
RECEIPT_DATE DATE,
MANUFACTURER VARCHAR2(50 BYTE),
PCN_DOC_ID NUMBER,
CREATED_BY VARCHAR2(20 BYTE),
CREATION_DATE DATE,
LAST_UPDATED_BY VARCHAR2(20 BYTE),
LAST_UPDATE_DATE DATE,
SAMPLE_AVAILABILITY_DATE DATE,
SAMPLE_CONTACT_FIRST_NAME VARCHAR2(100 BYTE),
SAMPLE_CONTACT_LAST_NAME VARCHAR2(100 BYTE),
SAMPLE_ADDRESS VARCHAR2(2000 BYTE),
SAMPLE_CITY VARCHAR2(40 BYTE),
SAMPLE_STATE VARCHAR2(40 BYTE),
SAMPLE_ZIP_CODE VARCHAR2(20 BYTE),
SAMPLE_WORK_PHONE VARCHAR2(25 BYTE),
SAMPLE_CELL_PHONE VARCHAR2(25 BYTE),
SAMPLE_EMAIL VARCHAR2(40 BYTE),
SAMPLE_NOTES VARCHAR2(2000 BYTE),
MCN_TYPE VARCHAR2(40 BYTE),
MCN_TYPE_COMMENTS VARCHAR2(2000 BYTE),
PRIORITY VARCHAR2(40 BYTE),
CC_EMAIL VARCHAR2(40 BYTE),
SUPPLIER_AUDITED VARCHAR2(10 BYTE),
SUPPLIER_AUDITED_COMMENTS VARCHAR2(2000 BYTE),
FIRST_LEVEL_QUAL VARCHAR2(10 BYTE),
FIRST_LEVEL_QUAL_COMMENTS VARCHAR2(2000 BYTE),
SECOND_LEVEL_QUAL VARCHAR2(10 BYTE),
SECOND_LEVEL_QUAL_COMMENTS VARCHAR2(2000 BYTE),
ANALYSIS_NEW_FORM VARCHAR2(10 BYTE),
ANALYSIS_NEW_FORM_COMMENTS VARCHAR2(2000 BYTE),
CE_TEST_RECOMMENDATION VARCHAR2(2000 BYTE),
CE_TEST_REC_COMMENTS VARCHAR2(2000 BYTE),
STATUS_TRACK VARCHAR2(20 BYTE),
CE_CASE_CREATION_DATE DATE,
IMPLEMENTATION_DATE DATE,
PROPOSED_IMPLEMENTATION_DATE DATE,
MCN_PRIORITY_VALUE NUMBER(5,2),
MCN_PRIORITY_COMMENT VARCHAR2(1000 BYTE)
)
CREATE TABLE CCM_CCN_CISCO_PART_DETAILS
(
CISCO_LOG NUMBER NOT NULL,
MCN_PART_ID NUMBER NOT NULL,
CURRENT_ITEM_NO VARCHAR2(50 BYTE),
CURRENT_MFR_PART_NO VARCHAR2(50 BYTE),
NEW_ITEM_NO VARCHAR2(50 BYTE),
NEW_MFR_PART_NO VARCHAR2(50 BYTE),
NEW_MFR_NAME VARCHAR2(50 BYTE),
CREATED_BY VARCHAR2(20 BYTE),
CREATION_DATE DATE,
LAST_UPDATED_BY VARCHAR2(20 BYTE),
LAST_UPDATE_DATE DATE,
STATUS VARCHAR2(40 BYTE),
LTB VARCHAR2(3 BYTE)
)
CREATE TABLE CCM_CCN_ASSEMBLY_AFFECTED
(
ID NUMBER,
CISCO_PART VARCHAR2(50 BYTE),
MFG_PART_NUM VARCHAR2(50 BYTE),
BUSINESS_UNIT VARCHAR2(50 BYTE),
OWNER_EMAIL VARCHAR2(30 BYTE),
ASSEMBLY_MGR VARCHAR2(30 BYTE),
ASSEMBLY_ITEM VARCHAR2(40 BYTE),
CISCO_LOG NUMBER,
EFFECTIVE_DATE DATE,
ASSEMBLY_DESCRIPTION VARCHAR2(40 BYTE),
CASE_ID NUMBER,
DELETE_FLAG VARCHAR2(1 BYTE),
COMMENTS VARCHAR2(2000 BYTE),
TEST_DECISION VARCHAR2(40 BYTE),
PRODUCT_FAMILY VARCHAR2(2000 BYTE),
FIRST_LEVEL_ASSEMBLY VARCHAR2(2000 BYTE),
BU_MCN_CLOSED_DATE DATE,
BU_MCN_STATUS VARCHAR2(40 BYTE),
BU_MCN_CREATION_DATE DATE,
SAMPLE_QTY VARCHAR2(40 BYTE),
SPECIAL_REQUIREMENTS VARCHAR2(2000 BYTE),
BU_MCN_PRIORITY_VALUE NUMBER(5,2),
BU_MCN_PRIORITY_COMMENT VARCHAR2(2000 BYTE),
CC_EMAIL VARCHAR2(40 BYTE),
COMPONENT_SEQUENCE_ID NUMBER,
QUANTITY NUMBER
)
Plz let me know if i missed some thing ..
--Yash
|
|
|
|
|
|
| Re: Performance tuning [message #249532 is a reply to message #249481] |
Wed, 04 July 2007 22:13 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
DISTINCT causes a SORT, which means you only start to see data after all of the results have been found and sorted.
Without DISTINCT, the first row may be returned as soon as it is found. This gives the appearance of the query running much faster, but the difference is less if you time how long it takes to return every row.
Ross Leishman
|
|
|
|
| Re: Performance tuning [message #249882 is a reply to message #249532] |
Fri, 06 July 2007 07:11 |
ammishra
Messages: 179
Registered: January 2007
Location: india
|
Senior Member |
|
|
Hi michel!!
explain plan without distinct is as
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=CHOOSE 13 2138
NESTED LOOPS 13 1 K 2138
HASH JOIN 13 1 K 2125
TABLE ACCESS FULL PDMIS.CCM_CCN_SAMPLE_DATA 6 K 286 K 258
HASH JOIN OUTER 145 K 9 M 1795
TABLE ACCESS FULL PDMIS.CCM_CCN_CISCO_PART_DETAILS 145 K 4 M 176
TABLE ACCESS FULL PDMIS.CCM_CCN_ASSEMBLY_AFFECTED
537 K 21 M 1084
TABLE ACCESS BY INDEX ROWID PDMIS.CCM_CCN_HEADER 1 15 1
INDEX UNIQUE SCAN PDMIS.SYS_C0028031 1
with distinct is as-:
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=CHOOSE 13 2140
SORT UNIQUE 13 1 K 2140
NESTED LOOPS 13 1 K 2138
HASH JOIN 13 1 K 2125
TABLE ACCESS FULL PDMIS.CCM_CCN_SAMPLE_DATA 6 K 286 K 258
HASH JOIN OUTER 145 K 9 M 1795
TABLE ACCESS FULL PDMIS.CCM_CCN_CISCO_PART_DETAILS 145 K 4 M 176
TABLE ACCESS FULL PDMIS.CCM_CCN_ASSEMBLY_AFFECTED 537 K 21 M 1084
TABLE ACCESS BY INDEX ROWID PDMIS.CCM_CCN_HEADER 1 15 1
INDEX UNIQUE SCAN PDMIS.SYS_C0028031 1
--Yash
|
|
|
|
| Re: Performance tuning [message #250009 is a reply to message #249882] |
Fri, 06 July 2007 16:16 |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
In you case the only difference is SORT DISTINCT.
So as Ross already mentioned the performance of both queries differs only by time needed to sort the selected rows.
HTH.
Michael
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Nov 23 08:15:21 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
Tnaks Sir