Home » RDBMS Server » Performance Tuning » Query not executing (3 Merged) (Oracle 10g)
Query not executing (3 Merged) [message #501356] Mon, 28 March 2011 12:53 Go to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Our database is upgraded from Oracle 9i to 10g .

Before upgradation the following Materialized view refresh was completing with in 2 minutes.

After upgradation the is Materialized view refresh is running for long time and refresh is not completing.

Please help me to how to improve the performance of the MVIEW.

dbms_mview.refresh('vmc_mview', 'A');


Even if I run only the SQL query then also it's running for long time.

The following is the script for the vmc_mview materialized view.
CREATE SNAPSHOT vmc_mview
  PCTFREE     10
  PCTUSED     40
  MAXTRANS    255
  TABLESPACE  matl_vw_tbl
  STORAGE   (
    INITIAL     131072
    NEXT        139264
    PCTINCREASE 1
    MINEXTENTS  2
    MAXEXTENTS  2147483645
  )
BUILD IMMEDIATE 
REFRESH ON DEMAND
AS
select
distinct
  a.emplid,
  a.empl_rcd,
  a.vc_plan_id,
  a.vc_payout_prd_id,
  a.gb_group_id,
  a.vc_target_value,
  a.vc_target_value/ce.acct_rt as vc_target_value_usd_am,
  a.vc_calc_award,
  a.vc_calc_award/ce.acct_rt as vc_calc_award_USD_am,
  a.vc_adj_award,
  a.vc_perf_factor,
  a.vc_modifier,
  a.vc_actual_awd_amt,
  a.vc_actual_awd_amt/ce.acct_rt as vc_actual_awd_USD_am,
  a.vc_actual_awd_unit,
  a.vc_award_value,
  a.vc_award_value/ce.acct_rt as  vc_award_value_USD_am,
  a.vc_orig_awd_unit,
  a.vc_orig_awd_value,
  a.currency_cd,
  a.vc_award_dt,
  a.vc_award_status,
  b.xlatlongname as vc_award_status_dn,
  b.xlatshortname as vc_award_status_short_dn ,
  a.vc_carryover_accum,
  a.vc_carryover_bal,
  a.vc_calc_source,
  c.xlatlongname   vc_calc_source_dn,
  c.xlatshortname   vc_calc_source_short_dn,
  a.grant_nbr,
  a.st_pr_dt,
  a.erncd,
  a.grossup,
  a.sepchk_flag,
  a.rejected_reason,
  a.pin_ern_num,
  to_date(sysdate , 'DD-MON-YYYY:HH:MI:SS') as wedb_processed_dt
  from ps_vc_award a,
       xlattable_tbl b ,
       xlattable_tbl c,
       ww_currency_exch_vw ce,
       ps_hp_vc_metrics mp
  where
      ( b.fieldname = 'VC_AWARD_STATUS' and b.fieldvalue = a.vc_award_status)
      and (c.fieldname = 'VC_CALC_SOURCE' and c.fieldvalue = a.vc_calc_source)
      and ce.exch_currency_eff_dt = ( select max(ce1.exch_currency_eff_dt)
                                      from
                                      ww_currency_exch_vw ce1
				      where
				      ce1.exch_currency_eff_dt <= (select max( nvl(d.process_dt,sysdate))
				      from ps_hp_vc_metrics D
				      where d.vc_payout_prd_id=a.vc_payout_prd_id
				      and nvl(D.process_dt,sysdate)<=sysdate )
				      and ce1.iso_currency_cd = a.currency_cd)
				      and ce.iso_currency_cd = a.currency_cd;



Thanks in advance.








Re: Query not executing [message #501358 is a reply to message #501356] Mon, 28 March 2011 12:54 Go to previous messageGo to next message
BlackSwan
Messages: 22790
Registered: January 2009
Senior Member
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: Query not executing [message #501382 is a reply to message #501358] Mon, 28 March 2011 13:38 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Explain Plan

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

--------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                         | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                              |    38 |  8398 |  3642 |
|   1 |  HASH UNIQUE                      |                              |    38 |  8398 |  3642 |
|   2 |   FILTER                          |                              |       |       |       |
|   3 |    HASH JOIN                      |                              |  2316K|   488M|   708 |
|   4 |     TABLE ACCESS FULL             | CURRENCY_EXCH                | 15293 |   238K|    31 |
|   5 |     HASH JOIN                     |                              |   817K|   159M|   540 |
|   6 |      TABLE ACCESS BY INDEX ROWID  | XLATTABLE_TBL                |     7 |   294 |     2 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   7 |       INDEX RANGE SCAN            | XLATTABLE_FIELDNAME_INDX     |     7 |       |     1 |
|   8 |      HASH JOIN                    |                              |  3880K|   603M|   309 |
|   9 |       MERGE JOIN CARTESIAN        |                              | 14305 |   586K|    16 |
|  10 |        TABLE ACCESS BY INDEX ROWID| XLATTABLE_TBL                |     7 |   294 |     2 |
|  11 |         INDEX RANGE SCAN          | XLATTABLE_FIELDNAME_INDX     |     7 |       |     1 |
|  12 |        BUFFER SORT                |                              |  2097 |       |    14 |
|  13 |         INDEX FAST FULL SCAN      | PS_HP_VC_METRICS_PRD_ID_INDX |  2097 |       |     2 |
|  14 |       TABLE ACCESS FULL           | PS_VC_AWARD                  | 11485 |  1357K|    63 |
|  15 |    SORT AGGREGATE                 |                              |     1 |    16 |       |
|  16 |     INDEX RANGE SCAN              | ACCT_RT                      |     1 |    16 |     1 |
|  17 |    SORT AGGREGATE                 |                              |     1 |    43 |       |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|  18 |     HASH JOIN                     |                              |     1 |    43 |     8 |
|  19 |      INDEX RANGE SCAN             | ACCT_RT                      |     8 |   128 |     3 |
|  20 |       SORT AGGREGATE              |                              |     1 |    19 |       |
|  21 |        TABLE ACCESS BY INDEX ROWID| PS_HP_VC_METRICS             |     6 |   114 |     3 |
|  22 |         INDEX RANGE SCAN          | PS_HP_VC_METRICS_PRD_ID_INDX |   123 |       |     1 |
|  23 |      VIEW                         | VW_SQ_1                      |   158 |  4266 |     4 |
|  24 |       SORT GROUP BY               |                              |   158 |  2528 |     4 |
|  25 |        INDEX RANGE SCAN           | ACCT_RT                      |   158 |  2528 |     3 |
--------------------------------------------------------------------------------------------------

Note

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----
   - 'PLAN_TABLE' is old version

35 rows selected.

SQL>

Indexes


PS_VC_AWARD      	PS_VC_AWARD_AWD_STAT_INDX     	VC_AWARD_STATUS
PS_VC_AWARD      	PS_VC_AWARD_CALC_SRC_INDX     	VC_CALC_SOURCE
PS_VC_AWARD      	PS_VC_AWARD_PAY_PRD_ID_INDX   	VC_PAYOUT_PRD_ID
PS_VC_AWARD      	PS_VC_AWARD_CURR_CD_INDX      	CURRENCY_CD
PS_HP_VC_METRICS 	PS_HP_VC_METRICS_PRD_ID_INDX  	VC_PAYOUT_PRD_ID
PS_HP_VC_METRICS 	PS_HP_VC_METRICS_PROC_DT_INDX 	PROCESS_DT
XLATTABLE_TBL    	XLATTABLE_FIELDNAME_INDX      	FIELDNAME
XLATTABLE_TBL    	XLATTABLE_FIELDVALUE_INDX     	FIELDVALUE

DDL for tables

CREATE TABLE ps_vc_award
    (emplid                         VARCHAR2(33) NOT NULL,
    empl_rcd                       NUMBER(*,0) NOT NULL,
    vc_plan_id                     VARCHAR2(30) NOT NULL,
    vc_payout_prd_id               VARCHAR2(30) NOT NULL,
    gb_group_id                    VARCHAR2(45) NOT NULL,
    vc_target_value                NUMBER(18,6) NOT NULL,
    vc_calc_award                  NUMBER(18,6) NOT NULL,
    vc_adj_award                   NUMBER(18,6) NOT NULL,
    vc_perf_factor                 NUMBER(18,3) NOT NULL,
    vc_modifier                    NUMBER(5,2) NOT NULL,
    vc_actual_awd_amt              NUMBER(18,6) NOT NULL,
    vc_actual_awd_unit             NUMBER(14,6) NOT NULL,
    vc_award_value                 NUMBER(18,6) NOT NULL,
    vc_orig_awd_unit               NUMBER(14,6) NOT NULL,
    vc_orig_awd_value              NUMBER(18,6) NOT NULL,
    currency_cd                    VARCHAR2(9) NOT NULL,
    vc_award_dt                    DATE,
    vc_award_status                VARCHAR2(6) NOT NULL,
    vc_carryover_accum             NUMBER(18,6) NOT NULL,
    vc_carryover_bal               NUMBER(18,6) NOT NULL,
    vc_calc_source                 VARCHAR2(6) NOT NULL,
    grant_nbr                      VARCHAR2(30) NOT NULL,
    st_pr_dt                       DATE,
    erncd                          VARCHAR2(9) NOT NULL,
    grossup                        VARCHAR2(3) NOT NULL,
    sepchk_flag                    VARCHAR2(3) NOT NULL,
    rejected_reason                VARCHAR2(6) NOT NULL,
    pin_ern_num                    NUMBER(*,0) NOT NULL)


CREATE TABLE xlattable_tbl
    (fieldname                      VARCHAR2(54) NOT NULL,
    language_cd                    VARCHAR2(9) NOT NULL,
    fieldvalue                     VARCHAR2(12) NOT NULL,
    effdt                          DATE,
    version                        NUMBER(*,0) NOT NULL,
    eff_status                     VARCHAR2(3) NOT NULL,
    xlatlongname                   VARCHAR2(90) NOT NULL,
    xlatshortname                  VARCHAR2(30) NOT NULL,
    lastupddttm                    DATE,
    lastupdoprid                   VARCHAR2(90) NOT NULL)

CREATE TABLE ps_hp_vc_metrics
    (vc_goal_id                     VARCHAR2(30) NOT NULL,
    vc_payout_prd_id               VARCHAR2(30) NOT NULL,
    hp_bus_orgn_acrnm              VARCHAR2(9) NOT NULL,
    hp_metric_type                 VARCHAR2(48) NOT NULL,
    hp_metric_level                VARCHAR2(24) NOT NULL,
    hp_orgn_grp_acrnm              VARCHAR2(12) NOT NULL,
    hp_metric_product              VARCHAR2(48) NOT NULL,
    hp_thresh_scale                NUMBER(6,2) NOT NULL,
    hp_target_scale                NUMBER(6,2) NOT NULL,
    hp_max_scale                   NUMBER(6,2) NOT NULL,
    hp_percent_scale               NUMBER(7,2) NOT NULL,
    hp_actual_pay_pct              NUMBER(6,2) NOT NULL,
    hp_corp_gate_flag              VARCHAR2(9) NOT NULL,
    hp_roic_gate_met               VARCHAR2(3) NOT NULL,
    process_dt                     DATE)
Re: Query not executing [message #501386 is a reply to message #501382] Mon, 28 March 2011 13:54 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Please help me
Re: Query not executing [message #501388 is a reply to message #501386] Mon, 28 March 2011 14:00 Go to previous messageGo to next message
BlackSwan
Messages: 22790
Registered: January 2009
Senior Member
I am at a loss to reconcile posted EXPLAIN PLAN with post SQL.
EXPLAIN PLAN contains object names that do not appear within the SQL.

Are statistic current for all involved tables & indexes?
Re: Query not executing [message #501537 is a reply to message #501388] Tue, 29 March 2011 14:46 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
If I remove the DISTINCT clause the query will be executing in 10 seconds.

Please help me how to improve the performance of the query.
Re: Query not executing [message #501692 is a reply to message #501537] Wed, 30 March 2011 13:08 Go to previous messageGo to next message
Kevin Meade
Messages: 1938
Registered: December 1999
Location: Connecticut USA
Senior Member
Did it occurr to you to compare plans before and after and see what was different?

Kevin
Re: Query not executing [message #501694 is a reply to message #501692] Wed, 30 March 2011 13:41 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Thanks for your reply

I ran your query that's also taking long time and the execution is not completing.

Plan table with DISTINCT clause

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

----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                         | Rows  | Bytes | Cost  |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                              | 47246 |  8674K|   193K|
|   1 |  HASH UNIQUE                        |                              | 47246 |  8674K|   193K|
|   2 |   FILTER                            |                              |       |       |       |
|   3 |    HASH JOIN                        |                              |  3182M|   557G|   188K|
|   4 |     TABLE ACCESS FULL               | CURRENCY_EXCH                | 14945 |   189K|    31 |
|   5 |     HASH JOIN                       |                              |    20M|  3411M|  1548 |
|   6 |      TABLE ACCESS FULL              | PS_VC_AWARD                  | 11511 |  1112K|    63 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   7 |      MERGE JOIN CARTESIAN           |                              | 44401 |  3295K|    31 |
|   8 |       MERGE JOIN CARTESIAN          |                              |    21 |  1596 |    12 |
|   9 |        TABLE ACCESS BY INDEX ROWID  | XLATTABLE_TBL                |     5 |   190 |     2 |
|  10 |         INDEX RANGE SCAN            | XLATTABLE_FIELDNAME_INDX     |     5 |       |     1 |
|  11 |        BUFFER SORT                  |                              |     5 |   190 |    10 |
|  12 |         TABLE ACCESS BY INDEX ROWID | XLATTABLE_TBL                |     5 |   190 |     2 |
|  13 |          INDEX RANGE SCAN           | XLATTABLE_FIELDNAME_INDX     |     5 |       |     1 |
|  14 |       BUFFER SORT                   |                              |  2097 |       |    29 |
|  15 |        BITMAP CONVERSION TO ROWIDS  |                              |  2097 |       |     1 |
|  16 |         BITMAP INDEX FAST FULL SCAN | METRICS_PD_BITMAP_INDX       |       |       |       |
|  17 |    SORT AGGREGATE                   |                              |     1 |    13 |       |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|  18 |     INDEX RANGE SCAN                | ACCT_RT                      |     1 |    13 |     1 |
|  19 |    SORT AGGREGATE                   |                              |     1 |    40 |       |
|  20 |     HASH JOIN                       |                              |     1 |    40 |     8 |
|  21 |      INDEX RANGE SCAN               | ACCT_RT                      |     8 |   104 |     3 |
|  22 |       SORT AGGREGATE                |                              |     1 |    17 |       |
|  23 |        VIEW                         | index$_join$_007             |     6 |   102 |     3 |
|  24 |         HASH JOIN                   |                              |       |       |       |
|  25 |          INDEX RANGE SCAN           | PS_HP_VC_METRICS_PRD_ID_INDX |     6 |   102 |     1 |
|  26 |          BITMAP CONVERSION TO ROWIDS|                              |     6 |   102 |     1 |
|  27 |           BITMAP INDEX FULL SCAN    | METRICS_PD_BITMAP_INDX       |       |       |       |
|  28 |      VIEW                           | VW_SQ_1                      |   156 |  4212 |     4 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|  29 |       SORT GROUP BY                 |                              |   156 |  2028 |     4 |
|  30 |        INDEX RANGE SCAN             | ACCT_RT                      |   156 |  2028 |     3 |
----------------------------------------------------------------------------------------------------

Note
-----
   - 'PLAN_TABLE' is old version

40 rows selected.


Plan table without DISTINCT clause

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

---------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                         | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                              |    57 | 11514 |  1293 |
|   1 |  FILTER                            |                              |       |       |       |
|   2 |   HASH JOIN                        |                              |  3407K|   656M|   761 |
|   3 |    TABLE ACCESS BY INDEX ROWID     | XLATTABLE_TBL                |     5 |   190 |     2 |
|   4 |     INDEX RANGE SCAN               | XLATTABLE_FIELDNAME_INDX     |     5 |       |     1 |
|   5 |    HASH JOIN                       |                              |  3702K|   579M|   540 |
|   6 |     TABLE ACCESS FULL              | PS_VC_AWARD                  | 11511 |  1112K|    63 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   7 |     MERGE JOIN CARTESIAN           |                              | 28948 |  1837K|    89 |
|   8 |      MERGE JOIN CARTESIAN          |                              |    14 |   910 |    76 |
|   9 |       VIEW                         | WW_CURRENCY_EXCH_VW          |     3 |    81 |    70 |
|  10 |        SORT UNIQUE                 |                              |     3 |   114 |    70 |
|  11 |         FILTER                     |                              |       |       |       |
|  12 |          SORT GROUP BY             |                              |     3 |   114 |    70 |
|  13 |           HASH JOIN                |                              | 14945 |   554K|    64 |
|  14 |            TABLE ACCESS FULL       | CURRENCY_EXCH                | 14945 |   189K|    30 |
|  15 |            TABLE ACCESS FULL       | CURRENCY_EXCH                | 14945 |   364K|    30 |
|  16 |       BUFFER SORT                  |                              |     5 |   190 |    76 |
|  17 |        TABLE ACCESS BY INDEX ROWID | XLATTABLE_TBL                |     5 |   190 |     2 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|  18 |         INDEX RANGE SCAN           | XLATTABLE_FIELDNAME_INDX     |     5 |       |     1 |
|  19 |      BUFFER SORT                   |                              |  2097 |       |    87 |
|  20 |       BITMAP CONVERSION TO ROWIDS  |                              |  2097 |       |     1 |
|  21 |        BITMAP INDEX FAST FULL SCAN | METRICS_PD_BITMAP_INDX       |       |       |       |
|  22 |   SORT AGGREGATE                   |                              |     1 |    40 |       |
|  23 |    HASH JOIN                       |                              |     1 |    40 |     8 |
|  24 |     INDEX RANGE SCAN               | ACCT_RT                      |     8 |   104 |     3 |
|  25 |      SORT AGGREGATE                |                              |     1 |    17 |       |
|  26 |       VIEW                         | index$_join$_007             |     6 |   102 |     3 |
|  27 |        HASH JOIN                   |                              |       |       |       |
|  28 |         INDEX RANGE SCAN           | PS_HP_VC_METRICS_PRD_ID_INDX |     6 |   102 |     1 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|  29 |         BITMAP CONVERSION TO ROWIDS|                              |     6 |   102 |     1 |
|  30 |          BITMAP INDEX FULL SCAN    | METRICS_PD_BITMAP_INDX       |       |       |       |
|  31 |     VIEW                           | VW_SQ_2                      |   156 |  4212 |     4 |
|  32 |      SORT GROUP BY                 |                              |   156 |  2028 |     4 |
|  33 |       INDEX RANGE SCAN             | ACCT_RT                      |   156 |  2028 |     3 |
---------------------------------------------------------------------------------------------------

Note
-----
   - 'PLAN_TABLE' is old version


43 rows selected.


Please help me.


Re: Query not executing [message #501955 is a reply to message #501694] Fri, 01 April 2011 12:34 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
We have the same materialized view in our test data base and it also upgraded to Oracle 10g,but the MVIEW is running fine in test,but why we are getting issue in production.

The MVIEW is fetching 10000 records in test, in production it is fetching 11000

Please help me.
Thanks in advance.
Re: Query not executing [message #501966 is a reply to message #501955] Fri, 01 April 2011 15:15 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
And are the explain plans in test and prod the same?
Re: Query not executing [message #501970 is a reply to message #501694] Fri, 01 April 2011 15:43 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Explain Plan in TEST


--------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                         | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                              |  2006M|   457G|   218M|
|   1 |  HASH UNIQUE                      |                              |  2006M|   457G|   218M|
|   2 |   FILTER                          |                              |       |       |       |
|   3 |    HASH JOIN                      |                              |  2267M|   517G|   222K|
|   4 |     TABLE ACCESS BY INDEX ROWID   | XLATTABLE_TBL                |     7 |   294 |     2 |
|   5 |      INDEX RANGE SCAN             | XLATTABLE_FIELDNAME_INDX     |     7 |       |     1 |
|   6 |     HASH JOIN                     |                              |  2453M|   463G|   204K|
|   7 |      TABLE ACCESS BY INDEX ROWID  | XLATTABLE_TBL                |     7 |   294 |     2 |
|   8 |       INDEX RANGE SCAN            | XLATTABLE_FIELDNAME_INDX     |     7 |       |     1 |
|   9 |      HASH JOIN                    |                              |  2655M|   398G|   185K|
|  10 |       TABLE ACCESS FULL           | PS_VC_AWARD                  | 10182 |  1193K|    52 |
|  11 |       HASH JOIN                   |                              |    25M|   979M| 93562 |
|  12 |        VIEW                       | VW_SQ_2                      | 13305 |   324K|   106 |
|  13 |         HASH GROUP BY             |                              | 13305 |   207K|   106 |
|  14 |          TABLE ACCESS FULL        | CURRENCY_EXCH                | 15032 |   234K|    28 |
|  15 |        MERGE JOIN CARTESIAN       |                              |    28M|   431M| 50012 |
|  16 |         INDEX FAST FULL SCAN      | PS_HP_VC_METRICS_PRD_ID_INDX |  1882 |       |     3 |
|  17 |         BUFFER SORT               |                              | 15032 |   234K| 50009 |
|  18 |          TABLE ACCESS FULL        | CURRENCY_EXCH                | 15032 |   234K|    27 |
|  19 |    SORT AGGREGATE                 |                              |     1 |    43 |       |
|  20 |     HASH JOIN                     |                              |     1 |    43 |     8 |
|  21 |      INDEX RANGE SCAN             | ACCT_RT                      |     8 |   128 |     3 |
|  22 |       SORT AGGREGATE              |                              |     1 |    19 |       |
|  23 |        TABLE ACCESS BY INDEX ROWID| PS_HP_VC_METRICS             |     6 |   114 |     3 |
|  24 |         INDEX RANGE SCAN          | PS_HP_VC_METRICS_PRD_ID_INDX |   125 |       |     1 |
|  25 |      VIEW                         | VW_SQ_1                      |   157 |  4239 |     4 |
|  26 |       SORT GROUP BY               |                              |   157 |  2512 |     4 |
|  27 |        INDEX RANGE SCAN           | ACCT_RT                      |   157 |  2512 |     3 |
--------------------------------------------------------------------------------------------------

Note
-----
   - 'PLAN_TABLE' is old version



Explain Plan in PRODUCTION


----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                         | Rows  | Bytes | Cost  |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                              | 47246 |  8674K|   193K|
|   1 |  HASH UNIQUE                        |                              | 47246 |  8674K|   193K|
|   2 |   FILTER                            |                              |       |       |       |
|   3 |    HASH JOIN                        |                              |  3182M|   557G|   188K|
|   4 |     TABLE ACCESS FULL               | CURRENCY_EXCH                | 14945 |   189K|    31 |
|   5 |     HASH JOIN                       |                              |    20M|  3411M|  1548 |
|   6 |      TABLE ACCESS FULL              | PS_VC_AWARD                  | 11511 |  1112K|    63 |
|   7 |      MERGE JOIN CARTESIAN           |                              | 44401 |  3295K|    31 |
|   8 |       MERGE JOIN CARTESIAN          |                              |    21 |  1596 |    12 |
|   9 |        TABLE ACCESS BY INDEX ROWID  | XLATTABLE_TBL                |     5 |   190 |     2 |
|  10 |         INDEX RANGE SCAN            | XLATTABLE_FIELDNAME_INDX     |     5 |       |     1 |
|  11 |        BUFFER SORT                  |                              |     5 |   190 |    10 |
|  12 |         TABLE ACCESS BY INDEX ROWID | XLATTABLE_TBL                |     5 |   190 |     2 |
|  13 |          INDEX RANGE SCAN           | XLATTABLE_FIELDNAME_INDX     |     5 |       |     1 |
|  14 |       BUFFER SORT                   |                              |  2097 |       |    29 |
|  15 |        BITMAP CONVERSION TO ROWIDS  |                              |  2097 |       |     1 |
|  16 |         BITMAP INDEX FAST FULL SCAN | METRICS_PD_BITMAP_INDX       |       |       |       |
|  17 |    SORT AGGREGATE                   |                              |     1 |    13 |       |
|  18 |     INDEX RANGE SCAN                | ACCT_RT                      |     1 |    13 |     1 |
|  19 |    SORT AGGREGATE                   |                              |     1 |    40 |       |
|  20 |     HASH JOIN                       |                              |     1 |    40 |     8 |
|  21 |      INDEX RANGE SCAN               | ACCT_RT                      |     8 |   104 |     3 |
|  22 |       SORT AGGREGATE                |                              |     1 |    17 |       |
|  23 |        VIEW                         | index$_join$_007             |     6 |   102 |     3 |
|  24 |         HASH JOIN                   |                              |       |       |       |
|  25 |          INDEX RANGE SCAN           | PS_HP_VC_METRICS_PRD_ID_INDX |     6 |   102 |     1 |
|  26 |          BITMAP CONVERSION TO ROWIDS|                              |     6 |   102 |     1 |
|  27 |           BITMAP INDEX FULL SCAN    | METRICS_PD_BITMAP_INDX       |       |       |       |
|  28 |      VIEW                           | VW_SQ_1                      |   156 |  4212 |     4 |
|  29 |       SORT GROUP BY                 |                              |   156 |  2028 |     4 |
|  30 |        INDEX RANGE SCAN             | ACCT_RT                      |   156 |  2028 |     3 |
----------------------------------------------------------------------------------------------------

Note
-----
   - 'PLAN_TABLE' is old version

40 rows selected.



Re: Query not executing [message #502076 is a reply to message #501970] Mon, 04 April 2011 02:26 Go to previous message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
According to those plans production should run a lot faster than test.
So the stats are wrong. Refresh them and see what happens.
Previous Topic: derived column index
Next Topic: Query getting slowed running in Oracle 10g compared to Oracle 9i
Goto Forum:
  


Current Time: Fri Sep 19 09:51:23 CDT 2014

Total time taken to generate the page: 0.05010 seconds