Home » RDBMS Server » Performance Tuning » Performance tuning
Performance tuning [message #249395] Wed, 04 July 2007 05:00 Go to next message
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 #249406 is a reply to message #249395] Wed, 04 July 2007 06:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
First format your crap and post a correct question.

Regards
Michel
Re: Performance tuning [message #249426 is a reply to message #249406] Wed, 04 July 2007 08:05 Go to previous messageGo to next message
ShivrajGutte
Messages: 21
Registered: April 2007
Location: PUNE
Junior Member
Laughing Tnaks Sir
Re: Performance tuning [message #249430 is a reply to message #249406] Wed, 04 July 2007 08:17 Go to previous messageGo to next message
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 #249432 is a reply to message #249430] Wed, 04 July 2007 08:20 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
If you want table structure I can provide you ..
Re: Performance tuning [message #249436 is a reply to message #249430] Wed, 04 July 2007 08:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68722
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #249481 is a reply to message #249395] Wed, 04 July 2007 11:43 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
Post EXPLAIN for both cases (with and without distinct)

Michael
Re: Performance tuning [message #249532 is a reply to message #249481] Wed, 04 July 2007 22:13 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
Re: Performance tuning [message #250572 is a reply to message #250009] Tue, 10 July 2007 09:10 Go to previous message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Michel!!

can we tune this query. it is taking so much time.

--Yash
Previous Topic: detecting environment issues
Next Topic: How Oracle using SAMPLE Clause on statistics?
Goto Forum:
  


Current Time: Thu Dec 12 05:56:52 CST 2024