Oakies Blog Aggregator

dbakevlar's picture

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:

Sunday, Dec. 3rd  at 4pm in the Media Suite

#0000ff;">#0000ff;" href="http://supersunday17.ukoug.org/default.asp?p=16256&dlgact=shwprs&prs_prsid=13924&day_dayid=117" target="_blank" rel="noopener">Oracle vs. SQL Server- The war of the Indices

The second is in the standard schedule:

https://i0.wp.com/dbakevlar.com/wp-content/uploads/2017/11/tech17_2.png?... 300w, https://i0.wp.com/dbakevlar.com/wp-content/uploads/2017/11/tech17_2.png?... 768w, https://i0.wp.com/dbakevlar.com/wp-content/uploads/2017/11/tech17_2.png?... 1122w" sizes="(max-width: 379px) 100vw, 379px" data-recalc-dims="1" />

Monday, Dec. 4th at  12:35pm in Hall 1

#0000ff;">#0000ff;" href="http://tech17.ukoug.org/default.asp?p=16630&dlgact=shwprs&prs_prsid=13007&day_dayid=116" target="_blank" rel="noopener">Oracle Optimized- AWR, ASH & ADDM Features

I also signed up to chair a few sessions.  It’s important to give back to the community at large, so I was happy that I had some time to chair the following three sessions:

Title First name Surname Company name Title
Mr Guido Schmutz Trivadis Kafka Connect & Kafka Streams – Powerful Ecosystem Around the Kafka Core
Mr Mike Durran Oracle Oracle Data Visualization – The Latest & Greatest
Dr Roger MacNicol Oracle Using Oracle Columnar Technologies Across the Information Lifecycle

I promise I’ll only help and won’t heckle anyone… </p />
    <div class=»

davidkurtz's picture

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.
The problem is discussed in Oracle support note E-NV: nVision Performance using Microsoft Office 2010 and above (Doc ID 1537491.1).  Essentially, Excel 2010 upwards only runs single threaded.  Only one Excel nVision process that is not waiting for a database call to return can run concurrently on any one Windows server at any one time.  If you want to be able to run 10 concurrent nVision reports you would need to run one on each of 10 process schedulers, on 10 different windows servers.
From PT8.54, OpenXML is the default and preferred engine for executing nVision report on the process scheduler.  This uses a different PeopleSoft executable (PSNVSSRV).  It does not suffer from the single-threading problem so multiple processes can run concurrently.  It can also be run on non-Windows environments.
However, there are some limitations with OpenXML:

  • Excel macros are ignored during report generation, although macros can be put into a layout that will execute when the generated report is subsequently opened in Excel.
  • There are problems with nPlosion.  
  • Any print area set in the layout is lost.
  • When rerunning nVision to file any pre-existing file is not overwritten.

Therefore, it may be necessary to continue to run some nVision reports on Excel.  This would require:

  • Separate process schedulers configured to run Excel rather than OpenXML on each available Windows server.  Excel is selected by setting the variable Excel Automation = 1, in the nVision section of the process scheduler configuration file (psprcs.cfg).  
  • A new Excel nVision process type should be configured to run specific layouts or reportbooks on Excel.  
  • That new process type should only run on these additional process schedulers.  It should have a maximum concurrence of 1, or at most 2, on each Process Scheduler.  These schedulers should be configured to run this new process type (and a single Application Engine so that the purge process can run).

tanelpoder's picture

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:

Just like last time (AOT 2.5 about 2 years ago!), the attendees will get downloadable video recordings after the sessions for personal use! So, no crappy streaming with 14-day expiry date, you can download the video MP4 files straight to your computer or tablet and keep for your use forever!

If you sign up early and can’t wait until end of January, I can send the registered attendees most of the previous AOT 2.5 video recordings upfront, so you’d be ready for action in the live class :)

I also have a Youtube channel (that you may have missed), there are a couple of introductory videos about how I set up my environment & use some key scripts available now:

I plan to start posting some more Oracle/Linux/Hadoop stuff in the Youtube channel, but this is quite likely the last AOT class that I do, so see you soon! ;-)

Update: Here are a couple of teaser screenshots of the stuff you’ll be seeing a lot ;-)

https://i2.wp.com/blog.tanelpoder.com/wp-content/uploads/2017/11/AOT-met... 300w, https://i2.wp.com/blog.tanelpoder.com/wp-content/uploads/2017/11/AOT-met... 768w, https://i2.wp.com/blog.tanelpoder.com/wp-content/uploads/2017/11/AOT-met... 50w, https://i2.wp.com/blog.tanelpoder.com/wp-content/uploads/2017/11/AOT-met... 1600w" sizes="(max-width: 800px) 100vw, 800px" data-recalc-dims="1" />https://i2.wp.com/blog.tanelpoder.com/wp-content/uploads/2017/11/AOT-pro... 300w, https://i2.wp.com/blog.tanelpoder.com/wp-content/uploads/2017/11/AOT-pro... 768w, https://i2.wp.com/blog.tanelpoder.com/wp-content/uploads/2017/11/AOT-pro... 50w, https://i2.wp.com/blog.tanelpoder.com/wp-content/uploads/2017/11/AOT-pro... 1600w" sizes="(max-width: 800px) 100vw, 800px" data-recalc-dims="1" />

