Top 60 Oracle Blogs

Recent comments

March 2018

Create your Oracle test data using JSON and PLSQL

For a long time the only way to create a generator using the testdata_ninja package, was using a pure text
based syntax. It was using special characters and was maybe a bit obscure and not user friendly. The reason for this
was that the databases I needed this package on, was running Oracle 10 and 11. So I needed the package to be compatible
with those versions, and JSON is not supported natively. Looking at the requirements, it would be a good fit for JSON
though. So recently when I needed to run this on Oracle version 12, I had the chance to finally add JSON support. It makes
it a lot more easy to write the generator definitions, as you can see in the below examples.

Column Groups

There’s a question on the ODC database forum about column groups that throws up an interesting side point. The OP is looking at a query like the following and asking about which column groups might help the optimizer get the best plan:

Trivadis Performance Days 2018

I’m very excited and privileged to be asked to present the opening and closing sessions at this year’s Trivadis Performance Training Days in Zurich, Switzerland on 19-20 September 2018. It’s one of the premier Oracle training events in Europe, one which I’ve always wanted to attend. This year has a fantastic lineup, so I’m really […]

Fewer Platform Flags on Indexes from PeopleTools 8.55

It has always been possible in Application Designer to specify upon which databases platforms each index should be built.  This is really a feature that is used by PeopleSoft development, rather than customers to deliver indexes that are more appropriate for a particular platform due to differences in the optimizer.
Over the years, the number of supported PeopleSoft platforms has declined.  In PeopleTools 8.45, it went down from 9 to 6 and in PeopleTools 8.55 it has gone down to just 4, but there are still 9 columns on PSINDEXDEFN that correspond to the original 9 supported platforms.

My New David Bowie Blog

Just a short note to say I’ve started a new blog that we focus on the one and only David Bowie: Those of you that only vaguely know me, know I’m a huge David Bowie fan, so I thought it might be a bit of fun to write about his extraordinary body of work. I’m […]

Training Class Manuals For Sale

I have previously offered spare printed training manuals last year for sale here and these were snapped up. I have just found one manual for my two day class - how to perform a security audit of an Oracle database....[Read More]

Posted by Pete On 06/03/18 At 02:51 PM

Match_recognise – 2

In my previous post I presented a warning about the potential cost of sorting and the cost of failing to find a match after each pass of a long search. In a comment on that post Stew Ashton reminded me that the cost of repeatedly trying to find a match starting from “the next row down” could be less of a threat than the cost of “back-tracking” before moving to the next row down.

Taking the example from the previous posting to explain – the requirement was for customers who had executed a transaction in September but not October, and a match_recognize() clause suggested on the ODC (formerly OTN) database forum to implement this requirement was as follows:

Profiling Execution Plans

In my previous blog post, I demonstrated how to identify the active lines in an adaptive execution plan on DBA_HIST_SQL_PLAN so that I could profile behaviour. This post demonstrates a practical application of that technique. This statement comes out of the PeopleSoft Financials Multi-currency Processing. A process extracts groups of rows in elimination sets into a working storage table and joins that table to the ledger table for further processing. The number of rows extracted from the elimination set selector table (PS_ELIM_CF_SEL2001 in this case) can vary in a single process from a few to a few thousand. However, the process does regather optimizer statistics each time.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">SQL_ID fwcdxjy41j23n

A look into Oracle redo, part 6: oracle post-wait commit and the on disk SCN

This is the sixth part in a blog series about Oracle database redo. The previous posts provided information about the log writer writing, this post is about the process that is waiting after issuing commit for the log writer to write it’s redo from the public redo strand. When the database is using post/wait for process commits, the committing process follows the following (simplified) procedure: