[oracle@PD009 ~]$ grep -i approx_count oracle.str
settings for approx_count_distinct optimizations
qkaGBPushdown:  estimated memory without GPD = groupSize (%.2f) * aclsum (%u) = %.2f; estimated memory with GPD = optHllEntry (%u) * # of approx_count_distinct (%d) * parallelDegree (%.0f) = %.2f
APPROX_COUNT_DISTINCT
APPROX_COUNT_DISTINCT
APPROX_COUNT_DISTINCT
 
 
_approx_cnt_distinct_gby_pushdown = choose
_approx_cnt_distinct_optimization = 0




 alter system flush shared_pool;
  alter session set events '10053 trace name context forever ,level 1';
 select count( distinct prod_id) from sales_history where  amount_sold>1;
 

 select approx_count_distinct(prod_id) from sales_history where  amount_sold>1;

  oradebug setmypid;
  oradebug tracefile_name;
  /s01/diag/rdbms/pdprod/PDPROD/trace/PDPROD_ora_4086.trc
  
  
  
  sql= select count( distinct prod_id) from sales_history where  amount_sold>1
----- Explain Plan Dump -----
----- Plan Table -----

============
Plan Table
============
---------------------------------------------+-----------------------------------+
| Id  | Operation             | Name         | Rows  | Bytes | Cost  | Time      |
---------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT      |              |       |       |  4912 |           |
| 1   |  SORT AGGREGATE       |              |     1 |    13 |       |           |
| 2   |   VIEW                | VW_DAG_0     |    72 |   936 |  4912 |  00:00:59 |
| 3   |    HASH GROUP BY      |              |    72 |   648 |  4912 |  00:00:59 |
| 4   |     TABLE ACCESS FULL | SALES_HISTORY| 3589K |   32M |  4820 |  00:00:58 |
---------------------------------------------+-----------------------------------+













sql= select approx_count_distinct(prod_id) from sales_history where  amount_sold>1
----- Explain Plan Dump -----
----- Plan Table -----
 
============
Plan Table
============
----------------------------------------------+-----------------------------------+
| Id  | Operation              | Name         | Rows  | Bytes | Cost  | Time      |
----------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT       |              |       |       |  4820 |           |
| 1   |  SORT AGGREGATE APPROX |              |     1 |     9 |       |           |
| 2   |   TABLE ACCESS FULL    | SALES_HISTORY| 3589K |   32M |  4820 |  00:00:58 |
----------------------------------------------+-----------------------------------+
Predicate Information: