Speaker 1: You know that feeling right, You're using an app, click save,
Speaker 1: maybe run a report and it just crawls.
Speaker 2: Yeah, or you get that email. The system slow.
Speaker 1: Exactly, and if there's an Oracle database behind it, figuring
Speaker 1: out why it's slow and crucially how to speed it up. Well,
Speaker 1: that's the.
Speaker 2: Big challenge, absolutely, and it's usually not just one thing,
Speaker 2: is it. It could be the code, the database structure itself,
Speaker 2: the servers, the network, even it gets complex.
Speaker 1: That's precisely what we're digging into today. We've got some
Speaker 1: material specific excerpts from the Oracle Database eleven GR two
Speaker 1: Performance Tuning Cookbook.
Speaker 2: Ah, the cookbook I like that suggests practical recipes here exactly.
Speaker 1: Like solutions you can actually apply to specific problems.
Speaker 2: And the authors sound like they've been in the trenches.
Speaker 2: Sero Furialo Oracle DBA certified professional across a bunch of technologies.
Speaker 1: And advitvdo years with Oracle eight through eleven G Tuning Integration,
Speaker 1: now a lead DBA at Amazon.
Speaker 2: Apparently, so real world stuff, not just theory.
Speaker 1: Right, So our mission for you listening in is to
Speaker 1: pull out the core ideas, the main techniques from these sources.
Speaker 1: It's like getting a shortcut to their key takeaways.
Speaker 2: And like the sources say, there's no magic wand for tuning,
Speaker 2: it's about applying the right approach.
Speaker 1: Okay, so let's start with the basics. What's the ultimate
Speaker 1: goal here? According to this material.
Speaker 2: Simple really make applications responsive, cut down that user weight time,
Speaker 2: stop the complaining emails.
Speaker 1: Makes sense, and critically they stress, this isn't something you
Speaker 1: bolt on at the end.
Speaker 2: No way. Tuning starts early, ideally like application design phase,
Speaker 2: and it keeps going through development into production. It's ongoing,
Speaker 2: which leads.
Speaker 1: Us to the process they lay out, sort.
Speaker 2: Of cycle, right, Yeah, an iterative process. First step, get
Speaker 2: a baseline.
Speaker 1: Okay, why is that baseline so crucial? Seems obvious, but
Speaker 1: let's spill it out.
Speaker 2: Well, if you don't know how fast things are now,
Speaker 2: how can you tell if your change actually helped. You
Speaker 2: need that starting point, that measurement.
Speaker 1: A point of comparison exactly.
Speaker 2: And the source are clear. It needs to be a
Speaker 2: system wide baseline, server, stats, io, network, the database itself,
Speaker 2: the application, the whole picture, right.
Speaker 1: Not just looking at one tiny piece of a baseline established?
Speaker 2: Then what then investigate? Use that system wide view to
Speaker 2: hunt down the real bottleneck, where's the actual slowdown happening.
Speaker 2: So you form a hypothesis basically, you got it, and
Speaker 2: then you assume a solution based on that. And this
Speaker 2: is key. You define a specific test case for it.
Speaker 1: And a way to back out if it goes wrong.
Speaker 2: Absolutely, a rollback plan is essential. Can't make things worse?
Speaker 1: Okay, So you implement your change, test it functionally, make
Speaker 1: sure it didn't.
Speaker 2: Break anything, right, Then you test the performance. Compare it
Speaker 2: directly against that baseline you took earlier.
Speaker 1: Did it get better?
Speaker 2: If yes, brilliant, you might have found your fix.
Speaker 1: And if not, or if it oops made things.
Speaker 2: Worse, then you iterate, roll back, go back to investigating,
Speaker 2: maybe form a different hypothesis, try another solution, keep cycling through.
Speaker 1: And the sources mentioned the tools you'd use for this, right, Yeah,
Speaker 1: the usual suspects.
Speaker 2: Yeah, the core oracle stuff, the data dictionary, those vital
Speaker 2: Dynamic performance views, the vviews, plus reporting tools like stats pack, AWR,
Speaker 2: ADDM for diagnostics, and of course the alert log and
Speaker 2: trace files for digging into specifics. That's where you get
Speaker 2: your data.
Speaker 1: Okay, process clear, Let's get into the juicy bits. The
Speaker 1: key tuning areas from the cookbook. They start with application design.
Speaker 2: Makes sense, how the app talks to the database is huge.
Speaker 2: First up, connection management.
Speaker 1: Ah yeah, The sources point out how lots of online
Speaker 1: tutorials show like connecting for every single.
Speaker 2: Web page hit, which is just terrible for performance under
Speaker 2: any real load.
Speaker 1: So what's the recommended way for web apps connection pooling?
Speaker 2: Definitely reuse those connections for client server OLTP, maybe middle
Speaker 2: tier that manages shared connections.
Speaker 1: And the why is pretty simple.
Speaker 2: Yeah, setting up a database connection takes time and resources
Speaker 2: doing that millions of times. It adds up fast. Reusing
Speaker 2: is way way.
Speaker 1: To keep Okay. Now, this next one sounds critical. Parsing
Speaker 1: and bind variables. The sources really emphasize this.
Speaker 2: Oh absolutely, this is a big one. They talk about
Speaker 2: hard parsing versus soft parsing.
Speaker 1: Right, hard parses oracle figures everything out and scratch syntax, permissions,
Speaker 1: the execution.
Speaker 2: Plan exactly every single time. Soft parses reusing a plan
Speaker 2: that's already in the.
Speaker 1: Cash much faster, and the magic ingredient for soft parsing,
Speaker 1: especially when you run the same type of query over
Speaker 1: and over with different values, is bind variables.
Speaker 2: That's the key. If your app builds SQL with literal
Speaker 2: values like where use radicals one twenty three and then
Speaker 2: where use ridicules four fifty six.
Speaker 1: Oracle sees two totally different queries hard parse both right.
Speaker 2: But if you use a bind variable like where use
Speaker 2: radicles some value, Oracle sees the same sequel structure. It
Speaker 2: can reuse the plan generated for the first value when
Speaker 2: the second value comes along. Soft parse huge win.
Speaker 1: The concept in the sources comparing Java execution times without
Speaker 1: and with prepared statements which use binds really drives that home,
Speaker 1: doesn't it. The performance difference is massive, It really is.
Speaker 2: They even talk about aiming for a library cash hit ratio.
Speaker 2: You check them into a library catch of like zero
Speaker 2: point nine nine nine nine one and busy OLTP systems
Speaker 2: almost perfect reuse.
Speaker 1: Wow. And there's a security angle too.
Speaker 2: Big time buying variables are your primary defense against SQL injection,
Speaker 2: so faster and safer win win.
Speaker 1: And plcql handles this automatically for static SQL.
Speaker 2: Yep, the plcql engine is smart about that. Good news.
Speaker 1: There another application design tip mentioned, fewer round trips to
Speaker 1: the database.
Speaker 2: Yeah, especially over networks. Each trip has latency. Better to
Speaker 2: bundle operations.
Speaker 1: If you can, like using stored procedures or packages to
Speaker 1: do several things in one call.
Speaker 2: Exactly reduce the chatter. Materialized views also get a.
Speaker 1: Mention ah precalculated results good for data warehouses.
Speaker 2: Maybe yeah, can speed up complex queries, but the sources
Speaker 2: give a strong warning avoid refresh on commit for MVS
Speaker 2: in OLTP.
Speaker 1: Why is that?
Speaker 2: Because that refresh happens to your commit, it slows down
Speaker 2: every transaction that modifies the base tables. Bad for response time.
Speaker 1: Okay, got it. Let's shift from the application code to
Speaker 1: how the data is actually stored. Road chaining and migration.
Speaker 1: That's technical.
Speaker 2: It is a bit. Think of a data block as
Speaker 2: a page. Road Chaining is when one row is just
Speaker 2: too big to fit on a single page, so it
Speaker 2: gets split across multiple blocks.
Speaker 1: Okay, and migration.
Speaker 2: Migration is when a row fix initially, but then you
Speaker 2: update it, it grows and there's no more space left
Speaker 2: in that block, so oracle moves the whole row to
Speaker 2: a new block and leaves a pointer behind.
Speaker 1: And both are bad because extra.
Speaker 2: Io to read that one chained row, you need to
Speaker 2: read multiple blocks. For a migrated row, you read the
Speaker 2: original block just to find the pointer than the new block.
Speaker 2: More work for the database.
Speaker 1: How do you find these sources?
Speaker 2: Mention analyzed table list, chained rows or using a script
Speaker 2: called ultchain dot squyel and fixing it for chaining. Maybe
Speaker 2: a larger block size for that table, though that has
Speaker 2: other implications for migration. Adjusting pct free to leave more
Speaker 2: empty space in blocks for rows to grow into during updates.
Speaker 1: But using multiple block sizes can make buffer cash tuning tricky.
Speaker 2: Right yeah, as complexity. We'll get to the buffer cash later.
Speaker 1: What about a lobe's large objects blos clos.
Speaker 2: They're often stored differently, especially if they're big like over
Speaker 2: four k or so, often offline or out of row,
Speaker 2: separate from the main table data.
Speaker 1: And you can save space with deduplication or compression.
Speaker 2: YEP techniques exist. DBMS space space usage helps check how
Speaker 2: much space they're using.
Speaker 1: Okay, Clusters index and hash.
Speaker 2: These physically group rows from different tables together in the
Speaker 2: same blocks if they share common columns and are often
Speaker 2: joined on those columns.
Speaker 1: So if you query via that cluster key, the data
Speaker 1: is already co located. Faster joins.
Speaker 2: That's the idea avoids oracle having to jump around fetching
Speaker 2: blocks from different tables. How you load the data into
Speaker 2: the cluster initially matters, though.
Speaker 1: Makes sense, all right, indexes, This feels fundamentally oh it.
Speaker 2: Is avoiding full table scans on big tables, and OLTP
Speaker 2: is usually priority number one. Indexes are how you do that.
Speaker 2: They help Oracle find rows quickly.
Speaker 1: Standard B tree indexes, bitmap indexes. What about multi column indexes?
Speaker 2: The order matters critically. If your query wear clause uses
Speaker 2: the first column the leading edge of a multi column index,
Speaker 2: Oracle can efficiently scan just a relevant range index range scan.
Speaker 1: But if you only query on say the second column,
Speaker 1: it might.
Speaker 2: Still use the index with an index fast full scan
Speaker 2: which reads the entire index, not just a range. Faster
Speaker 2: than a full table scan maybe, but not as good
Speaker 2: as a range scan, and it doesn't return rows sorted
Speaker 2: by the index key.
Speaker 1: Function based indexes.
Speaker 2: Descending indexes useful for avoiding explicit sort operations. Sometimes, if
Speaker 2: you index the result of a function or index in
Speaker 2: descending order, Oracle might be able to skip a sort
Speaker 2: step for order by or group buy.
Speaker 1: But indexes aren't always used even if they exist.
Speaker 2: Right the sources list common reach using not equal or
Speaker 2: applying a function to an index column unless it's a
Speaker 2: matching function based index searching for NL's B tree. Indexes
Speaker 2: don't store entries for all nul.
Speaker 1: Keys, ah okay, And if the query needs columns not
Speaker 1: in the index.
Speaker 2: Then oracle has to do a table access by rowd
Speaker 2: for every row found in the index. On small tables,
Speaker 2: a full scan might actually be cheaper than thousands of
Speaker 2: those individual table lookups.
Speaker 1: Six. Rebuilding and compression.
Speaker 2: Why rebuild if stats suggests it's become inefficient or fragmented
Speaker 2: The sources mentioned looking at ratios like deleted leafros versus
Speaker 2: total leaf ros, delf, frous frows, INDEBA indexes or debanded
Speaker 2: statistics if it's high maybe two, or if leaf ros
Speaker 2: lfro is much lower than leafblocks lfblks, or if the
Speaker 2: index height gets to four or more. These are just indicators, though,
Speaker 2: and you can rebuild online yep lets users keep working DML,
Speaker 2: but the rebuild takes longer. Offline is faster, but locks
Speaker 2: the table. You can also gather fresh stats during the
Speaker 2: re build. Compression save space by storing common prefix values
Speaker 2: only once per index block. Fewer blocks means faster reads.
Speaker 2: Use the compressed keyword maybe specifying how many prefixed callers
Speaker 2: to compress.
Speaker 1: Reverse key indexes sound specific, very.
Speaker 2: Specific use case. Think sequence generated primary keys and high
Speaker 2: volume inserts.
Speaker 1: Right, because normally all inserts go to the right end of.
Speaker 2: The INDEXX actually causes contention on those last few index blocks.
Speaker 2: A reverse key index flips the key bytes around a
Speaker 2: one to twenty three becomes three twenty one. Spreading inserts
Speaker 2: across the whole index structure reduces that hot block contention.
Speaker 2: Range scans become inefficient because the keys aren't stored sequentially anymore.
Speaker 1: Okay. Bitmap indexes good for low cardinality columns.
Speaker 2: Yes, columns with few distinct values like gender status flags.
Speaker 2: Great in data warehouses on large tables, very efficient space
Speaker 2: wise and for certain queries.
Speaker 1: But not for OLTP.
Speaker 2: Why again, locking If you update one row covered by
Speaker 2: a bitmap index, segment oracle locks that entire segment in
Speaker 2: OLTP with lots of concurrent updates. This causes massive bottlenecks.
Speaker 2: Everyone waits. They also index NAL's unlike B.
Speaker 1: Trees index organized tables IoTs.
Speaker 2: Here the table is the index data is stored within
Speaker 2: the primary key B tree.
Speaker 1: Structure, and things like compress, including overflow control, how that
Speaker 1: data is stored within the.
Speaker 2: Index precisely, how non key columns fit, what happens if
Speaker 2: rows get too big, et cetera.
Speaker 1: Partitioning big topic, major benefit partition pruning.
Speaker 2: If your table is partitioned, say by date range, and
Speaker 2: your query has a wear clause on that date, Oracle
Speaker 2: only needs to scan the relevant partitions. Huge performance gain
Speaker 2: for large tables also helps with manageability.
Speaker 1: All right, let's switch to optimizing the actual SQL code
Speaker 1: avoiding full table scans again.
Speaker 2: Yeah, it's a recurring theme for large OLTP tables, though
Speaker 2: again for small tables, FDS can be fine, even faster sometimes.
Speaker 1: And the high water mark HWM concept comes up.
Speaker 2: Here, right. Think of it as the high tide mark
Speaker 2: in the table, and FTS reads every block below that mark,
Speaker 2: even if they're now empty after deletes.
Speaker 1: So deleting data doesn't lower the HWM.
Speaker 2: Noope, only ways mentioned to reset it are truncate, alter
Speaker 2: table move or an export drop import altertable deallocate unused
Speaker 2: just free space above the HWM.
Speaker 1: Okay, this next one feels like a core Oracle principle
Speaker 1: array processing and bulk operations doing things in sets, not
Speaker 1: row by row.
Speaker 2: Absolutely fundamental the sources. Contrast row by row processing in
Speaker 2: loops often called row at a time or slow by
Speaker 2: slow processing with using bulk collect to fetch many rows
Speaker 2: into plsuple rays at once and FOURAL to insert, update,
Speaker 2: or delete many rows from an array in a single database.
Speaker 1: Call, and the performance difference is dramatic.
Speaker 2: Huge orders of magnitude faster. Typically The examples shown really
Speaker 2: highlight this.
Speaker 1: They mentioned the limit clause with these bulk operations. Why
Speaker 1: use that? If processing all at once is.
Speaker 2: Fastest memory management? Bulk collect and FOURAL use PGA memory.
Speaker 2: If you try to process millions of rows without limit,
Speaker 2: you could blow out your PGA using limit one hundred
Speaker 2: or limit one thousand processes in manageable chunks, preventing memory
Speaker 2: errors while still being vastly faster than row biro.
Speaker 1: Smart optimizing joins. Oracle has different ways to join tables.
Speaker 2: YEP, nested loops, Sort, merge, hash join are the main
Speaker 2: ones mentioned.
Speaker 1: When does it prefer each Roughly.
Speaker 2: Nested loops often works well if there's a good index
Speaker 2: on the join column of the inner table and not
Speaker 2: too many rows are involved. Sort merge sorts both inputs first,
Speaker 2: then merges good for non equi joints doesn't need indexes
Speaker 2: but uses sort memory CPU hash join builds a hash
Speaker 2: table and memory on the smaller input. Often the go
Speaker 2: to when nested loops isn't efficient, needs memory.
Speaker 1: Subqueries in versus exists not in versus not.
Speaker 2: Exists sources mention. There are differences in how they handle
Speaker 2: nlls and potential performance variations. Worth understanding the nuances for
Speaker 2: your specific query and tracing.
Speaker 1: SQL trace and tkprof central.
Speaker 2: Tools absolutely vital for understanding what a query is really doing.
Speaker 2: TKPRF formats the trace file and shows you execution counts,
Speaker 2: CPU time, a lapse time ioreads disc versus memory rose
Speaker 2: processed all the details you need to diagnose where the
Speaker 2: time is actually going.
Speaker 1: Okay, let's talk sorts order by group by distinct. They
Speaker 1: all involve sorting right.
Speaker 2: Often yes for some joint methods. Key difference is in
Speaker 2: memory versus on disc sorts.
Speaker 1: Your memory is faster.
Speaker 2: Much faster happens in the PGA on disc sorts happen
Speaker 2: when the data to be sorted is too big for
Speaker 2: the available PGA, so oracle spills it to the temporary
Speaker 2: table space slower due to disc io. How do you
Speaker 2: monitor this ve stat has sort counters memory versus desk.
Speaker 2: Execution plans might show temp speary. Tuning often involves sizing
Speaker 2: the PGA appropriately.
Speaker 1: And VPGA target advice helps with that PGA sizing.
Speaker 2: Yeah, it's a great advisor. View simulates different PGA sizes
Speaker 2: and estimates the impact on sorts and hash joins helps
Speaker 2: you set pgeggor get target.
Speaker 1: But the best sort is no sort at all exactly.
Speaker 2: If you have the right index, oracle might be able
Speaker 2: to read the data in the order needed for an
Speaker 2: order buy directly from the index index full scan or
Speaker 2: satisfy a group by or distinct using an index index
Speaker 2: fast full scan maybe with a sort unique potentially avoided
Speaker 2: or made faster saves a whole processing step.
Speaker 1: Analytical functions like rank denser rank good for.
Speaker 2: Top then yeah, useful for that kind of analysis. The
Speaker 2: source example shows how rank handles ties might give you
Speaker 2: more than n rows if there are ties at the boundary.
Speaker 1: Aggregates. Min max count indexes.
Speaker 2: Help here too, definitely. For minimax an index on the column,
Speaker 2: let's oracle just grab the first or last index entry,
Speaker 2: avoiding a full scan for account. There's a cool optimization
Speaker 2: mentioned If a suitable bitmap index exists on a non
Speaker 2: Knoll column, oracle might use that for a very fast count.
Speaker 1: And troubleshooting temspace to monitoring usage pretty much.
Speaker 2: Views like vtemspace, hutter, vsort segment, vtemp file help you
Speaker 2: see who's using temspace and if you're running out, and
Speaker 2: how to create an assigned t table spaces.
Speaker 1: Let's loop back to PLCQ optimization bulk processing again.
Speaker 2: Yep, Bellki collect and foural same principle. Massive gains over
Speaker 2: roebiro loops inside plcql two can't stress that enough.
Speaker 1: Short circuit if statements, small tweaks.
Speaker 2: Yeah, In if condition one and D condition two, put
Speaker 2: the one most likely to be false first. In if
Speaker 2: condition one or R condition two, put the one most
Speaker 2: likely to be true first, might save evaluating the second conditions.
Speaker 1: Sometimes recursion generally avoid for performance critical stuff.
Speaker 2: That's the advice. Each recursive call eats PGA memory for
Speaker 2: its state. Deep recursion can get expensive. Fast iterative loops
Speaker 2: are usually much more efficient in plseql. The factorial example
Speaker 2: comparison shows.
Speaker 1: This native compilation PLCQL code type equals in native.
Speaker 2: Compiles PLSQL to machine code instead of interpreted bytecode can
Speaker 2: give a speed boost, as the binomial coefficient example suggests,
Speaker 2: but compilation takes longer and uses more resources.
Speaker 1: PLCQL result cache, similar.
Speaker 2: To the SQL yeah result cash clause on a function
Speaker 2: cash's results based on input parameters. If called again with
Speaker 2: the same inputs, oracle returns the cash result without re
Speaker 2: running the function. Need relycen if it depends on tables inlining.
Speaker 1: Compiling called code directly into the caller.
Speaker 2: Rate reduces function call overhead. PREGMA inline or setting PLSKO
Speaker 2: optimized level to three enables.
Speaker 1: This virtual columns. This eleven G feature sounds interesting.
Speaker 2: It is cool define a column based on an expression
Speaker 2: of other columns, but the data isn't stored physically.
Speaker 1: A digwin, you.
Speaker 2: Can index or partition based on that calculated value without
Speaker 2: needing a trigger to maintain it. Triggers can be major
Speaker 2: performance killers. The gross capital example in the loan's table
Speaker 2: makes this really clear.
Speaker 1: Okay, shifting again improving the oracle optimizer itself. How it
Speaker 1: works briefly parses.
Speaker 2: The sequel, optimizes, picks the plan using costs, generates the
Speaker 2: row source executes the plan. The cost based optimizer CBO
Speaker 2: uses statistics, object info and system parameters to SIP to
Speaker 2: make the cost of different plans and pick the cheapest optimizer.
Speaker 1: Hints suggestions not commands exactly.
Speaker 2: You put them in comments plus it inta They guide
Speaker 2: the optimizer, but it might ignore them if they're wrong
Speaker 2: or conflict, or follow them even if the resulting plan
Speaker 2: is bad.
Speaker 1: So always verify the plan after adding a hint.
Speaker 2: Absolutely critical. Don't assume the hint worked or helped without
Speaker 2: checking the explained plan or vesicle plan.
Speaker 1: And the foundation for good CBO decisions is statistics. YEP.
Speaker 2: Accurate, up to date statistics are essential. DBMS stats package
Speaker 2: gather procedures is the way to collect them. Dynamic sampling
Speaker 2: is mentioned as a fallback. If stats are missing. You
Speaker 2: can lock stats, view history restore old stats too.
Speaker 1: Histograms needed when data is.
Speaker 2: Skewed right when values aren't evenly distributed. Standard stats assume
Speaker 2: even distribution, which can lead to bad cardinality estimates. Histograms
Speaker 2: give the CEO more detail about actual data distribution, leading
Speaker 2: to better plans created via DBMS stats.
Speaker 1: Pland stability plans. Changing unexpectedly is a pain.
Speaker 2: Tools. Two main ones mentioned stored outlines are the older
Speaker 2: way They lock down a specific plan stable but rigid,
Speaker 2: won't adapt if a better plan becomes available later.
Speaker 1: That's SQL baselines. The eleven G approach more flexible.
Speaker 2: They capture known good plans but can evolve. You can
Speaker 2: test new plans generated by the optimizer and if verified
Speaker 2: to be better using DBMSPM evlvescal plan baseline, the baseline
Speaker 2: can accept the new improved plan. Stability plus adaptation.
Speaker 1: Adaptive cursor sharing tackles bind variable.
Speaker 2: Peaking exactly that issue where the plan chosen based on
Speaker 2: the first bind value scene isn't good for later values. ACS.
Speaker 2: Let's oracle detect this and maintain multiple plans for the
Speaker 2: same sequel, using the best one based on the range
Speaker 2: of bind values encountered.
Speaker 1: Pretty smart and automated tuning. SQL tuning Visor SQL tuning
Speaker 1: sets yeah.
Speaker 2: Tools to help automate the process. SQL tuning sets STS
Speaker 2: are just collections of SQL statements you want to analyze
Speaker 2: the SEQLE Tuning Advisor can analyze single queries a WR
Speaker 2: data or an sts and give recommendations new stats, profiles, hints, indexes, maybe.
Speaker 1: Okay, some other optimization odds and ends. SQL result cash.
Speaker 2: Again right using the plus result cash hint on a
Speaker 2: query cash is the actual results set in memory SGA
Speaker 2: or client. If the exact same query runs again, boom,
Speaker 2: instant results.
Speaker 1: Parallel SQL for multi core systems.
Speaker 2: YEP plus parallel hint breaks down query operations to run
Speaker 2: concurrently can speed things up dramatically on the right hardware,
Speaker 2: but the sources warn it can hurt performance on single
Speaker 2: CPU machines, or if you don't have enough resources.
Speaker 1: Direct path inserts insert plus appendres.
Speaker 2: Faster for bulkloads, skips the buffer. Cash writes directly to
Speaker 2: data files above the HWM trade offs can invalidate indexes
Speaker 2: need rebuild might be slower if IO is already saturated,
Speaker 2: data isn't immediately available in the buffer.
Speaker 1: Cash for reads create table is select cts uses direct
Speaker 1: path implicitly.
Speaker 2: Usually yes, efficient way to copy large amounts of data, and.
Speaker 1: Schooloader data pump direct path mode there too, YEP.
Speaker 2: Similar concept for loading external data much faster than conventional path,
Speaker 2: which goes through the buffer. Cash and SQL air.
Speaker 1: And dropping indexes before a huge load, then rebuilding Yeah
Speaker 1: faster sometimes often yes.
Speaker 2: The overhead of maintaining indexes during millions of inserts can
Speaker 2: be higher than a drop load and recreate.
Speaker 1: Cycle all right memory tuning SGA versus.
Speaker 2: PGA SGA system global area shared by all processes PGA
Speaker 2: program global area private to each process, v process vmstat,
Speaker 2: VISA stat help monitor.
Speaker 1: Shared pool, library cash crucial for SQL.
Speaker 2: Reuse absolutely stores parsed SQL pl single high hit ratio
Speaker 2: and VILO library cache is key low ratio often points
Speaker 2: back to no bind.
Speaker 1: Variables really vicious cycle.
Speaker 2: You can use visila a pl air ficile plan to
Speaker 2: see what's cashed DDMS share pool keep to pin critical objects.
Speaker 2: There's also the reserved pool for large allocations and the
Speaker 2: dictionary cash for metadata EGA.
Speaker 1: Use for sorts hash joints tuned via p egercate.
Speaker 2: Target primarily yes. Vckt Argita advice is your best friend
Speaker 2: for sizing that UGA is part of PGA holding session
Speaker 2: state affected by cursor parameters like open cursors.
Speaker 1: For cash cash is data blocks some disc.
Speaker 2: YEP reduces physical io. V clabkh advice helps size it
Speaker 2: calculate the hit ratio logical reads versus physical reads from stats.
Speaker 2: Multiple block sizes can complicate things.
Speaker 1: And the keep in recycled pools special.
Speaker 2: Purpose keep is for small frequently used objects you want
Speaker 2: to guarantee stay cached or maybe small fts tables. Recycle
Speaker 2: is for large objects you access once and don't want
Speaker 2: polluting the main cache assigned objects via alter table.
Speaker 1: Index storage tuning. IO raid levels matter big time.
Speaker 2: Sources say RAY ten zero plus one is best for
Speaker 2: performance but costs more. Rade five has a right penalty,
Speaker 2: bad for redo logs and undo.
Speaker 1: Asynchronous io allows overlapping IO requests.
Speaker 2: Yes helps DVWN and LGWR make better use of IO bandwidth,
Speaker 2: improving throughput and scalability. Less way waiting for iocompletion.
Speaker 1: Checkpoints need to monitor them, crucial for recovery.
Speaker 2: Alert log and VC stats show activity if checkpoints started
Speaker 2: far ex seeds completed. Redo logs might be switching too often,
Speaker 2: probably because they're too small.
Speaker 1: Relugs tuning involves monitoring switches be like history and weights
Speaker 1: v sense of N four log file parallel.
Speaker 2: Right exactly bottlenecks there can really slow things down fast.
Speaker 1: Big area tuning contention users waiting on each.
Speaker 2: Other locks are a common cost. Row locks, TX table locks,
Speaker 2: tm V lock helps find who's blocking whom. The example
Speaker 2: of two sessions trying to update the same row makes
Speaker 2: it clear.
Speaker 1: Deadlocks The nasty circular weight.
Speaker 2: YEP database de texts IT kills one transaction logs IT
Speaker 2: You find details in trace files and the alert.
Speaker 1: Log transaction design.
Speaker 2: Commit frequency matters a lot too frequent commits ad overhead two,
Speaker 2: infrequent holds, locks, longer increases, undo risks RI zero one
Speaker 2: five to five to five snapshot too old. Long transactions
Speaker 2: are generally tough on the system.
Speaker 1: Latches, low level memory.
Speaker 2: Locks, very fast short duration locks. Protecting SGA structures, monitor
Speaker 2: weights and contention using vlbcch vlatched children, OA reports latch
Speaker 2: free events.
Speaker 1: But the key takeaway for latches there's symptoms.
Speaker 2: High lashed contention means something else is wrong, usually bad SQL,
Speaker 2: lack of bind variables, undersized caches. You tune the cause,
Speaker 2: not the latch itself, and.
Speaker 1: Library cash latches directly link back to.
Speaker 2: Not using bind variables. All that hard parsing causes contention
Speaker 2: for latches protecting the library cash. It all ties together.
Speaker 1: Wow. Okay, that was definitely deep dive from application code
Speaker 1: storage SQL, PLSEQL memory io all the way to contention.
Speaker 2: It really covers the spectrum, doesn't it, And it hammers
Speaker 2: home that cookbook idea. Lots of different potential issues lots
Speaker 2: of different recipes or techniques to address them, and.
Speaker 1: The absolute need for that structured process. Baseline, investigate, hypothesize, test, measure, iterate.
Speaker 1: You can't just guess, no guessing.
Speaker 2: You need to diagnose the specific problem first, use the tools,
Speaker 2: look under the hood, understand the why.
Speaker 1: So maybe a final thought for you, the listener, based
Speaker 1: on all this, Yeah, just how incredibly interconnected everything is
Speaker 1: inside Oracle. Yeah, you change application code with bind variables
Speaker 1: and suddenly library cash latch contention drops. You choose the
Speaker 1: wrong raid level and redo log rights become a bottleneck.
Speaker 1: You size memory incorrectly and sort spill to disc slowing
Speaker 1: everything down.
Speaker 2: It's all linked. Understanding those connections, how turning one area
Speaker 2: can impact another. That's maybe the real key takeaway from
Speaker 2: this material. It's thinking about the whole system.
Speaker 1: Absolutely well, thanks for joining us on this exploration of
Speaker 1: Oracle database performance tuning, gated by these cookbook excerpts hopefully
Speaker 1: gives you a better framework for tackling those slowdowns.