Re: Identical table definitions, identical queries, identical xpalns: significant elapsed times in 2 different databases
From: fmhabash <fmhabash_at_gmail.com>
Date: Fri, 17 Feb 2012 09:24:23 -0500
Message-ID: <4F3E6317.4060706_at_gmail.com>
Thanks all. Issue resolved.
By disabling 'bitmap to rowid conversion' in the xplan, the query ET went down from 9min to 30secs. Here the xplans before and after ...
| Id | Operation | Name | Starts | E-Rows
|E-Bytes| Cost (%CPU)| E-Time | TQ |IN-OUT| PQ Distrib | A-Rows
| A-Time | Buffers | Reads |
| 0 | SELECT STATEMENT | | 1 | |
| 35424 (100)| | | | | 1 |00:00:39.32
| 10708 | 4255 |
| 1 | SORT AGGREGATE | | 1 | 1 | 38
| | | | | | 1 |00:00:39.32 |
10708 | 4255 |
| 2 | PX COORDINATOR | | 1 | |
| | | | | | 6 |00:00:39.30 |
10708 | 4255 |
| 3 | PX SEND QC (RANDOM) | :TQ10002 | 0 | 1
| 38 | | | Q1,02 | P->S | QC (RAND) | 0
|00:00:00.01 | 0 | 0 |
| 4 | SORT AGGREGATE | | 0 | 1 |
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 27 |
40 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 27 |
Date: Fri, 17 Feb 2012 09:24:23 -0500
Message-ID: <4F3E6317.4060706_at_gmail.com>
Thanks all. Issue resolved.
By disabling 'bitmap to rowid conversion' in the xplan, the query ET went down from 9min to 30secs. Here the xplans before and after ...
#
#########################################New xplan with ET of 30 secs
##########################################
| Id | Operation | Name | Starts | E-Rows
|E-Bytes| Cost (%CPU)| E-Time | TQ |IN-OUT| PQ Distrib | A-Rows
| A-Time | Buffers | Reads |
| 0 | SELECT STATEMENT | | 1 | |
| 35424 (100)| | | | | 1 |00:00:39.32
| 10708 | 4255 |
| 1 | SORT AGGREGATE | | 1 | 1 | 38
| | | | | | 1 |00:00:39.32 |
10708 | 4255 |
| 2 | PX COORDINATOR | | 1 | |
| | | | | | 6 |00:00:39.30 |
10708 | 4255 |
| 3 | PX SEND QC (RANDOM) | :TQ10002 | 0 | 1
| 38 | | | Q1,02 | P->S | QC (RAND) | 0
|00:00:00.01 | 0 | 0 |
| 4 | SORT AGGREGATE | | 0 | 1 |
38 | | | Q1,02 | PCWP | | 0 |00:00:00.01
| 0 | 0 |
|* 5 | VIEW | index$_join$_001 | 0 | 287 |
10906 | 35424 (1)| 00:07:06 | Q1,02 | PCWP | | 0
|00:00:00.01 | 0 | 0 |
|* 6 | HASH JOIN | | 0 | |
| | | Q1,02 | PCWP | | 0 |00:00:00.01 |
0 | 0 |
| 7 | BUFFER SORT | | 0 | |
| | | Q1,02 | PCWC | | 0 |00:00:00.01
| 0 | 0 |
| 8 | PX RECEIVE | | 0 | |
| | | Q1,02 | PCWP | | 0 |00:00:00.01 |
0 | 0 |
| 9 | PX SEND HASH | :TQ10000 | 0 | |
| | | | S->P | HASH | 0 |00:00:00.01
| 0 | 0 |
|* 10 | HASH JOIN | | 1 | |
| | | | | | 1298K|00:00:31.16
| 10697 | 4255 |
|* 11 | INDEX RANGE SCAN | JJJJJJJJJJJJJJ | 1 | 287 |
10906 | 1300 (1)| 00:00:16 | | | | 1298K|00:00:01.16 | 4792 | 0 |
|* 12 | INDEX RANGE SCAN | YYYYYYYYYYY | 1 | 287 |
10906 | 1742 (1)| 00:00:21 | | | | 1298K|00:00:21.20 | 5905 | 4255 |
| 13 | PX RECEIVE | | 0 | 287 | 10906
| 38790 (1)| 00:07:46 | Q1,02 | PCWP | | 0 |00:00:00.01
| 0 | 0 |
| 14 | PX SEND HASH | :TQ10001 | 0 | 287 |
10906 | 38790 (1)| 00:07:46 | Q1,01 | P->P | HASH | 0
|00:00:00.01 | 0 | 0 |
| 15 | PX BLOCK ITERATOR | | 0 | 287 | 10906 |
38790 (1)| 00:07:46 | Q1,01 | PCWC | | 0 |00:00:00.01
| 0 | 0 |
|* 16 | INDEX FAST FULL SCAN| ZZZZZZZZZZ | 0 | 287 |
10906 | 38790 (1)| 00:07:46 | Q1,01 | PCWP | | 0
|00:00:00.01 | 0 | 0 |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
#
#########################################Old plan with ET of 9mins
##########################################
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 27 |
40 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 27 |
| |
|* 2 | TABLE ACCESS BY INDEX ROWID | ZZZZZZZZZZ | 1 |
27 | 40 (0)| 00:00:01 |
| 3 | BITMAP CONVERSION TO ROWIDS | |
| | | |
| 4 | BITMAP AND | | | |
| |
| 5 | BITMAP CONVERSION FROM ROWIDS| | |
| | |
|* 6 | INDEX RANGE SCAN | RRRRRRRRRRRR | 654 |
| 5 (0)| 00:00:01 |
| 7 | BITMAP CONVERSION FROM ROWIDS| | |
| | |
|* 8 | INDEX RANGE SCAN | BBBBBBBBBBBBB | 654
| | 35 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Feb 17 2012 - 08:24:23 CST