CBO, Analytic functions and Cardinalities in Oracle 11.2

In an Oracle forum that I frequent a user posted that he found cardinality values to be severely inflated when using an analytic function. This user posted an example that will be used here to demonstrate the problem. It will also be used to demonstrate another issue that can make this problem even more confusing, but the latter issue is one that can easily be fixed.

The example script contains the following code, which includes statements not executed by the user posting the problem (creating a new user to run the example from, using an additional statistics gathering method to verify the method wasn’t part of the cause and modifying the dynamic sampling setting), but which have no effect on the results generated:



connect bing/#############

spool wrong_card_ex.log
select * From v$version;

set echo on linesize 132

create table t1
as
select * From all_objects;

exec dbms_stats.gather_table_stats(user, 'T1')

EXPLAIN PLAN FOR
SELECT object_type
     , object_name
FROM (
	  SELECT object_type
	       , object_name
	  FROM   t1
)
WHERE object_name = 'DUAL';
SELECT * FROM TABLE(dbms_xplan.display);

set autotrace on

select object_type, object_name
from
(select object_type, object_name from t1)
where object_name = 'DUAL';

select object_type, object_name, object_rank
from
(select object_type, object_name, row_number() over (partition by object_type order by created) object_rank from t1)
where object_name = 'DUAL';

set autotrace off

connect bing/#############

set echo on linesize 132

alter session set optimizer_dynamic_sampling=11;

EXPLAIN PLAN FOR
SELECT object_type
     , object_name
     , object_rank
FROM (
	  SELECT object_type
	       , object_name
	       , ROW_NUMBER() OVER (PARTITION BY object_type ORDER BY created) object_rank
	  FROM   t1
)
WHERE object_name = 'DUAL';
SELECT * FROM TABLE(dbms_xplan.display);

set autotrace on

select object_type, object_name
from
(select object_type, object_name from t1)
where object_name = 'DUAL';

select object_type, object_name, object_rank
from
(select object_type, object_name, row_number() over (partition by object_type order by created) object_rank from t1)
where object_name = 'DUAL';

set autotrace off

exec dbms_stats.gather_schema_stats(user)

EXPLAIN PLAN FOR
SELECT object_type
     , object_name
FROM (
	  SELECT object_type
	       , object_name
	  FROM   t1
)
WHERE object_name = 'DUAL';
SELECT * FROM TABLE(dbms_xplan.display);

set autotrace on

select object_type, object_name
from
(select object_type, object_name from t1)
where object_name = 'DUAL';

select object_type, object_name, object_rank
from
(select object_type, object_name, row_number() over (partition by object_type order by created) object_rank from t1)
where object_name = 'DUAL';

set autotrace off

connect bing/#############

set echo on linesize 132

alter session set optimizer_dynamic_sampling=11;

EXPLAIN PLAN FOR
SELECT object_type
     , object_name
     , object_rank
FROM (
	  SELECT object_type
	       , object_name
	       , ROW_NUMBER() OVER (PARTITION BY object_type ORDER BY created) object_rank
	  FROM   t1
)
WHERE object_name = 'DUAL';
SELECT * FROM TABLE(dbms_xplan.display);

set autotrace on

select object_type, object_name
from
(select object_type, object_name from t1)
where object_name = 'DUAL';

select object_type, object_name, object_rank
from
(select object_type, object_name, row_number() over (partition by object_type order by created) object_rank from t1)
where object_name = 'DUAL';

set autotrace off

spool off

Nothing unusual there really, other than the second run of the code minus the table creation. That was done to test whether gathering schema stats over gathering table stats made a difference in the cardinality numbers; it did not. Let’s look at a run of this script in an 11.2.0.4 database and see what cardinalities Oracle generates:


BANNER                                                                                                                              
--------------------------------------------------------------------------------                                                    
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production                                                        
PL/SQL Release 11.2.0.4.0 - Production                                                                                              
CORE	11.2.0.4.0	Production                                                                                                          
TNS for 64-bit Windows: Version 11.2.0.4.0 - Production                                                                             
NLSRTL Version 11.2.0.4.0 - Production                                                                                              

SQL> 
SQL> create table t1
  2  as
  3  select * From all_objects;

Table created.

SQL> 
SQL> exec dbms_stats.gather_table_stats(user, 'T1')

PL/SQL procedure successfully completed.

SQL> 
SQL> EXPLAIN PLAN FOR
  2  SELECT object_type
  3  	  , object_name
  4  FROM (
  5  	       SELECT object_type
  6  		    , object_name
  7  	       FROM   t1
  8  )
  9  WHERE object_name = 'DUAL';

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT                                                                                                                   
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013                                                                                                         
                                                                                                                                    
--------------------------------------------------------------------------                                                          
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                                          
--------------------------------------------------------------------------                                                          
|   0 | SELECT STATEMENT  |      |     2 |    72 |   274   (1)| 00:00:04 |                                                          
|*  1 |  TABLE ACCESS FULL| T1   |     2 |    72 |   274   (1)| 00:00:04 |                                                          
--------------------------------------------------------------------------                                                          
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 

PLAN_TABLE_OUTPUT                                                                                                                   
------------------------------------------------------------------------------------------------------------------------------------
                                                                                                                                    
   1 - filter("OBJECT_NAME"='DUAL')                                                                                                 

13 rows selected.

SQL> 
SQL> set autotrace on
SQL> 
SQL> select object_type, object_name
  2  from
  3  (select object_type, object_name from t1)
  4  where object_name = 'DUAL';

OBJECT_TYPE         OBJECT_NAME                                                                                                     
------------------- ------------------------------                                                                                  
TABLE               DUAL                                                                                                            
SYNONYM             DUAL                                                                                                            


Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 3617692013                                                                                                         
                                                                                                                                    
