Solved

Performance tuning in oracle for a query

Posted on 2014-12-09
4
170 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL query of Oracle 10g database. 8 75
oracle report printing 2 pages in one page 2 56
Oracle -- identify blocking session 24 41
Oracle - SQL Parse String 5 15
Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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 shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

929 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

13 Experts available now in Live!

Get 1:1 Help Now