This forum is currently locked. You can't register or post questions at this time. (read more)

query optimisation

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

amathew
Senior Member
Posts: 217
Joined: Thu Dec 02, 2004 5:50 am

query optimisation

Postby amathew » Thu Jun 05, 2014 8:42 am

hi,

when i execute the below query it takes a long time. anyway we can optimise it? it runs as longops.
Database version is 11.2.0.4

Code: Select all

SELECT *  FROM
(SELECT substr(key_1, 4, 16) acct_no, customer_no, curr_bal, acct_type,
                 currency, branch_no, customer_type, program_code, (SELECT descript
                      FROM depp
                      WHERE type = acct_type
                        AND invm.int_cat = depp.int_cat) acct_desc, (SELECT cust_type_desc
                      FROM ctyp
                      WHERE customer_type = cust_type_code) cust_desc, hold_val
              FROM invm, cusm
              WHERE rownum <= 50
                AND invm.customer_no = cusm.cust_acct_no
                AND curr_status = '00'
                AND hold_val = '0'
                AND curr_bal > '100'
              ORDER BY dbms_random.value)
    WHERE rownum <= '5'
AM

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: query optimisation

Postby Tim... » Thu Jun 05, 2014 9:06 am

Hi.

It makes life a lot easier if it is formatted consistently... :)

Code: Select all

SELECT *
FROM   (SELECT substr(key_1, 4, 16) acct_no,
               customer_no,
               curr_bal,
               acct_type,
               currency,
               branch_no,
               customer_type,
               program_code,
               (SELECT descript FROM depp WHERE type = acct_type AND invm.int_cat = depp.int_cat) acct_desc,
               (SELECT cust_type_desc FROM ctyp WHERE customer_type = cust_type_code) cust_desc,
               hold_val
        FROM invm, cusm
        WHERE rownum <= 50
        AND invm.customer_no = cusm.cust_acct_no
        AND curr_status = '00'
        AND hold_val = '0'
        AND curr_bal > '100'
        ORDER BY dbms_random.value)
WHERE rownum <= '5'


To this is a top-N query. Let's ignore the outer part of the query as the performance issues will not really be affected by that. SO what we care about is this.

Code: Select all

SELECT substr(key_1, 4, 16) acct_no,
       customer_no,
       curr_bal,
       acct_type,
       currency,
       branch_no,
       customer_type,
       program_code,
       (SELECT descript FROM depp WHERE type = acct_type AND invm.int_cat = depp.int_cat) acct_desc,
       (SELECT cust_type_desc FROM ctyp WHERE customer_type = cust_type_code) cust_desc,
       hold_val
FROM invm, cusm
WHERE rownum <= 50
AND invm.customer_no = cusm.cust_acct_no
AND curr_status = '00'
AND hold_val = '0'
AND curr_bal > '100'
ORDER BY dbms_random.value


Things that seem odd include:

- Order by DBMS_RANDOM.value. So you have a random order. Why?
- You have "rownum <= 50" inside something that will ultimately be used in a top-n query. I don't see how this makes sense. You are asking for the first 50 records that match the other criteria, regardless of ordering, then with those 50 records, which could be any, you are ordering by something random. Then you are asking for the top 5. I fail to see how this can be remotely useful to anyone. :)
- You have sub-selects to get the acct_desc and cust_desc columns. Why are these not just normal joins?

If I were trying to performance tun this query, I would probably check the following:

- Ignoring the silly looking "rownum <= 50" check, how many records to you expect to get back from this statement?
- Is that figure a small percentage of the total data in the tables?
- If so, is the execution plan using indexes to get the data? If you are retrieving a small proportion of the total data, then indexes are usually a good idea.
- If not, is the execution plan using full table scans. If you are expecting to touch most of the rows in a table, full table scans are usually a good idea.

This should be fairly standard sql tuning, once you determine why the statement includes rather odd stuff.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

amathew
Senior Member
Posts: 217
Joined: Thu Dec 02, 2004 5:50 am

Re: query optimisation

Postby amathew » Thu Jun 05, 2014 9:20 am

hi Tim,

You have sub-selects to get the acct_desc and cust_desc columns. Why are these not just normal joins?


can this be rewritten to use normal joins? that would be very helpful.

thanks again,
AM
AM

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: query optimisation

Postby Tim... » Thu Jun 05, 2014 9:28 am

Yes. They would probably be something like this.

Code: Select all

SELECT substr(key_1, 4, 16) acct_no,
       customer_no,
       curr_bal,
       acct_type,
       currency,
       branch_no,
       customer_type,
       program_code,
       descript AS acct_desc,
       cust_type_desc AS cust_desc,
       hold_val
FROM invm, cusm, depp, ctyp
WHERE rownum <= 50
AND invm.customer_no = cusm.cust_acct_no
AND depp.type = acct_type AND invm.int_cat = depp.int_cat
AND customer_type = ctyp.cust_type_code
AND curr_status = '00'
AND hold_val = '0'
AND curr_bal > '100'
ORDER BY dbms_random.value


Note. It is hard to tell which columns come from which tables, so I may have goofed up here. It would make sense if you do one of two things:

1) Prefix all column names with the table name, so you know where they are coming from.
2) User an alias for the table name in the FROM clause, then prefix all columns names with the table alias.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

amathew
Senior Member
Posts: 217
Joined: Thu Dec 02, 2004 5:50 am

Re: query optimisation

Postby amathew » Thu Jun 05, 2014 9:43 am

Thank you very much Tim. I will do as you said.

thanks again,
AM
AM

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: query optimisation

Postby Tim... » Thu Jun 05, 2014 9:57 am

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 6 guests

cron