--------------------------------------------------------------------------                                                          
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                                          
--------------------------------------------------------------------------                                                          
|   0 | SELECT STATEMENT  |      |     2 |    72 |   274   (1)| 00:00:04 |                                                          
|*  1 |  TABLE ACCESS FULL| T1   |     2 |    72 |   274   (1)| 00:00:04 |                                                          
--------------------------------------------------------------------------                                                          
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   1 - filter("OBJECT_NAME"='DUAL')                                                                                                 


Statistics
----------------------------------------------------------                                                                          
          1  recursive calls                                                                                                        
          0  db block gets                                                                                                          
        983  consistent gets                                                                                                        
          0  physical reads                                                                                                         
          0  redo size                                                                                                              
        674  bytes sent via SQL*Net to client                                                                                       
        499  bytes received via SQL*Net from client                                                                                 
          2  SQL*Net roundtrips to/from client                                                                                      
          0  sorts (memory)                                                                                                         
          0  sorts (disk)                                                                                                           
          2  rows processed                                                                                                         

SQL> 
SQL> select object_type, object_name, object_rank
  2  from
  3  (select object_type, object_name, row_number() over (partition by object_type order by created) object_rank from t1)
  4  where object_name = 'DUAL';

OBJECT_TYPE         OBJECT_NAME                    OBJECT_RANK                                                                      
------------------- ------------------------------ -----------                                                                      
SYNONYM             DUAL                                     1                                                                      
TABLE               DUAL                                    43                                                                      


Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 2273146475                                                                                                         
                                                                                                                                    
------------------------------------------------------------------------------------                                                
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                                
------------------------------------------------------------------------------------                                                
|   0 | SELECT STATEMENT    |      | 68395 |  2738K|       |  1042   (1)| 00:00:13 |                                                
|*  1 |  VIEW               |      | 68395 |  2738K|       |  1042   (1)| 00:00:13 |                                                
|   2 |   WINDOW SORT       |      | 68395 |  2938K|  3784K|  1042   (1)| 00:00:13 |                                                
|   3 |    TABLE ACCESS FULL| T1   | 68395 |  2938K|       |   274   (1)| 00:00:04 |                                                
------------------------------------------------------------------------------------                                                
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   1 - filter("OBJECT_NAME"='DUAL')                                                                                                 


Statistics
----------------------------------------------------------                                                                          
          1  recursive calls                                                                                                        
          0  db block gets                                                                                                          
        982  consistent gets                                                                                                        
          0  physical reads                                                                                                         
          0  redo size                                                                                                              
        753  bytes sent via SQL*Net to client                                                                                       
        499  bytes received via SQL*Net from client                                                                                 
          2  SQL*Net roundtrips to/from client                                                                                      
          1  sorts (memory)                                                                                                         

          0  sorts (disk)                                                                                                           
          2  rows processed                                                                                                         

SQL> 
SQL> set autotrace off
SQL> 
SQL> connect bing/##########
Connected.
SQL> 
SQL> set echo on linesize 132
SQL> 
SQL> alter session set optimizer_dynamic_sampling=11;

Session altered.

SQL> 
SQL> EXPLAIN PLAN FOR
  2  SELECT object_type
  3  	  , object_name
  4  	  , object_rank
  5  FROM (
  6  	       SELECT object_type
  7  		    , object_name
  8  		    , ROW_NUMBER() OVER (PARTITION BY object_type ORDER BY created) object_rank
  9  	       FROM   t1
 10  )
 11  WHERE object_name = 'DUAL';

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT                                                                                                                   
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2273146475                                                                                                         
                                                                                                                                    
------------------------------------------------------------------------------------                                                
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                                
------------------------------------------------------------------------------------                                                
|   0 | SELECT STATEMENT    |      | 68395 |  2738K|       |  1042   (1)| 00:00:13 |                                                
|*  1 |  VIEW               |      | 68395 |  2738K|       |  1042   (1)| 00:00:13 |                                                
|   2 |   WINDOW SORT       |      | 68395 |  2938K|  3784K|  1042   (1)| 00:00:13 |                                                
|   3 |    TABLE ACCESS FULL| T1   | 68395 |  2938K|       |   274   (1)| 00:00:04 |                                                
------------------------------------------------------------------------------------                                                
                                                                                                                                    

PLAN_TABLE_OUTPUT                                                                                                                   
------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   1 - filter("OBJECT_NAME"='DUAL')                                                                                                 
                                                                                                                                    
Note                                                                                                                                
-----                                                                                                                               
   - dynamic sampling used for this statement (level=11)                                                                            

19 rows selected.

SQL> 
SQL> set autotrace on
SQL> 
SQL> select object_type, object_name
  2  from
  3  (select object_type, object_name from t1)
  4  where object_name = 'DUAL';

OBJECT_TYPE         OBJECT_NAME                                                                                                     
------------------- ------------------------------                                                                                  
TABLE               DUAL                                                                                                            
SYNONYM             DUAL                                                                                                            


Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 3617692013                                                                                                         
                                                                                                                                    
--------------------------------------------------------------------------                                                          
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                                          
--------------------------------------------------------------------------                                                          
|   0 | SELECT STATEMENT  |      |     2 |    72 |   274   (1)| 00:00:04 |                                                          
|*  1 |  TABLE ACCESS FULL| T1   |     2 |    72 |   274   (1)| 00:00:04 |                                                          
--------------------------------------------------------------------------                                                          
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   1 - filter("OBJECT_NAME"='DUAL')                                                                                                 
                                                                                                                                    
Note                                                                                                                                
-----                                                                                                                               
   - dynamic sampling used for this statement (level=11)                                                                            


Statistics
----------------------------------------------------------                                                                          
         12  recursive calls                                                                                                        
          0  db block gets                                                                                                          
       1788  consistent gets                                                                                                        
          1  physical reads                                                                                                         
          0  redo size                                                                                                              
        674  bytes sent via SQL*Net to client                                                                                       
        500  bytes received via SQL*Net from client                                                                                 
          2  SQL*Net roundtrips to/from client                                                                                      
          0  sorts (memory)                                                                                                         
          0  sorts (disk)                                                                                                           
          2  rows processed                                                                                                         

