APACOUC successful applications on Autonomous

Well done! You've come up with the killer idea for 2021. You've got the best UI design anyone has ever seen! Your modern application ticks all the boxes - serverless, functional, Kubernetes, microservices, API-based, the list goes on. It runs on every OS and every type of device. But unfortunately, all of this counts for absolutely NOTHING if your data access is slow or buggy. But an Autonomous database will fix all that right? Only if you understand the fundamentals of how SQL is processed by the database. For novice developers, SQL can be hard to understand and sometimes totally hidden from view under an ORM. Let's peel back the covers to show how SQL is processed, how to avoid getting hacked, and how to get data back to your application in a snappy fashion

Transcript

1. 1 Connor McDonald
2. 2 Me youtube.com/c/ConnorMcDonaldOracle connor-mcdonald.com @connor_mc_d
3. https://asktom.oracle.com
4. https://asktom.oracle.com/officehours
5. Successful Application Development Connor McDonald Database Advocate Copyright © 2019 Oracle and/or its affiliates.
6. 6 before we begin
8. 8 communities need .... a community! stay safe ! stay healthy !
9. 9 some controversy to start
10. 10 its all about fundamentals
11. 11 why ?
12. 12 12
13. 13 SQL performance core to everything
14. 14 "but Autonomous fixes that right?"
15. 15 here's the problem...
16. 16 ... it's us
17. 17 a small digression
18. 18 a little bit of history on servers ...
19. 19 (not too) long ago
21. 21 FSB = "front side bus"
22. 22 any access to ... anything
23. 23 CPU capped
25. 25 hypertransport
27. memory access direct from CPU
29. ~2007
30. 30 "why do I care?"
31. 31 we can't blame the server anymore 
32. 32 if we can't get that performance ...
33. 33 ... it's on us pkdemo
37. 37 all have their place...
38. 38 "The sooner your company admits that you are not Google, the sooner you can get down to some real work" - Ted Dziuba
39. 39 if we're not getting 500,000 queries per second...
40. 40 ... what's holding us back ?
41. 42 It all comes down to...
42. 43 too much work or ...
43. 44 ... not being able to do work
44. 45 SQL is like any language
45. 46 compile it
46. 47 compile it on the fly
47. 48 run it
48. 49 get | store results
49. 50 for successful applications...
50. 51 compile it ... fast
51. 52 run it ... fast
52. 53 get | store results ... fast
53. 54 that's it!
54. 55 what everyone talks about
55. 56 compile execute store/fetch results
56. 57 this session
57. 58 compile execute store/fetch results
58. PART #1 COMPILING SQL
59. 60 terminology
60. 61 cursors declare cursor C(p number) is select * from DEPT where DEPTNO = p; begin for rec in C loop … end loop; end; select * from EMPLOYEE where EMPNO > 1234; delete from MY_TABLE; drop table MY_TABLE; begin MY_PROCEDURE(1,2,3); end;
61. 62 all of them!
62. 63 3 phases
63. 64 open process close give me some memory do some stuff using that memory, maybe access other memory here's your memory back
64. 65 memory access = controlled access
65. 66 a quick primer on (database) memory
67. 68 metaphor
68. 69 69
70. 73 limited resource lots of people want it concurrent access causes problems it's a complex system
71. 74 the solution ?
73. 76 same with memory
74. 77 SGA
75. 78 SGA protected by
76. 79 SGA protected by 1) get latch
77. 80 SGA protected by 2) access memory
78. 81 SGA protected by 3) release latch
79. 82 latch contention
80. 83 someone must wait ... SGA protected by
81. 84 active wait
82. 85 spinning can I have the latch ? can I have the latch ? can I have the latch ? can I have the latch ? can I have the latch ? can I have the latch ? can I have the latch ? can I have the latch ? can I have the latch ? can I have the latch ? can I have the latch ? can I have the latch ? can I have the latch ? can I have the latch ? can I have the latch ? can I have the latch ? can I have the latch ? can I have the latch ? can I have the latch ? can I have the latch ? can I have the latch ? can I have the latch ? can I have the latch ? can I have the latch ? can I have the latch ? can I have the latch ? can I have the latch ? can I have the latch ?
85. 88 latch contention....
86. 89 hurts CPU...
87. 90 hurts concurrency
88. YOU
89. GET
90. NOTHING
91. DONE
92. 100 "Errrr.... weren't we talking SQL?"
93. 101 to run a SQL statement
94. 102 Syntax Semantic Optimization Rowsourcing Execution (Fetch) SQL> select * 2 frmo emp; frmo emp * ERROR at line 2: ORA-00923: FROM keyword not found where expected
95. 103 Syntax Semantic Optimization Rowsourcing Execution (Fetch) SQL> select empnoo 2 from emp; select empnoo * ERROR at line 1: ORA-00904: invalid column name
96. 104 PLAN ------------------------------------- SELECT STATEMENT TABLE ACCESS BY INDEX ROWID EMP INDEX RANGE SCAN EMP_PK Syntax Semantic Optimization Rowsourcing Execution (Fetch)
97. 105 EMP_PK EMP Syntax Semantic Optimization Rowsourcing Execution (Fetch)
98. 106 Syntax Semantic Optimization Rowsourcing Execution (Fetch)
99. 107 Syntax Semantic Optimization Rowsourcing Execution (Fetch)
100. 108 lots of preliminaries
101. 109 parsing
102. 110 lots of memory access
103. 111 lots of latching !
104. 112 impossible to avoid ?
105. 113 two mechanisms
106. 114 1) library cache
107. 115 previously executed statements
108. 116 parse statement already in library cache ? reuse optimizer info reuse row source info select * from emp where empno = 123 syntax, semantics OK
109. select surname, firstname from emp where empno = 123 select * from dept where deptno = 4567 select * from customer where locality = 17 select prno from property where reference = 'X123G' select * from dept where deptno = 23 select surname, firstname from emp where empno = 123 select * from customer where locality = 256 select * from organisation where officer = 'OFF876' select surname, firstname from emp where empno = 7632 select * from dept where deptno = 4567 select offender from crimes where crno = 247462 Two full parses avoided library cache
110. 118 select surname, firstname from emp where empno = 123 select * from dept where dname = 'SALES' probability of reuse low ?
111. 119 many queries are “nearly the same” select surname, firstname from emp where empno = 123 select surname, firstname from emp where empno = 456
112. 120 2) binding
113. 121 binding parse this... now run it with ? = 123 now run it with ? = 456 select surname, firstname from emp where empno = ?
114. select surname, firstname from emp where empno = ? select * from dept where deptno = ? select * from customer where locality = ? select prno from property where reference = ? select * from dept where deptno = ? select surname, firstname from emp where empno = ? select * from customer where locality = ? select * from organisation where officer = ? select surname, firstname from emp where empno = ? select * from dept where deptno = ? select offender from crimes where crno = ? Five full parses avoided library cache
115. 123 demo ParseDemo ParseDemoBind
116. 124 "performance still looks ok"
117. 125 let's make it real ParseDemo2 nn ParseDemo2Bind nn
118. 126 much more serious
120. 128 building SQL by concatenation
121. 129 you'll get hacked
122. 130 select ename from emp where empno = 6543 select ename from emp where empno = 6543 and 1=0 union all select table_name from all_tables where table_name like '%SECURITY%' select ename from emp where empno = 6543 and 1=0 union all select username from app_security where ...
124. um ... er... no ☺
125. you'll get hacked ... ... really easily!
126. 134 #1 hacking app .... Google
128. 136 "Hi! We're hackable"
129. 137 <special edits>
130. 138 <special edits>
131. 139 https://mysite.com/...=6543%20and%201%3D0%20union%20all%20select%20username%7C%7Cemail%7C%7Cpassword%20from%20app_security%20where&oq=6543%20and%201%3D0%20union%20all%20select%20username%7C%7C https://mysite.com?empno=6543%20and%201%3D0%20union%20all%20select%20usern... select ename from emp where empno = 6543 and 1=0 union all select username from app_security where ...
137. 145 https://www.troyhunt.com/hacking-is-childs-play-sql-injection/
138. 146 it takes 5 minutes to hack you
140. 148 Jul 27, 2020
141. for fast, secure SQL ...
142. ... always bind user input
143. while we are talking binding
144. parse statement already in library cache ? reuse optimizer info reuse row source info select * from emp where empno = :bind syntax, semantics OK Yes! Yes! ??? ???
145. still some parsing to do
146. Syntax Validity Optimization Rowsourcing Execution (Fetch) Hard Parse Soft Parse found not found in lib cache
147. scott@mydb> select * 2 from EMP 3 where EMPNO = :b1 mike@mydb> select * 2 from EMP 3 where EMPNO = :b1
148. scott@mydb> select * 2 from EMP 3 where EMPNO = :b1 mike@mydb> select * 2 from EMP 3 where EMPNO = :b1 SQL> desc mike.emp Name Null? Type ----------------- -------- ------------ EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9)
149. (even soft) parsing = memory access = latching
150. can we do better ?
151. recall
152. open process close I need some memory do some stuff using that memory here's your memory back
153. your SQL is always shareable library cache select * from emp
154. your cursor points to it cursor library cache select * from emp
155. close cursor = lose pointer library cache select * from emp
156. next time we parse soft parse
157. impossible to avoid ?
158. what if we don't close the cursor
159. library cache select * from emp select * from dept select * from emp where ... cursor 1 cursor 2 cursor 3
160. library cache select * from emp select * from dept select * from emp where ... pstmt_all_emp = con.prepareStatement("select * from emp"); cursor 1 cursor 2 cursor 3
161. emplist(String[] args) { if "first time I'm using this" { else "reset the pointer, re-execute" } library cache select * from emp cursor 1 select * from dept cursor 2 select * from emp where ... cursor 3 pstmt_all_emp = con.prepareStatement("select * from emp");
162. don’t close the cursor
163. hang on to the memory
164. remember what cursors we’ve used
165. reuse whenever possible
166. don’t exhaust memory
167. don’t allow invalid cursor reuse
168. sounds complicated ...
169. a free solution exists
170. plsql
171. create procedure MY_PROC is begin select empno, ename, ... into … from emp where empno = my_plsql_variable ... end;
172. static SQL in stored PLSQL
173. static SQL in stored PLSQL automatically uses bind variables
174. static SQL in stored PLSQL automatically uses bind variables hold’s cursors open (even if you close)
175. procedure MY_PROC is cursor c_emp is select * from emp; begin open c_emp; fetch c_emp into …; close c_emp; end; select * from emp ...
176. PART #2 get | store results
178. 186 start with reading data
179. 187 querying data
180. 188 data is stored in blocks | pages
181. 189 to read data, you read blocks...
182. SQL> select * 2 from EMP 3 where ... /u01/my_data1.dat memory
183. 191 but (database) memory access is complex
184. SQL> select * 2 from EMP 3 where … SQL> update EMP 2 set … 3 where … SQL> select * 2 from EMP 3 where … SQL> insert into 2 EMP 3 values (…) SQL> select * 2 from CUSTOMER 3 / SQL> select max(EMP) 2 from DEPT SQL> delete 2 from DEPT 3 /
185. 193 we have to latch !
186. • Get latch • Search along list of blocks in memory • Read block • Extract row of interest • Release latch • Give row back to client "fetch a row"
187. 195 lots of rows
188. 196 lots of latching
189. 197 typical program
190. rs = stmt.executeQuery("..."); while(rs.next()) { v1 = rs.getInt(1); v2 = rs.getString(2); ... }
191. rs = stmt.executeQuery("..."); while(rs.next()) { v1 = rs.getInt(1); v2 = rs.getString(2); ... } • Get latch • Walk along list • Get block • Extract single row • Release latch • Give row back to client • Get latch (again) • Walk along list (again) • Get block (again) • Extract single row • Release latch (again) • Give row back to client (again) Row #1 Row #2 • Get latch (again) • Walk along list (again) • Get block (again) • Extract single row • Release latch (again) • Give row back to client (again) Row #3
192. a better strategy.... “pinning” (start the demo)
193. "fetch a row" • Get latch • Walk along list • Get block • Pin the block • Release latch • Give row 1 back to client • Give row 2 back to client • Give row 3 back to client … • Give row n to client • Get latch • Walk along list • Remove my pin on the block • Release latch “and ... I will need several rows from this block”
194. 202 how do you say “I may want many rows” ?
195. 203 responsibility of the client
200. FetchDemo nn
201. 209 focus on blocks
202. 210 focus on roundtrips
204. 212 all that data goes somewhere
205. 213 SQL> declare . . . 9 begin 10 open c; 11 loop 12 fetch c 13 into r; 14 exit when c%notfound; . . . 19 / Elapsed: 00:01:11.72
206. 214 SQL> declare . . . 9 begin 10 open c; 11 12 fetch c 13 bulk collect 14 into r; 15 . . . 19 / Elapsed: 00:00:08.93
207. 215 SQL> select * from v$mystats 2 where name like '%pga%' 3 / NAME VALUE ------------------------------ ---------- session pga memory 501534672 session pga memory max 501534672
208. 216 law of diminishing returns
209. 217 pin rows on a block (8k)
210. 218 SQL> declare . . . 9 begin 10 open c; 11 loop 12 fetch c 13 bulk collect 14 into r limit 100; 15 exit when c%notfound; . . . 19 / Elapsed: 00:00:08.17
211. 219 SQL> declare . . . 9 begin 10 open c; 11 loop 12 fetch c 13 bulk collect 14 into r limit 100; 15 exit when c%notfound; . . . 19 / Elapsed: 00:00:08.17 NAME VALUE ------------------------------ ---------- session pga memory 625912 session pga memory max 625912
212. 220 SQL> declare . . . 9 begin 10 for i in c loop 12 ... 16 end loop; 17 end; 18 / Elapsed: 00:00:08.21
213. PART #2(a) storing data
214. 222 the same rules apply
215. 223 think blocks not rows when appropriate
216. 225 demo InsertDemo3 InsertDemo4 nn
217. summary
218. 227 for slow and insecure applications...
219. 228 no binding, no SQL reuse, no pinning
220. 229 for fast, secure SQL ...
221. 230 ... hence fast, secure applications
222. 231 ... always bind user input
223. 232 ... use pinning/array processing
224. 233 We're done! youtube.com/c/ConnorMcDonaldOracle connor-mcdonald.com @connor_mc_d