Oracle Database 11gR2 Performance Tuning Cookbook

You know that feeling right, You're using an app, click save, maybe run a report and it just crawls.

Yeah, or you get that email. The system slow.

Exactly, and if there's an Oracle database behind it, figuring out why it's slow and crucially how to speed it up. Well, that's the.

Big challenge, absolutely, and it's usually not just one thing, is it. It could be the code, the database structure itself, the servers, the network, even it gets complex.

That's precisely what we're digging into today. We've got some material specific excerpts from the Oracle Database eleven GR two Performance Tuning Cookbook.

Ah, the cookbook I like that suggests practical recipes here exactly.

Like solutions you can actually apply to specific problems.

And the authors sound like they've been in the trenches. Sero Furialo Oracle DBA certified professional across a bunch of technologies.

And advitvdo years with Oracle eight through eleven G Tuning Integration, now a lead DBA at Amazon.

Apparently, so real world stuff, not just theory.

Right, So our mission for you listening in is to pull out the core ideas, the main techniques from these sources. It's like getting a shortcut to their key takeaways.

And like the sources say, there's no magic wand for tuning, it's about applying the right approach.

Okay, so let's start with the basics. What's the ultimate goal here? According to this material.

Simple really make applications responsive, cut down that user weight time, stop the complaining emails.

Makes sense, and critically they stress, this isn't something you bolt on at the end.

No way. Tuning starts early, ideally like application design phase, and it keeps going through development into production. It's ongoing, which leads.

Us to the process they lay out, sort.

Of cycle, right, Yeah, an iterative process. First step, get a baseline.

Okay, why is that baseline so crucial? Seems obvious, but let's spill it out.

Well, if you don't know how fast things are now, how can you tell if your change actually helped. You need that starting point, that measurement.

A point of comparison exactly.

And the source are clear. It needs to be a system wide baseline, server, stats, io, network, the database itself, the application, the whole picture, right.

Not just looking at one tiny piece of a baseline established?

Then what then investigate? Use that system wide view to hunt down the real bottleneck, where's the actual slowdown happening. So you form a hypothesis basically, you got it, and then you assume a solution based on that. And this is key. You define a specific test case for it.

And a way to back out if it goes wrong.

Absolutely, a rollback plan is essential. Can't make things worse?

Okay, So you implement your change, test it functionally, make sure it didn't.

Break anything, right, Then you test the performance. Compare it directly against that baseline you took earlier.

Did it get better?

If yes, brilliant, you might have found your fix.

And if not, or if it oops made things.

Worse, then you iterate, roll back, go back to investigating, maybe form a different hypothesis, try another solution, keep cycling through.

And the sources mentioned the tools you'd use for this, right, Yeah, the usual suspects.

Yeah, the core oracle stuff, the data dictionary, those vital Dynamic performance views, the vviews, plus reporting tools like stats pack, AWR, ADDM for diagnostics, and of course the alert log and trace files for digging into specifics. That's where you get your data.

Okay, process clear, Let's get into the juicy bits. The key tuning areas from the cookbook. They start with application design.

Makes sense, how the app talks to the database is huge. First up, connection management.

Ah yeah, The sources point out how lots of online tutorials show like connecting for every single.

Web page hit, which is just terrible for performance under any real load.

So what's the recommended way for web apps connection pooling?

Definitely reuse those connections for client server OLTP, maybe middle tier that manages shared connections.

And the why is pretty simple.

Yeah, setting up a database connection takes time and resources doing that millions of times. It adds up fast. Reusing is way way.

To keep Okay. Now, this next one sounds critical. Parsing and bind variables. The sources really emphasize this.

Oh absolutely, this is a big one. They talk about hard parsing versus soft parsing.

Right, hard parses oracle figures everything out and scratch syntax, permissions, the execution.

Plan exactly every single time. Soft parses reusing a plan that's already in the.

Cash much faster, and the magic ingredient for soft parsing, especially when you run the same type of query over and over with different values, is bind variables.

That's the key. If your app builds SQL with literal values like where use radicals one twenty three and then where use ridicules four fifty six.

Oracle sees two totally different queries hard parse both right.

But if you use a bind variable like where use radicles some value, Oracle sees the same sequel structure. It can reuse the plan generated for the first value when the second value comes along. Soft parse huge win.

The concept in the sources comparing Java execution times without and with prepared statements which use binds really drives that home, doesn't it. The performance difference is massive, It really is.

They even talk about aiming for a library cash hit ratio. You check them into a library catch of like zero point nine nine nine nine one and busy OLTP systems almost perfect reuse.

Wow. And there's a security angle too.

Big time buying variables are your primary defense against SQL injection, so faster and safer win win.

And plcql handles this automatically for static SQL.

Yep, the plcql engine is smart about that. Good news.

There another application design tip mentioned, fewer round trips to the database.

Yeah, especially over networks. Each trip has latency. Better to bundle operations.

If you can, like using stored procedures or packages to do several things in one call.

Exactly reduce the chatter. Materialized views also get a.

Mention ah precalculated results good for data warehouses.

Maybe yeah, can speed up complex queries, but the sources give a strong warning avoid refresh on commit for MVS in OLTP.

Why is that?

Because that refresh happens to your commit, it slows down every transaction that modifies the base tables. Bad for response time.

Okay, got it. Let's shift from the application code to how the data is actually stored. Road chaining and migration. That's technical.

It is a bit. Think of a data block as a page. Road Chaining is when one row is just too big to fit on a single page, so it gets split across multiple blocks.

Okay, and migration.

Migration is when a row fix initially, but then you update it, it grows and there's no more space left in that block, so oracle moves the whole row to a new block and leaves a pointer behind.

And both are bad because extra.

Io to read that one chained row, you need to read multiple blocks. For a migrated row, you read the original block just to find the pointer than the new block. More work for the database.

How do you find these sources?

Mention analyzed table list, chained rows or using a script called ultchain dot squyel and fixing it for chaining. Maybe a larger block size for that table, though that has other implications for migration. Adjusting pct free to leave more empty space in blocks for rows to grow into during updates.

But using multiple block sizes can make buffer cash tuning tricky.

Right yeah, as complexity. We'll get to the buffer cash later.

What about a lobe's large objects blos clos.

They're often stored differently, especially if they're big like over four k or so, often offline or out of row, separate from the main table data.

And you can save space with deduplication or compression.

YEP techniques exist. DBMS space space usage helps check how much space they're using.

Okay, Clusters index and hash.

These physically group rows from different tables together in the same blocks if they share common columns and are often joined on those columns.

So if you query via that cluster key, the data is already co located. Faster joins.

That's the idea avoids oracle having to jump around fetching blocks from different tables. How you load the data into the cluster initially matters, though.

Makes sense, all right, indexes, This feels fundamentally oh it.

Is avoiding full table scans on big tables, and OLTP is usually priority number one. Indexes are how you do that. They help Oracle find rows quickly.

Standard B tree indexes, bitmap indexes. What about multi column indexes?

The order matters critically. If your query wear clause uses the first column the leading edge of a multi column index, Oracle can efficiently scan just a relevant range index range scan.

But if you only query on say the second column, it might.

Still use the index with an index fast full scan which reads the entire index, not just a range. Faster than a full table scan maybe, but not as good as a range scan, and it doesn't return rows sorted by the index key.

Function based indexes.

Descending indexes useful for avoiding explicit sort operations. Sometimes, if you index the result of a function or index in descending order, Oracle might be able to skip a sort step for order by or group buy.

But indexes aren't always used even if they exist.

Right the sources list common reach using not equal or applying a function to an index column unless it's a matching function based index searching for NL's B tree. Indexes don't store entries for all nul.

Keys, ah okay, And if the query needs columns not in the index.

Then oracle has to do a table access by rowd for every row found in the index. On small tables, a full scan might actually be cheaper than thousands of those individual table lookups.

Six. Rebuilding and compression.

Why rebuild if stats suggests it's become inefficient or fragmented The sources mentioned looking at ratios like deleted leafros versus total leaf ros, delf, frous frows, INDEBA indexes or debanded statistics if it's high maybe two, or if leaf ros lfro is much lower than leafblocks lfblks, or if the index height gets to four or more. These are just indicators, though, and you can rebuild online yep lets users keep working DML, but the rebuild takes longer. Offline is faster, but locks

Reverse key indexes sound specific, very.

Specific use case. Think sequence generated primary keys and high volume inserts.

Right, because normally all inserts go to the right end of.

The INDEXX actually causes contention on those last few index blocks. A reverse key index flips the key bytes around a one to twenty three becomes three twenty one. Spreading inserts across the whole index structure reduces that hot block contention. Range scans become inefficient because the keys aren't stored sequentially anymore.

Okay. Bitmap indexes good for low cardinality columns.

Yes, columns with few distinct values like gender status flags. Great in data warehouses on large tables, very efficient space wise and for certain queries.

But not for OLTP.

Why again, locking If you update one row covered by a bitmap index, segment oracle locks that entire segment in OLTP with lots of concurrent updates. This causes massive bottlenecks. Everyone waits. They also index NAL's unlike B.

Trees index organized tables IoTs.

Here the table is the index data is stored within the primary key B tree.

Structure, and things like compress, including overflow control, how that data is stored within the.

Index precisely, how non key columns fit, what happens if rows get too big, et cetera.

Partitioning big topic, major benefit partition pruning.

If your table is partitioned, say by date range, and your query has a wear clause on that date, Oracle only needs to scan the relevant partitions. Huge performance gain for large tables also helps with manageability.

All right, let's switch to optimizing the actual SQL code avoiding full table scans again.

Yeah, it's a recurring theme for large OLTP tables, though again for small tables, FDS can be fine, even faster sometimes.

And the high water mark HWM concept comes up.

Here, right. Think of it as the high tide mark in the table, and FTS reads every block below that mark, even if they're now empty after deletes.

So deleting data doesn't lower the HWM.

Noope, only ways mentioned to reset it are truncate, alter table move or an export drop import altertable deallocate unused just free space above the HWM.

Okay, this next one feels like a core Oracle principle array processing and bulk operations doing things in sets, not row by row.

Absolutely fundamental the sources. Contrast row by row processing in loops often called row at a time or slow by slow processing with using bulk collect to fetch many rows into plsuple rays at once and FOURAL to insert, update, or delete many rows from an array in a single database.

Call, and the performance difference is dramatic.

Huge orders of magnitude faster. Typically The examples shown really highlight this.

They mentioned the limit clause with these bulk operations. Why use that? If processing all at once is.

Fastest memory management? Bulk collect and FOURAL use PGA memory. If you try to process millions of rows without limit, you could blow out your PGA using limit one hundred or limit one thousand processes in manageable chunks, preventing memory errors while still being vastly faster than row biro.

Smart optimizing joins. Oracle has different ways to join tables.

YEP, nested loops, Sort, merge, hash join are the main ones mentioned.

When does it prefer each Roughly.

Nested loops often works well if there's a good index on the join column of the inner table and not too many rows are involved. Sort merge sorts both inputs first, then merges good for non equi joints doesn't need indexes but uses sort memory CPU hash join builds a hash table and memory on the smaller input. Often the go to when nested loops isn't efficient, needs memory.

Subqueries in versus exists not in versus not.

Exists sources mention. There are differences in how they handle nlls and potential performance variations. Worth understanding the nuances for your specific query and tracing.

SQL trace and tkprof central.

Tools absolutely vital for understanding what a query is really doing. TKPRF formats the trace file and shows you execution counts, CPU time, a lapse time ioreads disc versus memory rose processed all the details you need to diagnose where the time is actually going.

Okay, let's talk sorts order by group by distinct. They all involve sorting right.

Often yes for some joint methods. Key difference is in memory versus on disc sorts.

Your memory is faster.

Much faster happens in the PGA on disc sorts happen when the data to be sorted is too big for the available PGA, so oracle spills it to the temporary table space slower due to disc io. How do you monitor this ve stat has sort counters memory versus desk. Execution plans might show temp speary. Tuning often involves sizing the PGA appropriately.

And VPGA target advice helps with that PGA sizing.

Yeah, it's a great advisor. View simulates different PGA sizes and estimates the impact on sorts and hash joins helps you set pgeggor get target.

But the best sort is no sort at all exactly.

If you have the right index, oracle might be able to read the data in the order needed for an order buy directly from the index index full scan or satisfy a group by or distinct using an index index fast full scan maybe with a sort unique potentially avoided or made faster saves a whole processing step.

Analytical functions like rank denser rank good for.

Top then yeah, useful for that kind of analysis. The source example shows how rank handles ties might give you more than n rows if there are ties at the boundary.

Aggregates. Min max count indexes.

Help here too, definitely. For minimax an index on the column, let's oracle just grab the first or last index entry, avoiding a full scan for account. There's a cool optimization mentioned If a suitable bitmap index exists on a non Knoll column, oracle might use that for a very fast count.