SQL> 

So far, so good, as the cardinality accurately reflects the actual number of rows for DUAL in the ALL_OBJECTS view. Let’s add the ROW_NUMBER() analytic function and see what changes:


SQL> select object_type, object_name, object_rank
  2  from
  3  (select object_type, object_name, row_number() over (partition by object_type order by created) object_rank from t1)
  4  where object_name = 'DUAL';

OBJECT_TYPE         OBJECT_NAME                    OBJECT_RANK                                                                      
------------------- ------------------------------ -----------                                                                      
SYNONYM             DUAL                                     1                                                                      
TABLE               DUAL                                    43                                                                      


Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 2273146475                                                                                                         
                                                                                                                                    
------------------------------------------------------------------------------------                                                
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                                
------------------------------------------------------------------------------------                                                
|   0 | SELECT STATEMENT    |      | 68395 |  2738K|       |  1042   (1)| 00:00:13 |                                                
|*  1 |  VIEW               |      | 68395 |  2738K|       |  1042   (1)| 00:00:13 |                                                
|   2 |   WINDOW SORT       |      | 68395 |  2938K|  3784K|  1042   (1)| 00:00:13 |                                                
|   3 |    TABLE ACCESS FULL| T1   | 68395 |  2938K|       |   274   (1)| 00:00:04 |                                                
------------------------------------------------------------------------------------                                                
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   1 - filter("OBJECT_NAME"='DUAL')                                                                                                 
                                                                                                                                    
Note                                                                                                                                
-----                                                                                                                               
   - dynamic sampling used for this statement (level=11)                                                                            


Statistics
----------------------------------------------------------                                                                          
          7  recursive calls                                                                                                        
          0  db block gets                                                                                                          
        989  consistent gets                                                                                                        
          0  physical reads                                                                                                         
          0  redo size                                                                                                              
        753  bytes sent via SQL*Net to client                                                                                       
        500  bytes received via SQL*Net from client                                                                                 
          2  SQL*Net roundtrips to/from client                                                                                      
          1  sorts (memory)                                                                                                         
          0  sorts (disk)                                                                                                           
          2  rows processed                                                                                                         

SQL> 
SQL> set autotrace off

Interestingly (to put it mildly) the cardinality has jumped to over 30,000 times its original value. Maybe it’s the way the stats were gathered; another run is made, this time gathering stats for the entire schema (fortunately it contains only this one table):


SQL> 
SQL> exec dbms_stats.gather_schema_stats(user)

PL/SQL procedure successfully completed.

SQL> 
SQL> EXPLAIN PLAN FOR
  2  SELECT object_type
  3  	  , object_name
  4  FROM (
  5  	       SELECT object_type
  6  		    , object_name
  7  	       FROM   t1
  8  )
  9  WHERE object_name = 'DUAL';

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT                                                                                                                   
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013                                                                                                         
                                                                                                                                    
--------------------------------------------------------------------------                                                          
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                                          
--------------------------------------------------------------------------                                                          
|   0 | SELECT STATEMENT  |      |     2 |    72 |   274   (1)| 00:00:04 |                                                          
|*  1 |  TABLE ACCESS FULL| T1   |     2 |    72 |   274   (1)| 00:00:04 |                                                          
--------------------------------------------------------------------------                                                          
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 

PLAN_TABLE_OUTPUT                                                                                                                   
------------------------------------------------------------------------------------------------------------------------------------
                                                                                                                                    
   1 - filter("OBJECT_NAME"='DUAL')                                                                                                 
                                                                                                                                    
Note                                                                                                                                
-----                                                                                                                               
   - dynamic sampling used for this statement (level=11)                                                                            

17 rows selected.

SQL> 
SQL> set autotrace on
SQL> 
SQL> select object_type, object_name
  2  from
  3  (select object_type, object_name from t1)
  4  where object_name = 'DUAL';

OBJECT_TYPE         OBJECT_NAME                                                                                                     
------------------- ------------------------------                                                                                  
TABLE               DUAL                                                                                                            
SYNONYM             DUAL                                                                                                            


Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 3617692013                                                                                                         
                                                                                                                                    
--------------------------------------------------------------------------                                                          
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                                          
--------------------------------------------------------------------------                                                          
|   0 | SELECT STATEMENT  |      |     2 |    72 |   274   (1)| 00:00:04 |                                                          
|*  1 |  TABLE ACCESS FULL| T1   |     2 |    72 |   274   (1)| 00:00:04 |                                                          
--------------------------------------------------------------------------                                                          
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   1 - filter("OBJECT_NAME"='DUAL')                                                                                                 
                                                                                                                                    
Note                                                                                                                                
-----                                                                                                                               
   - dynamic sampling used for this statement (level=11)                                                                            


Statistics
----------------------------------------------------------                                                                          
          0  recursive calls                                                                                                        
          0  db block gets                                                                                                          
        983  consistent gets                                                                                                        
          0  physical reads                                                                                                         
          0  redo size                                                                                                              
        674  bytes sent via SQL*Net to client                                                                                       
        500  bytes received via SQL*Net from client                                                                                 
          2  SQL*Net roundtrips to/from client                                                                                      
          0  sorts (memory)                                                                                                         
          0  sorts (disk)                                                                                                           
          2  rows processed                                                                                                         

SQL> 
SQL> select object_type, object_name, object_rank
  2  from
  3  (select object_type, object_name, row_number() over (partition by object_type order by created) object_rank from t1)
  4  where object_name = 'DUAL';

OBJECT_TYPE         OBJECT_NAME                    OBJECT_RANK                                                                      
------------------- ------------------------------ -----------                                                                      
SYNONYM             DUAL                                     1                                                                      
TABLE               DUAL                                    43                                                                      


Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 2273146475                                                                                                         
                                                                                                                                    