NB! I am running one more Advanced Oracle Troubleshooting training in 2018! You can attend the live online training and can download personal video recordings too. The Part 1 starts on 29th January 2018 - sign up here!

mwidlake's picture

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.

Getting There


A lot of people arrive by train, coming into the main train station, Birmingham New Street. This used to be a dark, horrible, Stygian nightmare of a train station but now it is bright, clean and has a nice shopping centre above it. It is just a 10 minute walk up the road called New Street, through the German Market and then bearing left-ish (probably past the Birmingham Town Hall) towards Broad Street where the conference centre (the Birmingham ICC) and most hotels people stay in are clustered. There are underpasses or bridges to take you over the horribly busy road that is in the way.

There are a few other train stations in Birmingham city centre (Moor Street, Snow Hill and Five Ways ) but I have never used them, sorry. Apparently you can walk to the ICC from them. Or get a taxi, but as it is Christmas and Broad Street is very close to the shopping areas, expect a slow taxi journey.

It is probably too late for this advice, but buy UK train tickets as soon in advance as possible. Train fares in the UK are stupidly high (for a crap service) and the sooner you book, the less eye-wateringly expensive it is. The UK is about the only European country where the trains are run privately for profit rather than by the government, which is why they are so “efficient and good value” here. Not.


Again, a bit late for advice on your actual plane route but if you come into Birmingham international get the train in. The service is regular and quick. Taxies, especially at this festive period, are likely to be slow and expensive. I have never tried Uber in Birmingham but UK Uber is in a lot of trouble at the moment as they have been accused of not vetting their drives much.
If you are coming into London, again get the train. From London Euston to Birmingham NEW STREET (not Birmingham International, that is the airport). It takes about 90 minutes.


If you are driving into Birmingham then in some ways it is good, in some ways it is terrible. For example, the A38M gets you deep into Birmingham pretty quickly and smoothly but, like a lot of cities, at times the route is a bit confused and, as it is Christmas, the centre of Birmingham itself will be hell to drive around. Once you get near the centre the traffic will just stop.
I advise you allow for at least half an hour or more extra for driving into or out of Birmingham than you expect. If you arrive on Saturday or on Sunday afternoon add an hour.
If you are leaving by car on Wednesday afternoon, I suggest you consider having a final coffee or light meal in Birmingham before you go. Trying to drive out of the Broad Street area between 4pm and 6pm is something I do not want to have to do again in my life. One year (when it snowed a bit) it took me 2 hours just to get onto the M42 that circles the East and South of Birmingham.

If your hotel has reserved parking, great. If not then good luck. Check the UKOUG web site for suggested car parks, plan which car park you go for and remember, Saturday and Sunday all public car parks will be very, very busy. I had such a nightmare last year when I arrived on Saturday that I am parking 20 miles away and coming in by train!

Once There…

Once you are at the ICC you won’t need public transport – unless you booked a hotel a distance from the conference, in which case you are on your own. Or you are local and coming in and out each day. In which case you know the place and you are on your own.

You can walk to enough bars and restaurants as you could want, taxies in the evening for a couple of miles are not too bad. Access for wheelchairs is pretty good in the UK, there is step-free access to the ICC. I have a nasty feeling you cannot go across the canal to the main bars & restaurants from the back entrance of the ICC by wheelchair, but you can go via Broad Street.


Food & Drink

The UKOUG conference is great for breadth and depth of content. It is not so good on the catering front…

Coffee & Tea

It seems like a small thing, but getting a cup of tea or a coffee at the UKOUG conference can be a bit of a trial. Unlike other conferences, such drinks are not always available. I know, it’s mad. I’ve tried to argue about this when I’ve been involved in organising the conference but the ICC charge silly amounts for constant provisions of these conference basics and the UKOUG want to use the provision of drink and food to drive footfall through the exhibition. When it is the allotted time for Tea/Coffee, the queues are of course bad. And the ICC staff take some sort of evil delight by directing you to a different queue. Which turns out to be just as large or, occasionally, not even open yet. (They do this at lunch time too).

To make things worse, providing drinks as an exhibitor seems to be a real challenge too. I looked into having my own stand a couple of years back, with decent coffee. To provide anything more than the odd jug of real coffee would have cost me a fortune, if even possible.

