Search

Top 60 Oracle Blogs

Recent comments

March 2018

Oracle Database 18c: Introduction to Memoptimized RowStore (Memory of a Free Festival)

One of the potentially cool new features introduced in Oracle Database 18c is the Memoptimized RowStore, designed to improve the performance and scalability of key-value based queries. A new pool in the SGA called the Memoptimize Pool can be configured to store specific heap tables that you may wish to optimize, in a manner not […]

Quick lists when generating test data using plsql

One of the outstanding issues that I have had on the testdata_ninja library, was the ability to do quick lists.
A short list of possible values, like a status column or open/close kind of values.

Quick lists when generating test data using plsql

One of the outstanding issues that I have had on the testdata_ninja library, was the ability to do quick lists.
A short list of possible values, like a status column or open/close kind of values.

18c new Lost Write Protection

There are many layers between the Oracle Database pwrite() calls and the physical sector written on disk: filesystem, logical volume, SAN or NAS, with a lot of smart software running for Virtualisation, Compression, Snapshotting, Synchronisation… Are you sure that the changes you made to your data is actually persisted on disk, completely and without any corruption? In case of bug or crash in the storage layer, it may happen that only part of the changes was written. In the case of crash, Oracle ensures that the datafile headers are written at the end, so that recovery can kick-in after the crash. Then, a partially written block can be detected and restored. With different checksum settings, you can also check block integrity while writing or reading. But that protects only for fractured blocks. What if a block write just did not occur? An old version of the block remains and then is perfectly correct for checksum, RMAN, and DBV.

Friday Philosophy – Criticism is Critical for Believable Endorsement

If you had a friend who always told you that your were the best, that you had no faults, and that everything you did was great – would you trust them? I know I would not. I am fully aware that I am not perfect(*). I used to see this sometimes in relationships too, especially when I was younger. One of them would be so desperate for their boyfriend/girlfriend to like them that they would never criticise the light of their life. The relationship never lasted as it was, well, creepy and false.

Conditional SQL – 5

Here’s a note that has been sitting around for more than 3 years (the draft date is Jan 2015), waiting for me to finish it off; and in that time we’ve got a new version of Oracle that changes the solution to the problem it presented. (I also managed to write “Conditional SQL –  6” in the intervening period !)

This posting started with a question on the OTN (now ODC) database forum about an execution plan used by 11.2.0.3.  Here’s a model to represent the data and the query:

Setting Oracle Session Parameters for Specific Process Scheduler Processes

This note describes a mechanism for setting initialisation parameters for specific processes run on the process scheduler. I will demonstrate it relation to nVision, but it has general application in PeopleSoft.
A table is used to hold metadata that described what setting is applied to which processes. A trigger on the process scheduler request table PSPRCSRQST reads that data and alters the session setting. This approach is easier to adjust and understand that static PL/SQL code in a trigger.

How to Identify Active Lines in an Adaptive Execution Plan in DBA_HIST_SQL_PLAN

When investigating performance problems I spend a lot of my time profiling ASH and AWR data. I sometimes want to join back to captured plans in DBA_HIST_SQL_PLAN to see how an object was access, or which index was used, or how many rows the optimizer thought it would get.
From Oracle 12c, we can get adaptive execution plans where the optimizer considers different plans at runtime. Mostly it is a choice between either a nested loop or a hash join of tables.
For example, in the below execution plan, DBMS_XPLAN includes a note to say that inactive plans with a dash.  Thus we can see that the nested loop lookup of PS_LEDGER using the PSCLEDGER index was rejected in favour of a Bloom filter on a full scan of the ledger table.

Predictable random test data using plsql

One of the problems in using random generated test data, is that it is random. Because sometimes what you need
is test data that is the same every time you generate it. This entry will look at how you can achieve this using
the seed functionality of the dbms_random package.

Predictable random test data using plsql

One of the problems in using random generated test data, is that it is random. Because sometimes what you need
is test data that is the same every time you generate it. This entry will look at how you can achieve this using
the seed functionality of the dbms_random package.