------------------------------------------------------------------------------------                                                
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                                
------------------------------------------------------------------------------------                                                
|   0 | SELECT STATEMENT    |      | 68395 |  2738K|       |  1042   (1)| 00:00:13 |                                                
|*  1 |  VIEW               |      | 68395 |  2738K|       |  1042   (1)| 00:00:13 |                                                
|   2 |   WINDOW SORT       |      | 68395 |  2938K|  3784K|  1042   (1)| 00:00:13 |                                                
|   3 |    TABLE ACCESS FULL| T1   | 68395 |  2938K|       |   274   (1)| 00:00:04 |                                                
------------------------------------------------------------------------------------                                                
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   1 - filter("OBJECT_NAME"='DUAL')                                                                                                 
                                                                                                                                    
Note                                                                                                                                
-----                                                                                                                               
   - dynamic sampling used for this statement (level=11)                                                                            


Statistics
----------------------------------------------------------                                                                          
          0  recursive calls                                                                                                        
          0  db block gets                                                                                                          
        982  consistent gets                                                                                                        
          0  physical reads                                                                                                         
          0  redo size                                                                                                              
        753  bytes sent via SQL*Net to client                                                                                       
        500  bytes received via SQL*Net from client                                                                                 
          2  SQL*Net roundtrips to/from client                                                                                      
          1  sorts (memory)                                                                                                         
          0  sorts (disk)                                                                                                           
          2  rows processed                                                                                                         

SQL> 
SQL> set autotrace off
SQL> 
SQL> connect bing/##########
Connected.
SQL> 
SQL> set echo on linesize 132
SQL> 
SQL> alter session set optimizer_dynamic_sampling=11;

Session altered.

SQL> 
SQL> EXPLAIN PLAN FOR
  2  SELECT object_type
  3  	  , object_name
  4  	  , object_rank
  5  FROM (
  6  	       SELECT object_type
  7  		    , object_name
  8  		    , ROW_NUMBER() OVER (PARTITION BY object_type ORDER BY created) object_rank
  9  	       FROM   t1
 10  )
 11  WHERE object_name = 'DUAL';

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT                                                                                                                   
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2273146475                                                                                                         
                                                                                                                                    
------------------------------------------------------------------------------------                                                
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                                
------------------------------------------------------------------------------------                                                
|   0 | SELECT STATEMENT    |      | 68395 |  2738K|       |  1042   (1)| 00:00:13 |                                                
|*  1 |  VIEW               |      | 68395 |  2738K|       |  1042   (1)| 00:00:13 |                                                
|   2 |   WINDOW SORT       |      | 68395 |  2938K|  3784K|  1042   (1)| 00:00:13 |                                                
|   3 |    TABLE ACCESS FULL| T1   | 68395 |  2938K|       |   274   (1)| 00:00:04 |                                                
------------------------------------------------------------------------------------                                                
                                                                                                                                    

PLAN_TABLE_OUTPUT                                                                                                                   
------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   1 - filter("OBJECT_NAME"='DUAL')                                                                                                 
                                                                                                                                    
Note                                                                                                                                
-----                                                                                                                               
   - dynamic sampling used for this statement (level=11)                                                                            

19 rows selected.

SQL> 
SQL> set autotrace on
SQL> 
SQL> select object_type, object_name
  2  from
  3  (select object_type, object_name from t1)
  4  where object_name = 'DUAL';

OBJECT_TYPE         OBJECT_NAME                                                                                                     
------------------- ------------------------------                                                                                  
TABLE               DUAL                                                                                                            
SYNONYM             DUAL                                                                                                            


Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 3617692013                                                                                                         
                                                                                                                                    
--------------------------------------------------------------------------                                                          
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                                          
--------------------------------------------------------------------------                                                          
|   0 | SELECT STATEMENT  |      |     2 |    72 |   274   (1)| 00:00:04 |                                                          
|*  1 |  TABLE ACCESS FULL| T1   |     2 |    72 |   274   (1)| 00:00:04 |                                                          
--------------------------------------------------------------------------                                                          
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   1 - filter("OBJECT_NAME"='DUAL')                                                                                                 
                                                                                                                                    
Note                                                                                                                                
-----                                                                                                                               
   - dynamic sampling used for this statement (level=11)                                                                            


Statistics
----------------------------------------------------------                                                                          
          0  recursive calls                                                                                                        
          0  db block gets                                                                                                          
        983  consistent gets                                                                                                        
          0  physical reads                                                                                                         
          0  redo size                                                                                                              
        674  bytes sent via SQL*Net to client                                                                                       
        499  bytes received via SQL*Net from client                                                                                 
          2  SQL*Net roundtrips to/from client                                                                                      
          0  sorts (memory)                                                                                                         
          0  sorts (disk)                                                                                                           
          2  rows processed                                                                                                         

SQL> 
SQL> select object_type, object_name, object_rank
  2  from
  3  (select object_type, object_name, row_number() over (partition by object_type order by created) object_rank from t1)
  4  where object_name = 'DUAL';

OBJECT_TYPE         OBJECT_NAME                    OBJECT_RANK                                                                      
------------------- ------------------------------ -----------                                                                      
SYNONYM             DUAL                                     1                                                                      
TABLE               DUAL                                    43                                                                      


Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 2273146475                                                                                                         
                                                                                                                                    
------------------------------------------------------------------------------------                                                
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                                
------------------------------------------------------------------------------------                                                
|   0 | SELECT STATEMENT    |      | 68395 |  2738K|       |  1042   (1)| 00:00:13 |                                                
|*  1 |  VIEW               |      | 68395 |  2738K|       |  1042   (1)| 00:00:13 |                                                
|   2 |   WINDOW SORT       |      | 68395 |  2938K|  3784K|  1042   (1)| 00:00:13 |                                                
|   3 |    TABLE ACCESS FULL| T1   | 68395 |  2938K|       |   274   (1)| 00:00:04 |                                                
------------------------------------------------------------------------------------                                                
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   1 - filter("OBJECT_NAME"='DUAL')                                                                                                 
                                                                                                                                    
