OraFAQ Forum: Performance Tuning » The query was running before 11g upgrade

Home » RDBMS Server » Performance Tuning » The query was running before 11g upgrade (11.2.0.2.2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

The query was running before 11g upgrade [message #526015]

Fri, 07 October 2011 11:59

getzeeshan
Messages: 65
Registered: July 2008

Member

Hi There,

WE have a query which use to run in 10g faster i.e. approx 2 mins now the same query on 11g takes around 16+ mins.

Any idea what could be the issue.

SELECT b.id_point,
       f.code_hotel_type,
       f.code_region,
       f.code_location,
       c.checkout_date,
       b.bonus_date,
       b.id_member,
       b.id_bonus_ext,
       b.id_stay,
       c.discounted_flag                    discount_flag,
       c.duration,
       c.voucher_net_amt,
       b.code_pgm_control,
       d.code_pgm_ctrl_cat,
       d.code_pgm_ctrl_type,
       b.code_billing_type,
       b.code_hotel,
       f.code_country                       code_hotel_country,
       e.code_country                       code_mem_country,
       b.code_program_al,
       b.code_program_car,
       b.code_product,
       b.code_promotion,
       g.code_tier,
       b.code_plan,
       Nvl(b.code_source, ' '),
       b.code_settlement,
       b.stmt_date,
       b.invoice_date,
       b.day_of_week,
       b.code_adj,
       b.code_algorithm,
       'HNF'                                code_program,
       b.num_points,
       b.amt,
       Decode(b.code_adj, NULL, +1,
                          'A ', +1,
                          -1)               row_count,
       b.audit_time,
       b.code_org,
       d.code_pgm_mkt_type,
       NULL                                 code_sub_region,
       CASE
         WHEN c.userid IN ( 'PROD', 'ACTLOAD', 'MBRLOAD' ) THEN 'Y'
         ELSE 'N'
       END                                  ind_stay_load,
       Add_months(Trunc(SYSDATE, 'mm'), -1) rpt_date,
       b.estmt_date,
       b.gpm_id_stay
FROM   jstewart.err a,
       t_act_point b,
       t_act_stay c,
       t_pgm_control d,
       t_mem_base e,
       t_pr_hotel f,
       t_elig_enroll g
WHERE  b.id_point = a.id_point
       AND c.id_stay = b.id_stay
       AND d.code_pgm_control = b.code_pgm_control
       AND d.code_pgm_control = b.code_pgm_control
       AND e.id_member = b.id_member
       AND f.code_hotel(+) = c.code_hotel
       AND g.id_member = b.id_member
       AND g.status = 'A';

======

The Explain plan is

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Inst   |IN-OUT|
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |               |  3095K|  1112M|       |   816K  (5)| 02:06:56 |        |      |
|*  1 |  HASH JOIN              |               |  3095K|  1112M|       |   816K  (5)| 02:06:56 |        |      |
|   2 |   REMOTE                | T_PGM_CONTROL | 55725 |  1306K|       |   141   (8)| 00:00:02 |   HHP3 | R->S |
|*  3 |   HASH JOIN RIGHT OUTER |               |  3095K|  1041M|       |   816K  (5)| 02:06:54 |        |      |
|   4 |    MAT_VIEW ACCESS FULL | T_PR_HOTEL    |  4886 |   100K|       |    16   (0)| 00:00:01 |        |      |
|*  5 |    HASH JOIN            |               |  3095K|   980M|   799M|   816K  (5)| 02:06:53 |        |      |
|*  6 |     HASH JOIN           |               |  3047K|   764M|   367M|   180K  (3)| 00:28:04 |        |      |
|   7 |      REMOTE             | T_MEM_BASE    |    12M|   220M|       | 34519   (5)| 00:05:23 |   HHP3 | R->S |
|*  8 |      HASH JOIN          |               |  3047K|   712M|    48M|   123K  (2)| 00:19:11 |        |      |
|   9 |       REMOTE            | T_ELIG_ENROLL |  1639K|    29M|       | 12285   (1)| 00:01:55 |   HHP3 | R->S |
|  10 |       NESTED LOOPS      |               |    24M|  5208M|       |   323K(100)| 00:50:15 |        |      |
|  11 |        TABLE ACCESS FULL| ERR           |    57 |   399 |       |     2   (0)| 00:00:01 |        |      |
|  12 |        REMOTE           | T_ACT_POINT   |   423K|    88M|       |     2   (0)| 00:00:01 |   HHP3 | R->S |
|  13 |     VIEW                | T_ACT_STAY    |   186M|    12G|       |   325K  (9)| 00:50:40 |        |      |
|  14 |      REMOTE             | T_ACT_STAY    |   186M|    12G|       |   325K  (9)| 00:50:40 |   HHP3 | R->S |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("CODE_PGM_CONTROL"="CODE_PGM_CONTROL")
   3 - access("F"."CODE_HOTEL"(+)="C"."CODE_HOTEL")
   5 - access("C"."ID_STAY"="ID_STAY")
   6 - access("ID_MEMBER"="ID_MEMBER")
   8 - access("ID_MEMBER"="ID_MEMBER")

Remote SQL Information (identified by operation id):
----------------------------------------------------

   2 - SELECT "CODE_PGM_CONTROL","CODE_PGM_CTRL_CAT","CODE_PGM_CTRL_TYPE","CODE_PGM_MKT_TYPE" FROM
       "HH"."T_PGM_CONTROL" "T_PGM_CONTROL" (accessing 'HHP3' )

   7 - SELECT "ID_MEMBER","CODE_COUNTRY" FROM "HH"."T_MEM_BASE" "T_MEM_BASE" (accessing 'HHP3' )

   9 - SELECT "ID_MEMBER","CODE_TIER","STATUS" FROM "HH"."T_ELIG_ENROLL" "T_ELIG_ENROLL" WHERE
       "STATUS"='A' (accessing 'HHP3' )

  12 - SELECT "ID_POINT","CODE_PGM_CONTROL","ID_MEMBER","CODE_BILLING_TYPE","CODE_ORG","CODE_HOTEL","ID_S
        TAY","BONUS_DATE","ID_BONUS_EXT","NUM_POINTS","CODE_ALGORITHM","AMT","CODE_PROGRAM_AL","CODE_PROGRAM_CAR"
        ,"CODE_PRODUCT","CODE_PROMOTION","CODE_PLAN","CODE_SOURCE","DAY_OF_WEEK","CODE_SETTLEMENT","STMT_DATE","I
        NVOICE_DATE","CODE_ADJ","AUDIT_TIME","ESTMT_DATE","GPM_ID_STAY" FROM "HH"."T_ACT_POINT" "T_ACT_POINT"
        WHERE "ID_POINT"=:1 (accessing 'HHP3' )

  14 - SELECT "ID_STAY","CHECKOUT_DATE","CODE_HOTEL","DURATION","VOUCHER_NET_AMT","DISCOUNTED_FLAG","USER
        ID" FROM "HH"."T_ACT_STAY" "T_ACT_STAY" (accessing 'HHP3' )


51 rows selected.

Please can anyone help me on this.

Regards
Zeeshan

* code formatted by BlackSwan

[Updated on: Fri, 07 October 2011 12:13] by Moderator

Report message to a moderator

Re: The query was running before 11g upgrade [message #526021 is a reply to message #526015]

Fri, 07 October 2011 12:24

kumarl27
Messages: 1
Registered: October 2011

Junior Member

I would check first the table stats.

--Kumar

Report message to a moderator

Re: The query was running before 11g upgrade [message #526024 is a reply to message #526021]

Fri, 07 October 2011 12:29

getzeeshan
Messages: 65
Registered: July 2008

Member

This query runs a database P2 on views which --> are referring the Materialized views on a remote database P3.

To put it precisely the data is fetched from P3 to --> P2 via views .

The source mviews are analyzed and their respective indexes are analyzed up to date. But still these fail to produce the results in time.

--Zee

Report message to a moderator

Re: The query was running before 11g upgrade [message #526038 is a reply to message #526015]

Fri, 07 October 2011 13:39

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

Do you still have an execution plan in 10g?

Could you please supply some additional information:

1. make the following settings

set linesize 1000
set pagesize 1000

2. then run

alter session set statistics_level=all;

3. then run your sql,
4. after that run the following select:

select plan_table_output from table ( sys.dbms_xplan.display_cursor( '','','ADVANCED ALLSTATS LAST'));

5. upload the last formatted output.

Report message to a moderator

Re: The query was running before 11g upgrade [message #526046 is a reply to message #526038]

Fri, 07 October 2011 14:21

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

If the output of your query is really too big and it is therefore problematic to fulfill the suggested actions, then I can offer another method for getting practically the same information.

Report message to a moderator

Re: The query was running before 11g upgrade [message #526056 is a reply to message #526046]

Fri, 07 October 2011 15:07

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>WE have a query which use to run in 10g faster i.e. approx 2 mins now the same query on 11g takes around 16+ mins.
>This query runs a database P2 on views which --> are referring the Materialized views on a remote database P3.

which DB was upgraded?
P2?
P3?
both?

Report message to a moderator

Re: The query was running before 11g upgrade [message #526058 is a reply to message #526038]

Fri, 07 October 2011 15:17

getzeeshan
Messages: 65
Registered: July 2008

Member



15:16:06 SQL> select plan_table_output from table ( sys.dbms_xplan.display_cursor( '','','ADVANCED ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5ftfuczgu15nm, child number 1
-------------------------------------
select plan_table_output from table ( sys.dbms_xplan.display_cursor(
'','','ADVANCED ALLSTATS LAST'))

Plan hash value: 3602215112

--------------------------------------------------------------------------------------------------------------------------------------------
-----------

| Id  | Operation                         | Name           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers
 | Reads  |

--------------------------------------------------------------------------------------------------------------------------------------------
-----------

|   0 | SELECT STATEMENT                  |                |      1 |        |       |     9 (100)|          |    165 |00:00:00.49 |    3805
 |     22 |

|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY_CURSOR |      1 |     44 |    88 |     9   (0)| 00:00:01 |    165 |00:00:00.49 |    3805
 |     22 |

--------------------------------------------------------------------------------------------------------------------------------------------
-----------


Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$F5BB74E1 / KOKBF$@SEL$2

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
      DB_VERSION('11.2.0.2')
      ALL_ROWS
      FORCE_XML_QUERY_REWRITE
      XML_DML_RWT_STMT
      XMLINDEX_REWRITE
      XMLINDEX_REWRITE_IN_SELECT
      NO_COST_XML_QUERY_REWRITE
      OUTLINE_LEAF(@"SEL$F5BB74E1")
      MERGE(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      FULL(@"SEL$F5BB74E1" "KOKBF$"@"SEL$2")
      END_OUTLINE_DATA
  */

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - VALUE(A0)[300]

Note
-----
   - cardinality feedback used for this statement


50 rows selected.

Elapsed: 00:00:00.11
15:16:13 SQL> set lines 1000
15:16:31 SQL>

15:16:35 SQL> /

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5ftfuczgu15nm, child number 2
-------------------------------------
select plan_table_output from table ( sys.dbms_xplan.display_cursor(
'','','ADVANCED ALLSTATS LAST'))

Plan hash value: 3602215112

----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                |      1 |        |       |     9 (100)|          |     50 |00:00:00.09 |     700 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY_CURSOR |      1 |   8168 | 16336 |     9   (0)| 00:00:01 |     50 |00:00:00.09 |     700 |
----------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$F5BB74E1 / KOKBF$@SEL$2

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
      DB_VERSION('11.2.0.2')
      ALL_ROWS
      FORCE_XML_QUERY_REWRITE
      XML_DML_RWT_STMT
      XMLINDEX_REWRITE
      XMLINDEX_REWRITE_IN_SELECT
      NO_COST_XML_QUERY_REWRITE
      OUTLINE_LEAF(@"SEL$F5BB74E1")
      MERGE(@"SEL$2")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      FULL(@"SEL$F5BB74E1" "KOKBF$"@"SEL$2")
      END_OUTLINE_DATA
  */

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - VALUE(A0)[300]

Note
-----
   - SQL plan baseline SQL_PLAN_4vazvryk7sstb8df50001 used for this statement


50 rows selected.

Elapsed: 00:00:00.07

Report message to a moderator

Re: The query was running before 11g upgrade [message #526059 is a reply to message #526058]

Fri, 07 October 2011 15:20

getzeeshan
Messages: 65
Registered: July 2008

Member

Sorry I believe the whole content did not get pasted earlier.



15:14:46 SQL> 15:14:46 SQL> select plan_table_output from table ( sys.dbms_xplan.display_cursor( '','','ADVANCED ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
 ------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------
SQL_ID  4q7njcqna2631, child number 0
-------------------------------------
select b.id_point, f.code_hotel_type, f.code_region, f.code_location,
c.checkout_date, b.bonus_date, b.id_member, b.id_bonus_ext, b.id_stay,
c.discounted_flag discount_flag, c.duration, c.voucher_net_amt,
b.code_pgm_control, d.code_pgm_ctrl_cat, d.code_pgm_ctrl_type,
b.code_billing_type, b.code_hotel, f.code_country code_hotel_country,
e.code_country code_mem_country, b.code_program_al, b.code_program_car,
b.code_product, b.code_promotion, g.code_tier, b.code_plan,
nvl(b.code_source,' '), b.code_settlement, b.stmt_date, b.invoice_date,
b.day_of_week, b.code_adj, b.code_algorithm, 'HNF' code_program,
b.num_points, b.amt, decode(b.code_adj,NULL,+1,'A  ', +1, -1)
row_count, b.audit_time, b.code_org, d.code_pgm_mkt_type, NULL
code_sub_region, case when c.userid in ('PROD','ACTLOAD','MBRLOAD')
then 'Y' else 'N' end ind_stay_load, add_months(trunc(sysdate,'mm'),-1)
rpt_date, b.estmt_date, b.gpm_id_stay from jstewart.err a, t_act_point
b, t_act_stay c, t_pgm_control d, t_mem_base e, t_pr_hote

Plan hash value: 3108081317

 ------------------------------------------------------------------------------------------------------------------------------------- -----------------------------------------------------------------------------
| Id  | Operation               | Name          | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | Inst   |IN-OUT| A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
 ------------------------------------------------------------------------------------------------------------------------------------- -----------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |               |      1 |        |       |       |   816K(100)|          |        |      |     61 |00:08:34.69 |     187 |  49290 |  60791 |       |       |          |         |
|*  1 |  HASH JOIN              |               |      1 |   3068K|  1103M|       |   816K  (5)| 02:06:54 |        |      |     61 |00:08:34.69 |     187 |  49290 |  60791 |  3398K|  1295K| 4559K (0)|         |
|   2 |   REMOTE                | T_PGM_CONTROL |      1 |  56015 |  1312K|       |   141   (8)| 00:00:02 |   HHP3 | R->S |  55777 |00:00:00.44 |       0 |      0 |      0 |       |       |          |         |
|*  3 |   HASH JOIN RIGHT OUTER |               |      1 |   3068K|  1033M|       |   815K  (5)| 02:06:52 |        |      |     61 |00:08:34.68 |     187 |  49290 |  60791 |   985K|   985K| 1490K (0)|         |
|   4 |    MAT_VIEW ACCESS FULL | T_PR_HOTEL    |      1 |   4886 |   100K|       |    16   (0)| 00:00:01 |        |      |   4917 |00:00:00.01 |     185 |      0 |      0 |       |       |          |         |
|*  5 |    HASH JOIN            |               |      1 |   3068K|   971M|   792M|   815K  (5)| 02:06:51 |        |      |     61 |00:08:34.70 |       2 |  49290 |  60791 |   743K|   743K|   14M (0)|         |
|*  6 |     HASH JOIN           |               |      1 |   3021K|   757M|   367M|   180K  (3)| 00:28:04 |        |      |     61 |00:01:47.10 |       2 |  49290 |  60791 |   447M|    24M|   27M (1)|     271K|
|   7 |      REMOTE             | T_MEM_BASE    |      1 |     12M|   220M|       | 34519   (5)| 00:05:23 |   HHP3 | R->S |     12M|00:00:47.48 |       0 |      0 |      0 |       |       |          |         |
|*  8 |      HASH JOIN          |               |      1 |   3021K|   705M|    48M|   123K  (2)| 00:19:12 |        |      |     61 |00:00:39.04 |       2 |  27280 |  27280 |   386M|    12M| 8404K (1)|     220K|
|   9 |       REMOTE            | T_ELIG_ENROLL |      1 |   1625K|    29M|       | 12365   (1)| 00:01:56 |   HHP3 | R->S |     12M|00:00:24.42 |       0 |      0 |      0 |       |       |          |         |
|  10 |       NESTED LOOPS      |               |      1 |     24M|  5208M|       |   323K(100)| 00:50:15 |        |      |     61 |00:00:00.59 |       2 |      0 |      0 |       |       |          |         |
|  11 |        TABLE ACCESS FULL| ERR           |      1 |     57 |   399 |       |     2   (0)| 00:00:01 |        |      |     61 |00:00:00.01 |       2 |      0 |      0 |       |       |          |         |
|  12 |        REMOTE           | T_ACT_POINT   |     61 |    423K|    88M|       |     2   (0)| 00:00:01 |   HHP3 | R->S |     61 |00:00:00.58 |       0 |      0 |      0 |       |       |          |         |
|  13 |     VIEW                | T_ACT_STAY    |      1 |    186M|    12G|       |   325K  (9)| 00:50:40 |        |      |    190M|00:10:59.77 |       0 |      0 |      0 |       |       |          |         |
|  14 |      REMOTE             | T_ACT_STAY    |      1 |    186M|    12G|       |   325K  (9)| 00:50:40 |   HHP3 | R->S |    190M|00:10:03.65 |       0 |      0 |      0 |       |       |          |         |
 ------------------------------------------------------------------------------------------------------------------------------------- -----------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$BEF5D90F
   2 - SEL$BEF5D90F / T_PGM_CONTROL@SEL$4
   4 - SEL$BEF5D90F / F@SEL$1
   7 - SEL$BEF5D90F / T_MEM_BASE@SEL$5
   9 - SEL$BEF5D90F / T_ELIG_ENROLL@SEL$6
  11 - SEL$BEF5D90F / A@SEL$1
  12 - SEL$BEF5D90F / T_ACT_POINT@SEL$2
  13 - SEL$3        / C@SEL$1
  14 - SEL$3        / T_ACT_STAY@SEL$3

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
      DB_VERSION('11.2.0.2')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SEL$BEF5D90F")
      MERGE(@"SEL$2")
      MERGE(@"SEL$4")
      MERGE(@"SEL$5")
      MERGE(@"SEL$6")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$4")
      OUTLINE(@"SEL$5")
      OUTLINE(@"SEL$6")
      FULL(@"SEL$BEF5D90F" "A"@"SEL$1")
      FULL(@"SEL$BEF5D90F" "T_ACT_POINT"@"SEL$2")
      FULL(@"SEL$BEF5D90F" "T_ELIG_ENROLL"@"SEL$6")
      FULL(@"SEL$BEF5D90F" "T_MEM_BASE"@"SEL$5")
      NO_ACCESS(@"SEL$BEF5D90F" "C"@"SEL$1")
      FULL(@"SEL$BEF5D90F" "F"@"SEL$1")
      FULL(@"SEL$BEF5D90F" "T_PGM_CONTROL"@"SEL$4")
      LEADING(@"SEL$BEF5D90F" "A"@"SEL$1" "T_ACT_POINT"@"SEL$2" "T_ELIG_ENROLL"@"SEL$6" "T_MEM_BASE"@"SEL$5" "C"@"SEL$1" "F"@"SEL$1" "T_PGM_CONTROL"@"SEL$4")
      USE_NL(@"SEL$BEF5D90F" "T_ACT_POINT"@"SEL$2")
      USE_HASH(@"SEL$BEF5D90F" "T_ELIG_ENROLL"@"SEL$6")
      USE_HASH(@"SEL$BEF5D90F" "T_MEM_BASE"@"SEL$5")
      USE_HASH(@"SEL$BEF5D90F" "C"@"SEL$1")
      USE_HASH(@"SEL$BEF5D90F" "F"@"SEL$1")
      USE_HASH(@"SEL$BEF5D90F" "T_PGM_CONTROL"@"SEL$4")
      SWAP_JOIN_INPUTS(@"SEL$BEF5D90F" "T_ELIG_ENROLL"@"SEL$6")
      SWAP_JOIN_INPUTS(@"SEL$BEF5D90F" "T_MEM_BASE"@"SEL$5")
      SWAP_JOIN_INPUTS(@"SEL$BEF5D90F" "F"@"SEL$1")
      SWAP_JOIN_INPUTS(@"SEL$BEF5D90F" "T_PGM_CONTROL"@"SEL$4")
      FULL(@"SEL$3" "T_ACT_STAY"@"SEL$3")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
 ------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------

   1 - access("CODE_PGM_CONTROL"="CODE_PGM_CONTROL")
   3 - access("F"."CODE_HOTEL"="C"."CODE_HOTEL")
   5 - access("C"."ID_STAY"="ID_STAY")
   6 - access("ID_MEMBER"="ID_MEMBER")
   8 - access("ID_MEMBER"="ID_MEMBER")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=1) "CODE_PGM_CTRL_CAT"[CHARACTER,2], "CODE_PGM_CTRL_TYPE"[CHARACTER,2], "CODE_PGM_MKT_TYPE"[CHARACTER,6], "F"."CODE_COUNTRY"[CHARACTER,3], "F"."CODE_REGION"[CHARACTER,2],
       "F"."CODE_LOCATION"[CHARACTER,2], "F"."CODE_HOTEL_TYPE"[CHARACTER,2], "ID_STAY"[NUMBER,22], "ID_MEMBER"[NUMBER,22], "CODE_COUNTRY"[CHARACTER,3], "ESTMT_DATE"[DATE,7], "CODE_TIER"[CHARACTER,1],
       "ID_POINT"[NUMBER,22], "CODE_PGM_CONTROL"[CHARACTER,6], "GPM_ID_STAY"[NUMBER,22], "CODE_BILLING_TYPE"[CHARACTER,1], "CODE_ORG"[CHARACTER,6], "CODE_HOTEL"[CHARACTER,7], "AUDIT_TIME"[DATE,7],
       "BONUS_DATE"[DATE,7], "ID_BONUS_EXT"[NUMBER,22], "NUM_POINTS"[NUMBER,22], "CODE_ALGORITHM"[CHARACTER,1], "AMT"[NUMBER,22], "CODE_PROGRAM_AL"[CHARACTER,3], "CODE_PROGRAM_CAR"[CHARACTER,3],
       "CODE_PRODUCT"[CHARACTER,4], "CODE_PROMOTION"[CHARACTER,6], "CODE_PLAN"[CHARACTER,6], "CODE_SOURCE"[CHARACTER,5], "DAY_OF_WEEK"[CHARACTER,1], "CODE_SETTLEMENT"[CHARACTER,3], "STMT_DATE"[DATE,7],
       "INVOICE_DATE"[DATE,7], "CODE_ADJ"[CHARACTER,3], "C"."USERID"[VARCHAR2,14], "C"."CHECKOUT_DATE"[DATE,7], "C"."DURATION"[NUMBER,22], "C"."VOUCHER_NET_AMT"[NUMBER,22], "C"."DISCOUNTED_FLAG"[CHARACTER,1]
   2 - "CODE_PGM_CONTROL"[CHARACTER,6], "CODE_PGM_CTRL_CAT"[CHARACTER,2], "CODE_PGM_CTRL_TYPE"[CHARACTER,2], "CODE_PGM_MKT_TYPE"[CHARACTER,6]
   3 - (#keys=1) "F"."CODE_COUNTRY"[CHARACTER,3], "F"."CODE_REGION"[CHARACTER,2], "F"."CODE_LOCATION"[CHARACTER,2], "F"."CODE_HOTEL_TYPE"[CHARACTER,2], "ID_STAY"[NUMBER,22], "ID_MEMBER"[NUMBER,22],
       "CODE_COUNTRY"[CHARACTER,3], "ESTMT_DATE"[DATE,7], "CODE_TIER"[CHARACTER,1], "ID_POINT"[NUMBER,22], "CODE_PGM_CONTROL"[CHARACTER,6], "GPM_ID_STAY"[NUMBER,22], "CODE_BILLING_TYPE"[CHARACTER,1],
       "CODE_ORG"[CHARACTER,6], "CODE_HOTEL"[CHARACTER,7], "AUDIT_TIME"[DATE,7], "BONUS_DATE"[DATE,7], "ID_BONUS_EXT"[NUMBER,22], "NUM_POINTS"[NUMBER,22], "CODE_ALGORITHM"[CHARACTER,1], "AMT"[NUMBER,22],
       "CODE_PROGRAM_AL"[CHARACTER,3], "CODE_PROGRAM_CAR"[CHARACTER,3], "CODE_PRODUCT"[CHARACTER,4], "CODE_PROMOTION"[CHARACTER,6], "CODE_PLAN"[CHARACTER,6], "CODE_SOURCE"[CHARACTER,5],
       "DAY_OF_WEEK"[CHARACTER,1], "CODE_SETTLEMENT"[CHARACTER,3], "STMT_DATE"[DATE,7], "INVOICE_DATE"[DATE,7], "CODE_ADJ"[CHARACTER,3], "C"."USERID"[VARCHAR2,14], "C"."CHECKOUT_DATE"[DATE,7],
       "C"."DURATION"[NUMBER,22], "C"."VOUCHER_NET_AMT"[NUMBER,22], "C"."DISCOUNTED_FLAG"[CHARACTER,1]
   4 - "F"."CODE_HOTEL"[CHARACTER,7], "F"."CODE_COUNTRY"[CHARACTER,3], "F"."CODE_REGION"[CHARACTER,2], "F"."CODE_LOCATION"[CHARACTER,2], "F"."CODE_HOTEL_TYPE"[CHARACTER,2]
   5 - (#keys=1) "ID_STAY"[NUMBER,22], "ID_MEMBER"[NUMBER,22], "CODE_COUNTRY"[CHARACTER,3], "ESTMT_DATE"[DATE,7], "CODE_TIER"[CHARACTER,1], "ID_POINT"[NUMBER,22], "CODE_PGM_CONTROL"[CHARACTER,6],
       "GPM_ID_STAY"[NUMBER,22], "CODE_BILLING_TYPE"[CHARACTER,1], "CODE_ORG"[CHARACTER,6], "CODE_HOTEL"[CHARACTER,7], "AUDIT_TIME"[DATE,7], "BONUS_DATE"[DATE,7], "ID_BONUS_EXT"[NUMBER,22],
       "NUM_POINTS"[NUMBER,22], "CODE_ALGORITHM"[CHARACTER,1], "AMT"[NUMBER,22], "CODE_PROGRAM_AL"[CHARACTER,3], "CODE_PROGRAM_CAR"[CHARACTER,3], "CODE_PRODUCT"[CHARACTER,4], "CODE_PROMOTION"[CHARACTER,6],
       "CODE_PLAN"[CHARACTER,6], "CODE_SOURCE"[CHARACTER,5], "DAY_OF_WEEK"[CHARACTER,1], "CODE_SETTLEMENT"[CHARACTER,3], "STMT_DATE"[DATE,7], "INVOICE_DATE"[DATE,7], "CODE_ADJ"[CHARACTER,3],
       "C"."USERID"[VARCHAR2,14], "C"."CHECKOUT_DATE"[DATE,7], "C"."CODE_HOTEL"[CHARACTER,7], "C"."DURATION"[NUMBER,22], "C"."VOUCHER_NET_AMT"[NUMBER,22], "C"."DISCOUNTED_FLAG"[CHARACTER,1]
   6 - (#keys=1) "ID_MEMBER"[NUMBER,22], "CODE_COUNTRY"[CHARACTER,3], "ESTMT_DATE"[DATE,7], "CODE_TIER"[CHARACTER,1], "ID_POINT"[NUMBER,22], "CODE_PGM_CONTROL"[CHARACTER,6], "GPM_ID_STAY"[NUMBER,22],
       "CODE_BILLING_TYPE"[CHARACTER,1], "CODE_ORG"[CHARACTER,6], "CODE_HOTEL"[CHARACTER,7], "ID_STAY"[NUMBER,22], "BONUS_DATE"[DATE,7], "ID_BONUS_EXT"[NUMBER,22], "NUM_POINTS"[NUMBER,22],
       "CODE_ALGORITHM"[CHARACTER,1], "AMT"[NUMBER,22], "CODE_PROGRAM_AL"[CHARACTER,3], "CODE_PROGRAM_CAR"[CHARACTER,3], "CODE_PRODUCT"[CHARACTER,4], "CODE_PROMOTION"[CHARACTER,6], "CODE_PLAN"[CHARACTER,6],
       "CODE_SOURCE"[CHARACTER,5], "DAY_OF_WEEK"[CHARACTER,1], "CODE_SETTLEMENT"[CHARACTER,3], "STMT_DATE"[DATE,7], "INVOICE_DATE"[DATE,7], "CODE_ADJ"[CHARACTER,3], "AUDIT_TIME"[DATE,7]
   7 - "ID_MEMBER"[NUMBER,22], "CODE_COUNTRY"[CHARACTER,3]
   8 - (#keys=1) "ID_MEMBER"[NUMBER,22], "CODE_TIER"[CHARACTER,1], "ID_POINT"[NUMBER,22], "CODE_PGM_CONTROL"[CHARACTER,6], "GPM_ID_STAY"[NUMBER,22], "CODE_BILLING_TYPE"[CHARACTER,1],
       "CODE_ORG"[CHARACTER,6], "CODE_HOTEL"[CHARACTER,7], "ID_STAY"[NUMBER,22], "BONUS_DATE"[DATE,7], "ID_BONUS_EXT"[NUMBER,22], "NUM_POINTS"[NUMBER,22], "CODE_ALGORITHM"[CHARACTER,1], "AMT"[NUMBER,22],
       "CODE_PROGRAM_AL"[CHARACTER,3], "CODE_PROGRAM_CAR"[CHARACTER,3], "CODE_PRODUCT"[CHARACTER,4], "CODE_PROMOTION"[CHARACTER,6], "CODE_PLAN"[CHARACTER,6], "CODE_SOURCE"[CHARACTER,5],
       "DAY_OF_WEEK"[CHARACTER,1], "CODE_SETTLEMENT"[CHARACTER,3], "STMT_DATE"[DATE,7], "INVOICE_DATE"[DATE,7], "CODE_ADJ"[CHARACTER,3], "AUDIT_TIME"[DATE,7], "ESTMT_DATE"[DATE,7]
   9 - "ID_MEMBER"[NUMBER,22], "CODE_TIER"[CHARACTER,1], "STATUS"[CHARACTER,1]
  10 - "ID_POINT"[NUMBER,22], "CODE_PGM_CONTROL"[CHARACTER,6], "ID_MEMBER"[NUMBER,22], "CODE_BILLING_TYPE"[CHARACTER,1], "CODE_ORG"[CHARACTER,6], "CODE_HOTEL"[CHARACTER,7], "ID_STAY"[NUMBER,22],
       "BONUS_DATE"[DATE,7], "ID_BONUS_EXT"[NUMBER,22], "NUM_POINTS"[NUMBER,22], "CODE_ALGORITHM"[CHARACTER,1], "AMT"[NUMBER,22], "CODE_PROGRAM_AL"[CHARACTER,3], "CODE_PROGRAM_CAR"[CHARACTER,3],
       "CODE_PRODUCT"[CHARACTER,4], "CODE_PROMOTION"[CHARACTER,6], "CODE_PLAN"[CHARACTER,6], "CODE_SOURCE"[CHARACTER,5], "DAY_OF_WEEK"[CHARACTER,1], "CODE_SETTLEMENT"[CHARACTER,3], "STMT_DATE"[DATE,7],
       "INVOICE_DATE"[DATE,7], "CODE_ADJ"[CHARACTER,3], "AUDIT_TIME"[DATE,7], "ESTMT_DATE"[DATE,7], "GPM_ID_STAY"[NUMBER,22]
  11 - "A"."ID_POINT"[NUMBER,22]
  12 - "ID_POINT"[NUMBER,22], "CODE_PGM_CONTROL"[CHARACTER,6], "ID_MEMBER"[NUMBER,22], "CODE_BILLING_TYPE"[CHARACTER,1], "CODE_ORG"[CHARACTER,6], "CODE_HOTEL"[CHARACTER,7], "ID_STAY"[NUMBER,22],
       "BONUS_DATE"[DATE,7], "ID_BONUS_EXT"[NUMBER,22], "NUM_POINTS"[NUMBER,22], "CODE_ALGORITHM"[CHARACTER,1], "AMT"[NUMBER,22], "CODE_PROGRAM_AL"[CHARACTER,3], "CODE_PROGRAM_CAR"[CHARACTER,3],
       "CODE_PRODUCT"[CHARACTER,4], "CODE_PROMOTION"[CHARACTER,6], "CODE_PLAN"[CHARACTER,6], "CODE_SOURCE"[CHARACTER,5], "DAY_OF_WEEK"[CHARACTER,1], "CODE_SETTLEMENT"[CHARACTER,3], "STMT_DATE"[DATE,7],
       "INVOICE_DATE"[DATE,7], "CODE_ADJ"[CHARACTER,3], "AUDIT_TIME"[DATE,7], "ESTMT_DATE"[DATE,7], "GPM_ID_STAY"[NUMBER,22]
  13 - "C"."ID_STAY"[NUMBER,22], "C"."CHECKOUT_DATE"[DATE,7], "C"."CODE_HOTEL"[CHARACTER,7], "C"."DURATION"[NUMBER,22], "C"."VOUCHER_NET_AMT"[NUMBER,22], "C"."DISCOUNTED_FLAG"[CHARACTER,1],
       "C"."USERID"[VARCHAR2,14]
  14 - "ID_STAY"[NUMBER,22], "CHECKOUT_DATE"[DATE,7], "CODE_HOTEL"[CHARACTER,7], "DURATION"[NUMBER,22], "VOUCHER_NET_AMT"[NUMBER,22], "DISCOUNTED_FLAG"[CHARACTER,1], "USERID"[VARCHAR2,14]

Remote SQL Information (identified by operation id):
----------------------------------------------------

   2 - SELECT "CODE_PGM_CONTROL","CODE_PGM_CTRL_CAT","CODE_PGM_CTRL_TYPE","CODE_PGM_MKT_TYPE" FROM "HH"."T_PGM_CONTROL" "T_PGM_CONTROL" (accessing 'HHP3' )

   7 - SELECT "ID_MEMBER","CODE_COUNTRY" FROM "HH"."T_MEM_BASE" "T_MEM_BASE" (accessing 'HHP3' )

   9 - SELECT "ID_MEMBER","CODE_TIER","STATUS" FROM "HH"."T_ELIG_ENROLL" "T_ELIG_ENROLL" WHERE "STATUS"='A' (accessing 'HHP3' )

  12 - SELECT "ID_POINT","CODE_PGM_CONTROL","ID_MEMBER","CODE_BILLING_TYPE","CODE_ORG","CODE_HOTEL","ID_STAY","BONUS_DATE","ID_BONUS_EXT ","NUM_POINTS","CODE_ALGORITHM","AMT","CODE_PROGRAM_AL","CODE_PROG
        RAM_CAR","CODE_PRODUCT","CODE_PROMOTION","CODE_PLAN","CODE_SOURCE","DAY_OF_WEEK","CODE_SETTLEMENT","STMT_DATE","INVOICE_DATE ","CODE_ADJ","AUDIT_TIME","ESTMT_DATE","GPM_ID_STAY" FROM "HH"."T_ACT_POINT"
        "T_ACT_POINT" WHERE "ID_POINT"=:1 (accessing 'HHP3' )

  14 - SELECT "ID_STAY","CHECKOUT_DATE","CODE_HOTEL","DURATION","VOUCHER_NET_AMT","DISCOUNTED_FLAG","USERID" FROM "HH"."T_ACT_STAY" "T_ACT_STAY" (accessing 'HHP3' )



165 rows selected.
============================

--> and the 10g plan is not available.
--> do you think setting the Optimzer_feature_enable to 10.2.0.4 will give the 10g plan?
--> if thatz ok then I can set the parameter feature_enale to 10.2.0.4 and get the plan

Please advice...

Regards
Zeeshan

[Zeeshan, I've added the closing [/code] tag that you had missed out, so this is now readable.
Please check your posts in future with the "Preview Message" button. JW.]

[Updated on: Sat, 08 October 2011 10:46] by Moderator

Report message to a moderator

Re: The query was running before 11g upgrade [message #526060 is a reply to message #526059]

Fri, 07 October 2011 15:23

getzeeshan
Messages: 65
Registered: July 2008

Member

Both P2 & P3 databases were upgraded.

Report message to a moderator

Re: The query was running before 11g upgrade [message #526114 is a reply to message #526059]

Sat, 08 October 2011 08:07

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

Could you please format your output, as I asked you.

Report message to a moderator

Re: The query was running before 11g upgrade [message #526124 is a reply to message #526059]

Sat, 08 October 2011 10:15

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

Regarding OFE=10.2.0.4. We don't know the reason of execution plan changing, so I cannot say, if it can help or not. You can test this, if you want.

Report message to a moderator

Re: The query was running before 11g upgrade [message #526125 is a reply to message #526124]

Sat, 08 October 2011 10:19

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Just curious, if for both DBs you set COMPATIBLE back to V10? (whatever) prior to upgrade, what happens to performance?

Report message to a moderator

Re: The query was running before 11g upgrade [message #526129 is a reply to message #526125]

Sat, 08 October 2011 11:13

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

I think you might have a data problem. Look at steps 10, 11, and 12 of your execution plan.
These are joining jstewert.err and t_act_point, on id_point.
According to the stats, err has 57 rows and t_act_point has 423000 rows.
Now, if this were a cartesian join, you would expect to get 24111000 rows back, which is exactly what you are getting.
But you will also get this result with an inner join if the join column has the same value in all the rows in both tables.
Should that column have unique values?
Or do you actually want a cartesian product there?

Report message to a moderator

Re: The query was running before 11g upgrade [message #526134 is a reply to message #526125]

Sat, 08 October 2011 14:44

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

OFE isn't the same as COMPATIBLE. You cannot go back to 10g with COMPATIBLE. COMPATIBLE is used for downgrade purposes.

Report message to a moderator

Re: The query was running before 11g upgrade [message #526135 is a reply to message #526059]

Sat, 08 October 2011 14:50

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

Could you please send information about indexes on your tables (especially on T_ACT_STAY).

Report message to a moderator

Re: The query was running before 11g upgrade [message #526323 is a reply to message #526135]

Mon, 10 October 2011 10:04

getzeeshan
Messages: 65
Registered: July 2008

Member

HI Team,
Sorry could not reply during weekend.

Setting up OFE to 10.2.. has not changed the plan it shows the same execution plan.

Indexes on
T_Act_point are on

Index Name	Unique	Column
IE_ACT_POINT_2	N	AUDIT_TIME	
IE_ACT_POINT_4	N	ID_STAY	Asc	
IE_ACT_POINT_5	N	ID_MEMBER	
I_ACT_POINT1	Y	ID_POINT

Should that column have unique values? --> Yes this column needs to have unique values.

--Zee

Report message to a moderator

Re: The query was running before 11g upgrade [message #526339 is a reply to message #526323]

Mon, 10 October 2011 11:03

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

I asked about Indexes on all of your Tables, but first of all on T_ACT_STAY.

Report message to a moderator

Re: The query was running before 11g upgrade [message #526343 is a reply to message #526323]

Mon, 10 October 2011 11:13

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

Quote:

Should that column have unique values? --> Yes this column needs to have unique values.

I know it SHOULD have unique values. But does it ACTUALLY have unique values?

Report message to a moderator

Re: The query was running before 11g upgrade [message #526345 is a reply to message #526339]

Mon, 10 October 2011 11:21

getzeeshan
Messages: 65
Registered: July 2008

Member

I'm sorry, may be misread it. Here you go.

T_act_point

Index Name	Unique	Column
IE_ACT_POINT_2	N	AUDIT_TIME	
IE_ACT_POINT_4	N	ID_STAY	Asc	
IE_ACT_POINT_5	N	ID_MEMBER	
I_ACT_POINT1	Y	ID_POINT

------
T_ACT_STAY

Index Name	Unique	Column
IE_ACT_STAY_1	N	CODE_HOTEL
IE_ACT_STAY_2	N	AUDIT_TIME
IE_ACT_STAY_3	N	ID_MEMBER
IE_ACT_STAY_4	N	CHECKOUT_DATE	
I_ACT_STAY	Y	ID_STAY	Asc	

-----------------
T_PGM_CONTROL

Index Name		Unique	  Column
I_PGM_CONTROL_NEW	Y	CODE_PGM_CONTROL

------------------
T_MEM_BASE

Index Name	Unique	Column
I_MEM_BASE	Y	ID_MEMBER

------------------
T_PR_HOTEL

Index Name	Unique	  Column
IE_PR_HOTEL_2	N	CTYHOCN	Asc
I_PR_HOTEL_NEW	Y	CODE_HOTEL

-----------------------------
T_ELIG_ENROLL

Index Name		Unique	  Column
IE_ELIG_ENROLL2		N	ID_MEMBER	
IE_ELIG_ENROLL4		N	ENROLL_DATE	
IE_ELIG_ENROLL5		N	CODE_SOURCE	
IE_ELIG_ENROLL6		N	STATUS	Asc	
I_ELIG_ENROLL		Y	ID_ELIG_ENROLL	

============================

Report message to a moderator

Re: The query was running before 11g upgrade [message #526348 is a reply to message #526345]

Mon, 10 October 2011 11:43

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

Try please the following sql:

SELECT /*+ index(c IE_ACT_POINT_4) */ b.id_point,
       f.code_hotel_type,
       f.code_region,
       f.code_location,
       c.checkout_date,
       b.bonus_date,
       b.id_member,
       b.id_bonus_ext,
       b.id_stay,
       c.discounted_flag                    discount_flag,
       c.duration,
       c.voucher_net_amt,
       b.code_pgm_control,
       d.code_pgm_ctrl_cat,
       d.code_pgm_ctrl_type,
       b.code_billing_type,
       b.code_hotel,
       f.code_country                       code_hotel_country,
       e.code_country                       code_mem_country,
       b.code_program_al,
       b.code_program_car,
       b.code_product,
       b.code_promotion,
       g.code_tier,
       b.code_plan,
       Nvl(b.code_source, ' '),
       b.code_settlement,
       b.stmt_date,
       b.invoice_date,
       b.day_of_week,
       b.code_adj,
       b.code_algorithm,
       'HNF'                                code_program,
       b.num_points,
       b.amt,
       Decode(b.code_adj, NULL, +1,
                          'A ', +1,
                          -1)               row_count,
       b.audit_time,
       b.code_org,
       d.code_pgm_mkt_type,
       NULL                                 code_sub_region,
       CASE
         WHEN c.userid IN ( 'PROD', 'ACTLOAD', 'MBRLOAD' ) THEN 'Y'
         ELSE 'N'
       END                                  ind_stay_load,
       Add_months(Trunc(SYSDATE, 'mm'), -1) rpt_date,
       b.estmt_date,
       b.gpm_id_stay
FROM   jstewart.err a,
       t_act_point b,
       t_act_stay c,
       t_pgm_control d,
       t_mem_base e,
       t_pr_hotel f,
       t_elig_enroll g
WHERE  b.id_point = a.id_point
       AND c.id_stay = b.id_stay
       AND d.code_pgm_control = b.code_pgm_control
       AND d.code_pgm_control = b.code_pgm_control
       AND e.id_member = b.id_member
       AND f.code_hotel(+) = c.code_hotel
       AND g.id_member = b.id_member
       AND g.status = 'A';

Send please once more execution plan + runtime stats.

Report message to a moderator

Re: The query was running before 11g upgrade [message #526375 is a reply to message #526348]

Mon, 10 October 2011 14:32

getzeeshan
Messages: 65
Registered: July 2008

Member

This takes 15:12 mins.

Report message to a moderator

Re: The query was running before 11g upgrade [message #526376 is a reply to message #526375]

Mon, 10 October 2011 14:34

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

getzeeshan wrote on Mon, 10 October 2011 12:32

This takes 15:12 mins.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/311

Report message to a moderator

Re: The query was running before 11g upgrade [message #526381 is a reply to message #526376]

Mon, 10 October 2011 15:07

getzeeshan
Messages: 65
Registered: July 2008

Member

I have run this and please see the output of the query which you had asked me to send.


set linesize 1000
set pagesize 1000

alter session set statistics_level=all;

--> Ran the sql,
-- >after that run the following select:

select plan_table_output from table ( sys.dbms_xplan.display_cursor( '','','ADVANCED ALLSTATS LAST'));

----------------
output is below this.

SQL> set linesize 1000
set pagesize 1000

SQL> alter session set statistics_level=all;

Session altered.

SQL>
SQL> set time on timing on

SQL>
--> SQL Which you had given earlier with the Index Hint was run

Elapsed: 00:13:57.91
14:59:05 SQL>

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-


14:59:05 SQL> select plan_table_output from table ( sys.dbms_xplan.display_cursor( '','','ADVANCED ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  gpfmy2n35k0ax, child number 1
-------------------------------------
SELECT /*+ index(c IE_ACT_POINT_4) */ b.id_point,
f.code_hotel_type,        f.code_region,        f.code_location,
c.checkout_date,        b.bonus_date,        b.id_member,
b.id_bonus_ext,        b.id_stay,        c.discounted_flag
      discount_flag,        c.duration,        c.voucher_net_amt,
 b.code_pgm_control,        d.code_pgm_ctrl_cat,
d.code_pgm_ctrl_type,        b.code_billing_type,        b.code_hotel,
      f.code_country                       code_hotel_country,
e.code_country                       code_mem_country,
b.code_program_al,        b.code_program_car,        b.code_product,
    b.code_promotion,        g.code_tier,        b.code_plan,
Nvl(b.code_source, ' '),        b.code_settlement,        b.stmt_date,
      b.invoice_date,        b.day_of_week,        b.code_adj,
b.code_algorithm,        'HNF'
code_program,        b.num_points,        b.amt,        D

Plan hash value: 3108081317

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name          | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | Inst   |IN-OUT| A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |               |      1 |        |       |       |   816K(100)|          |        |      |     61 |00:13:57.71 |     187 |  49755 |  61659 |       |       |          |         |
|*  1 |  HASH JOIN              |               |      1 |   3069K|  1103M|       |   816K  (5)| 02:06:54 |        |      |     61 |00:13:57.71 |     187 |  49755 |  61659 |  3398K|  1295K| 4670K (0)|         |
|   2 |   REMOTE                | T_PGM_CONTROL |      1 |  56015 |  1312K|       |   141   (8)| 00:00:02 |   HHP3 | R->S |  55778 |00:00:00.38 |       0 |      0 |      0 |       |       |          |         |
|*  3 |   HASH JOIN RIGHT OUTER |               |      1 |   3069K|  1033M|       |   815K  (5)| 02:06:52 |        |      |     61 |00:13:57.26 |     187 |  49755 |  61659 |   985K|   985K| 1443K (0)|         |
|   4 |    MAT_VIEW ACCESS FULL | T_PR_HOTEL    |      1 |   4886 |   100K|       |    16   (0)| 00:00:01 |        |      |   4917 |00:00:00.01 |     185 |      0 |      0 |       |       |          |         |
|*  5 |    HASH JOIN            |               |      1 |   3069K|   971M|   792M|   815K  (5)| 02:06:51 |        |      |     61 |00:13:57.25 |       2 |  49755 |  61659 |   743K|   743K|   14M (0)|         |
|*  6 |     HASH JOIN           |               |      1 |   3021K|   757M|   364M|   180K  (3)| 00:28:04 |        |      |     61 |00:01:33.16 |       2 |  49755 |  61659 |   448M|    24M|   28M (1)|     278K|
|   7 |      REMOTE             | T_MEM_BASE    |      1 |     12M|   218M|       | 34598   (5)| 00:05:23 |   HHP3 | R->S |     13M|00:00:32.96 |       0 |      0 |      0 |       |       |          |         |
|*  8 |      HASH JOIN          |               |      1 |   3021K|   706M|    46M|   123K  (2)| 00:19:12 |        |      |     61 |00:00:43.54 |       2 |  27280 |  27280 |   387M|    12M| 8404K (1)|     220K|
|   9 |       REMOTE            | T_ELIG_ENROLL |      1 |   1555K|    28M|       | 12392   (1)| 00:01:56 |   HHP3 | R->S |     12M|00:00:32.24 |       0 |      0 |      0 |       |       |          |         |
|  10 |       NESTED LOOPS      |               |      1 |     24M|  5208M|       |   323K(100)| 00:50:15 |        |      |     61 |00:00:00.73 |       2 |      0 |      0 |       |       |          |         |
|  11 |        TABLE ACCESS FULL| ERR           |      1 |     57 |   399 |       |     2   (0)| 00:00:01 |        |      |     61 |00:00:00.01 |       2 |      0 |      0 |       |       |          |         |
|  12 |        REMOTE           | T_ACT_POINT   |     61 |    423K|    88M|       |     2   (0)| 00:00:01 |   HHP3 | R->S |     61 |00:00:00.73 |       0 |      0 |      0 |       |       |          |         |
|  13 |     VIEW                | T_ACT_STAY    |      1 |    186M|    12G|       |   325K  (9)| 00:50:40 |        |      |    190M|00:10:26.87 |       0 |      0 |      0 |       |       |          |         |
|  14 |      REMOTE             | T_ACT_STAY    |      1 |    186M|    12G|       |   325K  (9)| 00:50:40 |   HHP3 | R->S |    190M|00:09:31.08 |       0 |      0 |      0 |       |       |          |         |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$BEF5D90F
   2 - SEL$BEF5D90F / T_PGM_CONTROL@SEL$4
   4 - SEL$BEF5D90F / F@SEL$1
   7 - SEL$BEF5D90F / T_MEM_BASE@SEL$5
   9 - SEL$BEF5D90F / T_ELIG_ENROLL@SEL$6
  11 - SEL$BEF5D90F / A@SEL$1
  12 - SEL$BEF5D90F / T_ACT_POINT@SEL$2
  13 - SEL$3        / C@SEL$1
  14 - SEL$3        / T_ACT_STAY@SEL$3

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
      DB_VERSION('11.2.0.2')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SEL$BEF5D90F")
      MERGE(@"SEL$2")
      MERGE(@"SEL$4")
      MERGE(@"SEL$5")
      MERGE(@"SEL$6")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$4")
      OUTLINE(@"SEL$5")
      OUTLINE(@"SEL$6")
      FULL(@"SEL$BEF5D90F" "A"@"SEL$1")
      FULL(@"SEL$BEF5D90F" "T_ACT_POINT"@"SEL$2")
      FULL(@"SEL$BEF5D90F" "T_ELIG_ENROLL"@"SEL$6")
      FULL(@"SEL$BEF5D90F" "T_MEM_BASE"@"SEL$5")
      NO_ACCESS(@"SEL$BEF5D90F" "C"@"SEL$1")
      FULL(@"SEL$BEF5D90F" "F"@"SEL$1")
      FULL(@"SEL$BEF5D90F" "T_PGM_CONTROL"@"SEL$4")
      LEADING(@"SEL$BEF5D90F" "A"@"SEL$1" "T_ACT_POINT"@"SEL$2" "T_ELIG_ENROLL"@"SEL$6" "T_MEM_BASE"@"SEL$5" "C"@"SEL$1" "F"@"SEL$1" "T_PGM_CONTROL"@"SEL$4")
      USE_NL(@"SEL$BEF5D90F" "T_ACT_POINT"@"SEL$2")
      USE_HASH(@"SEL$BEF5D90F" "T_ELIG_ENROLL"@"SEL$6")
      USE_HASH(@"SEL$BEF5D90F" "T_MEM_BASE"@"SEL$5")
      USE_HASH(@"SEL$BEF5D90F" "C"@"SEL$1")
      USE_HASH(@"SEL$BEF5D90F" "F"@"SEL$1")
      USE_HASH(@"SEL$BEF5D90F" "T_PGM_CONTROL"@"SEL$4")
      SWAP_JOIN_INPUTS(@"SEL$BEF5D90F" "T_ELIG_ENROLL"@"SEL$6")
      SWAP_JOIN_INPUTS(@"SEL$BEF5D90F" "T_MEM_BASE"@"SEL$5")
      SWAP_JOIN_INPUTS(@"SEL$BEF5D90F" "F"@"SEL$1")
      SWAP_JOIN_INPUTS(@"SEL$BEF5D90F" "T_PGM_CONTROL"@"SEL$4")
      FULL(@"SEL$3" "T_ACT_STAY"@"SEL$3")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("CODE_PGM_CONTROL"="CODE_PGM_CONTROL")
   3 - access("F"."CODE_HOTEL"="C"."CODE_HOTEL")
   5 - access("C"."ID_STAY"="ID_STAY")
   6 - access("ID_MEMBER"="ID_MEMBER")
   8 - access("ID_MEMBER"="ID_MEMBER")

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=1) "CODE_PGM_CTRL_CAT"[CHARACTER,2], "CODE_PGM_CTRL_TYPE"[CHARACTER,2], "CODE_PGM_MKT_TYPE"[CHARACTER,6], "F"."CODE_COUNTRY"[CHARACTER,3], "F"."CODE_REGION"[CHARACTER,2],
       "F"."CODE_LOCATION"[CHARACTER,2], "F"."CODE_HOTEL_TYPE"[CHARACTER,2], "ID_STAY"[NUMBER,22], "ID_MEMBER"[NUMBER,22], "CODE_COUNTRY"[CHARACTER,3], "ESTMT_DATE"[DATE,7], "CODE_TIER"[CHARACTER,1],
       "ID_POINT"[NUMBER,22], "CODE_PGM_CONTROL"[CHARACTER,6], "GPM_ID_STAY"[NUMBER,22], "CODE_BILLING_TYPE"[CHARACTER,1], "CODE_ORG"[CHARACTER,6], "CODE_HOTEL"[CHARACTER,7], "AUDIT_TIME"[DATE,7],
       "BONUS_DATE"[DATE,7], "ID_BONUS_EXT"[NUMBER,22], "NUM_POINTS"[NUMBER,22], "CODE_ALGORITHM"[CHARACTER,1], "AMT"[NUMBER,22], "CODE_PROGRAM_AL"[CHARACTER,3], "CODE_PROGRAM_CAR"[CHARACTER,3],
       "CODE_PRODUCT"[CHARACTER,4], "CODE_PROMOTION"[CHARACTER,6], "CODE_PLAN"[CHARACTER,6], "CODE_SOURCE"[CHARACTER,5], "DAY_OF_WEEK"[CHARACTER,1], "CODE_SETTLEMENT"[CHARACTER,3], "STMT_DATE"[DATE,7],
       "INVOICE_DATE"[DATE,7], "CODE_ADJ"[CHARACTER,3], "C"."USERID"[VARCHAR2,14], "C"."CHECKOUT_DATE"[DATE,7], "C"."DURATION"[NUMBER,22], "C"."VOUCHER_NET_AMT"[NUMBER,22], "C"."DISCOUNTED_FLAG"[CHARACTER,1]
   2 - "CODE_PGM_CONTROL"[CHARACTER,6], "CODE_PGM_CTRL_CAT"[CHARACTER,2], "CODE_PGM_CTRL_TYPE"[CHARACTER,2], "CODE_PGM_MKT_TYPE"[CHARACTER,6]
   3 - (#keys=1) "F"."CODE_COUNTRY"[CHARACTER,3], "F"."CODE_REGION"[CHARACTER,2], "F"."CODE_LOCATION"[CHARACTER,2], "F"."CODE_HOTEL_TYPE"[CHARACTER,2], "ID_STAY"[NUMBER,22], "ID_MEMBER"[NUMBER,22],
       "CODE_COUNTRY"[CHARACTER,3], "ESTMT_DATE"[DATE,7], "CODE_TIER"[CHARACTER,1], "ID_POINT"[NUMBER,22], "CODE_PGM_CONTROL"[CHARACTER,6], "GPM_ID_STAY"[NUMBER,22], "CODE_BILLING_TYPE"[CHARACTER,1],
       "CODE_ORG"[CHARACTER,6], "CODE_HOTEL"[CHARACTER,7], "AUDIT_TIME"[DATE,7], "BONUS_DATE"[DATE,7], "ID_BONUS_EXT"[NUMBER,22], "NUM_POINTS"[NUMBER,22], "CODE_ALGORITHM"[CHARACTER,1], "AMT"[NUMBER,22],
       "CODE_PROGRAM_AL"[CHARACTER,3], "CODE_PROGRAM_CAR"[CHARACTER,3], "CODE_PRODUCT"[CHARACTER,4], "CODE_PROMOTION"[CHARACTER,6], "CODE_PLAN"[CHARACTER,6], "CODE_SOURCE"[CHARACTER,5],
       "DAY_OF_WEEK"[CHARACTER,1], "CODE_SETTLEMENT"[CHARACTER,3], "STMT_DATE"[DATE,7], "INVOICE_DATE"[DATE,7], "CODE_ADJ"[CHARACTER,3], "C"."USERID"[VARCHAR2,14], "C"."CHECKOUT_DATE"[DATE,7],
       "C"."DURATION"[NUMBER,22], "C"."VOUCHER_NET_AMT"[NUMBER,22], "C"."DISCOUNTED_FLAG"[CHARACTER,1]
   4 - "F"."CODE_HOTEL"[CHARACTER,7], "F"."CODE_COUNTRY"[CHARACTER,3], "F"."CODE_REGION"[CHARACTER,2], "F"."CODE_LOCATION"[CHARACTER,2], "F"."CODE_HOTEL_TYPE"[CHARACTER,2]
   5 - (#keys=1) "ID_STAY"[NUMBER,22], "ID_MEMBER"[NUMBER,22], "CODE_COUNTRY"[CHARACTER,3], "ESTMT_DATE"[DATE,7], "CODE_TIER"[CHARACTER,1], "ID_POINT"[NUMBER,22], "CODE_PGM_CONTROL"[CHARACTER,6],
       "GPM_ID_STAY"[NUMBER,22], "CODE_BILLING_TYPE"[CHARACTER,1], "CODE_ORG"[CHARACTER,6], "CODE_HOTEL"[CHARACTER,7], "AUDIT_TIME"[DATE,7], "BONUS_DATE"[DATE,7], "ID_BONUS_EXT"[NUMBER,22],
       "NUM_POINTS"[NUMBER,22], "CODE_ALGORITHM"[CHARACTER,1], "AMT"[NUMBER,22], "CODE_PROGRAM_AL"[CHARACTER,3], "CODE_PROGRAM_CAR"[CHARACTER,3], "CODE_PRODUCT"[CHARACTER,4], "CODE_PROMOTION"[CHARACTER,6],
       "CODE_PLAN"[CHARACTER,6], "CODE_SOURCE"[CHARACTER,5], "DAY_OF_WEEK"[CHARACTER,1], "CODE_SETTLEMENT"[CHARACTER,3], "STMT_DATE"[DATE,7], "INVOICE_DATE"[DATE,7], "CODE_ADJ"[CHARACTER,3],
       "C"."USERID"[VARCHAR2,14], "C"."CHECKOUT_DATE"[DATE,7], "C"."CODE_HOTEL"[CHARACTER,7], "C"."DURATION"[NUMBER,22], "C"."VOUCHER_NET_AMT"[NUMBER,22], "C"."DISCOUNTED_FLAG"[CHARACTER,1]
   6 - (#keys=1) "ID_MEMBER"[NUMBER,22], "CODE_COUNTRY"[CHARACTER,3], "ESTMT_DATE"[DATE,7], "CODE_TIER"[CHARACTER,1], "ID_POINT"[NUMBER,22], "CODE_PGM_CONTROL"[CHARACTER,6], "GPM_ID_STAY"[NUMBER,22],
       "CODE_BILLING_TYPE"[CHARACTER,1], "CODE_ORG"[CHARACTER,6], "CODE_HOTEL"[CHARACTER,7], "ID_STAY"[NUMBER,22], "BONUS_DATE"[DATE,7], "ID_BONUS_EXT"[NUMBER,22], "NUM_POINTS"[NUMBER,22],
       "CODE_ALGORITHM"[CHARACTER,1], "AMT"[NUMBER,22], "CODE_PROGRAM_AL"[CHARACTER,3], "CODE_PROGRAM_CAR"[CHARACTER,3], "CODE_PRODUCT"[CHARACTER,4], "CODE_PROMOTION"[CHARACTER,6], "CODE_PLAN"[CHARACTER,6],
       "CODE_SOURCE"[CHARACTER,5], "DAY_OF_WEEK"[CHARACTER,1], "CODE_SETTLEMENT"[CHARACTER,3], "STMT_DATE"[DATE,7], "INVOICE_DATE"[DATE,7], "CODE_ADJ"[CHARACTER,3], "AUDIT_TIME"[DATE,7]
   7 - "ID_MEMBER"[NUMBER,22], "CODE_COUNTRY"[CHARACTER,3]
   8 - (#keys=1) "ID_MEMBER"[NUMBER,22], "CODE_TIER"[CHARACTER,1], "ID_POINT"[NUMBER,22], "CODE_PGM_CONTROL"[CHARACTER,6], "GPM_ID_STAY"[NUMBER,22], "CODE_BILLING_TYPE"[CHARACTER,1],
       "CODE_ORG"[CHARACTER,6], "CODE_HOTEL"[CHARACTER,7], "ID_STAY"[NUMBER,22], "BONUS_DATE"[DATE,7], "ID_BONUS_EXT"[NUMBER,22], "NUM_POINTS"[NUMBER,22], "CODE_ALGORITHM"[CHARACTER,1], "AMT"[NUMBER,22],
       "CODE_PROGRAM_AL"[CHARACTER,3], "CODE_PROGRAM_CAR"[CHARACTER,3], "CODE_PRODUCT"[CHARACTER,4], "CODE_PROMOTION"[CHARACTER,6], "CODE_PLAN"[CHARACTER,6], "CODE_SOURCE"[CHARACTER,5],
       "DAY_OF_WEEK"[CHARACTER,1], "CODE_SETTLEMENT"[CHARACTER,3], "STMT_DATE"[DATE,7], "INVOICE_DATE"[DATE,7], "CODE_ADJ"[CHARACTER,3], "AUDIT_TIME"[DATE,7], "ESTMT_DATE"[DATE,7]
   9 - "ID_MEMBER"[NUMBER,22], "CODE_TIER"[CHARACTER,1], "STATUS"[CHARACTER,1]
  10 - "ID_POINT"[NUMBER,22], "CODE_PGM_CONTROL"[CHARACTER,6], "ID_MEMBER"[NUMBER,22], "CODE_BILLING_TYPE"[CHARACTER,1], "CODE_ORG"[CHARACTER,6], "CODE_HOTEL"[CHARACTER,7], "ID_STAY"[NUMBER,22],
       "BONUS_DATE"[DATE,7], "ID_BONUS_EXT"[NUMBER,22], "NUM_POINTS"[NUMBER,22], "CODE_ALGORITHM"[CHARACTER,1], "AMT"[NUMBER,22], "CODE_PROGRAM_AL"[CHARACTER,3], "CODE_PROGRAM_CAR"[CHARACTER,3],
       "CODE_PRODUCT"[CHARACTER,4], "CODE_PROMOTION"[CHARACTER,6], "CODE_PLAN"[CHARACTER,6], "CODE_SOURCE"[CHARACTER,5], "DAY_OF_WEEK"[CHARACTER,1], "CODE_SETTLEMENT"[CHARACTER,3], "STMT_DATE"[DATE,7],
       "INVOICE_DATE"[DATE,7], "CODE_ADJ"[CHARACTER,3], "AUDIT_TIME"[DATE,7], "ESTMT_DATE"[DATE,7], "GPM_ID_STAY"[NUMBER,22]
  11 - "A"."ID_POINT"[NUMBER,22]
  12 - "ID_POINT"[NUMBER,22], "CODE_PGM_CONTROL"[CHARACTER,6], "ID_MEMBER"[NUMBER,22], "CODE_BILLING_TYPE"[CHARACTER,1], "CODE_ORG"[CHARACTER,6], "CODE_HOTEL"[CHARACTER,7], "ID_STAY"[NUMBER,22],
       "BONUS_DATE"[DATE,7], "ID_BONUS_EXT"[NUMBER,22], "NUM_POINTS"[NUMBER,22], "CODE_ALGORITHM"[CHARACTER,1], "AMT"[NUMBER,22], "CODE_PROGRAM_AL"[CHARACTER,3], "CODE_PROGRAM_CAR"[CHARACTER,3],
       "CODE_PRODUCT"[CHARACTER,4], "CODE_PROMOTION"[CHARACTER,6], "CODE_PLAN"[CHARACTER,6], "CODE_SOURCE"[CHARACTER,5], "DAY_OF_WEEK"[CHARACTER,1], "CODE_SETTLEMENT"[CHARACTER,3], "STMT_DATE"[DATE,7],
       "INVOICE_DATE"[DATE,7], "CODE_ADJ"[CHARACTER,3], "AUDIT_TIME"[DATE,7], "ESTMT_DATE"[DATE,7], "GPM_ID_STAY"[NUMBER,22]
  13 - "C"."ID_STAY"[NUMBER,22], "C"."CHECKOUT_DATE"[DATE,7], "C"."CODE_HOTEL"[CHARACTER,7], "C"."DURATION"[NUMBER,22], "C"."VOUCHER_NET_AMT"[NUMBER,22], "C"."DISCOUNTED_FLAG"[CHARACTER,1],
       "C"."USERID"[VARCHAR2,14]
  14 - "ID_STAY"[NUMBER,22], "CHECKOUT_DATE"[DATE,7], "CODE_HOTEL"[CHARACTER,7], "DURATION"[NUMBER,22], "VOUCHER_NET_AMT"[NUMBER,22], "DISCOUNTED_FLAG"[CHARACTER,1], "USERID"[VARCHAR2,14]

Remote SQL Information (identified by operation id):
----------------------------------------------------

   2 - SELECT "CODE_PGM_CONTROL","CODE_PGM_CTRL_CAT","CODE_PGM_CTRL_TYPE","CODE_PGM_MKT_TYPE" FROM "HH"."T_PGM_CONTROL" "T_PGM_CONTROL" (accessing 'HHP3' )

   7 - SELECT "ID_MEMBER","CODE_COUNTRY" FROM "HH"."T_MEM_BASE" "T_MEM_BASE" (accessing 'HHP3' )

   9 - SELECT "ID_MEMBER","CODE_TIER","STATUS" FROM "HH"."T_ELIG_ENROLL" "T_ELIG_ENROLL" WHERE "STATUS"='A' (accessing 'HHP3' )

  12 - SELECT "ID_POINT","CODE_PGM_CONTROL","ID_MEMBER","CODE_BILLING_TYPE","CODE_ORG","CODE_HOTEL","ID_STAY","BONUS_DATE","ID_BONUS_EXT","NUM_POINTS","CODE_ALGORITHM","AMT","CODE_PROGRAM_AL","CODE_PROG
        RAM_CAR","CODE_PRODUCT","CODE_PROMOTION","CODE_PLAN","CODE_SOURCE","DAY_OF_WEEK","CODE_SETTLEMENT","STMT_DATE","INVOICE_DATE","CODE_ADJ","AUDIT_TIME","ESTMT_DATE","GPM_ID_STAY" FROM "HH"."T_ACT_POINT"
        "T_ACT_POINT" WHERE "ID_POINT"=:1 (accessing 'HHP3' )

  14 - SELECT /*+ INDEX ("T_ACT_STAY" "IE_ACT_POINT_4") */ "ID_STAY","CHECKOUT_DATE","CODE_HOTEL","DURATION","VOUCHER_NET_AMT","DISCOUNTED_FLAG","USERID" FROM "HH"."T_ACT_STAY" "T_ACT_STAY" (accessing
        'HHP3' )



166 rows selected.

Elapsed: 00:00:01.55

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

Report message to a moderator

Re: The query was running before 11g upgrade [message #526387 is a reply to message #526381]

Mon, 10 October 2011 15:42

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

Ok, it is the same execution plan.

Is the result of join of ERR, T_ACT_POINT and T_ACT_STAY always so small?

If so, you can try this sql:

SELECT /*+ index(c IE_ACT_POINT_4) leading(a b c) use_nl(a b c) */ b.id_point,
       f.code_hotel_type,
       f.code_region,
       f.code_location,
       c.checkout_date,
       b.bonus_date,
       b.id_member,
       b.id_bonus_ext,
       b.id_stay,
       c.discounted_flag                    discount_flag,
       c.duration,
       c.voucher_net_amt,
       b.code_pgm_control,
       d.code_pgm_ctrl_cat,
       d.code_pgm_ctrl_type,
       b.code_billing_type,
       b.code_hotel,
       f.code_country                       code_hotel_country,
       e.code_country                       code_mem_country,
       b.code_program_al,
       b.code_program_car,
       b.code_product,
       b.code_promotion,
       g.code_tier,
       b.code_plan,
       Nvl(b.code_source, ' '),
       b.code_settlement,
       b.stmt_date,
       b.invoice_date,
       b.day_of_week,
       b.code_adj,
       b.code_algorithm,
       'HNF'                                code_program,
       b.num_points,
       b.amt,
       Decode(b.code_adj, NULL, +1,
                          'A ', +1,
                          -1)               row_count,
       b.audit_time,
       b.code_org,
       d.code_pgm_mkt_type,
       NULL                                 code_sub_region,
       CASE
         WHEN c.userid IN ( 'PROD', 'ACTLOAD', 'MBRLOAD' ) THEN 'Y'
         ELSE 'N'
       END                                  ind_stay_load,
       Add_months(Trunc(SYSDATE, 'mm'), -1) rpt_date,
       b.estmt_date,
       b.gpm_id_stay
FROM   jstewart.err a,
       t_act_point b,
       t_act_stay c,
       t_pgm_control d,
       t_mem_base e,
       t_pr_hotel f,
       t_elig_enroll g
WHERE  b.id_point = a.id_point
       AND c.id_stay = b.id_stay
       AND d.code_pgm_control = b.code_pgm_control
       AND d.code_pgm_control = b.code_pgm_control
       AND e.id_member = b.id_member
       AND f.code_hotel(+) = c.code_hotel
       AND g.id_member = b.id_member
       AND g.status = 'A';

You can firstly try with the explain plan (to check, if the hints do, what they should).

Report message to a moderator

Re: The query was running before 11g upgrade [message #526392 is a reply to message #526387]

Mon, 10 October 2011 17:20

getzeeshan
Messages: 65
Registered: July 2008

Member

--> Yes the result of the join of ERR is usually small and the table ERR is created by a BSA monthly for their reporting and processing and they usually have ID members with around 60-65 records.

following is the explain plan out put

[/code]

select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------
Plan hash value: 2223175828

-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Inst |IN-OUT|
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3069K| 1103M| | 7875G (9)|999:59:59 | | |
|* 1 | HASH JOIN | | 3069K| 1103M| | 7875G (9)|999:59:59 | | |
| 2 | REMOTE | T_PGM_CONTROL | 56015 | 1312K| | 141 (8)| 00:00:02 | HHP3 | R->S |
|* 3 | HASH JOIN RIGHT OUTER | | 3069K| 1033M| | 7875G (9)|999:59:59 | | |
| 4 | MAT_VIEW ACCESS FULL | T_PR_HOTEL | 4886 | 100K| | 16 (0)| 00:00:01 | | |
|* 5 | HASH JOIN | | 3069K| 971M| 364M| 7875G (9)|999:59:59 | | |
| 6 | REMOTE | T_MEM_BASE | 12M| 218M| | 34598 (5)| 00:05:23 | HHP3 | R->S |
|* 7 | HASH JOIN | | 3069K| 919M| 46M| 7875G (9)|999:59:59 | | |
| 8 | REMOTE | T_ELIG_ENROLL | 1555K| 28M| | 12392 (1)| 00:01:56 | HHP3 | R->S |
| 9 | NESTED LOOPS | | 24M| 6905M| | 7875G (9)|999:59:59 | | |
| 10 | NESTED LOOPS | | 24M| 5208M| | 323K(100)| 00:50:15 | | |
| 11 | TABLE ACCESS FULL| ERR | 57 | 399 | | 2 (0)| 00:00:01 | | |
| 12 | REMOTE | T_ACT_POINT | 423K| 88M| | 2 (0)| 00:00:01 | HHP3 | R->S |
|* 13 | VIEW | T_ACT_STAY | 1 | 69 | | 325K (9)| 00:50:40 | | |
| 14 | REMOTE | T_ACT_STAY | 186M| 12G| | 325K (9)| 00:50:40 | HHP3 | R->S |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("CODE_PGM_CONTROL"="CODE_PGM_CONTROL")
3 - access("F"."CODE_HOTEL"(+)="C"."CODE_HOTEL")
5 - access("ID_MEMBER"="ID_MEMBER")
7 - access("ID_MEMBER"="ID_MEMBER")
13 - filter("C"."ID_STAY"="ID_STAY")

Remote SQL Information (identified by operation id):
----------------------------------------------------

2 - SELECT "CODE_PGM_CONTROL","CODE_PGM_CTRL_CAT","CODE_PGM_CTRL_TYPE","CODE_PGM_MKT_TYPE" FROM
"HH"."T_PGM_CONTROL" "T_PGM_CONTROL" (accessing 'HHP3' )

6 - SELECT "ID_MEMBER","CODE_COUNTRY" FROM "HH"."T_MEM_BASE" "T_MEM_BASE" (accessing 'HHP3' )

8 - SELECT "ID_MEMBER","CODE_TIER","STATUS" FROM "HH"."T_ELIG_ENROLL" "T_ELIG_ENROLL" WHERE
"STATUS"='A' (accessing 'HHP3' )

12 - SELECT /*+ USE_NL ("T_ACT_POINT") */ "ID_POINT","CODE_PGM_CONTROL","ID_MEMBER","CODE_BILLING_TYPE"
,"CODE_ORG","CODE_HOTEL","ID_STAY","BONUS_DATE","ID_BONUS_EXT","NUM_POINTS","CODE_ALGORITHM","AMT","CODE_
PROGRAM_AL","CODE_PROGRAM_CAR","CODE_PRODUCT","CODE_PROMOTION","CODE_PLAN","CODE_SOURCE","DAY_OF_WEEK","C
ODE_SETTLEMENT","STMT_DATE","INVOICE_DATE","CODE_ADJ","AUDIT_TIME","ESTMT_DATE","GPM_ID_STAY" FROM
"HH"."T_ACT_POINT" "T_ACT_POINT" WHERE "ID_POINT"=:1 (accessing 'HHP3' )

14 - SELECT /*+ INDEX ("T_ACT_STAY" "IE_ACT_POINT_4") */
"ID_STAY","CHECKOUT_DATE","CODE_HOTEL","DURATION","VOUCHER_NET_AMT","DISCOUNTED_FLAG","USERID" FROM
"HH"."T_ACT_STAY" "T_ACT_STAY" (accessing 'HHP3' )

52 rows selected.

Elapsed: 00:00:09.23

[/code]

I ran the query after taking the above execution plan.
The query is still running from over 2 hours.

--Zee

Report message to a moderator

Re: The query was running before 11g upgrade [message #526420 is a reply to message #526392]

Tue, 11 October 2011 01:07

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

I think, the problem is, that the predicate "ID_STAY"=:1 isn't pushed into the view. Compare

 12 - SELECT /*+ USE_NL ("T_ACT_POINT") */ "ID_POINT","CODE_PGM_CONTROL","ID_MEMBER","CODE_BILLING_TYPE"
,"CODE_ORG","CODE_HOTEL","ID_STAY","BONUS_DATE","ID_BONUS_EXT","NUM_POINTS","CODE_ALGORITHM","AMT","CODE_
PROGRAM_AL","CODE_PROGRAM_CAR","CODE_PRODUCT","CODE_PROMOTION","CODE_PLAN","CODE_SOURCE","DAY_OF_WEEK","C
ODE_SETTLEMENT","STMT_DATE","INVOICE_DATE","CODE_ADJ","AUDIT_TIME","ESTMT_DATE","GPM_ID_STAY" FROM
"HH"."T_ACT_POINT" "T_ACT_POINT" WHERE [b]"ID_POINT"=:1[/b] (accessing 'HHP3' )

with

 14 - SELECT /*+ INDEX ("T_ACT_STAY" "IE_ACT_POINT_4") */
"ID_STAY","CHECKOUT_DATE","CODE_HOTEL","DURATION","VOUCHER_NET_AMT","DISCOUNTED_FLAG","USERID" FROM
"HH"."T_ACT_STAY" "T_ACT_STAY" (accessing 'HHP3' )

It could be a bug. Today I'm very busy, so I can analyze further tomorrow.

Report message to a moderator

Re: The query was running before 11g upgrade [message #526426 is a reply to message #526420]

Tue, 11 October 2011 01:28

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

You could try to work around this with DRIVING_SITE hint. Exchange all hints with this one: DRIVING_SITE( c ).

As I said already, I'll continue tomorrow, if it doesn't help either.

Report message to a moderator

Re: The query was running before 11g upgrade [message #526541 is a reply to message #526426]

Tue, 11 October 2011 09:11

getzeeshan
Messages: 65
Registered: July 2008

Member

"Driving Site" hint works fine for table T_PGM_CONTROL and it returns the query in few seconds on P2.

But further I would like to know what would have caused the query that has been running over years on 9i & 10g nothing has changed over years about the query and the kind of data suddenly runs on 11g very slow? because we have upgraded the database to 11g just a month ago and just before the upgrade this query was running on same P2 database in few secs.

It would be perfect if you can find out if its a bug or not. So that accordingly we can check with OSC.

Thanks! Very much
--Zee

Report message to a moderator

Re: The query was running before 11g upgrade [message #526581 is a reply to message #526541]

Tue, 11 October 2011 13:50

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

It looks like a bug. I'll try to find it. But in each case you should open a service request at Oracle for this issue.

Report message to a moderator

Re: The query was running before 11g upgrade [message #526584 is a reply to message #526581]

Tue, 11 October 2011 14:03

getzeeshan
Messages: 65
Registered: July 2008

Member

makes sense to me to create an SR with Oracle. Because we do not want any such thing happening on these databases as I doubt more such things are going to come up for us to tune or go through issues.

I'm waiting for your anwser wether its a bug or not a bug.

thx!
Zee

Report message to a moderator

Re: The query was running before 11g upgrade [message #526586 is a reply to message #526584]

Tue, 11 October 2011 14:49

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

What exactly an Oracle version do you have?

Could you please send your execution plan with the hint driving_site.

Report message to a moderator

Re: The query was running before 11g upgrade [message #526645 is a reply to message #526586]

Wed, 12 October 2011 03:37

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

I found already, that you use 11.2.0.2.

Your problem can be formulated as follows:

In the execution plan of a distributed query a hinted nested loop will be used. But this hint and the corresponding predicate will not be propagated to the remote site. It causes a full table scan on the remote site.

I tried already to find a known bug, but I didn't find any. If you want to open a service request, then you need a testcase for this issue (otherwise you have a bad chance to get RCA for your problem).

I tried to reproduce this issue on 11.1.0.7 and on 11.2.0.1. But I couldn't.

If you want, you can also make some tests with my testcase on 11.2.0.2 (you can modify it too).

1. Local

create table leo.lll(a number);

2. Remote

create table leo.ttt(a number, b number);
create table leo.tttt(b number);

3. Local.

explain plan for
select /*+ index(c aaa) leading(a b c) use_nl(a b c) */ b.a, c.b
from leo.lll a, ttt@<dblink> b, tttt@<dblink> c
where
a.a = b.a and
b.b = c.b;

I send to you my test on 11.1.0.7:

SQL Statement:

  1  explain plan set statement_id = 'TUTOOL_20111012101439' into sys.plan_table for
  2  select /*+ index(c aaa) leading(a b c) use_nl(a b c) */ b.a, c.b
  3  from leo.lll a, ttt@leo1121 b, tttt@leo1121 c
  4  where
  5  a.a = b.a and
  6* b.b = c.b


Plan Hash Value: 4179481569

    0       SELECT STATEMENT Optimizer=ALL_ROWS (Cost=9 Card=1 Bytes=33 CPU_Cost=1861850 IO_Cost=9 Time=1)
    1     0   NESTED LOOPS (Cost=9 Card=1 Bytes=33 CPU_Cost=1861850 IO_Cost=9 Time=1)
    2     1     NESTED LOOPS (Cost=7 Card=1 Bytes=20 CPU_Cost=1842429 IO_Cost=7 Time=1)
    3     2       TABLE ACCESS (FULL) OF LLL (TABLE) (Cost=2 Card=1 Bytes=13 CPU_Cost=0 IO_Cost=2 Time=1)
    4     2       REMOTE OF TTT (REMOTE) [SERIAL_FROM_REMOTE -> LEO1121] (Cost=5 Card=1 Bytes=7 CPU_Cost=1842429 IO_Cost=5 Time=1)
    5     1     REMOTE OF TTTT (REMOTE) [SERIAL_FROM_REMOTE -> LEO1121] (Cost=2 Card=82 Bytes=1066 CPU_Cost=19421 IO_Cost=2 Time=1)

Predicate Information (identified by operation id):


Query Block Name / Object Alias (identified by operation id):

     1 - SEL$1           /
     3 - SEL$1           / A@SEL$1
     4 - SEL$1           / B@SEL$1
     5 - SEL$1           / C@SEL$1

Remote SQL Information (identified by operation id):

    4 -  SELECT /*+ USE_NL ("B") */ "A","B" FROM "TTT" "B" WHERE :1="A"
    5 -  SELECT /*+ USE_NL ("C") INDEX ("C" "AAA") */ "B" FROM "TTTT" "C" WHERE :1="B"

Outline Data

/*+
BEGIN_OUTLINE_DATA
USE_NL(@"SEL$1" "C"@"SEL$1")
USE_NL(@"SEL$1" "B"@"SEL$1")
LEADING(@"SEL$1" "A"@"SEL$1" "B"@"SEL$1" "C"@"SEL$1")
FULL(@"SEL$1" "C"@"SEL$1")
FULL(@"SEL$1" "B"@"SEL$1")
FULL(@"SEL$1" "A"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('11.1.0.7')
OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/

As you see the hint and the predicate will be propagated.

[Updated on: Wed, 12 October 2011 04:52]

Report message to a moderator

Re: The query was running before 11g upgrade [message #526667 is a reply to message #526584]

Wed, 12 October 2011 06:04

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

Could you please send a ddl for the view T_ACT_STAY.

Report message to a moderator

Re: The query was running before 11g upgrade [message #526692 is a reply to message #526667]

Wed, 12 October 2011 09:27

getzeeshan
Messages: 65
Registered: July 2008

Member

here you go with the Driving_Site hint


select /*+ DRIVING_SITE (d )*/ b.id_point,f.code_hotel_type,f.code_region, f.code_location, c.checkout_date, b.bonus_date, b.id_member, b.id_bonus_ext, b.id_stay, c.discounted_flag discount_flag, c.duration, c.voucher_net_amt, b.code_pgm_control, d.code_pgm_ctrl_cat, d.code_pgm_ctrl_type, b.code_billing_type, b.code_hotel, f.code_country code_hotel_country, e.code_country code_mem_country, b.code_program_al, b.code_program_car, b.code_product, b.code_promotion, g.code_tier,b.code_plan, nvl(b.code_source,' '), b.code_settlement, b.stmt_date, b.invoice_date, b.day_of_week, b.code_adj, b.code_algorithm, 'HNF' code_program, b.num_points, b.amt,decode(b.code_adj,NULL,+1,'A ', +1, -1) row_count, b.audit_time, b.code_org, d.code_pgm_mkt_type, NULL code_sub_region, case when c.userid in ('PROD','ACTLOAD','MBRLOAD') then 'Y' else 'N' end ind_stay_load, add_months(trunc(sysdate,'mm'),-1) rpt_date, b.estmt_date, b.gpm_id_stay from jstewart.err a, t_act_point b, t_act_stay c, t_pgm_control@hhp3 d, t_mem_base e, t_pr_hotel f, t_elig_enroll g where b.id_point=a.id_point and c.id_stay=b.id_stay and d.code_pgm_control=b.code_pgm_control and d.code_pgm_control=b.code_pgm_control and e.id_member=b.id_member and f.code_hotel(+)=c.code_hotel and g.id_member=b.id_member and g.status = 'A';



PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 830722565
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT REMOTE | | 58 | 19140 | 590 (1)| 00:00:06 | | | |* 1 | HASH JOIN OUTER | | 58 | 19140 | 590 (1)| 00:00:06 | | | | 2 | NESTED LOOPS | | | | | | | | | 3 | NESTED LOOPS | | 58 | 17632 | 573 (1)| 00:00:06 | | | | 4 | NESTED LOOPS | | 57 | 14934 | 459 (1)| 00:00:05 | | | | 5 | NESTED LOOPS | | 57 | 14307 | 288 (1)| 00:00:03 | | | | 6 | NESTED LOOPS | | 57 | 13680 | 173 (0)| 00:00:02 | | | | 7 | NESTED LOOPS | | 57 | 12882 | 116 (0)| 00:00:02 | | |
| 8 | REMOTE | ERR | 57 | 399 | 2 (0)| 00:00:01 | ! | R->S |
| 9 | MAT_VIEW ACCESS BY INDEX ROWID| T_ACT_POINT | 1 | 233 | 2 (0)| 00:00:01 | HHP3 | |
|* 10 | INDEX UNIQUE SCAN | I_ACT_POINT1 | 1 | | 1 (0)| 00:00:01 | HHP3 | |
| 11 | MAT_VIEW ACCESS BY INDEX ROWID | T_PGM_CONTROL | 1 | 14 | 1 (0)| 00:00:01 | HHP3 | |
|* 12 | INDEX UNIQUE SCAN | I_PGM_CONTROL_NEW | 1 | | 0 (0)| 00:00:01 | HHP3 | |
| 13 | MAT_VIEW ACCESS BY INDEX ROWID | T_MEM_BASE | 1 | 11 | 2 (0)| 00:00:01 | HHP3 | |
|* 14 | INDEX UNIQUE SCAN | I_MEM_BASE | 1 | | 1 (0)| 00:00:01 | HHP3 | |
|* 15 | MAT_VIEW ACCESS BY INDEX ROWID | T_ELIG_ENROLL | 1 | 11 | 3 (0)| 00:00:01 | HHP3 | |
|* 16 | INDEX RANGE SCAN | IE_ELIG_ENROLL2 | 1 | | 2 (0)| 00:00:01 | HHP3 | |
|* 17 | INDEX UNIQUE SCAN | I_ACT_STAY | 1 | | 1 (0)| 00:00:01 | HHP3 | |
| 18 | MAT_VIEW ACCESS BY INDEX ROWID | T_ACT_STAY | 1 | 42 | 2 (0)| 00:00:01 | HHP3 | |
| 19 | REMOTE | T_PR_HOTEL | 4886 | 124K| 17 (6)| 00:00:01 | ! | R->S |
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A2"."CODE_HOTEL"(+)="A5"."CODE_HOTEL")
10 - access("A6"."ID_POINT"="A7"."ID_POINT")
12 - access("A4"."CODE_PGM_CONTROL"="A6"."CODE_PGM_CONTROL")
14 - access("A3"."ID_MEMBER"="A6"."ID_MEMBER")
15 - filter("A1"."STATUS"='A')
16 - access("A1"."ID_MEMBER"="A6"."ID_MEMBER")
17 - access("A5"."ID_STAY"="A6"."ID_STAY")
Remote SQL Information (identified by operation id):
----------------------------------------------------
8 - SELECT "ID_POINT" FROM "JSTEWART"."ERR" "A7" (accessing '!' )
19 - SELECT "CODE_HOTEL","CODE_COUNTRY","CODE_REGION","CODE_LOCATION","CODE_HO
"A2" (accessing '!' )
Note
-----
- fully remote statement
- dynamic sampling used for this statement (level=2)
51 rows selected.

And her you go with the View Code for T_ACT_STAY


CREATE OR REPLACE FORCE VIEW HH.T_ACT_STAY
(
   ID_STAY,
   CHECKOUT_DATE,
   CODE_HOTEL,
   ID_MEMBER,
   CHECKIN_DATE,
   DURATION,
   CODE_CURRENCY,
   VOUCHER_GROSS_AMT,
   VOUCHER_NET_AMT,
   VOUCHER_NET_AMT_F,
   CODE_PLAN,
   CODE_TIER,
   CODE_SETTLEMENT,
   ONLINE_REASON,
   NUM_OF_ADULTS,
   NUM_OF_CHILDREN,
   ACCOMODATIONS,
   PREMIUM_IND,
   RATE_CATEGORY,
   DISCOUNTED_FLAG,
   ROOM_RATE,
   ROOM_RATE_FOREIGN,
   BATCH_NUM,
   CODE_PROGRAM_AL,
   EXT_ACCT_NO,
   CODE_PROGRAM_CAR,
   STMT_DATE,
   INVOICE_DATE,
   STATUS,
   USERID,
   AUDIT_TIME,
   ID_ADJ_XREF,
   TRANSACTION_TYPE,
   IND_EARNING_STYLE,
   ESTMT_DATE,
   CONFIRMATION_NUM,
   GPM_ID_STAY,
   IND_CHECKIN,
   EXTENDED_STAY_IND,
   CORPORATE_ID,
   GPM_NATIVE_CURRENCY,
   GPM_CONVERSION_FACTOR,
   REDEMPTION_TYPE,
   IND_UPGRADE,
   AMT
)
AS
   SELECT "ID_STAY",
          "CHECKOUT_DATE",
          "CODE_HOTEL",
          "ID_MEMBER",
          "CHECKIN_DATE",
          "DURATION",
          "CODE_CURRENCY",
          "VOUCHER_GROSS_AMT",
          "VOUCHER_NET_AMT",
          "VOUCHER_NET_AMT_F",
          "CODE_PLAN",
          "CODE_TIER",
          "CODE_SETTLEMENT",
          "ONLINE_REASON",
          "NUM_OF_ADULTS",
          "NUM_OF_CHILDREN",
          "ACCOMODATIONS",
          "PREMIUM_IND",
          "RATE_CATEGORY",
          "DISCOUNTED_FLAG",
          "ROOM_RATE",
          "ROOM_RATE_FOREIGN",
          "BATCH_NUM",
          "CODE_PROGRAM_AL",
          "EXT_ACCT_NO",
          "CODE_PROGRAM_CAR",
          "STMT_DATE",
          "INVOICE_DATE",
          "STATUS",
          "USERID",
          "AUDIT_TIME",
          "ID_ADJ_XREF",
          "TRANSACTION_TYPE",
          "IND_EARNING_STYLE",
          "ESTMT_DATE",
          "CONFIRMATION_NUM",
          "GPM_ID_STAY",
          "IND_CHECKIN",
          "EXTENDED_STAY_IND",
          "CORPORATE_ID",
          "GPM_NATIVE_CURRENCY",
          "GPM_CONVERSION_FACTOR",
          "REDEMPTION_TYPE",
          "IND_UPGRADE",
          "AMT"
     FROM hh.t_act_stay@hhp3;

CM: fixed the code tags, first one was an end tag. Use the preview function to check.

[Updated on: Wed, 12 October 2011 09:50] by Moderator

Report message to a moderator

Re: The query was running before 11g upgrade [message #526700 is a reply to message #526692]

Wed, 12 October 2011 09:50

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

Could you please format properly your output.

I tried already my testcase with views, but I couldn't reproduce the issue either.

You can try the same on 11.2.0.2. If you reproduce the issue, then you can open a service request.

Report message to a moderator

Re: The query was running before 11g upgrade [message #526701 is a reply to message #526700]

Wed, 12 October 2011 09:51

getzeeshan
Messages: 65
Registered: July 2008

Member

Sure let me format the output and re post it for you.

I dont know why the earlier post got messed up.

--Zee

Report message to a moderator

Re: The query was running before 11g upgrade [message #526703 is a reply to message #526701]

Wed, 12 October 2011 09:54

cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester

Senior Member

Because your first tag was an end code tag instead of a start one.
I've already fixed it.

Report message to a moderator

Re: The query was running before 11g upgrade [message #526704 is a reply to message #526703]

Wed, 12 October 2011 09:55

cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester

Senior Member

Looking back I realise that's only part of the problem. That shouldn't happen with a direct copy and paste from sqlplus.

Report message to a moderator

Re: The query was running before 11g upgrade [message #527014 is a reply to message #526701]

Fri, 14 October 2011 08:11

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

What is about formatted output? I still hope to get it Smile

.

Did you try to reproduce the issue in 11.2.0.2?

Report message to a moderator

Re: The query was running before 11g upgrade [message #527027 is a reply to message #527014]

Fri, 14 October 2011 09:07

getzeeshan
Messages: 65
Registered: July 2008

Member

Im sorry I could not get time to work on it since we had some Prod restore of database for last 2 days working on it and some Sev1 related TAR. Today I will try and duplicate the same

thx!
Zee

Report message to a moderator

Previous Topic:	sga,pga parameter resizing
Next Topic:	QUERY Help

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 21 19:03:29 CST 2024