November 2017

UKOUG Tech 2017

Its been a couple years since I presented at UKOUG.  After taking last year off to prioritize some projects with Delphix, I’m back to speaking and the event committee was gracious enough to offer a goth girl a couple spots on the schedule this year.

The event is in Birmingham, and although I’ve never been there, I’m told by reports, it’s rather rainy and cold this time of year, so I better prepare, (nothing like people selling you on a location…:))

I have two sessions this year, the first one is on Super Sunday:

nVision Performance Tuning: 11. Excel -v- OpenXML

This blog post is part of a series that discusses how to get optimal performance from PeopleSoft nVision reporting as used in General Ledger.

The general objective the performance tuning changes described in this series of blog posts has been to improve the performance of individual nVision reports, but also to allow many reports to execute concurrently.
However, if you use Excel 2010, Excel 2013 or above, then you may notice run times are significantly longer than with Excel 2007.  Also, from PeopleTools 8.54, Excel 2007 is no longer certified.

Advanced Oracle Troubleshooting seminar in 2018!

A lot of people have asked me to do another run of my Advanced Oracle Troubleshooting training or at least get access to previous recordings – so I decided to geek out over the holiday period, update the material with latest stuff and run one more AOT class in 2018!

The online training will take place on 29 January – 2 February 2018 (Part 1) & 26 February – 2 March 2018 (Part 2).

The latest TOC is below:

Seminar registration details:

UKOUG Conference Survival Guide

I’ve been going to the UKOUG conference for about… Well, most of this century. I think this year (2017) will be my 14th visit. Not only that but I’ve helped organise the tech side of the conference for several years. I was the Database Stream or whole Tech Stream lead for the last 3 years (though, this year, blame others as I passed over the duties to Neil Chandler as Tech17 lead and Chris Dunscombe as Database lead). I also grew up 30km North of Birmingham, in a little city called Lichfield – but to be honest that does not help as my family was from Nottingham and we went there to shop.

So with my many years of experience, here is my Survival Guide to UKOUG Conference in Birmingham.

Oh, and just to be clear. I sometimes say “we” when talking about organising the conference. It is habit. I was not involved this year.

impdp logtime=all metrics=y and 12cR2 parallel metadata

A quick post to show why you should always use LOGTIME=ALL METRICS=Y when using Data Pump. Just look at an example showing the timestamp in front of each line and a message about each task completed by the worker:

15-NOV-17 11:48:32.305: W-5 Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
15-NOV-17 11:48:34.439: W-13 Completed 28 PROCACT_SCHEMA objects in 1 seconds
15-NOV-17 11:48:34.439: W-13 Completed by worker 1 28 PROCACT_SCHEMA objects in 1 seconds
15-NOV-17 11:48:34.440: W-13 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
15-NOV-17 11:48:35.472: W-17 Startup took 70 seconds
15-NOV-17 11:48:35.596: W-18 Startup took 70 seconds
15-NOV-17 11:48:35.719: W-20 Startup took 70 seconds
15-NOV-17 11:48:35.841: W-19 Startup took 70 seconds
15-NOV-17 11:48:43.520: W-5 Completed 1714 TABLE objects in 7 seconds

Database Scoped Configurations

As most of you know, I’m working to update all of my SQL Server knowledge, which hasn’t gone as deep as I would like since SQL Server 2012.

So there are a couple things we’re going to focus on today, first of which is the top in the list which I’ve highlighted, Legacy Cardinality Estimation:

nVision Performance Tuning: 10. Maintaining Statistics on Non-Partitioned Tree Selector Tables

This blog post is part of a series that discusses how to get optimal performance from PeopleSoft nVision reporting as used in General Ledger.

If you do not partition your selector tables, and if you predominantly use static selectors then you can maintain the statistics on the selector table as the new static selectors are created or updated.  PeopleTools table PSTREESELCTL is used to track static selectors.  It is keyed by selector number.  A row is inserted when a new selector is created, and the version number is updated when an existing selector is re-extracted.  Therefore, a trigger on this table can be used to submit a job to refresh the statistics on the tree selector.

SPD State Does Not Change If Adaptive Statistics Are Disabled

The aim of this post is to point out an issue (bug?) that I recently discovered. But, before talking about it, I need to go through a rather long introduction about the state of SQL plan directive (SPD).

As of version, an SPD has two state information. You can see both of them through the CDB/DBA_SQL_PLAN_DIRECTIVES data dictionary views:

Tech 2017 Agenda

As usual it’s hard to pick a personal agenda from the wealth of content available for the UKOUG annual conference, but this is my starting list:


13:40 – 14:25 Roger MacNicol: “My query plan says Table Access Full: what happens next ?”

14:40 – 15:30 Gert Poel: “Smart Database Development with PL/SQL and Oracle REST Data Services”

16:10 – 17:00 Kellyn Pot’Vin: “Oracle vs. SQL Server – the War of the Indexes”

17:10 – 18:00 Luiza Koziel: “How to improve your presentations AK the Tool is Just a Tool… Learn how to use it for a Good Cause”


9:00 – 9:50  ME! I launch (one stream of) the conference with “Index Statistics and Column Groups”

11:35 – 13:25 Ivica Arsov: “Parallel Execution with Oracle 12c” … there may be some overlap with my 2nd presentation

Why INSERT causes TX – row lock contention or TM – contention in #Oracle 600w,