-none-

Mailing List

Home

Forum Home

Oracle List - by freelists.org

Oracle on SUSE Linux - Runing Oracle on SUSE Linux

Oracle database error code ...

www.freelists.org

Subjects

•	ORA 12540: TNS:internal limit restriction exceeded
•	ORA 12838 please : Is possible to append two times to the same table befo
•	ORA 12838 please : Is possible to append two times to the same table before
•	ora 04031
•	ora 12500 on windows
•	ORA 32004: obsolete and/or deprecated parameter(s) specified
•	ORA 01925: maximum of 30 enabled roles exceeded
•	ORA 01925: maximum of 30 enabled roles exceeded
•	ora 12500 on windows
•	ORA 01650, one idea
•	ORA 01650
•	ORA 4030
•	ORA 12838 please : Is possible to append two times to thesametable before do
•	ORA 12838 please : Is possible to append two times to thesame table before d
•	ORA 01536
•	ORA 03113 end of file on communication channel
•	ORA 32004: obsolete and/or deprecated parameter(s) specified
•	ORA 00600:
•	ORA 00020: maximum number of processes (%s) exceeded
•	ORA 01925: maximum of 30 enabled roles exceeded
•	ORA 3113 while creating a cluster database 9201 RAC on Linux with OCFS
•	ora 12500 on windows
•	ora 12500 on windows
•	ora 12500 on windows
•	ORA 01650, one idea
•	ora 12500 on windows
•	ora 12500 on windows
•	ora 12500 on windows
•	ORA 2000 Error Using DBMS STATS GATHER SCHEMA STATS
•	ORA 01650, one idea
•	ORA 01650, one idea
•	ORA 01650, one idea
•	ORA 01650
•	ORA 01650
•	ORA 01031
•	ORA 4030
•	ORA 4030
•	ORA 06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
•	ORA 01722 invalid number

-none-

-none-

2007-09-02 - By LS Cheng

Back
Hi all

I have a query which involves three table joins a range predicate with dates
with bind variables. The predicate asks for a 30 minutes range data.

num_rows t1 463220
num_rows t2 15
num_rows t3 460880

var a varchar2(20)
var b number
var c varchar2(20)
var d number

exec :a := '20070801 235000'
exec :b := 31
exec :c := '20070801 235000'
exec :d := -1

alter session set nls_date_format = 'YYYYMMDD HH24MISS';

SELECT *
FROM t1, t2, t3
WHERE t1.c1 = t2.c1(+)
AND t1.c2 = t2.c2(+)
AND t1.c3 = t2.c3(+)
AND t1.date_pre >= (TO_DATE (:a) - (:b) / 1440)
AND t1.date_pre <= (TO_DATE (:c) - (:d) / 1440)
AND t1.c1 = t3.c1(+)
AND t1.c2 = t3.c2(+)
AND t1.c3 = t3.c3(+)

9.2.0.8 plan
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
-- ----
| Id | Operation | Name | Rows | Bytes
| Cost |
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
-- ----
| 0 | SELECT STATEMENT | | 1158 |
313K| 849 |
|* 1 | FILTER | | |
| |
|* 2 | HASH JOIN OUTER | | 1158 |
313K| 849 |
|* 3 | HASH JOIN OUTER | | 1158 |
170K| 17 |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 1158 |
135K| 13 |
|* 5 | INDEX RANGE SCAN | IDX3T1 | 2084 |
| 8 |
| 6 | TABLE ACCESS FULL | T2 | 15 | 465
| 2 |
|* 7 | TABLE ACCESS FULL | T3 | 12942 |
1592K| 825 |
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
-- ----

10.2.0.3 plan
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----+-- ---- ---- ---
-- ---- ---- ------+
| Id | Operation | Name | Rows |
Bytes | Cost | Time |
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----+-- ---- ---- ---
-- ---- ---- ------+
| 0 | SELECT STATEMENT | |
| | 87 | |
| 1 | FILTER | |
| | | |
| 2 | NESTED LOOPS OUTER | | 26 |
7202 | 87 | 00:00:02 |
| 3 | NESTED LOOPS OUTER | | 26 |
3926 | 26 | 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID | T1 | 26 |
3120 | 25 | 00:00:01 |
| 5 | INDEX RANGE SCAN | IDX3T1 | 26
| | 3 | 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | T2 | 1 |
31 | 1 | 00:00:01 |
| 7 | INDEX UNIQUE SCAN | PKT2 | 1
| | 0 | |
| 8 | TABLE ACCESS BY INDEX ROWID | T3 | 1 |
126 | 3 | 00:00:01 |
| 9 | INDEX RANGE SCAN | IDX4T3 | 1
| | 2 | 00:00:01 |
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----+-- ---- ---- ---
-- ---- ---- ------+

As we can see the index cardinality (IDX3T1) in 9.2.0.8 is incorrect, the
selectivity used is the default because of bind variables, 0.05 * 0.05. So
it calculates a cardinality of 463220 * 0.05 * 0.05 which yields 1158 as
plan shows.

