New Features of Oracle12.1.0.2 ApproximateCountDistinct

[Oracle @ PD009 ~] $ Grep-iapprox_countoracle.strsettingsforapprox_count_distinctoptimizationsqkaGBPushdown: estimatedmemorywithoutGPDgroupSize (%. 2f) * aclsum (% u) %. 2f; estimatedmemorywithGPDoptHllEntry (% u) * # ofappro

[Oracle @ PD009 ~] $ Grep-I approx_count oracle. strsettings for approx_count_distinct optimizationsqkaGBPushdown: estimated memory without GPD = groupSize (%. 2f) * aclsum (% u) = %. 2f; estimated memory with GPD = optHllEntry (% u) * # of appro

[oracle@PD009 ~]$ grep -i approx_count oracle.strsettings for approx_count_distinct optimizationsqkaGBPushdown:  estimated memory without GPD = groupSize (%.2f) * aclsum (%u) = %.2f; estimated memory with GPD = optHllEntry (%u) * # of approx_count_distinct (%d) * parallelDegree (%.0f) = %.2fAPPROX_COUNT_DISTINCTAPPROX_COUNT_DISTINCTAPPROX_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: 

Related posts:

1. Extract SQL Plan from AWR
2. Introduction to using partial row Indexes
3. Script: Diagnostic parsing waiting and High version count
4. [11g new features] spm SQL PLAN MANAGEMENT execution PLAN MANAGEMENT Flowchart
5. Number of distinct values (Newcastle) & synopsis & Histogram gather speed
6. SQL optimization: the CBO Cardinality calculation error is caused by predicates with functions.
7. [11g new features] Cardinality Feedback base Feedback
8. [Oracle CBO optimizer] View Merging technology _ complex_view_merging & _ simple_view_merging
9. Massive Data insertion performance test
10. Slow query v $ lock and direct path write temp wait

Original article address: Approximate Count Distinct, new feature of Oracle 12.1.0.2. Thank you for sharing it with me.