In an Oracle forum that I frequent a user posted that he found cardinality values to be severely inflated when using an analytic function. This user posted an example that will be used here to demonstrate the problem. It will also be used to demonstrate another issue that can make this problem even more confusing, but the latter issue is one that can easily be fixed.
The example script contains the following code, which includes statements not executed by the user posting the problem (creating a new user to run the example from, using an additional statistics gathering method to verify the method wasn’t part of the cause and modifying the dynamic sampling setting), but which have no effect on the results generated:
connect bing/#############
spool wrong_card_ex.log
select * From v$version;
set echo on linesize 132
create table t1
as
select * From all_objects;
exec dbms_stats.gather_table_stats(user, 'T1')
EXPLAIN PLAN FOR
SELECT object_type
, object_name
FROM (
SELECT object_type
, object_name
FROM t1
)
WHERE object_name = 'DUAL';
SELECT * FROM TABLE(dbms_xplan.display);
set autotrace on
select object_type, object_name
from
(select object_type, object_name from t1)
where object_name = 'DUAL';
select object_type, object_name, object_rank
from
(select object_type, object_name, row_number() over (partition by object_type order by created) object_rank from t1)
where object_name = 'DUAL';
set autotrace off
connect bing/#############
set echo on linesize 132
alter session set optimizer_dynamic_sampling=11;
EXPLAIN PLAN FOR
SELECT object_type
, object_name
, object_rank
FROM (
SELECT object_type
, object_name
, ROW_NUMBER() OVER (PARTITION BY object_type ORDER BY created) object_rank
FROM t1
)
WHERE object_name = 'DUAL';
SELECT * FROM TABLE(dbms_xplan.display);
set autotrace on
select object_type, object_name
from
(select object_type, object_name from t1)
where object_name = 'DUAL';
select object_type, object_name, object_rank
from
(select object_type, object_name, row_number() over (partition by object_type order by created) object_rank from t1)
where object_name = 'DUAL';
set autotrace off
exec dbms_stats.gather_schema_stats(user)
EXPLAIN PLAN FOR
SELECT object_type
, object_name
FROM (
SELECT object_type
, object_name
FROM t1
)
WHERE object_name = 'DUAL';
SELECT * FROM TABLE(dbms_xplan.display);
set autotrace on
select object_type, object_name
from
(select object_type, object_name from t1)
where object_name = 'DUAL';
select object_type, object_name, object_rank
from
(select object_type, object_name, row_number() over (partition by object_type order by created) object_rank from t1)
where object_name = 'DUAL';
set autotrace off
connect bing/#############
set echo on linesize 132
alter session set optimizer_dynamic_sampling=11;
EXPLAIN PLAN FOR
SELECT object_type
, object_name
, object_rank
FROM (
SELECT object_type
, object_name
, ROW_NUMBER() OVER (PARTITION BY object_type ORDER BY created) object_rank
FROM t1
)
WHERE object_name = 'DUAL';
SELECT * FROM TABLE(dbms_xplan.display);
set autotrace on
select object_type, object_name
from
(select object_type, object_name from t1)
where object_name = 'DUAL';
select object_type, object_name, object_rank
from
(select object_type, object_name, row_number() over (partition by object_type order by created) object_rank from t1)
where object_name = 'DUAL';
set autotrace off
spool off
Nothing unusual there really, other than the second run of the code minus the table creation. That was done to test whether gathering schema stats over gathering table stats made a difference in the cardinality numbers; it did not. Let’s look at a run of this script in an 11.2.0.4 database and see what cardinalities Oracle generates:
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for 64-bit Windows: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL>
SQL> create table t1
2 as
3 select * From all_objects;
Table created.
SQL>
SQL> exec dbms_stats.gather_table_stats(user, 'T1')
PL/SQL procedure successfully completed.
SQL>
SQL> EXPLAIN PLAN FOR
2 SELECT object_type
3 , object_name
4 FROM (
5 SELECT object_type
6 , object_name
7 FROM t1
8 )
9 WHERE object_name = 'DUAL';
Explained.
SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 72 | 274 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T1 | 2 | 72 | 274 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
1 - filter("OBJECT_NAME"='DUAL')
13 rows selected.
SQL>
SQL> set autotrace on
SQL>
SQL> select object_type, object_name
2 from
3 (select object_type, object_name from t1)
4 where object_name = 'DUAL';
OBJECT_TYPE OBJECT_NAME
------------------- ------------------------------
TABLE DUAL
SYNONYM DUAL
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 72 | 274 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T1 | 2 | 72 | 274 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='DUAL')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
983 consistent gets
0 physical reads
0 redo size
674 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
SQL>
SQL> select object_type, object_name, object_rank
2 from
3 (select object_type, object_name, row_number() over (partition by object_type order by created) object_rank from t1)
4 where object_name = 'DUAL';
OBJECT_TYPE OBJECT_NAME OBJECT_RANK
------------------- ------------------------------ -----------
SYNONYM DUAL 1
TABLE DUAL 43
Execution Plan
----------------------------------------------------------
Plan hash value: 2273146475
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 68395 | 2738K| | 1042 (1)| 00:00:13 |
|* 1 | VIEW | | 68395 | 2738K| | 1042 (1)| 00:00:13 |
| 2 | WINDOW SORT | | 68395 | 2938K| 3784K| 1042 (1)| 00:00:13 |
| 3 | TABLE ACCESS FULL| T1 | 68395 | 2938K| | 274 (1)| 00:00:04 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='DUAL')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
982 consistent gets
0 physical reads
0 redo size
753 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2 rows processed
SQL>
SQL> set autotrace off
SQL>
SQL> connect bing/##########
Connected.
SQL>
SQL> set echo on linesize 132
SQL>
SQL> alter session set optimizer_dynamic_sampling=11;
Session altered.
SQL>
SQL> EXPLAIN PLAN FOR
2 SELECT object_type
3 , object_name
4 , object_rank
5 FROM (
6 SELECT object_type
7 , object_name
8 , ROW_NUMBER() OVER (PARTITION BY object_type ORDER BY created) object_rank
9 FROM t1
10 )
11 WHERE object_name = 'DUAL';
Explained.
SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2273146475
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 68395 | 2738K| | 1042 (1)| 00:00:13 |
|* 1 | VIEW | | 68395 | 2738K| | 1042 (1)| 00:00:13 |
| 2 | WINDOW SORT | | 68395 | 2938K| 3784K| 1042 (1)| 00:00:13 |
| 3 | TABLE ACCESS FULL| T1 | 68395 | 2938K| | 274 (1)| 00:00:04 |
------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='DUAL')
Note
-----
- dynamic sampling used for this statement (level=11)
19 rows selected.
SQL>
SQL> set autotrace on
SQL>
SQL> select object_type, object_name
2 from
3 (select object_type, object_name from t1)
4 where object_name = 'DUAL';
OBJECT_TYPE OBJECT_NAME
------------------- ------------------------------
TABLE DUAL
SYNONYM DUAL
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 72 | 274 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T1 | 2 | 72 | 274 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='DUAL')
Note
-----
- dynamic sampling used for this statement (level=11)
Statistics
----------------------------------------------------------
12 recursive calls
0 db block gets
1788 consistent gets
1 physical reads
0 redo size
674 bytes sent via SQL*Net to client
500 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
SQL>
So far, so good, as the cardinality accurately reflects the actual number of rows for DUAL in the ALL_OBJECTS view. Let’s add the ROW_NUMBER() analytic function and see what changes:
SQL> select object_type, object_name, object_rank
2 from
3 (select object_type, object_name, row_number() over (partition by object_type order by created) object_rank from t1)
4 where object_name = 'DUAL';
OBJECT_TYPE OBJECT_NAME OBJECT_RANK
------------------- ------------------------------ -----------
SYNONYM DUAL 1
TABLE DUAL 43
Execution Plan
----------------------------------------------------------
Plan hash value: 2273146475
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 68395 | 2738K| | 1042 (1)| 00:00:13 |
|* 1 | VIEW | | 68395 | 2738K| | 1042 (1)| 00:00:13 |
| 2 | WINDOW SORT | | 68395 | 2938K| 3784K| 1042 (1)| 00:00:13 |
| 3 | TABLE ACCESS FULL| T1 | 68395 | 2938K| | 274 (1)| 00:00:04 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='DUAL')
Note
-----
- dynamic sampling used for this statement (level=11)
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
989 consistent gets
0 physical reads
0 redo size
753 bytes sent via SQL*Net to client
500 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2 rows processed
SQL>
SQL> set autotrace off
Interestingly (to put it mildly) the cardinality has jumped to over 30,000 times its original value. Maybe it’s the way the stats were gathered; another run is made, this time gathering stats for the entire schema (fortunately it contains only this one table):
SQL>
SQL> exec dbms_stats.gather_schema_stats(user)
PL/SQL procedure successfully completed.
SQL>
SQL> EXPLAIN PLAN FOR
2 SELECT object_type
3 , object_name
4 FROM (
5 SELECT object_type
6 , object_name
7 FROM t1
8 )
9 WHERE object_name = 'DUAL';
Explained.
SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 72 | 274 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T1 | 2 | 72 | 274 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
1 - filter("OBJECT_NAME"='DUAL')
Note
-----
- dynamic sampling used for this statement (level=11)
17 rows selected.
SQL>
SQL> set autotrace on
SQL>
SQL> select object_type, object_name
2 from
3 (select object_type, object_name from t1)
4 where object_name = 'DUAL';
OBJECT_TYPE OBJECT_NAME
------------------- ------------------------------
TABLE DUAL
SYNONYM DUAL
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 72 | 274 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T1 | 2 | 72 | 274 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='DUAL')
Note
-----
- dynamic sampling used for this statement (level=11)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
983 consistent gets
0 physical reads
0 redo size
674 bytes sent via SQL*Net to client
500 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
SQL>
SQL> select object_type, object_name, object_rank
2 from
3 (select object_type, object_name, row_number() over (partition by object_type order by created) object_rank from t1)
4 where object_name = 'DUAL';
OBJECT_TYPE OBJECT_NAME OBJECT_RANK
------------------- ------------------------------ -----------
SYNONYM DUAL 1
TABLE DUAL 43
Execution Plan
----------------------------------------------------------
Plan hash value: 2273146475
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 68395 | 2738K| | 1042 (1)| 00:00:13 |
|* 1 | VIEW | | 68395 | 2738K| | 1042 (1)| 00:00:13 |
| 2 | WINDOW SORT | | 68395 | 2938K| 3784K| 1042 (1)| 00:00:13 |
| 3 | TABLE ACCESS FULL| T1 | 68395 | 2938K| | 274 (1)| 00:00:04 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='DUAL')
Note
-----
- dynamic sampling used for this statement (level=11)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
982 consistent gets
0 physical reads
0 redo size
753 bytes sent via SQL*Net to client
500 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2 rows processed
SQL>
SQL> set autotrace off
SQL>
SQL> connect bing/##########
Connected.
SQL>
SQL> set echo on linesize 132
SQL>
SQL> alter session set optimizer_dynamic_sampling=11;
Session altered.
SQL>
SQL> EXPLAIN PLAN FOR
2 SELECT object_type
3 , object_name
4 , object_rank
5 FROM (
6 SELECT object_type
7 , object_name
8 , ROW_NUMBER() OVER (PARTITION BY object_type ORDER BY created) object_rank
9 FROM t1
10 )
11 WHERE object_name = 'DUAL';
Explained.
SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2273146475
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 68395 | 2738K| | 1042 (1)| 00:00:13 |
|* 1 | VIEW | | 68395 | 2738K| | 1042 (1)| 00:00:13 |
| 2 | WINDOW SORT | | 68395 | 2938K| 3784K| 1042 (1)| 00:00:13 |
| 3 | TABLE ACCESS FULL| T1 | 68395 | 2938K| | 274 (1)| 00:00:04 |
------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='DUAL')
Note
-----
- dynamic sampling used for this statement (level=11)
19 rows selected.
SQL>
SQL> set autotrace on
SQL>
SQL> select object_type, object_name
2 from
3 (select object_type, object_name from t1)
4 where object_name = 'DUAL';
OBJECT_TYPE OBJECT_NAME
------------------- ------------------------------
TABLE DUAL
SYNONYM DUAL
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 72 | 274 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T1 | 2 | 72 | 274 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='DUAL')
Note
-----
- dynamic sampling used for this statement (level=11)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
983 consistent gets
0 physical reads
0 redo size
674 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
SQL>
SQL> select object_type, object_name, object_rank
2 from
3 (select object_type, object_name, row_number() over (partition by object_type order by created) object_rank from t1)
4 where object_name = 'DUAL';
OBJECT_TYPE OBJECT_NAME OBJECT_RANK
------------------- ------------------------------ -----------
SYNONYM DUAL 1
TABLE DUAL 43
Execution Plan
----------------------------------------------------------
Plan hash value: 2273146475
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 68395 | 2738K| | 1042 (1)| 00:00:13 |
|* 1 | VIEW | | 68395 | 2738K| | 1042 (1)| 00:00:13 |
| 2 | WINDOW SORT | | 68395 | 2938K| 3784K| 1042 (1)| 00:00:13 |
| 3 | TABLE ACCESS FULL| T1 | 68395 | 2938K| | 274 (1)| 00:00:04 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='DUAL')
Note
-----
- dynamic sampling used for this statement (level=11)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
982 consistent gets
0 physical reads
0 redo size
753 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2 rows processed
SQL>
SQL> set autotrace off
SQL>
SQL> spool off
In both attempts (gathering table stats and gathering schema stats) the dynamic sampling setting was changed from 2 (the default for this database) to 11, and both autotrace and dbms_xplan were used to generate the execution plans. To clear out the environment set by autotrace the user was logged in again before running any SQL statements. No change to the results was noticed (meaning that the generated plan didn’t change using one method over the other) so autotrace had no ‘adverse’ effect on the outcome. Oracle has recently proclaimed this as a bug which can be found on MOS :
Bug 20591362 - WRONG CARDINALITY WHEN ANALYTICS FUNCTION IS USED
In a nutshell the CBO ignores the selectivity of the WHERE clause when an analytic function is introduced into a query and as a result computes the incorrect cardinality.
On a side note if your 11.2.0.x installation does not include Oracle JVM you can run afoul of even stranger numbers:
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for 64-bit Windows: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL>
SQL> create table t1
2 as
3 select * From all_objects;
Table created.
SQL>
SQL> exec dbms_stats.gather_table_stats(user, 'T1')
PL/SQL procedure successfully completed.
SQL>
SQL> EXPLAIN PLAN FOR
2 SELECT object_type
3 , object_name
4 FROM (
5 SELECT object_type
6 , object_name
7 FROM t1
8 )
9 WHERE object_name = 'DUAL';
Explained.
SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 56 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 26 | 56 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
1 - filter("OBJECT_NAME"='DUAL')
13 rows selected.
SQL>
SQL> set autotrace on
SQL>
SQL> select object_type, object_name
2 from
3 (select object_type, object_name from t1)
4 where object_name = 'DUAL';
OBJECT_TYPE OBJECT_NAME
------------------- ------------------------------
TABLE DUAL
SYNONYM DUAL
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 56 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 26 | 56 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='DUAL')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
191 consistent gets
0 physical reads
0 redo size
674 bytes sent via SQL*Net to client
500 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
SQL>
SQL> select object_type, object_name, object_rank
2 from
3 (select object_type, object_name, row_number() over (partition by object_type order by created) object_rank from t1)
4 where object_name = 'DUAL';
OBJECT_TYPE OBJECT_NAME OBJECT_RANK
------------------- ------------------------------ -----------
SYNONYM DUAL 1
TABLE DUAL 43
Execution Plan
----------------------------------------------------------
Plan hash value: 2273146475
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14281 | 571K| | 190 (2)| 00:00:03 |
|* 1 | VIEW | | 14281 | 571K| | 190 (2)| 00:00:03 |
| 2 | WINDOW SORT | | 14281 | 474K| 632K| 190 (2)| 00:00:03 |
| 3 | TABLE ACCESS FULL| T1 | 14281 | 474K| | 56 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='DUAL')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
190 consistent gets
0 physical reads
0 redo size
753 bytes sent via SQL*Net to client
500 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2 rows processed
SQL>
SQL> set autotrace off
SQL>
SQL> connect bing/##########
Connected.
SQL>
SQL> set echo on linesize 132
SQL>
SQL> alter session set optimizer_dynamic_sampling=11;
Session altered.
SQL>
SQL> EXPLAIN PLAN FOR
2 SELECT object_type
3 , object_name
4 , object_rank
5 FROM (
6 SELECT object_type
7 , object_name
8 , ROW_NUMBER() OVER (PARTITION BY object_type ORDER BY created) object_rank
9 FROM t1
10 )
11 WHERE object_name = 'DUAL';
Explained.
SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2273146475
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14281 | 571K| | 190 (2)| 00:00:03 |
|* 1 | VIEW | | 14281 | 571K| | 190 (2)| 00:00:03 |
| 2 | WINDOW SORT | | 14281 | 474K| 632K| 190 (2)| 00:00:03 |
| 3 | TABLE ACCESS FULL| T1 | 14281 | 474K| | 56 (0)| 00:00:01 |
------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='DUAL')
Note
-----
- dynamic sampling used for this statement (level=11)
19 rows selected.
SQL>
SQL> set autotrace on
SQL>
SQL> select object_type, object_name
2 from
3 (select object_type, object_name from t1)
4 where object_name = 'DUAL';
OBJECT_TYPE OBJECT_NAME
------------------- ------------------------------
TABLE DUAL
SYNONYM DUAL
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 56 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 26 | 56 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='DUAL')
Note
-----
- dynamic sampling used for this statement (level=11)
Statistics
----------------------------------------------------------
11 recursive calls
0 db block gets
385 consistent gets
0 physical reads
0 redo size
674 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
SQL>
SQL> select object_type, object_name, object_rank
2 from
3 (select object_type, object_name, row_number() over (partition by object_type order by created) object_rank from t1)
4 where object_name = 'DUAL';
OBJECT_TYPE OBJECT_NAME OBJECT_RANK
------------------- ------------------------------ -----------
SYNONYM DUAL 1
TABLE DUAL 43
Execution Plan
----------------------------------------------------------
Plan hash value: 2273146475
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14281 | 571K| | 190 (2)| 00:00:03 |
|* 1 | VIEW | | 14281 | 571K| | 190 (2)| 00:00:03 |
| 2 | WINDOW SORT | | 14281 | 474K| 632K| 190 (2)| 00:00:03 |
| 3 | TABLE ACCESS FULL| T1 | 14281 | 474K| | 56 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='DUAL')
Note
-----
- dynamic sampling used for this statement (level=11)
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
196 consistent gets
0 physical reads
0 redo size
753 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2 rows processed
SQL>
SQL> set autotrace off
SQL>
SQL> exec dbms_stats.gather_schema_stats(user)
PL/SQL procedure successfully completed.
SQL>
SQL> EXPLAIN PLAN FOR
2 SELECT object_type
3 , object_name
4 FROM (
5 SELECT object_type
6 , object_name
7 FROM t1
8 )
9 WHERE object_name = 'DUAL';
Explained.
SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 56 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 26 | 56 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
1 - filter("OBJECT_NAME"='DUAL')
Note
-----
- dynamic sampling used for this statement (level=11)
17 rows selected.
SQL>
SQL> set autotrace on
SQL>
SQL> select object_type, object_name
2 from
3 (select object_type, object_name from t1)
4 where object_name = 'DUAL';
OBJECT_TYPE OBJECT_NAME
------------------- ------------------------------
TABLE DUAL
SYNONYM DUAL
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 56 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 26 | 56 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='DUAL')
Note
-----
- dynamic sampling used for this statement (level=11)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
191 consistent gets
0 physical reads
0 redo size
674 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
SQL>
SQL> select object_type, object_name, object_rank
2 from
3 (select object_type, object_name, row_number() over (partition by object_type order by created) object_rank from t1)
4 where object_name = 'DUAL';
OBJECT_TYPE OBJECT_NAME OBJECT_RANK
------------------- ------------------------------ -----------
SYNONYM DUAL 1
TABLE DUAL 43
Execution Plan
----------------------------------------------------------
Plan hash value: 2273146475
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14281 | 571K| | 190 (2)| 00:00:03 |
|* 1 | VIEW | | 14281 | 571K| | 190 (2)| 00:00:03 |
| 2 | WINDOW SORT | | 14281 | 474K| 632K| 190 (2)| 00:00:03 |
| 3 | TABLE ACCESS FULL| T1 | 14281 | 474K| | 56 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='DUAL')
Note
-----
- dynamic sampling used for this statement (level=11)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
190 consistent gets
0 physical reads
0 redo size
753 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2 rows processed
SQL>
SQL> set autotrace off
SQL>
SQL> connect bing/##########
Connected.
SQL>
SQL> set echo on linesize 132
SQL>
SQL> alter session set optimizer_dynamic_sampling=11;
Session altered.
SQL>
SQL> EXPLAIN PLAN FOR
2 SELECT object_type
3 , object_name
4 , object_rank
5 FROM (
6 SELECT object_type
7 , object_name
8 , ROW_NUMBER() OVER (PARTITION BY object_type ORDER BY created) object_rank
9 FROM t1
10 )
11 WHERE object_name = 'DUAL';
Explained.
SQL> SELECT * FROM TABLE(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2273146475
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14281 | 571K| | 190 (2)| 00:00:03 |
|* 1 | VIEW | | 14281 | 571K| | 190 (2)| 00:00:03 |
| 2 | WINDOW SORT | | 14281 | 474K| 632K| 190 (2)| 00:00:03 |
| 3 | TABLE ACCESS FULL| T1 | 14281 | 474K| | 56 (0)| 00:00:01 |
------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='DUAL')
Note
-----
- dynamic sampling used for this statement (level=11)
19 rows selected.
SQL>
SQL> set autotrace on
SQL>
SQL> select object_type, object_name
2 from
3 (select object_type, object_name from t1)
4 where object_name = 'DUAL';
OBJECT_TYPE OBJECT_NAME
------------------- ------------------------------
TABLE DUAL
SYNONYM DUAL
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 56 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 26 | 56 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='DUAL')
Note
-----
- dynamic sampling used for this statement (level=11)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
191 consistent gets
0 physical reads
0 redo size
674 bytes sent via SQL*Net to client
500 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
SQL>
SQL> select object_type, object_name, object_rank
2 from
3 (select object_type, object_name, row_number() over (partition by object_type order by created) object_rank from t1)
4 where object_name = 'DUAL';
OBJECT_TYPE OBJECT_NAME OBJECT_RANK
------------------- ------------------------------ -----------
SYNONYM DUAL 1
TABLE DUAL 43
Execution Plan
----------------------------------------------------------
Plan hash value: 2273146475
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14281 | 571K| | 190 (2)| 00:00:03 |
|* 1 | VIEW | | 14281 | 571K| | 190 (2)| 00:00:03 |
| 2 | WINDOW SORT | | 14281 | 474K| 632K| 190 (2)| 00:00:03 |
| 3 | TABLE ACCESS FULL| T1 | 14281 | 474K| | 56 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='DUAL')
Note
-----
- dynamic sampling used for this statement (level=11)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
190 consistent gets
0 physical reads
0 redo size
753 bytes sent via SQL*Net to client
500 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2 rows processed
SQL>
SQL> set autotrace off
SQL>
SQL> spool off
Notice that the number of rows reported in the execution plans for the non-analytic query are half of what they are supposed to be, and the inflated number of rows is roughly one-fourth of the previously reported value. In the absence of the JVM the data dictionary is ‘short’ around 50,000 rows, which can skew the selectivity down to around 1 for the example query. Installing the JVM, using dbca, fixes that issue.
Until this bug is fixed by a one-off patch pay close attention to the row estimates produced for queries using analytic functions; at the least this little ‘inconvenience’ could make troubleshooting performance issues difficult. You may need to eliminate the analytic function to reveal a better estimate of the number of rows returned so you’re not chasing an issue that doesn’t really exist.
