1 2 Previous Next 16 Replies Latest reply: Feb 18, 2013 9:35 AM by 985871 RSS

    Need help to Tune a Query

    985871
      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
          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
            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
              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
                Ajay ,

                it says insufficient privilages to run that script
                • 5. Re: Need help to Tune a Query
                  moreajays
                  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
                    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
                      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
                        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
                          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
                            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
                              SEGMENT_NAME                                                                      BYTES/1024/1024
                              --------------------------------------------------------------------------------- ---------------
                              ACCOUNTS                                                                                       27
                              ACCOUNT_TYPES                                                                               .0625
                              USERS                                                                                             7
                              USER_ACCOUNTS                                                                                  23
                              • 12. Re: Need help to Tune a Query
                                moreajays
                                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
                                  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
                                    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