8 Replies Latest reply on Apr 2, 2013 4:29 PM by orausern

    How to rewrite a time consuming sql

    orausern
      Hi Experts,

      We are on Oracle 11.2.0.2 on Solaris 10. I have a query having performance issue and it has two claues one exists clause and one not exists clause. I want to rewrite the sql to change these to IN and NOT IN and have a question as to how to rewrite. Can someone help on it?
      Following is the sql:
      
      variable v_resource_oid NUMBER;
      variable v_resource_type varchar2(30);
      variable par_app_id varchar2(30);
      variable par_client_oid varchar2(30);
      
      exec :v_resource_oid :=306615;
      exec :v_resource_type:='PROCESS';
      exec :par_app_id:='App1';
      exec :par_client_oid:='XCEMT2';
      
      INSERT INTO access_grp_resource_xref
         SELECT DISTINCT d.grp_oid,
                         a.resource_oid,
                         x.excluded,
                         SYSDATE,
                         'ADD_RESOURCES_FOR Company',
                         NULL,
                         NULL
           FROM access_grp_resource_xref a
                JOIN access_grp b
                   ON (b.grp_oid = a.grp_oid)
                JOIN access_grp c
                   ON (c.grp_name = b.grp_name)
                JOIN access_grp d
                   ON (d.parent_grp_oid = c.grp_oid)
                JOIN access_resource f
                   ON (f.resource_oid = a.resource_oid)
                JOIN access_resource_hierarchy rh
                   ON (rh.resource_oid = f.resource_oid AND rh.view_id = b.app_id)
                JOIN access_grp_resource_xref x
                   ON (    rh.parent_resource_oid = x.resource_oid
                       AND x.grp_oid = d.grp_oid)
          WHERE     a.resource_oid = :v_resource_oid
                AND f.resource_type = :v_resource_type
                AND b.app_id = :par_app_id
                AND c.client_oid = :par_client_oid
                AND NOT EXISTS
                           (SELECT 1
                              FROM access_grp_resource_xref e
                             WHERE     e.grp_oid = d.grp_oid
                                   AND e.resource_oid = a.resource_oid)
                AND EXISTS
                       (SELECT 1
                          FROM access_grp_resource_xref g
                         WHERE        g.grp_oid = c.grp_oid
                                  AND g.resource_oid = rh.parent_resource_oid
                               OR rh.parent_resource_oid = 0);
      Note that all other tables referred in the sql has less than 3000 rows whereas this table access_grp_resource_xref has 138,425 rows. I have read that IN and NOT in can be helpful sometimes versus EXISTS and NOT EXISTS but I am not sure on how to do this rewrite.

      Thanks,
      OrauserN

      Edited by: orausern on Mar 29, 2013 10:15 AM

      Edited by: orausern on Mar 29, 2013 10:15 AM
        • 1. Re: How to rewrite a time consuming sql
          Please see HOW TO: Post a SQL statement tuning request - template posting


          Right now the information you provide is insufficient.

          -------------
          Sybrand Bakker
          Senior Oracle DBA
          • 2. Re: How to rewrite a time consuming sql
            Jonathan Lewis
            orausern wrote:
            AND EXISTS
            (SELECT 1
            FROM access_grp_resource_xref g
            WHERE        g.grp_oid = c.grp_oid
            AND g.resource_oid = rh.parent_resource_oid
            OR rh.parent_resource_oid = 0);
            First step - check that you've written the statement you intended to write. The OR clause in the last suqbuery looks suspect - might you want a bracket around (g.resource_oid = rh.parent_resource_oid OR rh.parent_resource_oid = 0).
            Note that all other tables referred in the sql has less than 3000 rows whereas this table access_grp_resource_xref has 138,425 rows. I have read that IN and NOT in can be helpful sometimes versus EXISTS and NOT EXISTS but I am not sure on how to do this rewrite.
            So what's the execution plan ? Oracle is pretty good at transforming IN and NOT IN to EXISTS and NOT EXISTS where it's efficient and legal - so there's no point in rewriting the query unless you know the plan you want to see in case (a) you still get the same plan after wasting a lot of effort or (b) you manage to do it wrong.

            How much data does the insert generate ? How long does that take ? What's the current execution plan ? How do you think Oracle should operate the query.

            Regards
            Jonathan Lewis
            1 person found this helpful
            • 3. Re: How to rewrite a time consuming sql
              JohnWatson
              Here is a simple example of two equivalent SQLs, one using EXISTS and the other using IN:
              select dname from dept d where exists (select 1 from emp e where e.deptno=d.deptno);
              select dname from dept where deptno in (Select deptno from emp);
              The execution plans are identical. I'm sure you'll find that you get the same plan if you do the same re-write. You can trust the optimizer to recognisze te equivalence.
              • 4. Re: How to rewrite a time consuming sql
                orausern
                Hi Jonathan,

                The sql takes one minute and 15 seconds in development environment (and in production more than 4 minutes) and it is called hundreds of time from a stored procedure and so has become a problem. The insert generates 0 rows! In some schenario it may insert may be 10 rows or so but even when it inserts 0 rows, it takes this much time. About your question "how do you think Oracle should handlle the query" well if I am not very knowledgable so unable to tell what the query plan should be! It needs to be done in something like 5 seconds or so.

                Following is the current and time consuming execution plan from my development environment:
                Plan hash value: 997206842
                
                ----------------------------------------------------------------------------------------------------
                
                PLAN_TABLE_OUTPUT
                ----------------------------------------------------------------------------------------------------
                | Id  | Operation                               | Name                          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
                ----------------------------------------------------------------------------------------------------
                |   0 | INSERT STATEMENT                        |                               |       |       |       |  1403 (100)|          |
                |   1 |  LOAD TABLE CONVENTIONAL                |                               |       |       |       |            |          |
                |   2 |   HASH UNIQUE                           |                               |  1039 |   183K|       |  1403  (19)| 00:00:17 |
                |*  3 |    HASH JOIN RIGHT ANTI                 |                               |   254K|    43M|       |  1390  (18)| 00:00:17 |
                |   4 |     TABLE ACCESS BY INDEX ROWID         | ACCESS_GRP_RESOURCE_XREF      |    71 |   923 |       |    40   (0)| 
                |*  5 |      INDEX RANGE SCAN                   | ACCESS_GRP_RESOURCE_XREF_IDX02|    71 |       |       |     1   (0)| 00:00:01 
                |*  6 |     HASH JOIN                           |                               |   272K|    43M|       |  1348  (19)| 00:00:17 |
                |   7 |      TABLE ACCESS BY INDEX ROWID        | ACCESS_GRP_RESOURCE_XREF      |    71 |   923 |       |    40   (0)| 
                |*  8 |       INDEX RANGE SCAN                  | ACCESS_GRP_RESOURCE_XREF_IDX02|    71 |       |       |     1   (0)| 00:00:01
                |   9 |      MERGE JOIN                         |                               |  1185K|   175M|       |  1301  (19)| 00:00:16 |
                |  10 |       SORT JOIN                         |                               |   218K|    24M|       |  1284  (18)| 00:00:16 |
                |* 11 |        HASH JOIN                        |                               |   218K|    24M|       |  1284  (18)| 00:00:16 |
                |* 12 |         VIEW                            | index$_join$_005              |    59 |  2537 |       |     8  (13)| 00:00:01 |
                |* 13 |          HASH JOIN                      |                               |       |       |       |            |          |
                |* 14 |           INDEX RANGE SCAN              | ACCESS_GRP_IDX01              |    59 |  2537 |       |     2   (0)| 00:00:01 |
                |  15 |           INDEX FAST FULL SCAN          | PK_ACCESS_GROUP               |    59 |  2537 |       |     6   (0)| 00:00:01 |
                |  16 |         MERGE JOIN                      |                               |    10M|   738M|       |  1220  (15)| 00:00:15 |
                |  17 |          SORT JOIN                      |                               |    29M|  1818M|       |  1209  (15)| 00:00:15 |
                |* 18 |           HASH JOIN                     |                               |    29M|  1818M|  3944K|  1209  (15)| 00:00:15 |
                
                PLAN_TABLE_OUTPUT
                ----------------------------------------------------------------------------------------------------
                |  19 |            TABLE ACCESS FULL            | ACCESS_GRP_RESOURCE_XREF      |   144K|  2253K|       |   274   (1)| 00:00:04 |
                |  20 |            MERGE JOIN                   |                               |   105K|  4933K|       |   278   (1)| 00:00:04 |
                |  21 |             NESTED LOOPS                |                               |   144K|  3802K|       |   275   (1)| 00:00:04 |
                |* 22 |              TABLE ACCESS BY INDEX ROWID| ACCESS_RESOURCE               |     1 |    14 |       |     2   (0)| 00:00:01 
                |* 23 |               INDEX UNIQUE SCAN         | PK_ACCESS_RESOURCE            |     1 |       |       |     1   (0)| 00:00:01 |
                |  24 |              INDEX FAST FULL SCAN       | PK_ACCESS_GRP_RESOURCE_XREF   |   144K|  1830K|       |   273   (1)| 00:
                |* 25 |             SORT JOIN                   |                               |     1 |    21 |       |     3  (34)| 00:00:01 |
                |* 26 |              INDEX RANGE SCAN           | ACCESS_RESOURCE_HIERARCHY_PK  |     1 |    21 |       |     2   (0)| 00:00:0
                |* 27 |          SORT JOIN                      |                               |  1039 | 13507 |       |    11  (19)| 00:00:01 |
                |  28 |           VIEW                          | index$_join$_007              |  1039 | 13507 |       |    10  (10)| 00:00:01 |
                |* 29 |            HASH JOIN                    |                               |       |       |       |            |          |
                |* 30 |             INDEX FAST FULL SCAN        | ACCESS_GRP_IDX03              |  1039 | 13507 |       |     5   (0)| 00:00:01 |
                |  31 |             INDEX FAST FULL SCAN        | PK_ACCESS_GROUP               |  1039 | 13507 |       |     6   (0)| 00:00:01 |
                |* 32 |       SORT JOIN                         |                               |   309 | 10815 |       |    12   (9)| 00:00:01 |
                |* 33 |        TABLE ACCESS FULL                | ACCESS_GROUP                  |   309 | 10815 |       |    11   (0)| 00:00:01 |
                ----------------------------------------------------------------------------------------------------
                
                Predicate Information (identified by operation id):
                ---------------------------------------------------
                
                   3 - access("E"."GRP_OID"="D"."GRP_OID" AND "E"."RESOURCE_OID"="A"."RESOURCE_OID")
                
                PLAN_TABLE_OUTPUT
                ----------------------------------------------------------------------------------------------------
                   5 - access("E"."RESOURCE_OID"=:V_RESOURCE_OID)
                   6 - access("F"."RESOURCE_OID"="A"."RESOURCE_OID" AND "B"."GRP_OID"="A"."GRP_OID")
                   8 - access("A"."RESOURCE_OID"=:V_RESOURCE_OID)
                  11 - access("D"."PARENT_GRP_OID"="C"."GRP_OID")
                       filter((("G"."GRP_OID"="C"."GRP_OID" AND "G"."RESOURCE_OID"="RH"."PARENT_RESOURCE_OID") O
                              "RH"."PARENT_RESOURCE_OID"=:SYS_B_3))
                  12 - filter("C"."CLIENT_OID"=:PAR_CLIENT_OID)
                  13 - access(ROWID=ROWID)
                  14 - access("C"."CLIENT_OID"=:PAR_CLIENT_OID)
                  18 - access("RH"."PARENT_RESOURCE_OID"="X"."RESOURCE_OID")
                  22 - filter("F"."RESOURCE_TYPE"=:V_RESOURCE_TYPE)
                  23 - access("F"."RESOURCE_OID"=:V_RESOURCE_OID)
                  25 - access("RH"."RESOURCE_OID"="F"."RESOURCE_OID")
                       filter("RH"."RESOURCE_OID"="F"."RESOURCE_OID")
                  26 - access("RH"."RESOURCE_OID"=:V_RESOURCE_OID AND "RH"."VIEW_ID"=:PAR_APP_ID)
                       filter("RH"."VIEW_ID"=:PAR_APP_ID)
                  27 - access("X"."GRP_OID"="D"."GRP_OID")
                       filter("X"."GRP_OID"="D"."GRP_OID")
                  29 - access(ROWID=ROWID)
                  30 - filter("D"."PARENT_GRP_OID" IS NOT NULL)
                  32 - access("RH"."VIEW_ID"="B"."APP_ID" AND "C"."GRP_NAME"="B"."GRP_NAME")
                
                PLAN_TABLE_OUTPUT
                ----------------------------------------------------------------------------------------------------
                       filter(("C"."GRP_NAME"="B"."GRP_NAME" AND "RH"."VIEW_ID"="B"."APP_ID"))
                  33 - filter("B"."APP_ID"=:PAR_APP_ID)
                • 5. Re: How to rewrite a time consuming sql
                  Jonathan Lewis
                  orausern wrote:

                  The sql takes one minute and 15 seconds in development environment (and in production more than 4 minutes) and it is called hundreds of time from a stored procedure and so has become a problem. The insert generates 0 rows! In some schenario it may insert may be 10 rows or so but even when it inserts 0 rows, it takes this much time. About your question "how do you think Oracle should handlle the query" well if I am not very knowledgable so unable to tell what the query plan should be! It needs to be done in something like 5 seconds or so.
                  If it's taking a long time to find a few rows then it's doing a lot of work accessing and constructing data and then throwing it away. There are two major points to consider (one is very specific to your query).

                  a) Does the query generate a large amount of data, then collapse it to a small size because of the DISTINCT in the select - in this case is there a coding error which means a join has not been specified correctly thus creating multiple copies of rows which then have to be eliminated at great cost; or is it a clue that you could rewrite the query with an in-line aggregate (group by) view to stop the data set from growing.

                  b) If you're throwing away lots of data, can you find out which step in the query is the step that eliminates data, and make that step run as early as possible. (for example, your plan shows the NOT EXISTS turning into a very late hash right anti join - perhaps you can force that subquery to operate earlier).

                  Two steps to adopt:
                  a) Run the query again with the gather_plan_statistics hint, or with statistics_level set to all to generate the rowsource execution statistics then pull the plan from memory with dbms_xplan.display_cursor; or you could just enable sql_trace and get the stats and plan through tkprof. This will tell you where the time went and where the data volume expanded and collapsed.

                  b) Look at the query and the data - and the predicates - and ask yourself: "which ONE table should I go to with the predicates I have to pick up a small number of rows", and then just keep asking "which table should I go to next to keep the result set small". You don't need to decide about about access paths and join methods at this point, you're simply trying to work out if it might be possible to get the data you need without creating a very large intermediate data set.


                  You didn't answer my question about the suspect subquery.

                  Regards
                  Jonathan Lewis
                  1 person found this helpful
                  • 6. Re: How to rewrite a time consuming sql
                    695836
                    Hi,
                    Does the data in dev and prod same?
                    Why don't you compare the execution plans of dev and prod and see where the difference is?
                    • 7. Re: How to rewrite a time consuming sql
                      orausern
                      Thank you again Jonathan!!! My knowledge on Tuning is less and I am trying to understand your points and will come back once I have the details.

                      Thank you!!
                      OrauserN.
                      • 8. Re: How to rewrite a time consuming sql
                        orausern
                        Hi Jonathan,

                        Our developers decided to entirely rewrite the logic and totally change this sql. So marking the question as answered and again thanks for the valuable suggestions!

                        Regards,
                        OrauserN