Note                                                                                                                                
-----                                                                                                                               
   - dynamic sampling used for this statement (level=11)                                                                            


Statistics
----------------------------------------------------------                                                                          
          0  recursive calls                                                                                                        
          0  db block gets                                                                                                          
        982  consistent gets                                                                                                        
          0  physical reads                                                                                                         
          0  redo size                                                                                                              
        753  bytes sent via SQL*Net to client                                                                                       
        499  bytes received via SQL*Net from client                                                                                 
          2  SQL*Net roundtrips to/from client                                                                                      
          1  sorts (memory)                                                                                                         
          0  sorts (disk)                                                                                                           
          2  rows processed                                                                                                         

SQL> 
SQL> set autotrace off
SQL> 
SQL> spool off

In both attempts (gathering table stats and gathering schema stats) the dynamic sampling setting was changed from 2 (the default for this database) to 11, and both autotrace and dbms_xplan were used to generate the execution plans. To clear out the environment set by autotrace the user was logged in again before running any SQL statements. No change to the results was noticed (meaning that the generated plan didn’t change using one method over the other) so autotrace had no ‘adverse’ effect on the outcome. Oracle has recently proclaimed this as a bug which can be found on MOS :


Bug 20591362 - WRONG CARDINALITY WHEN ANALYTICS FUNCTION IS USED

In a nutshell the CBO ignores the selectivity of the WHERE clause when an analytic function is introduced into a query and as a result computes the incorrect cardinality.

On a side note if your 11.2.0.x installation does not include Oracle JVM you can run afoul of even stranger numbers:


BANNER                                                                                                                              
--------------------------------------------------------------------------------                                                    
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production                                                        
PL/SQL Release 11.2.0.4.0 - Production                                                                                              
CORE	11.2.0.4.0	Production                                                                                                          
TNS for 64-bit Windows: Version 11.2.0.4.0 - Production                                                                             
NLSRTL Version 11.2.0.4.0 - Production                                                                                              

SQL> 
SQL> create table t1
  2  as
  3  select * From all_objects;

Table created.

SQL> 
SQL> exec dbms_stats.gather_table_stats(user, 'T1')

PL/SQL procedure successfully completed.

SQL> 
SQL> EXPLAIN PLAN FOR
  2  SELECT object_type
  3  	  , object_name
  4  FROM (
  5  	       SELECT object_type
  6  		    , object_name
  7  	       FROM   t1
  8  )
  9  WHERE object_name = 'DUAL';

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT                                                                                                                   
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013                                                                                                         
                                                                                                                                    
--------------------------------------------------------------------------                                                          
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                                          
--------------------------------------------------------------------------                                                          
|   0 | SELECT STATEMENT  |      |     1 |    26 |    56   (0)| 00:00:01 |                                                          
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    26 |    56   (0)| 00:00:01 |                                                          
--------------------------------------------------------------------------                                                          
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 

PLAN_TABLE_OUTPUT                                                                                                                   
------------------------------------------------------------------------------------------------------------------------------------
                                                                                                                                    
   1 - filter("OBJECT_NAME"='DUAL')                                                                                                 

13 rows selected.

SQL> 
SQL> set autotrace on
SQL> 
SQL> select object_type, object_name
  2  from
  3  (select object_type, object_name from t1)
  4  where object_name = 'DUAL';

OBJECT_TYPE         OBJECT_NAME                                                                                                     
------------------- ------------------------------                                                                                  
TABLE               DUAL                                                                                                            
SYNONYM             DUAL                                                                                                            


Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 3617692013                                                                                                         
                                                                                                                                    
--------------------------------------------------------------------------                                                          
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                                          
--------------------------------------------------------------------------                                                          
|   0 | SELECT STATEMENT  |      |     1 |    26 |    56   (0)| 00:00:01 |                                                          
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    26 |    56   (0)| 00:00:01 |                                                          
--------------------------------------------------------------------------                                                          
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   1 - filter("OBJECT_NAME"='DUAL')                                                                                                 


Statistics
----------------------------------------------------------                                                                          
          1  recursive calls                                                                                                        
          0  db block gets                                                                                                          
        191  consistent gets                                                                                                        
          0  physical reads                                                                                                         
          0  redo size                                                                                                              
        674  bytes sent via SQL*Net to client                                                                                       
        500  bytes received via SQL*Net from client                                                                                 
          2  SQL*Net roundtrips to/from client                                                                                      
          0  sorts (memory)                                                                                                         
          0  sorts (disk)                                                                                                           
          2  rows processed                                                                                                         

SQL> 
SQL> select object_type, object_name, object_rank
  2  from
  3  (select object_type, object_name, row_number() over (partition by object_type order by created) object_rank from t1)
  4  where object_name = 'DUAL';

OBJECT_TYPE         OBJECT_NAME                    OBJECT_RANK                                                                      
------------------- ------------------------------ -----------                                                                      
SYNONYM             DUAL                                     1                                                                      
TABLE               DUAL                                    43                                                                      


Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 2273146475                                                                                                         
                                                                                                                                    
------------------------------------------------------------------------------------                                                
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                                
------------------------------------------------------------------------------------                                                
|   0 | SELECT STATEMENT    |      | 14281 |   571K|       |   190   (2)| 00:00:03 |                                                
|*  1 |  VIEW               |      | 14281 |   571K|       |   190   (2)| 00:00:03 |                                                
|   2 |   WINDOW SORT       |      | 14281 |   474K|   632K|   190   (2)| 00:00:03 |                                                
|   3 |    TABLE ACCESS FULL| T1   | 14281 |   474K|       |    56   (0)| 00:00:01 |                                                
------------------------------------------------------------------------------------                                                
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   1 - filter("OBJECT_NAME"='DUAL')                                                                                                 