And troubleshooting temspace to monitoring usage pretty much.

Views like vtemspace, hutter, vsort segment, vtemp file help you see who's using temspace and if you're running out, and how to create an assigned t table spaces.

Let's loop back to PLCQ optimization bulk processing again.

Yep, Bellki collect and foural same principle. Massive gains over roebiro loops inside plcql two can't stress that enough.

Short circuit if statements, small tweaks.

Yeah, In if condition one and D condition two, put the one most likely to be false first. In if condition one or R condition two, put the one most likely to be true first, might save evaluating the second conditions.

Sometimes recursion generally avoid for performance critical stuff.

That's the advice. Each recursive call eats PGA memory for its state. Deep recursion can get expensive. Fast iterative loops are usually much more efficient in plseql. The factorial example comparison shows.

This native compilation PLCQL code type equals in native.

Compiles PLSQL to machine code instead of interpreted bytecode can give a speed boost, as the binomial coefficient example suggests, but compilation takes longer and uses more resources.

PLCQL result cache, similar.

To the SQL yeah result cash clause on a function cash's results based on input parameters. If called again with the same inputs, oracle returns the cash result without re running the function. Need relycen if it depends on tables inlining.

Compiling called code directly into the caller.

Rate reduces function call overhead. PREGMA inline or setting PLSKO optimized level to three enables.

This virtual columns. This eleven G feature sounds interesting.

It is cool define a column based on an expression of other columns, but the data isn't stored physically.

A digwin, you.

Can index or partition based on that calculated value without needing a trigger to maintain it. Triggers can be major performance killers. The gross capital example in the loan's table makes this really clear.

Okay, shifting again improving the oracle optimizer itself. How it works briefly parses.

The sequel, optimizes, picks the plan using costs, generates the row source executes the plan. The cost based optimizer CBO uses statistics, object info and system parameters to SIP to make the cost of different plans and pick the cheapest optimizer.

Hints suggestions not commands exactly.

You put them in comments plus it inta They guide the optimizer, but it might ignore them if they're wrong or conflict, or follow them even if the resulting plan is bad.

So always verify the plan after adding a hint.

Absolutely critical. Don't assume the hint worked or helped without checking the explained plan or vesicle plan.

And the foundation for good CBO decisions is statistics. YEP.

Accurate, up to date statistics are essential. DBMS stats package gather procedures is the way to collect them. Dynamic sampling is mentioned as a fallback. If stats are missing. You can lock stats, view history restore old stats too.

Histograms needed when data is.

Skewed right when values aren't evenly distributed. Standard stats assume even distribution, which can lead to bad cardinality estimates. Histograms give the CEO more detail about actual data distribution, leading to better plans created via DBMS stats.

Pland stability plans. Changing unexpectedly is a pain.

Tools. Two main ones mentioned stored outlines are the older way They lock down a specific plan stable but rigid, won't adapt if a better plan becomes available later.

That's SQL baselines. The eleven G approach more flexible.

They capture known good plans but can evolve. You can test new plans generated by the optimizer and if verified to be better using DBMSPM evlvescal plan baseline, the baseline can accept the new improved plan. Stability plus adaptation.

Adaptive cursor sharing tackles bind variable.

Peaking exactly that issue where the plan chosen based on the first bind value scene isn't good for later values. ACS. Let's oracle detect this and maintain multiple plans for the same sequel, using the best one based on the range of bind values encountered.

Pretty smart and automated tuning. SQL tuning Visor SQL tuning sets yeah.

Tools to help automate the process. SQL tuning sets STS are just collections of SQL statements you want to analyze the SEQLE Tuning Advisor can analyze single queries a WR data or an sts and give recommendations new stats, profiles, hints, indexes, maybe.

Okay, some other optimization odds and ends. SQL result cash.

Again right using the plus result cash hint on a query cash is the actual results set in memory SGA or client. If the exact same query runs again, boom, instant results.

Parallel SQL for multi core systems.

YEP plus parallel hint breaks down query operations to run concurrently can speed things up dramatically on the right hardware, but the sources warn it can hurt performance on single CPU machines, or if you don't have enough resources.

Direct path inserts insert plus appendres.

Faster for bulkloads, skips the buffer. Cash writes directly to data files above the HWM trade offs can invalidate indexes need rebuild might be slower if IO is already saturated, data isn't immediately available in the buffer.

Cash for reads create table is select cts uses direct path implicitly.

Usually yes, efficient way to copy large amounts of data, and.

Schooloader data pump direct path mode there too, YEP.

Similar concept for loading external data much faster than conventional path, which goes through the buffer. Cash and SQL air.

And dropping indexes before a huge load, then rebuilding Yeah faster sometimes often yes.

The overhead of maintaining indexes during millions of inserts can be higher than a drop load and recreate.

Cycle all right memory tuning SGA versus.

PGA SGA system global area shared by all processes PGA program global area private to each process, v process vmstat, VISA stat help monitor.

Shared pool, library cash crucial for SQL.

Reuse absolutely stores parsed SQL pl single high hit ratio and VILO library cache is key low ratio often points back to no bind.

Variables really vicious cycle.

You can use visila a pl air ficile plan to see what's cashed DDMS share pool keep to pin critical objects. There's also the reserved pool for large allocations and the dictionary cash for metadata EGA.

Use for sorts hash joints tuned via p egercate.

Target primarily yes. Vckt Argita advice is your best friend for sizing that UGA is part of PGA holding session state affected by cursor parameters like open cursors.

For cash cash is data blocks some disc.

YEP reduces physical io. V clabkh advice helps size it calculate the hit ratio logical reads versus physical reads from stats. Multiple block sizes can complicate things.

And the keep in recycled pools special.

Purpose keep is for small frequently used objects you want to guarantee stay cached or maybe small fts tables. Recycle is for large objects you access once and don't want polluting the main cache assigned objects via alter table.

Index storage tuning. IO raid levels matter big time.

Sources say RAY ten zero plus one is best for performance but costs more. Rade five has a right penalty, bad for redo logs and undo.

Asynchronous io allows overlapping IO requests.

Yes helps DVWN and LGWR make better use of IO bandwidth, improving throughput and scalability. Less way waiting for iocompletion.

Checkpoints need to monitor them, crucial for recovery.

Alert log and VC stats show activity if checkpoints started far ex seeds completed. Redo logs might be switching too often, probably because they're too small.

Relugs tuning involves monitoring switches be like history and weights v sense of N four log file parallel.

Right exactly bottlenecks there can really slow things down fast.

Big area tuning contention users waiting on each.

Other locks are a common cost. Row locks, TX table locks, tm V lock helps find who's blocking whom. The example of two sessions trying to update the same row makes it clear.

Deadlocks The nasty circular weight.

YEP database de texts IT kills one transaction logs IT You find details in trace files and the alert.

Log transaction design.

Commit frequency matters a lot too frequent commits ad overhead two, infrequent holds, locks, longer increases, undo risks RI zero one five to five to five snapshot too old. Long transactions are generally tough on the system.

Latches, low level memory.

Locks, very fast short duration locks. Protecting SGA structures, monitor weights and contention using vlbcch vlatched children, OA reports latch free events.

But the key takeaway for latches there's symptoms.

High lashed contention means something else is wrong, usually bad SQL, lack of bind variables, undersized caches. You tune the cause, not the latch itself, and.

Library cash latches directly link back to.

Not using bind variables. All that hard parsing causes contention for latches protecting the library cash. It all ties together.

Wow. Okay, that was definitely deep dive from application code storage SQL, PLSEQL memory io all the way to contention.

It really covers the spectrum, doesn't it, And it hammers home that cookbook idea. Lots of different potential issues lots of different recipes or techniques to address them, and.

The absolute need for that structured process. Baseline, investigate, hypothesize, test, measure, iterate. You can't just guess, no guessing.

You need to diagnose the specific problem first, use the tools, look under the hood, understand the why.

So maybe a final thought for you, the listener, based on all this, Yeah, just how incredibly interconnected everything is inside Oracle. Yeah, you change application code with bind variables and suddenly library cash latch contention drops. You choose the wrong raid level and redo log rights become a bottleneck. You size memory incorrectly and sort spill to disc slowing everything down.

It's all linked. Understanding those connections, how turning one area can impact another. That's maybe the real key takeaway from this material. It's thinking about the whole system.

Absolutely well, thanks for joining us on this exploration of Oracle database performance tuning, gated by these cookbook excerpts hopefully gives you a better framework for tackling those slowdowns.