I would advise you just hang about a bit. Maybe get a biscuit before someone who is trying to get a week’s calories from just the conference food hoovers them all up. Then get a coffee/tea once the rush dies down.

There are water coolers around, so you can get water. If it does not run out. Don’t drink water from the bathrooms, it is not “potable” unless otherwise stated – it will be going through a tank  which may or may not have a dead pigeon in it.

However, on the floor below the main conference reception area (so technically outside the conference) there is a coffee company that will sell you something approaching coffee or tea. And if you go out of the “back” of the ICC  and over the canal, there are some coffee places out there.

I suggest you get a bottle of water and keep it filled and with you. The conference rooms can be warm and if you “enjoyed yourself” the night before you could be dehydrated from that too. I used to get tired and suffer headaches until I realised I was constantly dehydrated.

Conference Food

The lunch will be OK. Probably. It varies from year to year. Last year we had a buffet as opposed to a “real” meal and, I have to say, it was a hell of a lot better than the slime provided the year before. If you are vegetarian or have allergies I hope you said so when you registered. In any respect, let your needs be known to the catering staff or the UKOUG staff. If the member of catering staff you are asking does not understand you, go ask someone else. It’s just not worth the risk.

The food provided on Tuesday evening is OK and, if you are determined, you could eat enough to count as a meal. But most people will go outside the ICC for evening meals and drinks. I would recommend you do so.

Drinking and Eating in Birmingham

The conference is in central Birmingham. There are lots of options. If you go out the “back” of the ICC (the opposite side to the main entrance, where there will be some sort of winter fair) you will go over the canal and find many plastic bars and restaurants. You know, All Bar One, Wagamama, Pizza Express, Slug and Lettuce. If that is your thing, you will be happy.

The conference centre is on Broad Street. If you come out the main entrance where the fair is, go right and then once at the road, go “back” along the side of the conference centre. If you come out the back entrance, head left (by the canal or once you have reached the bars). If you are not on Broad Street within 1 minute, you went the wrong left. Here there are more bars, restaurants, even an executive gentlemans dancing club. I have no idea how entertaining  executive gentlemen getting groovy is, I never popped in. There is a lot of choice of types of food up and down Broad Street, though the national cuisine of curry is most in evident. Please do not ask me to recommend anywhere, I loose track of where has been good. Use an App.

I would suggest you do NOT go out in a gang of 20 and expect to get seated. Go out in a gang of 4 or 6, maybe 8 and you will fair better. If you want a bigger group, use your search app of choice and book somewhere.

I would also suggest you be willing to walk more than 5 minutes, it really increases your choice and places are less busy. There are some nice Chinese and Thai places a little further out and lots of other food choices. For drinking I like the “Wellington” pub and there are a couple of other real-ale places scattered about but near by.  There is also the *speaker’s pub* but I won’t tell you where that is as you will all go there and I won’t be able to get in. I’ll happily take you there though. Let’s just say it is much improved since it was renovated and stopped smelling or urine.

Most people end up in the bars and restaurants near the ICC but if you walk back towards New Street (not all the way to New Street!) you will find the German Market where you can get Gluhwein and other bars with more character.

As the years have gone on, I’ve been more likely to pop out from the conference to get lunch or have a quiet coffee. It helps me keep fresh for the rest of the talks I go to.


Alcohol is of course utterly optional. But it seems a lot of British people and international conference presenters opt for it. There should be non-alcoholic options at all UKOUG organised social events. If there is not, feel free to complain like hell as it has been an issue once or twice and should not be.

Given you do wish to partake of the odd alcoholic beverage I feel I would like to offer some advice. That last pint in the hotel bar before you go to bed? Why is it always that one that is bad? I try and keep things in moderation until the last night at least Trying to concentrate on index internals whilst the Hangover Pixies bang hammers on the inside of your skull is not easy.

As a general rule, alcohol is not available in the conference during sessions.

As a general rule, after all sessions are finished, alcohol seems to be appreciated by many.

If you are not English (and in particular if you are from the US) you should be aware that our pints are a bit bigger than a US pint or a half litre. Also, though I know that the US have finally got their heads around “craft beer”, English beer (especially Real Ale) tends to be a bit more flavoursome and stronger than what many people thing of as beer, namely lager. You can get lager from most of the bars around the ICC and nasty, bland pap it is too. I’ll be holding out for beer that is brown, above 5 degrees C and is not mildly fizzy. Preferably delivered from the keg via hand pump, not just pressure-squirted out of a tap.


The Conference

Firstly, I’ll admit my prejudice. Other conferences have their own selling points, I actually personally prefer a few of the smaller national conferences, but for breadth of content and the technical validity of what is on offer, I don’t think you can beat the UKOUG conference. DOAG is on a par and is a little larger, but I think is less relaxed than the UK experience. Oracle Open World is a massive, ball-achingly on-message sales and marketing event that frankly I can live without. The UKOUG conference is independent of Oracle and, though we love having their presence and speakers, you will get talks that are not all “Oracle is the best”. You even sometimes get Oracle corporation speakers letting slip the occasional negative word or admission that something could be better. We put things in the water to make them more honest. Yeast, barley and hops, mostly.