Statistics
----------------------------------------------------------                                                                          
          1  recursive calls                                                                                                        
          0  db block gets                                                                                                          
        190  consistent gets                                                                                                        
          0  physical reads                                                                                                         
          0  redo size                                                                                                              
        753  bytes sent via SQL*Net to client                                                                                       
        500  bytes received via SQL*Net from client                                                                                 
          2  SQL*Net roundtrips to/from client                                                                                      
          1  sorts (memory)                                                                                                         
          0  sorts (disk)                                                                                                           
          2  rows processed                                                                                                         

SQL> 
SQL> set autotrace off
SQL> 
SQL> connect bing/##########
Connected.
SQL> 
SQL> set echo on linesize 132
SQL> 
SQL> alter session set optimizer_dynamic_sampling=11;

Session altered.

SQL> 
SQL> EXPLAIN PLAN FOR
  2  SELECT object_type
  3  	  , object_name
  4  	  , object_rank
  5  FROM (
  6  	       SELECT object_type
  7  		    , object_name
  8  		    , ROW_NUMBER() OVER (PARTITION BY object_type ORDER BY created) object_rank
  9  	       FROM   t1
 10  )
 11  WHERE object_name = 'DUAL';

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT                                                                                                                   
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2273146475                                                                                                         
                                                                                                                                    
------------------------------------------------------------------------------------                                                
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                                
------------------------------------------------------------------------------------                                                
|   0 | SELECT STATEMENT    |      | 14281 |   571K|       |   190   (2)| 00:00:03 |                                                
|*  1 |  VIEW               |      | 14281 |   571K|       |   190   (2)| 00:00:03 |                                                
|   2 |   WINDOW SORT       |      | 14281 |   474K|   632K|   190   (2)| 00:00:03 |                                                
|   3 |    TABLE ACCESS FULL| T1   | 14281 |   474K|       |    56   (0)| 00:00:01 |                                                
------------------------------------------------------------------------------------                                                
                                                                                                                                    

PLAN_TABLE_OUTPUT                                                                                                                   
------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   1 - filter("OBJECT_NAME"='DUAL')                                                                                                 
                                                                                                                                    
Note                                                                                                                                
-----                                                                                                                               
   - dynamic sampling used for this statement (level=11)                                                                            

19 rows selected.

SQL> 
SQL> set autotrace on
SQL> 
SQL> select object_type, object_name
  2  from
  3  (select object_type, object_name from t1)
  4  where object_name = 'DUAL';

OBJECT_TYPE         OBJECT_NAME                                                                                                     
------------------- ------------------------------                                                                                  
TABLE               DUAL                                                                                                            
SYNONYM             DUAL                                                                                                            


Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 3617692013                                                                                                         
                                                                                                                                    
--------------------------------------------------------------------------                                                          
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                                          
--------------------------------------------------------------------------                                                          
|   0 | SELECT STATEMENT  |      |     1 |    26 |    56   (0)| 00:00:01 |                                                          
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    26 |    56   (0)| 00:00:01 |                                                          
--------------------------------------------------------------------------                                                          
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   1 - filter("OBJECT_NAME"='DUAL')                                                                                                 
                                                                                                                                    
Note                                                                                                                                
-----                                                                                                                               
   - dynamic sampling used for this statement (level=11)                                                                            


Statistics
----------------------------------------------------------                                                                          
         11  recursive calls                                                                                                        
          0  db block gets                                                                                                          
        385  consistent gets                                                                                                        
          0  physical reads                                                                                                         
          0  redo size                                                                                                              
        674  bytes sent via SQL*Net to client                                                                                       
        499  bytes received via SQL*Net from client                                                                                 
          2  SQL*Net roundtrips to/from client                                                                                      
          0  sorts (memory)                                                                                                         
          0  sorts (disk)                                                                                                           
          2  rows processed                                                                                                         

SQL> 
SQL> select object_type, object_name, object_rank
  2  from
  3  (select object_type, object_name, row_number() over (partition by object_type order by created) object_rank from t1)
  4  where object_name = 'DUAL';

OBJECT_TYPE         OBJECT_NAME                    OBJECT_RANK                                                                      
------------------- ------------------------------ -----------                                                                      
SYNONYM             DUAL                                     1                                                                      
TABLE               DUAL                                    43                                                                      


Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 2273146475                                                                                                         
                                                                                                                                    
------------------------------------------------------------------------------------                                                
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                                
------------------------------------------------------------------------------------                                                
|   0 | SELECT STATEMENT    |      | 14281 |   571K|       |   190   (2)| 00:00:03 |                                                
|*  1 |  VIEW               |      | 14281 |   571K|       |   190   (2)| 00:00:03 |                                                
|   2 |   WINDOW SORT       |      | 14281 |   474K|   632K|   190   (2)| 00:00:03 |                                                
|   3 |    TABLE ACCESS FULL| T1   | 14281 |   474K|       |    56   (0)| 00:00:01 |                                                
------------------------------------------------------------------------------------                                                
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   1 - filter("OBJECT_NAME"='DUAL')                                                                                                 
                                                                                                                                    
Note                                                                                                                                
-----                                                                                                                               
   - dynamic sampling used for this statement (level=11)                                                                            


Statistics
----------------------------------------------------------                                                                          
          7  recursive calls                                                                                                        
          0  db block gets                                                                                                          
        196  consistent gets                                                                                                        
          0  physical reads                                                                                                         
          0  redo size                                                                                                              
        753  bytes sent via SQL*Net to client                                                                                       
        499  bytes received via SQL*Net from client                                                                                 
          2  SQL*Net roundtrips to/from client                                                                                      
          1  sorts (memory)                                                                                                         
          0  sorts (disk)                                                                                                           
          2  rows processed                                                                                                         

