Home » RDBMS Server » Performance Tuning » MAJOR performance issues since upgrade to 10G (10.2.0.3.0)
MAJOR performance issues since upgrade to 10G [message #353467] Tue, 14 October 2008 00:22 Go to next message
kenton
Messages: 2
Registered: October 2008
Junior Member
Hello ...
A query that used to take 20 mins to complete under 9i now does not return a result after 24 hours.

Looking at the CBO execution plans we get ...
for 9i
-------------------------------------------------------------------------------------------------------------------------
| Operation                      | Name               | Rows  | Bytes | Cost  |  TQ  |IN-OUT| PQ Distrib |Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
| INSERT STATEMENT               |                    |     0 |     0 |  7359 |      |      |           |       |       |
|  SORT GROUP BY                 |                    |    20 |  3700 |  6559 |      |      |           |       |       |
|   FILTER                       |                    |     0 |     0 |     0 |      |      |           |       |       |
|    NESTED LOOPS                |                    |    20 |  3700 |  6555 |9949,2|P->S  |QC (RANDOM)|       |       |
|     HASH JOIN                  |                    |    20 |  2460 |  6552 |9949,2|PCWP  |           |       |       |
|      NESTED LOOPS              |                    |   135 |  7965 |     3 |9949,1|P->P  |BROADCAST  |       |       |
|       TABLE ACCESS BY INDEX RO | D_PERIOD           |     1 |    22 |     2 |9949,0|S->P  |BROADCAST  |       |       |
|        INDEX UNIQUE SCAN       | PK_D_PERIOD        |     1 |     0 |     1 |      |      |           |       |       |
|       TABLE ACCESS FULL        | D_AGENT_INSURER    |   135 |  4995 |     1 |9949,1|PCWP  |           |       |       |
|      PARTITION RANGE ALL       |                    |     0 |     0 |     0 |9949,2|PCWP  |           | 1     | 8     |
|       TABLE ACCESS FULL        | F_POLICY           |  8072 |  504K |  6549 |9949,2|PCWP  |           | 1     | 8     |
|     TABLE ACCESS BY INDEX ROWI | D_EMPLOYER         |     1 |    62 |     1 |9949,2|PCWP  |           |       |       |
|      INDEX UNIQUE SCAN         | PK_D_EMPLOYER      |     1 |     0 |     0 |9949,2|PCWP  |           |       |       |
|    SORT AGGREGATE              |                    |     1 |    20 |     0 |      |      |           |       |       |
|     PARTITION RANGE SINGLE     |                    |     0 |     0 |     0 |      |      |           | KEY   | KEY   |
|      TABLE ACCESS BY LOCAL IND | F_POLICY           |     1 |    20 |    40 |      |      |           | KEY   | KEY   |
|       INDEX RANGE SCAN         | F_POLICY_FK_D_POL_IDX|    39 |     0 |     3 |      |      |           | KEY   | KEY   |
------------------------------------------------------------------------------------------------------------------------

and under 10g:
------------------------------------------------------------------------------+-----------------------------------+-------------------------+---------------+
| Id  | Operation                                         | Name              | Rows  | Bytes | Cost  | Time      |  TQ  |IN-OUT|PQ Distrib | Pstart| Pstop |
------------------------------------------------------------------------------+-----------------------------------+-------------------------+---------------+
| 0   | INSERT STATEMENT                                  |                   |       |       |     6 |           |      |      |           |       |       |
| 1   |  PX COORDINATOR                                   |                   |       |       |       |           |      |      |           |       |       |
| 2   |   PX SEND QC (RANDOM)                             | :TQ10004          |     1 |   238 |     6 |  00:00:01 |:Q1004| P->S |QC (RANDOM)|       |       |
| 3   |    HASH GROUP BY                                  |                   |     1 |   238 |     6 |  00:00:01 |:Q1004| PCWP |           |       |       |
| 4   |     PX RECEIVE                                    |                   |     1 |   238 |     6 |  00:00:01 |:Q1004| PCWP |           |       |       |
| 5   |      PX SEND HASH                                 | :TQ10003          |     1 |   238 |     6 |  00:00:01 |:Q1003| P->P |HASH       |       |       |
| 6   |       HASH GROUP BY                               |                   |     1 |   238 |     6 |  00:00:01 |:Q1003| PCWP |           |       |       |
| 7   |        VIEW                                       |                   |     1 |   238 |     5 |  00:00:01 |:Q1003| PCWP |           |       |       |
| 8   |         FILTER                                    |                   |       |       |       |           |:Q1003| PCWC |           |       |       |
| 9   |          SORT GROUP BY                            |                   |     1 |   422 |     5 |  00:00:01 |:Q1003| PCWP |           |       |       |
| 10  |           PX RECEIVE                              |                   |     1 |   422 |     5 |  00:00:01 |:Q1003| PCWP |           |       |       |
| 11  |            PX SEND HASH                           | :TQ10002          |     1 |   422 |     5 |  00:00:01 |:Q1002| P->P |HASH       |       |       |
| 12  |             SORT GROUP BY                         |                   |     1 |   422 |     5 |  00:00:01 |:Q1002| PCWP |           |       |       |
| 13  |              NESTED LOOPS                         |                   |     1 |   422 |     4 |  00:00:01 |:Q1002| PCWP |           |       |       |
| 14  |               NESTED LOOPS                        |                   |     1 |   351 |     4 |  00:00:01 |:Q1002| PCWP |           |       |       |
| 15  |                PX RECEIVE                         |                   |       |       |       |           |:Q1002| PCWP |           |       |       |
| 16  |                 PX SEND BROADCAST                 | :TQ10001          |       |       |       |           |:Q1001| P->P |BROADCAST  |       |       |
| 17  |                  NESTED LOOPS                     |                   |     1 |   268 |     4 |  00:00:01 |:Q1001| PCWP |           |       |       |
| 18  |                   NESTED LOOPS                    |                   |     1 |   222 |     4 |  00:00:01 |:Q1001| PCWP |           |       |       |
| 19  |                    BUFFER SORT                    |                   |       |       |       |           |:Q1001| PCWC |           |       |       |
| 20  |                     PX RECEIVE                    |                   |       |       |       |           |:Q1001| PCWP |           |       |       |
| 21  |                      PX SEND BROADCAST            | :TQ10000          |       |       |       |           |      | S->P |BROADCAST  |       |       |
| 22  |                       TABLE ACCESS BY INDEX ROWID | D_PERIOD          |     1 |    30 |     2 |  00:00:01 |      |      |           |       |       |
| 23  |                        INDEX UNIQUE SCAN          | PK_D_PERIOD       |     1 |       |     1 |  00:00:01 |      |      |           |       |       |
| 24  |                    PX BLOCK ITERATOR              |                   |     1 |   192 |     2 |  00:00:01 |:Q1001| PCWC |           | 1     | 8     |
| 25  |                     TABLE ACCESS FULL             | F_POLICY          |     1 |   192 |     2 |  00:00:01 |:Q1001| PCWP |           | 1     | 8     |
| 26  |                   TABLE ACCESS BY INDEX ROWID     | D_AGENT_INSURER   |     1 |    46 |     0 |           |:Q1001| PCWP |           |       |       |
| 27  |                    INDEX UNIQUE SCAN              | PK_D_AGENT_INSURER|     1 |       |     0 |           |:Q1001| PCWP |           |       |       |
| 28  |                PX BLOCK ITERATOR                  |                   |     1 |    83 |     0 |           |:Q1002| PCWC |           | KEY   | KEY   |
| 29  |                 TABLE ACCESS FULL                 | F_POLICY          |     1 |    83 |     0 |           |:Q1002| PCWP |           | KEY   | KEY   |
| 30  |               TABLE ACCESS BY INDEX ROWID         | D_EMPLOYER        |     1 |    71 |     1 |  00:00:01 |:Q1002| PCWP |           |       |       |
| 31  |                INDEX UNIQUE SCAN                  | PK_D_EMPLOYER     |     1 |       |     1 |  00:00:01 |:Q1002| PCWP |           |       |       |
------------------------------------------------------------------------------+-----------------------------------+-------------------------+---------------+


The tables and indexes are all the same in both databases (they are running concurrently so it's easy to test/compare)

I am at an absolute loss as to what is wrong. The 10G upgrade will need to be cancelled if this is not resolved.

Can anyone out there help me?


Thanks
Kenton
Re: MAJOR performance issues since upgrade to 10G [message #353472 is a reply to message #353467] Tue, 14 October 2008 00:38 Go to previous messageGo to next message
ora_baby
Messages: 89
Registered: May 2008
Member
i understood of very large difeerent cost,bytes,rows between 9i and 10g.
try some tracer for analyze your query
may be you have locks, therefore, query has not result

{ps. sorry for my language}

[Updated on: Tue, 14 October 2008 00:39]

Report message to a moderator

Re: MAJOR performance issues since upgrade to 10G [message #353713 is a reply to message #353472] Tue, 14 October 2008 16:35 Go to previous message
kenton
Messages: 2
Registered: October 2008
Junior Member
What sort of trace? (Sorry I'm not a DBA)

Our DBA got me to use
ALTER SESSION SET EVENTS '10132 trace name context forever, level 12';


And I have looked at the output and can find no locks if that's what you're talking about. (but then again, I don't really know what I'm looking for)
Previous Topic: TRIGGERS/PROCEDURES running too slow after index rebuild
Next Topic: Performance issue on RAC
Goto Forum:
  


Current Time: Sun Dec 04 14:34:18 CST 2016

Total time taken to generate the page: 0.08974 seconds