using index hint

User_KWXZ9

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 ,

sb92075

HOW To Make TUNING request

https://forums.oracle.com/forums/thread.jspa?threadID=2174552#9360003

Mark D Powell

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

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

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

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

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

User_KWXZ9

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

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

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

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.