WEBVTT 1 00:00:00.040 --> 00:00:02.279 You know that feeling right, You're using an app, click save, 2 00:00:02.480 --> 00:00:04.759 maybe run a report and it just crawls. 3 00:00:04.879 --> 00:00:07.360 Yeah, or you get that email. The system slow. 4 00:00:07.240 --> 00:00:10.279 Exactly, and if there's an Oracle database behind it, figuring 5 00:00:10.320 --> 00:00:14.080 out why it's slow and crucially how to speed it up. Well, 6 00:00:14.119 --> 00:00:14.519 that's the. 7 00:00:14.439 --> 00:00:17.199 Big challenge, absolutely, and it's usually not just one thing, 8 00:00:17.359 --> 00:00:20.399 is it. It could be the code, the database structure itself, 9 00:00:20.480 --> 00:00:23.399 the servers, the network, even it gets complex. 10 00:00:23.719 --> 00:00:26.280 That's precisely what we're digging into today. We've got some 11 00:00:26.359 --> 00:00:31.079 material specific excerpts from the Oracle Database eleven GR two 12 00:00:31.160 --> 00:00:32.520 Performance Tuning Cookbook. 13 00:00:32.719 --> 00:00:37.159 Ah, the cookbook I like that suggests practical recipes here exactly. 14 00:00:36.920 --> 00:00:39.759 Like solutions you can actually apply to specific problems. 15 00:00:39.880 --> 00:00:42.119 And the authors sound like they've been in the trenches. 16 00:00:42.520 --> 00:00:47.399 Sero Furialo Oracle DBA certified professional across a bunch of technologies. 17 00:00:46.759 --> 00:00:53.119 And advitvdo years with Oracle eight through eleven G Tuning Integration, 18 00:00:53.759 --> 00:00:55.679 now a lead DBA at Amazon. 19 00:00:55.719 --> 00:00:58.600 Apparently, so real world stuff, not just theory. 20 00:00:58.960 --> 00:01:02.560 Right, So our mission for you listening in is to 21 00:01:02.600 --> 00:01:06.079 pull out the core ideas, the main techniques from these sources. 22 00:01:06.319 --> 00:01:08.599 It's like getting a shortcut to their key takeaways. 23 00:01:08.680 --> 00:01:11.480 And like the sources say, there's no magic wand for tuning, 24 00:01:11.599 --> 00:01:13.359 it's about applying the right approach. 25 00:01:13.519 --> 00:01:16.319 Okay, so let's start with the basics. What's the ultimate 26 00:01:16.400 --> 00:01:18.799 goal here? According to this material. 27 00:01:18.840 --> 00:01:23.680 Simple really make applications responsive, cut down that user weight time, 28 00:01:23.840 --> 00:01:25.319 stop the complaining emails. 29 00:01:25.599 --> 00:01:29.680 Makes sense, and critically they stress, this isn't something you 30 00:01:29.760 --> 00:01:30.640 bolt on at the end. 31 00:01:30.840 --> 00:01:34.599 No way. Tuning starts early, ideally like application design phase, 32 00:01:34.799 --> 00:01:39.079 and it keeps going through development into production. It's ongoing, 33 00:01:39.159 --> 00:01:39.840 which leads. 34 00:01:39.719 --> 00:01:42.000 Us to the process they lay out, sort. 35 00:01:41.799 --> 00:01:44.799 Of cycle, right, Yeah, an iterative process. First step, get 36 00:01:44.799 --> 00:01:45.599 a baseline. 37 00:01:45.799 --> 00:01:49.400 Okay, why is that baseline so crucial? Seems obvious, but 38 00:01:49.519 --> 00:01:50.560 let's spill it out. 39 00:01:50.799 --> 00:01:52.680 Well, if you don't know how fast things are now, 40 00:01:52.760 --> 00:01:55.519 how can you tell if your change actually helped. You 41 00:01:55.640 --> 00:01:57.840 need that starting point, that measurement. 42 00:01:57.439 --> 00:02:00.000 A point of comparison exactly. 43 00:01:59.439 --> 00:02:01.239 And the source are clear. It needs to be a 44 00:02:01.280 --> 00:02:07.120 system wide baseline, server, stats, io, network, the database itself, 45 00:02:07.159 --> 00:02:09.800 the application, the whole picture, right. 46 00:02:09.719 --> 00:02:12.800 Not just looking at one tiny piece of a baseline established? 47 00:02:13.360 --> 00:02:17.120 Then what then investigate? Use that system wide view to 48 00:02:17.319 --> 00:02:21.479 hunt down the real bottleneck, where's the actual slowdown happening. 49 00:02:21.599 --> 00:02:24.039 So you form a hypothesis basically, you got it, and 50 00:02:24.080 --> 00:02:26.319 then you assume a solution based on that. And this 51 00:02:26.360 --> 00:02:29.120 is key. You define a specific test case for it. 52 00:02:29.240 --> 00:02:30.919 And a way to back out if it goes wrong. 53 00:02:31.159 --> 00:02:35.280 Absolutely, a rollback plan is essential. Can't make things worse? 54 00:02:35.439 --> 00:02:39.280 Okay, So you implement your change, test it functionally, make 55 00:02:39.319 --> 00:02:40.039 sure it didn't. 56 00:02:39.840 --> 00:02:42.919 Break anything, right, Then you test the performance. Compare it 57 00:02:42.960 --> 00:02:44.919 directly against that baseline you took earlier. 58 00:02:44.960 --> 00:02:45.639 Did it get better? 59 00:02:45.759 --> 00:02:48.280 If yes, brilliant, you might have found your fix. 60 00:02:48.319 --> 00:02:50.800 And if not, or if it oops made things. 61 00:02:50.639 --> 00:02:54.680 Worse, then you iterate, roll back, go back to investigating, 62 00:02:55.000 --> 00:02:58.479 maybe form a different hypothesis, try another solution, keep cycling through. 63 00:02:58.639 --> 00:03:01.159 And the sources mentioned the tools you'd use for this, right, Yeah, 64 00:03:01.159 --> 00:03:02.199 the usual suspects. 65 00:03:02.479 --> 00:03:06.319 Yeah, the core oracle stuff, the data dictionary, those vital 66 00:03:06.400 --> 00:03:12.120 Dynamic performance views, the vviews, plus reporting tools like stats pack, AWR, 67 00:03:12.520 --> 00:03:16.199 ADDM for diagnostics, and of course the alert log and 68 00:03:16.280 --> 00:03:19.039 trace files for digging into specifics. That's where you get 69 00:03:19.039 --> 00:03:19.560 your data. 70 00:03:19.639 --> 00:03:23.560 Okay, process clear, Let's get into the juicy bits. The 71 00:03:23.639 --> 00:03:27.240 key tuning areas from the cookbook. They start with application design. 72 00:03:27.080 --> 00:03:30.039 Makes sense, how the app talks to the database is huge. 73 00:03:30.360 --> 00:03:32.080 First up, connection management. 74 00:03:32.240 --> 00:03:35.039 Ah yeah, The sources point out how lots of online 75 00:03:35.080 --> 00:03:38.240 tutorials show like connecting for every single. 76 00:03:38.000 --> 00:03:41.520 Web page hit, which is just terrible for performance under 77 00:03:41.599 --> 00:03:42.479 any real load. 78 00:03:42.639 --> 00:03:45.520 So what's the recommended way for web apps connection pooling? 79 00:03:45.599 --> 00:03:49.639 Definitely reuse those connections for client server OLTP, maybe middle 80 00:03:49.639 --> 00:03:51.439 tier that manages shared connections. 81 00:03:51.479 --> 00:03:52.719 And the why is pretty simple. 82 00:03:52.800 --> 00:03:56.000 Yeah, setting up a database connection takes time and resources 83 00:03:56.120 --> 00:03:59.120 doing that millions of times. It adds up fast. Reusing 84 00:03:59.240 --> 00:03:59.960 is way way. 85 00:03:59.800 --> 00:04:02.960 To keep Okay. Now, this next one sounds critical. Parsing 86 00:04:03.080 --> 00:04:05.599 and bind variables. The sources really emphasize this. 87 00:04:05.879 --> 00:04:08.199 Oh absolutely, this is a big one. They talk about 88 00:04:08.199 --> 00:04:09.919 hard parsing versus soft parsing. 89 00:04:10.080 --> 00:04:14.039 Right, hard parses oracle figures everything out and scratch syntax, permissions, 90 00:04:14.280 --> 00:04:15.319 the execution. 91 00:04:15.000 --> 00:04:18.600 Plan exactly every single time. Soft parses reusing a plan 92 00:04:18.639 --> 00:04:19.399 that's already in the. 93 00:04:19.360 --> 00:04:22.959 Cash much faster, and the magic ingredient for soft parsing, 94 00:04:23.120 --> 00:04:25.360 especially when you run the same type of query over 95 00:04:25.399 --> 00:04:28.600 and over with different values, is bind variables. 96 00:04:28.920 --> 00:04:32.279 That's the key. If your app builds SQL with literal 97 00:04:32.360 --> 00:04:35.120 values like where use radicals one twenty three and then 98 00:04:35.160 --> 00:04:37.319 where use ridicules four fifty six. 99 00:04:37.319 --> 00:04:40.560 Oracle sees two totally different queries hard parse both right. 100 00:04:40.680 --> 00:04:43.160 But if you use a bind variable like where use 101 00:04:43.240 --> 00:04:47.319 radicles some value, Oracle sees the same sequel structure. It 102 00:04:47.360 --> 00:04:50.079 can reuse the plan generated for the first value when 103 00:04:50.120 --> 00:04:52.920 the second value comes along. Soft parse huge win. 104 00:04:53.160 --> 00:04:57.000 The concept in the sources comparing Java execution times without 105 00:04:57.199 --> 00:05:01.079 and with prepared statements which use binds really drives that home, 106 00:05:01.079 --> 00:05:03.680 doesn't it. The performance difference is massive, It really is. 107 00:05:03.720 --> 00:05:06.560 They even talk about aiming for a library cash hit ratio. 108 00:05:06.600 --> 00:05:08.399 You check them into a library catch of like zero 109 00:05:08.399 --> 00:05:11.240 point nine nine nine nine one and busy OLTP systems 110 00:05:11.439 --> 00:05:12.639 almost perfect reuse. 111 00:05:12.839 --> 00:05:14.519 Wow. And there's a security angle too. 112 00:05:14.560 --> 00:05:18.199 Big time buying variables are your primary defense against SQL injection, 113 00:05:18.319 --> 00:05:20.040 so faster and safer win win. 114 00:05:20.600 --> 00:05:23.279 And plcql handles this automatically for static SQL. 115 00:05:23.360 --> 00:05:25.920 Yep, the plcql engine is smart about that. Good news. 116 00:05:25.959 --> 00:05:30.199 There another application design tip mentioned, fewer round trips to 117 00:05:30.240 --> 00:05:30.800 the database. 118 00:05:30.920 --> 00:05:34.800 Yeah, especially over networks. Each trip has latency. Better to 119 00:05:34.800 --> 00:05:35.639 bundle operations. 120 00:05:35.680 --> 00:05:38.720 If you can, like using stored procedures or packages to 121 00:05:38.800 --> 00:05:40.240 do several things in one call. 122 00:05:40.199 --> 00:05:44.680 Exactly reduce the chatter. Materialized views also get a. 123 00:05:44.680 --> 00:05:48.480 Mention ah precalculated results good for data warehouses. 124 00:05:48.519 --> 00:05:52.720 Maybe yeah, can speed up complex queries, but the sources 125 00:05:52.720 --> 00:05:56.839 give a strong warning avoid refresh on commit for MVS 126 00:05:56.920 --> 00:05:57.600 in OLTP. 127 00:05:57.839 --> 00:05:58.240 Why is that? 128 00:05:58.680 --> 00:06:01.319 Because that refresh happens to your commit, it slows down 129 00:06:01.360 --> 00:06:05.279 every transaction that modifies the base tables. Bad for response time. 130 00:06:05.399 --> 00:06:08.240 Okay, got it. Let's shift from the application code to 131 00:06:09.160 --> 00:06:11.720 how the data is actually stored. Road chaining and migration. 132 00:06:12.079 --> 00:06:12.759 That's technical. 133 00:06:13.000 --> 00:06:15.839 It is a bit. Think of a data block as 134 00:06:15.879 --> 00:06:18.920 a page. Road Chaining is when one row is just 135 00:06:19.000 --> 00:06:20.959 too big to fit on a single page, so it 136 00:06:20.959 --> 00:06:22.720 gets split across multiple blocks. 137 00:06:22.800 --> 00:06:24.040 Okay, and migration. 138 00:06:24.399 --> 00:06:26.839 Migration is when a row fix initially, but then you 139 00:06:26.959 --> 00:06:29.879 update it, it grows and there's no more space left 140 00:06:29.879 --> 00:06:33.199 in that block, so oracle moves the whole row to 141 00:06:33.279 --> 00:06:35.120 a new block and leaves a pointer behind. 142 00:06:35.319 --> 00:06:37.600 And both are bad because extra. 143 00:06:37.279 --> 00:06:40.279 Io to read that one chained row, you need to 144 00:06:40.279 --> 00:06:42.680 read multiple blocks. For a migrated row, you read the 145 00:06:42.720 --> 00:06:45.240 original block just to find the pointer than the new block. 146 00:06:45.600 --> 00:06:46.959 More work for the database. 147 00:06:47.360 --> 00:06:48.800 How do you find these sources? 148 00:06:48.839 --> 00:06:52.639 Mention analyzed table list, chained rows or using a script 149 00:06:52.680 --> 00:06:57.040 called ultchain dot squyel and fixing it for chaining. Maybe 150 00:06:57.079 --> 00:06:59.319 a larger block size for that table, though that has 151 00:06:59.360 --> 00:07:03.360 other implications for migration. Adjusting pct free to leave more 152 00:07:03.399 --> 00:07:06.480 empty space in blocks for rows to grow into during updates. 153 00:07:06.519 --> 00:07:09.920 But using multiple block sizes can make buffer cash tuning tricky. 154 00:07:10.000 --> 00:07:12.399 Right yeah, as complexity. We'll get to the buffer cash later. 155 00:07:12.519 --> 00:07:17.000 What about a lobe's large objects blos clos. 156 00:07:17.279 --> 00:07:20.120 They're often stored differently, especially if they're big like over 157 00:07:20.160 --> 00:07:23.000 four k or so, often offline or out of row, 158 00:07:23.120 --> 00:07:24.920 separate from the main table data. 159 00:07:24.920 --> 00:07:27.360 And you can save space with deduplication or compression. 160 00:07:27.439 --> 00:07:32.079 YEP techniques exist. DBMS space space usage helps check how 161 00:07:32.120 --> 00:07:33.079 much space they're using. 162 00:07:33.160 --> 00:07:36.279 Okay, Clusters index and hash. 163 00:07:36.000 --> 00:07:39.439 These physically group rows from different tables together in the 164 00:07:39.480 --> 00:07:42.319 same blocks if they share common columns and are often 165 00:07:42.439 --> 00:07:43.639 joined on those columns. 166 00:07:43.759 --> 00:07:46.639 So if you query via that cluster key, the data 167 00:07:46.680 --> 00:07:48.879 is already co located. Faster joins. 168 00:07:49.160 --> 00:07:52.199 That's the idea avoids oracle having to jump around fetching 169 00:07:52.240 --> 00:07:55.240 blocks from different tables. How you load the data into 170 00:07:55.279 --> 00:07:57.240 the cluster initially matters, though. 171 00:07:57.199 --> 00:07:59.920 Makes sense, all right, indexes, This feels fundamentally oh it. 172 00:07:59.879 --> 00:08:03.000 Is avoiding full table scans on big tables, and OLTP 173 00:08:03.240 --> 00:08:06.040 is usually priority number one. Indexes are how you do that. 174 00:08:06.199 --> 00:08:08.360 They help Oracle find rows quickly. 175 00:08:08.160 --> 00:08:12.319 Standard B tree indexes, bitmap indexes. What about multi column indexes? 176 00:08:12.360 --> 00:08:15.600 The order matters critically. If your query wear clause uses 177 00:08:15.680 --> 00:08:18.720 the first column the leading edge of a multi column index, 178 00:08:19.000 --> 00:08:23.360 Oracle can efficiently scan just a relevant range index range scan. 179 00:08:23.839 --> 00:08:27.240 But if you only query on say the second column, 180 00:08:27.560 --> 00:08:28.079 it might. 181 00:08:28.000 --> 00:08:31.000 Still use the index with an index fast full scan 182 00:08:31.120 --> 00:08:34.200 which reads the entire index, not just a range. Faster 183 00:08:34.279 --> 00:08:36.679 than a full table scan maybe, but not as good 184 00:08:36.720 --> 00:08:40.120 as a range scan, and it doesn't return rows sorted 185 00:08:40.120 --> 00:08:41.080 by the index key. 186 00:08:41.200 --> 00:08:42.480 Function based indexes. 187 00:08:42.519 --> 00:08:47.759 Descending indexes useful for avoiding explicit sort operations. Sometimes, if 188 00:08:47.799 --> 00:08:50.080 you index the result of a function or index in 189 00:08:50.120 --> 00:08:53.240 descending order, Oracle might be able to skip a sort 190 00:08:53.240 --> 00:08:55.039 step for order by or group buy. 191 00:08:55.440 --> 00:08:58.279 But indexes aren't always used even if they exist. 192 00:08:58.440 --> 00:09:01.879 Right the sources list common reach using not equal or 193 00:09:02.240 --> 00:09:04.600 applying a function to an index column unless it's a 194 00:09:04.639 --> 00:09:08.720 matching function based index searching for NL's B tree. Indexes 195 00:09:08.759 --> 00:09:10.320 don't store entries for all nul. 196 00:09:10.240 --> 00:09:13.279 Keys, ah okay, And if the query needs columns not 197 00:09:13.360 --> 00:09:14.360 in the index. 198 00:09:14.080 --> 00:09:16.519 Then oracle has to do a table access by rowd 199 00:09:16.639 --> 00:09:19.679 for every row found in the index. On small tables, 200 00:09:19.720 --> 00:09:22.240 a full scan might actually be cheaper than thousands of 201 00:09:22.279 --> 00:09:23.720 those individual table lookups. 202 00:09:23.879 --> 00:09:25.200 Six. Rebuilding and compression. 203 00:09:25.240 --> 00:09:28.720 Why rebuild if stats suggests it's become inefficient or fragmented 204 00:09:29.240 --> 00:09:33.000 The sources mentioned looking at ratios like deleted leafros versus 205 00:09:33.000 --> 00:09:37.399 total leaf ros, delf, frous frows, INDEBA indexes or debanded 206 00:09:37.399 --> 00:09:40.559 statistics if it's high maybe two, or if leaf ros 207 00:09:40.879 --> 00:09:45.679 lfro is much lower than leafblocks lfblks, or if the 208 00:09:45.720 --> 00:09:49.679 index height gets to four or more. These are just indicators, though, 209 00:09:49.759 --> 00:09:53.879 and you can rebuild online yep lets users keep working DML, 210 00:09:53.960 --> 00:09:57.240 but the rebuild takes longer. Offline is faster, but locks 211 00:09:57.279 --> 00:09:59.759 the table. You can also gather fresh stats during the 212 00:09:59.799 --> 00:10:03.639 re build. Compression save space by storing common prefix values 213 00:10:03.679 --> 00:10:07.159 only once per index block. Fewer blocks means faster reads. 214 00:10:07.639 --> 00:10:10.759 Use the compressed keyword maybe specifying how many prefixed callers 215 00:10:10.799 --> 00:10:11.279 to compress. 216 00:10:11.320 --> 00:10:13.919 Reverse key indexes sound specific, very. 217 00:10:13.759 --> 00:10:17.279 Specific use case. Think sequence generated primary keys and high 218 00:10:17.360 --> 00:10:18.080 volume inserts. 219 00:10:18.159 --> 00:10:20.320 Right, because normally all inserts go to the right end of. 220 00:10:20.320 --> 00:10:24.159 The INDEXX actually causes contention on those last few index blocks. 221 00:10:24.360 --> 00:10:26.799 A reverse key index flips the key bytes around a 222 00:10:26.840 --> 00:10:29.519 one to twenty three becomes three twenty one. Spreading inserts 223 00:10:29.519 --> 00:10:32.600 across the whole index structure reduces that hot block contention. 224 00:10:33.039 --> 00:10:37.000 Range scans become inefficient because the keys aren't stored sequentially anymore. 225 00:10:37.120 --> 00:10:41.679 Okay. Bitmap indexes good for low cardinality columns. 226 00:10:41.399 --> 00:10:45.320 Yes, columns with few distinct values like gender status flags. 227 00:10:45.759 --> 00:10:49.480 Great in data warehouses on large tables, very efficient space 228 00:10:49.519 --> 00:10:51.720 wise and for certain queries. 229 00:10:51.240 --> 00:10:52.519 But not for OLTP. 230 00:10:53.039 --> 00:10:57.159 Why again, locking If you update one row covered by 231 00:10:57.159 --> 00:11:00.440 a bitmap index, segment oracle locks that entire segment in 232 00:11:00.519 --> 00:11:04.320 OLTP with lots of concurrent updates. This causes massive bottlenecks. 233 00:11:04.320 --> 00:11:07.759 Everyone waits. They also index NAL's unlike B. 234 00:11:07.840 --> 00:11:10.399 Trees index organized tables IoTs. 235 00:11:10.559 --> 00:11:12.960 Here the table is the index data is stored within 236 00:11:13.000 --> 00:11:14.720 the primary key B tree. 237 00:11:14.519 --> 00:11:18.159 Structure, and things like compress, including overflow control, how that 238 00:11:18.240 --> 00:11:19.399 data is stored within the. 239 00:11:19.360 --> 00:11:22.200 Index precisely, how non key columns fit, what happens if 240 00:11:22.279 --> 00:11:23.480 rows get too big, et cetera. 241 00:11:23.600 --> 00:11:26.919 Partitioning big topic, major benefit partition pruning. 242 00:11:27.519 --> 00:11:30.480 If your table is partitioned, say by date range, and 243 00:11:30.519 --> 00:11:33.240 your query has a wear clause on that date, Oracle 244 00:11:33.279 --> 00:11:36.559 only needs to scan the relevant partitions. Huge performance gain 245 00:11:36.639 --> 00:11:38.840 for large tables also helps with manageability. 246 00:11:38.919 --> 00:11:41.519 All right, let's switch to optimizing the actual SQL code 247 00:11:42.360 --> 00:11:43.879 avoiding full table scans again. 248 00:11:44.000 --> 00:11:46.919 Yeah, it's a recurring theme for large OLTP tables, though 249 00:11:46.960 --> 00:11:50.279 again for small tables, FDS can be fine, even faster sometimes. 250 00:11:50.320 --> 00:11:52.840 And the high water mark HWM concept comes up. 251 00:11:52.720 --> 00:11:55.159 Here, right. Think of it as the high tide mark 252 00:11:55.159 --> 00:11:58.440 in the table, and FTS reads every block below that mark, 253 00:11:58.639 --> 00:12:00.519 even if they're now empty after deletes. 254 00:12:00.600 --> 00:12:03.000 So deleting data doesn't lower the HWM. 255 00:12:03.240 --> 00:12:07.000 Noope, only ways mentioned to reset it are truncate, alter 256 00:12:07.159 --> 00:12:12.039 table move or an export drop import altertable deallocate unused 257 00:12:12.120 --> 00:12:14.080 just free space above the HWM. 258 00:12:14.320 --> 00:12:16.759 Okay, this next one feels like a core Oracle principle 259 00:12:17.120 --> 00:12:20.879 array processing and bulk operations doing things in sets, not 260 00:12:21.039 --> 00:12:21.600 row by row. 261 00:12:21.720 --> 00:12:25.399 Absolutely fundamental the sources. Contrast row by row processing in 262 00:12:25.440 --> 00:12:27.919 loops often called row at a time or slow by 263 00:12:27.919 --> 00:12:30.720 slow processing with using bulk collect to fetch many rows 264 00:12:30.759 --> 00:12:34.279 into plsuple rays at once and FOURAL to insert, update, 265 00:12:34.360 --> 00:12:37.759 or delete many rows from an array in a single database. 266 00:12:37.399 --> 00:12:41.080 Call, and the performance difference is dramatic. 267 00:12:40.759 --> 00:12:44.320 Huge orders of magnitude faster. Typically The examples shown really 268 00:12:44.440 --> 00:12:44.919 highlight this. 269 00:12:45.240 --> 00:12:48.480 They mentioned the limit clause with these bulk operations. Why 270 00:12:48.600 --> 00:12:50.679 use that? If processing all at once is. 271 00:12:50.639 --> 00:12:56.080 Fastest memory management? Bulk collect and FOURAL use PGA memory. 272 00:12:56.600 --> 00:12:59.080 If you try to process millions of rows without limit, 273 00:12:59.320 --> 00:13:02.399 you could blow out your PGA using limit one hundred 274 00:13:02.440 --> 00:13:05.840 or limit one thousand processes in manageable chunks, preventing memory 275 00:13:05.919 --> 00:13:09.000 errors while still being vastly faster than row biro. 276 00:13:09.039 --> 00:13:12.919 Smart optimizing joins. Oracle has different ways to join tables. 277 00:13:13.000 --> 00:13:16.440 YEP, nested loops, Sort, merge, hash join are the main 278 00:13:16.480 --> 00:13:17.080 ones mentioned. 279 00:13:17.159 --> 00:13:19.120 When does it prefer each Roughly. 280 00:13:19.159 --> 00:13:21.600 Nested loops often works well if there's a good index 281 00:13:21.639 --> 00:13:23.600 on the join column of the inner table and not 282 00:13:23.679 --> 00:13:27.879 too many rows are involved. Sort merge sorts both inputs first, 283 00:13:28.120 --> 00:13:31.360 then merges good for non equi joints doesn't need indexes 284 00:13:31.399 --> 00:13:34.399 but uses sort memory CPU hash join builds a hash 285 00:13:34.480 --> 00:13:36.879 table and memory on the smaller input. Often the go 286 00:13:36.919 --> 00:13:39.440 to when nested loops isn't efficient, needs memory. 287 00:13:39.519 --> 00:13:43.080 Subqueries in versus exists not in versus not. 288 00:13:43.120 --> 00:13:45.759 Exists sources mention. There are differences in how they handle 289 00:13:45.840 --> 00:13:49.799 nlls and potential performance variations. Worth understanding the nuances for 290 00:13:49.840 --> 00:13:51.679 your specific query and tracing. 291 00:13:52.080 --> 00:13:56.200 SQL trace and tkprof central. 292 00:13:55.879 --> 00:13:59.480 Tools absolutely vital for understanding what a query is really doing. 293 00:14:00.000 --> 00:14:03.559 TKPRF formats the trace file and shows you execution counts, 294 00:14:03.600 --> 00:14:08.600 CPU time, a lapse time ioreads disc versus memory rose 295 00:14:08.639 --> 00:14:11.360 processed all the details you need to diagnose where the 296 00:14:11.360 --> 00:14:12.240 time is actually going. 297 00:14:12.320 --> 00:14:16.639 Okay, let's talk sorts order by group by distinct. They 298 00:14:16.679 --> 00:14:17.720 all involve sorting right. 299 00:14:17.799 --> 00:14:21.000 Often yes for some joint methods. Key difference is in 300 00:14:21.120 --> 00:14:22.879 memory versus on disc sorts. 301 00:14:22.960 --> 00:14:23.879 Your memory is faster. 302 00:14:24.120 --> 00:14:27.320 Much faster happens in the PGA on disc sorts happen 303 00:14:27.360 --> 00:14:29.240 when the data to be sorted is too big for 304 00:14:29.279 --> 00:14:32.080 the available PGA, so oracle spills it to the temporary 305 00:14:32.080 --> 00:14:34.960 table space slower due to disc io. How do you 306 00:14:35.000 --> 00:14:38.840 monitor this ve stat has sort counters memory versus desk. 307 00:14:39.240 --> 00:14:43.159 Execution plans might show temp speary. Tuning often involves sizing 308 00:14:43.200 --> 00:14:44.919 the PGA appropriately. 309 00:14:44.360 --> 00:14:47.720 And VPGA target advice helps with that PGA sizing. 310 00:14:47.840 --> 00:14:50.799 Yeah, it's a great advisor. View simulates different PGA sizes 311 00:14:50.840 --> 00:14:53.759 and estimates the impact on sorts and hash joins helps 312 00:14:53.759 --> 00:14:55.279 you set pgeggor get target. 313 00:14:55.320 --> 00:14:58.679 But the best sort is no sort at all exactly. 314 00:14:58.879 --> 00:15:01.360 If you have the right index, oracle might be able 315 00:15:01.399 --> 00:15:03.480 to read the data in the order needed for an 316 00:15:03.600 --> 00:15:07.159 order buy directly from the index index full scan or 317 00:15:07.200 --> 00:15:10.919 satisfy a group by or distinct using an index index 318 00:15:11.000 --> 00:15:14.679 fast full scan maybe with a sort unique potentially avoided 319 00:15:14.759 --> 00:15:17.279 or made faster saves a whole processing step. 320 00:15:17.600 --> 00:15:21.480 Analytical functions like rank denser rank good for. 321 00:15:21.440 --> 00:15:24.320 Top then yeah, useful for that kind of analysis. The 322 00:15:24.360 --> 00:15:27.120 source example shows how rank handles ties might give you 323 00:15:27.120 --> 00:15:29.360 more than n rows if there are ties at the boundary. 324 00:15:29.480 --> 00:15:32.960 Aggregates. Min max count indexes. 325 00:15:32.440 --> 00:15:35.320 Help here too, definitely. For minimax an index on the column, 326 00:15:35.399 --> 00:15:37.919 let's oracle just grab the first or last index entry, 327 00:15:37.919 --> 00:15:41.480 avoiding a full scan for account. There's a cool optimization 328 00:15:41.600 --> 00:15:44.639 mentioned If a suitable bitmap index exists on a non 329 00:15:44.720 --> 00:15:47.960 Knoll column, oracle might use that for a very fast count. 330 00:15:47.879 --> 00:15:51.720 And troubleshooting temspace to monitoring usage pretty much. 331 00:15:52.039 --> 00:15:55.840 Views like vtemspace, hutter, vsort segment, vtemp file help you 332 00:15:55.879 --> 00:15:58.480 see who's using temspace and if you're running out, and 333 00:15:58.559 --> 00:16:00.879 how to create an assigned t table spaces. 334 00:16:00.960 --> 00:16:04.080 Let's loop back to PLCQ optimization bulk processing again. 335 00:16:04.320 --> 00:16:09.039 Yep, Bellki collect and foural same principle. Massive gains over 336 00:16:09.120 --> 00:16:12.480 roebiro loops inside plcql two can't stress that enough. 337 00:16:12.519 --> 00:16:15.159 Short circuit if statements, small tweaks. 338 00:16:15.600 --> 00:16:18.879 Yeah, In if condition one and D condition two, put 339 00:16:18.919 --> 00:16:21.879 the one most likely to be false first. In if 340 00:16:21.879 --> 00:16:24.440 condition one or R condition two, put the one most 341 00:16:24.480 --> 00:16:28.200 likely to be true first, might save evaluating the second conditions. 342 00:16:28.279 --> 00:16:32.720 Sometimes recursion generally avoid for performance critical stuff. 343 00:16:32.759 --> 00:16:36.440 That's the advice. Each recursive call eats PGA memory for 344 00:16:36.519 --> 00:16:40.519 its state. Deep recursion can get expensive. Fast iterative loops 345 00:16:40.519 --> 00:16:44.279 are usually much more efficient in plseql. The factorial example 346 00:16:44.279 --> 00:16:45.320 comparison shows. 347 00:16:45.120 --> 00:16:49.440 This native compilation PLCQL code type equals in native. 348 00:16:49.279 --> 00:16:52.679 Compiles PLSQL to machine code instead of interpreted bytecode can 349 00:16:52.679 --> 00:16:55.799 give a speed boost, as the binomial coefficient example suggests, 350 00:16:55.960 --> 00:16:58.320 but compilation takes longer and uses more resources. 351 00:16:58.399 --> 00:17:00.600 PLCQL result cache, similar. 352 00:17:00.360 --> 00:17:02.519 To the SQL yeah result cash clause on a function 353 00:17:02.639 --> 00:17:05.359 cash's results based on input parameters. If called again with 354 00:17:05.400 --> 00:17:08.200 the same inputs, oracle returns the cash result without re 355 00:17:08.319 --> 00:17:11.880 running the function. Need relycen if it depends on tables inlining. 356 00:17:12.319 --> 00:17:14.920 Compiling called code directly into the caller. 357 00:17:14.799 --> 00:17:19.119 Rate reduces function call overhead. PREGMA inline or setting PLSKO 358 00:17:19.119 --> 00:17:21.359 optimized level to three enables. 359 00:17:20.920 --> 00:17:24.079 This virtual columns. This eleven G feature sounds interesting. 360 00:17:24.160 --> 00:17:27.039 It is cool define a column based on an expression 361 00:17:27.039 --> 00:17:29.880 of other columns, but the data isn't stored physically. 362 00:17:30.000 --> 00:17:30.960 A digwin, you. 363 00:17:30.920 --> 00:17:34.079 Can index or partition based on that calculated value without 364 00:17:34.200 --> 00:17:37.279 needing a trigger to maintain it. Triggers can be major 365 00:17:37.319 --> 00:17:40.680 performance killers. The gross capital example in the loan's table 366 00:17:40.759 --> 00:17:42.240 makes this really clear. 367 00:17:42.519 --> 00:17:45.960 Okay, shifting again improving the oracle optimizer itself. How it 368 00:17:46.000 --> 00:17:47.759 works briefly parses. 369 00:17:47.400 --> 00:17:51.599 The sequel, optimizes, picks the plan using costs, generates the 370 00:17:51.680 --> 00:17:56.039 row source executes the plan. The cost based optimizer CBO 371 00:17:56.559 --> 00:18:00.119 uses statistics, object info and system parameters to SIP to 372 00:18:00.119 --> 00:18:03.279 make the cost of different plans and pick the cheapest optimizer. 373 00:18:03.359 --> 00:18:06.480 Hints suggestions not commands exactly. 374 00:18:06.519 --> 00:18:09.400 You put them in comments plus it inta They guide 375 00:18:09.400 --> 00:18:11.920 the optimizer, but it might ignore them if they're wrong 376 00:18:12.039 --> 00:18:15.079 or conflict, or follow them even if the resulting plan 377 00:18:15.200 --> 00:18:15.559 is bad. 378 00:18:15.920 --> 00:18:18.440 So always verify the plan after adding a hint. 379 00:18:18.759 --> 00:18:22.599 Absolutely critical. Don't assume the hint worked or helped without 380 00:18:22.680 --> 00:18:25.079 checking the explained plan or vesicle plan. 381 00:18:25.279 --> 00:18:29.160 And the foundation for good CBO decisions is statistics. YEP. 382 00:18:29.559 --> 00:18:33.519 Accurate, up to date statistics are essential. DBMS stats package 383 00:18:33.599 --> 00:18:36.720 gather procedures is the way to collect them. Dynamic sampling 384 00:18:36.799 --> 00:18:39.400 is mentioned as a fallback. If stats are missing. You 385 00:18:39.440 --> 00:18:42.640 can lock stats, view history restore old stats too. 386 00:18:43.039 --> 00:18:45.160 Histograms needed when data is. 387 00:18:45.119 --> 00:18:49.880 Skewed right when values aren't evenly distributed. Standard stats assume 388 00:18:50.000 --> 00:18:54.279 even distribution, which can lead to bad cardinality estimates. Histograms 389 00:18:54.279 --> 00:18:57.599 give the CEO more detail about actual data distribution, leading 390 00:18:57.599 --> 00:19:00.400 to better plans created via DBMS stats. 391 00:19:00.039 --> 00:19:03.000 Pland stability plans. Changing unexpectedly is a pain. 392 00:19:03.480 --> 00:19:07.000 Tools. Two main ones mentioned stored outlines are the older 393 00:19:07.039 --> 00:19:10.480 way They lock down a specific plan stable but rigid, 394 00:19:10.799 --> 00:19:13.000 won't adapt if a better plan becomes available later. 395 00:19:13.119 --> 00:19:16.279 That's SQL baselines. The eleven G approach more flexible. 396 00:19:16.480 --> 00:19:19.039 They capture known good plans but can evolve. You can 397 00:19:19.079 --> 00:19:22.319 test new plans generated by the optimizer and if verified 398 00:19:22.359 --> 00:19:27.640 to be better using DBMSPM evlvescal plan baseline, the baseline 399 00:19:27.640 --> 00:19:31.519 can accept the new improved plan. Stability plus adaptation. 400 00:19:31.880 --> 00:19:35.119 Adaptive cursor sharing tackles bind variable. 401 00:19:34.799 --> 00:19:38.119