Solved

Performance tuning in oracle for a query

Posted on 2014-12-09
4
177 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 77

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 74

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 49

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

707 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