So my question is, why is the default selectivity is used? Shouldnt bind
peeking suppose to happen and get a proper selectivity? I tried restarted
the database, flushed the shared_pool, invalidated the cursor to ensure a
hard parse but no luck however in 10.2.0.3 it gets a proper
0.000055488selectivity and bind peeking happens seen from 10053 trace
file.

Thanks

--
LSC

Hi all I have a query which involves three table joins a range predicate
with dates with bind variables. The predicate asks for a 30 minutes range data.
 num_rows t1 463220
 num_rows t2 15 num_rows t3 460880 var a varchar2(20) var b
number var c varchar2(20) var d number exec :a := '20070801
235000' exec :b := 31 exec :c := '20070801 235000'
 exec :d := -1 alter session set nls_date_format = 'YYYYMMDD
HH24MISS'; SELECT *   FROM t1,
t2, t3
  WHERE t1.c1 = t2.c1(+) 
   AND t1.c2 = t2.c2(+)    AND
t1.c3 = t2.c3(+) 
   AND t1.date_pre >= (TO_DATE (:a) - (:b) / 1440)    AND t1.date_pre <= (TO_DATE (:c) - (:d) / 1440) 
   AND t1.c1 = t3
.c1(+)    AND t1.c2 = t3.c2(+) 
   AND t1.c3 = t3
.c3(+) 
<a href="http://9.2.0.8">9.2.0.8</a> plan -- ---- -----
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- -----
 | Id  | Operation     
             
    |  Name        
       | Rows  | Bytes | Cost  |
 
-- ---- ---- ---- ---- ------
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- 
|   0 | SELECT STATEMENT       
        |      
             
  |  1158 |   313K|   849 | |*  1 |  FILTER        
             
   |           
          |    
   |       |    
   |
 |*  2 |   HASH JOIN OUTER  
            |  
             
      |  1158 |   313K|   849
| 
|*  3 |  &nbsp
; HASH JOIN OUTER          
   |           
          |  1158 | &nbsp
; 170K|    17 | 
|   4 |     TABLE ACCESS BY INDEX ROWID| T1 
             
    |  1158 |   135K|    13 | |*  5 |      INDEX RANGE
SCAN          | IDX3T1  
            |  2084
|       |     8 |
 |   6 |     TABLE ACCESS
FULL          | T2  
             
   |    15 |   465 |    
2 | 
|*  7 |  &nbsp
; TABLE ACCESS FULL          
| T3            
      | 12942 |  1592K|   825 |
 
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
-- ---- 
<a href="http://10.2.0.3">10.2.0.3</a> plan -- --
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----+-- ---- ---- ---- ----
-- ---- -----+
 | Id  | Operation     
             
     | Name        
        | Rows  | Bytes | Cost  |
Time      | 
-- ---- ---- ---- ---- ------
-- ---- ---- ---- ---- ---- -----+-- ---- ---- ---- ---- ---- ---- --+ 
| 0   | SELECT STATEMENT       
         |     
             
   |       |    
   |    87 |       
    | | 1   |  FILTER&nbsp
;             
           |   
             
     |       |  
     |       |  
         |
 | 2   |   NESTED LOOPS OUTER 
           |   
             
     |    26 |  7202 |   
87 |  00:00:02 | 
| 3   |  
  NESTED LOOPS OUTER         
  |            
         |    26 | 
3926 |    26 |  00:00:01 | 
| 4   |     TABLE ACCESS BY INDEX ROWID | T1&nbsp
;             
    |    26 |  3120 |    25 |
  00:00:01 | | 5   |   
   INDEX RANGE SCAN        
   | IDX3T1         &nbsp
;    |    26 |      
|     3 |  00:00:01 |
 | 6   |     TABLE ACCESS
BY INDEX ROWID | T2          
        |     1 | 
   31 |     1 |  00:00:01 | 
| 7   |  
    INDEX UNIQUE SCAN       
   | PKT2          
      |     1 |   
    |     0 |     
      | 
| 8   |    TABLE ACCESS BY INDEX ROWID  | T3&nbsp
;             
    |     1 |   126 |  
   3 |  00:00:01 | | 9   |
     INDEX RANGE SCAN      
      | IDX4T3      &nbsp
;       |     1 |  
     |     2 |  00:00:01 |
 -- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
-- -----+-- ---- ---- ---- ---- ---- ---- --+ 
As we can see the index cardinality (IDX3T1) in <a href="http://9.2.0.8">9.2.0
.8</a> is incorrect, the selectivity used is the default because of bind
variables, 0.05 * 0.05. So it calculates a cardinality of 463220 * 0.05
* 0.05 which yields 1158 as plan shows. So my question is, why is the
default selectivity is used? Shouldnt bind peeking suppose to happen and get a
proper selectivity? I tried restarted the database, flushed the shared_pool,
invalidated the cursor to ensure a hard parse but no luck however in
<a href="http://10.2.0.3">10.2.0.3</a> it gets a proper 0.000055488 selectivity
and bind peeking happens seen from 10053 trace file. Thanks -- LSC