Home » RDBMS Server » Performance Tuning » Performance Tuining(URGENT)
| Performance Tuining(URGENT) [message #173694] |
Wed, 24 May 2006 01:38  |
sethybibeka
Messages: 10
Registered: August 2005
Location: bhubanes
|
Junior Member |
|
|
Hi,
This query is taking 23 sec to execute please tell me how to reduce the time:
CREATE OR REPLACE FORCE VIEW V_AGENCYRESOURCETITLES_HIST
(PLANID, PLANSUBMIT_DATE, ID_PK, AVERAGEHOURLYRATE, EFFECTIVE_DATE,
AGENCYID_FK, FUNCTIONGROUPID_FK, LOOKUP_PM_RATE_HISTORY, LOOKUP_RATE_HISTORY, PARENTTITLE,
RATEHISTORYID_FK, TITLE, YEAR)
AS
WITH a AS (SELECT /*+ FIRST_ROWS */ DISTINCT
V_ANNUALPLANS_CURRENT_AND_LE.ID_PK PLANID,
V_ANNUALPLANS_CURRENT_AND_LE.PLANSUBMIT_DATE,
v_agencyresourcetitles.ID_PK,
v_agencyresourcetitles.AVERAGEHOURLYRATE,
v_agencyresourcetitles.EFFECTIVE_DATE,
--to_char(v_agencyresourcetitles.EFFECTIVE_DATE, 'mm') mth,
v_agencyresourcetitles.AGENCYID_FK,
v_agencyresourcetitles.FUNCTIONGROUPID_FK,
v_agencyresourcetitles.LOOKUP_PM_RATE_HISTORY,
v_agencyresourcetitles.LOOKUP_RATE_HISTORY,
v_agencyresourcetitles.PARENTTITLE,
v_agencyresourcetitles.RATEHISTORYID_FK,
v_agencyresourcetitles.TITLE,
v_agencyresourcetitles.YEAR
FROM
v_agencyresources_history,
v_agencyresourcetitles,
V_ANNUALPLANS_CURRENT_AND_LE,
BRANDTEAMS,
PROJECTS,
PROJECTHOURS,
POA,
AGENCIES
WHERE
( AGENCIES.ID_PK=BRANDTEAMS.AGENCYID_FK )
AND ( BRANDTEAMS.ID_PK=V_ANNUALPLANS_CURRENT_AND_LE.BRANDTEAMID_FK )
AND ( V_ANNUALPLANS_CURRENT_AND_LE.ID_PK=PROJECTS.ANNUALPLANID_FK )
AND ( PROJECTS.POAID_FK=POA.ID_PK )
AND PROJECTS.ID_PK=PROJECTHOURS.PROJECTID_FK(+)
AND ( PROJECTHOURS.AGENCYRESOURCEID_FK=v_agencyresources_history.ID_PK(+) )
AND v_agencyresources_history.TITLEID = v_agencyresourcetitles.ID_PK
AND v_agencyresourcetitles.EFFECTIVE_DATE < NVL(V_ANNUALPLANS_CURRENT_AND_LE.PLANSUBMIT_DATE,'01-JAN-2100')
ORDER BY V_ANNUALPLANS_CURRENT_AND_LE.ID_PK ,v_agencyresourcetitles.ID_PK)
SELECT /*+ FIRST_ROWS */
a.PLANID,
a.PLANSUBMIT_DATE,
a.ID_PK,
a.AVERAGEHOURLYRATE,
a.EFFECTIVE_DATE,
A.AGENCYID_FK,
A.FUNCTIONGROUPID_FK,
A.LOOKUP_PM_RATE_HISTORY,
A.LOOKUP_RATE_HISTORY,
A.PARENTTITLE,
A.RATEHISTORYID_FK,
A.TITLE,
A.YEAR
FROM
a,
(SELECT a.planid,a.ID_PK,MAX(a.EFFECTIVE_DATE) EFFECTIVE_DATE
FROM
a
GROUP BY a.planid,a.ID_PK) b
WHERE
a.planid = b.planid
AND a.ID_PK = b.ID_PK
AND a.EFFECTIVE_DATE = b.EFFECTIVE_DATE
ORDER BY a.planid,a.id_pk;
|
|
|
|
|
|
| Re: Performance Tuining(URGENT) [message #173781 is a reply to message #173694] |
Wed, 24 May 2006 07:16  |
Art Metzer
Messages: 2480
Registered: December 2002
|
Senior Member |
|
|
Have you tried the view without the hint? How's the performance then?
Do you really need the ORDER BY in the view? Shouldn't the ORDER BY be applied when you SELECT from the view, so different calls can order the data as each call sees fit?
I get mildly nervous when I see DISTINCT. Is it absolutely necessary? Its presence implies that without it, this view would return duplicate rows, and DISTINCT will certainly fix that. The down side to DISTINCT is that your query might be doing a ton of (unnecessary) work: DISTINCTing that ton of unnecessary work may produce "the right answer", but perhaps at the price of performance, and unbeknownst to you. It would be preferrable for you to analyze this data model properly, unearth why DISTINCT's absence produces duplicate rows, and winnow that result set down to non-duplicated rows using predicates in the WHERE clause instead of plopping a DISTINCT in the SLECT clause. That scenario might not apply here, but I've seen it all too often.
It seems to me like your outer joins would do nothing? You outer-join from projects to projecthours and again from projecthours to v_agencyresources_history, but then the follow-up join from v_agencyresources_history to v_agencyresourcetitles has no outer join, thereby invalidating your outer joins up to that point. For more information about that, please read this.
That all said, does this version of the view produce the same result set? Does it perform any better?CREATE OR REPLACE FORCE VIEW v_agencyresourcetitles_hist (
planid
, plansubmit_date
, id_pk
, averagehourlyrate
, effective_date
, agencyid_fk
, functiongroupid_fk
, lookup_pm_rate_history
, lookup_rate_history
, parenttitle
, ratehistoryid_fk
, title
, year
)
AS
SELECT /*+ FIRST_ROWS */
DISTINCT a.planid
, a.plansubmit_date
, a.id_pk
, a.averagehourlyrate
, a.effective_date
, a.agencyid_fk
, a.functiongroupid_fk
, a.lookup_pm_rate_history
, a.lookup_rate_history
, a.parenttitle
, a.ratehistoryid_fk
, a.title
, a.year
FROM (SELECT pln.id_pk planid
, pln.plansubmit_date
, rt.id_pk
, rt.averagehourlyrate
, rt.effective_date
, MAX(rt.effective_date)
OVER (PARTITION BY pln.id_pk
, rt.id_pk) max_eff_dt
, rt.agencyid_fk
, rt.functiongroupid_fk
, rt.lookup_pm_rate_history
, rt.lookup_rate_history
, rt.parenttitle
, rt.ratehistoryid_fk
, rt.title
, rt.year
FROM v_agencyresources_history hist
, v_agencyresourcetitles rt
, v_annualplans_current_and_le pln
, brandteams bt
, projects p
, projecthours hr
, poa poa
, agencies a
WHERE a.id_pk = bt.agencyid_fk
AND bt.id_pk = pln.brandteamid_fk
AND pln.id_pk = p.annualplanid_fk
AND p.poaid_fk = poa.id_pk
AND p.id_pk = hr.projectid_fk
AND hr.agencyresourceid_fk = hist.id_pk
AND hist.titleid = rt.id_pk
AND rt.effective_date < NVL(pln.plansubmit_date
, TO_DATE('01-JAN-2100'
, 'DD-MON-YYYY'))) a
WHERE a.effective_date = a.max_eff_dt
/ |
|
|
|
|
|
|
|
| Re: Performance Tuining(URGENT) [message #174010 is a reply to message #174000] |
Thu, 25 May 2006 09:10 |
sethybibeka
Messages: 10
Registered: August 2005
Location: bhubanes
|
Junior Member |
|
|
I have tried it without using the the hint also.
but the with clause is working best what i have used in my query
Thanks for ur reply .
if u will help me agin then greatfull to u
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 13:48:14 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
