Forum Stats

  • 3,770,159 Users
  • 2,253,079 Discussions
  • 7,875,349 Comments

Discussions

using index hint

User_KWXZ9
User_KWXZ9 Member Posts: 232 Blue Ribbon
edited Jun 13, 2013 10:52PM in General Database Discussions

Hi Friends ,

I am using 11.2.0.3.0 . one of my views is being used on live and the same data is getting replicated(without some colums) via Golden Gate to Staging. The query of the view runs well in live but the same is running slow on staging . Can you please help me how to force indexes for more then 2 tables and indexes .

Regards ,

Tagged:

Answers

  • Mark D Powell
    Mark D Powell Member Posts: 5,914 Blue Diamond

    user 985871, I have not reviewed the link sb92075 pointed you to yet but step one is usually to get the explain plan for the query in question.  Step two is often updating the statistics for the tables/indexes involved.  Or perhaps comparison of the plans from both instances and then the statistics on each instance against each other so you can determine why the plans differ in this case.

    To get help with a query tuning issue you generally need to post the plan.  Listing indexes available including column information and table size information is also often useful.

    HTH -- Mark D Powell --

  • Karan
    Karan Member Posts: 989 Bronze Badge
    edited Jun 13, 2013 10:14AM

    When you access data from the view optimization applies to select-project-join views which do not use aggregate functions , group by and so on... On the destination yes there can be many reasons why plan has changed like statistics missing, unselective indexes and so on, still there are many factors which can influence the optimizer to change the plans. So post the differences in the plan first from both instances

  • rahulras
    rahulras Member Posts: 876

    You have something which works fine in Live, but not in a test environment. If data volumes are same, hardware setup is reasonably same, all I can think is statistics. Either there is no stats or more likely, wrong stats in your test environment.

    Re-collect the stats and give it a go.

    Best way to understand the problem is look at the plan in your test database. If you can compare it with plan on Live, nothing like that


  • User_KWXZ9
    User_KWXZ9 Member Posts: 232 Blue Ribbon
    edited Jun 13, 2013 11:46AM

    CREATE OR REPLACE FORCE VIEW S_INVENTORY_VIEW

    (SERVICE_ID, ALTERNATIVE_ID, ASSET_CREATION_DATE, ASSET_BILLING_ACCNT_ID, BILLING_ACCOUNT_NUMBER,

    BILLING_ACCOUNT_NAME, ASSET_OWNER_ACCNT_ID, COMPANY_NAME, ACCOUNT_IDENTITY_VALUE, PRODUCT_SCODE,

    PRODUCT_NAME, ASSET_ID, ASSET_STATUS, ADDRFROM_INTEGRATION_ID, ADDRTO_INTEGRATION_ID,

    ADDRFROM_ADDR_LINE, ADDRTO_ADDR_LINE, ADDRTO_ZIPCODE, ADDRFROM_ZIPCODE, PARENT_ASSET_ID)

    AS

    SELECT ast.serial_num, serpt.serial_num, ast.created, ast.bill_accnt_id,

              billact.ou_num, billact.NAME, ast.owner_accnt_id, cug.NAME,

              ast.integration_id, prod.part_num, prod.NAME, ast.row_id,

              ast.status_cd, addrfrom.integration_id, addrto.integration_id,

                 addrfrom.addr

              || DECODE (addrfrom.addr_line_2, NULL, NULL, ',')

              || addrfrom.addr_line_2

              || DECODE (addrfrom.addr_line_3, NULL, NULL, ',')

              || addrfrom.addr_line_3

              || DECODE (addrfrom.addr_num, NULL, NULL, ',')

              || addrfrom.addr_num

              || DECODE (addrfrom.x_premise_name, NULL, NULL, ',')

              || addrfrom.x_premise_name

              || DECODE (addrfrom.city, NULL, NULL, ',')

              || addrfrom.city

              || DECODE (addrfrom.country, NULL, NULL, ',')

              || addrfrom.country,

                 addrto.addr

              || DECODE (addrto.addr_line_2, NULL, NULL, ',')

              || addrto.addr_line_2

              || DECODE (addrto.addr_line_3, NULL, NULL, ',')

              || addrto.addr_line_3

              || DECODE (addrto.addr_num, NULL, NULL, ',')

              || addrto.addr_num

              || DECODE (addrto.x_premise_name, NULL, NULL, ',')

              || addrto.x_premise_name

              || DECODE (addrto.city, NULL, NULL, ',')

              || addrto.city

              || DECODE (addrto.country, NULL, NULL, ',')

              || addrto.country,

              addrto.zipcode, addrfrom.zipcode, ast.par_asset_id

         FROM s_asset ast,

              s_asset serpt,

              s_asset_om astom,

              s_org_ext cug,

              s_org_ext billact,

              s_org_ext srv,

              s_org_ext sac,

              s_prod_int prod,

              s_addr_per addrfrom,

              s_addr_per addrto,

              s_bu bu,

              (SELECT start_execution_time, last_execution_time

                 FROM odi_last_execution_details

                WHERE scenario ='LOAD_ONE_SIEBEL_TO_WCDS_DS_INVENTORY_PKG') odi_exec

      WHERE bu.NAME                   = 'BT Wholesale Markets'

      AND ast.bu_id                   = bu.row_id

      AND ast.prod_id                 = prod.row_id

      AND ast.owner_accnt_id          = cug.par_row_id

      AND ast.bill_accnt_id           = billact.par_row_id

      AND cug.par_ou_id               = sac.par_row_id

      AND ast.service_point_id        = serpt.row_id(+)

      AND ast.serv_acct_id            = srv.par_row_id(+)

      AND srv.pr_addr_id              = addrfrom.row_id(+)

      AND ast.row_id                  = astom.par_row_id(+)

      AND astom.to_addr_id            = addrto.row_id(+)

    AND ((ast.process_timestamp         >= odi_exec.start_execution_time AND ast.process_timestamp      < odi_exec.last_execution_time)

         OR (serpt.process_timestamp    >= odi_exec.start_execution_time AND serpt.process_timestamp    < odi_exec.last_execution_time)

         OR (astom.process_timestamp    >= odi_exec.start_execution_time AND astom.process_timestamp    < odi_exec.last_execution_time)

         OR (cug.process_timestamp      >= odi_exec.start_execution_time AND cug.process_timestamp      < odi_exec.last_execution_time)

         OR (billact.process_timestamp  >= odi_exec.start_execution_time AND billact.process_timestamp  < odi_exec.last_execution_time)

         OR (srv.process_timestamp      >= odi_exec.start_execution_time AND srv.process_timestamp      < odi_exec.last_execution_time)

         OR (sac.process_timestamp      >= odi_exec.start_execution_time AND sac.process_timestamp       < odi_exec.last_execution_time)

         OR (prod.process_timestamp     >= odi_exec.start_execution_time AND prod.process_timestamp     < odi_exec.last_execution_time)

         OR (addrfrom.process_timestamp >= odi_exec.start_execution_time AND addrfrom.process_timestamp < odi_exec.last_execution_time)

         OR (addrto.process_timestamp   >= odi_exec.start_execution_time AND addrto.process_timestamp   < odi_exec.last_execution_time)

         OR (bu.process_timestamp       >= odi_exec.start_execution_time AND bu.process_timestamp       < odi_exec.last_execution_time)

        );

    here is the explain plan

    -------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                                | Name                       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                         |                            |   313K|   202M|       |  2591K  (1)| 08:38:23 |
    |*  1 |  FILTER                                  |                            |       |       |       |            |          |
    |   2 |   NESTED LOOPS OUTER                     |                            |   313K|   202M|       |  2591K  (1)| 08:38:23 |
    |*  3 |    HASH JOIN RIGHT OUTER                 |                            |    14M|  8029M|  2593M|  2502K  (1)| 08:20:35 |
    |   4 |     TABLE ACCESS FULL                    | S_ADDR_PER                 |    23M|  2320M|       |   103K  (1)| 00:20:37 |
    |*  5 |     HASH JOIN                            |                            |    14M|  6605M|   123M|  1934K  (1)| 06:26:59 |
    |   6 |      TABLE ACCESS FULL                   | S_ORG_EXT                  |  3807K|    79M|       | 16227   (1)| 00:03:15 |
    |*  7 |      HASH JOIN                           |                            |    14M|  6373M|   206M|  1587K  (1)| 05:17:33 |
    |   8 |       TABLE ACCESS FULL                  | S_ORG_EXT                  |  3807K|   163M|       | 16227   (1)| 00:03:15 |
    |*  9 |       HASH JOIN RIGHT OUTER              |                            |    14M|  5737M|  1367M|  1268K  (1)| 04:13:37 |
    |  10 |        TABLE ACCESS FULL                 | S_ASSET_OM                 |    40M|   898M|       | 82957   (2)| 00:16:36 |
    |* 11 |        HASH JOIN RIGHT OUTER             |                            |    14M|  5412M|   410M|   839K  (1)| 02:47:57 |
    |  12 |         TABLE ACCESS FULL                | S_ASSET                    |  9158K|   305M|       | 44762   (1)| 00:08:58 |
    |* 13 |         HASH JOIN RIGHT OUTER            |                            |    14M|  4918M|   167M|   521K  (1)| 01:44:24 |
    |  14 |          TABLE ACCESS FULL               | S_ORG_EXT                  |  3807K|   123M|       | 16227   (1)| 00:03:15 |
    |* 15 |          HASH JOIN                       |                            |    14M|  4437M|   206M|   268K  (1)| 00:53:45 |
    |  16 |           TABLE ACCESS FULL              | S_ORG_EXT                  |  3807K|   163M|       | 16227   (1)| 00:03:15 |
    |* 17 |           HASH JOIN                      |                            |    14M|  3801M|       | 45023   (2)| 00:09:01 |
    |  18 |            TABLE ACCESS FULL             | S_PROD_INT                 |  7234 |   353K|       |    23   (0)| 00:00:01 |
    |* 19 |            HASH JOIN                     |                            |    14M|  3071M|       | 44885   (2)| 00:08:59 |
    |  20 |             MERGE JOIN CARTESIAN         |                            |     2 |   194 |       |     4   (0)| 00:00:01 |
    |  21 |              TABLE ACCESS BY INDEX ROWID | ODI_LAST_EXECUTION_DETAILS |     2 |   112 |       |     2   (0)| 00:00:01 |
    |* 22 |               INDEX RANGE SCAN           | ODI_LAST_EXE_DET_SCENARIO  |     2 |       |       |     1   (0)| 00:00:01 |
    |  23 |              BUFFER SORT                 |                            |     1 |    41 |       |     2   (0)| 00:00:01 |
    |  24 |               TABLE ACCESS BY INDEX ROWID| S_BU                       |     1 |    41 |       |     1   (0)| 00:00:01 |
    |* 25 |                INDEX RANGE SCAN          | S_BU_NAME                  |     1 |       |       |     0   (0)| 00:00:01 |
    |* 26 |             TABLE ACCESS FULL            | S_ASSET                    |  7354K|   855M|       | 44767   (1)| 00:08:58 |
    |  27 |    TABLE ACCESS BY INDEX ROWID           | S_ADDR_PER                 |     1 |   102 |       |     2   (0)| 00:00:01 |
    |* 28 |     INDEX UNIQUE SCAN                    | S_ADDR_PER_P1              |     1 |       |       |     1   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------------------------------------------------
    
  • User_KWXZ9
    User_KWXZ9 Member Posts: 232 Blue Ribbon

    Sorry guys .. the new formatting options are not working for me i guess ...

  • User_KWXZ9
    User_KWXZ9 Member Posts: 232 Blue Ribbon

    yes i have checked with the production where it is mostly using the indexes ... also i have gathered the stats ... after using /*+ rule */ hint the query returns in 2 minutes 30 seconds approx . but i still need to reduce this time ..

    Thanks for your help in advance ,


  • Martin Preiss
    Martin Preiss Member Posts: 2,381 Gold Trophy

    you could try to gather rowsource statistics for the query (for example with the gather_plan_statistics hint). Oracle expects that there is a lot of work to do and if you know (or assume) that there is a better way then it's not unlikely that the CBO calculates with the wrong numbers. So you could check estimated and actual row counts and see if there a big errors in the estimates (beeing a sign of bad statistics or problems in the calculation).

    Regards

    Martin

  • Mark D Powell
    Mark D Powell Member Posts: 5,914 Blue Diamond

    Is this the plan from where the query runs well or poor?  How does it differ from the other plan?  I notice a lot of hash joins which would lead to full table scans over index usage.  Are all the optimizer related parameters for the two databases set the same? (v$sys_optimizer_env)

    -- HTH -- Mark D Powell --

  • Hemant K Chitale
    Hemant K Chitale Member Posts: 15,759 Blue Diamond

    Are you using the RULE hint in both environments ?  If not, check the optimizer parameters in  the two environments with

    select name, value, isdefault
    from v$ses_optimizer_env
    where sid = (select sys_context('USERENV','SID') from dual)
    order by 1
    /
    

    and also check the statistics on the tables, columns and indexes

    and compare the two environments.

This discussion has been closed.