Oracle 8 Performance tuning question

[myworldntl]

Hi there, i have been given some SQL at work that is taking forever to run client side.

The first version of the SQL statment is the block i was given which i have already altered and performs better and returns the correct data still, just is still too slow.
The second block is the SQL i've updated for performance reasons, i got the explain plan from it thinking it would be better, but in fact the explain plan for this SQL is even worse... Also, there is one line that is different between the 2 scripts which i am unsure i have corrected for the better, this is/will be annotated however..

Thanks in advance.

ORIGINAL SQL:




The SQL below was given to me and i have altered and it still works and quicker, but still is too slow...

The SQL below that i have altered to run faster but i think at least one line is incorrect and and also, the explain plan says that it costs more...

Unfortunately the customer at work i am doing this for is running Oracle 8....

--------------------------------------------------
---------------------OLD SQL---------------------
--------------------------------------------------

SELECT SYSDATE AS DATE_ROW_CREATED,
con.con_pos_ref_no AS POSITION_REFERENCE,
job.party_nm AS JOB_TITLE,
con.party_lnk_d AS START_DATE,
con.party_lnk_ed AS END_DATE,
spt.scale_pt_refcd AS SCALE_POINT,
phs.party_hrs AS HOURS,
mpy.con_pos_ref_no AS LINE_MANAGER,
pay.payroll_nm AS PAYROLL_NAME,
NVL(GREATEST(con.last_mod_d, pip.last_mod_d, man.last_mod_d, phs.last_mod_d, pgd.last_mod_d),SYSDATE) AS LAST_UPDATED

FROM

trentadm.tparty_lnk con,
trentadm.tparty_in_pay pip,
trentadm.tpayroll pay,
trentadm.tparty job,
trentadm.tpty_det_own pdo,
trentadm.tparty_hrs phs,
trentadm.tparty_grade pgd,
trentadm.tscale_pt spt,
trentadm.tparty_lnk man,
trentadm.tparty_lnk mpy

WHERE 1=1
AND job.party_id = con.party_id2
AND con.party_id2 = man.party_id(+)
AND con.party_id2 = pdo.party_id(+)
AND man.party_id2 = mpy.party_id2(+)
AND pip.party_id = con.party_id
AND pay.payroll_id = pip.payroll_id
AND pdo.owner_id = phs.party_id(+)
AND pgd.scale_pt_act = spt.scale_pt_id(+)




AND pip.party_in_pay_d < SYSDATE
AND (pip.party_in_pay_ed >= SYSDATE OR pip.party_in_pay_ed IS NULL)




AND pdo.detail_group(+) = 'CONT_HRS'
AND pdo.detail_type(+) = 'CONT_HRS'
AND pdo.pty_det_own_d(+) < SYSDATE
AND 'Y' = DECODE(LEAST(pdo.pty_det_own_ed(+),SYSDATE), NULL, 'Y', SYSDATE, 'Y', 'N')


AND phs.hrs_type_id(+) = 'CONT_HRS'
AND phs.party_hrs_d(+) < SYSDATE
AND 'Y' = DECODE(LEAST(phs.party_hrs_ed(+),SYSDATE), NULL, 'Y', SYSDATE, 'Y', 'N')


AND pgd.party_grade_d(+) < SYSDATE
AND 'Y' = DECODE(LEAST(pgd.party_grade_ed(+),SYSDATE), NULL, 'Y', SYSDATE, 'Y', 'N')


AND man.link_type_id(+) = 'POSN_POSN'
AND man.party_lnk_d(+) < SYSDATE
AND 'Y' = DECODE(LEAST(man.party_lnk_ed(+),SYSDATE), NULL, 'Y', SYSDATE, 'Y', 'N')


AND mpy.link_type_id(+) = 'CONT'
AND mpy.party_lnk_d(+) < SYSDATE
AND 'Y' = DECODE(LEAST(mpy.party_lnk_ed(+),SYSDATE), NULL, 'Y', SYSDATE, 'Y', 'N')

AND 'Y' = DECODE(pgd.party_id(+), con.party_id2, 'Y', 'N')
--LINE ABOVE IS THE 1 I THINK I GOT WRONG IN THE NEW SQL

AND con.PARTY_LNK_D <= SYSDATE
AND (con.PARTY_LNK_ED >= SYSDATE OR con.PARTY_LNK_ED IS NULL)

AND job.party_d < SYSDATE
AND (job.party_ed >= SYSDATE OR job.party_ed IS NULL)

AND EXISTS
(
SELECT position_id FROM ZTSDPOSITIONID
WHERE
position_id = job.party_id
)


--------------------------------------------------
---------------------NEW SQL---------------------
--------------------------------------------------

SELECT SYSDATE AS DATE_ROW_CREATED,
con.con_pos_ref_no AS POSITION_REFERENCE,
job1.party_nm AS JOB_TITLE,
con.party_lnk_d AS START_DATE,
con.party_lnk_ed AS END_DATE,
spt.scale_pt_refcd AS SCALE_POINT,
phs.party_hrs AS HOURS,
mpy.con_pos_ref_no AS LINE_MANAGER,
pay.payroll_nm AS PAYROLL_NAME,
NVL(GREATEST(con.last_mod_d, pip.last_mod_d, man.last_mod_d, phs.last_mod_d, pgd.last_mod_d),SYSDATE) AS LAST_UPDATED

FROM trentadm.tparty job1