The UKOUG conference is big. Tech17 has something like 12 concurrent streams, 3 or 4 of which are database, a similar number across development and middleware. And you can move between Tech17, Apps17 and JDE17 as you wish now.

There will be times you want to see more than 1 session on at the same time. Sorry, this is impossible to avoid for the organisers. I’ve spent days in darkened rooms working on the agenda in the past. We try to make sure that talks in the same technical area (e.g. Database) are not on similar topics, we try not to put popular talks/presenters in small rooms or against each other. We try to look across the agenda so that a database-based talk on PL/SQL is not clashing on a PL/SQL talk about database management. But it is impossible. We organisers make mistakes or we simply do not see what is obvious in retrospect. Add to that short-notice changes in speaker availability and other run-time issues, the planning is not ideal.

But the main reasons we get such clashes is that:
(a) We have no control over what you lot are interested in. You might be passionate about APEX and database performance, or in-memory and Java. There are too many variables to plan to suit everyone
(b) There are so many good talks submitted by known speakers we could pack the event with only known, established speakers that you all want to see. And we don’t as we want to encourage new speakers and new topic areas.

So, when there is a clash, please try to be mellow and just accept that the UKOUG put on SO MUCH good content you are going to have to miss something.

And for when there is nothing at all you want to see? Sorry, it will probably happen too, it’s called random variation. Read below for suggestions.

Try to plan your day and what sessions you are going to. I have a piece of paper or the full agenda and I put big circles around the talks I intend to go to, so that I don’t have to keep thinking about this stuff as my head fills with new information.

Go to one or two sessions outside “your” area. It’s good to expand your viewpoint. Some of the best, most useful talks are ones I had to go to as I was chairing them. So now I throw a couple of oddities in each year. This is of course an ideal thing to do if you hit a point where there is no talk you really want to see. Rather than go see a talk you have seen before or a speaker who is well known, go see a talk on something you know nothing about.

HAVE A BREAK. If you feel your head is full or you cannot concentrate anymore, skip a session and chill. Have a coffee. Chat to people. When I first started coming to UKOUG conference I would go to 7 or 8 talks in a row. I did not really remember the last couple from day 1 or 2, or most of them from day 3. Because I was too tired to process new information anymore. Now I take the odd session out and, over all, I learn more. Pace yourself.

GIVE FEEDBACK! You buggers are getting worse and worse each year for filling in the feedback forms. I know, you all think you will do it online later. You won’t. You never do. I know, I’ve been reviewing the feedback for years. Fill in that paper form. And be honest. Don’t give everyone 5 or 6 for everything, apart from Derrick who was crap and you give 1 or 2 for everything. Of course, any talk I do (I’m not doing one this year) is 6’s across the board. But use the whole breadth of the scoring. (update, see the comment section).

Those feedback scores not only help the presenters personally, we use them when planning who gets to talk in future years. We really wish you would just tell us what you did and did not like. Please.


Speakers are there as they want to be there. Well, most of them. So feel free to go talk to them when you see them around and about. Obviously don’t rudely butt in when they are deep in conversation with someone else, but of course you can chat to them in a queue for bad coffee or when you bump into them in the exhibitors hall. Oh, if you are between them and the nearest loo and they have a slightly determined look in their eye – leave them alone. And don’t follow them in! Yes, I had that once. I did not want to talk to that gentleman about Index Organised Tables right at that moment.

In my opinion, the worst time to try to talk to a speaker is… just after they have spoken! They need to get out of the way of the next speaker, you might be one of 4 or 5 people vying for attention and (true for me at least) often just after presenting you hit a bit of a lull in cognitive ability. I actually don’t tend to go to a session after I have presented as I know I won’t concentrate. So do ask your question, but ask it a little later when you see them about.

Oh, and you know those Oracle Hero Names? I’ll let you in on a big secret. They are just people, like you are. Obviously smarter and more handsome/pretty than you, but just people. Do you object if someone chats to you? No. So chat to them. And you don’t need to have a question you can, like, just talk to them like they are normal people. Apart from Tim Hall, he is strange. (Joke!)


Conference is anything but just sessions. You are surrounded by people who all have an interest in Oracle, many of whom have a shared interest with you. Sessions are great but often the best stuff comes from conversations with people. It can be hard to talk to people you don’t know, but then those people you don’t know often feel the same. Come and talk to me, I hardly ever tell people to go away.

There are social events Monday and Tuesday nights. Come to them. Relax. Drink Whisky (Monday tech Community Networking). Or water. And chat.

