1 / 115

Optimizer Yin and Yang

Optimizer Yin and Yang. Thomas Kyte http://asktom.oracle.com/. Programming to fail…. Programming to fail…. Too smart for their own good Parse count Parse count (failures) Quick stories about parsing…. Programming to fail…. Begin execute immediate

mirra
Download Presentation

Optimizer Yin and Yang

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Optimizer Yin and Yang Thomas Kyte http://asktom.oracle.com/

  2. Programming to fail…

  3. Programming to fail… • Too smart for their own good • Parse count • Parse count (failures) • Quick stories about parsing…

  4. Programming to fail… Begin execute immediate ‘begin internal_pkg.some_code; end;’; Exception when others then null; End;

  5. Programming to fail… Sandeep - my math was wrong, I said 40% of your hard parses were failed parses.  In looking at the numbers again:Statistic                                     Total     per Second     per Trans-------------------------------- ------------------ -------------- -------------parse count (failures)                      389,176          109.0           3.0parse count (hard)                          607,096          170.1           4.7parse count (total)                       6,775,397        1,898.0          52.3It would be correct to say that 64% (yes, 64%!!!!!!!!!!!!!) of your parses are *FAILED* parsed.  The parse count hard included failed and successful parses - therefore, it is 389k/607k*100 to get the right percentage.2 out of 3 SQL statements FAIL PARSING.  That is sick

  6. Abusing Functions

  7. Function Abuse • Cardinality estimation issues • May reduce access paths • Can increase CPU needs (repeated function calls) • Could lead to partition elimination elimination

  8. Cardinality Estimation Issues ops$tkyte%ORA11GR2> create table t 2 as 3 select * 4 from all_objects 5 / Table created.

  9. Cardinality Estimation Issues ops$tkyte%ORA11GR2> select count(*) 2 from t 3 where created >= to_date( '5-sep-2010', 'dd-mon-yyyy' ) 4 and created < to_date( '6-sep-2010', 'dd-mon-yyyy' ) 5 / COUNT(*) ---------- 65925 ops$tkyte%ORA11GR2> select count(*), 0.01 * count(*), 0.01 * 0.01 * count(*) 2 from t 3 / COUNT(*) 0.01*COUNT(*) 0.01*0.01*COUNT(*) ---------- ------------- ------------------ 72926 729.26 7.2926

  10. Cardinality Estimation Issues ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats( user, 'T' ); PL/SQL procedure successfully completed. • Why did I wait till here to gather statistics?

  11. Cardinality Estimation Issues ops$tkyte%ORA11GR2> select count(*) 2 from t t2 3 where created >= to_date( '5-sep-2010', 'dd-mon-yyyy' ) 4 and created < to_date( '6-sep-2010', 'dd-mon-yyyy' ) 5 / COUNT(*) ---------- 65925 ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor); --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 291 (100)| | | 1 | SORT AGGREGATE | | 1 | 8 | | | |* 2 | TABLE ACCESS FULL| T | 65462 | 511K| 291 (1)| 00:00:04 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(("CREATED"<TO_DATE(' 2010-09-06 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "CREATED">=TO_DATE(' 2010-09-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

  12. Cardinality Estimation Issues ops$tkyte%ORA11GR2> select count(*) 2 from t t1 3 where trunc(created) = to_date( '5-sep-2010', 'dd-mon-yyyy' ) 4 / COUNT(*) ---------- 65925 ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor); --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 294 (100)| | | 1 | SORT AGGREGATE | | 1 | 8 | | | |* 2 | TABLE ACCESS FULL| T | 729 | 5832 | 294 (2)| 00:00:04 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(TRUNC(INTERNAL_FUNCTION("CREATED"))=TO_DATE(' 2010-09-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

  13. Cardinality Estimation Issues ops$tkyte%ORA11GR2> select count(*) 2 from t t1 3 where trunc(created) = to_date( '5-sep-2010', 'dd-mon-yyyy' ) 4 and substr( owner, 1, 3 ) = 'SYS' 5 / COUNT(*) ---------- 33535 ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor); --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 292 (100)| | | 1 | SORT AGGREGATE | | 1 | 14 | | | |* 2 | TABLE ACCESS FULL| T | 7 | 98 | 292 (1)| 00:00:04 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter((SUBSTR("OWNER",1,3)='SYS' AND TRUNC(INTERNAL_FUNCTION("CREATED"))=TO_DATE(' 2010-09-05 00:00:00' 'syyyy-mm-dd hh24:mi:ss')))

  14. Cardinality Estimation Issues ops$tkyte%ORA11GR2> select count(*) 2 from t t1 3 where trunc(created) = to_date( '5-sep-2010', 'dd-mon-yyyy' ) 4 and substr( owner, 1, 3 ) = 'SYS' 5 and mod(object_id,100000) > 1 6 / COUNT(*) ---------- 33535 ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor); --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 292 (100)| | | 1 | SORT AGGREGATE | | 1 | 19 | | | |* 2 | TABLE ACCESS FULL| T | 1 | 19 | 292 (1)| 00:00:04 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter((SUBSTR("OWNER",1,3)='SYS' AND MOD("OBJECT_ID",100000)>1 AND TRUNC(INTERNAL_FUNCTION("CREATED"))=TO_DATE(' 2010-09-05 00:00 'syyyy-mm-dd hh24:mi:ss'))) 23 rows selected.

  15. Compile with warnings… SQL> alter session set plsql_warnings='enable:all’; SQL> create or replace procedure p 2 as 3 begin 4 dbms_output.put_line( 'hello world' ); 5 exception 6 when others 7 then null; 8 end; 9 / Warning: Procedure created with compilation errors. c##tkyte%CDB1> show errors Errors for PROCEDURE P: LINE/COL ERROR ---- ----------------------------------------------------------------- 6/6 PLS-06009: procedure "P" OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR

  16. Increased CPU ops$tkyte%ORA11GR2> create or replace procedure p authid definer 2 as 3 l_date varchar2(30) := '01-jan-2011'; 4 l_start number := dbms_utility.get_cpu_time; 5 begin 6 for i in 1 .. 10 7 loop 8 for x in ( select owner, object_name 9 from big_table.big_table 10 where created = l_date ) 11 loop 12 null; 13 end loop; 14 end loop; 15 dbms_output.put_line( 'CPU: ' || 16 to_char( dbms_utility.get_cpu_time-l_start ) ); 17 end; 18 / SP2-0804: Procedure created with compilation warnings ops$tkyte%ORA11GR2> exec p CPU: 132

  17. Increased CPU … 7 loop 8 for x in ( select owner, object_name 9 from big_table.big_table 10 where created = l_date ) 11 loop 12 null; 13 end loop; … ops$tkyte%ORA11GR2> show errors procedure p Errors for PROCEDURE P: LINE/COL ERROR -------- ----------------------------------------------------------------- 10/36 PLW-07204: conversion away from column type may result in sub-optimal query plan

  18. Increased CPU ops$tkyte%ORA11GR2> create or replace procedure p authid definer 2 as 3 l_date date := to_date('01-jan-2011','dd-mon-yyyy'); 4 l_start number := dbms_utility.get_cpu_time; 5 begin 6 for i in 1 .. 10 7 loop 8 for x in ( select owner, object_name 9 from big_table.big_table 10 where created = l_date ) 11 loop 12 null; 13 end loop; 14 end loop; 15 dbms_output.put_line( 'CPU: ' || 16 to_char( dbms_utility.get_cpu_time-l_start ) ); 17 end; 18 / Procedure created. ops$tkyte%ORA11GR2> exec p CPU: 94 30% less CPU in this case

  19. Reduced Access Paths ops$tkyte%ORA11GR2> create table t 2 ( x varchar2(20) constraint t_pk primary key, 3 y varchar2(30) 4 ); Table created. ops$tkyte%ORA11GR2> insert into t 2 select user_id, username 3 from all_users; 47 rows created. ops$tkyte%ORA11GR2> commit; Commit complete.

  20. Reduced Access Paths ops$tkyte%ORA11GR2> create or replace procedure p authid definer 2 as 3 l_rect%rowtype; 4 l_key number := 5; 5 begin 6 select * into l_rec from t where x = l_key; 7 for x in (select plan_table_output 8 from TABLE( dbms_xplan.display_cursor())) 9 loop 10 dbms_output.put_line( x.plan_table_output ); 11 end loop; 12 end; 13 / SP2-0804: Procedure created with compilation warnings

  21. Reduced Access Paths … 5 begin 6 select * into l_rec from t where x = l_key; 7 for x in (select plan_table_output … ops$tkyte%ORA11GR2> show errors Errors for PROCEDURE P: LINE/COL ERROR -------- ----------------------------------------------------------- 6/42 PLW-07204: conversion away from column type may result in sub-optimal query plan

  22. Reduced Access Paths ops$tkyte%ORA11GR2> exec p SQL_ID 18796jgha0hwz, child number 0 ------------------------------------- SELECT * FROM T WHERE X = :B1 Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | |* 1 | TABLE ACCESS FULL| T | 1 | 29 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(TO_NUMBER("X")=:B1)

  23. Reduced Access Paths ops$tkyte%ORA11GR2> create or replace procedure p authid definer 2 as 3 l_rect%rowtype; 4 l_key varchar2(5) := '5'; 5 begin 6 select * into l_rec from t where x = l_key; 7 for x in (select plan_table_output 8 from TABLE( dbms_xplan.display_cursor())) 9 loop 10 dbms_output.put_line( x.plan_table_output ); 11 end loop; 12 end; 13 / Procedure created. ops$tkyte%ORA11GR2> show errors No errors.

  24. Reduced Access Paths ops$tkyte%ORA11GR2> exec p SQL_ID 18796jgha0hwz, child number 1 ------------------------------------- SELECT * FROM T WHERE X = :B1 Plan hash value: 1303508680 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | | | 1 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 29 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | T_PK | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("X"=:B1)

  25. Partition Elimination Eliminated ops$tkyte%ORA11GR2> CREATE TABLE t 2 ( 3 dtdate, 4 x int, 5 y varchar2(30) 6 ) 7 PARTITION BY RANGE (dt) 8 ( 9 PARTITION part1 VALUES LESS THAN(to_date('31-jan-2011', 'dd-mon-yyyy')), 10 PARTITION part2 VALUES LESS THAN(to_date('28-feb-2011', 'dd-mon-yyyy')) 11 ) 12 / Table created.

  26. Partition Elimination Eliminated ops$tkyte%ORA11GR2> create or replace procedure p authid definer 2 as 3 l_date timestamp := timestamp'2011-01-15 00:00:00.000'; 4 l_count number; 5 begin 6 select count(*) into l_count from t where dt = l_date; 7 8 for x in (select plan_table_output 9 from TABLE( dbms_xplan.display_cursor() ) ) 10 loop 11 dbms_output.put_line( '.'||x.plan_table_output ); 12 end loop; 13 end; 14 / SP2-0804: Procedure created with compilation warnings

  27. Partition Elimination Eliminated … 5 begin 6 select count(*) into l_count from t where dt = l_date; 7 … SP2-0804: Procedure created with compilation warnings ops$tkyte%ORA11GR2> show errors Errors for PROCEDURE P: LINE/COL ERROR -------- -------------------------------------------------------------- 6/47 PLW-07204: conversion away from column type may result in sub-optimal query plan

  28. Partition Elimination Eliminated SQL_ID 0t5m83d3m67q7, child number 0 ------------------------------------- SELECT COUNT(*) FROM T WHERE DT = :B1 Plan hash value: 3225603066 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | | | 1 | SORT AGGREGATE | | 1 | 9 | | | | | | 2 | PARTITION RANGE ALL| | 1 | 9 | 2 (0)| 00:00:01 | 1 | 2 | |* 3 | TABLE ACCESS FULL | T | 1 | 9 | 2 (0)| 00:00:01 | 1 | 2 | --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(INTERNAL_FUNCTION("DT")=:B1)

  29. Partition Elimination Eliminated ops$tkyte%ORA11GR2> create or replace procedure p authid definer 2 as 3 l_date date := to_date( '2011-01-15', 'yyyy-mm-dd' ); 4 l_count number; 5 begin 6 select count(*) into l_count from t where dt = l_date; 7 8 for x in (select plan_table_output 9 from TABLE( dbms_xplan.display_cursor() ) ) 10 loop 11 dbms_output.put_line( '.'||x.plan_table_output ); 12 end loop; 13 end; 14 / Procedure created. ops$tkyte%ORA11GR2> show errors No errors.

  30. Partition Elimination Eliminated .SQL_ID 0t5m83d3m67q7, child number 1 .------------------------------------- .SELECT COUNT(*) FROM T WHERE DT = :B1 . .Plan hash value: 3660200434 . .------------------------------------------------------------------------------------------------ .| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | .------------------------------------------------------------------------------------------------ .| 0 | SELECT STATEMENT | | | | 2 (100)| | | | .| 1 | SORT AGGREGATE | | 1 | 9 | | | | | .| 2 | PARTITION RANGE SINGLE| | 1 | 9 | 2 (0)| 00:00:01 | KEY | KEY | .|* 3 | TABLE ACCESS FULL | T | 1 | 9 | 2 (0)| 00:00:01 | KEY | KEY | .------------------------------------------------------------------------------------------------ . .Predicate Information (identified by operation id): .--------------------------------------------------- . . 3 - filter("DT"=:B1)

  31. Partition Elimination Eliminated ops$tkyte%ORA11GR2> alter session set Plsql_Warnings = 'error:all‘; ops$tkyte%ORA11GR2> create or replace procedure p authid definer 2 as 3 l_date timestamp := timestamp'2011-01-15 00:00:00.000'; 4 l_count number; 5 begin 6 select count(*) into l_count from t where dt = l_date; 7 8 for x in (select plan_table_output 9 from TABLE( dbms_xplan.display_cursor() ) ) 10 loop 11 dbms_output.put_line( '.'||x.plan_table_output ); 12 end loop; 13 end; 14 /

  32. Gathering stats when you shouldn’t

  33. Right command, wrong time • Temporary tables • Empty at 3am, full at 3pm • Queue Tables • Same as above • Partitioned Transactional Tables • Partition starts empty • Grows rapidly • Gather stats every hour? (no…)

  34. Right command, wrong time – Temporary tables • Temporary tables • Dynamic Sampling might be appropriate (might be shared over sessions) • Fill with representative data, Gather and Lock • Cardinality/Opt_Estimate Hints on a session by session basis • 12c Session Private Statistics • Hard parse…

  35. Right command, wrong time – Partitioned tables SALES_Q4_2003 Copies stats from source partition to destination partition Adjusts min & max values for partition column at both partition & global level Copies statistics of the dependent objects Columns, local indexes etc. Does not update global indexes Maybe use DBMS_STATS.COPY_TABLE_STATS(); : DBMS_STATS.COPY_TABLE_STATS (‘SH’, 'SALES’, 'SALES_Q4_2003’, 'SALES_Q1_2004’); Sales Table SALES_1995 SALES_Q1_2004

  36. Not Having Representative Stats When You Should

  37. Is 10% enough? • 1,000,000 rows • Grows at a rate of 10,000 rows per month • 10 months – no stats… • What might happen?

  38. Is 10% right? ops$tkyte%ORA11GR2> create table t 2 as 3 select * 4 from ( 5 select add_months(sysdate,-100) + mod( rownum, 3000 ) dt 6 from dual 7 connect by level <= 1000000 8 ) 9 where dt < trunc(sysdate,'y') 10 / Table created.

  39. Is 10% right? ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats( user, 'T' ); PL/SQL procedure successfully completed. ops$tkyte%ORA11GR2> select num_rows from user_tables where table_name = 'T'; NUM_ROWS ---------- 994672

  40. Is 10% right? ops$tkyte%ORA11GR2> insert into t 2 select trunc(sysdate,'y') + mod( rownum, 150 ) dt 3 from dual 4 connect by level <= 50000 5 / 50000 rows created. ops$tkyte%ORA11GR2> commit; Commit complete.

  41. Is 10% right? ops$tkyte%ORA11GR2> select trunc(dt,'mm'), count(*) 2 from t 3 where dt >= add_months( trunc(sysdate,'y'),-3) 4 group by trunc(dt,'mm') order by 1; TRUNC(DT, COUNT(*) --------- ---------- 01-OCT-13 10323 01-NOV-13 9990 01-DEC-13 10323 01-JAN-14 10353 01-FEB-14 9344 01-MAR-14 10323 01-APR-14 9990 01-MAY-14 9990 8 rows selected.

  42. Is 10% right? ops$tkyte%ORA11GR2> select count(*) 2 from t 3 where dt between to_date( '01-dec-2013' ) 4 and to_date( '31-dec-2013' ); COUNT(*) ---------- 9990 ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor); --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 506 (100)| | | 1 | SORT AGGREGATE | | 1 | 8 | | | |* 2 | TABLE ACCESS FULL| T | 10517 | 84136 | 506 (2)| 00:00:07 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(("DT">=TO_DATE(' 2013-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "DT"<=TO_DATE(' 2013-12-31 00:00:00', 'syyyy-mm-d hh24:mi:ss')))

  43. Is 10% right? ops$tkyte%ORA11GR2> select count(*) 2 from t 3 where dt between to_date( '01-jan-2014' ) 4 and to_date( '31-jan-2014' ); COUNT(*) ---------- 10353 ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor); --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 506 (100)| | | 1 | SORT AGGREGATE | | 1 | 8 | | | |* 2 | TABLE ACCESS FULL| T | 333 | 2664 | 506 (2)| 00:00:07 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(("DT">=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "DT"<=TO_DATE(' 2014-01-31 00:00:00', 'syyyy-mm-d hh24:mi:ss')))

  44. Is 10% right? ops$tkyte%ORA11GR2> select count(*) 2 from t 3 where dt between to_date( '01-sep-2014' ) 4 and to_date( '30-sep-2014' ); COUNT(*) ---------- 0 ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor); --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 506 (100)| | | 1 | SORT AGGREGATE | | 1 | 8 | | | |* 2 | TABLE ACCESS FULL| T | 333 | 2664 | 506 (2)| 00:00:07 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(("DT">=TO_DATE(' 2014-09-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "DT"<=TO_DATE(' 2014-09-30 00:00:00', 'syyyy-mm-d hh24:mi:ss')))

  45. Is 10% right? When might be the time to gather stats? At what %? “it depends”

  46. Is 10% right? ops$tkyte%ORA11GR2> create or replace function stats_to_date(p_raw raw) 2 return date 3 as 4 l_dt date; 5 begin 6 dbms_stats.convert_raw_value(p_raw,l_dt); 7 return l_dt; 8 end; 9 / Function created. ops$tkyte%ORA11GR2> select stats_to_date( low_value ), stats_to_date( high_value ) 2 from user_tab_columns 3 where table_name = 'T'; STATS_TO_ STATS_TO_ --------- --------- 31-OCT-05 31-DEC-13

  47. Is 10% right? ops$tkyte%ORA11GR2> declare 2 l_distcnt number; 3 l_density number; 4 l_nullcnt number; 5 rec_srecdbms_stats.statrec; 6 datevalsdbms_stats.datearray; 7 l_avgclen number; 8 l_low date; 9 l_high date; 10 BEGIN 11 dbms_stats.get_column_stats 12 (user, 'T', 'DT', 13 distcnt => l_distcnt, 14 density => l_density, 15 nullcnt => l_nullcnt, 16 srec => rec_srec, 17 avgclen => l_avgclen ); 18 19 select stats_to_date(low_value), stats_to_date(high_value) 20 into l_low, l_high 21 from user_tab_col_statistics 22 where table_name = 'T' 23 and column_name = 'DT'; 25 l_high := add_months(l_high, 5); 26 datevals := dbms_stats.datearray (l_low, l_high); 27 rec_srec.minval:=NULL; 28 rec_srec.maxval:=NULL; 29 rec_srec.bkvals:=NULL; 30 rec_srec.novals:=NULL; 31 32 dbms_stats.prepare_column_values (rec_srec, datevals); 33 34 dbms_stats.set_column_stats 35 (user, 'T', 'DT', 36 distcnt => l_distcnt, 37 density => l_density, 38 nullcnt => l_nullcnt, 39 srec => rec_srec, 40 avgclen => l_avgclen ); 41 END; 42 /

  48. Is 10% right? ops$tkyte%ORA11GR2> select stats_to_date( low_value ), stats_to_date( high_value ) 2 from user_tab_columns 3 where table_name = 'T'; STATS_TO_ STATS_TO_ --------- --------- 31-OCT-05 31-MAY-14

  49. Is 10% right? ops$tkyte%ORA11GR2> select count(*) 2 from t t2 3 where dt between to_date( '01-jan-2014' ) 4 and to_date( '31-jan-2014' ); COUNT(*) ---------- 10353 ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor); --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 506 (100)| | | 1 | SORT AGGREGATE | | 1 | 8 | | | |* 2 | TABLE ACCESS FULL| T | 10188 | 81504 | 506 (2)| 00:00:07 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(("DT">=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "DT"<=TO_DATE(' 2014-01-31 00:00:00', 'syyyy-mm-d hh24:mi:ss')))

  50. Is 10% right? Knowledge of your data is a mandatory prerequisite to developing a sensible statistics implementation

More Related