INNER JOIN
(
SELECT party_id, party_id2, con_pos_ref_no, party_lnk_d, party_lnk_ed, last_mod_d FROM trentadm.tparty_lnk
WHERE PARTY_LNK_D <= SYSDATE
AND (PARTY_LNK_ED >= SYSDATE OR PARTY_LNK_ED IS NULL)
) con ON job1.party_id = con.party_id2

INNER JOIN
(
SELECT party_id, payroll_id, last_mod_d FROM trentadm.tparty_in_pay
WHERE party_in_pay_d < SYSDATE
AND (party_in_pay_ed >= SYSDATE OR party_in_pay_ed IS NULL)
) pip ON pip.party_id = con.party_id

LEFT OUTER JOIN
(
SELECT party_id, party_id2, last_mod_d FROM trentadm.tparty_lnk
WHERE party_lnk_d(+) < SYSDATE
AND 'Y' = DECODE(LEAST(party_lnk_ed(+),SYSDATE), NULL, 'Y', SYSDATE, 'Y', 'N')
AND link_type_id(+) = 'POSN_POSN'
) man ON con.party_id2 = man.party_id --(+)

LEFT OUTER JOIN
(
SELECT party_id, last_mod_d, scale_pt_act FROM trentadm.tparty_grade
WHERE party_grade_d(+) < SYSDATE
AND 'Y' = DECODE(LEAST(party_grade_ed(+),SYSDATE), NULL, 'Y', SYSDATE, 'Y', 'N')
) pgd ON 'Y' = DECODE(pgd.party_id, con.party_id2, 'Y', 'N')
--THIS 'ON' is DERIVED FROM THE LINE I ANNOTATED IN THE ORIGINAL SQL

LEFT OUTER JOIN
(
SELECT party_id, owner_id FROM trentadm.tpty_det_own
WHERE pty_det_own_d(+) < SYSDATE
AND 'Y' = DECODE(LEAST(pty_det_own_ed(+),SYSDATE), NULL, 'Y', SYSDATE, 'Y', 'N')
AND detail_group(+) = 'CONT_HRS'
AND detail_type(+) = 'CONT_HRS'
) pdo ON con.party_id2 = pdo.party_id --(+)


LEFT OUTER JOIN
(
SELECT party_id, party_hrs, last_mod_d FROM trentadm.tparty_hrs
WHERE party_hrs_d(+) < SYSDATE
AND 'Y' = DECODE(LEAST(party_hrs_ed(+),SYSDATE), NULL, 'Y', SYSDATE, 'Y', 'N')
AND hrs_type_id(+) = 'CONT_HRS'
) phs ON pdo.owner_id = phs.party_id --(+)

INNER JOIN trentadm.tpayroll pay
ON pay.payroll_id = pip.payroll_id

LEFT OUTER JOIN
(
SELECT party_id2, con_pos_ref_no FROM trentadm.tparty_lnk
WHERE party_lnk_d(+) < SYSDATE
AND 'Y' = DECODE(LEAST(party_lnk_ed(+),SYSDATE), NULL, 'Y', SYSDATE, 'Y', 'N')
AND link_type_id(+) = 'CONT'
) mpy ON man.party_id2 = mpy.party_id2 --(+)


LEFT OUTER JOIN trentadm.tscale_pt spt ON pgd.scale_pt_act = spt.scale_pt_id --(+)

WHERE 1=1

AND (job1.party_ed >= SYSDATE OR job1.party_ed IS NULL)
AND job1.party_d < SYSDATE
AND EXISTS
(
SELECT position_id FROM ZTSDPOSITIONID
WHERE
position_id = job1.party_id
)

 

[SantaMufasa]

MyWorld,

First of all, how long is the code taking presently to run, and how long to you believe it should take to run?

Have you confirmed that all of the column expressions that are operands in an equijoin ('=') in your WHERE clauses are expressions in indexes?

What have you tried so far to improve performance?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[myworldntl]

Problem is i guess that we only have oracle 9i and 10g at work and the customer runs 8i, so there are optimizer differences...

However, (the first block of SQL) runs in 15 minutes on 9i, but can take up to an hour and a half or more on the customer site...

So i would have thought that with tuning, it should run on their site in at least 15 minutes, and possibly faster if tuned correctly..

So far, what i have done is removed 2 un-necissary tables and joins thus removing 2 full table scans... Because they were litterally doing them for no reason as they were not brining back any data that was being used, anywhere...

Besides that, in the 2'd SQL i have tried to use derived tables to help speed it up as i've done this before and its worked to great effect... However, the explain plan pretty much told me i'd made things worse... So i must have got something wrong...

The sql itself is basically the same in both statements, just arranged differently and uses derived tables in the 2'd version..


at a bit of a loose end to be honest.. Just graduated from uni and been in the job a month, and im seemingly the only one there that knows anything useful about databases, so not getting much help from colleagues.

 

[myworldntl]

Not too sure about this bit though:

" Have you confirmed that all of the column expressions that are operands in an equijoin ('=') in your WHERE clauses are expressions in indexes? "

Dont follow/understand this bit?

Thanks

 

[ddiamond]

I think the first thing you should attempt to do is get rid of the table scans. This can be done by adding indexes on the appropriate fields. In oracle 8, the optimizer will sometimes choose not to do an index join even if the the correct indexes are present. This can be fixed by introducing the hint /*+ rule */ directly after the select key word. When you look at the sql plan on the old version of the query, which tables are being scanned? Once I know which tables are being scanned, I can tell which fields may need to be indexed.

p.s. I do not know if the hint /*+ rule */ works on oracle 9i and 10g. I am only familiar with oracle 8.