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;
welcome 123Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
People make entire careers out of nothing but tuning.  These types of questions are really hard to answer on a Q&A site like this.

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.
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
0
sdstuberCommented:
Your UNION should be UNION ALL, because the sub-queries are guaranteed to be mutually distinct.

The bulk operations might look something like this...

DECLARE
    TYPE phgroup_tab IS TABLE OF users_bkp.hostgroup%TYPE
        INDEX BY BINARY_INTEGER;

    phgroups phgroup_tab;
BEGIN
    OPEN cursor_temp;

    FETCH cursor_temp BULK COLLECT INTO phgroups;

    FORALL i IN 1 .. phgroups.COUNT
        -- Change these column and table names to your real columns and table
        INSERT INTO table2(
                        col1,
                        col2,
                        col3,
                        col4,
                        col5,
                        col6,
                        col7,
                        col8,
                        col9,
                        col10,
                        col11,
                        col12,
                        col13,
                        col14
                    )
            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 = phgroups(i)
            UNION ALL
            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 = phgroups(i);
END;

Open in new window



As for the selects themselves, you'll have to provide a lot more detail, like structure, indexes, approximate cardinalities, plans, etc.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
just wanted to point out that using "SELECT DISTINCT" and "UNION" together is redundant, "UNION" by itself will produce a distinct list.

But: because the upper and lower queries cannot produce rows that are the same (due to the constants 'USER' and 'CLASS')  then as shown by sdstuber you should use UNION ALL.

plus you gain the benefit that  UNION ALL is LESS EFFORT than UNION.

"SELECT DISTINCT" is the enemy of speed. Are you absolutely certain you need to use "DISTINCT"?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.