welcome 123
asked on
Performance tuning in oracle for a query
The below query should be reduced to 1 sec for each hgroup records and any help greatly appreciated:
Each iteration is taking 10 seconds
There are 20000 hgroups it is taking 200000 secs.
open cursor_temp;
for cursor_var IN cursor_temp
loop
phgroup := cursor_temp.hgroup;
SELECT DISTINCT
ur.hgroup, UPPER(u.empid) empid, h.job_role, u.unix_username, u.type,
ur.username, ur.route, 'USER' route_type,
ur.su, ur.suexec, ur.target_account, ur.source,
ur.method, ur.command
FROM
hier_job h,
userroutes_bkp ur,
users_bkp u
WHERE h.empid = u.empid
AND u.keon_username = ur.username
AND u.hostgroup = phgroup
UNION
SELECT DISTINCT
cr.hgroup, UPPER(u.empid) empid, h.job_role, u.unix_username, u.type,
cm.username, cr.route, 'CLASS' route_type,
cr.su, cr.suexec, cr.target_account, cr.source,
cr.method, cr.command
FROM
hier_job h,
classroutes_bkp cr,
class_members_bkp cm,
users_bkp u
WHERE h.sid = u.sid
AND u.keon_username = cm.username
AND cm.userclass = cr.userclass
AND cr.hostgroup = phgroup
then <insert those rows in table 2>
end loop;
Each iteration is taking 10 seconds
There are 20000 hgroups it is taking 200000 secs.
open cursor_temp;
for cursor_var IN cursor_temp
loop
phgroup := cursor_temp.hgroup;
SELECT DISTINCT
ur.hgroup, UPPER(u.empid) empid, h.job_role, u.unix_username, u.type,
ur.username, ur.route, 'USER' route_type,
ur.su, ur.suexec, ur.target_account, ur.source,
ur.method, ur.command
FROM
hier_job h,
userroutes_bkp ur,
users_bkp u
WHERE h.empid = u.empid
AND u.keon_username = ur.username
AND u.hostgroup = phgroup
UNION
SELECT DISTINCT
cr.hgroup, UPPER(u.empid) empid, h.job_role, u.unix_username, u.type,
cm.username, cr.route, 'CLASS' route_type,
cr.su, cr.suexec, cr.target_account, cr.source,
cr.method, cr.command
FROM
hier_job h,
classroutes_bkp cr,
class_members_bkp cm,
users_bkp u
WHERE h.sid = u.sid
AND u.keon_username = cm.username
AND cm.userclass = cr.userclass
AND cr.hostgroup = phgroup
then <insert those rows in table 2>
end loop;
100% agreeing with slightwv, plus: despite the fact, that Oracle does many things internally, you also should consider the use of BULK COLLECT and FORALL processing and I suggest trying to put as much as possible within just one SQL statement (like MERGE ... USING ...).
But without proper index useage and execution plans, you won't be able to improve anything @all
But without proper index useage and execution plans, you won't be able to improve anything @all
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
A 10 times increase in performance may be impossible without buying new hardware. If the system isn't configured properly you may be able to get some performance increase but 10 times is asking a lot.
All that said:
Take the individual queries involved and generate explain plans for them. See if any new indexes will help.