Home » RDBMS Server » Performance Tuning » Query getting slowed running in Oracle 10g compared to Oracle 9i (Oracle 10g, Linux)
| Query getting slowed running in Oracle 10g compared to Oracle 9i [message #501598] |
Wed, 30 March 2011 01:26  |
ram anand
Messages: 244
Registered: February 2008
Location: india
|
Senior Member |
|
|
Hi,
I am facing slowness for a query and the same query is running for a long time and could not get the results,but the same query when i tried in oracle 9i db ,the query returns the results within 8 mins.
In oracle 10g DB recently we updated the version from 10.2.0.4.0 to 10.2.0.5.0
We suspect that the issue is due to the system parameters ,although we changed the below system parameter's we didn't get expected result.so what could be the cause for this slowness.
sga_max_size 838860800 = 800M
sga_target 838860800 = 800M
pga_aggregate_target 262144000 = 250M
sort_area_size 1048576 = 1024M
undo_retention 4500
db_cache_size 209715200 = 200M
shared_pool_size 432013312 = 412M
show sga;
Total System Global Area 838860800 bytes
Fixed Size 1276476 bytes
Variable Size 444597700 bytes
Database Buffers 385875968 bytes
Redo Buffers 7110656 bytes
pls do the needful and Advice me in this regard.
|
|
|
|
|
|
| Re: Query getting slowed running in Oracle 10g compared to Oracle 9i [message #501615 is a reply to message #501601] |
Wed, 30 March 2011 02:29  |
ram anand
Messages: 244
Registered: February 2008
Location: india
|
Senior Member |
|
|
FYI..
sga_max_size 838860800 = 800M
sga_target 838860800 = 800M
pga_aggregate_target 262144000 = 250M
sort_area_size 1048576 = 1024M
undo_retention 4500
db_cache_size 209715200 = 200M
shared_pool_size 432013312 = 412M
show sga;
Total System Global Area 838860800 bytes
Fixed Size 1276476 bytes
Variable Size 444597700 bytes
Database Buffers 385875968 bytes
Redo Buffers 7110656 bytes
|
|
|
|
|
|
|
|
| Re: Query getting slowed running in Oracle 10g compared to Oracle 9i [message #501877 is a reply to message #501690] |
Fri, 01 April 2011 03:24 |
ram anand
Messages: 244
Registered: February 2008
Location: india
|
Senior Member |
|
|
hi,
First sorry for my late reply and apologize to post invalid data's
Here are the details below for the above scenario (i.e.) the below query gives the result within 3mins in Oracle 9i but in Oracle 10g it takes long time and also didn't get the result,
select count(*) from sample_v_new;
Here the view details and the explain plan for the above query.In the view ORDER BY clause is mandatory.pls do the needful to solve this.
CREATE OR REPLACE FORCE VIEW type_exploded_v (col1,
seq_no,
col3,
max_item
)
AS
SELECT it.col1, it.seq_no, dp.col3 - 1 col3,
it.max_item
FROM tableA it, tableB dp
WHERE col3 > 0
AND col3 <=
NVL ((SELECT MAX (max_items) + 1
FROM tableC
WHERE it.col1 = tableC.col1
AND max_items <> 999
AND col4 NOT IN ('TRK', 'RFS')),
1
); |
|
|
|
| Re: Query getting slowed running in Oracle 10g compared to Oracle 9i [message #501879 is a reply to message #501877] |
Fri, 01 April 2011 03:25 |
ram anand
Messages: 244
Registered: February 2008
Location: india
|
Senior Member |
|
|
continuation of the previous reply...
CREATE OR REPLACE FORCE VIEW sample_v_new
AS
SELECT /*+ ORDERED */
1 fractional_parts, 0 min_resolution, it1.col1 col1,
it1.col3 col2_count, it2.col1 col3,
it2.col3 col4_count, it3.col1 col5,
it3.col3 col6_count, it4.col1 col7,
it4.col3 col8_count, it5.col1 col9,
it5.col3 col10_count, it6.col1 col11,
it6.col3 col12_count, it7.col1 col13,
it7.col3 col14_count, it8.col1 col15,
it8.col3 col16_count, it9.col1 col17,
it9.col3 col18_count, it10.col1 col19,
it10.col3 col20_count,
DECODE (it10.col3, 0, 0, 1) has_bulk,
(it1.col3 * it1.max_item)
+ (it2.col3 * it2.max_item)
+ (it3.col3 * it3.max_item)
+ (it4.col3 * it4.max_item)
+ (it5.col3 * it5.max_item)
+ (it6.col3 * it6.max_item)
+ (it7.col3 * it7.max_item)
+ (it8.col3 * it8.max_item)
+ (it9.col3 * it9.max_item)
+ (it10.col3 * it10.max_item) total_volume,
it1.col1
+ it2.col1
+ it3.col1
+ it4.col1
+ it5.col1
+ it6.col1
+ it7.col1
+ it8.col1
+ it9.col1
+ it10.col1 total_pos_count
FROM type_exploded_v it1,
type_exploded_v it2,
type_exploded_v it3,
type_exploded_v it4,
type_exploded_v it5,
type_exploded_v it6,
type_exploded_v it7,
type_exploded_v it8,
type_exploded_v it9,
type_exploded_v it10
WHERE it1.seq_no = 2
AND it2.seq_no = 3
AND it3.seq_no = 4
AND it4.seq_no = 1
AND it5.seq_no = 7
AND it6.seq_no = 8
AND it7.seq_no = 10
AND it8.seq_no = 9
AND it9.seq_no = 5
AND it10.seq_no = 6
AND (it1.col3 * it1.max_item)
+ (it2.col3 * it2.max_item)
+ (it3.col3 * it3.max_item)
+ (it4.col3 * it4.max_item)
+ (it5.col3 * it5.max_item)
+ (it6.col3 * it6.max_item)
+ (it7.col3 * it7.max_item)
+ (it8.col3 * it8.max_item)
+ (it9.col3 * it9.max_item)
+ (it10.col3 * it10.max_item) < 200
AND it1.col3
+ it2.col3
+ it3.col3
+ it4.col3
+ it9.col3
+ it10.col3 < 32
AND it7.col3 + it8.col3 + it9.col3 < 28
AND it1.col3
+ it2.col3
+ it3.col3
+ it4.col3
+ it5.col3
+ it6.col3
+ it7.col3
+ it8.col3
+ it9.col3 < 52
ORDER BY (it1.col3 * it1.max_item)
+ (it2.col3 * it2.max_item)
+ (it3.col3 * it3.max_item)
+ (it4.col3 * it4.max_item)
+ (it5.col3 * it5.max_item)
+ (it6.col3 * it6.max_item)
+ (it7.col3 * it7.max_item)
+ (it8.col3 * it8.max_item)
+ (it9.col3 * it9.max_item)
+ (it10.col3 * it10.max_item) DESC,
DECODE (it10.col3, 0, 0, 1),
it1.col3
+ it2.col3
+ it3.col3
+ it4.col3
+ it5.col3
+ it6.col3
+ it7.col3
+ it8.col3
+ it9.col3
+ it10.col3 ASC;
|
|
|
|
| Re: Query getting slowed running in Oracle 10g compared to Oracle 9i [message #501882 is a reply to message #501879] |
Fri, 01 April 2011 03:28 |
ram anand
Messages: 244
Registered: February 2008
Location: india
|
Senior Member |
|
|
And here is the explain plan for the below query
select count(*) from sample_v_new;
PLAN_TABLE_OUTPUT
Plan hash value: 383828989
-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 130 | | 2807T (1)|999:59:59 |
| 1 | SORT ORDER BY | | 1 | 130 | 3509T| 2807T (1)|999:59:59 |
|* 2 | FILTER | | | | | | |
| 3 | NESTED LOOPS | | 28T| 3341T| | 2807T (1)|999:59:59 |
| 4 | MERGE JOIN CARTESIAN | | 114T| 12P| | 2781T (1)|999:59:59 |
| 5 | NESTED LOOPS | | 114T| 11P| | 2254T (1)|999:59:59 |
| 6 | MERGE JOIN CARTESIAN | | 461T| 46P| | 2152T (1)|999:59:59 |
| 7 | MERGE JOIN CARTESIAN | | 461T| 42P| | 22T (1)|999:59:59 |
| 8 | MERGE JOIN CARTESIAN | | 4660G| 428T| | 21T (1)|999:59:59 |
| 9 | MERGE JOIN CARTESIAN | | 4660G| 385T| | 230G (1)|999:59:59 |
| 10 | MERGE JOIN CARTESIAN | | 47G| 3858G| | 219G (1)|999:59:59 |
| 11 | MERGE JOIN CARTESIAN | | 47G| 3419G| | 2323M (1)|999:59:59 |
| 12 | MERGE JOIN CARTESIAN | | 475M| 33G| | 2218M (1)|999:59:59 |
| 13 | MERGE JOIN CARTESIAN | | 475M| 28G| | 23M (1)| 78:14:43 |
| 14 | MERGE JOIN CARTESIAN | | 4802K| 283M| | 22M (1)| 74:41:19 |
| 15 | MERGE JOIN CARTESIAN | | 4802K| 238M| | 237K (1)| 00:47:26 |
| 16 | MERGE JOIN CARTESIAN | | 48515 | 2321K| | 226K (1)| 00:45:17 |
| 17 | MERGE JOIN CARTESIAN | | 48515 | 1847K| | 2410 (1)| 00:00:29 |
| 18 | MERGE JOIN CARTESIAN | | 490 | 17640 | | 2299 (1)| 00:00:28 |
| 19 | MERGE JOIN CARTESIAN | | 490 | 12740 | | 35 (0)| 00:00:01 |
| 20 | MERGE JOIN CARTESIAN | | 5 | 115 | | 32 (0)| 00:00:01 |
| 21 | NESTED LOOPS | | 5 | 65 | | 7 (0)| 00:00:01 |
|* 22 | TABLE ACCESS FULL | tableA | 1 | 10 | | 6 (0)| 00:00:01 |
|* 23 | INDEX RANGE SCAN | XPKtableB | 5 | 15 | | 1 (0)| 00:00:01 |
| 24 | SORT AGGREGATE | | 1 | 12 | | | |
|* 25 | TABLE ACCESS BY INDEX ROWID | tableC | 9 | 108 | | 2 (0)| 00:00:01 |
|* 26 | INDEX RANGE SCAN | XIFtableC | 10 | | | 1 (0)| 00:00:01 |
| 27 | BUFFER SORT | | 1 | 10 | | 31 (0)| 00:00:01 |
|* 28 | TABLE ACCESS FULL | tableA | 1 | 10 | | 5 (0)| 00:00:01 |
| 29 | BUFFER SORT | | 99 | 297 | | 30 (0)| 00:00:01 |
|* 30 | INDEX FAST FULL SCAN | XPKtableB | 99 | 297 | | 1 (0)| 00:00:01 |
| 31 | BUFFER SORT | | 1 | 10 | | 2298 (1)| 00:00:28 |
|* 32 | TABLE ACCESS FULL | tableA | 1 | 10 | | 5 (0)| 00:00:01 |
| 33 | BUFFER SORT | | 99 | 297 | | 2405 (1)| 00:00:29 |
|* 34 | INDEX FAST FULL SCAN | XPKtableB | 99 | 297 | | 0 (0)| 00:00:01 |
| 35 | BUFFER SORT | | 1 | 10 | | 226K (1)| 00:45:17 |
|* 36 | TABLE ACCESS FULL | tableA | 1 | 10 | | 5 (0)| 00:00:01 |
| 37 | BUFFER SORT | | 99 | 297 | | 237K (1)| 00:47:26 |
|* 38 | INDEX FAST FULL SCAN | XPKtableB | 99 | 297 | | 0 (0)| 00:00:01 |
| 39 | BUFFER SORT | | 1 | 10 | | 22M (1)| 74:41:19 |
|* 40 | TABLE ACCESS FULL | tableA | 1 | 10 | | 5 (0)| 00:00:01 |
| 41 | BUFFER SORT | | 99 | 297 | | 23M (1)| 78:14:43 |
|* 42 | INDEX FAST FULL SCAN | XPKtableB | 99 | 297 | | 0 (0)| 00:00:01 |
| 43 | BUFFER SORT | | 1 | 10 | | 2218M (1)|999:59:59 |
|* 44 | TABLE ACCESS FULL | tableA | 1 | 10 | | 5 (0)| 00:00:01 |
| 45 | BUFFER SORT | | 99 | 297 | | 2323M (1)|999:59:59 |
|* 46 | INDEX FAST FULL SCAN | XPKtableB | 99 | 297 | | 0 (0)| 00:00:01 |
| 47 | BUFFER SORT | | 1 | 10 | | 219G (1)|999:59:59 |
|* 48 | TABLE ACCESS FULL | tableA | 1 | 10 | | 5 (0)| 00:00:01 |
| 49 | BUFFER SORT | | 99 | 297 | | 230G (1)|999:59:59 |
|* 50 | INDEX FAST FULL SCAN | XPKtableB | 99 | 297 | | 0 (0)| 00:00:01 |
| 51 | BUFFER SORT | | 1 | 10 | | 21T (1)|999:59:59 |
|* 52 | TABLE ACCESS FULL | tableA | 1 | 10 | | 5 (0)| 00:00:01 |
| 53 | BUFFER SORT | | 99 | 297 | | 22T (1)|999:59:59 |
|* 54 | INDEX FAST FULL SCAN | XPKtableB | 99 | 297 | | 0 (0)| 00:00:01 |
| 55 | BUFFER SORT | | 1 | 10 | | 2152T (1)|999:59:59 |
|* 56 | TABLE ACCESS FULL | tableA | 1 | 10 | | 5 (0)| 00:00:01 |
|* 57 | INDEX FAST FULL SCAN | XPKtableB | 1 | 3 | | 0 (0)| 00:00:01 |
| 58 | BUFFER SORT | | 1 | 10 | | 2781T (1)|999:59:59 |
|* 59 | TABLE ACCESS FULL | tableA | 1 | 10 | | 5 (0)| 00:00:01 |
|* 60 | INDEX FAST FULL SCAN | XPKtableB | 1 | 3 | | 0 (0)| 00:00:01 |
| 61 | SORT AGGREGATE | | 1 | 12 | | | |
|* 62 | TABLE ACCESS BY INDEX ROWID | tableC | 9 | 108 | | 2 (0)| 00:00:01 |
|* 63 | INDEX RANGE SCAN | XIFtableC | 10 | | | 1 (0)| 00:00:01 |
| 64 | SORT AGGREGATE | | 1 | 12 | | | |
|* 65 | TABLE ACCESS BY INDEX ROWID | tableC | 9 | 108 | | 2 (0)| 00:00:01 |
|* 66 | INDEX RANGE SCAN | XIFtableC | 10 | | | 1 (0)| 00:00:01 |
| 67 | SORT AGGREGATE | | 1 | 12 | | | |
|* 68 | TABLE ACCESS BY INDEX ROWID | tableC | 9 | 108 | | 2 (0)| 00:00:01 |
|* 69 | INDEX RANGE SCAN | XIFtableC | 10 | | | 1 (0)| 00:00:01 |
| 70 | SORT AGGREGATE | | 1 | 12 | | | |
|* 71 | TABLE ACCESS BY INDEX ROWID | tableC | 9 | 108 | | 2 (0)| 00:00:01 |
|* 72 | INDEX RANGE SCAN | XIFtableC | 10 | | | 1 (0)| 00:00:01 |
| 73 | SORT AGGREGATE | | 1 | 12 | | | |
|* 74 | TABLE ACCESS BY INDEX ROWID | tableC | 9 | 108 | | 2 (0)| 00:00:01 |
|* 75 | INDEX RANGE SCAN | XIFtableC | 10 | | | 1 (0)| 00:00:01 |
| 76 | SORT AGGREGATE | | 1 | 12 | | | |
|* 77 | TABLE ACCESS BY INDEX ROWID | tableC | 9 | 108 | | 2 (0)| 00:00:01 |
|* 78 | INDEX RANGE SCAN | XIFtableC | 10 | | | 1 (0)| 00:00:01 |
| 79 | SORT AGGREGATE | | 1 | 12 | | | |
|* 80 | TABLE ACCESS BY INDEX ROWID | tableC | 9 | 108 | | 2 (0)| 00:00:01 |
|* 81 | INDEX RANGE SCAN | XIFtableC | 10 | | | 1 (0)| 00:00:01 |
| 82 | SORT AGGREGATE | | 1 | 12 | | | |
|* 83 | TABLE ACCESS BY INDEX ROWID | tableC | 9 | 108 | | 2 (0)| 00:00:01 |
|* 84 | INDEX RANGE SCAN | XIFtableC | 10 | | | 1 (0)| 00:00:01 |
| 85 | SORT AGGREGATE | | 1 | 12 | | | |
|* 86 | TABLE ACCESS BY INDEX ROWID | tableC | 9 | 108 | | 2 (0)| 00:00:01 |
|* 87 | INDEX RANGE SCAN | XIFtableC | 10 | | | 1 (0)| 00:00:01 |
| 88 | SORT AGGREGATE | | 1 | 12 | | | |
|* 89 | TABLE ACCESS BY INDEX ROWID | tableC | 9 | 108 | | 2 (0)| 00:00:01 |
|* 90 | INDEX RANGE SCAN | XIFtableC | 10 | | | 1 (0)| 00:00:01 |
| 91 | SORT AGGREGATE | | 1 | 12 | | | |
|* 92 | TABLE ACCESS BY INDEX ROWID | tableC | 9 | 108 | | 2 (0)| 00:00:01 |
|* 93 | INDEX RANGE SCAN | XIFtableC | 10 | | | 1 (0)| 00:00:01 |
| 94 | SORT AGGREGATE | | 1 | 12 | | | |
|* 95 | TABLE ACCESS BY INDEX ROWID | tableC | 9 | 108 | | 2 (0)| 00:00:01 |
|* 96 | INDEX RANGE SCAN | XIFtableC | 10 | | | 1 (0)| 00:00:01 |
| 97 | SORT AGGREGATE | | 1 | 12 | | | |
|* 98 | TABLE ACCESS BY INDEX ROWID | tableC | 9 | 108 | | 2 (0)| 00:00:01 |
|* 99 | INDEX RANGE SCAN | XIFtableC | 10 | | | 1 (0)| 00:00:01 |
| 100 | SORT AGGREGATE | | 1 | 12 | | | |
|*101 | TABLE ACCESS BY INDEX ROWID | tableC | 9 | 108 | | 2 (0)| 00:00:01 |
|*102 | INDEX RANGE SCAN | XIFtableC | 10 | | | 1 (0)| 00:00:01 |
| 103 | SORT AGGREGATE | | 1 | 12 | | | |
|*104 | TABLE ACCESS BY INDEX ROWID | tableC | 9 | 108 | | 2 (0)| 00:00:01 |
|*105 | INDEX RANGE SCAN | XIFtableC | 10 | | | 1 (0)| 00:00:01 |
| 106 | SORT AGGREGATE | | 1 | 12 | | | |
|*107 | D TABLE ACCESS BY INDEX ROWI | tableC | 9 | 108 | | 2 (0)| 00:00:01 |
|*108 | INDEX RANGE SCAN | XIFtableC | 10 | | | 1 (0)| 00:00:01 |
| 109 | SORT AGGREGATE | | 1 | 12 | | | |
|*110 | WID TABLE ACCESS BY INDEX RO | tableC | 9 | 108 | | 2 (0)| 00:00:01 |
|*111 | INDEX RANGE SCAN | XIFtableC | 10 | | | 1 (0)| 00:00:01 |
| 112 | SORT AGGREGATE | | 1 | 12 | | | |
|*113 | ROWID TABLE ACCESS BY INDEX | tableC | 9 | 108 | | 2 (0)| 00:00:01 |
|*114 | INDEX RANGE SCAN | XIFtableC | 10 | | | 1 (0)| 00:00:01 |
| 115 | SORT AGGREGATE | | 1 | 12 | | | |
|*116 | X ROWID TABLE ACCESS BY INDE | tableC | 9 | 108 | | 2 (0)| 00:00:01 |
|*117 | INDEX RANGE SCAN | XIFtableC | 10 | | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("col3"<=NVL( (SELECT MAX("MAX_ITEMS")+1 FROM user."tableC" "tableC" WHERE
"tableC"."col1"=:B1 AND "col4"<>'TRK' AND "col4"<>'RFS' AND "MAX_ITEMS"<>999),1) AND
"col3"<=NVL( (SELECT MAX("MAX_ITEMS")+1 FROM user."tableC" "tableC" WHERE
"tableC"."col1"=:B2 AND "col4"<>'TRK' AND "col4"<>'RFS' AND "MAX_ITEMS"<>999),1) AND
"col3"<=NVL( (SELECT MAX("MAX_ITEMS")+1 FROM user."tableC" "tableC" WHERE
"tableC"."col1"=:B3 AND "col4"<>'TRK' AND "col4"<>'RFS' AND "MAX_ITEMS"<>999),1) AND
"col3"<=NVL( (SELECT MAX("MAX_ITEMS")+1 FROM user."tableC" "tableC" WHERE
"tableC"."col1"=:B4 AND "col4"<>'TRK' AND "col4"<>'RFS' AND "MAX_ITEMS"<>999),1) AND
"col3"<=NVL( (SELECT MAX("MAX_ITEMS")+1 FROM user."tableC" "tableC" WHERE
"tableC"."col1"=:B5 AND "col4"<>'TRK' AND "col4"<>'RFS' AND "MAX_ITEMS"<>999),1) AND
"col3"<=NVL( (SELECT MAX("MAX_ITEMS")+1 FROM user."tableC" "tableC" WHERE
"tableC"."col1"=:B6 AND "col4"<>'TRK' AND "col4"<>'RFS' AND "MAX_ITEMS"<>999),1) AND
"col3"<=NVL( (SELECT MAX("MAX_ITEMS")+1 FROM user."tableC" "tableC" WHERE
"tableC"."col1"=:B7 AND "col4"<>'TRK' AND "col4"<>'RFS' AND "MAX_ITEMS"<>999),1) AND
"col3"<=NVL( (SELECT MAX("MAX_ITEMS")+1 FROM user."tableC" "tableC" WHERE
"tableC"."col1"=:B8 AND "col4"<>'TRK' AND "col4"<>'RFS' AND "MAX_ITEMS"<>999),1) AND
"col3"<=NVL( (SELECT MAX("MAX_ITEMS")+1 FROM user."tableC" "tableC" WHERE
"tableC"."col1"=:B9 AND "col4"<>'TRK' AND "col4"<>'RFS' AND "MAX_ITEMS"<>999),1) AND NVL(
(SELECT MAX("MAX_ITEMS")+1 FROM user."tableC" "tableC" WHERE
"tableC"."col1"=:B10 AND "col4"<>'TRK' AND "col4"<>'RFS' AND "MAX_ITEMS"<>999),1)>0 AND
NVL( (SELECT MAX("MAX_ITEMS")+1 FROM user."tableC" "tableC" WHERE
"tableC"."col1"=:B11 AND "col4"<>'TRK' AND "col4"<>'RFS' AND "MAX_ITEMS"<>999),1)>0 AND
NVL( (SELECT MAX("MAX_ITEMS")+1 FROM user."tableC" "tableC" WHERE
"tableC"."col1"=:B12 AND "col4"<>'TRK' AND "col4"<>'RFS' AND "MAX_ITEMS"<>999),1)>0 AND
NVL( (SELECT MAX("MAX_ITEMS")+1 FROM user."tableC" "tableC" WHERE
"tableC"."col1"=:B13 AND "col4"<>'TRK' AND "col4"<>'RFS' AND "MAX_ITEMS"<>999),1)>0 AND
NVL( (SELECT MAX("MAX_ITEMS")+1 FROM user."tableC" "tableC" WHERE
"tableC"."col1"=:B14 AND "col4"<>'TRK' AND "col4"<>'RFS' AND "MAX_ITEMS"<>999),1)>0 AND
NVL( (SELECT MAX("MAX_ITEMS")+1 FROM user."tableC" "tableC" WHERE
"tableC"."col1"=:B15 AND "col4"<>'TRK' AND "col4"<>'RFS' AND "MAX_ITEMS"<>999),1)>0 AND
NVL( (SELECT MAX("MAX_ITEMS")+1 FROM user."tableC" "tableC" WHERE
"tableC"."col1"=:B16 AND "col4"<>'TR)
22 - filter("IT"."SEQ_NO"=2)
23 - access("col3">0 AND "col3"<=NVL( (SELECT MAX("MAX_ITEMS")+1 FROM user."tableC"
"tableC" WHERE "tableC"."col1"=:B1 AND "col4"<>'TRK' AND "col4"<>'RFS' AND
"MAX_ITEMS"<>999),1))
25 - filter("col4"<>'TRK' AND "col4"<>'RFS' AND "MAX_ITEMS"<>999)
26 - access("tableC"."col1"=:B1)
28 - filter("IT"."SEQ_NO"=3)
30 - filter("col3">0)
32 - filter("IT"."SEQ_NO"=4)
34 - filter("col3">0)
36 - filter("IT"."SEQ_NO"=1)
38 - filter("col3">0)
40 - filter("IT"."SEQ_NO"=7)
42 - filter("col3">0)
44 - filter("IT"."SEQ_NO"=8)
46 - filter("col3">0)
48 - filter("IT"."SEQ_NO"=10)
50 - filter("col3">0)
52 - filter("IT"."SEQ_NO"=9)
54 - filter("col3">0)
56 - filter("IT"."SEQ_NO"=5)
57 - filter("col3">0 AND "DP"."col3"-1+("DP"."col3"-1)+("DP"."col3"-1)<28 AND
"DP"."col3"-1+("DP"."col3"-1)+("DP"."col3"-1)+("DP"."col3"-1)+("DP"."col3"-1)+("DP"."col3"-1)+("DP"."DAYS_P
RIOR"-1)+("DP"."col3"-1)+("DP"."col3"-1)<52)
59 - filter("IT"."SEQ_NO"=6)
60 - filter("col3">0 AND ("DP"."col3"-1)*"IT"."max_item"+("DP"."col3"-1)*"IT"."max_item"+("
DP"."col3"-1)*"IT"."max_item"+("DP"."col3"-1)*"IT"."max_item"+("DP"."col3"-1)*"IT"."MAX_ITEM"+("DP"."col3"-1)*"IT"."max_item"+("DP"."col3"-1)*"IT"."max_item"+("DP"."col3"
-1)*"IT"."max_item"+("DP"."col3"-1)*"IT"."max_item"+("DP"."col3"-1)*"IT"."max_item"
<200 AND "DP"."col3"-1+("DP"."col3"-1)+("DP"."col3"-1)+("DP"."col3"-1)+("DP"."col3"-1)+("DP"."col3"-1)<32)
62 - filter("col4"<>'TRK' AND "col4"<>'RFS' AND "MAX_ITEMS"<>999)
63 - access("tableC"."col1"=:B1)
65 - filter("col4"<>'TRK' AND "col4"<>'RFS' AND "MAX_ITEMS"<>999)
66 - access("tableC"."col1"=:B1)
68 - filter("col4"<>'TRK' AND "col4"<>'RFS' AND "MAX_ITEMS"<>999)
69 - access("tableC"."col1"=:B1)
71 - filter("col4"<>'TRK' AND "col4"<>'RFS' AND "MAX_ITEMS"<>999)
72 - access("tableC"."col1"=:B1)
74 - filter("col4"<>'TRK' AND "col4"<>'RFS' AND "MAX_ITEMS"<>999)
75 - access("tableC"."col1"=:B1)
77 - filter("col4"<>'TRK' AND "col4"<>'RFS' AND "MAX_ITEMS"<>999)
78 - access("tableC"."col1"=:B1)
80 - filter("col4"<>'TRK' AND "col4"<>'RFS' AND "MAX_ITEMS"<>999)
81 - access("tableC"."col1"=:B1)
83 - filter("col4"<>'TRK' AND "col4"<>'RFS' AND "MAX_ITEMS"<>999)
84 - access("tableC"."col1"=:B1)
86 - filter("col4"<>'TRK' AND "col4"<>'RFS' AND "MAX_ITEMS"<>999)
87 - access("tableC"."col1"=:B1)
89 - filter("col4"<>'TRK' AND "col4"<>'RFS' AND "MAX_ITEMS"<>999)
90 - access("tableC"."col1"=:B1)
92 - filter("col4"<>'TRK' AND "col4"<>'RFS' AND "MAX_ITEMS"<>999)
93 - access("tableC"."col1"=:B1)
95 - filter("col4"<>'TRK' AND "col4"<>'RFS' AND "MAX_ITEMS"<>999)
96 - access("tableC"."col1"=:B1)
98 - filter("col4"<>'TRK' AND "col4"<>'RFS' AND "MAX_ITEMS"<>999)
99 - access("tableC"."col1"=:B1)
101 - filter("col4"<>'TRK' AND "col4"<>'RFS' AND "MAX_ITEMS"<>999)
102 - access("tableC"."col1"=:B1)
104 - filter("col4"<>'TRK' AND "col4"<>'RFS' AND "MAX_ITEMS"<>999)
105 - access("tableC"."col1"=:B1)
107 - filter("col4"<>'TRK' AND "col4"<>'RFS' AND "MAX_ITEMS"<>999)
108 - access("tableC"."col1"=:B1)
110 - filter("col4"<>'TRK' AND "col4"<>'RFS' AND "MAX_ITEMS"<>999)
111 - access("tableC"."col1"=:B1)
113 - filter("col4"<>'TRK' AND "col4"<>'RFS' AND "MAX_ITEMS"<>999)
114 - access("tableC"."col1"=:B1)
116 - filter("col4"<>'TRK' AND "col4"<>'RFS' AND "MAX_ITEMS"<>999)
117 - access("tableC"."col1"=:B1) |
|
|
|
|
|
|
|
|
|
|
|
| Re: Query getting slowed running in Oracle 10g compared to Oracle 9i [message #501895 is a reply to message #501891] |
Fri, 01 April 2011 05:33 |
ram anand
Messages: 244
Registered: February 2008
Location: india
|
Senior Member |
|
|
The below is the explain plan taken from local oracle server version 10.2.0.1.0
PLAN_TABLE_OUTPUT
Plan hash value: 2005503222
-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 130 | 1004K (1)| 03:20:56 |
| 1 | SORT ORDER BY | | 1 | 130 | 1004K (1)| 03:20:56 |
|* 2 | FILTER | | | | | |
| 3 | NESTED LOOPS | | 55 | 7150 | 1004K (1)| 03:20:54 |
| 4 | MERGE JOIN CARTESIAN | | 4461 | 553K| 1004K (1)| 03:20:54 |
| 5 | NESTED LOOPS | | 4461 | 509K| 994K (1)| 03:18:56 |
| 6 | MERGE JOIN CARTESIAN | | 360K| 39M| 994K (1)| 03:18:51 |
| 7 | NESTED LOOPS | | 360K| 35M| 200K (1)| 00:40:12 |
| 8 | MERGE JOIN CARTESIAN | | 72818 | 7182K| 200K (1)| 00:40:11 |
| 9 | NESTED LOOPS | | 72818 | 6471K| 40611 (1)| 00:08:08 |
| 10 | MERGE JOIN CARTESIAN | | 14711 | 1264K| 40592 (1)| 00:08:08 |
| 11 | NESTED LOOPS | | 14711 | 1120K| 8216 (1)| 00:01:39 |
| 12 | MERGE JOIN CARTESIAN | | 2972 | 217K| 8211 (1)| 00:01:39 |
| 13 | NESTED LOOPS | | 2972 | 188K| 1669 (1)| 00:00:21 |
| 14 | MERGE JOIN CARTESIAN | | 600 | 37200 | 1667 (1)| 00:00:21 |
| 15 | NESTED LOOPS | | 600 | 31200 | 345 (1)| 00:00:05 |
| 16 | MERGE JOIN CARTESIAN | | 121 | 5929 | 344 (1)| 00:00:05 |
| 17 | NESTED LOOPS | | 121 | 4719 | 76 (0)| 00:00:01 |
| 18 | MERGE JOIN CARTESIAN | | 25 | 900 | 75 (0)| 00:00:01 |
| 19 | NESTED LOOPS | | 25 | 650 | 19 (0)| 00:00:01 |
| 20 | MERGE JOIN CARTESIAN | | 5 | 115 | 18 (0)| 00:00:01 |
| 21 | NESTED LOOPS | | 5 | 65 | 5 (0)| 00:00:01 |
|* 22 | TABLE ACCESS FULL | tableA | 1 | 10 | 4 (0)| 00:00:01 |
|* 23 | INDEX RANGE SCAN | XPKcol3 | 5 | 15 | 1 (0)| 00:00:01 |
| 24 | SORT AGGREGATE | | 1 | 12 | | |
|* 25 | TABLE ACCESS BY INDEX ROWID| tableC | 16 | 192 | 2 (0)| 00:00:01 |
|* 26 | INDEX RANGE SCAN | XIFtableC | 20 | | 1 (0)| 00:00:01 |
| 27 | BUFFER SORT | | 1 | 10 | 17 (0)| 00:00:01 |
|* 28 | TABLE ACCESS FULL | tableA | 1 | 10 | 3 (0)| 00:00:01 |
|* 29 | INDEX RANGE SCAN | XPKcol3 | 5 | 15 | 1 (0)| 00:00:01 |
| 30 | SORT AGGREGATE | | 1 | 12 | | |
|* 31 | TABLE ACCESS BY INDEX ROWID | tableC | 16 | 192 | 2 (0)| 00:00:01 |
|* 32 | INDEX RANGE SCAN | XIFtableC | 20 | | 1 (0)| 00:00:01 |
| 33 | BUFFER SORT | | 1 | 10 | 74 (0)| 00:00:01 |
|* 34 | TABLE ACCESS FULL | tableA | 1 | 10 | 2 (0)| 00:00:01 |
|* 35 | INDEX RANGE SCAN | XPKcol3 | 5 | 15 | 1 (0)| 00:00:01 |
| 36 | SORT AGGREGATE | | 1 | 12 | | |
|* 37 | TABLE ACCESS BY INDEX ROWID | tableC | 16 | 192 | 2 (0)| 00:00:01 |
|* 38 | INDEX RANGE SCAN | XIFtableC | 20 | | 1 (0)| 00:00:01 |
| 39 | BUFFER SORT | | 1 | 10 | 343 (1)| 00:00:05 |
|* 40 | TABLE ACCESS FULL | tableA | 1 | 10 | 2 (0)| 00:00:01 |
|* 41 | INDEX RANGE SCAN | XPKcol3 | 5 | 15 | 1 (0)| 00:00:01 |
| 42 | SORT AGGREGATE | | 1 | 12 | | |
|* 43 | TABLE ACCESS BY INDEX ROWID | tableC | 16 | 192 | 2 (0)| 00:00:01 |
|* 44 | INDEX RANGE SCAN | XIFtableC | 20 | | 1 (0)| 00:00:01 |
| 45 | BUFFER SORT | | 1 | 10 | 1666 (1)| 00:00:20 |
|* 46 | TABLE ACCESS FULL | tableA | 1 | 10 | 2 (0)| 00:00:01 |
|* 47 | INDEX RANGE SCAN | XPKcol3 | 5 | 15 | 1 (0)| 00:00:01 |
| 48 | SORT AGGREGATE | | 1 | 12 | | |
|* 49 | TABLE ACCESS BY INDEX ROWID | tableC | 16 | 192 | 2 (0)| 00:00:01 |
|* 50 | INDEX RANGE SCAN | XIFtableC | 20 | | 1 (0)| 00:00:01 |
| 51 | BUFFER SORT | | 1 | 10 | 8210 (1)| 00:01:39 |
|* 52 | TABLE ACCESS FULL | tableA | 1 | 10 | 2 (0)| 00:00:01 |
|* 53 | INDEX RANGE SCAN | XPKcol3 | 5 | 15 | 1 (0)| 00:00:01 |
| 54 | SORT AGGREGATE | | 1 | 12 | | |
|* 55 | TABLE ACCESS BY INDEX ROWID | tableC | 16 | 192 | 2 (0)| 00:00:01 |
|* 56 | INDEX RANGE SCAN | XIFtableC | 20 | | 1 (0)| 00:00:01 |
| 57 | BUFFER SORT | | 1 | 10 | 40591 (1)| 00:08:08 |
|* 58 | TABLE ACCESS FULL | tableA | 1 | 10 | 2 (0)| 00:00:01 |
|* 59 | INDEX RANGE SCAN | XPKcol3 | 5 | 15 | 1 (0)| 00:00:01 |
| 60 | SORT AGGREGATE | | 1 | 12 | | |
|* 61 | TABLE ACCESS BY INDEX ROWID | tableC | 16 | 192 | 2 (0)| 00:00:01 |
|* 62 | INDEX RANGE SCAN | XIFtableC | 20 | | 1 (0)| 00:00:01 |
| 63 | BUFFER SORT | | 1 | 10 | 200K (1)| 00:40:11 |
|* 64 | TABLE ACCESS FULL | tableA | 1 | 10 | 2 (0)| 00:00:01 |
|* 65 | INDEX RANGE SCAN | XPKcol3 | 5 | 15 | 1 (0)| 00:00:01 |
| 66 | SORT AGGREGATE | | 1 | 12 | | |
|* 67 | TABLE ACCESS BY INDEX ROWID | tableC | 16 | 192 | 2 (0)| 00:00:01 |
|* 68 | INDEX RANGE SCAN | XIFtableC | 20 | | 1 (0)| 00:00:01 |
| 69 | BUFFER SORT | | 1 | 10 | 994K (1)| 03:18:51 |
|* 70 | TABLE ACCESS FULL | tableA | 1 | 10 | 2 (0)| 00:00:01 |
|* 71 | INDEX RANGE SCAN | XPKcol3 | 1 | 3 | 1 (0)| 00:00:01 |
| 72 | SORT AGGREGATE | | 1 | 12 | | |
|* 73 | TABLE ACCESS BY INDEX ROWID | tableC | 16 | 192 | 2 (0)| 00:00:01 |
|* 74 | INDEX RANGE SCAN | XIFtableC | 20 | | 1 (0)| 00:00:01 |
| 75 | BUFFER SORT | | 1 | 10 | 1004K (1)| 03:20:54 |
|* 76 | TABLE ACCESS FULL | tableA | 1 | 10 | 2 (0)| 00:00:01 |
|* 77 | INDEX RANGE SCAN | XPKcol3 | 1 | 3 | 1 (0)| 00:00:01 |
| 78 | SORT AGGREGATE | | 1 | 12 | | |
|* 79 | TABLE ACCESS BY INDEX ROWID | tableC | 16 | 192 | 2 (0)| 00:00:01 |
|* 80 | INDEX RANGE SCAN | XIFtableC | 20 | | 1 (0)| 00:00:01 |
| 81 | SORT AGGREGATE | | 1 | 12 | | |
|* 82 | TABLE ACCESS BY INDEX ROWID | tableC | 16 | 192 | 2 (0)| 00:00:01 |
|* 83 | INDEX RANGE SCAN | XIFtableC | 20 | | 1 (0)| 00:00:01 |
| 84 | SORT AGGREGATE | | 1 | 12 | | |
|* 85 | TABLE ACCESS BY INDEX ROWID | tableC | 16 | 192 | 2 (0)| 00:00:01 |
|* 86 | INDEX RANGE SCAN | XIFtableC | 20 | | 1 (0)| 00:00:01 |
| 87 | SORT AGGREGATE | | 1 | 12 | | |
|* 88 | TABLE ACCESS BY INDEX ROWID | tableC | 16 | 192 | 2 (0)| 00:00:01 |
|* 89 | INDEX RANGE SCAN | XIFtableC | 20 | | 1 (0)| 00:00:01 |
| 90 | SORT AGGREGATE | | 1 | 12 | | |
|* 91 | TABLE ACCESS BY INDEX ROWID | tableC | 16 | 192 | 2 (0)| 00:00:01 |
|* 92 | INDEX RANGE SCAN | XIFtableC | 20 | | 1 (0)| 00:00:01 |
| 93 | SORT AGGREGATE | | 1 | 12 | | |
|* 94 | TABLE ACCESS BY INDEX ROWID | tableC | 16 | 192 | 2 (0)| 00:00:01 |
|* 95 | INDEX RANGE SCAN | XIFtableC | 20 | | 1 (0)| 00:00:01 |
| 96 | SORT AGGREGATE | | 1 | 12 | | |
|* 97 | TABLE ACCESS BY INDEX ROWID | tableC | 16 | 192 | 2 (0)| 00:00:01 |
|* 98 | INDEX RANGE SCAN | XIFtableC | 20 | | 1 (0)| 00:00:01 |
| 99 | SORT AGGREGATE | | 1 | 12 | | |
|*100 | TABLE ACCESS BY INDEX ROWID | tableC | 16 | 192 | 2 (0)| 00:00:01 |
|*101 | INDEX RANGE SCAN | XIFtableC | 20 | | 1 (0)| 00:00:01 |
| 102 | SORT AGGREGATE | | 1 | 12 | | |
|*103 | TABLE ACCESS BY INDEX ROWID | tableC | 16 | 192 | 2 (0)| 00:00:01 |
|*104 | INDEX RANGE SCAN | XIFtableC | 20 | | 1 (0)| 00:00:01 |
| 105 | SORT AGGREGATE | | 1 | 12 | | |
|*106 | TABLE ACCESS BY INDEX ROWID | tableC | 16 | 192 | 2 (0)| 00:00:01 |
|*107 | INDEX RANGE SCAN | XIFtableC | 20 | | 1 (0)| 00:00:01 |
| 108 | SORT AGGREGATE | | 1 | 12 | | |
|*109 | TABLE ACCESS BY INDEX ROWID | tableC | 16 | 192 | 2 (0)| 00:00:01 |
|*110 | INDEX RANGE SCAN | XIFtableC | 20 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(NVL( (SELECT MAX("max_items")+1 FROM user."tableC" "tableC" WHERE
"tableC"."col1"=:B1 AND "col4"<>'RFS' AND "col4"<>'TRK' AND
"max_items"<>999),1)>0 AND NVL( (SELECT MAX("max_items")+1 FROM user."tableC"
"tableC" WHERE "tableC"."col1"=:B2 AND "col4"<>'RFS' AND
"col4"<>'TRK' AND "max_items"<>999),1)>0 AND NVL( (SELECT MAX("max_items")+1 FROM
user."tableC" "tableC" WHERE "tableC"."col1"=:B3 AND
"col4"<>'RFS' AND "col4"<>'TRK' AND "max_items"<>999),1)>0 AND NVL( (SELECT
MAX("max_items")+1 FROM user."tableC" "tableC" WHERE
"tableC"."col1"=:B4 AND "col4"<>'RFS' AND "col4"<>'TRK' AND
"max_items"<>999),1)>0 AND NVL( (SELECT MAX("max_items")+1 FROM user."tableC"
"tableC" WHERE "tableC"."col1"=:B5 AND "col4"<>'RFS' AND
"col4"<>'TRK' AND "max_items"<>999),1)>0 AND NVL( (SELECT MAX("max_items")+1 FROM
user."tableC" "tableC" WHERE "tableC"."col1"=:B6 AND
"col4"<>'RFS' AND "col4"<>'TRK' AND "max_items"<>999),1)>0 AND NVL( (SELECT
MAX("max_items")+1 FROM user."tableC" "tableC" WHERE
"tableC"."col1"=:B7 AND "col4"<>'RFS' AND "col4"<>'TRK' AND
"max_items"<>999),1)>0 AND NVL( (SELECT MAX("max_items")+1 FROM user."tableC"
"tableC" WHERE "tableC"."col1"=:B8 AND "col4"<>'RFS' AND
"col4"<>'TRK' AND "max_items"<>999),1)>0 AND NVL( (SELECT MAX("max_items")+1 FROM
user."tableC" "tableC" WHERE "tableC"."col1"=:B9 AND
"col4"<>'RFS' AND "col4"<>'TRK' AND "max_items"<>999),1)>0 AND NVL( (SELECT
MAX("max_items")+1 FROM user."tableC" "tableC" WHERE
"tableC"."col1"=:B10 AND "col4"<>'RFS' AND "col4"<>'TRK' AND
"max_items"<>999),1)>0)
22 - filter("IT"."SEQ_NO"=2)
23 - access("col3">0 AND "col3"<=NVL( (SELECT MAX("max_items")+1 FROM user."tableC"
"tableC" WHERE "tableC"."col1"=:B1 AND "col4"<>'RFS' AND
"col4"<>'TRK' AND "max_items"<>999),1))
25 - filter("col4"<>'RFS' AND "col4"<>'TRK' AND "max_items"<>999)
26 - access("tableC"."col1"=:B1)
28 - filter("IT"."SEQ_NO"=3)
29 - access("col3">0 AND "col3"<=NVL( (SELECT MAX("max_items")+1 FROM user."tableC"
"tableC" WHERE "tableC"."col1"=:B1 AND "col4"<>'RFS' AND
"col4"<>'TRK' AND "max_items"<>999),1))
31 - filter("col4"<>'RFS' AND "col4"<>'TRK' AND "max_items"<>999)
32 - access("tableC"."col1"=:B1)
34 - filter("IT"."SEQ_NO"=4)
35 - access("col3">0 AND "col3"<=NVL( (SELECT MAX("max_items")+1 FROM user."tableC"
"tableC" WHERE "tableC"."col1"=:B1 AND "col4"<>'RFS' AND
"col4"<>'TRK' AND "max_items"<>999),1))
37 - filter("col4"<>'RFS' AND "col4"<>'TRK' AND "max_items"<>999)
38 - access("tableC"."col1"=:B1)
40 - filter("IT"."SEQ_NO"=1)
41 - access("col3">0 AND "col3"<=NVL( (SELECT MAX("max_items")+1 FROM user."tableC"
"tableC" WHERE "tableC"."col1"=:B1 AND "col4"<>'RFS' AND
"col4"<>'TRK' AND "max_items"<>999),1))
43 - filter("col4"<>'RFS' AND "col4"<>'TRK' AND "max_items"<>999)
44 - access("tableC"."col1"=:B1)
46 - filter("IT"."SEQ_NO"=7)
47 - access("col3">0 AND "col3"<=NVL( (SELECT MAX("max_items")+1 FROM user."tableC"
"tableC" WHERE "tableC"."col1"=:B1 AND "col4"<>'RFS' AND
"col4"<>'TRK' AND "max_items"<>999),1))
49 - filter("col4"<>'RFS' AND "col4"<>'TRK' AND "max_items"<>999)
50 - access("tableC"."col1"=:B1)
52 - filter("IT"."SEQ_NO"=8)
53 - access("col3">0 AND "col3"<=NVL( (SELECT MAX("max_items")+1 FROM user."tableC"
"tableC" WHERE "tableC"."col1"=:B1 AND "col4"<>'RFS' AND
"col4"<>'TRK' AND "max_items"<>999),1))
55 - filter("col4"<>'RFS' AND "col4"<>'TRK' AND "max_items"<>999)
56 - access("tableC"."col1"=:B1)
58 - filter("IT"."SEQ_NO"=10)
59 - access("col3">0 AND "col3"<=NVL( (SELECT MAX("max_items")+1 FROM user."tableC"
"tableC" WHERE "tableC"."col1"=:B1 AND "col4"<>'RFS' AND
"col4"<>'TRK' AND "max_items"<>999),1))
61 - filter("col4"<>'RFS' AND "col4"<>'TRK' AND "max_items"<>999)
62 - access("tableC"."col1"=:B1)
64 - filter("IT"."SEQ_NO"=9)
65 - access("col3">0 AND "col3"<=NVL( (SELECT MAX("max_items")+1 FROM user."tableC"
"tableC" WHERE "tableC"."col1"=:B1 AND "col4"<>'RFS' AND
"col4"<>'TRK' AND "max_items"<>999),1))
67 - filter("col4"<>'RFS' AND "col4"<>'TRK' AND "max_items"<>999)
68 - access("tableC"."col1"=:B1)
70 - filter("IT"."SEQ_NO"=5)
71 - access("col3">0 AND "col3"<=NVL( (SELECT MAX("max_items")+1 FROM user."tableC"
"tableC" WHERE "tableC"."col1"=:B1 AND "col4"<>'RFS' AND
"col4"<>'TRK' AND "max_items"<>999),1))
filter("DP"."col3"-1+("DP"."col3"-1)+("DP"."col3"-1)<28 AND
"DP"."col3"-1+("DP"."col3"-1)+("DP"."col3"-1)+("DP"."col3"-1)+("DP"."col3"-1)+("DP"."col3
"-1)+("DP"."col3"-1)+("DP"."col3"-1)+("DP"."col3"-1)<52)
73 - filter("col4"<>'RFS' AND "col4"<>'TRK' AND "max_items"<>999)
74 - access("tableC"."col1"=:B1)
76 - filter("IT"."SEQ_NO"=6)
77 - access("col3">0 AND "col3"<=NVL( (SELECT MAX("max_items")+1 FROM user."tableC"
"tableC" WHERE "tableC"."col1"=:B1 AND "col4"<>'RFS' AND
"col4"<>'TRK' AND "max_items"<>999),1))
filter(("DP"."col3"-1)*"IT"."max_item"+("DP"."col3"-1)*"IT"."max_item"+("D
P"."col3"-1)*"IT"."max_item"+("DP"."col3"-1)*"IT"."max_item"+("DP"."col3"-1
)*"IT"."max_item"+("DP"."col3"-1)*"IT"."max_item"+("DP"."col3"-1)*"IT"."MAX_PAYLO
AD_VOL_PER_ITEM"+("DP"."col3"-1)*"IT"."max_item"+("DP"."col3"-1)*"IT"."max_item"+
("DP"."col3"-1)*"IT"."max_item"<200 AND "DP"."col3"-1+("DP"."col3"-1)+("DP"."col3"-1)
+("DP"."col3"-1)+("DP"."col3"-1)+("DP"."col3"-1)<32)
79 - filter("col4"<>'RFS' AND "col4"<>'TRK' AND "max_items"<>999)
80 - access("tableC"."col1"=:B1)
82 - filter("col4"<>'RFS' AND "col4"<>'TRK' AND "max_items"<>999)
83 - access("tableC"."col1"=:B1)
85 - filter("col4"<>'RFS' AND "col4"<>'TRK' AND "max_items"<>999)
86 - access("tableC"."col1"=:B1)
88 - filter("col4"<>'RFS' AND "col4"<>'TRK' AND "max_items"<>999)
89 - access("tableC"."col1"=:B1)
91 - filter("col4"<>'RFS' AND "col4"<>'TRK' AND "max_items"<>999)
92 - access("tableC"."col1"=:B1)
94 - filter("col4"<>'RFS' AND "col4"<>'TRK' AND "max_items"<>999)
95 - access("tableC"."col1"=:B1)
97 - filter("col4"<>'RFS' AND "col4"<>'TRK' AND "max_items"<>999)
98 - access("tableC"."col1"=:B1)
100 - filter("col4"<>'RFS' AND "col4"<>'TRK' AND "max_items"<>999)
101 - access("tableC"."col1"=:B1)
103 - filter("col4"<>'RFS' AND "col4"<>'TRK' AND "max_items"<>999)
104 - access("tableC"."col1"=:B1)
106 - filter("col4"<>'RFS' AND "col4"<>'TRK' AND "max_items"<>999)
107 - access("tableC"."col1"=:B1)
109 - filter("col4"<>'RFS' AND "col4"<>'TRK' AND "max_items"<>999)
110 - access("tableC"."col1"=:B1) |
|
|
|
|
|
| Re: Query getting slowed running in Oracle 10g compared to Oracle 9i [message #501912 is a reply to message #501896] |
Fri, 01 April 2011 06:59 |
ram anand
Messages: 244
Registered: February 2008
Location: india
|
Senior Member |
|
|
yeah comparing the Oracle 10.2.0.5.0 and oracle 10.2.0.1.0 the stats plan are different ,but the data volume are same, though the same query i tried in oracle 9i it gives better results see the explain plan result of oracle 9i below
The main concern from my side why this same query gets dumped while running in the oracle version 10.2.0.5.0 ,whether we need to optimize the query or we need to concentrate on system parameter's ,pls help on this
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 214 | 72 |
| 1 | VIEW | sample_v_new | 1 | 214 | 72 |
| 2 | SORT ORDER BY | | 1 | 130 | 52 |
|* 3 | FILTER | | | | |
| 4 | NESTED LOOPS | | 1 | 130 | 50 |
| 5 | MERGE JOIN CARTESIAN | | 1 | 127 | 49 |
| 6 | NESTED LOOPS | | 1 | 117 | 45 |
| 7 | MERGE JOIN CARTESIAN | | 1 | 114 | 44 |
| 8 | NESTED LOOPS | | 1 | 104 | 40 |
| 9 | MERGE JOIN CARTESIAN | | 1 | 101 | 39 |
| 10 | NESTED LOOPS | | 1 | 91 | 35 |
| 11 | MERGE JOIN CARTESIAN | | 1 | 88 | 34 |
| 12 | NESTED LOOPS | | 1 | 78 | 30 |
| 13 | MERGE JOIN CARTESIAN | | 1 | 75 | 29 |
| 14 | NESTED LOOPS | | 1 | 65 | 25 |
| 15 | MERGE JOIN CARTESIAN | | 1 | 62 | 24 |
| 16 | NESTED LOOPS | | 1 | 52 | 20 |
| 17 | MERGE JOIN CARTESIAN | | 1 | 49 | 19 |
| 18 | NESTED LOOPS | | 1 | 39 | 15 |
| 19 | MERGE JOIN CARTESIAN | | 1 | 36 | 14 |
| 20 | NESTED LOOPS | | 1 | 26 | 10 |
| 21 | MERGE JOIN CARTESIAN | | 1 | 23 | 9 |
| 22 | NESTED LOOPS | | 1 | 13 | 5 |
|* 23 | TABLE ACCESS FULL | tableA | 1 | 10 | 4 |
|* 24 | INDEX RANGE SCAN | XPKcol3 | 5 | 15 | 1 |
| 25 | SORT AGGREGATE | | 1 | 12 | |
|* 26 | TABLE ACCESS BY INDEX ROWID| tableC | 9 | 108 | 2 |
|* 27 | INDEX RANGE SCAN | XIFtableC | 10 | | 1 |
| 28 | BUFFER SORT | | 1 | 10 | 8 |
|* 29 | TABLE ACCESS FULL | tableA | 1 | 10 | 4 |
|* 30 | INDEX RANGE SCAN | XPKcol3 | 5 | 15 | 1 |
| 31 | SORT AGGREGATE | | 1 | 12 | |
|* 32 | TABLE ACCESS BY INDEX ROWID | tableC | 9 | 108 | 2 |
|* 33 | INDEX RANGE SCAN | XIFtableC | 10 | | 1 |
| 34 | BUFFER SORT | | 1 | 10 | 13 |
|* 35 | TABLE ACCESS FULL | tableA | 1 | 10 | 4 |
|* 36 | INDEX RANGE SCAN | XPKcol3 | 5 | 15 | 1 |
| 37 | SORT AGGREGATE | | 1 | 12 | |
|* 38 | TABLE ACCESS BY INDEX ROWID | tableC | 9 | 108 | 2 |
|* 39 | INDEX RANGE SCAN | XIFtableC | 10 | | 1 |
| 40 | BUFFER SORT | | 1 | 10 | 18 |
|* 41 | TABLE ACCESS FULL | tableA | 1 | 10 | 4 |
|* 42 | INDEX RANGE SCAN | XPKcol3 | 5 | 15 | 1 |
| 43 | SORT AGGREGATE | | 1 | 12 | |
|* 44 | TABLE ACCESS BY INDEX ROWID | tableC | 9 | 108 | 2 |
|* 45 | INDEX RANGE SCAN | XIFtableC | 10 | | 1 |
| 46 | BUFFER SORT | | 1 | 10 | 23 |
|* 47 | TABLE ACCESS FULL | tableA | 1 | 10 | 4 |
|* 48 | INDEX RANGE SCAN | XPKcol3 | 5 | 15 | 1 |
| 49 | SORT AGGREGATE | | 1 | 12 | |
|* 50 | TABLE ACCESS BY INDEX ROWID | tableC | 9 | 108 | 2 |
|* 51 | INDEX RANGE SCAN | XIFtableC | 10 | | 1 |
| 52 | BUFFER SORT | | 1 | 10 | 28 |
|* 53 | TABLE ACCESS FULL | tableA | 1 | 10 | 4 |
|* 54 | INDEX RANGE SCAN | XPKcol3 | 5 | 15 | 1 |
| 55 | SORT AGGREGATE | | 1 | 12 | |
|* 56 | TABLE ACCESS BY INDEX ROWID | tableC | 9 | 108 | 2 |
|* 57 | INDEX RANGE SCAN | XIFtableC | 10 | | 1 |
| 58 | BUFFER SORT | | 1 | 10 | 33 |
|* 59 | TABLE ACCESS FULL | tableA | 1 | 10 | 4 |
|* 60 | INDEX RANGE SCAN | XPKcol3 | 5 | 15 | 1 |
| 61 | SORT AGGREGATE | | 1 | 12 | |
|* 62 | TABLE ACCESS BY INDEX ROWID | tableC | 9 | 108 | 2 |
|* 63 | INDEX RANGE SCAN | XIFtableC | 10 | | 1 |
| 64 | BUFFER SORT | | 1 | 10 | 38 |
|* 65 | TABLE ACCESS FULL | tableA | 1 | 10 | 4 |
|* 66 | INDEX RANGE SCAN | XPKcol3 | 5 | 15 | 1 |
| 67 | SORT AGGREGATE | | 1 | 12 | |
|* 68 | TABLE ACCESS BY INDEX ROWID | tableC | 9 | 108 | 2 |
|* 69 | INDEX RANGE SCAN | XIFtableC | 10 | | 1 |
| 70 | BUFFER SORT | | 1 | 10 | 43 |
|* 71 | TABLE ACCESS FULL | tableA | 1 | 10 | 4 |
|* 72 | INDEX RANGE SCAN | XPKcol3 | 1 | 3 | 1 |
| 73 | SORT AGGREGATE | | 1 | 12 | |
|* 74 | TABLE ACCESS BY INDEX ROWID | tableC | 9 | 108 | 2 |
|* 75 | INDEX RANGE SCAN | XIFtableC | 10 | | 1 |
| 76 | BUFFER SORT | | 1 | 10 | 48 |
|* 77 | TABLE ACCESS FULL | tableA | 1 | 10 | 4 |
|* 78 | INDEX RANGE SCAN | XPKcol3 | 1 | 3 | 1 |
| 79 | SORT AGGREGATE | | 1 | 12 | |
|* 80 | TABLE ACCESS BY INDEX ROWID | tableC | 9 | 108 | 2 |
|* 81 | INDEX RANGE SCAN | XIFtableC | 10 | | 1 |
| 82 | SORT AGGREGATE | | 1 | 12 | |
|* 83 | TABLE ACCESS BY INDEX ROWID | tableC | 9 | 108 | 2 |
|* 84 | INDEX RANGE SCAN | XIFtableC | 10 | | 1 |
| 85 | SORT AGGREGATE | | 1 | 12 | |
|* 86 | TABLE ACCESS BY INDEX ROWID | tableC | 9 | 108 | 2 |
|* 87 | INDEX RANGE SCAN | XIFtableC | 10 | | 1 |
| 88 | SORT AGGREGATE | | 1 | 12 | |
|* 89 | TABLE ACCESS BY INDEX ROWID | tableC | 9 | 108 | 2 |
|* 90 | INDEX RANGE SCAN | XIFtableC | 10 | | 1 |
| 91 | SORT AGGREGATE | | 1 | 12 | |
|* 92 | TABLE ACCESS BY INDEX ROWID | tableC | 9 | 108 | 2 |
|* 93 | INDEX RANGE SCAN | XIFtableC | 10 | | 1 |
| 94 | SORT AGGREGATE | | 1 | 12 | |
|* 95 | TABLE ACCESS BY INDEX ROWID | tableC | 9 | 108 | 2 |
|* 96 | INDEX RANGE SCAN | XIFtableC | 10 | | 1 |
| 97 | SORT AGGREGATE | | 1 | 12 | |
|* 98 | TABLE ACCESS BY INDEX ROWID | tableC | 9 | 108 | 2 |
|* 99 | INDEX RANGE SCAN | XIFtableC | 10 | | 1 |
| 100 | SORT AGGREGATE | | 1 | 12 | |
|*101 | TABLE ACCESS BY INDEX ROWID | tableC | 9 | 108 | 2 |
|*102 | INDEX RANGE SCAN | XIFtableC | 10 | | 1 |
| 103 | SORT AGGREGATE | | 1 | 12 | |
|*104 | TABLE ACCESS BY INDEX ROWID | tableC | 9 | 108 | 2 |
|*105 | INDEX RANGE SCAN | XIFtableC | 10 | | 1 |
| 106 | SORT AGGREGATE | | 1 | 12 | |
|*107 | TABLE ACCESS BY INDEX ROWID | tableC | 9 | 108 | 2 |
|*108 | INDEX RANGE SCAN | XIFtableC | 10 | | 1 |
| 109 | SORT AGGREGATE | | 1 | 12 | |
|*110 | TABLE ACCESS BY INDEX ROWID | tableC | 9 | 108 | 2 |
|*111 | INDEX RANGE SCAN | XIFtableC | 10 | | 1 |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(NVL( (SELECT /*+ */ MAX("tableC"."max_items")+1 FROM "tableC"
"tableC" WHERE "tableC"."col1"=:B1 AND
"tableC"."max_items"<>999 AND "tableC"."col4"<>'TRK' AND
"tableC"."col4"<>'RFS'),1)>0 AND NVL( (SELECT /*+ */
MAX("tableC"."max_items")+1 FROM "tableC" "tableC" WHERE
"tableC"."col1"=:B2 AND "tableC"."max_items"<>999 AND
"tableC"."col4"<>'TRK' AND "tableC"."col4"<>'RFS'),1)>0
AND NVL( (SELECT /*+ */ MAX("tableC"."max_items")+1 FROM "tableC"
"tableC" WHERE "tableC"."col1"=:B3 AND
"tableC"."max_items"<>999 AND "tableC"."col4"<>'TRK' AND
"tableC"."col4"<>'RFS'),1)>0 AND NVL( (SELECT /*+ */
MAX("tableC"."max_items")+1 FROM "tableC" "tableC" WHERE
"tableC"."col1"=:B4 AND "tableC"."max_items"<>999 AND
"tableC"."col4"<>'TRK' AND "tableC"."col4"<>'RFS'),1)>0
AND NVL( (SELECT /*+ */ MAX("tableC"."max_items")+1 FROM "tableC"
"tableC" WHERE "tableC"."col1"=:B5 AND
"tableC"."max_items"<>999 AND "tableC"."col4"<>'TRK' AND
"tableC"."col4"<>'RFS'),1)>0 AND NVL( (SELECT /*+ */
MAX("tableC"."max_items")+1 FROM "tableC" "tableC" WHERE
"tableC"."col1"=:B6 AND "tableC"."max_items"<>999 AND
"tableC"."col4"<>'TRK' AND "tableC"."col4"<>'RFS'),1)>0
AND NVL( (SELECT /*+ */ MAX("tableC"."max_items")+1 FROM "tableC"
"tableC" WHERE "tableC"."col1"=:B7 AND
"tableC"."max_items"<>999 AND "tableC"."col4"<>'TRK' AND
"tableC"."col4"<>'RFS'),1)>0 AND NVL( (SELECT /*+ */
MAX("tableC"."max_items")+1 FROM "tableC" "tableC" WHERE
"tableC"."col1"=:B8 AND "tableC"."max_items"<>999 AND
"tableC"."col4"<>'TRK' AND "tableC"."col4"<>'RFS'),1)>0
AND NVL( (SELECT /*+ */ MAX("tableC"."max_items")+1 FROM "tableC"
"tableC" WHERE "tableC"."col1"=:B9 AND
"tableC"."max_items"<>999 AND "tableC"."col4"<>'TRK' AND
"tableC"."col4"<>'RFS'),1)>0 AND NVL( (SELECT /*+ */
MAX("tableC"."max_items")+1 FROM "tableC" "tableC" WHERE
"tableC"."col1"=:B10 AND "tableC"."max_items"<>999 AND
"tableC"."col4"<>'TRK' AND "tableC"."col4"<>'RFS'),1)>0)
23 - filter("SYS_ALIAS_10"."SEQ_NO"=2)
24 - access("DP"."col3">0 AND "DP"."col3"<=NVL( (SELECT /*+ */
MAX("tableC"."max_items")+1 FROM "tableC" "tableC" WHERE
"tableC"."col1"=:B1 AND "tableC"."max_items"<>999 AND
"tableC"."col4"<>'TRK' AND "tableC"."col4"<>'RFS'),1))
26 - filter("tableC"."max_items"<>999 AND "tableC"."col4"<>'TRK'
AND "tableC"."col4"<>'RFS')
27 - access("tableC"."col1"=:B1)
29 - filter("SYS_ALIAS_9"."SEQ_NO"=3)
30 - access("SYS_ALIAS_0008"."col3">0 AND "SYS_ALIAS_0008"."col3"<=NVL( (SELECT /*+ */
MAX("tableC"."max_items")+1 FROM "tableC" "tableC" WHERE
"tableC"."col1"=:B1 AND "tableC"."max_items"<>999 AND
"tableC"."col4"<>'TRK' AND "tableC"."col4"<>'RFS'),1))
32 - filter("tableC"."max_items"<>999 AND "tableC"."col4"<>'TRK'
AND "tableC"."col4"<>'RFS')
33 - access("tableC"."col1"=:B1)
35 - filter("SYS_ALIAS_8"."SEQ_NO"=4)
36 - access("SYS_ALIAS_0007"."col3">0 AND "SYS_ALIAS_0007"."col3"<=NVL( (SELECT /*+ */
MAX("tableC"."max_items")+1 FROM "tableC" "tableC" WHERE
"tableC"."col1"=:B1 AND "tableC"."max_items"<>999 AND
"tableC"."col4"<>'TRK' AND "tableC"."col4"<>'RFS'),1))
38 - filter("tableC"."max_items"<>999 AND "tableC"."col4"<>'TRK'
AND "tableC"."col4"<>'RFS')
39 - access("tableC"."col1"=:B1)
41 - filter("SYS_ALIAS_7"."SEQ_NO"=1)
42 - access("SYS_ALIAS_0006"."col3">0 AND "SYS_ALIAS_0006"."col3"<=NVL( (SELECT /*+ */
MAX("tableC"."max_items")+1 FROM "tableC" "tableC" WHERE
"tableC"."col1"=:B1 AND "tableC"."max_items"<>999 AND
"tableC"."col4"<>'TRK' AND "tableC"."col4"<>'RFS'),1))
44 - filter("tableC"."max_items"<>999 AND "tableC"."col4"<>'TRK'
AND "tableC"."col4"<>'RFS')
45 - access("tableC"."col1"=:B1)
47 - filter("SYS_ALIAS_6"."SEQ_NO"=7)
48 - access("SYS_ALIAS_0005"."col3">0 AND "SYS_ALIAS_0005"."col3"<=NVL( (SELECT /*+ */
MAX("tableC"."max_items")+1 FROM "tableC" "tableC" WHERE
"tableC"."col1"=:B1 AND "tableC"."max_items"<>999 AND
"tableC"."col4"<>'TRK' AND "tableC"."col4"<>'RFS'),1))
50 - filter("tableC"."max_items"<>999 AND "tableC"."col4"<>'TRK'
AND "tableC"."col4"<>'RFS')
51 - access("tableC"."col1"=:B1)
53 - filter("SYS_ALIAS_5"."SEQ_NO"=8)
54 - access("SYS_ALIAS_0004"."col3">0 AND "SYS_ALIAS_0004"."col3"<=NVL( (SELECT /*+ */
MAX("tableC"."max_items")+1 FROM "tableC" "tableC" WHERE
"tableC"."col1"=:B1 AND "tableC"."max_items"<>999 AND
"tableC"."col4"<>'TRK' AND "tableC"."col4"<>'RFS'),1))
56 - filter("tableC"."max_items"<>999 AND "tableC"."col4"<>'TRK'
AND "tableC"."col4"<>'RFS')
57 - access("tableC"."col1"=:B1)
59 - filter("SYS_ALIAS_4"."SEQ_NO"=10)
60 - access("SYS_ALIAS_0003"."col3">0 AND "SYS_ALIAS_0003"."col3"<=NVL( (SELECT /*+ */
MAX("tableC"."max_items")+1 FROM "tableC" "tableC" WHERE
"tableC"."col1"=:B1 AND "tableC"."max_items"<>999 AND
"tableC"."col4"<>'TRK' AND "tableC"."col4"<>'RFS'),1))
62 - filter("tableC"."max_items"<>999 AND "tableC"."col4"<>'TRK'
AND "tableC"."col4"<>'RFS')
63 - access("tableC"."col1"=:B1)
65 - filter("SYS_ALIAS_3"."SEQ_NO"=9)
66 - access("SYS_ALIAS_0002"."col3">0 AND "SYS_ALIAS_0002"."col3"<=NVL( (SELECT /*+ */
MAX("tableC"."max_items")+1 FROM "tableC" "tableC" WHERE
"tableC"."col1"=:B1 AND "tableC"."max_items"<>999 AND
"tableC"."col4"<>'TRK' AND "tableC"."col4"<>'RFS'),1))
68 - filter("tableC"."max_items"<>999 AND "tableC"."col4"<>'TRK'
AND "tableC"."col4"<>'RFS')
69 - access("tableC"."col1"=:B1)
71 - filter("SYS_ALIAS_2"."SEQ_NO"=5)
72 - access("SYS_ALIAS_0001"."col3">0 AND "SYS_ALIAS_0001"."col3"<=NVL( (SELECT /*+ */
MAX("tableC"."max_items")+1 FROM "tableC" "tableC" WHERE
"tableC"."col1"=:B1 AND "tableC"."max_items"<>999 AND
"tableC"."col4"<>'TRK' AND "tableC"."col4"<>'RFS'),1))
filter("SYS_ALIAS_0003"."col3"-1+("SYS_ALIAS_0002"."col3"-1)+("SYS_ALIAS_0001"."col3"-1)<28
AND "DP"."col3"-1+("SYS_ALIAS_0008"."col3"-1)+("SYS_ALIAS_0007"."col3"-1)+("SYS_ALIAS_0006"."DAYS_PRI
OR"-1)+("SYS_ALIAS_0005"."col3"-1)+("SYS_ALIAS_0004"."col3"-1)+("SYS_ALIAS_0003"."col3"-1)+("SYS_ALIA
S_0002"."col3"-1)+("SYS_ALIAS_0001"."col3"-1)<52)
74 - filter("tableC"."max_items"<>999 AND "tableC"."col4"<>'TRK'
AND "tableC"."col4"<>'RFS')
75 - access("tableC"."col1"=:B1)
77 - filter("SYS_ALIAS_1"."SEQ_NO"=6)
78 - access("SYS_ALIAS_0000"."col3">0 AND "SYS_ALIAS_0000"."col3"<=NVL( (SELECT /*+ */
MAX("tableC"."max_items")+1 FROM "tableC" "tableC" WHERE
"tableC"."col1"=:B1 AND "tableC"."max_items"<>999 AND
"tableC"."col4"<>'TRK' AND "tableC"."col4"<>'RFS'),1))
filter(("DP"."col3"-1)*"SYS_ALIAS_10"."max_item"+("SYS_ALIAS_0008"."col3"-1)*"SYS_AL
IAS_9"."max_item"+("SYS_ALIAS_0007"."col3"-1)*"SYS_ALIAS_8"."max_item"+("SYS_ALIA
S_0006"."col3"-1)*"SYS_ALIAS_7"."max_item"+("SYS_ALIAS_0005"."col3"-1)*"SYS_ALIAS_6"."max_item"+("SYS_ALIAS_0004"."col3"-1)*"SYS_ALIAS_5"."max_item"+("SYS_ALIAS_0003"."col3
"-1)*"SYS_ALIAS_4"."max_item"+("SYS_ALIAS_0002"."col3"-1)*"SYS_ALIAS_3"."max_item"+("SYS_ALIAS_0001"."col3"-1)*"SYS_ALIAS_2"."max_item"+("SYS_ALIAS_0000"."col3"-1)*"S
YS_ALIAS_1"."max_item"<200 AND "DP"."col3"-1+("SYS_ALIAS_0008"."col3"-1)+("SYS_ALIAS_0007".
"col3"-1)+("SYS_ALIAS_0006"."col3"-1)+("SYS_ALIAS_0001"."col3"-1)+("SYS_ALIAS_0000"."col3"-1)<3
2)
80 - filter("tableC"."max_items"<>999 AND "tableC"."col4"<>'TRK'
AND "tableC"."col4"<>'RFS')
81 - access("tableC"."col1"=:B1)
83 - filter("tableC"."max_items"<>999 AND "tableC"."col4"<>'TRK'
AND "tableC"."col4"<>'RFS')
84 - access("tableC"."col1"=:B1)
86 - filter("tableC"."max_items"<>999 AND "tableC"."col4"<>'TRK'
AND "tableC"."col4"<>'RFS')
87 - access("tableC"."col1"=:B1)
89 - filter("tableC"."max_items"<>999 AND "tableC"."col4"<>'TRK'
AND "tableC"."col4"<>'RFS')
90 - access("tableC"."col1"=:B1)
92 - filter("tableC"."max_items"<>999 AND "tableC"."col4"<>'TRK'
AND "tableC"."col4"<>'RFS')
93 - access("tableC"."col1"=:B1)
95 - filter("tableC"."max_items"<>999 AND "tableC"."col4"<>'TRK'
AND "tableC"."col4"<>'RFS')
96 - access("tableC"."col1"=:B1)
98 - filter("tableC"."max_items"<>999 AND "tableC"."col4"<>'TRK'
AND "tableC"."col4"<>'RFS')
99 - access("tableC"."col1"=:B1)
101 - filter("tableC"."max_items"<>999 AND "tableC"."col4"<>'TRK'
AND "tableC"."col4"<>'RFS')
102 - access("tableC"."col1"=:B1)
104 - filter("tableC"."max_items"<>999 AND "tableC"."col4"<>'TRK'
AND "tableC"."col4"<>'RFS')
105 - access("tableC"."col1"=:B1)
107 - filter("tableC"."max_items"<>999 AND "tableC"."col4"<>'TRK'
AND "tableC"."col4"<>'RFS')
108 - access("tableC"."col1"=:B1)
110 - filter("tableC"."max_items"<>999 AND "tableC"."col4"<>'TRK'
AND "tableC"."col4"<>'RFS')
111 - access("tableC"."col1"=:B1)
Note: cpu costing is off |
|
|
|
|
|
| Re: Query getting slowed running in Oracle 10g compared to Oracle 9i [message #501919 is a reply to message #501915] |
Fri, 01 April 2011 08:02 |
ram anand
Messages: 244
Registered: February 2008
Location: india
|
Senior Member |
|
|
Comparing to Oracle 10.2.0.5.0 and 10.2.0.1.0 the explain plan shows more difference in the cost and bytes used and also CPU % are high in oracle 10.2.0.5.0. if you note when we remove the ORDER BY clause in the view "sample_v_new" it gives the result with 2sec ,but here the order by caluse is mandatory based on the order only it tries to load.
so how to overcome or optimize the ORDER BY level in this view.pls throw some spark and get me out of this great head ache!!
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Query getting slowed running in Oracle 10g compared to Oracle 9i [message #502098 is a reply to message #502091] |
Mon, 04 April 2011 06:35 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Hi - I hesitate to contribute to a sql tuning topic (you already have people better at development than me looking at this) but from the DBA's point of view, your root problem is unlikely to be those instance parameters. If you query v$pga_target_advice and v$sga_target_advice, you'll see what Oracle suggests for pga_aggregate_target and sga_target, the other parameters don't matter.
One point I would suggest it removing the /*+ ordered */ hint from your view definition. I have often had very good results when upgrading to 10g by removing hints. Trust the optimizer.
|
|
|
|
Goto Forum:
Current Time: Sun Nov 24 20:44:01 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
