Oracle PL/SQL – Quantify Code Performance For Comparison Of Different Approaches – Tom Kyte’s “runstats”

Is my PL/SQL code going to perform better? I have rewritten my Oracle code. Now, how do I tell if my coding approach A is better than coding approach B? If someone were to ask you the above question, typically, you would give the following answers If one approach is faster than the other, that … Continue reading Oracle PL/SQL – Quantify Code Performance For Comparison Of Different Approaches – Tom Kyte’s “runstats”

Advertisements

Oracle – Parallel To PGA (P2P) & Serial To SGA (S2S) – Caching Differences And When Parallelism Is Not The Panacea For Performance!

If you are running into memory issues such as below, there could be several causes but one of the causes is excessive parallelism and exhaustion of PGA memory. “ORA-01034: ORACLE not available / ORA-27102: out of memory / Linux-x86_64 Error: 12: Cannot allocate” Please read on to find out how to resolve this. There are … Continue reading Oracle – Parallel To PGA (P2P) & Serial To SGA (S2S) – Caching Differences And When Parallelism Is Not The Panacea For Performance!

Oracle REDO – A Basic Explanation

In an earlier post, we saw how Oracle’s UNDO works in very basic terms. REDO is another critical aspect that goes hand in hand with UNDO. Let us get the basics of REDO sorted out here. UNDO and REDO In its most basic sense UNDO is information saved to get back in case a ROLLBACK … Continue reading Oracle REDO – A Basic Explanation

Oracle – Is my APPEND Hint Being Ignored? How To Find Out?

Why is the APPEND hint so great? In a prior post, we extolled the virtues of the APPEND hint (AKA Direct Path Insert). It is a great option to use • UNDO usage is negligible. It is only generated for index maintenance and meta data changes. The keyword is index maintenance – indexes generate UNDO. … Continue reading Oracle – Is my APPEND Hint Being Ignored? How To Find Out?

Oracle – TRUNCATE vs DELETE – Spoiler: TRUNCATE Wins & Why? Hours vs Seconds!

Why DELETE is bad? In Oracle, when it comes to deleting  ** A L L **   the data from a table (clearing the contents of a staging table for example), using a DELETE statement is the most inefficient choice. Here are some reasons why DELETE is bad It uses UNDO It uses REDO It fires … Continue reading Oracle – TRUNCATE vs DELETE – Spoiler: TRUNCATE Wins & Why? Hours vs Seconds!