Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Rajeshwaran.

Asked: March 15, 2016 - 3:06 pm UTC

Last updated: April 29, 2016 - 5:16 pm UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

Team,

Going through this paper

http://www.soug.ch/fileadmin/user_upload/SIGs/SIG_150521_Tuning_R/Christian_Antognini_AdaptiveDynamicSampling_trivadis.pdf

On the slide#22 able understand the single table cardinality adjustments (see that in prior to 12c)

but can you provide an sample test case for Index cardinality adjustments/ join cardinality adjustments and Query block cardinality adjustments? not able to find those details on docs.



and Chris said...

It's a similar principle to single table adjustments. If the optimizer thinks the estimates are insufficient/missing/stale then it can use generate dynamic stats.

SQL> create table t as
  2    select rownum x, sysdate y from dual connect by level <= 1;
SQL>
SQL> create index i on t (x);
SQL>
SQL> insert into t
  2    select rownum+1 x, sysdate y from dual connect by level <= 999;
SQL>
SQL> commit;
SQL>
SQL> select num_rows from user_ind_statistics
  2  where  index_name = 'I';

  NUM_ROWS
----------
         1


So we have a one thousand row table. But Oracle thinks there's only one row in the index. A query finding rows where x > 900 will return 100 rows. With dynamic stats disabled (dynamic_sampling = 0) it's estimates are way off:

SQL> select /*+ gather_plan_statistics dynamic_sampling (0) */count(*) from t
  2  where  x > 900;

  COUNT(*)
----------
       100
SQL>
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST +NOTE'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------

SQL_ID  7saawtsj93tyf, child number 1
-------------------------------------
select /*+ gather_plan_statistics dynamic_sampling (0) */count(*) from
t where  x > 900

Plan hash value: 2079104444

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |      1 |00:00:00.01 |       2 |
|   1 |  SORT AGGREGATE   |      |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  2 |   INDEX RANGE SCAN| I    |      1 |     16 |    100 |00:00:00.01 |       2 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("X">900)


But enable dynamic stats (dynamic_sampling = 11) and it gets the E-rows spot on:

SQL> select /*+ gather_plan_statistics dynamic_sampling (11) */count(*) from t
  2  where  x > 900;

  COUNT(*)
----------
       100
SQL>
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST +NOTE'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------

SQL_ID  2gk8mp858dnsk, child number 0
-------------------------------------
select /*+ gather_plan_statistics dynamic_sampling (11) */count(*) from
t where  x > 900

Plan hash value: 2079104444

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |      1 |00:00:00.01 |       2 |
|   1 |  SORT AGGREGATE   |      |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  2 |   INDEX RANGE SCAN| I    |      1 |    100 |    100 |00:00:00.01 |       2 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("X">900)

Note
-----
   - dynamic statistics used: dynamic sampling (level=0)


The query is an index only scan (it doesn't access the table). So this must be dynamic index stats!

You can see similar improvements when you add a group by (query block adjustment):

SQL> select /*+ gather_plan_statistics dynamic_sampling (0) */mod(x, 10) m, count(*) from t
  2  where  x > 900
  3  group  by mod(x, 10);

         M   COUNT(*)
---------- ----------
         1         10
         6         10
         2         10
         4         10
         5         10
         8         10
         3         10
         7         10
         9         10
         0         10
SQL>
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST +NOTE'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------

SQL_ID  4gz72qvvwjz12, child number 0
-------------------------------------
select /*+ gather_plan_statistics dynamic_sampling (0) */mod(x, 10) m,
count(*) from t where  x > 900 group  by mod(x, 10)

Plan hash value: 1380848886

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |     10 |00:00:00.01 |       2 |
|   1 |  HASH GROUP BY    |      |      1 |     33 |     10 |00:00:00.01 |       2 |
|*  2 |   INDEX RANGE SCAN| I    |      1 |     33 |    100 |00:00:00.01 |       2 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("X">900)

SQL>
SQL> select /*+ gather_plan_statistics dynamic_sampling (11) */mod(x, 10) m, count(*) from t
  2  where  x > 900
  3  group  by mod(x, 10);

         M   COUNT(*)
---------- ----------
         1         10
         6         10
         2         10
         4         10
         5         10
         8         10
         3         10
         7         10
         9         10
         0         10
SQL>
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST +NOTE'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------

SQL_ID  3p9f80dwhv2rj, child number 0
-------------------------------------
select /*+ gather_plan_statistics dynamic_sampling (11) */mod(x, 10) m,
count(*) from t where  x > 900 group  by mod(x, 10)

Plan hash value: 1380848886

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |     10 |00:00:00.01 |       2 |
|   1 |  HASH GROUP BY    |      |      1 |     10 |     10 |00:00:00.01 |       2 |
|*  2 |   INDEX RANGE SCAN| I    |      1 |    100 |    100 |00:00:00.01 |       2 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("X">900)

Note
-----
   - dynamic statistics used: dynamic sampling (level=0)


Without the dynamic stats Oracle guessed the group by would return 33 rows. But it actually gives 10. With dynamic stats the estimate is correct.

You can see similar with joining:

SQL> select /*+ gather_plan_statistics dynamic_sampling (0) */count(*)
  2  from   t t1
  3  join   t t2
  4  on     mod(t1.x, 100) = mod(t2.x, 10);

  COUNT(*)
----------
     10000
SQL>
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST +NOTE'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------

SQL_ID  70txpy61g9byg, child number 0
-------------------------------------
select /*+ gather_plan_statistics dynamic_sampling (0) */count(*) from
 t t1 join   t t2 on     mod(t1.x, 100) = mod(t2.x, 10)

Plan hash value: 791582492

--------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      1 |00:00:00.01 |      14 |
|   1 |  SORT AGGREGATE     |      |      1 |      1 |      1 |00:00:00.01 |      14 |
|*  2 |   HASH JOIN         |      |      1 |   4277 |  10000 |00:00:00.01 |      14 |
|   3 |    TABLE ACCESS FULL| T    |      1 |    654 |   1000 |00:00:00.01 |       7 |
|   4 |    TABLE ACCESS FULL| T    |      1 |    654 |   1000 |00:00:00.01 |       7 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(MOD("T1"."X",100)=MOD("T2"."X",10))

SQL>
SQL> select /*+ gather_plan_statistics dynamic_sampling (11) */count(*)
  2  from   t t1
  3  join   t t2
  4  on     mod(t1.x, 100) = mod(t2.x, 10);

  COUNT(*)
----------
     10000
SQL>
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST +NOTE'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------

SQL_ID  8s93pjnzqxh3x, child number 0
-------------------------------------
select /*+ gather_plan_statistics dynamic_sampling (11) */count(*) from
  t t1 join   t t2 on     mod(t1.x, 100) = mod(t2.x, 10)

Plan hash value: 791582492

--------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      1 |00:00:00.01 |      14 |
|   1 |  SORT AGGREGATE     |      |      1 |      1 |      1 |00:00:00.01 |      14 |
|*  2 |   HASH JOIN         |      |      1 |  10000 |  10000 |00:00:00.01 |      14 |
|   3 |    TABLE ACCESS FULL| T    |      1 |    654 |   1000 |00:00:00.01 |       7 |
|   4 |    TABLE ACCESS FULL| T    |      1 |    654 |   1000 |00:00:00.01 |       7 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(MOD("T1"."X",100)=MOD("T2"."X",10))

Note
-----
   - dynamic statistics used: dynamic sampling (level=0)


Notice the E-rows for the join is now correct (vs. 5,723 out without this).

Rating

  (10 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

This helps.

Rajeshwaran Jeyabal, March 16, 2016 - 6:14 am UTC

Thanks Chris, This help us.

Notice the E-rows for the join is now correct (vs. 5,723 out without this).

rather that should be like this.

Notice the E-rows for the join is now correct (vs. 4277 out without this).
Connor McDonald
March 16, 2016 - 11:13 am UTC

It's out (wrong) by 10,000 - 4,277 = 5,723.

Dynamic statistics level = 11

Rajeshwaran Jeyabal, March 16, 2016 - 6:40 am UTC

http://docs.oracle.com/database/121/TGSQL/tgsql_astat.htm#TGSQL453

Use dynamic statistics automatically when the optimizer deems it necessary. The resulting statistics are persistent in the statistics repository, making them available to other queries.


rajesh@ORA12C> create table t
  2  as
  3  select *
  4  from all_objects;

Table created.

rajesh@ORA12C>
rajesh@ORA12C> column column_name format a12
rajesh@ORA12C> select column_name,num_distinct,num_nulls,density,histogram
  2  from user_tab_col_statistics
  3  where table_name ='T'
  4  and column_name in ('OWNER','OBJECT_TYPE');

COLUMN_NAME  NUM_DISTINCT  NUM_NULLS    DENSITY HISTOGRAM
------------ ------------ ---------- ---------- ---------------
OWNER                  33          0  .03030303 NONE
OBJECT_TYPE            40          0       .025 NONE

2 rows selected.

rajesh@ORA12C> select num_rows from user_tables where table_name ='T';

  NUM_ROWS
----------
     90075

1 row selected.

rajesh@ORA12C> select 90075*1/40*1/33 estimated_card from dual;

ESTIMATED_CARD
--------------
    68.2386364

1 row selected.


With basic statistics in place, the predicates on OWNER=?? AND OBJECT_TYPE = ?? report the estimated cardinality as 68.

rajesh@ORA12C> set autotrace on explain
rajesh@ORA12C> select count(*)
  2  from t
  3  where owner ='SCOTT'
  4  and object_type ='TABLE';

  COUNT(*)
----------
         4

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    15 |   422   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    15 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |    68 |  1020 |   422   (1)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_TYPE"='TABLE' AND "OWNER"='SCOTT')

As per the plan, the estimates are way off to actual, so with dynamic sampling in place

rajesh@ORA12C> select /*+ dynamic_sampling(11) */ sum(object_id),count(*)
  2  from t
  3  where owner ='SCOTT'
  4  and object_type ='TABLE';

SUM(OBJECT_ID)   COUNT(*)
-------------- ----------
        373227          4

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    20 |   422   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    20 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |     4 |    80 |   422   (1)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_TYPE"='TABLE' AND "OWNER"='SCOTT')

Note
-----
   - dynamic statistics used: dynamic sampling (level=0)


Estimates matches with Actual.

rajesh@ORA12C> select /*+ dynamic_sampling(11) */ min(object_id),count(*)
  2  from t
  3  where owner ='SCOTT'
  4  and object_type ='TABLE';

MIN(OBJECT_ID)   COUNT(*)
-------------- ----------
         93304          4

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    20 |   422   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    20 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |     4 |    80 |   422   (1)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OBJECT_TYPE"='TABLE' AND "OWNER"='SCOTT')

Note
-----
   - dynamic statistics used: dynamic sampling (level=0)


So how do we proof that the dynamic sampling generated for the previous SQL (the one has sum(object_id)) is preserved in the repository and got shared to this SQL (the one has min(object_id))
Connor McDonald
April 21, 2016 - 9:45 am UTC

Oracle stores dynamic stats in the result cache. It doesn't persist these to disk.

The dynamic sampling queries have a DS_SVC comment in them. So if you trace your session you can see these. If the same DS query is executed multiple times, then Oracle may be using the stored stats (if they're still in the result cache).

Query Block Cardinality Adjustments

Rajeshwaran Jeyabal, March 16, 2016 - 7:51 am UTC

Could you help me to understand, With dynamic_sampling set to 11, why the estimates on index access doesn't match up with actual (but the estimates match up with Table access)?

rajesh@ORA12C> select /*+ gather_plan_statistics dynamic_sampling(11) */ owner,count(*)
  2  from t where id > 900
  3  group by owner;

OWNER        COUNT(*)
---------- ----------
SYS                99

1 row selected.

rajesh@ORA12C> select * from table(dbms_xplan.display_cursor(format=>'IOSTATS last +NOTE'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------
SQL_ID  2krs8fczmtw68, child number 0
-------------------------------------
select /*+ gather_plan_statistics dynamic_sampling(11) */
owner,count(*) from t where id > 900 group by owner

Plan hash value: 1301711901

---------------------------------------------------------------------------------
| Id  | Operation                            | Name  | Starts | E-Rows | A-Rows |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |       |      1 |        |      1 |
|   1 |  HASH GROUP BY                       |       |      1 |      1 |      1 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T     |      1 |     99 |     99 |
|*  3 |    INDEX RANGE SCAN                  | T_IDX |      1 |      1 |     99 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("ID">900)

Note
-----
   - dynamic statistics used: dynamic sampling (level=0)


25 rows selected.

rajesh@ORA12C>


In case if index is used as skinny version of table, then estimates match up with actual at index access.

rajesh@ORA12C>
rajesh@ORA12C> select /*+ gather_plan_statistics dynamic_sampling(11) */ mod(id,2),count(*)
  2  from t where id > 900
  3  group by mod(id,2);

 MOD(ID,2)   COUNT(*)
---------- ----------
         1         50
         0         49

2 rows selected.

rajesh@ORA12C> select * from table(dbms_xplan.display_cursor(format=>'IOSTATS last +NOTE'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
SQL_ID  dn78px0t5jc5v, child number 0
-------------------------------------
select /*+ gather_plan_statistics dynamic_sampling(11) */
mod(id,2),count(*) from t where id > 900 group by mod(id,2)

Plan hash value: 1203281196

--------------------------------------------------------------
| Id  | Operation         | Name  | Starts | E-Rows | A-Rows |
--------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |      1 |        |      2 |
|   1 |  HASH GROUP BY    |       |      1 |      2 |      2 |
|*  2 |   INDEX RANGE SCAN| T_IDX |      1 |     99 |     99 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID">900)

Note
-----
   - dynamic statistics used: dynamic sampling (level=0)


24 rows selected.

rajesh@ORA12C>


Incase if test case script needed, please make use of this.

drop table stage purge;
create table stage as
select a.*, rownum as id 
from all_objects a;

drop table t purge;
create table t as
select * from stage
where id = 2;

create index t_idx on t(id);

select num_rows from user_ind_statistics
where table_name ='T';

insert into t select * from stage where id <=999;
commit;

select num_rows from user_ind_statistics
where table_name ='T';

show parameter optimizer_dynamic_sampling

set serveroutput off
select /*+ gather_plan_statistics */ owner,count(*)
from t where id > 900
group by owner;
select * from table(dbms_xplan.display_cursor(format=>'IOSTATS last +NOTE'));

select /*+ gather_plan_statistics dynamic_sampling(11) */ owner,count(*)
from t where id > 900
group by owner;
select * from table(dbms_xplan.display_cursor(format=>'IOSTATS last +NOTE'));


select /*+ gather_plan_statistics dynamic_sampling(11) */ mod(id,2),count(*)
from t where id > 900
group by mod(id,2);
select * from table(dbms_xplan.display_cursor(format=>'IOSTATS last +NOTE'));

Connor McDonald
April 21, 2016 - 9:48 am UTC

I'm not able to reproduce this. The estimates and actuals all match up for me.

SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c EE Extreme Perf Release 12.1.0.2.0 - 64bit Production                 0
PL/SQL Release 12.1.0.2.0 - Production                                                    0
CORE    12.1.0.2.0      Production                                                                0
TNS for Linux: Version 12.1.0.2.0 - Production                                            0
NLSRTL Version 12.1.0.2.0 - Production                                                    0

SQL> show parameter optimizer_dynamic_sampling

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling           integer     2

SQL> select /*+ gather_plan_statistics dynamic_sampling(11) */ owner,count(*)
  2  from t where id > 900
  3  group by owner;

OWNER                            COUNT(*)
------------------------------ ----------
SYS                                    99

SQL> select * from table(dbms_xplan.display_cursor(format=>'IOSTATS last +NOTE'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

SQL_ID  2krs8fczmtw68, child number 0
-------------------------------------
select /*+ gather_plan_statistics dynamic_sampling(11) */
owner,count(*) from t where id > 900 group by owner

Plan hash value: 1834049183

------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |      1 |        |      1 |00:00:00.01 |       4 |
|   1 |  HASH GROUP BY               |       |      1 |      1 |      1 |00:00:00.01 |       4 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T     |      1 |     99 |     99 |00:00:00.01 |       4 |
|*  3 |    INDEX RANGE SCAN          | T_IDX |      1 |     99 |     99 |00:00:00.01 |       2 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("ID">900)

Note
-----
   - dynamic statistics used: dynamic sampling (level=0)


25 rows selected.

SQL>
SQL>
SQL> select /*+ gather_plan_statistics dynamic_sampling(11) */ mod(id,2),count(*)
  2  from t where id > 900
  3  group by mod(id,2);

 MOD(ID,2)   COUNT(*)
---------- ----------
         1         50
         0         49

SQL> select * from table(dbms_xplan.display_cursor(format=>'IOSTATS last +NOTE'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

SQL_ID  dn78px0t5jc5v, child number 0
-------------------------------------
select /*+ gather_plan_statistics dynamic_sampling(11) */
mod(id,2),count(*) from t where id > 900 group by mod(id,2)

Plan hash value: 1203281196

-------------------------------------------------------------------------------------
| Id  | Operation         | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |      1 |        |      2 |00:00:00.01 |       2 |
|   1 |  HASH GROUP BY    |       |      1 |      2 |      2 |00:00:00.01 |       2 |
|*  2 |   INDEX RANGE SCAN| T_IDX |      1 |     99 |     99 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID">900)

Note
-----
   - dynamic statistics used: dynamic sampling (level=0)


24 rows selected.

above two followups

Rajeshwaran Jeyabal, March 18, 2016 - 4:27 am UTC

Team,

Any additional details missing to answer the above two followup's ?
Connor McDonald
March 18, 2016 - 7:20 am UTC

Chris and I will be chatting with Optimizer team in the near future. We'll bring this up and keep you posted.

Cheers,
Connor

Updates from Optimizer Team ?!?!?

Rajeshwaran Jeyabal, April 15, 2016 - 2:49 pm UTC

Team - Any updates from Optimizer Team on this?
Chris Saxon
April 15, 2016 - 3:30 pm UTC

We'll update you when there is.

Query Block Cardinality Adjustments

Rajeshwaran Jeyabal, April 22, 2016 - 2:37 am UTC

Team,

Here is my test execution, that shows up cardinality doesn't matches up for index range scan.

rajesh@ORA12C> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0
PL/SQL Release 12.1.0.2.0 - Production                                                    0
CORE    12.1.0.2.0      Production
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production                                   0
NLSRTL Version 12.1.0.2.0 - Production                                                    0

5 rows selected.

rajesh@ORA12C>
rajesh@ORA12C> column owner format a15
rajesh@ORA12C> create table stage as
  2  select a.*, rownum as id
  3  from all_objects a;

Table created.

rajesh@ORA12C>
rajesh@ORA12C> create table t as
  2  select * from stage
  3  where id = 2;

Table created.

rajesh@ORA12C> create index t_idx on t(id);

Index created.

rajesh@ORA12C>
rajesh@ORA12C> select num_rows from user_ind_statistics
  2  where table_name ='T';

  NUM_ROWS
----------
         1

1 row selected.

rajesh@ORA12C>
rajesh@ORA12C> insert into t select * from stage where id <=999;

999 rows created.

rajesh@ORA12C> commit;

Commit complete.

rajesh@ORA12C>
rajesh@ORA12C> select num_rows from user_ind_statistics
  2  where table_name ='T';

  NUM_ROWS
----------
         1

1 row selected.

rajesh@ORA12C>
rajesh@ORA12C> show parameter optimizer_dynamic_sampling

NAME                                          TYPE        VALUE
--------------------------------------------- ----------- -------------------------
optimizer_dynamic_sampling                    integer     2
rajesh@ORA12C> set serveroutput off
rajesh@ORA12C> select /*+ gather_plan_statistics dynamic_sampling(11) */ owner,count(*)
  2  from t where id > 900
  3  group by owner;

OWNER             COUNT(*)
--------------- ----------
SYS                     99

1 row selected.

rajesh@ORA12C> select * from table(dbms_xplan.display_cursor(format=>'IOSTATS last +NOTE'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
SQL_ID  2krs8fczmtw68, child number 0
-------------------------------------
select /*+ gather_plan_statistics dynamic_sampling(11) */
owner,count(*) from t where id > 900 group by owner

Plan hash value: 1301711901

--------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |       |      1 |        |      1 |00:00:00.01 |       4 |
|   1 |  HASH GROUP BY                       |       |      1 |      1 |      1 |00:00:00.01 |       4 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T     |      1 |     99 |     99 |00:00:00.01 |       4 |
|*  3 |    INDEX RANGE SCAN                  | T_IDX |      1 |      1 |     99 |00:00:00.01 |       2 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("ID">900)

Note
-----
   - dynamic statistics used: dynamic sampling (level=0)


25 rows selected.

rajesh@ORA12C>

Connor McDonald
April 22, 2016 - 12:12 pm UTC

Well we can play spot the difference between our environments or you can take this up with support.

I'm using an Oracle Cloud Extreme Performance DBaaS with default settings.

query on v$version.

Rajeshwaran Jeyabal, April 22, 2016 - 2:40 am UTC

Team,

your query on v$version show this.

SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c EE Extreme Perf Release 12.1.0.2.0 - 64bit Production                 0
PL/SQL Release 12.1.0.2.0 - Production                                                    0
CORE    12.1.0.2.0      Production                                                                0
TNS for Linux: Version 12.1.0.2.0 - Production                                            0
NLSRTL Version 12.1.0.2.0 - Production                                                    0


but my query on v$version show this.

rajesh@ORA12C> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0
PL/SQL Release 12.1.0.2.0 - Production                                                    0
CORE    12.1.0.2.0      Production                                                                0
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production                                   0
NLSRTL Version 12.1.0.2.0 - Production                                                    0

5 rows selected.

rajesh@ORA12C>


The first line of query output has this "Oracle Database 12c EE Extreme Perf Release 12.1.0.2.0" - what is "EE Extreme Perf" ?
Connor McDonald
April 22, 2016 - 12:15 pm UTC

It's using the Oracle Cloud DBaaS.

Extreme Performance is one of the options available:

https://cloud.oracle.com/en_US/database?resolvetemplatefordevice=true&tabID=1406491812773

Basically EE plus these options:

In-Memory Database, RAC (Real Application Clusters), Active Data Guard, Multitenant, Partitioning, Real Application Testing, Advanced Compression, Advanced Security, Label Security, Database Vault, OLAP, Advanced Analytics, Spatial and Graph, Diagnostics Pack, Tuning Pack, Database Lifecycle Management Pack, Data Masking & Subsetting Pack and Cloud Management Pack for Oracle Database.

Dynamic statistics level = 11

Rajeshwaran Jeyabal, April 22, 2016 - 7:27 am UTC

Oracle stores dynamic stats in the result cache. It doesn't persist these to disk.
The dynamic sampling queries have a DS_SVC comment in them. So if you trace your session you can see these. If the same DS query is executed multiple times, then Oracle may be using the stored stats (if they're still in the result cache).


Thanks for that details, it helps.

Here is my test case for that

rajesh@ORA12C> create table t as select * from all_objects;

Table created.

rajesh@ORA12C> column column_name format a12
rajesh@ORA12C> select column_name,num_distinct,num_nulls,density,histogram
  2  from user_tab_col_statistics
  3  where table_name ='T'
  4  and column_name in ('OWNER','OBJECT_TYPE');

COLUMN_NAME  NUM_DISTINCT  NUM_NULLS    DENSITY HISTOGRAM
------------ ------------ ---------- ---------- ---------------
OWNER                  32          0     .03125 NONE
OBJECT_TYPE            40          0       .025 NONE

2 rows selected.

rajesh@ORA12C> @tkfilename.sql
D:\APP\VNAMEIT\diag\rdbms\ora12c\ora12c\trace\ora12c_ora_5792.trc


PL/SQL procedure successfully completed.

rajesh@ORA12C> @tktrace.sql

Session altered.


Session altered.

rajesh@ORA12C> set serveroutput off
rajesh@ORA12C> select /*+ dynamic_sampling(11) */ count(*) ,sum(object_id)
  2  from t
  3  where owner ='SCOTT'
  4  and object_type ='TABLE';

  COUNT(*) SUM(OBJECT_ID)
---------- --------------
         4         373227

1 row selected.

rajesh@ORA12C> select /*+ dynamic_sampling(11) */ count(*) ,min(object_id)
  2  from t
  3  where owner ='SCOTT'
  4  and object_type ='TABLE';

  COUNT(*) MIN(OBJECT_ID)
---------- --------------
         4          93304

1 row selected.

rajesh@ORA12C> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options


Tkprof shows me this

SQL ID: 0na0vpd3q0u79 Plan Hash: 1807638002

SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring 
  optimizer_features_enable(default) no_parallel result_cache(snapshot=3600) 
  */ SUM(C1) 
FROM
 (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "T")  */ 1 AS C1 FROM "T" 
  SAMPLE BLOCK(51.6796, 8) SEED(1)  "T" WHERE ("T"."OBJECT_TYPE"='TABLE') AND 
  ("T"."OWNER"='SCOTT')) innerQuery


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.01       3.05         53         39          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.01       3.06         53         41          0           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 111     (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  RESULT CACHE  69r7shhu4tvbdg27g20jb08sap (cr=0 pr=0 pw=0 time=1 us)
         0          0          0   SORT AGGREGATE (cr=0 pr=0 pw=0 time=9 us)
         0          0          0    TABLE ACCESS SAMPLE T (cr=0 pr=0 pw=0 time=4 us cost=210 size=972 card=36)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file scattered read                          9        1.47          3.05
--------------------------------------------------------------------------------

select /*+ dynamic_sampling(11) */ count(*) ,sum(object_id)
from t
where owner ='SCOTT'
and object_type ='TABLE'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.01          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01      14.13       1514       1517          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.04      14.15       1514       1518          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 111  
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=1517 pr=1514 pw=0 time=14137004 us)
         4          4          4   TABLE ACCESS FULL T (cr=1517 pr=1514 pw=0 time=14136976 us cost=405 size=1400 card=70)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  reliable message                                1        1.83          1.83
  enq: KO - fast object checkpoint                1        0.07          0.07
  direct path read                               48        1.57         12.10
  SQL*Net message from client                     2        7.77          7.78
********************************************************************************

SQL ID: 0na0vpd3q0u79 Plan Hash: 1807638002

SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring 
  optimizer_features_enable(default) no_parallel result_cache(snapshot=3600) 
  */ SUM(C1) 
FROM
 (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "T")  */ 1 AS C1 FROM "T" 
  SAMPLE BLOCK(51.6796, 8) SEED(1)  "T" WHERE ("T"."OBJECT_TYPE"='TABLE') AND 
  ("T"."OWNER"='SCOTT')) innerQuery


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.03       2.21        239        269          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.03       2.21        239        269          0           0

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 111     (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  RESULT CACHE  69r7shhu4tvbdg27g20jb08sap (cr=0 pr=0 pw=0 time=4 us)
         0          0          0   SORT AGGREGATE (cr=0 pr=0 pw=0 time=19 us)
         0          0          0    TABLE ACCESS SAMPLE T (cr=0 pr=0 pw=0 time=6 us cost=210 size=972 card=36)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file scattered read                         32        0.32          2.20
--------------------------------------------------------------------------------

select /*+ dynamic_sampling(11) */ count(*) ,min(object_id)
from t
where owner ='SCOTT'
and object_type ='TABLE'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.03       3.40       1514       1517          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.03       3.41       1514       1518          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 111  
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=1517 pr=1514 pw=0 time=3405598 us)
         4          4          4   TABLE ACCESS FULL T (cr=1517 pr=1514 pw=0 time=3405572 us cost=405 size=1400 card=70)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  direct path read                               49        0.45          3.37
  SQL*Net message from client                     2       69.11         69.11


The above trace shows that recursive sql (sql-id : 0na0vpd3q0u79 ) is executed before the user sql's. As you said i could see DS_SVC comment in it.

Question:

1) On the second execution of the sql ( with sql-id = 0na0vpd3q0u79) - if it get benefited from result cache then consistent get should be zero, but why it went to 269? please clarify.
Connor McDonald
April 22, 2016 - 12:44 pm UTC

Are you sure the row source stats are correct?

The plan will show the result_cache step even if it had to fetch the data from the table.

Dynamic statistics level = 11

Rajeshwaran Jeyabal, April 25, 2016 - 8:18 am UTC

Are you sure the row source stats are correct?

Yes they are correct.

The plan will show the result_cache step even if it had to fetch the data from the table. 


1) I can't understand this. Are you saying the result cache step will even fetch data from Table (disk) even if the result cache is not-stale?

2) The SQL ID: 0na0vpd3q0u79 made the first execution and populated the result cache by doing 41 logical IO, on the subsequent execution, why does the same sql-id : 0na0vpd3q0u79 (with no changes to predicates/binds/session parameters) produced logical IO 269 rather than reusing the results in result cache (incase reusing the result cache logical IO should be zero) ? please make use of the script in case need to reproduce.

drop table t purge;
create table t as select * from all_objects;
column column_name format a12
select column_name,num_distinct,num_nulls,density,histogram
from user_tab_col_statistics
where table_name ='T'
and column_name in ('OWNER','OBJECT_TYPE');

exec dbms_monitor.session_trace_enable(binds=>true,waits=>true);

set serveroutput off

select /*+ dynamic_sampling(11) */ count(*) ,sum(object_id)
from t 
where owner ='SCOTT'
and object_type ='TABLE';

select /*+ dynamic_sampling(11) */ count(*) ,min(object_id)
from t 
where owner ='SCOTT'
and object_type ='TABLE';
exit;

Chris Saxon
April 29, 2016 - 5:16 pm UTC

1. The first time you execute a query using the result cache, the plan will still show "RESULT CACHE ...."

But clearly the first execution must access the actual rows, not the cache, because it's not set yet!

2. Your first execution also shows this:

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  RESULT CACHE  69r7shhu4tvbdg27g20jb08sap (cr=0 pr=0 pw=0 time=1 us)
         0          0          0   SORT AGGREGATE (cr=0 pr=0 pw=0 time=9 us)
         0          0          0    TABLE ACCESS SAMPLE T (cr=0 pr=0 pw=0 time=4 us cost=210 size=972 card=36)


To return no rows can't be right! Oracle has to populate the cache. Hence me checking that these are correct.

For reference, when I run it, both executions are reported together with rowsource like:

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  RESULT CACHE  fqvtdnmaudm3u6m38rg8t86unt (cr=784 pr=376 pw=0 time=36230 us)
         1          0          1   SORT AGGREGATE (cr=784 pr=376 pw=0 time=36200 us)
         4          2          4    TABLE ACCESS SAMPLE T (cr=784 pr=376 pw=0 time=36188 us cost=436 size=1183 card=13)


So I don't know why it's showing values for you in the disk column.

Dynamic statistics level = 11

Rajeshwaran Jeyabal, April 30, 2016 - 12:17 pm UTC

I think, I am close to it now.

demo@ORA12C> create table t as select * from all_objects;

Table created.

demo@ORA12C> column column_name format a12
demo@ORA12C> select column_name,num_distinct,num_nulls,density,histogram
  2  from user_tab_col_statistics
  3  where table_name ='T'
  4  and column_name in ('OWNER','OBJECT_TYPE');

COLUMN_NAME  NUM_DISTINCT  NUM_NULLS    DENSITY HISTOGRAM
------------ ------------ ---------- ---------- ---------------
OWNER                  33          0  .03030303 NONE
OBJECT_TYPE            40          0       .025 NONE

2 rows selected.

demo@ORA12C> @tkfilename.sql
D:\APP\VNAMEIT\diag\rdbms\ora12c\ora12c\trace\ora12c_ora_11192.trc


PL/SQL procedure successfully completed.

demo@ORA12C> exec dbms_monitor.session_trace_enable(binds=>true,waits=>true);

PL/SQL procedure successfully completed.

demo@ORA12C> set serveroutput off
demo@ORA12C> select /*+ dynamic_sampling(11) */ count(*) ,sum(object_id)
  2  from t
  3  where owner ='SCOTT'
  4  and object_type ='TABLE';

  COUNT(*) SUM(OBJECT_ID)
---------- --------------
         4         373227

1 row selected.

demo@ORA12C>
demo@ORA12C> select /*+ dynamic_sampling(11) */ count(*) ,min(object_id)
  2  from t
  3  where owner ='SCOTT'
  4  and object_type ='TABLE';

  COUNT(*) MIN(OBJECT_ID)
---------- --------------
         4          93304

1 row selected.

demo@ORA12C> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

C:\Users\179818>tkprof D:\APP\VNAMEIT\diag\rdbms\ora12c\ora12c\trace\ora12c_ora_11192.trc d:\tk.txt sys=no

TKPROF: Release 12.1.0.2.0 - Development on Sat Apr 30 17:40:57 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.



C:\Users\179818>tkprof D:\APP\VNAMEIT\diag\rdbms\ora12c\ora12c\trace\ora12c_ora_11192.trc d:\tk.txt sys=no aggregate=no

TKPROF: Release 12.1.0.2.0 - Development on Sat Apr 30 17:41:43 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.



Tkprof shows this. (for the first time, populate the result cache with 780 logical IO, on subsequent execution no more logical IO instead benefited from Result Cache)

********************************************************************************

SQL ID: fmrt75k5432c2 Plan Hash: 1807638002

SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring 
  optimizer_features_enable(default) no_parallel result_cache(snapshot=3600) 
  */ SUM(C1) 
FROM
 (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "T")  */ 1 AS C1 FROM "T" 
  SAMPLE BLOCK(51.713, 8) SEED(1)  "T" WHERE ("T"."OBJECT_TYPE"='TABLE') AND 
  ("T"."OWNER"='SCOTT')) innerQuery


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.12        802        778          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.12        802        780          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 125     (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  RESULT CACHE  ah73ur32a062ka8r5mgpv1szay (cr=778 pr=802 pw=0 time=124539 us)
         1          1          1   SORT AGGREGATE (cr=778 pr=802 pw=0 time=116814 us)
         4          4          4    TABLE ACCESS SAMPLE T (cr=778 pr=802 pw=0 time=116794 us cost=257 size=945 card=35)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file scattered read                        103        0.01          0.10
  db file sequential read                         1        0.00          0.00
--------------------------------------------------------------------------------

select /*+ dynamic_sampling(11) */ count(*) ,sum(object_id)
from t
where owner ='SCOTT'
and object_type ='TABLE'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01       0.19       1513       1516          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.20       1513       1517          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 125  
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=1516 pr=1513 pw=0 time=197757 us)
         4          4          4   TABLE ACCESS FULL T (cr=1516 pr=1513 pw=0 time=197743 us cost=497 size=160 card=8)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  reliable message                                1        0.00          0.00
  enq: KO - fast object checkpoint                1        0.00          0.00
  direct path read                               51        0.10          0.18
  SQL*Net message from client                     2        0.82          0.82
********************************************************************************

SQL ID: fmrt75k5432c2 Plan Hash: 1807638002

SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring 
  optimizer_features_enable(default) no_parallel result_cache(snapshot=3600) 
  */ SUM(C1) 
FROM
 (SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "T")  */ 1 AS C1 FROM "T" 
  SAMPLE BLOCK(51.713, 8) SEED(1)  "T" WHERE ("T"."OBJECT_TYPE"='TABLE') AND 
  ("T"."OWNER"='SCOTT')) innerQuery


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          0          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          0          0           1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 125     (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  RESULT CACHE  ah73ur32a062ka8r5mgpv1szay (cr=0 pr=0 pw=0 time=11 us)
         0          0          0   SORT AGGREGATE (cr=0 pr=0 pw=0 time=0 us)
         0          0          0    TABLE ACCESS SAMPLE T (cr=0 pr=0 pw=0 time=0 us cost=257 size=945 card=35)

--------------------------------------------------------------------------------

select /*+ dynamic_sampling(11) */ count(*) ,min(object_id)
from t
where owner ='SCOTT'
and object_type ='TABLE'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.53       1513       1516          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.53       1513       1517          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 125  
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=1516 pr=1513 pw=0 time=531416 us)
         4          4          4   TABLE ACCESS FULL T (cr=1516 pr=1513 pw=0 time=531403 us cost=497 size=160 card=8)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  direct path read                               53        0.04          0.52
  SQL*Net message from client                     2        2.97          2.97



********************************************************************************

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.