This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Jan 11, 2013 9:16 AM by Nikolay Savvinov Go to original post RSS
  • 15. Re: sql is running very slow
    moreajays Pro
    Currently Being Moderated
    Hi,

    If that is the case then EXISTS condition may help you out


    In the place of
    AND (tl_group_id) IN (SELECT tl_group_id
    FROM ps_oi_tl_group_sel
    WHERE oprid = 'FCAMERON')
    Put
    AND EXISTS (SELECT 1
    FROM ps_oi_tl_group_sel a, ps_oi_tl_rptim_vw5 b 
    WHERE a.tl_group_id = b.tl_group_id
    and  a.oprid = 'FCAMERON')
    Also re-check if i have given correct column in EXISTS condition

    Thanks,
    Ajay More
    http://moreajays.blogspot.com
  • 16. Re: sql is running very slow
    riedelme Expert
    Currently Being Moderated
    Oceaner wrote:
    If I change the query, Instead of fetching the value for TL_GROUP_ID from ps_oi_tl_group_sel, I put it as a constant value (Though just for testing), query runs fine..
    You appear to have 2 answers - the SQL profile and a problem with the subquery. Both should be workable, though I am more comforatble with the subquery fix (if it continues working) since the profiles are something of a "black box".

    SQL profiles alter the environment for a SQL for efficiency. What they do can be a bit mysterious (which is what I am uncomfortable with). They often provide good results though and if you're happy with the results use it.

    I understand the subquery answer better - something in the subquery itself or its inclusion is slowing things down. If you decide to use the EXISTS subquery make sure you do a correlated lookup for efficiency.

    Either way test your approch carefully!

    Good luck.
  • 17. Re: sql is running very slow
    Oceaner Explorer
    Currently Being Moderated
    I have tried the Exists clause instead of IN clause but it doesnot made any difference. Here is the plan with Exists clause
    ------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                            |                    |     1 |  2446 | 49965   (1)| 00:10:00 |
    |*  1 |  FILTER                                     |                    |       |       |            |          |
    |   2 |   VIEW                                      | PS_OI_TL_RPTIM_VW5 |     1 |  2446 | 49964   (1)| 00:10:00 |
    |   3 |    HASH UNIQUE                              |                    |     1 |   642 | 49964   (1)| 00:10:00 |
    |*  4 |     FILTER                                  |                    |       |       |            |          |
    |*  5 |      HASH JOIN                              |                    |     4 |  2568 |   751   (1)| 00:00:10 |
    |   6 |       NESTED LOOPS                          |                    |       |       |            |          |
    |   7 |        NESTED LOOPS                         |                    |     1 |   624 |   700   (1)| 00:00:09 |
    |   8 |         NESTED LOOPS                        |                    |     1 |   596 |   699   (1)| 00:00:09 |
    |   9 |          NESTED LOOPS                       |                    |     1 |   587 |   699   (1)| 00:00:09 |
    |* 10 |           HASH JOIN                         |                    |   108 | 59184 |   483   (1)| 00:00:06 |
    |* 11 |            HASH JOIN                        |                    |   111 | 58608 |   478   (0)| 00:00:06 |
    |  12 |             INDEX FAST FULL SCAN            | PS_TL_TRC_TBL      |  1066 | 14924 |     3   (0)| 00:00:01 |
    |  13 |             NESTED LOOPS OUTER              |                    |    78 | 40092 |   475   (0)| 00:00:06 |
    |  14 |              NESTED LOOPS OUTER             |                    |    78 | 36504 |   397   (0)| 00:00:05 |
    |  15 |               NESTED LOOPS                  |                    |    78 | 22932 |   396   (0)| 00:00:05 |
    |  16 |                NESTED LOOPS                 |                    |    78 | 20826 |   240   (0)| 00:00:03 |
    |  17 |                 NESTED LOOPS                |                    |    12 |  2508 |   204   (0)| 00:00:03 |
    |  18 |                  TABLE ACCESS BY INDEX ROWID| PS_NAMES           |     6 |   222 |   167   (0)| 00:00:03 |
    |* 19 |                   INDEX SKIP SCAN           | PSANAMES           |     6 |       |   160   (0)| 00:00:02 |
    |  20 |                    SORT AGGREGATE           |                    |     1 |    21 |            |          |
    |* 21 |                     INDEX RANGE SCAN        | PS_NAMES           |     1 |    21 |     2   (0)| 00:00:01 |
    |  22 |                  TABLE ACCESS BY INDEX ROWID| PS_TL_RPTD_TIME    |     2 |   344 |     7   (0)| 00:00:01 |
    |* 23 |                   INDEX RANGE SCAN          | PS_TL_RPTD_TIME    |     3 |       |     5   (0)| 00:00:01 |
    |  24 |                 TABLE ACCESS BY INDEX ROWID | PS_JOB             |     6 |   348 |     3   (0)| 00:00:01 |
    |* 25 |                  INDEX RANGE SCAN           | IDX$$_3F450003     |     1 |       |     2   (0)| 00:00:01 |
    |* 26 |                INDEX RANGE SCAN             | PSASET_CNTRL_REC   |     1 |    27 |     2   (0)| 00:00:01 |
    |  27 |               TABLE ACCESS BY INDEX ROWID   | PS_OI_TL_PRJ_COMNT |     1 |   174 |     1   (0)| 00:00:01 |
    |* 28 |                INDEX UNIQUE SCAN            | PS_OI_TL_PRJ_COMNT |     1 |       |     0   (0)| 00:00:01 |
    |  29 |              TABLE ACCESS BY INDEX ROWID    | PS_PROJECT         |     1 |    46 |     1   (0)| 00:00:01 |
    |* 30 |               INDEX UNIQUE SCAN             | PS_PROJECT         |     1 |       |     0   (0)| 00:00:01 |
    |  31 |            INDEX FAST FULL SCAN             | PS4DEPT_TBL        |  1815 | 36300 |     4   (0)| 00:00:01 |
    |  32 |           TABLE ACCESS BY INDEX ROWID       | PS_OI_BUS_TITLE    |     1 |    39 |     2   (0)| 00:00:01 |
    |* 33 |            INDEX UNIQUE SCAN                | PS_OI_BUS_TITLE    |     1 |       |     1   (0)| 00:00:01 |
    |* 34 |          INDEX UNIQUE SCAN                  | PS_PERSONAL_DATA   |     1 |     9 |     0   (0)| 00:00:01 |
    |* 35 |         INDEX UNIQUE SCAN                   | PS_PERSONAL_DATA   |     1 |       |     0   (0)| 00:00:01 |
    |  36 |        TABLE ACCESS BY INDEX ROWID          | PS_PERSONAL_DATA   |     1 |    28 |     1   (0)| 00:00:01 |
    |  37 |       INDEX FAST FULL SCAN                  | PS_TL_GROUP_DTL    | 51579 |   906K|    50   (0)| 00:00:01 |
    |  38 |      SORT AGGREGATE                         |                    |     1 |    20 |            |          |
    |* 39 |       INDEX RANGE SCAN                      | PSAJOB             |     1 |    20 |     3   (0)| 00:00:01 |
    |  40 |      SORT AGGREGATE                         |                    |     1 |    14 |            |          |
    |* 41 |       INDEX RANGE SCAN                      | PS_TL_TRC_TBL      |     1 |    14 |     2   (0)| 00:00:01 |
    
    PLAN_TABLE_OUTPUT
    -------------------------------------------------------------------------------------------------------------------
    |  42 |      SORT AGGREGATE                         |                    |     1 |    20 |            |          |
    |* 43 |       INDEX RANGE SCAN                      | PS_DEPT_TBL        |     1 |    20 |     2   (0)| 00:00:01 |
    |  44 |      SORT AGGREGATE                         |                    |     1 |    23 |            |          |
    |* 45 |       INDEX RANGE SCAN                      | PSAJOB             |     1 |    23 |     3   (0)| 00:00:01 |
    |* 46 |   INDEX RANGE SCAN                          | PS_OI_TL_GROUP_SEL |     1 |    14 |     1   (0)| 00:00:01 |
    ------------------------------------------------------------------------------------------------------------------
    What problem I am seeing is it is fetching all data from the view and from the table from subquery, and then doing a hash join between two results.

    To resolve this, I have changed the Query in which I have removed the subquery and replaced it with a join between View and Table using a join between TL_GROUP_ID and it has worked fine.
    -----------------------------------------------------------------------------------------------------------------
    | Id  | Operation                                  | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                           |                    |     1 |  2448 |   541   (1)| 00:00:07 |
    |   1 |  SORT ORDER BY                             |                    |     1 |  2448 |   541   (1)| 00:00:07 |
    |   2 |   VIEW                                     | VM_NWVW_0          |     1 |  2448 |   541   (1)| 00:00:07 |
    |   3 |    HASH UNIQUE                             |                    |     1 |   512 |   541   (1)| 00:00:07 |
    |   4 |     NESTED LOOPS                           |                    |     1 |   512 |   267   (1)| 00:00:04 |
    |   5 |      NESTED LOOPS                          |                    |     1 |   503 |   267   (1)| 00:00:04 |
    |   6 |       NESTED LOOPS                         |                    |     1 |   476 |   266   (1)| 00:00:04 |
    |   7 |        NESTED LOOPS                        |                    |     1 |   456 |   265   (1)| 00:00:04 |
    |   8 |         NESTED LOOPS                       |                    |     1 |   429 |   263   (1)| 00:00:04 |
    |   9 |          NESTED LOOPS                      |                    |     1 |   371 |   261   (1)| 00:00:04 |
    |  10 |           NESTED LOOPS                     |                    |     1 |   332 |   258   (1)| 00:00:04 |
    |  11 |            NESTED LOOPS OUTER              |                    |     1 |   318 |   257   (1)| 00:00:04 |
    |  12 |             NESTED LOOPS                   |                    |     1 |   272 |   256   (1)| 00:00:04 |
    |* 13 |              HASH JOIN                     |                    |    37 |  9546 |   256   (1)| 00:00:04 |
    |  14 |               NESTED LOOPS OUTER           |                    |    12 |  2880 |   205   (0)| 00:00:03 |
    |  15 |                NESTED LOOPS                |                    |    12 |  2508 |   204   (0)| 00:00:03 |
    |  16 |                 TABLE ACCESS BY INDEX ROWID| PS_NAMES           |     6 |   222 |   167   (0)| 00:00:03 |
    |* 17 |                  INDEX SKIP SCAN           | PSANAMES           |     6 |       |   160   (0)| 00:00:02 |
    |  18 |                   SORT AGGREGATE           |                    |     1 |    21 |            |          |
    |* 19 |                    INDEX RANGE SCAN        | PS_NAMES           |     1 |    21 |     2   (0)| 00:00:01 |
    |  20 |                 TABLE ACCESS BY INDEX ROWID| PS_TL_RPTD_TIME    |     2 |   344 |     7   (0)| 00:00:01 |
    |* 21 |                  INDEX RANGE SCAN          | PS_TL_RPTD_TIME    |     3 |       |     5   (0)| 00:00:01 |
    |  22 |                TABLE ACCESS BY INDEX ROWID | PS_OI_TL_PRJ_COMNT |     1 |    31 |     1   (0)| 00:00:01 |
    |* 23 |                 INDEX UNIQUE SCAN          | PS_OI_TL_PRJ_COMNT |     1 |       |     0   (0)| 00:00:01 |
    |  24 |               INDEX FAST FULL SCAN         | PS_TL_GROUP_DTL    | 51595 |   906K|    50   (0)| 00:00:01 |
    |* 25 |              INDEX UNIQUE SCAN             | PS_OI_TL_GROUP_SEL |     1 |    14 |     0   (0)| 00:00:01 |
    |  26 |             TABLE ACCESS BY INDEX ROWID    | PS_PROJECT         |     1 |    46 |     1   (0)| 00:00:01 |
    |* 27 |              INDEX UNIQUE SCAN             | PS_PROJECT         |     1 |       |     0   (0)| 00:00:01 |
    |* 28 |            INDEX RANGE SCAN                | PS_TL_TRC_TBL      |     1 |    14 |     1   (0)| 00:00:01 |
    |  29 |             SORT AGGREGATE                 |                    |     1 |    14 |            |          |
    |* 30 |              INDEX RANGE SCAN              | PS_TL_TRC_TBL      |     1 |    14 |     2   (0)| 00:00:01 |
    |  31 |           TABLE ACCESS BY INDEX ROWID      | PS_OI_BUS_TITLE    |     7 |   273 |     3   (0)| 00:00:01 |
    |* 32 |            INDEX RANGE SCAN                | PS_OI_BUS_TITLE    |     1 |       |     2   (0)| 00:00:01 |
    |  33 |          TABLE ACCESS BY INDEX ROWID       | PS_JOB             |     1 |    58 |     2   (0)| 00:00:01 |
    |* 34 |           INDEX UNIQUE SCAN                | PS_JOB             |     1 |       |     1   (0)| 00:00:01 |
    |  35 |            SORT AGGREGATE                  |                    |     1 |    20 |            |          |
    |* 36 |             INDEX RANGE SCAN               | PSAJOB             |     1 |    20 |     3   (0)| 00:00:01 |
    |  37 |            SORT AGGREGATE                  |                    |     1 |    23 |            |          |
    |* 38 |             INDEX RANGE SCAN               | PSAJOB             |     1 |    23 |     3   (0)| 00:00:01 |
    |* 39 |         INDEX RANGE SCAN                   | PSASET_CNTRL_REC   |     1 |    27 |     2   (0)| 00:00:01 |
    |* 40 |        INDEX RANGE SCAN                    | PS_DEPT_TBL        |     1 |    20 |     1   (0)| 00:00:01 |
    |  41 |         SORT AGGREGATE                     |                    |     1 |    20 |            |          |
    |* 42 |          INDEX RANGE SCAN                  | PS_DEPT_TBL        |     1 |    20 |     2   (0)| 00:00:01 |
    |  43 |       TABLE ACCESS BY INDEX ROWID          | PS_PERSONAL_DATA   |     1 |    27 |     1   (0)| 00:00:01 |
    |* 44 |        INDEX UNIQUE SCAN                   | PS_PERSONAL_DATA   |     1 |       |     0   (0)| 00:00:01 |
    |* 45 |      INDEX UNIQUE SCAN                     | PS_PERSONAL_DATA   |     1 |     9 |     0   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------------------------------------
    Please correct me if I am wrong somewhere

    Regards,
    Navneet
  • 18. Re: sql is running very slow
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi,

    explain plan is only useful for tuning when the cost it shows is comparable with actual logical I/O (consistent gets). The plan you posted shows a low cost (its 15 times lower than the actual I/O for the good plan, so it's wrong by several orders of magnitude), and therefore it cannt possibly tell us where the actual high cost originates from.

    The plan with rowsource statstics is more useful, because it contains information about the actual numbers of rows returned at each step ("A-rows") which we can compare to optimizer's estimates to see where it was wrong. Unfortunately, it's not the plan that you want t fix, but still, the optimizer errors that show up in this ("good") plan may be the same that lead to optimizer's poor choices in the original plan. In any case, before you provide the plan with rowsource stats for the "bad" execution of the query, that's all we have.

    This plan shows a large discrepancy between actual and estimated rows in step 18:
    |* 18 |  17 |   4 |                  INDEX SKIP SCAN           | PSANAMES           |      1 |      3 |  26187 |00:00:00.44 |   27820 |     10 |
    which is most likely explained by wrong estimation of the selectivity of the predicates:
     18 - access("SYS_ALIAS_13"."NAME_TYPE"='PRI')
           filter(("SYS_ALIAS_13"."NAME_TYPE"='PRI' AND "SYS_ALIAS_13"."EFFDT"=))
    Several explanations are possible:

    1) stale or non-representative statistics on PS_ANAMES table and/or its index(es)
    2) skewed distribution on one or both (NAME_TYPE and EFFDT) columns with missing or suboptimal histogram
    3) strong correlation betwen the two columns

    In order to recommend a solution, it is important to find out which case it is. Also, it's probably worth mentioning that SKIP SCAN is not a preferred way of accessing an index, especially when NDV of the leading column is relatively high, so you may consider creating a new index to replace this step with a RANGE (or UNIQUE) SCAN.

    Best regards,
    Nikolay
1 2 Previous Next

Legend

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