If you get into a good discussion with someone and a new session is about to start, well maybe change your plan and go to the same session as them. Or. Don’t go to a session. If you have found someone who has had the same slow-death-by-frustration as you with feature X or implementing Payroll version 666 then spending half an hour with them might be the best thing you both get out of the conference. It’s what the Oracle Community is all about.

Odd Stuff


I have not noticed queues outside the ladies loos. This is because IT is still a distressingly  male-dominated sphere, most conferences even more so. Though UKOUG try to encourage a better balance, one benefit for the ladies is no queuing for the loo. Men however, may need to queue! The problem is, several talks will finish  at the same time and those in need head off to the loos. The same ones as everyone else, right next to the hall you were in. Take a tip from me, if your bladder can hold on for 95 seconds, go and find a loo away from the hall. The ones down by halls 10 and 11 are quieter, it is worth seeing if there are loos by a hall that is not in use. I have my “favourite” loo, where it is always quiet. I’m not telling you where it is.

Wandering Around the Area – Safety

You will be safe in the vicinity of the ICC, Broad Street, down towards the centre of Birmingham. Of course, be sensible! Don’t wander down some dark alley on your own and don’t tell a bunch of Youth they look stupid with their trousers around their assholes (still a fashion thing for some here in the UK, I don’t get it). But you and your mate(s) will be safe wandering around where there are bars & restaurants. If you suddenly realise you are surrounded by only dark, lightless buildings – you are probably STILL safe. But maybe go back towards the bars. If I was a lady on my own, I would like to think I would not feel threatened on the main roads and thoroughfares. But I’ve never been a lady on my own. Several of those ladies I know who frequent the conference are at ease walking back to their hotel on their own. Though as a gentleman I find it tricky to let them do it, but that’s my latent sexism coming out.


Birmingham in Winter is world renowned for it’s warm climate and sunshine. Or more specifically, for how it is NOT warm and is only rarely sunny. As I type this I am looking at the long-term weather forecast and see no snow is predicted. But last time I told people there would be no snow – it snowed. (We do not get snow like say Canada or Norway gets snow. We get an inch or two that confuses everyone driving a car.)

It will be cold, I can be absolutely sure of that. A few degrees centigrade above freezing in the evenings, with a breeze. You will need a coat, gloves and a hat will help. If you are from America, it will be about 40f.  You will still need a coat, hat and gloves. Maybe throw in a scarf.

It will almost certainly rain at some point.

German Market and Shopping

I should not be encouraging you to leave the conference for a period of time, but in the evening the German Market and lots of shops are open. I personally don’t bother with the German Market anymore as I’ve been there soooo often (and, as I said on social media recently, you only need so many wooden toys and sausage in your life). But it is well worth a visit if you have not been before, or at least not for a while.

I know some people who include a mooch around the shops as part of their conference experience.

Coats and Luggage

It is warm in the ICC and blinking cold outside in Birmingham in December (see weather). So you will probably want to drop off your coat and maybe your luggage. There is a cloakroom in the ground floor of the ICC where you can do so. They will charge you a British pound or two. People complain about this charge. A lot.

You work in I.T, you are paid well, you do not want for money to pay for food and water. Just pay the damned pound will you? Take it off whatever charities you contribute to if it bothers you that much. Just don’t keep complaining at me about it.

If you stayed in a local hotel, they should be willing to hold on to your luggage for you on the last day. Depending on which hotel you are in this might not be convenient of course. If you do, how many of you will tip them more than a pound for doing so?

Franck Pachot's picture

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
15-NOV-17 11:48:43.520: W-5 Completed by worker 1 39 TABLE objects in 2 seconds
15-NOV-17 11:48:43.520: W-5 Completed by worker 2 113 TABLE objects in 7 seconds
15-NOV-17 11:48:43.520: W-5 Completed by worker 3 85 TABLE objects in 6 seconds
15-NOV-17 11:48:43.520: W-5 Completed by worker 4 111 TABLE objects in 6 seconds
15-NOV-17 11:48:43.520: W-5 Completed by worker 5 25 TABLE objects in 1 seconds
15-NOV-17 11:48:43.520: W-5 Completed by worker 6 113 TABLE objects in 7 seconds
15-NOV-17 11:48:43.520: W-5 Completed by worker 7 113 TABLE objects in 7 seconds
15-NOV-17 11:48:43.520: W-5 Completed by worker 8 111 TABLE objects in 6 seconds
15-NOV-17 11:48:43.520: W-5 Completed by worker 9 89 TABLE objects in 5 seconds
15-NOV-17 11:48:43.520: W-5 Completed by worker 10 74 TABLE objects in 4 seconds
15-NOV-17 11:48:43.520: W-5 Completed by worker 11 113 TABLE objects in 7 seconds
15-NOV-17 11:48:43.520: W-5 Completed by worker 12 113 TABLE objects in 7 seconds
15-NOV-17 11:48:43.520: W-5 Completed by worker 13 34 TABLE objects in 2 seconds
15-NOV-17 11:48:43.520: W-5 Completed by worker 14 111 TABLE objects in 6 seconds
15-NOV-17 11:48:43.520: W-5 Completed by worker 15 108 TABLE objects in 7 seconds
15-NOV-17 11:48:43.520: W-5 Completed by worker 16 90 TABLE objects in 4 seconds
15-NOV-17 11:48:43.520: W-5 Completed by worker 17 82 TABLE objects in 4 seconds
15-NOV-17 11:48:43.520: W-5 Completed by worker 18 40 TABLE objects in 3 seconds
15-NOV-17 11:48:43.520: W-5 Completed by worker 19 97 TABLE objects in 6 seconds
15-NOV-17 11:48:43.520: W-5 Completed by worker 20 53 TABLE objects in 3 seconds
15-NOV-17 11:48:44.266: W-1 Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

