unexpected query plan

From: Baumgartel, Paul <paul.baumgartel_at_credit-suisse.com>
Date: Fri, 19 Jan 2007 21:21:42 -0000
Message-ID: <D97D1FAE0521BD44820B920EDAB3BBAC1663BB7A@ENYC11P32005.corpny.csfb.com>

9.2.0.6 on Solaris. All stats gathered with dbms_stats. There are histograms on a couple of the columns (but not on column mentioned in predicate here).

Simple query

select trans_id from ods_execution where ods_process_date = '12/27/2006';

gives this plan

| 0 | SELECT STATEMENT | | 1369K| 18M| 1219K (4)| | | | | |

|*  1 |  VIEW                  | index$_join$_001  |  1369K|    18M|            |       |       | 02,02  | P->S | QC (RAND)  |
|*  2 |   HASH JOIN            |                   |  1369K|    18M|            |       |       | 02,02  | PCWP |            |


|   3 |    PARTITION RANGE ALL |                   |       |       |            |     1 |   107 | 02,00  | S->P | HASH       |

|*  4 |     INDEX RANGE SCAN   | NUK_PRC_DT_CUSIP  |  1369K|    18M|   497K (24)|     1 |   107 |        |      |            |


|   5 |    INDEX FAST FULL SCAN| NUK_EXEC_TRANS    |  1369K|    18M|   497K (24)|       |       | 02,01  | P->P | HASH       |

------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

   1 - filter("ODS_EXECUTION"."ODS_PROCESS_DATE"=TO_DATE('2006-12-27 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
   2 - access("indexjoin$_alias$_003".ROWID="indexjoin$_alias$_002".ROWID)
   4 - access("indexjoin$_alias$_002"."ODS_PROCESS_DATE"=TO_DATE('2006-12-27 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))

What is this indexjoin operation? I have never seen it. Query takes a minute and a half to run; should run in a second or so. Any info appreciated!

Thanks,

Paul Baumgartel
CREDIT SUISSE
Information Technology
DBA & Admin - NY, KIGA 1
11 Madison Avenue
New York, NY 10010
USA
Phone 212.538.1143
paul.baumgartel_at_credit-suisse.com
www.credit-suisse.com

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Tanel Poder Sent: Friday, January 19, 2007 1:29 PM
To: rjamya_at_gmail.com; 'Oracle Discussion List' Subject: RE: Q about compressed table

This query should give you a low-confidence estimate, based on dba_tables.avg_row_len and num_rows, so your stats must be up to date.

I wrote it just now and it seems that I have missed something as it predicts the real space usage 3-5% lower when uncompressing the table with alter table move...

I tested it on freelist managed tablespace, ASSM adds additional overhead.

select
t.num_rows -- number of rows in table

( t.avg_row_len -- average row length + 2 -- there's a 2-byte entry in block row directory for each row + 3 -- dbms_stats seems to not account for row header bytes )
( 100 -- calculate how much there's block overhead / ( 100
- t.pct_free
- ( ( select sum( type_size * decode("TYPE", 'KTBIT', t.ini_trans, 1) ) from v$type_size where "TYPE" in ( 'KCBH', 'KTBBH', 'KDBH', 'KTBIT') ) + t.avg_row_len * 0.5 ) / ts.block_size ) ) estimated_size from dba_tables t, dba_tablespaces ts where t.tablespace_name = ts.tablespace_name and t.owner = '&1' and t.table_name = '&2' /

Tanel.

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of rjamya Sent: Friday, January 19, 2007 22:53
To: Oracle Discussion List
Subject: Q about compressed table

I have a 400GB compressed partitioned table in a 10gR2 db. Is there a way to estimate uncompressed size without exporting/dpexporting or copying the table into another schema? The estimate feature of expdp doesn't work all that well with compressed segments.

TIA
rjamya

Got RAC?

Please access the attached hyperlink for an important electronic communications disclaimer:

http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jan 19 2007 - 15:21:42 CST