Solved

Performance tuning in oracle for a query

Posted on 2014-12-09
4
169 Views
Last Modified: 2015-02-20
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;
0
Comment
Question by:welcome 123
4 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40489680
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
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40489764
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
 
LVL 73

Accepted Solution

by:
sdstuber earned 450 total points
ID: 40489941
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
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 50 total points
ID: 40490206
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now