Home » RDBMS Server » Performance Tuning » improve the performance (merged 2)
| improve the performance (merged 2) [message #398291] |
Wed, 15 April 2009 23:29  |
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.
|
|
|
|
|
|
| improve the performance [message #398293 is a reply to message #398291] |
Wed, 15 April 2009 23:34  |
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 #398303 is a reply to message #398293] |
Wed, 15 April 2009 23:49 |
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 #398473 is a reply to message #398314] |
Thu, 16 April 2009 06:36 |
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 |
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.
|
|
|
|
Goto Forum:
Current Time: Wed Jun 26 11:39:53 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
