Oracle Tips and Tricks — David Fitzjarrell

March 30, 2015

Sound Advice

Filed under: Performance — dfitzjarrell @ 09:33

"There is much worth noticing that often escapes the eye."
- Norton Juster, The Phantom Tollbooth

Oracle has provided views to assist in managing the SGA and PGA settings when you’re not using Automatic Memory Management (AMM) and a view to manage memory_target when you are using AMM. These views were designed to be fairly easy to interpret but it seems that there has been confusion of late in how to actually use the recommendations Oracle is presenting. Let’s fix that.

Starting with 10gR2 Oracle provided both V$SGA_TARGET_ADVICE and V$PGA_TARGET_ADVICE to assist the DBA in sizing the SGA and PGA based on how the database was using memory. The definitions of these views (as of 11.2.0.4) are shown below:


SQL> desc v$sga_target_advice
 Name                                                                     Null?    Type
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 SGA_SIZE                                                                          NUMBER
 SGA_SIZE_FACTOR                                                                   NUMBER
 ESTD_DB_TIME                                                                      NUMBER
 ESTD_DB_TIME_FACTOR                                                               NUMBER
 ESTD_PHYSICAL_READS                                                               NUMBER

SQL> desc v$pga_target_advice
 Name                                                                     Null?    Type
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 PGA_TARGET_FOR_ESTIMATE                                                           NUMBER
 PGA_TARGET_FACTOR                                                                 NUMBER
 ADVICE_STATUS                                                                     VARCHAR2(3)
 BYTES_PROCESSED                                                                   NUMBER
 ESTD_TIME                                                                         NUMBER
 ESTD_EXTRA_BYTES_RW                                                               NUMBER
 ESTD_PGA_CACHE_HIT_PERCENTAGE                                                     NUMBER
 ESTD_OVERALLOC_COUNT                                                              NUMBER

SQL>

Nothing unusual or esoteric here, however it appears that interpreting the provided advice can be confusing. Looking first at the contents of V$SGA_TARGET_ADVICE let’s see what Oracle reports:


SQL> select * From v$sga_target_advice
  2  /

  SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
       520              .5        65793              5.8208             2579079
       780             .75        23911              2.1155             2068005
      1040               1        11303                   1             1134459
      1300            1.25         9162               .8106              970643
      1560             1.5         9098               .8049              970643
      1820            1.75         9058               .8014              970643
      2080               2         9058               .8014              970643

7 rows selected.

SQL>

Based on resource usage since startup this view provides information on SGA memory settings that may improve performance if adopted. The key element in any of these views is the size factor, with a size factor of 1 being the current setting. Looking at the above output it’s possible that physical reads can be reduced a small amount if sga_target is increased from 1040 MB to 1300 MB; any further increase to sga_target will show no improvement. This is proven by the ESTD_PHYSICAL_READS column, the value of which stays constant for an SGA sized at 1300 MB or greater. In addition the ESTD_DB_TIME_FACTOR doesn’t improve much at greater SGA sizes indicating that further increases provide no real benefit. Ideally the goal is to find the setting providing substantial benefit and use it; the problem appears to be ‘where do I stop looking?’ With the above output we stopped looking for improvement at the first entry providing the lowest estimated physical reads. All entries below that provided no additional benefit.

If sga_target and sga_max_size are set diffently (to allow some degree of dynamic SGA management) it may not be necessary to restart the database since sga_target may be able to be set without adjusting sga_max_size. This view may, however, provide an SGA setting that requires both sga_target and sga_max_size be adjusted, and in that case the database will need to be restarted.

In a similar fashion the V$PGA_TARGET_ADVICE view can also be used to ‘fine tune’ overall PGA settings for the database:


SQL> select * From v$pga_target_advice;

