Home » RDBMS Server » Performance Tuning » Performance discrepancies (Oracle 9i/10g Window2000)
Performance discrepancies [message #314067] Tue, 15 April 2008 14:20 Go to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
Hi all,
I'm running this below query between two different environment. They are devl and preprod. Devl is in Oracle 10g and Preprod in 9i.
It takes 2 to 4 sec in devl. But when it comes to preprod it takes more than 200 sec. I tried using different hints like cardinality, ordered, leading and etc.
but nothing help me out. Statistics are collected at same day.

Please find the query below.
SELECT /*+ cardinality(arr 1) cardinality(quotearr 1) leading(arr client)*/
       client.acct_id account,
       sec.sec_id,
       quotearr.usd_price,
       sec.sec_nm
FROM   
       iv.instrument client,
       iv.instrument pf,
       iv.security   sec,
       iv.position_analytic cl_pf,
       iv.position_analytic sec_pf,  
       TABLE(pmw_group_member_arr(pmw_group_member_rec(null,'C','9709',null,null,null))) arr,
       TABLE(pmw_sec_price_arr(pmw_sec_price_rec(null,null,null,null,null))) quotearr
WHERE  
       client.instrument_typ = 25 AND
       pf.instrument_typ = 30 AND
       cl_pf.parent_inst_id = client.inst_id AND
       cl_pf.child_inst_id = pf.inst_id AND
       sec_pf.parent_inst_id = pf.inst_id AND
       sec_pf.child_inst_id = sec.inst_id AND
       (sec_pf.units <> 0 or sec_pf.open_qty <> 0) AND
       sec.sec_id = quotearr.sec_id(+) AND
       client.acct_id = arr.member_id AND
       arr.member_typ = 'C'

Devl plan
=========
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------
| Id  | Operation                                          |  Name               | Rows  | Bytes |TempSpc| Cost  |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                   |                     |    39 | 16497 |       | 14059 |
|*  1 |  HASH JOIN OUTER                                   |                     |    39 | 16497 |       | 14059 |
|   2 |   NESTED LOOPS                                     |                     |    39 | 10725 |       | 14041 |
|   3 |    NESTED LOOPS                                    |                     |    39 | 10608 |       | 14041 |
|   4 |     NESTED LOOPS                                   |                     |    39 |  9516 |       | 13963 |
|*  5 |      HASH JOIN                                     |                     |    39 |  8541 |       | 13885 |
|*  6 |       TABLE ACCESS BY INDEX ROWID                  | POSITION_TBL        |    39 |  1248 |       |     9 |
|   7 |        NESTED LOOPS                                |                     |    39 |  6864 |       |   116 |
|   8 |         NESTED LOOPS                               |                     |     1 |   144 |       |   107 |
|   9 |          NESTED LOOPS                              |                     |     1 |   119 |       |   105 |
|  10 |           NESTED LOOPS                             |                     |     1 |   116 |       |   105 |
|  11 |            NESTED LOOPS                            |                     |     1 |    88 |       |   105 |
|  12 |             NESTED LOOPS                           |                     |    39 |  2340 |       |    27 |
|  13 |              NESTED LOOPS                          |                     |     1 |    32 |       |    18 |
|* 14 |               COLLECTION ITERATOR CONSTRUCTOR FETCH|                     |       |       |       |       |
|* 15 |               TABLE ACCESS BY INDEX ROWID          | INSTRUMENT_TBL      |     1 |    30 |       |     2 |
|* 16 |                INDEX RANGE SCAN                    | INSTRUMENT_IDX3     |     1 |       |       |     1 |
|  17 |              TABLE ACCESS BY INDEX ROWID           | POSITION_TBL        |    39 |  1092 |       |     9 |
|* 18 |               INDEX RANGE SCAN                     | POSITION_IDX1       |    39 |       |       |     2 |
|* 19 |             TABLE ACCESS BY INDEX ROWID            | INSTRUMENT_TBL      |     1 |    28 |       |     2 |
|* 20 |              INDEX UNIQUE SCAN                     | INSTRUMENT_PK       |     1 |       |       |     1 |
|  21 |            TABLE ACCESS BY INDEX ROWID             | INSTRUMENT_TBL      |     1 |    28 |       |       |
|* 22 |             INDEX UNIQUE SCAN                      | INSTRUMENT_PK       |     1 |       |       |     1 |
|* 23 |           INDEX UNIQUE SCAN                        | INSTRUMENT_TYPE_PK  |     1 |     3 |       |       |
|  24 |          TABLE ACCESS BY INDEX ROWID               | INSTRUMENT_TBL      |     1 |    25 |       |     2 |
|* 25 |           INDEX UNIQUE SCAN                        | INSTRUMENT_PK       |     1 |       |       |     1 |
|* 26 |         INDEX RANGE SCAN                           | POSITION_IDX1       |    39 |       |       |     2 |
|  27 |       VIEW                                         | SECURITY            |   531K|    21M|       | 13755 |
|  28 |        NESTED LOOPS                                |                     |   531K|    27M|       | 13755 |
|  29 |         MERGE JOIN                                 |                     |   531K|    25M|       | 13755 |
|  30 |          SORT JOIN                                 |                     |   531K|    16M|    48M|  5701 |
|  31 |           TABLE ACCESS FULL                        | INSTRUMENT_TBL      |   531K|    16M|       |  2462 |
|* 32 |          SORT JOIN                                 |                     |   531K|  9869K|    28M|  8054 |
|  33 |           TABLE ACCESS FULL                        | IV_SECURITY_TBL     |   531K|  9869K|       |  5868 |
|* 34 |         INDEX UNIQUE SCAN                          | INSTRUMENT_TYPE_PK  |     1 |     3 |       |       |
|  35 |      TABLE ACCESS BY INDEX ROWID                   | INSTRUMENT_TBL      |     1 |    25 |       |     2 |
|* 36 |       INDEX UNIQUE SCAN                            | INSTRUMENT_PK       |     1 |       |       |     1 |
|  37 |     TABLE ACCESS BY INDEX ROWID                    | INSTRUMENT_TBL      |     1 |    28 |       |     2 |
|* 38 |      INDEX UNIQUE SCAN                             | INSTRUMENT_PK       |     1 |       |       |     1 |
|* 39 |    INDEX UNIQUE SCAN                               | INSTRUMENT_TYPE_PK  |     1 |     3 |       |       |
|  40 |   VIEW                                             |                     |  8168 |  1180K|       |    16 |
|  41 |    COLLECTION ITERATOR CONSTRUCTOR FETCH           |                     |       |       |       |       |
----------------------------------------------------------------------------------------------------

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

   1 - access("SEC"."SEC_ID"="QUOTEARR"."SYS_NC_ROWINFO$"."SEC_ID"(+))
   5 - access("SYS_ALIAS_10"."INST_ID"="SEC"."INST_ID")
   6 - filter("SYS_ALIAS_10"."UNITS"<>0 OR "SYS_ALIAS_10"."OPEN_QTY_BUY"-"SYS_ALIAS_10"."OPEN_QTY_SE
              _10"."OPEN_QTY_BUY_COVER"-"SYS_ALIAS_10"."OPEN_QTY_SHORT_SELL"<>0)
  14 - filter(SYS_OP_ATG(VALUE(KOKBF$),2,3,2)='C')
  15 - filter("SYS_ALIAS_52"."INSTRUMENT_TYP"=25)
  16 - access("SYS_ALIAS_52"."ACCT_ID"=SYS_OP_ATG(VALUE(KOKBF$),3,4,2))
  18 - access("SYS_ALIAS_32"."PARENT_INST_ID"="SYS_ALIAS_52"."INST_ID")
  19 - filter("SYS_ALIAS_48"."INSTRUMENT_TYP"=30)
  20 - access("SYS_ALIAS_32"."INST_ID"="SYS_ALIAS_48"."INST_ID")
  22 - access("SYS_ALIAS_32"."INST_ID"="SYS_ALIAS_40"."INST_ID")
  23 - access("SYS_ALIAS_40"."INSTRUMENT_TYP"="IT"."INSTRUMENT_TYP")
  25 - access("SYS_ALIAS_32"."PARENT_INST_ID"="SYS_ALIAS_44"."INST_ID")
  26 - access("SYS_ALIAS_10"."PARENT_INST_ID"="SYS_ALIAS_48"."INST_ID")
  32 - access("SYS_ALIAS_4"."INST_ID"="S"."INST_ID")
       filter("SYS_ALIAS_4"."INST_ID"="S"."INST_ID")
  34 - access("SYS_ALIAS_4"."INSTRUMENT_TYP"="IT"."INSTRUMENT_TYP")
  36 - access("SYS_ALIAS_10"."PARENT_INST_ID"="SYS_ALIAS_22"."INST_ID")
  38 - access("SYS_ALIAS_10"."INST_ID"="SYS_ALIAS_18"."INST_ID")
  39 - access("SYS_ALIAS_18"."INSTRUMENT_TYP"="IT"."INSTRUMENT_TYP")

Note: cpu costing is off

73 rows selected.


Preprod Plan
=============
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3813316835

----------------------------------------------------------------------------------------------------
| Id  | Operation                                           | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                    |                    |   277 |   128K|  2318   (1)| 00:00:18 |
|*  1 |  HASH JOIN OUTER                                    |                    |   277 |   128K|  2318   (1)| 00:00:18 |
|   2 |   NESTED LOOPS                                      |                    |   277 | 90302 |  2288   (1)| 00:00:18 |
|   3 |    NESTED LOOPS                                     |                    |   277 | 82546 |  1733   (1)| 00:00:13 |
|   4 |     NESTED LOOPS                                    |                    |   277 | 76729 |  1179   (1)| 00:00:09 |
|   5 |      NESTED LOOPS                                   |                    |   277 | 75621 |  1178   (1)| 00:00:09 |
|   6 |       NESTED LOOPS                                  |                    |   277 | 74513 |  1178   (1)| 00:00:09 |
|   7 |        NESTED LOOPS                                 |                    |   277 | 65649 |   901   (1)| 00:00:07 |
|   8 |         NESTED LOOPS                                |                    |   277 | 55400 |   346   (1)| 00:00:03 |
|   9 |          NESTED LOOPS                               |                    |     3 |   486 |   315   (1)| 00:00:03 |
|  10 |           NESTED LOOPS                              |                    |     3 |   474 |   315   (1)| 00:00:03 |
|  11 |            NESTED LOOPS                             |                    |     3 |   378 |   309   (1)| 00:00:03 |
|  12 |             NESTED LOOPS                            |                    |     3 |   294 |   303   (1)| 00:00:03 |
|  13 |              NESTED LOOPS                           |                    |   122 |  8052 |    59   (2)| 00:00:01 |
|  14 |               NESTED LOOPS                          |                    |     1 |    36 |    32   (4)| 00:00:01 |
|* 15 |                COLLECTION ITERATOR CONSTRUCTOR FETCH|                    |       |       |            |          |
|* 16 |                TABLE ACCESS BY INDEX ROWID          | INSTRUMENT_TBL     |     1 |    34 |     2   (0)| 00:
|* 17 |                 INDEX RANGE SCAN                    | INSTRUMENT_IDX3    |     1 |       |     1   (0)| 00:00:01 |
|  18 |               TABLE ACCESS BY INDEX ROWID           | POSITION_TBL       |   105 |  3150 |    27   (0)| 00:00:
|* 19 |                INDEX RANGE SCAN                     | POSITION_IDX1      |   105 |       |     2   (0)| 00:00:01 |
|* 20 |              TABLE ACCESS BY INDEX ROWID            | INSTRUMENT_TBL     |     1 |    32 |     2   (0)| 00:00
|* 21 |               INDEX UNIQUE SCAN                     | INSTRUMENT_PK      |     1 |       |     1   (0)| 00:00:01 |
|* 22 |             INDEX UNIQUE SCAN                       | INSTRUMENT_PK      |     1 |       |     1   (0)| 00:00:01 |
|  23 |            TABLE ACCESS BY INDEX ROWID              | INSTRUMENT_TBL     |     1 |    32 |     2   (0)| 00:00:
|* 24 |             INDEX UNIQUE SCAN                       | INSTRUMENT_PK      |     1 |       |     1   (0)| 00:00:01 |
|* 25 |           INDEX UNIQUE SCAN                         | INSTRUMENT_TYPE_PK |     1 |     4 |     0   (0)| 00:00:01 |
|* 26 |          TABLE ACCESS BY INDEX ROWID                | POSITION_TBL       |   105 |  3990 |    27   (0)| 00:00:01 |
|* 27 |           INDEX RANGE SCAN                          | POSITION_IDX1      |   105 |       |     2   (0)| 00:00:01 |
|  28 |         TABLE ACCESS BY INDEX ROWID                 | INSTRUMENT_TBL     |     1 |    37 |     2   (0)| 00:00:01 
|* 29 |          INDEX UNIQUE SCAN                          | INSTRUMENT_PK      |     1 |       |     1   (0)| 00:00:01 |
|  30 |        TABLE ACCESS BY INDEX ROWID                  | INSTRUMENT_TBL     |     1 |    32 |     1   (0)| 00:
|* 31 |         INDEX UNIQUE SCAN                           | INSTRUMENT_PK      |     1 |       |     1   (0)| 00:00:01 |
|* 32 |       INDEX UNIQUE SCAN                             | INSTRUMENT_TYPE_PK |     1 |     4 |     0   (0)| 00:00:01 
|* 33 |      INDEX UNIQUE SCAN                              | INSTRUMENT_TYPE_PK |     1 |     4 |     0   (0)| 00:00:01 |
|  34 |     TABLE ACCESS BY INDEX ROWID                     | IV_SECURITY_TBL    |     1 |    21 |     2   (0)| 00:0
|* 35 |      INDEX UNIQUE SCAN                              | IV_SECURITY_TBL_PK |     1 |       |     1   (0)| 00:00:01 |
|* 36 |    INDEX UNIQUE SCAN                                | INSTRUMENT_PK      |     1 |       |     1   (0)| 00:00:01 |
|  37 |   VIEW                                              |                    |  8168 |  1180K|    29   (0)| 00:00:01 |
|  38 |    COLLECTION ITERATOR CONSTRUCTOR FETCH            |                    |       |       |            |          |
----------------------------------------------------------------------------------------------------

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

   1 - access("SEC_ID"="QUOTEARR"."SYS_NC_ROWINFO$"."SEC_ID"(+))
  15 - filter(SYS_OP_ATG(VALUE(KOKBF$),2,3,2)='C')
  16 - filter("INSTRUMENT_TYP"=25)
  17 - access("ACCT_ID"=SYS_OP_ATG(VALUE(KOKBF$),3,4,2))
  19 - access("PARENT_INST_ID"="INST_ID")
  20 - filter("INSTRUMENT_TYP"=30)
  21 - access("INST_ID"="INST_ID")
  22 - access("PARENT_INST_ID"="INST_ID")
  24 - access("INST_ID"="INST_ID")
  25 - access("INSTRUMENT_TYP"="INSTRUMENT_TYP")
  26 - filter("UNITS"<>0 OR "PT"."OPEN_QTY_BUY"-"PT"."OPEN_QTY_SELL"+"PT"."OPEN_QTY_BUY_COVER"-"PT".
              T_SELL"<>0)
  27 - access("PARENT_INST_ID"="INST_ID")
  29 - access("INST_ID"="INST_ID")
  31 - access("INST_ID"="INST_ID")
  32 - access("INSTRUMENT_TYP"="INSTRUMENT_TYP")
  33 - access("INSTRUMENT_TYP"="INSTRUMENT_TYP")
  35 - access("INST_ID"="S"."INST_ID")
  36 - access("PARENT_INST_ID"="INST_ID")

68 rows selected.


But when take that two PL/SQL table out of query like given below, the execution time as well as the plan are same(with minor difference).
  
  SELECT 
         client.acct_id account,
         sec.sec_id,
         sec.sec_nm
  FROM   
         iv.instrument client,
         iv.instrument pf,
         iv.security   sec,
         iv.position_analytic cl_pf,
         iv.position_analytic sec_pf
  WHERE  
         client.instrument_typ = 25 AND
         pf.instrument_typ = 30 AND
         cl_pf.parent_inst_id = client.inst_id AND
         cl_pf.child_inst_id = pf.inst_id AND
         sec_pf.parent_inst_id = pf.inst_id AND
         sec_pf.child_inst_id = sec.inst_id AND
         (sec_pf.units <> 0 or sec_pf.open_qty <> 0) AND
         client.acct_id = '9709'
       
   

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

-----------------------------------------------------------------------------------------------
| Id  | Operation                               |  Name               | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                     |    39 | 11076 |   336 |
|   1 |  NESTED LOOPS                           |                     |    39 | 11076 |   336 |
|   2 |   NESTED LOOPS                          |                     |    39 | 10335 |   258 |
|   3 |    NESTED LOOPS                         |                     |    39 |  9360 |   180 |
|   4 |     NESTED LOOPS                        |                     |    39 |  9243 |   180 |
|   5 |      NESTED LOOPS                       |                     |    39 |  9126 |   180 |
|   6 |       NESTED LOOPS                      |                     |    39 |  8034 |   180 |
|   7 |        NESTED LOOPS                     |                     |    39 |  6786 |   102 |
|   8 |         NESTED LOOPS                    |                     |     1 |   142 |    93 |
|   9 |          NESTED LOOPS                   |                     |     1 |   139 |    93 |
|  10 |           NESTED LOOPS                  |                     |     1 |   111 |    91 |
|  11 |            NESTED LOOPS                 |                     |     1 |    86 |    89 |
|  12 |             NESTED LOOPS                |                     |    39 |  2262 |    11 |
|* 13 |              TABLE ACCESS BY INDEX ROWID| INSTRUMENT_TBL      |     1 |    30 |     2 |
|* 14 |               INDEX RANGE SCAN          | INSTRUMENT_IDX3     |     1 |       |     1 |
|  15 |              TABLE ACCESS BY INDEX ROWID| POSITION_TBL        |    39 |  1092 |     9 |
|* 16 |               INDEX RANGE SCAN          | POSITION_IDX1       |    39 |       |     2 |
|* 17 |             TABLE ACCESS BY INDEX ROWID | INSTRUMENT_TBL      |     1 |    28 |     2 |
|* 18 |              INDEX UNIQUE SCAN          | INSTRUMENT_PK       |     1 |       |     1 |
|  19 |            TABLE ACCESS BY INDEX ROWID  | INSTRUMENT_TBL      |     1 |    25 |     2 |
|* 20 |             INDEX UNIQUE SCAN           | INSTRUMENT_PK       |     1 |       |     1 |
|  21 |           TABLE ACCESS BY INDEX ROWID   | INSTRUMENT_TBL      |     1 |    28 |     2 |
|* 22 |            INDEX UNIQUE SCAN            | INSTRUMENT_PK       |     1 |       |     1 |
|* 23 |          INDEX UNIQUE SCAN              | INSTRUMENT_TYPE_PK  |     1 |     3 |       |
|* 24 |         TABLE ACCESS BY INDEX ROWID     | POSITION_TBL        |    39 |  1248 |     9 |
|* 25 |          INDEX RANGE SCAN               | POSITION_IDX1       |    39 |       |     2 |
|  26 |        TABLE ACCESS BY INDEX ROWID      | INSTRUMENT_TBL      |     1 |    32 |     2 |
|* 27 |         INDEX UNIQUE SCAN               | INSTRUMENT_PK       |     1 |       |     1 |
|  28 |       TABLE ACCESS BY INDEX ROWID       | INSTRUMENT_TBL      |     1 |    28 |       |
|* 29 |        INDEX UNIQUE SCAN                | INSTRUMENT_PK       |     1 |       |     1 |
|* 30 |      INDEX UNIQUE SCAN                  | INSTRUMENT_TYPE_PK  |     1 |     3 |       |
|* 31 |     INDEX UNIQUE SCAN                   | INSTRUMENT_TYPE_PK  |     1 |     3 |       |
|  32 |    TABLE ACCESS BY INDEX ROWID          | INSTRUMENT_TBL      |     1 |    25 |     2 |
|* 33 |     INDEX UNIQUE SCAN                   | INSTRUMENT_PK       |     1 |       |     1 |
|  34 |   TABLE ACCESS BY INDEX ROWID           | IV_SECURITY_TBL     |     1 |    19 |     2 |
|* 35 |    INDEX UNIQUE SCAN                    | IV_SECURITY_TBL_PK  |     1 |       |     1 |
-----------------------------------------------------------------------------------------------

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

  13 - filter("SYS_ALIAS_56"."INSTRUMENT_TYP"=25)
  14 - access("SYS_ALIAS_56"."ACCT_ID"='9709')
  16 - access("SYS_ALIAS_32"."PARENT_INST_ID"="SYS_ALIAS_56"."INST_ID")
  17 - filter("SYS_ALIAS_52"."INSTRUMENT_TYP"=30)
  18 - access("SYS_ALIAS_32"."INST_ID"="SYS_ALIAS_52"."INST_ID")
  20 - access("SYS_ALIAS_32"."PARENT_INST_ID"="SYS_ALIAS_44"."INST_ID")
  22 - access("SYS_ALIAS_32"."INST_ID"="SYS_ALIAS_40"."INST_ID")
  23 - access("SYS_ALIAS_40"."INSTRUMENT_TYP"="IT"."INSTRUMENT_TYP")
  24 - filter("SYS_ALIAS_10"."UNITS"<>0 OR "SYS_ALIAS_10"."OPEN_QTY_BUY"-"SYS_ALIAS_10"."OPEN_Q
              TY_SELL"+"SYS_ALIAS_10"."OPEN_QTY_BUY_COVER"-"SYS_ALIAS_10"."OPEN_QTY_SHORT_SELL"<>0)
  25 - access("SYS_ALIAS_10"."PARENT_INST_ID"="SYS_ALIAS_52"."INST_ID")
  27 - access("SYS_ALIAS_10"."INST_ID"="SYS_ALIAS_48"."INST_ID")
  29 - access("SYS_ALIAS_10"."INST_ID"="SYS_ALIAS_18"."INST_ID")
  30 - access("SYS_ALIAS_48"."INSTRUMENT_TYP"="IT"."INSTRUMENT_TYP")
  31 - access("SYS_ALIAS_18"."INSTRUMENT_TYP"="IT"."INSTRUMENT_TYP")
  33 - access("SYS_ALIAS_10"."PARENT_INST_ID"="SYS_ALIAS_22"."INST_ID")
  35 - access("SYS_ALIAS_48"."INST_ID"="S"."INST_ID")


By the way the number of records are almost same between two environments.
Do you have any input to improve the performance while using PL/SQL table.

Thanks,
Thangam
Re: Performance discrepancies [message #314068 is a reply to message #314067] Tue, 15 April 2008 14:25 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Do a parameter check from dev to preprod.

See what is different in v$parameter.

Check the SGA values, PGA values etc.

Also, you need to see what the table functions are doing themselves. Are they executing queries against table you havnt considered analyzing yet. Are they using lots of inline memory - etc...
Re: Performance discrepancies [message #314069 is a reply to message #314068] Tue, 15 April 2008 14:33 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
I guess, there should not be much diffence between devl and preprod(I dont have an access to check it in preprod).. I am wondering about table function. why it is completly making huge difference??
Re: Performance discrepancies [message #314096 is a reply to message #314067] Tue, 15 April 2008 15:27 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Depends what its doing.

Infact, you are piplining 2 functions into each other.

THey could in themselves be fully fledged batch jobs.

You need to inspect the code for the 3 table functions to get a clue. What objects are they accessing? are those objects fully analyzed? How many rows are they reading etc...


:=

pmw_group_member_arr
pmw_group_member_rec
pmw_sec_price_arr
Re: Performance discrepancies [message #314122 is a reply to message #314096] Tue, 15 April 2008 16:15 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
I did not do anything in pl/sql table. Please see the desc of that records and tables.
CREATE OR REPLACE TYPE pmw_group_member_rec AS OBJECT
(
    pmw_group_id     NUMBER(10),
    member_typ       VARCHAR2(1),
    member_id        VARCHAR2(20),
    member_sym       VARCHAR2(30),
    manager_initials VARCHAR2(4),
    member_nm        VARCHAR2(30)
);

CREATE OR REPLACE TYPE pmw_group_member_arr AS TABLE OF pmw_group_member_rec;


CREATE OR REPLACE TYPE pmw_sec_price_rec AS OBJECT
(
    sec_id        NUMBER,
    usd_price     NUMBER,
    local_price   NUMBER,
    exch_rt       NUMBER,
    cross_exch_rt NUMBER
);

CREATE OR REPLACE TYPE pmw_sec_price_arr AS TABLE OF pmw_sec_price_rec;
Re: Performance discrepancies [message #314123 is a reply to message #314122] Tue, 15 April 2008 16:16 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
BTW, I'm using this pl/sql tables to just pass inputs from front-end.
Re: Performance discrepancies [message #314129 is a reply to message #314067] Tue, 15 April 2008 17:21 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Ok. They are just types.

Check the indexes are the same AND analyzed on

INSTRUMENT_TBL
IV_SECURITY_TBL

You have a full table access on DEV and a an index lookup on PREPROD.
Re: Performance discrepancies [message #314163 is a reply to message #314129] Tue, 15 April 2008 22:23 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The PREPROD plan thinks that it will process just 277 rows. If it is taking 200+ seconds, then one of the row estimates in the plan is pretty wrong.

Trace it and use TKPROF to obtain the ACTUAL row counts at each step of the plan. Recalculate statistics or use CARDINALITY hints as necessary to tell the CBO what the REAL row estimate is.

Ross Leishman
Previous Topic: INDEX ANALYZE
Next Topic: Loading objects in parallel?
Goto Forum:
  


Current Time: Sat Nov 09 19:14:57 CST 2024