SQL> 
SQL> set autotrace off
SQL> 
SQL> exec dbms_stats.gather_schema_stats(user)

PL/SQL procedure successfully completed.

SQL> 
SQL> EXPLAIN PLAN FOR
  2  SELECT object_type
  3  	  , object_name
  4  FROM (
  5  	       SELECT object_type
  6  		    , object_name
  7  	       FROM   t1
  8  )
  9  WHERE object_name = 'DUAL';

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT                                                                                                                   
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013                                                                                                         
                                                                                                                                    
--------------------------------------------------------------------------                                                          
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                                          
--------------------------------------------------------------------------                                                          
|   0 | SELECT STATEMENT  |      |     1 |    26 |    56   (0)| 00:00:01 |                                                          
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    26 |    56   (0)| 00:00:01 |                                                          
--------------------------------------------------------------------------                                                          
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 

PLAN_TABLE_OUTPUT                                                                                                                   
------------------------------------------------------------------------------------------------------------------------------------
                                                                                                                                    
   1 - filter("OBJECT_NAME"='DUAL')                                                                                                 
                                                                                                                                    
Note                                                                                                                                
-----                                                                                                                               
   - dynamic sampling used for this statement (level=11)                                                                            

17 rows selected.

SQL> 
SQL> set autotrace on
SQL> 
SQL> select object_type, object_name
  2  from
  3  (select object_type, object_name from t1)
  4  where object_name = 'DUAL';

OBJECT_TYPE         OBJECT_NAME                                                                                                     
------------------- ------------------------------                                                                                  
TABLE               DUAL                                                                                                            
SYNONYM             DUAL                                                                                                            


Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 3617692013                                                                                                         
                                                                                                                                    
--------------------------------------------------------------------------                                                          
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                                          
--------------------------------------------------------------------------                                                          
|   0 | SELECT STATEMENT  |      |     1 |    26 |    56   (0)| 00:00:01 |                                                          
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    26 |    56   (0)| 00:00:01 |                                                          
--------------------------------------------------------------------------                                                          
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   1 - filter("OBJECT_NAME"='DUAL')                                                                                                 
                                                                                                                                    
Note                                                                                                                                
-----                                                                                                                               
   - dynamic sampling used for this statement (level=11)                                                                            


Statistics
----------------------------------------------------------                                                                          
          0  recursive calls                                                                                                        
          0  db block gets                                                                                                          
        191  consistent gets                                                                                                        
          0  physical reads                                                                                                         
          0  redo size                                                                                                              
        674  bytes sent via SQL*Net to client                                                                                       
        499  bytes received via SQL*Net from client                                                                                 
          2  SQL*Net roundtrips to/from client                                                                                      
          0  sorts (memory)                                                                                                         
          0  sorts (disk)                                                                                                           
          2  rows processed                                                                                                         

SQL> 
SQL> select object_type, object_name, object_rank
  2  from
  3  (select object_type, object_name, row_number() over (partition by object_type order by created) object_rank from t1)
  4  where object_name = 'DUAL';

OBJECT_TYPE         OBJECT_NAME                    OBJECT_RANK                                                                      
------------------- ------------------------------ -----------                                                                      
SYNONYM             DUAL                                     1                                                                      
TABLE               DUAL                                    43                                                                      


Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 2273146475                                                                                                         
                                                                                                                                    
------------------------------------------------------------------------------------                                                
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                                
------------------------------------------------------------------------------------                                                
|   0 | SELECT STATEMENT    |      | 14281 |   571K|       |   190   (2)| 00:00:03 |                                                
|*  1 |  VIEW               |      | 14281 |   571K|       |   190   (2)| 00:00:03 |                                                
|   2 |   WINDOW SORT       |      | 14281 |   474K|   632K|   190   (2)| 00:00:03 |                                                
|   3 |    TABLE ACCESS FULL| T1   | 14281 |   474K|       |    56   (0)| 00:00:01 |                                                
------------------------------------------------------------------------------------                                                
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   1 - filter("OBJECT_NAME"='DUAL')                                                                                                 
                                                                                                                                    
Note                                                                                                                                
-----                                                                                                                               
   - dynamic sampling used for this statement (level=11)                                                                            


Statistics
----------------------------------------------------------                                                                          
          0  recursive calls                                                                                                        
          0  db block gets                                                                                                          
        190  consistent gets                                                                                                        
          0  physical reads                                                                                                         
          0  redo size                                                                                                              
        753  bytes sent via SQL*Net to client                                                                                       
        499  bytes received via SQL*Net from client                                                                                 
          2  SQL*Net roundtrips to/from client                                                                                      
          1  sorts (memory)                                                                                                         
          0  sorts (disk)                                                                                                           
          2  rows processed                                                                                                         

SQL> 
SQL> set autotrace off
SQL> 
SQL> connect bing/##########
Connected.
SQL> 
SQL> set echo on linesize 132
SQL> 
SQL> alter session set optimizer_dynamic_sampling=11;

Session altered.

SQL> 
SQL> EXPLAIN PLAN FOR
  2  SELECT object_type
  3  	  , object_name
  4  	  , object_rank
  5  FROM (
  6  	       SELECT object_type
  7  		    , object_name
  8  		    , ROW_NUMBER() OVER (PARTITION BY object_type ORDER BY created) object_rank
  9  	       FROM   t1
 10  )
 11  WHERE object_name = 'DUAL';

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT                                                                                                                   
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2273146475                                                                                                         
                                                                                                                                    
------------------------------------------------------------------------------------                                                
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                                
------------------------------------------------------------------------------------                                                
|   0 | SELECT STATEMENT    |      | 14281 |   571K|       |   190   (2)| 00:00:03 |                                                
|*  1 |  VIEW               |      | 14281 |   571K|       |   190   (2)| 00:00:03 |                                                
|   2 |   WINDOW SORT       |      | 14281 |   474K|   632K|   190   (2)| 00:00:03 |                                                
|   3 |    TABLE ACCESS FULL| T1   | 14281 |   474K|       |    56   (0)| 00:00:01 |                                                
------------------------------------------------------------------------------------                                                
                                                                                                                                    

PLAN_TABLE_OUTPUT                                                                                                                   
------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   1 - filter("OBJECT_NAME"='DUAL')                                                                                                 
                                                                                                                                    
Note                                                                                                                                
-----                                                                                                                               
   - dynamic sampling used for this statement (level=11)                                                                            

19 rows selected.

SQL> 
SQL> set autotrace on
SQL> 
SQL> select object_type, object_name
  2  from
  3  (select object_type, object_name from t1)
  4  where object_name = 'DUAL';

OBJECT_TYPE         OBJECT_NAME                                                                                                     
------------------- ------------------------------                                                                                  
TABLE               DUAL                                                                                                            
SYNONYM             DUAL                                                                                                            


Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 3617692013                                                                                                         
                                                                                                                                    
--------------------------------------------------------------------------                                                          
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                                          
--------------------------------------------------------------------------                                                          
|   0 | SELECT STATEMENT  |      |     1 |    26 |    56   (0)| 00:00:01 |                                                          
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    26 |    56   (0)| 00:00:01 |                                                          
--------------------------------------------------------------------------                                                          
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   1 - filter("OBJECT_NAME"='DUAL')                                                                                                 
                                                                                                                                    
Note                                                                                                                                
-----                                                                                                                               
   - dynamic sampling used for this statement (level=11)                                                                            


Statistics
----------------------------------------------------------                                                                          
          0  recursive calls                                                                                                        
          0  db block gets                                                                                                          
        191  consistent gets                                                                                                        
          0  physical reads                                                                                                         
          0  redo size                                                                                                              
        674  bytes sent via SQL*Net to client                                                                                       
        500  bytes received via SQL*Net from client                                                                                 
          2  SQL*Net roundtrips to/from client                                                                                      
          0  sorts (memory)                                                                                                         
          0  sorts (disk)                                                                                                           
          2  rows processed                                                                                                         

SQL> 
SQL> select object_type, object_name, object_rank
  2  from
  3  (select object_type, object_name, row_number() over (partition by object_type order by created) object_rank from t1)
  4  where object_name = 'DUAL';

OBJECT_TYPE         OBJECT_NAME                    OBJECT_RANK                                                                      
------------------- ------------------------------ -----------                                                                      
SYNONYM             DUAL                                     1                                                                      
TABLE               DUAL                                    43                                                                      


Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 2273146475                                                                                                         
                                                                                                                                    
------------------------------------------------------------------------------------                                                
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                                
------------------------------------------------------------------------------------                                                
|   0 | SELECT STATEMENT    |      | 14281 |   571K|       |   190   (2)| 00:00:03 |                                                
|*  1 |  VIEW               |      | 14281 |   571K|       |   190   (2)| 00:00:03 |                                                
|   2 |   WINDOW SORT       |      | 14281 |   474K|   632K|   190   (2)| 00:00:03 |                                                
|   3 |    TABLE ACCESS FULL| T1   | 14281 |   474K|       |    56   (0)| 00:00:01 |                                                
------------------------------------------------------------------------------------                                                
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   1 - filter("OBJECT_NAME"='DUAL')                                                                                                 
                                                                                                                                    
Note                                                                                                                                
-----                                                                                                                               
   - dynamic sampling used for this statement (level=11)                                                                            


Statistics
----------------------------------------------------------                                                                          
          0  recursive calls                                                                                                        
          0  db block gets                                                                                                          
        190  consistent gets                                                                                                        
          0  physical reads                                                                                                         
          0  redo size                                                                                                              
        753  bytes sent via SQL*Net to client                                                                                       
        500  bytes received via SQL*Net from client                                                                                 
          2  SQL*Net roundtrips to/from client                                                                                      
          1  sorts (memory)                                                                                                         
          0  sorts (disk)                                                                                                           
          2  rows processed                                                                                                         

SQL> 
SQL> set autotrace off
SQL> 
SQL> spool off

Notice that the number of rows reported in the execution plans for the non-analytic query are half of what they are supposed to be, and the inflated number of rows is roughly one-fourth of the previously reported value. In the absence of the JVM the data dictionary is ‘short’ around 50,000 rows, which can skew the selectivity down to around 1 for the example query. Installing the JVM, using dbca, fixes that issue.

Until this bug is fixed by a one-off patch pay close attention to the row estimates produced for queries using analytic functions; at the least this little ‘inconvenience’ could make troubleshooting performance issues difficult. You may need to eliminate the analytic function to reveal a better estimate of the number of rows returned so you’re not chasing an issue that doesn’t really exist.

See all articles by David Fitzjarrell

David Fitzjarrell
David Fitzjarrell has more than 20 years of administration experience with various releases of the Oracle DBMS. He has installed the Oracle software on many platforms, including UNIX, Windows and Linux, and monitored and tuned performance in those environments. He is knowledgeable in the traditional tools for performance tuning – the Oracle Wait Interface, Statspack, event 10046 and 10053 traces, tkprof, explain plan and autotrace – and has used these to great advantage at the U.S. Postal Service, American Airlines/SABRE, ConocoPhilips and SiriusXM Radio, among others, to increase throughput and improve the quality of the production system. He has also set up scripts to regularly monitor available space and set thresholds to notify DBAs of impending space shortages before they affect the production environment. These scripts generate data which can also used to trend database growth over time, aiding in capacity planning. He has used RMAN, Streams, RAC and Data Guard in Oracle installations to ensure full recoverability and failover capabilities as well as high availability, and has configured a 'cascading' set of DR databases using the primary DR databases as the source, managing the archivelog transfers manually and montoring, through scripts, the health of these secondary DR databases. He has also used ASM, ASMM and ASSM to improve performance and manage storage and shared memory.

Latest Articles