[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Performance tuning in oracle for a query

Posted on 2014-12-09
4
Medium Priority
?
179 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 14

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 1800 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 200 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to take different types of Oracle backups using RMAN.

649 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