Home » RDBMS Server » Performance Tuning » improve the performance (merged 2)
improve the performance (merged 2) [message #398291] Wed, 15 April 2009 23:29 Go to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
select /*+ parallel(con,2) parallel(CON_DET,2) parallel(colh,2) */
con.LE_CODE LE_Code,
con.LE_NAME LE_Name,
COLH.LOB_CODE Organisation_LOB_Code,
COLH.LOB_DESC Organisation_LOB_Description,
COLH.SHORT_LOB_DESC Org_LOB_Short_Desc,
CON_DET.CON_FIRST_NAME Contact_First_Name,
CON_DET.CON_LAST_NAME Contact_Last_Name,
CON_DET.CON_TEL_NUMBER Contact_Telephone_Number,
CON_DET.CON_EMAIL_ADDRESS Contact_Email_Address,
CON_DET.CREATED Contact_Created_Date,
CON_DET.CON_CREATED_BY Contact_Created_By,
CON_DET.CON_TITLE Contact_Title,
case CON_DET.PHONE_CONSENT when 'C' then 'Yes' when 'D' then 'No' when 'X' then 'Not Asked'
else CON_DET.PHONE_CONSENT end Phone_Consent,
case CON_DET.EMAIL_CONSENT when 'C' then 'Yes' when 'D' then 'No' when 'X' then 'Not Asked'
else CON_DET.EMAIL_CONSENT end  Email_Consent,
case CON_DET.ADDRESS_CONSENT when 'C' then 'Yes' when 'D' then 'No' when 'X' then 'Not Asked'
else CON_DET.ADDRESS_CONSENT end Address_Consent,
CON_DET.PRIMARY_CONTACT Primary_Contact,
CON_DET.RTC RTC,
CON_DET.CON_ROW_ID Con_Rowid
from
CONTACT CON,
DETAILS CON_DET,
COLH COLH
where
CON.ROW_ID = con_det.CON_ROW_ID AND
COLH.ROW_ID(+) = con_det.CON_ORG_ID


Hi

Can you please let me know how can i modify the query so that the performance of the query improves.

The count of this query fetches 1.4 million recs and it takes 20-25 mins for getting the output.

Indexes are present in the tables and are in proper use.
Re: improving query performance [message #398292 is a reply to message #398291] Wed, 15 April 2009 23:34 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/143594/136107/

Do not cross/multi-post!

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
improve the performance [message #398293 is a reply to message #398291] Wed, 15 April 2009 23:34 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
select /*+ parallel(con,2) parallel(CON_DET,2) parallel(colh,2) */
con.LE_CODE LE_Code,
con.LE_NAME LE_Name,
COLH.LOB_CODE Organisation_LOB_Code,
COLH.LOB_DESC Organisation_LOB_Description,
COLH.SHORT_LOB_DESC Org_LOB_Short_Desc,
CON_DET.CON_FIRST_NAME Contact_First_Name,
CON_DET.CON_LAST_NAME Contact_Last_Name,
CON_DET.CON_TEL_NUMBER Contact_Telephone_Number,
CON_DET.CON_EMAIL_ADDRESS Contact_Email_Address,
CON_DET.CREATED Contact_Created_Date,
CON_DET.CON_CREATED_BY Contact_Created_By,
CON_DET.CON_TITLE Contact_Title,
case CON_DET.PHONE_CONSENT when 'C' then 'Yes' when 'D' then 'No' when 'X' then 'Not Asked'
else CON_DET.PHONE_CONSENT end Phone_Consent,
case CON_DET.EMAIL_CONSENT when 'C' then 'Yes' when 'D' then 'No' when 'X' then 'Not Asked'
else CON_DET.EMAIL_CONSENT end  Email_Consent,
case CON_DET.ADDRESS_CONSENT when 'C' then 'Yes' when 'D' then 'No' when 'X' then 'Not Asked'
else CON_DET.ADDRESS_CONSENT end Address_Consent,
CON_DET.PRIMARY_CONTACT Primary_Contact,
CON_DET.RTC RTC,
CON_DET.CON_ROW_ID Con_Rowid
from
CONTACT CON,
DETAILS CON_DET,
COLH COLH
where
CON.ROW_ID = con_det.CON_ROW_ID AND
COLH.ROW_ID(+) = con_det.CON_ORG_ID


Hi

Can you please let me know how can i modify the query so that the performance of the query improves.

The count of this query fetches 1.4 million recs and it takes 20-25 mins for getting the output.

Indexes are present in the tables and are in proper use.
Re: improve the performance [message #398294 is a reply to message #398293] Wed, 15 April 2009 23:36 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/143594/136107/

Do not cross/multi-post!

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

Stop spamming this forum!
Re: improving query performance [message #398296 is a reply to message #398291] Wed, 15 April 2009 23:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First remove the hints then provide the usual and required information.

Regards
Michel

[Updated on: Thu, 16 April 2009 00:36]

Report message to a moderator

Re: improve the performance [message #398297 is a reply to message #398293] Wed, 15 April 2009 23:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First remove the hints then provide the usual and required information.

Regards
Michel

[Updated on: Thu, 16 April 2009 00:35]

Report message to a moderator

Re: improve the performance [message #398301 is a reply to message #398294] Wed, 15 April 2009 23:46 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
sorry for the same.

there is some network issue in the internet.hence want sure whether the messga ewas sent or not.
Re: improve the performance [message #398303 is a reply to message #398293] Wed, 15 April 2009 23:49 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
reposting without hint.

Thanks

select 
con.LE_CODE LE_Code,
con.LE_NAME LE_Name,
COLH.LOB_CODE Organisation_LOB_Code,
COLH.LOB_DESC Organisation_LOB_Description,
COLH.SHORT_LOB_DESC Org_LOB_Short_Desc,
CON_DET.CON_FIRST_NAME Contact_First_Name,
CON_DET.CON_LAST_NAME Contact_Last_Name,
CON_DET.CON_TEL_NUMBER Contact_Telephone_Number,
CON_DET.CON_EMAIL_ADDRESS Contact_Email_Address,
CON_DET.CREATED Contact_Created_Date,
CON_DET.CON_CREATED_BY Contact_Created_By,
CON_DET.CON_TITLE Contact_Title,
case CON_DET.PHONE_CONSENT when 'C' then 'Yes' when 'D' then 'No' when 'X' then 'Not Asked'
else CON_DET.PHONE_CONSENT end Phone_Consent,
case CON_DET.EMAIL_CONSENT when 'C' then 'Yes' when 'D' then 'No' when 'X' then 'Not Asked'
else CON_DET.EMAIL_CONSENT end  Email_Consent,
case CON_DET.ADDRESS_CONSENT when 'C' then 'Yes' when 'D' then 'No' when 'X' then 'Not Asked'
else CON_DET.ADDRESS_CONSENT end Address_Consent,
CON_DET.PRIMARY_CONTACT Primary_Contact,
CON_DET.RTC RTC,
CON_DET.CON_ROW_ID Con_Rowid
from
CONTACT CON,
DETAILS CON_DET,
COLH COLH
where
CON.ROW_ID = con_det.CON_ROW_ID AND
COLH.ROW_ID(+) = con_det.CON_ORG_ID


Hi

Can you please let me know how can i modify the query so that the performance of the query improves.

The count of this query fetches 1.4 million recs and it takes 20-25 mins for getting the output.

Indexes are present in the tables and are in proper use.
Re: improve the performance [message #398314 is a reply to message #398303] Thu, 16 April 2009 00:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
When I said removed the hint, it was not in the post but in your program.
You still didn't post the mandatory information.

Regards
Michel
Re: improve the performance [message #398473 is a reply to message #398314] Thu, 16 April 2009 06:36 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You're getting about 1000 rows per second back, which isn't bad performance.

In the absence of anything useful, like an explain plan, there's not a lot of advice we can give other than things like 'Are the stats up to date'/
Re: improve the performance [message #398474 is a reply to message #398473] Thu, 16 April 2009 06:40 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Indexes are present in the tables and are in proper use.


What does the secondhalf of this mean?

Can you tell us what the proper use of indexes for this query is?

I'd be quite worried if this query were using indexes for anything other than Fast Full Index scans in the case where an index had all the columns required in the Select list in it.
Previous Topic: want to change snap level in 10g r2
Next Topic: problem while generating AWR report
Goto Forum:
  


Current Time: Sun Dec 04 10:34:10 CST 2016

Total time taken to generate the page: 0.07398 seconds