Home » RDBMS Server » Performance Tuning » Performance discrepancies (Oracle 9i/10g Window2000)
| Performance discrepancies [message #314067] |
Tue, 15 April 2008 14:20  |
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  |
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 |
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 |
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 |
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 #314129 is a reply to message #314067] |
Tue, 15 April 2008 17:21 |
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 |
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
|
|
|
|
Goto Forum:
Current Time: Fri Jun 28 12:49:05 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
