[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:
Oracle 12.1.0.2新特性 Approximate Count Distinct
2014/10/11 by Leave a Comment
Comment