Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2013
    Posts
    1

    Unanswered: Oracle 10g database optimization

    Hello.

    Sorry for my English.

    I have so big DB on Oracle 10g.

    And I have queries, which executed so slow. For example, I have query :
    115559 rows 102 seconds

    Execution plan:

    Code:
    ------------------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                           | Name                | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
    ------------------------------------------------------------------------------------------------------------------------------------------------
    |   1 |  SORT ORDER BY                      |                     |      1 |  30503 |     50 |00:00:09.33 |    3989 |    33M|  2065K|   29M (0)|
    |*  2 |   HASH JOIN RIGHT OUTER             |                     |      1 |  30503 |    115K|00:00:05.71 |    3989 |   862K|   862K| 1186K (0)|
    |   3 |    INDEX FAST FULL SCAN             | IX2_SLCND           |      1 |   1387 |   1387 |00:00:00.01 |      17 |       |       |          |
    |*  4 |    HASH JOIN RIGHT OUTER            |                     |      1 |  30503 |    115K|00:00:05.47 |    3972 |   785K|   785K| 1145K (0)|
    |   5 |     TABLE ACCESS FULL               | DISC                |      1 |     90 |     90 |00:00:00.01 |       7 |       |       |          |
    |*  6 |     HASH JOIN RIGHT OUTER           |                     |      1 |  30503 |    115K|00:00:05.24 |    3965 |   776K|   776K|  403K (0)|
    |   7 |      VIEW                           | index$_join$_030    |      1 |      2 |      2 |00:00:00.01 |       6 |       |       |          |
    |*  8 |       HASH JOIN                     |                     |      1 |        |      2 |00:00:00.01 |       6 |   760K|   760K|  464K (0)|
    |   9 |        INDEX FAST FULL SCAN         | IX_CMP_NAME         |      1 |      2 |      2 |00:00:00.01 |       3 |       |       |          |
    |  10 |        INDEX FAST FULL SCAN         | PK_CMP              |      1 |      2 |      2 |00:00:00.01 |       3 |       |       |          |
    |* 11 |      HASH JOIN RIGHT OUTER          |                     |      1 |  30503 |    115K|00:00:05.01 |    3959 |   756K|   756K| 1066K (0)|
    |  12 |       TABLE ACCESS FULL             | TTYPE               |      1 |     26 |     27 |00:00:00.01 |       7 |       |       |          |
    |* 13 |       HASH JOIN RIGHT OUTER         |                     |      1 |  30503 |    115K|00:00:04.78 |    3952 |   767K|   767K|  416K (0)|
    |  14 |        TABLE ACCESS FULL            | COMMTCOMP           |      1 |      2 |      2 |00:00:00.01 |       7 |       |       |          |
    |* 15 |        HASH JOIN RIGHT OUTER        |                     |      1 |  30503 |    115K|00:00:04.54 |    3945 |   760K|   760K| 1037K (0)|
    |  16 |         TABLE ACCESS FULL           | TTYPE               |      1 |     26 |     27 |00:00:00.01 |       7 |       |       |          |
    |* 17 |         HASH JOIN RIGHT OUTER       |                     |      1 |  30503 |    115K|00:00:04.31 |    3938 |   842K|   842K| 1267K (0)|
    |  18 |          TABLE ACCESS FULL          | SLRSSEASONTC        |      1 |   2885 |   2974 |00:00:00.01 |      53 |       |       |          |
    |* 19 |          HASH JOIN RIGHT OUTER      |                     |      1 |  30503 |    115K|00:00:04.07 |    3885 |   909K|   909K| 1178K (0)|
    |  20 |           TABLE ACCESS FULL         | CAR                 |      1 |    976 |    976 |00:00:00.01 |      16 |       |       |          |
    |* 21 |           HASH JOIN RIGHT OUTER     |                     |      1 |  30503 |    115K|00:00:03.84 |    3869 |  1036K|  1036K| 1169K (0)|
    |  22 |            INDEX FULL SCAN          | PK_STP              |      1 |    258 |    258 |00:00:00.01 |       1 |       |       |          |
    |* 23 |            HASH JOIN                |                     |      1 |  30503 |    115K|00:00:03.60 |    3868 |   778K|   778K| 1165K (0)|
    |  24 |             TABLE ACCESS FULL       | WKST                |      1 |     83 |     83 |00:00:00.01 |       7 |       |       |          |
    |* 25 |             HASH JOIN               |                     |      1 |  61027 |    115K|00:00:03.37 |    3861 |   804K|   804K|  924K (0)|
    |  26 |              VIEW                   | index$_join$_014    |      1 |     12 |     12 |00:00:00.01 |       6 |       |       |          |
    |* 27 |               HASH JOIN             |                     |      1 |        |     12 |00:00:00.01 |       6 |   783K|   783K|  919K (0)|
    |  28 |                INDEX FAST FULL SCAN | IX_BRANCH_NAME      |      1 |     12 |     12 |00:00:00.01 |       3 |       |       |          |
    |  29 |                INDEX FAST FULL SCAN | PK_BRANCH           |      1 |     12 |     12 |00:00:00.01 |       3 |       |       |          |
    |* 30 |              HASH JOIN              |                     |      1 |  61027 |    115K|00:00:03.13 |    3855 |   808K|   808K| 1166K (0)|
    |  31 |               TABLE ACCESS FULL     | USERS               |      1 |     96 |     96 |00:00:00.01 |      16 |       |       |          |
    |  32 |               MERGE JOIN            |                     |      1 |    115K|    115K|00:00:02.90 |    3839 |       |       |          |
    |* 33 |                HASH JOIN RIGHT OUTER|                     |      1 |    115K|    115K|00:00:02.32 |    3838 |   848K|   848K| 1169K (0)|
    |  34 |                 TABLE ACCESS FULL   | STP                 |      1 |    258 |    258 |00:00:00.01 |       7 |       |       |          |
    |* 35 |                 HASH JOIN           |                     |      1 |    115K|    115K|00:00:01.95 |    3831 |   848K|   848K| 1169K (0)|
    |  36 |                  TABLE ACCESS FULL  | STP                 |      1 |    258 |    258 |00:00:00.01 |       7 |       |       |          |
    |* 37 |                  HASH JOIN          |                     |      1 |    115K|    115K|00:00:01.52 |    3824 |   848K|   848K| 1169K (0)|
    |  38 |                   TABLE ACCESS FULL | STP                 |      1 |    258 |    258 |00:00:00.01 |       7 |       |       |          |
    |* 39 |                   HASH JOIN         |                     |      1 |    115K|    115K|00:00:01.09 |    3817 |    10M|  1956K|   12M (0)|
    |* 40 |                    TABLE ACCESS FULL| SL                  |      1 |    116K|    120K|00:00:00.01 |    2115 |       |       |          |
    |* 41 |                    TABLE ACCESS FULL| SLTC                |      1 |    114K|    115K|00:00:00.19 |    1702 |       |       |          |
    |* 42 |                SORT JOIN            |                     |    115K|      1 |    115K|00:00:00.27 |       1 | 73728 | 73728 |          |
    |* 43 |                 INDEX UNIQUE SCAN   | PK_CMP              |      1 |      1 |      1 |00:00:00.01 |       1 |       |       |          |
    ------------------------------------------------------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       2 - access("SLCND"."ID"="SL"."ID")
       4 - access("SL"."DISC_ID"="DISC"."ID")
       6 - access("CARI"."ID"="SLTC"."CARI_ID")
       8 - access(ROWID=ROWID)
      11 - access("TTYPE"."ID"="SLTC"."TTYPE_ID")
      13 - access("CTC"."ID"="SLRSSEASONTC "."ID_COMMTCOMP")
      15 - access("RS"."ID"="SLRSSEASONTC "."COMMTTYPE_ID")
      17 - access("SLRSSEASONTC "."ID"="SLTC"."ID")
      19 - access("CAR"."ID"="SLTC"."CAR_ID")
      21 - access("STP"."ID"="WKST"."DEF_STP_ID")
      23 - access("SL"."POS_WKST_ID"="WKST"."ID")
      25 - access("USERS"."BRANCH_ID"="BRANCH"."ID")
      27 - access(ROWID=ROWID)
      30 - access("SL"."USR_CAS_ID"="USERS"."ID")
      33 - access("SLTC"."THSTP_ID"="THSTP"."ID")
      35 - access("SLTC"."TSTP_ID"="TSTP"."ID")
      37 - access("SLTC"."FSTP_ID"="FSTP"."ID")
      39 - access("SLTC"."ID"="SL"."ID")
      40 - filter(("SL"."WHEN_DATE">=TO_DATE('2001-10-29 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "SL"."SCOMP_ID"=1 AND 
                  "SL"."WHEN_DATE"<=TO_DATE('2013-10-29 23:59:59', 'yyyy-mm-dd hh24:mi:ss')))
      41 - filter("SLTC"."MANUAL"='N')
      42 - access("SL"."SCOMP_ID"="CMP"."ID")
           filter("SL"."SCOMP_ID"="CMP"."ID")
      43 - access("CMP"."ID"=1)
    I need ideas to make db faster.

    p.s.
    Im new in databases
    Thanks You!

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    It looks like your estimated rows, and actual rows are significantly different (e-rows vs. a-rows). I would start by making sure the index statistics for the tables in the query (probably the rest of the database, as well) are up to date.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •