| MAJOR performance issues since upgrade to 10G [message #353467] |
Tue, 14 October 2008 00:22  |
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 |
------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------+-----------------------------------+-------------------------+---------------+
| 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 #353713 is a reply to message #353472] |
Tue, 14 October 2008 16:35  |
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)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
