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 Go to next message
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 #501601 is a reply to message #501598] Wed, 30 March 2011 01:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post information as usual.

Regards
Michel
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 Go to previous messageGo to next message
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 #501619 is a reply to message #501615] Wed, 30 March 2011 03:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is not the usual required information.

Regards
Michel
Re: Query getting slowed running in Oracle 10g compared to Oracle 9i [message #501690 is a reply to message #501619] Wed, 30 March 2011 13:03 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
I am always enthralled by the inifinte number of posters who can tell us about a query that is slow but then never actually provide the query. Or how they can say it was fast before and not now but not provide a query plan before and after. I am interested too in why RAM feels it must be parameter related.

Kevin
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
And here is the explain plan for the below query

se
lect 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 #501885 is a reply to message #501882] Fri, 01 April 2011 04:14 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Are those deliberate cartesian joins?

I don't think I've ever seen petabyte projections in an explain plan before!
Re: Query getting slowed running in Oracle 10g compared to Oracle 9i [message #501888 is a reply to message #501885] Fri, 01 April 2011 04:35 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
hi,

Yeah its a purposeful cartesion join and it must be used.
Re: Query getting slowed running in Oracle 10g compared to Oracle 9i [message #501890 is a reply to message #501888] Fri, 01 April 2011 04:38 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Another information in my local oracle server version 10.2.0.1.0 this same query gives the result within 3mins see the result below.

SQL> set timing on;
SQL> select count(*) from sample_v_new;

  COUNT(*)
----------
   5232235

Elapsed: 00:03:48.73
Re: Query getting slowed running in Oracle 10g compared to Oracle 9i [message #501891 is a reply to message #501890] Fri, 01 April 2011 04:41 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Are the plans the same across versions?
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 Go to previous messageGo to next message
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 #501896 is a reply to message #501895] Fri, 01 April 2011 05:43 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Plans are very different, are the stats on all versions correct? Are the data volumes in all versions the same? i.e is the only variance the oracle version?
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 Go to previous messageGo to next message
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 #501915 is a reply to message #501912] Fri, 01 April 2011 07:07 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Yes, but if Oracle has bad stats to calculate a plan from, it'll probably generate a bad plan.

My first port of call would be getting the correct stats in there.
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 Go to previous messageGo to next message
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 #501920 is a reply to message #501919] Fri, 01 April 2011 08:07 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
But if the stats are different, you would expect a different plan.

As far as I can tell you are not comparing like with like, you're telling Oracle two different things and Oracle is coming up with two different approaches.

Unless I'm misunderstanding you that is.

Make the data fed into the CBO consistent and then see if the plans are different and then tune from there.

[Updated on: Fri, 01 April 2011 08:11]

Report message to a moderator

Re: Query getting slowed running in Oracle 10g compared to Oracle 9i [message #501921 is a reply to message #501920] Fri, 01 April 2011 08:16 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
No idea on CBO consistent ,can you pls help on this
Re: Query getting slowed running in Oracle 10g compared to Oracle 9i [message #501925 is a reply to message #501921] Fri, 01 April 2011 08:40 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,

How to overcome the 'MERGE JOIN CARTESIAN' in the explain plan shown above
Re: Query getting slowed running in Oracle 10g compared to Oracle 9i [message #501927 is a reply to message #501925] Fri, 01 April 2011 08:48 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
You can't. You have no join criteria between the tables, it HAS to. And besides, you said above it was intentional.
Re: Query getting slowed running in Oracle 10g compared to Oracle 9i [message #501968 is a reply to message #501927] Fri, 01 April 2011 15:27 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Did you get that 9i plan off a DB with similare data volumes to the 10g instances?
I ask cause the rows and bytes figures in it are tiny compared to the other plans.

I would also suggest 800M is pretty small for an SGA generally, how much RAM have you got free in the DB server?
Re: Query getting slowed running in Oracle 10g compared to Oracle 9i [message #502089 is a reply to message #501968] Mon, 04 April 2011 04:32 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
As per below details nearly 8GB RAM is used.so 800M for SGA is enough or we need to increase it and how much we need to increase?

[oracle@cn1aflsdb7505 ~]$ free -m
       total      used       free     shared  buffers cached
Mem:   3802       3662        140          0    12      35
-/+ buffers/cache: 613       3188
Swap:  8040         85       7954


Thanks in advance,
Re: Query getting slowed running in Oracle 10g compared to Oracle 9i [message #502091 is a reply to message #502089] Mon, 04 April 2011 04:41 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you've got statspack or awr they have an SGA advisor, see what they say. If not, install one of them.
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 Go to previous message
John Watson
Messages: 8922
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.
Previous Topic: Query not executing (3 Merged)
Next Topic: please help me tune this query
Goto Forum:
  


Current Time: Thu Mar 28 06:10:20 CDT 2024