Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> unexpected query plan

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  



| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop | TQ |IN-OUT| PQ Distrib |

| 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

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US