databse & technology 2 | connor mcdonald | managing optimiser statistics - a better way.pdf
DESCRIPTION
TRANSCRIPT
![Page 1: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/1.jpg)
NOTE
itty bitty fonts in this
presentation
SQL> exec sample_font
Can you read this ?
1
![Page 2: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/2.jpg)
Connor McDonald
OracleDBA
co
.uk
2
![Page 3: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/3.jpg)
3
![Page 4: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/4.jpg)
bio slide
4
![Page 5: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/5.jpg)
Connor McDonald
![Page 6: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/6.jpg)
a funny story
6
![Page 7: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/7.jpg)
7
![Page 8: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/8.jpg)
8
![Page 9: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/9.jpg)
9
![Page 10: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/10.jpg)
why ?
10
![Page 11: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/11.jpg)
![Page 12: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/12.jpg)
12
![Page 13: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/13.jpg)
![Page 14: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/14.jpg)
life was simple
14
![Page 15: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/15.jpg)
15
![Page 16: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/16.jpg)
(good) cost optimizer
16
![Page 17: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/17.jpg)
17
![Page 18: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/18.jpg)
version 7.0
18
![Page 19: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/19.jpg)
19
optimizer_mode = CHOOSE
![Page 20: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/20.jpg)
"we choose RULE"
20
![Page 21: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/21.jpg)
21
SQL> analyze table SALES estimate statistics;
![Page 22: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/22.jpg)
22
![Page 23: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/23.jpg)
eventually....
23
![Page 24: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/24.jpg)
it got better
24
![Page 25: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/25.jpg)
added more functionality
25
![Page 26: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/26.jpg)
26
SQL> analyze table SALES estimate statistics
2 for table
3 for columns size 10
4 for ....;
![Page 27: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/27.jpg)
SQL syntax engine
27
![Page 28: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/28.jpg)
oracle 8i
28
![Page 29: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/29.jpg)
DBMS_STATS
29
![Page 30: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/30.jpg)
problem....
30
![Page 31: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/31.jpg)
31
analyze table SALES estimate statistics;SQL>
41 characters
![Page 32: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/32.jpg)
32
SQL>
2
3
4
5
6
7
8
128 characters
begin
dbms_stats.gather_table_stats(
ownname=>'HR',
tabname=>'SALES',
cascade=>true,
estimate_percent=>20
);
end;
![Page 33: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/33.jpg)
don't get chaining
33
![Page 34: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/34.jpg)
s l o w e r
34
![Page 35: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/35.jpg)
"I don't think so....."
35
![Page 36: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/36.jpg)
times have changed ...
36
![Page 37: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/37.jpg)
37
SQL> desc DBMS_STATS
PROCEDURE ALTER_DATABASE_TAB_MONITORING
PROCEDURE ALTER_SCHEMA_TAB_MONITORING
PROCEDURE ALTER_STATS_HISTORY_RETENTION
PROCEDURE CLEANUP_STATS_JOB_PROC
PROCEDURE CONVERT_RAW_VALUE
PROCEDURE CONVERT_RAW_VALUE
PROCEDURE CONVERT_RAW_VALUE
PROCEDURE CONVERT_RAW_VALUE
PROCEDURE CONVERT_RAW_VALUE
PROCEDURE CONVERT_RAW_VALUE_NVARCHAR
PROCEDURE CONVERT_RAW_VALUE_ROWID
PROCEDURE COPY_TABLE_STATS
FUNCTION CREATE_EXTENDED_STATS RETURNS VARCHAR2
PROCEDURE CREATE_STAT_TABLE
PROCEDURE DELETE_COLUMN_STATS
PROCEDURE DELETE_DATABASE_PREFS
![Page 38: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/38.jpg)
126 routines !
38
![Page 39: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/39.jpg)
39
![Page 40: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/40.jpg)
features
40
profiles
baselines
tuning sets
adaptive cursor sharing
bind peeking
![Page 41: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/41.jpg)
better stats needed ...
41
![Page 42: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/42.jpg)
... for the optimizer
42
![Page 43: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/43.jpg)
hard to convince
43
![Page 44: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/44.jpg)
44
![Page 45: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/45.jpg)
stop using analyze
45
![Page 46: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/46.jpg)
ego
46
![Page 47: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/47.jpg)
"good ol' days"
47
![Page 48: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/48.jpg)
48
![Page 49: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/49.jpg)
49
![Page 50: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/50.jpg)
today....
50
![Page 51: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/51.jpg)
optimizer is probably...
51
![Page 52: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/52.jpg)
... smarter than you
52
![Page 53: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/53.jpg)
53
SQL> select count(e.hiredate)
2 from DEPT d, EMP e
3 where e.deptno = d.deptno(+)
4 and e.sal > 10;
nested loop outer
sort merge outer
hash hash anti
nested loop anti
![Page 54: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/54.jpg)
54
-------------------------------------------
| Id | Operation | Name | Rows |
-------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | TABLE ACCESS FULL| EMP | 14 |
-------------------------------------------
no DEPT ?
![Page 55: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/55.jpg)
55
SQL> select count(e.hiredate)
2 from DEPT d, EMP e
3 where e.deptno = d.deptno(+)
4 and e.sal > 10;
not null
foreign key
key preserved
![Page 56: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/56.jpg)
Oracle's solution....
56
![Page 57: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/57.jpg)
statistics by stealth...
57
![Page 58: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/58.jpg)
10g
58
![Page 59: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/59.jpg)
59
SQL> select owner, job_name, enabled
2 from dba_scheduler_jobs
3 where owner = 'SYS';
OWNER JOB_NAME ENABLED
--------------- ------------------------------ -------
SYS PURGE_LOG TRUE
SYS FGR$AUTOPURGE_JOB TRUE
SYS GATHER_STATS_JOB TRUE
SYS AUTO_SPACE_ADVISOR_JOB TRUE
FALSE
![Page 60: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/60.jpg)
11g
60
![Page 61: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/61.jpg)
61
SQL> select owner, job_name, enabled
2 from dba_scheduler_jobs
3 where owner = 'SYS';
OWNER JOB_NAME ENABLED
--------------- ------------------------------ -------
SYS AUTO_SPACE_ADVISOR_JOB FALSE
SYS BSLN_MAINTAIN_STATS_JOB TRUE
SYS DRA_REEVALUATE_OPEN_FAILURES TRUE
SYS FGR$AUTOPURGE_JOB FALSE
SYS GATHER_STATS_JOB FALSE
SYS HM_CREATE_OFFLINE_DICTIONARY FALSE
SYS ORA$AUTOTASK_CLEAN TRUE
SYS PURGE_LOG TRUE
SYS XMLDB_NFS_CLEANUP_JOB FALSE
![Page 62: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/62.jpg)
stats STILL being collected
62
![Page 63: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/63.jpg)
automatic "tasks"
63
"super stealth mode"
![Page 64: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/64.jpg)
64
SQL> select client_name, status
2 from DBA_AUTOTASK_CLIENT;
CLIENT_NAME STATUS
------------------------------------ --------
auto optimizer stats collection ENABLED
auto space advisor ENABLED
sql tuning advisor ENABLED
![Page 65: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/65.jpg)
most sites
65
stats every night
default options
![Page 66: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/66.jpg)
in this session...
66
![Page 67: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/67.jpg)
default behaviour
67
![Page 68: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/68.jpg)
BAD
IDEA68
![Page 69: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/69.jpg)
collecting statistics
69
![Page 70: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/70.jpg)
BAD
IDEA70
![Page 71: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/71.jpg)
but....
71
![Page 72: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/72.jpg)
some default behaviour
72
![Page 73: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/73.jpg)
GOOD
IDEA73
![Page 74: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/74.jpg)
collecting some statistics
74
![Page 75: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/75.jpg)
GOOD
IDEA75
![Page 76: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/76.jpg)
76
![Page 77: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/77.jpg)
77
Inflammatory
statements
which will
alienate the
audience
Presentation Duration
1
![Page 78: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/78.jpg)
we're all hypocrites
78
![Page 79: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/79.jpg)
statistical hypocrisy
79
![Page 80: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/80.jpg)
80
"I need to change somereference data in my system"
![Page 81: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/81.jpg)
wrong case
![Page 82: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/82.jpg)
82
"nope...."
![Page 83: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/83.jpg)
83
ITIL
![Page 84: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/84.jpg)
84
Information
Technology
Infrastructure
Library
![Page 85: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/85.jpg)
servicecall
helpdesk
problemrecordDONE !
![Page 86: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/86.jpg)
86
phew....
![Page 87: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/87.jpg)
corrected
![Page 88: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/88.jpg)
88
same site....
![Page 89: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/89.jpg)
89
"Every night, I would like to potentially change the
performance characteristics of every single SQL statement in
the database"
![Page 90: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/90.jpg)
90
"no problem...."
![Page 91: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/91.jpg)
91
collecting stats = risk
![Page 92: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/92.jpg)
92
Ensor's paradox
![Page 93: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/93.jpg)
93
1987
performance group
bstat/estat
tkprof
BMC patrol
afiedt.buf
![Page 94: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/94.jpg)
94
"It is only safe to gather statistics ..."
"...when to do so will make no difference"
![Page 95: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/95.jpg)
95
recommendation #1
![Page 96: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/96.jpg)
96
![Page 97: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/97.jpg)
97
unless things are bad...
do not change statistics
![Page 98: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/98.jpg)
98
"surely it can't hurt?"
![Page 99: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/99.jpg)
99
10g
![Page 100: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/100.jpg)
100
options=>'GATHER STALE'
![Page 101: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/101.jpg)
101
options=>'GATHER EMPTY'
![Page 102: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/102.jpg)
102
options=>'GATHER AUTO'
![Page 103: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/103.jpg)
103
"no plans changed"
![Page 104: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/104.jpg)
104
"no queries ran slower"
![Page 105: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/105.jpg)
still problems
105
![Page 106: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/106.jpg)
problem # 1
106
![Page 107: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/107.jpg)
107
dbms_stats
![Page 108: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/108.jpg)
108
the goal of statistics
![Page 109: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/109.jpg)
minimise expensive SQL....
109
![Page 110: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/110.jpg)
added more expensive SQL !
110
![Page 111: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/111.jpg)
111
SQL> alter session set sql_true = true;
Session altered.
SQL> begin
2 dbms_stats.gather_table_stats(
3 'DEMO',
4 'PEOPLE);
5 end;
6 /
![Page 112: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/112.jpg)
112
SQL> select
2 count(distinct GENDER),
3 min(GENDER),
4 max(GENDER),
5 count(distinct NAME),
6 min(NAME),
7 max(NAME)
...
...
21 from PEOPLE;
![Page 113: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/113.jpg)
hard !
113
![Page 114: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/114.jpg)
![Page 115: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/115.jpg)
problem #2
115
![Page 116: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/116.jpg)
116
lingering pain
![Page 117: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/117.jpg)
invalidation
117
![Page 118: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/118.jpg)
![Page 119: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/119.jpg)
library cache
119
![Page 120: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/120.jpg)
120
select *
from PEOPLE
insert into PEOPLE
select * from ...select ...
from PEOPLE,
DEPT
where ...
delete from T
where X in
( select PID
from PEOPLE )
declare
v people.name%type;
begin
...
SQL> begin
2 dbms_stats.gather_table_stats(
3 'DEMO',
4 'PEOPLE);
5 end;
6 /
![Page 121: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/121.jpg)
121
![Page 122: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/122.jpg)
recap
122
![Page 123: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/123.jpg)
gathering statistics
123
really hard core SQL
belted CPU with hard parsing
everything ran the same
![Page 124: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/124.jpg)
oracle 9
124
![Page 125: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/125.jpg)
125
SQL> desc DBMS_STATS
PROCEDURE GATHER_TABLE_STATS
Argument Name Type In/Out Default?
----------------------- -------------- ------ --------
OWNNAME VARCHAR2 IN
TABNAME VARCHAR2 IN
PARTNAME VARCHAR2 IN DEFAULT
ESTIMATE_PERCENT NUMBER IN DEFAULT
BLOCK_SAMPLE BOOLEAN IN DEFAULT
METHOD_OPT VARCHAR2 IN DEFAULT
DEGREE NUMBER IN DEFAULT
GRANULARITY VARCHAR2 IN DEFAULT
CASCADE BOOLEAN IN DEFAULT
STATTAB VARCHAR2 IN DEFAULT
STATID VARCHAR2 IN DEFAULT
STATOWN VARCHAR2 IN DEFAULT
NO_INVALIDATE BOOLEAN IN DEFAULT
STATTYPE VARCHAR2 IN DEFAULT
FORCE BOOLEAN IN DEFAULT
NO_INVALIDATE BOOLEAN IN DEFAULT
![Page 126: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/126.jpg)
126
no_invalidate => false | true
?
?
![Page 127: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/127.jpg)
127
oracle 10
![Page 128: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/128.jpg)
128
no_invalidate => auto
![Page 129: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/129.jpg)
129
SQL> select
2 x.ksppinm name,
3 y.kspftctxvl value
4 from
5 sys.x$ksppi x,
6 sys.x$ksppcv2 y
7 where
8 x.indx+1 = y.kspftctxpn and
9 x.ksppinm = '_optimizer_invalidation_period'
NAME VALUE
------------------------------ ------------
_optimizer_invalidation_period 18000
![Page 130: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/130.jpg)
130
SQL> exec dbms_stats.gather_table_stats(
user,'PEOPLE');
select *
from PEOPLEinsert into PEOPLE
select * from ...
select ...
from PEOPLE,
DEPT
where ...
delete from T
where X in
( select PID
from PEOPLE )
declare
v people.name%type;
begin
...
![Page 131: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/131.jpg)
131
better ?
![Page 132: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/132.jpg)
132
hard parse storm avoided
![Page 133: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/133.jpg)
133
5 hours before problems
![Page 134: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/134.jpg)
134
![Page 135: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/135.jpg)
135
unless things are bad...
DO NOT CHANGE STATISTICS
![Page 136: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/136.jpg)
136
Inflammatory
statements
which will
alienate the
audience
Presentation Duration
1
2
![Page 137: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/137.jpg)
137
we don't know if things are bad
![Page 138: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/138.jpg)
138
until its too late
![Page 139: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/139.jpg)
139
SQL's are slow
users fed up
stats out of date
![Page 140: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/140.jpg)
140
![Page 141: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/141.jpg)
141
![Page 142: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/142.jpg)
142
2 hours to gather stats
![Page 143: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/143.jpg)
143
![Page 144: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/144.jpg)
144
collect stats
![Page 145: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/145.jpg)
145
in readiness
![Page 146: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/146.jpg)
146
![Page 147: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/147.jpg)
147
pending statistics
![Page 148: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/148.jpg)
148
"pending" - private
"published" – optimizer uses
![Page 149: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/149.jpg)
149
SQL> begin
2 dbms_stats.set_schema_prefs(
3 ownname=>'DEMO',
4 pname=>'PUBLISH',
5 pvalue=>'FALSE');
6 end;
database,
table
![Page 150: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/150.jpg)
150
SQL> alter session set
2 optimizer_use_pending_statistics = true;
![Page 151: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/151.jpg)
151
SQL> begin
2 dbms_stats.publish_pending_stats(
3 ownname=>'DEMO',
4 tabname=>null);
5 end;
![Page 152: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/152.jpg)
152
recommendation #2
![Page 153: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/153.jpg)
153
when collecting statistics
![Page 154: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/154.jpg)
154
PUBLISH = false
always....
![Page 155: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/155.jpg)
155
even if you immediately publish
![Page 156: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/156.jpg)
156
atomic publication
![Page 157: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/157.jpg)
157
and don't wait 5 hours
![Page 158: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/158.jpg)
158
_optimizer_invalidation_period
![Page 159: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/159.jpg)
159
for most tables
![Page 160: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/160.jpg)
160
dbms_stats.lock_table_stats
![Page 161: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/161.jpg)
161
collecting system stats
![Page 162: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/162.jpg)
162
different story
![Page 163: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/163.jpg)
163
absolutely vital…
…to have them
![Page 164: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/164.jpg)
164
maximize I/O throughput
![Page 165: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/165.jpg)
165
SQL> set autotrace traceonly explain
SQL> select * from T;
---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 53459 | 4489K| 113 (1)|
| 1 | TABLE ACCESS FULL| T | 53459 | 4489K| 113 (1)|
---------------------------------------------------------------
SQL> select value
2 from v$spparameter
3 where name = 'db_file_multiblock_read_count';
VALUE
-----------
16
![Page 166: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/166.jpg)
166
SQL> alter session set events
2 = '10046 trace name context forever, level 8';
SQL> select * from T;
PARSING IN CURSOR #22 len=15 dep=0 uid=124 oct=3 lid=124 ...
select * from T
END OF STMT
PARSE #22:c=10000,e=108,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4
EXEC #22:c=0,e=65,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4
WAIT #22: nam='db file scattered read' ela= 3387
file#=5 block#=13122 blocks=16 obj#=199963
WAIT #22: nam='db file scattered read' ela= 3188
file#=5 block#=13234 blocks=16 obj#=199963
WAIT #22: nam='db file scattered read' ela= 3125
file#=5 block#=13250 blocks=16 obj#=199963
WAIT #22: nam='db file scattered read' ela= 3255
file#=5 block#=13266 blocks=16 obj#=199963
WAIT #22: nam='db file scattered read' ela= 3369
file#=5 block#=13282 blocks=16 obj#=199963
![Page 167: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/167.jpg)
167
SSTIOMAX
![Page 168: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/168.jpg)
168
1 MB
![Page 169: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/169.jpg)
169
db_block_size = 8192
![Page 170: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/170.jpg)
170
db_file_multiblock_read_count = 128
![Page 171: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/171.jpg)
171
?
![Page 172: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/172.jpg)
172
![Page 173: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/173.jpg)
173
the solution ?
![Page 174: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/174.jpg)
174
system stats
![Page 175: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/175.jpg)
175
SQL> select pname, pval1
2 from sys.aux_stats$
3 /
PNAME PVAL1
------------------------------ ----------
SREADTIM 4.065
MREADTIM 6.173
CPUSPEED 567
MBRC 12
MAXTHR 48203776
SLAVETHR -1
![Page 176: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/176.jpg)
176
use MBRC to cost
![Page 177: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/177.jpg)
177
use db_file_multiblock_read_count to read
![Page 178: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/178.jpg)
178
Inflammatory
statements
which will
alienate the
audience
Presentation Duration
1
2
3
![Page 179: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/179.jpg)
179
forget about system statistics
![Page 180: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/180.jpg)
180
do not use them
![Page 181: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/181.jpg)
181
![Page 182: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/182.jpg)
182
![Page 183: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/183.jpg)
183
absolutely vital…
…to have them
![Page 184: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/184.jpg)
184
do not “use”* them
* = collect, change, set
![Page 185: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/185.jpg)
185
problem #1
![Page 186: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/186.jpg)
186
![Page 187: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/187.jpg)
187
problem #2
![Page 188: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/188.jpg)
188
monitoring for bad....
![Page 189: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/189.jpg)
189
optimizing for bad
![Page 190: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/190.jpg)
190
recommendation #3
![Page 191: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/191.jpg)
191
gather
into STAT table
![Page 192: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/192.jpg)
192
monitor
![Page 193: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/193.jpg)
193
set once
![Page 194: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/194.jpg)
194
the defaults are probably fine
![Page 195: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/195.jpg)
195
SQL> select pname, pval1
2 from sys.aux_stats$
3 /
PNAME PVAL1
---------------------- ----------
IOSEEKTIM 10 (ms)
IOTFRSPEED 4096 (bytes/ms)
SREADTIM = IOSEEKTIM + db_block_size / IOTFRSPEED = 10
MREADTIM = IOSEEKTIM + mbrc * db_block_size / IOTFRSPEED = 26
![Page 196: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/196.jpg)
196
10.2 and above
![Page 197: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/197.jpg)
197
db_file_multiblock_read_count
_db_file_exec_read_count
_db_file_optimizer_read_count
![Page 198: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/198.jpg)
198
dbms_resource_manager
to calibrate
![Page 199: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/199.jpg)
199
so far...
![Page 200: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/200.jpg)
200
not changing statistics
![Page 201: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/201.jpg)
201
eventually....
![Page 202: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/202.jpg)
202
the time will come...
![Page 203: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/203.jpg)
203
one possible reason
![Page 204: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/204.jpg)
204
even if stats unchanged …
![Page 205: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/205.jpg)
205
… your plans might !
![Page 206: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/206.jpg)
SQL> create table T as
2 select to_date('01-AUG-2011')+
3 trunc(dbms_random.value(0,7)) dte,
4 rpad('padding',20) padding
5 from dual
6 connect by level <= 100000;
Table created.
![Page 207: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/207.jpg)
SQL> select dte, count(*)
2 from t
3 group by dte
4 order by 1;
DTE COUNT(*)
--------- ----------
01-AUG-11 14334
02-AUG-11 14222
03-AUG-11 14167
04-AUG-11 14510
05-AUG-11 14346
06-AUG-11 14349
07-AUG-11 14072
![Page 208: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/208.jpg)
208
15,000 rows per day always
![Page 209: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/209.jpg)
SQL> exec dbms_stats.gather_table_stats(user,'T')
PL/SQL procedure successfully completed.
SQL> create index IX on T ( dte ) ;
Index created.
![Page 210: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/210.jpg)
SQL> select * from t where dte = '03-AUG-2011';
--------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
--------------------------------------------------
| 0 | SELECT STATEMENT | | 14286 | 404K|
|* 1 | TABLE ACCESS FULL| T | 14286 | 404K|
--------------------------------------------------
![Page 211: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/211.jpg)
211
a week later…
![Page 212: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/212.jpg)
SQL> insert into T
2 select to_date('08-AUG-2011')+
3 trunc(dbms_random.value(0,7)) dte,
4 rpad('padding',20) padding
5 from dual
6 connect by level <= 100000;
100000 rows created.
![Page 213: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/213.jpg)
SQL> select dte, count(*)
2 from t
3 group by dte
4 order by 1;
DTE COUNT(*)
--------- ----------
01-AUG-11 14334
...
07-AUG-11 14072
08-AUG-11 14261
09-AUG-11 14106
10-AUG-11 14410
11-AUG-11 14289
12-AUG-11 14358
13-AUG-11 14252
14-AUG-11 14324
![Page 214: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/214.jpg)
214
statistics unchanged
![Page 215: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/215.jpg)
SQL> select * from t where dte = '12-AUG-2011';
----------------------------------------------------
| Id | Operation | Name | Rows |
----------------------------------------------------
| 0 | SELECT STATEMENT | | 2381 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 2381 |
|* 2 | INDEX RANGE SCAN | IX | 2381 |
----------------------------------------------------
![Page 216: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/216.jpg)
SQL> select * from t where dte = '14-AUG-2011';
----------------------------------------------------
| Id | Operation | Name | Rows |
----------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 |
|* 2 | INDEX RANGE SCAN | IX | 1 |
----------------------------------------------------
![Page 217: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/217.jpg)
217
why ?
![Page 218: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/218.jpg)
SQL> select low_value, high_value
2 from user_tab_columns
3 where table_name = 'T'
4 and column_name = 'DTE';
LOW_VALUE HIGH_VALUE
-------------------- -----------------
786F0801010101 786F0807010101
![Page 219: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/219.jpg)
SQL> set serverout on
SQL> declare
2 res date;
3 begin
4 dbms_stats.convert_raw_value(
5 '786F0801010101',res);
6 dbms_output.put_line(result);
7 dbms_stats.convert_raw_value(
8 '786F0806010101',res);
9 dbms_output.put_line(result);
10 end;
11 /
01-AUG-11
06-AUG-11
![Page 220: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/220.jpg)
01-AUG-11 06-AUG-11
![Page 221: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/221.jpg)
221
when the time comes…
![Page 222: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/222.jpg)
222
as accurate as possible
![Page 223: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/223.jpg)
223
as cheaply as possible
![Page 224: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/224.jpg)
224
statistics accurately
![Page 225: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/225.jpg)
225
extended statistics
![Page 226: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/226.jpg)
226
![Page 227: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/227.jpg)
227
![Page 228: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/228.jpg)
228
cardinality is everything
228
![Page 229: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/229.jpg)
229
same with Oracle
229
![Page 230: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/230.jpg)
230
some real(ish) data
230
![Page 231: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/231.jpg)
231
SQL> desc VEHICLE
Name Null? Type
-------------------------- -------- -------------
ID NUMBER
MAKE VARCHAR2(6)
MODEL VARCHAR2(6)
SQL> select count(*)
2 from VEHICLE;
COUNT(*)
------------
2,157,079
231
![Page 232: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/232.jpg)
232
default stats not enough
232
![Page 233: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/233.jpg)
233
SQL> select count(*)
2 from VEHICLE
3 where MAKE = 'HOLDEN';
COUNT(*)
----------
415387
------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 138|
| 1 | SORT AGGREGATE | | 1 | 7 | |
|* 2 | INDEX RANGE SCAN| MAKE_IX | 55310 | 378K| 138|
------------------------------------------------------------
233
![Page 234: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/234.jpg)
234
histogram
234
![Page 235: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/235.jpg)
235
SQL> begin
2 dbms_stats.gather_table_stats(user,'VEHICLE',
3 method_opt=>'for all columns size 1,'||
4 'for columns MAKE size 254,'||
5 'for columns MODEL size 254');
6 end;
7 /
PL/SQL procedure successfully completed.
235
![Page 236: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/236.jpg)
236
SQL> select count(*)
2 from VEHICLE
3 where MAKE = 'HOLDEN';
COUNT(*)
----------
415387
-----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 1024|
| 1 | SORT AGGREGATE | | 1 | 7 | |
|* 2 | INDEX RANGE SCAN| MAKE_IX | 418K| 2859K| 1024|
-----------------------------------------------------------
236
![Page 237: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/237.jpg)
237
make AND model
237
![Page 238: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/238.jpg)
238
SQL> select count(*)
2 from VEHICLE
3 where MAKE = 'HOLDEN'
4 and MODEL = 'COMMODORE';
COUNT(*)
----------
214468
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 |
| 1 | SORT AGGREGATE | | 1 | 14 |
| 2 | BITMAP CONVERSION COUNT | | 39527 | 540K|
| 3 | BITMAP AND | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS| | | |
|* 5 | INDEX RANGE SCAN | MODEL_IX | | |
| 6 | BITMAP CONVERSION FROM ROWIDS| | | |
|* 7 | INDEX RANGE SCAN | MAKE_IX | | |
---------------------------------------------------------------------
238
50% holdens are commodores
![Page 239: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/239.jpg)
239
two things
239
![Page 240: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/240.jpg)
240
![Page 241: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/241.jpg)
241241
![Page 242: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/242.jpg)
242
no correlation
10g and before
242
![Page 243: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/243.jpg)
243
11g
243
![Page 244: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/244.jpg)
244
SQL> select
2 DBMS_STATS.CREATE_EXTENDED_STATS(
3 user, 'VEHICLE','(MAKE,MODEL)') tag
4 from dual;
TAG
----------------------------------
SYS_STU8QPK2S$PEWHARK2CP3#1F#G
244
![Page 245: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/245.jpg)
245
SQL> begin
2 dbms_stats.gather_table_stats(user,'VEHICLE',
3 method_opt=>
4 'for columns (make,model) size 254');
5 end;
6 /
PL/SQL procedure successfully completed.
245
![Page 246: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/246.jpg)
246
SQL> select count(*)
2 from VEHICLE
3 where MAKE = 'HOLDEN'
4 and MODEL = 'COMMODORE';
COUNT(*)
----------
214468
-------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 1956|
| 1 | SORT AGGREGATE | | 1 | 14 | |
|* 2 | TABLE ACCESS FULL| VEHICLE | 220K| 3018K| 1956|
-------------------------------------------------------------
246
![Page 247: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/247.jpg)
247
composite indexes
247
![Page 248: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/248.jpg)
248
11.2 column groups
248
![Page 249: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/249.jpg)
249
SQL> exec DBMS_STATS.SEED_COL_USAGE(NULL,NULL,60);
249
![Page 250: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/250.jpg)
250
implemented via virtual columns
250
![Page 251: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/251.jpg)
251
various implications
251
![Page 252: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/252.jpg)
252
accurate stats not always possible...
![Page 253: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/253.jpg)
253
you can't have stats
![Page 254: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/254.jpg)
254
on everything !
![Page 255: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/255.jpg)
255
when stats wont do...
![Page 256: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/256.jpg)
256
... use the real data
![Page 257: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/257.jpg)
257
dynamic sampling
![Page 258: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/258.jpg)
258
SQL> drop table TOUGH_DATA purge;
Table dropped.
SQL> create table TOUGH_DATA nologging as
2 select
3 rownum pk,
4 dbms_random.string('U',10) str
5 from dual
6 connect by level < 1000000
7 /
Table created.
SQL> exec dbms_stats.gather_table_stats(
user,'TOUGH_DATA')
![Page 259: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/259.jpg)
259
SQL> select count(*)
2 from TOUGH_DATA
3 where str like '%XX'
4 /
COUNT(*)
----------
1452
hard
![Page 260: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/260.jpg)
260
SQL> select count(*)
2 from TOUGH_DATA
3 where str like '%XX'
4 /
-------------------------------------------------
| Id | Operation | Name | Rows |
-------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | TABLE ACCESS FULL| TOUGH_DATA | 50000 |
-------------------------------------------------
5% assumption
![Page 261: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/261.jpg)
261
SQL> select /*+ dynamic_sampling(t 2) */ count(*)
2 from TOUGH_DATA t
3 where str like '%XX'
4 /
-------------------------------------------------
| Id | Operation | Name | Rows |
-------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | TABLE ACCESS FULL| TOUGH_DATA | 1252 |
-------------------------------------------------
![Page 262: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/262.jpg)
262
simple queries as well
![Page 263: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/263.jpg)
263
SQL> create table EASY_DATA nologging as
2 select
3 rownum pk,
4 chr(65+trunc(rownum/40000)) str
5 from dual
6 connect by level < 1000000
7 /
Table created.
SQL> exec dbms_stats.gather_table_stats(user,'EASY_DATA')
PL/SQL procedure successfully completed.
A,A,A,A,A,B,B,B,B,.....1,2,3,4,5,6......
![Page 264: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/264.jpg)
264
SQL> select count(*)
2 from EASY_DATA
3 where str = 'F'
4 and pk > 900000;
COUNT(*)
----------
0
![Page 265: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/265.jpg)
265
SQL> set autotrace traceonly explain
SQL> select count(*)
2 from EASY_DATA
3 where str = 'F'
4 and pk > 900000;
------------------------------------------------
| Id | Operation | Name | Rows |
------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | TABLE ACCESS FULL| EASY_DATA | 4000 |
------------------------------------------------
![Page 266: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/266.jpg)
266
SQL> set autotrace traceonly explain
SQL> select /*+ dynamic_sampling(t 2) */ count(*)
2 from EASY_DATA t
3 where str = 'F'
4 and pk > 900000;
------------------------------------------------
| Id | Operation | Name | Rows |
------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | TABLE ACCESS FULL| EASY_DATA | 23 |
------------------------------------------------
![Page 267: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/267.jpg)
267
11.2
![Page 268: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/268.jpg)
268
parallel queries (maybe) sampled
![Page 269: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/269.jpg)
269
SQL> select count(*)
2 from EASY_DATA t
3 where str = 'F'
4 and pk > 900000;
----------------------------------------------------
| Id | Operation | Name | Rows |
----------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 | SORT AGGREGATE | | 1 |
| 2 | PX COORDINATOR | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 |
| 4 | SORT AGGREGATE | | 1 |
| 5 | PX BLOCK ITERATOR | | 4 |
|* 6 | TABLE ACCESS FULL| EASY_DATA | 4 |
----------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=4)
![Page 270: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/270.jpg)
270
low frequency
![Page 271: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/271.jpg)
271
high cost
![Page 272: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/272.jpg)
272
statistics cheaply
![Page 273: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/273.jpg)
273
SQL> desc DBA_TABLES
Name Null? Type
----------------------------- -------- -------------
OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
COLUMN_NAME NOT NULL VARCHAR2(30)
...
NUM_ROWS NUMBER
![Page 274: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/274.jpg)
274
SQL> desc DBA_TAB_COLS
Name Null? Type
----------------------------- -------- -------------
OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
COLUMN_NAME NOT NULL VARCHAR2(30)
...
NUM_DISTINCT NUMBER
LOW_VALUE RAW(32)
HIGH_VALUE RAW(32)
...
![Page 275: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/275.jpg)
275
SQL> desc PEOPLE
Name Null? Type
----------------------------- -------- -------------
PID NUMBER
GENDER CHAR(1)
NAME VARCHAR2(47)
AGE NUMBER
DEATH_RATE NUMBER
![Page 276: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/276.jpg)
276
SQL> alter session set sql_true = true;
Session altered.
SQL> begin
2 dbms_stats.gather_table_stats(
3 'DEMO',
4 'PEOPLE);
5 end;
6 /
![Page 277: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/277.jpg)
277
select
/*+ no_parallel(t)
no_parallel_index(t)
dbms_stats
cursor_sharing_exact
use_weak_name_resl
dynamic_sampling(0)
no_monitoring
no_substrb_pad */
count(*),
...
count("GENDER"),
count(distinct "GENDER"),
substrb(dump(min("GENDER"),16,0,32),1,120),
substrb(dump(max("GENDER"),16,0,32),1,120),
...
from "MCDONAC"."PEOPLE" t
count("GENDER")
count(distinct "GENDER")
min("GENDER")
max("GENDER")
![Page 278: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/278.jpg)
278
count("GENDER")
count(distinct "GENDER")
min("GENDER")
max("GENDER")
one pass
one pass
one pass
hard
![Page 279: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/279.jpg)
279
SQL> select count(*) from PEOPLE;
COUNT(*)
------------
500000000
Elapsed: 00:04:43.73
![Page 280: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/280.jpg)
280
SQL> begin
2 dbms_stats.gather_table_stats(
3 user,
4 'PEOPLE',
5 estimate_percent=>25);
6 end;
7 /
ERROR at line 1:
ORA-01652: unable to extend temp
segment by 128 in tablespace TEMP
Elapsed: 00:16:37.12
![Page 281: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/281.jpg)
281
11g
![Page 282: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/282.jpg)
282
one pass NDV
![Page 283: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/283.jpg)
283
HASH
12 6 3 12 3 7 11 12 33 6 11 12 6 45 15 7 15 17 45 6 17
12 6 3 7 11 33 45 15 17
![Page 284: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/284.jpg)
284
NDV = 9
12 6 3 7 11 33 45 15 17
![Page 285: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/285.jpg)
285
what about large NDV ?
12
6
3
7
11
33
45
15
17
21
92
71
34
56
615
2
41
64
91
73
![Page 286: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/286.jpg)
286
"Magic" HASH
![Page 287: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/287.jpg)
287
NDV =
remaining hashes x
2^number of splits
![Page 288: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/288.jpg)
288
demo
![Page 289: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/289.jpg)
289
SQL> create table ONE_PASS nologging
2 as select substr(text,1,1) single_char
3 from DBA_SOURCE;
Table created.
SQL> select count(distinct single_char) ndv
2 from one_pass;
NDV
----------
83
![Page 290: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/290.jpg)
290
0 127
16 buckets...
![Page 291: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/291.jpg)
291
64 127
![Page 292: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/292.jpg)
292
96 127
![Page 293: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/293.jpg)
293
SQL> set serverout on
SQL> declare
2 type t_bucket is table of varchar2(1);
3 l_synopsis t_bucket;
4 l_splits number := 0;
5 l_hash int;
6 l_min_val int := 0;
7 l_synopsis_size int := 16;
8 begin
9 for i in ( select single_char from one_pass ) loop
10 l_hash := ascii(i.single_char);
11
12 if l_synopsis.count = l_synopsis_size then
13 l_min_val :=
14 case
15 when l_min_val = 0 then 64
16 when l_min_val = 64 then 96
17 when l_min_val = 96 then 112
18 when l_min_val = 112 then 120
19 end;
20 l_splits := l_splits + 1;
![Page 294: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/294.jpg)
294
22
23 for j in 1 .. l_min_val loop
24 if l_synopsis.exists(j) then
25 l_synopsis.delete(j);
26 end if;
27 end loop;
28 end if;
29
30 if l_hash > l_min_val then
31 l_synopsis(l_hash) := 'Y';
32 end if;
33 end loop;
34 dbms_output.put_line(l_synopsis.count *
35 power(2,l_splits));
36 end;
37 /
Splitting, keeping entries above 64
Splitting, keeping entries above 96
Splitting, keeping entries above 112
88
![Page 295: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/295.jpg)
295
the reality
16384 bucket limit
18,446,744,073,709,551,616 hash range
![Page 296: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/296.jpg)
296
SQL> begin
2 dbms_stats.gather_table_stats(
3 user,
4 'PEOPLE',
5 estimate_percent=>25);
6 end;
7 /
PL/SQL procedure successfully completed.
Elapsed: 00:05:39.82
![Page 297: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/297.jpg)
297
recommendation #4
![Page 298: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/298.jpg)
298
estimate_percent
use DEFAULT size (AUTO)
![Page 299: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/299.jpg)
299
only for columns
without histograms
![Page 300: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/300.jpg)
300
Inflammatory
statements
which will
alienate the
audience
Presentation Duration
1
2
3
4
![Page 301: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/301.jpg)
301
histograms...
![Page 302: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/302.jpg)
302
...SUCK
![Page 303: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/303.jpg)
303
not really but...
![Page 304: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/304.jpg)
304
9i
![Page 305: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/305.jpg)
![Page 306: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/306.jpg)
306
SQL> set autotrace traceonly explain
SQL> select * from MY_TABLE_100K_ROWS
2 where r = :b1
3 /
------------------------------------------
| Id | Operation | Name | Rows |
------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 | INDEX UNIQUE SCAN| PK | 1 |
------------------------------------------
~ num rows / num distinct
![Page 307: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/307.jpg)
307
SQL> set autotrace traceonly explain
SQL> select * from MY_TABLE_100K_ROWS
2 where r < :b1
3 /
-----------------------------------------
| Id | Operation | Name | Rows |
-----------------------------------------
| 0 | SELECT STATEMENT | | 5000 |
| 1 | INDEX RANGE SCAN| PK | 5000 |
-----------------------------------------
5%
![Page 308: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/308.jpg)
308
![Page 309: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/309.jpg)
309
cool ?
![Page 310: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/310.jpg)
310
10g
![Page 311: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/311.jpg)
311
method_opt
for all columns size auto
![Page 312: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/312.jpg)
312
sys.col_usage$
![Page 313: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/313.jpg)
313
SQL> desc SYS.COL_USAGE$
Name Null? Type
----------------------- -------- ---------
OBJ# NUMBER
INTCOL# NUMBER
EQUALITY_PREDS NUMBER
EQUIJOIN_PREDS NUMBER
NONEQUIJOIN_PREDS NUMBER
RANGE_PREDS NUMBER
LIKE_PREDS NUMBER
NULL_PREDS NUMBER
TIMESTAMP DATE
![Page 314: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/314.jpg)
314
SQL> create table T (
2 skew varchar2(10),
3 even number);
Table created.
SQL> insert into T
2 select
3 case
4 when rownum > 99995 then 'SPECIAL'
5 else dbms_random.string('U',8)
6 end,
7 mod(rownum,200)
8 from dual
9 connect by level <= 100000
10 /
100000 rows created.
5 special
values
even
distribution
![Page 315: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/315.jpg)
315
SQL> exec dbms_stats.gather_table_stats(
user,'T', estimate_percent=>null);
PL/SQL procedure successfully completed.
SQL> select COLUMN_NAME,NUM_DISTINCT,DENSITY,
2 ( select count(*)
3 from user_tab_histograms
4 where table_name = 'T'
5 and column_name = c.column_name ) hist_cnt
6 from user_tab_cols c
7 where table_name = 'T'
8 order by table_name,COLUMN_ID
9 /
COLUMN_NAME NUM_DISTINCT DENSITY HIST_CNT
------------------- ------------ ---------- ----------
SKEW 99996 .00001 2
EVEN 200 .005 2
![Page 316: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/316.jpg)
316
SQL> select * from T where skew = 'SPECIAL';
SKEW VAL
---------- ----------
SPECIAL 196
SPECIAL 197
SPECIAL 198
SPECIAL 199
SPECIAL 0
5 rows selected.
SQL> select * from T where even = 5;
TAG VAL
---------- ----------
IBRXGVIE 5
[snip]
500 rows selected.
![Page 317: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/317.jpg)
317
SQL> exec dbms_stats.gather_table_stats(
user,'T', estimate_percent=>null);
PL/SQL procedure successfully completed.
SQL> select COLUMN_NAME,NUM_DISTINCT,DENSITY,
2 ( select count(*)
3 from user_tab_histograms
4 where table_name = 'T'
5 and column_name = c.column_name ) hist_cnt
6 from user_tab_cols c
7 where table_name = 'T'
8 order by table_name,COLUMN_ID
9 /
COLUMN_NAME NUM_DISTINCT DENSITY HIST_CNT
------------------- ------------ ---------- ----------
SKEW 99996 .00001 2
EVEN 200 .000005 200
![Page 318: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/318.jpg)
318
recommendation #5
![Page 319: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/319.jpg)
319
set_schema_prefs
for all columns size 1
![Page 320: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/320.jpg)
320
explicit control for each table
![Page 321: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/321.jpg)
321
set_table_prefs
for column CCC size nnn
![Page 322: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/322.jpg)
322
one more useful tool
![Page 323: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/323.jpg)
323
![Page 324: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/324.jpg)
324
SQL> desc CONFERENCE_ATTENDEES
Name Null? Type
----------------------------- -------- --------------
PID NUMBER
GENDER CHAR(1)
NAME VARCHAR2(40)
AGE NUMBER
DEATH_RATE NUMBER
![Page 325: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/325.jpg)
325
![Page 326: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/326.jpg)
326
SQL> select
2 count(distinct GENDER) NDV
3 from
4 CONFERENCE_ATTENDEES
NDV
----------------------
???
![Page 327: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/327.jpg)
327
SQL> select
2 ntile(GENDER) over ( ... )
3 from
4 CONFERENCE_ATTENDEES
![Page 328: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/328.jpg)
328
set_table_stats
num_rows
avg_row_len
![Page 329: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/329.jpg)
329
set_column_stats
high_value
num_distinct
![Page 330: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/330.jpg)
330
when all else fails ...
![Page 331: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/331.jpg)
331
... lie and cheat
![Page 332: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/332.jpg)
332
![Page 333: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/333.jpg)
333
no
![Page 334: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/334.jpg)
334
analyze ... validate structure
![Page 335: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/335.jpg)
335
SQL> desc INDEX_STATS
Name Null? Type
----------------------------- -------- --------------
HEIGHT NUMBER
BLOCKS NUMBER
NAME VARCHAR2(30)
PARTITION_NAME VARCHAR2(30)
...
OPT_CMPR_COUNT NUMBER
OPT_CMPR_PCTSAVE NUMBER
![Page 336: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/336.jpg)
336
wrap up
![Page 337: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/337.jpg)
337
don't collect stats .... unless
don't collect system stats ... unless
don't collect histograms ... unless
default estimate size (NDV)
lie and cheat
![Page 338: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/338.jpg)
Connor McDonald
OracleDBA
co
.uk
338
![Page 339: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/339.jpg)
339
ORA-00041
www.oracledba.co.uk
“active time limit exceeded - session terminated”
![Page 340: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/340.jpg)
340340
11.2
![Page 341: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/341.jpg)
341
actual versus estimate
341
![Page 342: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/342.jpg)
342
SQL> select /*+ GATHER_PLAN_STATISTICS */ count(*)
2 from VEHICLE
3 where MAKE = 'HOLDEN'
4 and MODEL = 'COMMODORE';
COUNT(*)
----------
214468
SQL> SELECT *
2 FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
3 NULL, NULL, 'ALLSTATS LAST'));
----------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
----------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |
|* 2 | TABLE ACCESS FULL| VEHICLE | 1 | 220K| 214K|
-----------------------------------------------------------------
342
![Page 343: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/343.jpg)
343343
employ someone....
![Page 344: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/344.jpg)
344344
check every query...
![Page 345: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/345.jpg)
345
SQL> select /*+ GATHER_PLAN_STATISTICS */ count(*)
2 from VEHICLE
3 where MAKE = 'HOLDEN'
4 and MODEL = 'COMMODORE';
COUNT(*)
----------
214468
SQL> SELECT *
2 FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
3 NULL, NULL, 'ALLSTATS LAST'));
-----------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
-----------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |
|* 2 | TABLE ACCESS FULL| VEHICLE | 1 | 220K| 214K|
-----------------------------------------------------------------
345
"ok"
![Page 346: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/346.jpg)
346346
![Page 347: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/347.jpg)
347347
but just maybe ....
![Page 348: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/348.jpg)
348348
... someone already is
![Page 349: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/349.jpg)
very
349
very
cool
![Page 350: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/350.jpg)
SQL> create table EMP as
2 select rownum empno,
3 mod(rownum,10) jobid,
4 mod(rownum,10)*1000 salary,
5 mod(rownum,50)+1 deptno
6 from dual
7 connect by rownum < 100000;
SQL> create table DEPT as
2 select rownum deptno,
3 'dept'||rownum dname
4 from dual
5 connect by rownum <= 100;
350
100,000 rows
100 rows
![Page 351: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/351.jpg)
SQL> exec dbms_stats.gather_table_stats(user,'EMP');
SQL> exec dbms_stats.gather_table_stats(user,'DEPT');
SQL> create index EMP_IX on EMP ( deptno );
SQL> create index DEPT_IX on DEPT ( deptno );
351
![Page 352: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/352.jpg)
SQL> select e.empno, d.dname
2 from emp e, dept d
3 where d.deptno = e.deptno
4 and e.jobid = 1
5 and e.salary > 5000;
no rows selected
----------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
----------------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | MERGE JOIN | | 4444 | 104K |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 100 | 1000 |
| 3 | INDEX FULL SCAN | DEPT_IX | 100 | |
|* 4 | SORT JOIN | | 4444 | 62216 |
|* 5 | TABLE ACCESS FULL | EMP | 4444 | 62216 |
----------------------------------------------------------------
352
4 and e.jobid = 1
5 and e.salary > 5000;
hard to
optimize
![Page 353: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/353.jpg)
re-run the query
353
![Page 354: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/354.jpg)
354
no anythingchanges to
![Page 355: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/355.jpg)
SQL> select e.empno, d.dname
2 from emp e, dept d
3 where d.deptno = e.deptno
4 and e.jobid = 1
5 and e.salary > 5000;
no rows selected
---------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
---------------------------------------------------
| 0 | SELECT STATEMENT | | | |
|* 1 | HASH JOIN | | 89 | 2136 |
| 2 | TABLE ACCESS FULL| DEPT | 1 | 10 |
|* 3 | TABLE ACCESS FULL| EMP | 4444 | 62216 |
---------------------------------------------------
355
![Page 356: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/356.jpg)
11.2
356
![Page 357: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/357.jpg)
the optimizer knows what "hard" is
357
![Page 358: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/358.jpg)
cardinality feedback loop
358
![Page 359: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/359.jpg)
its not continuous
359
![Page 360: Databse & Technology 2 | Connor McDonald | Managing Optimiser Statistics - A better way.pdf](https://reader034.vdocuments.us/reader034/viewer/2022051208/546fa55daf7959ae0a8b4608/html5/thumbnails/360.jpg)
several restrictions
360
but still very cool