PGA_TARGET_FOR_ESTIMATE PGA_TARGET_FACTOR ADV BYTES_PROCESSED  ESTD_TIME ESTD_EXTRA_BYTES_RW ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT
----------------------- ----------------- --- --------------- ---------- ------------------- ----------------------------- --------------------
               73400320              .125 ON       7.4905E+10   37173880          1395450880                            98                   88
              146800640               .25 ON       7.4905E+10   36716537           456747008                            99                    6
              293601280                .5 ON       7.4905E+10   36643991           307845120                           100                    0
              440401920               .75 ON       7.4905E+10   36494007                   0                           100                    0
              587202560                 1 ON       7.4905E+10   36494007                   0                           100                    0
              704643072               1.2 ON       7.4905E+10   36494007                   0                           100                    0
              822083584               1.4 ON       7.4905E+10   36494007                   0                           100                    0
              939524096               1.6 ON       7.4905E+10   36494007                   0                           100                    0
             1056964608               1.8 ON       7.4905E+10   36494007                   0                           100                    0
             1174405120                 2 ON       7.4905E+10   36494007                   0                           100                    0
             1761607680                 3 ON       7.4905E+10   36494007                   0                           100                    0

PGA_TARGET_FOR_ESTIMATE PGA_TARGET_FACTOR ADV BYTES_PROCESSED  ESTD_TIME ESTD_EXTRA_BYTES_RW ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT
----------------------- ----------------- --- --------------- ---------- ------------------- ----------------------------- --------------------
             2348810240                 4 ON       7.4905E+10   36494007                   0                           100                    0
             3523215360                 6 ON       7.4905E+10   36494007                   0                           100                    0
             4697620480                 8 ON       7.4905E+10   36494007                   0                           100                    0

14 rows selected.

SQL>

Again it’s the target factor that identifies the current setting; working toward larger target factors may show improvement in the PGA cache hit percentage which means processes will see more optimized (one-pass) reads. Like the V$SGA_TARGET_ADVICE view the method is to find a setting that provides improvement. In this example the PGA setting is already optimal (the cache hit percentage is 100 and 0 extra bytes will need to be read). Not every database will be so lucky; this view is provided to allow the DBA to make as few changes as possible to achieve improved performance and throughput, eliminating the need for trial and error PGA management. In this case the database will need to be restarted after the parameter values have been changed.

To restate the methodology of using these two views:


1.	Find the row referencing the current setting; this will be the row where the size factor is equal to 1
2.	Scan the data for the entry just before the improvement estimates no longer change; this will be the setting
	Oracle has calculated to provide the most benefit
3.	Implement that setting, restarting the database, if necessary, to effect the change

In a similar fashion when using Automatic Memory Management, by setting memory_target and memory_max_target, the V$MEMORY_TARGET_ADVICE view can be used to tune the overall memory settings. Output from this view looks like this:


SQL> select * From v$memory_target_advice;

MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR    VERSION     CON_ID
----------- ------------------ ------------ ------------------- ---------- ----------
        392                .25       143586              1.0063          0          0
        784                 .5       142687                   1          0          0
       1176                .75       142687                   1          0          0
       1568                  1       142687                   1          0          0
       1960               1.25       142687                   1          0          0
       2352                1.5       142687                   1          0          0
       2744               1.75       142687                   1          0          0
       3136                  2       142687                   1          0          0

8 rows selected.

SQL>

The same methodology is used here, and if memory_max_target is larger than the suggested memory_target value the setting can be changed without restarting the database. Like the SGA settings if memory_max_target needs to also be adjusted this will require a database restart. Note that if you’re using Linux and hugepages using AMM is not supported.

Managing database memory settings doesn’t need to be a gruelling task of ‘try this, see if it works, it doesn’t so try another setting and repeat’. Using the provided memory advisors can make the DBA’s job much easier. Hopefully this has made that task easier, freeing the DBA to address more pressing issues.

Sometimes it’s good to take things under advisement.

March 12, 2015

Extra-cost Option?

Filed under: General,Performance — dfitzjarrell @ 09:14

"Just because you have a choice, it doesn't mean that any of them 'has' to be right."
 Norton Juster, The Phantom Tollbooth 

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’ affect on the outcome. Oracle has recently proclaimed this as a bug:


Bug 20591362 - WRONG CARDINALITY WHEN ANALYTICS FUNCTION IS USED

which can be found on MOS. In a nutshell the CBO ignores the selectivity of the WHERE clause when an analytic fuunction 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.

Numbers never lie, until they do.

Create a free website or blog at WordPress.com.