This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Feb 18, 2013 7:35 AM by 985871 RSS

Need help to Tune a Query

985871 Newbie
Currently Being Moderated
Hi ,

I need help on how to tune this query which is taking approx 32s to complete.

SELECT /*+ FIRST_ROWS */ A.ACCOUNT_ID , A.DISPLAY_NAME AS NAME FROM ACCOUNTS A WHERE
A.ACCOUNT_ID IN (select a.parent_id from accounts a ,ACCOUNT_TYPES AT
where A.ACCOUNT_TYPE_ID=AT.ACCOUNT_TYPE_ID AND AT.ACCOUNT_LEVEL = 2 and a.ACCOUNT_ID not IN
( (SELECT UA.ACCOUNT_ID FROM USER_ACCOUNTS UA WHERE UA.USER_ID=151360 union all
SELECT U1.ACCOUNT_ID FROM USERS U1 WHERE U1.USER_ID=151360 ))) and A.ACTIVE != 1 order by upper(name);


-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 595 | 415K|
| 1 | SORT ORDER BY | | 17 | 595 | 415K|
| 2 | NESTED LOOPS | | 17 | 595 | 415K|
| 3 | VIEW | VW_NSO_1 | 62221 | 121K| 414K|
| 4 | HASH UNIQUE | | 197 | 972K| |
| 5 | FILTER | | | | |
| 6 | TABLE ACCESS BY INDEX ROWID | ACCOUNTS | 20741 | 222K| 450 |
| 7 | NESTED LOOPS | | 62222 | 972K| 935 |
| 8 | TABLE ACCESS FULL | ACCOUNT_TYPES | 3 | 15 | 3 |
| 9 | INDEX RANGE SCAN | IND_ACCOUNTS_ACCOUNT_TYPE_ID | 20741 | | 32 |
| 10 | UNION-ALL | | | | |
| 11 | INDEX RANGE SCAN | USERS_ACCOUNTS_PK | 1 | 10 | 6 |
| 12 | TABLE ACCESS BY INDEX ROWID| USERS | 1 | 10 | 2 |
| 13 | INDEX UNIQUE SCAN | USERS_PK | 1 | | 1 |
| 14 | TABLE ACCESS BY INDEX ROWID | ACCOUNTS | 1 | 33 | 1 |
| 15 | INDEX UNIQUE SCAN | ACCOUNTS_PK | 1 | | 0 |
--------------------------------------------------------------------------------------------------
  • 1. Re: Need help to Tune a Query
    moreajays Pro
    Currently Being Moderated
    Hi,

    Share complete explain plan , need to know the column used in below operations(6th)
    below part causing slowness

    6      TABLE ACCESS BY INDEX ROWID      ACCOUNTS      20741      222K      450
    7      NESTED LOOPS           62222      972K      935
    8      TABLE ACCESS FULL      ACCOUNT_TYPES      3      15      3

    Thanks,
    Ajay More
    http://www.moreajays.com
  • 2. Re: Need help to Tune a Query
    985871 Newbie
    Currently Being Moderated
    Ajay ,

    That is the only output i got from the explain plan .

    6 | TABLE ACCESS BY INDEX ROWID | ACCOUNTS | 20741 | 222K| 450 |
    | 7 | NESTED LOOPS | | 62222 | 972K| 935 |
    | 8 | TABLE ACCESS FULL | ACCOUNT_TYPES | 3 | 15 | 3 |


    I have used the command

    explain plan for
    <sql statement>

    and then

    select * from table(dbms_xplan.display);
  • 3. Re: Need help to Tune a Query
    moreajays Pro
    Currently Being Moderated
    Hi,

    From , sqlplus do this
    -- recreate plan table
    SQL> drop table plan_table;
    
    Table dropped.
    
    SQL> @?/rdbms/admin/utlxplan.sql
    
    Table created.
    
    -- get explain plan 
    explain plan for 
    <sql_query>;
    @?/rdbms/admin/utlxpls.sql
    Thanks,
    Ajay More
    http://www.moreajays.com
  • 4. Re: Need help to Tune a Query
    985871 Newbie
    Currently Being Moderated
    Ajay ,

    it says insufficient privilages to run that script
  • 5. Re: Need help to Tune a Query
    moreajays Pro
    Currently Being Moderated
    Hi,

    You need to executed as sys from db local server


    Thanks,
    Ajay More
    http://www.moreajays.com
  • 6. Re: Need help to Tune a Query
    Zoltan Kecskemethy Expert
    Currently Being Moderated
    See [url https://forums.oracle.com/forums/thread.jspa?messageID=9360003&tstart=0#9360003]3. How to improve the performance of my query? / My query is running slow. from {forum:id=75} FAQ.
  • 7. Re: Need help to Tune a Query
    Mohamed Houri Pro
    Currently Being Moderated
    You can use the following link in order to get an exploitable and interpretable execution plan :

    http://hourim.wordpress.com/2011/12/29/getting-explain-plan

    It is well known that, given adequate and correct volume and scattered data representation, the CBO will produce the best execution plan possible (excluding bugs and situation like yours where you have forced the CBO to run under its uncommon and buggy FIRST_ROWS)
    Start by posting the execution plan pulled from memory together with estimations (E-Rows) and actual (A-Rows) and the always very important predicate part of that execution plan

    Best regards

    Mohamed Houri
    www.hourim.wordpress.com
  • 8. Re: Need help to Tune a Query
    985871 Newbie
    Currently Being Moderated
    Ajay ,

    These are the columns being refered to
                                             
    OPERATION                         OBJECT_NAME   OPTIONS          COST 
      INDEX                    IND_ACCOUNTS_ACCOUNT_TYPE_ID   RANGE SCAN                   32
      {\code}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  • 9. Re: Need help to Tune a Query
    moreajays Pro
    Currently Being Moderated
    Hi,

    I am looking out for Predicate Information (identified by operation id) provided in explain plan against each operation id of plan
    If you can get complete explain plan with predicates then it would be great help
    What is ACCOUNT Table count & its size ?
    When it was last_analyzed ?

    Thanks,
    Ajay More
    http://www.moreajays.com
  • 10. Re: Need help to Tune a Query
    985871 Newbie
    Currently Being Moderated
    Ajay ,

    unforunately we do not have the sys access . May be thats why i am not able to give you the exact format.

    These are the no of records in each table .

    No of Records

    ACCOUNT - 210449
    ACCOUNT_TYPES - 15
    USER_ACCOUNTS - 813083
    USERS - 66316

    SQL> select table_name , last_analyzed from user_tables where table_name in ('ACCOUNTS','ACCOUNT_TYPES','USER_ACCOUNTS','USERS');

    TABLE_NAME LAST_ANAL
    ------------------------------ ---------
    ACCOUNTS 15-FEB-13
    ACCOUNT_TYPES 15-FEB-13
    USERS 15-FEB-13
    USER_ACCOUNTS 29-JAN-13
  • 11. Re: Need help to Tune a Query
    985871 Newbie
    Currently Being Moderated
    SEGMENT_NAME                                                                      BYTES/1024/1024
    --------------------------------------------------------------------------------- ---------------
    ACCOUNTS                                                                                       27
    ACCOUNT_TYPES                                                                               .0625
    USERS                                                                                             7
    USER_ACCOUNTS                                                                                  23
  • 12. Re: Need help to Tune a Query
    moreajays Pro
    Currently Being Moderated
    Hi,

    In absense of predicated , you can try below things

    1. remove hint /*+ FIRST_ROWS */ * check response & explain plan for complete query
    2. Provide below o/p
    select index_name,column_name,column_position from user_ind_columns where table_name='ACCOUNTS';

    3. Get explain plan & response time for sub queries

    select a.parent_id
    from accounts a, ACCOUNT_TYPES AT
    where A.ACCOUNT_TYPE_ID = AT.ACCOUNT_TYPE_ID
    AND AT.ACCOUNT_LEVEL = 2
    and a.ACCOUNT_ID not IN
    ((SELECT UA.ACCOUNT_ID
    FROM USER_ACCOUNTS UA
    WHERE UA.USER_ID = 151360
    union all
    SELECT U1.ACCOUNT_ID FROM USERS U1 WHERE U1.USER_ID = 151360))

    4. Get explain plan & response time for sub queries

    SELECT UA.ACCOUNT_ID
    FROM USER_ACCOUNTS UA
    WHERE UA.USER_ID = 151360
    union all
    SELECT U1.ACCOUNT_ID FROM USERS U1 WHERE U1.USER_ID = 151360

    Thanks,
    Ajay More
    http://www.moreajays.com
  • 13. Re: Need help to Tune a Query
    985871 Newbie
    Currently Being Moderated
    Ajay ,

    After removing the hint also it takes the same time i.e 32 s.

    We expect 207 rows only .
    Below is the output .
    INDEX_NAME               COLUMN_NAME     COLUMN_POSITION
    IND_ACCOUNTS_DISPLAY          SYS_NC00022$     1
    ACCOUNTS_PK               ACCOUNT_ID     1
    IND_ACCOUNTS_ACCOUNT_TYPE_ID     ACCOUNT_TYPE_ID     1
    IND_ACCOUNT_REF               ACCOUNT_REF     1
    first query
    4200 rows selected.

    Elapsed: 00:00:09.23
    -----------------------------------------------------------------------------------
    | Id  | Operation                     | Name              | Rows  | Bytes | Cost  |
    -----------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT              |                   | 45096 |   616K|   258K|
    |   1 |  FILTER                       |                   |       |       |       |
    |   2 |   HASH JOIN                   |                   | 45096 |   616K|   780 |
    |   3 |    TABLE ACCESS FULL          | ACCOUNT_TYPES     |     3 |    15 |     3 |
    |   4 |    TABLE ACCESS FULL          | ACCOUNTS          |   210K|  1849K|   772 |
    |   5 |   UNION-ALL                   |                   |       |       |       |
    |   6 |    INDEX RANGE SCAN           | USERS_ACCOUNTS_PK |     1 |    10 |     6 |
    |   7 |    TABLE ACCESS BY INDEX ROWID| USERS             |     1 |     8 |     2 |
    |   8 |     INDEX UNIQUE SCAN         | USERS_PK          |     1 |       |     1 |
    -----------------------------------------------------------------------------------
    second query - 1761 rows selected.

    Elapsed: 00:00:00.18
    ----------------------------------------------------------------------------------
    | Id  | Operation                    | Name              | Rows  | Bytes | Cost  |
    ----------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |                   |   921 |  9208 |     8 |
    |   1 |  UNION-ALL                   |                   |       |       |       |
    |   2 |   INDEX RANGE SCAN           | USERS_ACCOUNTS_PK |   920 |  9200 |     6 |
    |   3 |   TABLE ACCESS BY INDEX ROWID| USERS             |     1 |     8 |     2 |
    |   4 |    INDEX UNIQUE SCAN         | USERS_PK          |     1 |       |     1 |
    ----------------------------------------------------------------------------------
  • 14. Re: Need help to Tune a Query
    riedelme Expert
    Currently Being Moderated
    982868 wrote:
    Hi ,

    I need help on how to tune this query which is taking approx 32s to complete.
    SELECT
      /*+ FIRST_ROWS */
      A.ACCOUNT_ID ,
      A.DISPLAY_NAME AS NAME
    FROM ACCOUNTS A
    WHERE A.ACCOUNT_ID IN
      (SELECT a.parent_id
      FROM accounts a ,
        ACCOUNT_TYPES AT
      WHERE A.ACCOUNT_TYPE_ID=AT.ACCOUNT_TYPE_ID
      AND AT.ACCOUNT_LEVEL   = 2
      AND a.ACCOUNT_ID NOT  IN (
        (SELECT UA.ACCOUNT_ID FROM USER_ACCOUNTS UA WHERE UA.USER_ID=151360
        UNION ALL
        SELECT U1.ACCOUNT_ID FROM USERS U1 WHERE U1.USER_ID=151360
       ))
     )
    AND A.ACTIVE != 1
    ORDER BY upper(name);
     
     ------------------------------------------------------------------------------------------------
     | Id  | Operation                         | Name                         | Rows  | Bytes | Cost  |
     --------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                  |                              |    17 |   595 |   415K|
     |   1 |  SORT ORDER BY                    |                              |    17 |   595 |   415K|
     |   2 |   NESTED LOOPS                    |                              |    17 |   595 |   415K|
     |   3 |    VIEW                           | VW_NSO_1                     | 62221 |   121K|   414K|
     |   4 |     HASH UNIQUE                   |                              |   197 |   972K|       |
     |   5 |      FILTER                       |                              |       |       |       |
     |   6 |       TABLE ACCESS BY INDEX ROWID | ACCOUNTS                     | 20741 |   222K|   450 |
     |   7 |        NESTED LOOPS               |                              | 62222 |   972K|   935 |
     |   8 |         TABLE ACCESS FULL         | ACCOUNT_TYPES                |     3 |    15 |     3 |
     |   9 |         INDEX RANGE SCAN          | IND_ACCOUNTS_ACCOUNT_TYPE_ID | 20741 |       |    32 |
     |  10 |       UNION-ALL                   |                              |       |       |       |
     |  11 |        INDEX RANGE SCAN           | USERS_ACCOUNTS_PK            |     1 |    10 |     6 |
     |  12 |        TABLE ACCESS BY INDEX ROWID| USERS                        |     1 |    10 |     2 |
     |  13 |         INDEX UNIQUE SCAN         | USERS_PK                     |     1 |       |     1 |
     |  14 |    TABLE ACCESS BY INDEX ROWID    | ACCOUNTS                     |     1 |    33 |     1 |
     |  15 |     INDEX UNIQUE SCAN             | ACCOUNTS_PK                  |     1 |       |     0 |
    --------------------------------------------------------------------------------------------------
    SQL performance can't always be improved but we can try. I reformatted your SQL with SQL*Developer so it would be easier to read

    morejays already pointed out that the FIRST_ROWS hint may/may not help performance. Start by removing it and seeing what happens.

    There is a jump in cost is at step 414K where the optimizer expects a lot of work to be used. Can you recode the SQL to eliminate the inline view and possibly the internal VW_NSO_1 view along with it?
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points