Here, I was running an import with PARALLEL=20 and I can see exactly how many tables were processed by each worker. You see it is ‘TABLE’ and not ‘TABLE_DATA’ which is the proof that 12cR2 can import metadata in parallel.

I see no reason not to use LOGTIME=ALL METRICS=Y always and you will be happy to have this detail if something goes wrong.


Cet article impdp logtime=all metrics=y and 12cR2 parallel metadata est apparu en premier sur Blog dbi services.

dbakevlar's picture

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:

https://i1.wp.com/dbakevlar.com/wp-content/uploads/2017/11/sql2017_1.png... 300w, https://i1.wp.com/dbakevlar.com/wp-content/uploads/2017/11/sql2017_1.png... 768w" sizes="(max-width: 650px) 100vw, 650px" data-recalc-dims="1" />

By default, this value is turned off and as the optimizer is essential to database performance and in each database platform, handled differently, it’s worth starting to disect.  The Database Scoped Configuration settings, which are database specific, and can be accessed via the SSMS, right clicking on the database and left clicking on Database Properties and on Options.

The second is to query sys.database_scoped_configurations:

SELECT name, value 
 FROM sys.database_scoped_configurations;

The values from here will be displayed as the numerical values for ON=1 and OFF=0

https://i0.wp.com/dbakevlar.com/wp-content/uploads/2017/11/sql2017_2.png... 300w" sizes="(max-width: 280px) 100vw, 280px" data-recalc-dims="1" />

Only the MAXDOP, (Maximum Degree of Parallelism) is a numerical value vs. On/Off.  This is proven after I save my update to the MAX DOP shown at the properties for the database that will immediately change the value of DOP allowed from none to four.  Needless to say, this was just to prove the update and I reverted it to 0 vs. a distinct value.

https://i1.wp.com/dbakevlar.com/wp-content/uploads/2017/11/sql2017_3.png... 300w" sizes="(max-width: 298px) 100vw, 298px" data-recalc-dims="1" />

The Legacy_Cardinality_Estimation is part of a larger feature that controls feature versioning choices for the optimizer in SQL Server.

Legacy Cardinality Estimation

Oracle DBAs have used the CARDINALITY hint for some time and it should be understood that this may appear to be similar, but is actually quite different.  As hinting in TSQL is a bit different than PL/SQL, we can compare similar queries to assist:

SELECT CustomerId, OrderAddedDate 
FROM OrderTable 
WHERE OrderAddedDate >= '2016-05-01';

Where you might first mistake the CE hint for the following CARDINALITY hint in Oracle:


This would be incorrect and the closest hint in Oracle to SQL Server’s legacy CE hint would be the optimizer feature hint:

SELECT /*+ optimizer_features_enable('') */ ORD.CUSTOMER_ID, ORD.ORDER_DATE FROM ORDERS ORD 
WHERE ORD.ORDER_DATE >= '2016-05-01';

If you’re wondering why I chose a 9i version to force the optimizer to, keep reading and you’ll come to understand.

To check the compatibility version, as well as setting it per database in SQL Server, we would perform the following:

SELECT ServerProperty('ProductVersion');    

SELECT d.name, d.compatibility_level FROM sys.databases AS d 
WHERE d.name = '';

To update the compatibility version to the latest version to match our SQL Server vNext database engine, we’d run the following command:


Let’s assume we just upgraded our database to 140, (SQL Server 2017) and are experiencing some serious performance issues that didn’t appear during testing.  We could choose to set the compatibility level to 130, (SQL Server 2016) to temporarily address the performance problem, allowing the optimizer to use previous version features, while we troubleshoot the issue.


Now database engine and compatibility is an odd thing, just like in  Oracle.  To understand the versions that are out there, here’s a handy reference:

https://i2.wp.com/dbakevlar.com/wp-content/uploads/2017/11/sqlserver_ver... 236w" sizes="(max-width: 436px) 100vw, 436px" data-recalc-dims="1" />

Now you will notice that their isn’t a value for SQL Server 7 when setting compatibility.  Even though 70 might be the version value, the ability to set back to it requires a different answer to the fix than you see for newer versions.  First of all, if you’re still forcing compatibility down to version 7, we need to have a serious talk, but if you need to force compatibility back to version 7, this is where we get into crazy land… </p />
    <div class=»

davidkurtz's picture

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.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">CREATE OR REPLACE TRIGGER sysadm.pstreeselector_stats
BEFORE INSERT OR UPDATE ON sysadm.pstreeselctl
l_jobno NUMBER;
l_cmd VARCHAR2(1000);
l_table_name VARCHAR2(18);
l_suffix VARCHAR2(2);
l_table_name := 'PSTREESELECT'||LTRIM(TO_CHAR(:new.length,'00'));
l_suffix := SUBSTR(l_table_name,-2);
l_cmd := 'dbms_stats.gather_table_stats(ownname=>user,tabname=>'''||l_table_name||''',force=>TRUE);'

When all the selectors exist in a single segment, the optimizer will not correctly calculate the cardinality of the expressions in the nVision queries on the tree selector tables.  Therefore, I have found it necessary to manually set the density on the range columns to 1 to get the effective execution plans at least some of the time.
N.B. This trigger should not be used if the selector tables are partitioned.

Chris Antognini's picture

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:

  • The STATE column, which takes the values USABLE and SUPERSEDED, informs us on whether the query optimizer actively uses the SPD.
  • The NOTES column contains an XML fragment that, among other information, provide the INTERNAL_STATE element. This element is set to either NEW, MISSING_STATS, PERMANENT or HAS_STATS.

The internal state is the actual state of the SPD (and the only one available in version Oracle introduced the other one to hide some implementation details that they consider irrelevant for most users. As a result, the mapping between the two states is rather simple: except in case of redundant SPDs, the internal states NEW, MISSING_STATS and PERMANENT are mapped to USABLE; otherwise, SUPERSEDED is used.

So, for the rest of this discussion, let’s consider the internal state only. That, for simplicity, is referred to as “state”.

Depending on the state of an SPD and the database configuration, the database engine triggers different operations. And, while doing so, it can also change the state itself. For example, with a version default configuration, the lifecycle of a specific SPD can be like the following (let me stress that this is just an example, a number of different scenarios are also possible):

  • The SPD is created. Its state is set to NEW.
  • The query optimizer, while generating an execution plan, notices that a matching SPD exists. Since the SPD state is NEW, it triggers dynamic sampling and changes the SPD state to MISSING_STATS.
  • The DBMS_STATS package, while gathering object statistics, notices that the table it is working on has an associated SPD. Because of that, it creates an extension and gathers statistics for it.
  • The query optimizer, while generating an execution plan, notices that a matching SPD exist. Since the SPD state is MISSING_STATS and that an extension for it exists, it triggers dynamic sampling and changes the SPD state to HAS_STATS.
  • From now on, neither the query optimizer nor the DBMS_STATS package considers the SPD.

The essential thing I wanted to emphasize by describing such a scenario is that an SPD state is expected to change over time. And, depending on it and on the database configuration, the database engine can trigger different operations.

Now that the introduction part is over, let us focus on the issue I recently found while working for one of my customers. The particular thing about that customer (and, as a result, why I never noticed it before) is that it uses with the patch 22652097 (PROVIDE SEPARATE CONTROLS FOR ADAPTIVE PLANS AND ADAPTIVE STATISTICS FEATURES), but without the patch 21171382 (AUTO DOP COMPUTES A HIGH DOP UNNECESSARILY). If you do not know what those patches are, have a look to this blog post.

Since the customer was using OPTIMIZER_ADAPTIVE_STATISTICS set to FALSE, I expected the SPD to be created without being actually used. But, what I discovered is that while SPD do not trigger dynamic sampling, they do trigger the creation of extensions. And, even worse (and that is the real issue), they do so without changing the SPD state that remains set to NEW!

If you are interested to see an example, here is the test case I wrote to reproduce it. Note that the issue can also be reproduced with version

Finally, if you want to track this issue, refer to bug 27178368 (SPD STATE DOES NOT CHANGE IF ADAPTIVE STATISTICS IS FALSE).

Jonathan Lewis's picture

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

14:25 – 15:15 ME again! At the CBO round table — we may answer a few of the more specific questions that came in for the CBO panel.

15:25 – 16:25 Community Keynote – how could one possibly miss Maria Colgan, Connor McDonald and Chris Saxon … (maybe with Tim-Tams ? I can live in hope.)

16:55 – 17:45 Michael Salt – “An in-memory paradox – increased I/O”.  I think I know the answer, but this could teach me another way to do it wrong. (Pity to miss Richard Foote on AWRs, though)

17:55 – 18:45 Franck Pachot – “From Transportable Tablespace to Pluggable Databases”



9:00 – 9:50 ME yet again! Starting the day with Maria Colgan, Nigel Bayliss, Chris Antognini and Richard Foote on the CBO Panel, with Martin Widlake and Neil Chandler doing the MC bit and making sarky comments.

10:00 – 10:50 Marcin Przepierowski: “Rman – from Beginner to Advanced”, because you’ve always got to review what you thought you knew about recovery (and backup).  It means I have to miss Bryn Llewellyn and Kamil Stawiarski arguing about how to address performance in PL/SQL

11:25 – 12:15 Lucas Jellema: “Intro to Docker Containers & the Oracle Platform – Database, Weblogic and Cloud”.

12:25 – 13:15 Mark Rittman: “How Analytics is changing the World (again)”.

14:20 – 15:05 Stew Ashton: “Meet your Match: Advanced Row Pattern Matching”

15:40 – 16:25 Martin Berger: “Escape from Exadata”.

16:40 – 17:30 Roger MacNicol: “Using Oracle Columnar Technologies across the Information Lifecycle”


9:00 – 9:45 Simon Pane: “Modernizing your DBA Scripts and Backups with the Oracle Scheduler”

10:00 – 10:45 Allan McAleavy: “Moving to an All Flash Array – Dude Where’s my Bottleneck”

11:25 – 12:15 Jason Arneil: “An introduction to Sharding”

12:25 – 13:15 My final session: “Parallel Execution” – if you come to this it’s probably a good idea to see Ivica Arsov on Monday morning as well

14:15 – 15:05 Jaromir D.B. Nemec: “Anomaly Detection in Database Workload”.


Of course I may change my mind between now and the start of the event so if you’re feeling deprived (or relieved) that I’m not going to be in your audience – or if you’re feeling pressured that I am – never mind, my agenda isn’t cast in stone and I’ll probably end up at the wrong sessions anyway because I’ve been busy talking to someone without keeping an eye on the time.



Uwe Hesse's picture

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

https://uhesse.files.wordpress.com/2015/10/helps.png?w=600&h=558 600w, https://uhesse.files.wordpress.com/2015/10/helps.png?w=150&h=140 150w" sizes="(max-width: 300px) 100vw, 300px" />

Inserts may cause enq: TX – row lock contention and enq: TM – contention under special circumstances:

Bitmap Indexes

First session inserts. Second session inserts with the same values for the indexed column. Second session experiences wait event enq: TX – row lock contention until first session commits, then proceeds without error message.

Unique Indexes

First session inserts. Second session inserts with the same values for the indexed column. Second session experiences wait event enq: TX – row lock contention until first session commits, then gets ORA-00001: unique constraint violated.

Parallel Inserts

First session inserts with parallel dml enabled. Second session inserts sequentially or in parallel doesn’t matter. Second session experiences wait event enq: TM – contention. This is also true for partitioned tables unless the partitions are explicitly referred to!

My test cases for the above scenarios have been done with and  look as follows.

Bitmap Index scenario:

SQL> grant dba to adam identified by adam;

Grant succeeded.

SQL> connect adam/adam
SQL> create table t1 (n number);

Table created.

SQL> create bitmap index b on t1(n);

Index created.

SQL> insert into t1 values(1);

1 row created.

Second session inserts the same and waits. You see the wait event in v$session then.
Unique Index

SQL> create table t2 (n number);

Table created.

SQL> alter table t2 add constraint u unique(n);

Table altered.

SQL> insert into t2 values (1);

1 row created.

Second session inserts the same and waits.
Parallel insert

SQL> create table t3 (n number);

Table created.

SQL> alter table t3 parallel;

Table altered.

SQL> alter session enable parallel dml;

Session altered.

SQL> insert into t3 select 1 from dual;

1 row created.

Second session waits for any DML.
Partitioned table parallel insert without partition clause

SQL> create table t4 (n number) partition by list (n) (partition p1 values(1),partition p2 values(2));

Table created.

SQL> alter table t4 parallel;

Table altered.

SQL> alter session enable parallel dml;

Session altered.

SQL> insert into t4 select 1 from dual;

1 row created.

Second session waits for any DML.
Non-blocking parallel insert with explicit partition clause
Same table as above, same parallel degree and parallel DML enabled, but:

SQL> insert into t4 partition (p1) select 1 from dual;

This blocks only partition p1. Second session can do any DML against p2 explicitly.

I have the feeling that this is not widely known – could actually be a good interview question: When do inserts lead to locking waits? As always: Don’t believe it, test it! </p />
    <div class=»