On SQL Developer
First of all, apologies for the long delay between posts. We've been evaluating various avenues for this cycle of hardware and software refresh in our data centres and for obvious reasons I could not make any public postings or comments that might be mis-interpreted by the various suppliers.
In these situations there is a confidentiality protocol that must be followed and simply cannot be broken. Hence the absence. We've now reached the end of the first phase. More on that front soon.
Followers of this blog would have noticed I started using Twitter a few months ago - @wizofoz2k. Mostly as a result of Paul Vallée and Yury Velikanov from Pythian shaming me to join that social site. For nearly 10 years I could not be part of the emerging "social media" phenomena, due to my military clearance mandate between 2001 and 2004.
But that is now all gone and I can participate in these sites as any normal person - and also present at conferences and such. Which I'll be doing this year at the INSYNC13 AUSOUG event here in Sydney!
But, I digress. Back to the core of this post.
It is a result of a recent interchange between yours truly and Jeff Smith, of SQLDeveloper fame.
In a nutshell:
A few years ago I asked our folks to start using SQLDeveloper as an alternative to more Toad licensing. SQLDeveloper is of course free - in fact, one of the very few pieces of excellent software unca Lazza hasn't yet managed to license to insane expensive levels! (I'd likely should not have mentioned that...)
As a result, they started to use it more and more. Now it's one of the essential pieces of software in their arsenal. Note: these are internal developers - not contractors or temps. As such, they have slightly different - and higher - status than the "duhvelopers" one hears about around the place.
They are charged with fixing problems in our inhouse apps - mostly DW/BI but also some bespoke ones. Which from time to time involves connecting to production databases as the schema owner, to fix problems that cannot be fixed any other way in a usable timeframe.
Now, the problem with using SQLDeveloper for that is: it is essentially a development tool
(Duh, name says "Developer", doesn't it?).
In that sense, it does have optimizations for development work that are not necessarily compatible with the workflow of a production environment. In particular, it does things like keeping cursors open for as long as possible, placing locks on tables and other objects to avoid them changing under the developer's feet. And so on.
All legitimate. And very, very desirable. In a development environment.
Do I need to explain what sort of an impact this type of approach might have in a production database? Imagine a long running overnight PL/SQL process being locked out of DDL or change access to a table because someone has left a SQLDeveloper window open with a SELECT FOR UPDATE cursor in it?
Yeah. That sort of thing. Dumb thing to do, yes. But, hey: s**t happens! And in the best families. A big part of my job is to ensure bad effects of those are minimized or eliminated.
So, it's not a great idea to let developers use SQLDeveloper in a production environment. Sure, sometimes they might absolutely need to - emergencies. Otherwise, no way Josay!
Now, how to get around the issue that one thing is what is desirable, the other is what folks do? Yes, of course: like any other place, our developers from time to time go into our production DW db and leave some query window open overnight in their desktops. With the result that we get some major delays in overnight processing, or even major lockouts.
6 years ago I implemented a crontab job that killed all SQLDeveloper and Toad sessions in production after 6pm. Our folks quickly learned to not do silly things like leave uncommitted windows in Toad and SQLDeveloper. And in the interest of good relations, I removed the crontab job 2.5 years ago.
But we are still getting the odd overnight processing overruns. Nothing dramatic, simply unexplained. After a long exercise in monitoring and checks by one of the app experts in our team, it became apparent the issue was caused by SQLDeveloper windows with PQ SQL "grabbing" a bunch of "pnnn" background processes Oracle uses to perform parallel queries. And of course not letting overnight processes grab as much of those as needed for smooth, fast execution.
Those "pnnn" processes don't come in infinite numbers, so we had to manage that somehow. I didn't want to go back to the 6pm "kill them all" approach: some legitimate instances of their work would be affected by that. Locking them out of access to the main schema(s) wouldn't be possible either: they are not external people and their use of those schemas *is* necessary.
Enter Twitter and Jeff!
A few days ago, I had the idea of getting in touch with him and asking what his advice would be to handle the problem. Interestingly, Vishal Gupta jumped in on the conversation as well with the same PQ problem: it seems to be more widespread than I initially thought.
Now, Jeff heads one of the few Java developer teams I trust. Mostly because I've seen how good SQLDeveloper is and how often requests for improvements get heard and acted upon - by contrast with ConFusion and the imbecile dystopia that is J2EE.
And Jeff is very open to comments and interaction in Twitter - which is indeed a breath of fresh air!
So what did we talk about, then? To cut a long story short - the whole conversation is visible in our twitter accounts - Jeff suggested I use a login trigger to control what folks can do with PQ. And that was indeed the way to handle this problem!
We already use a login trigger to set current_schema by session, thereby avoiding the need to code everything with <owner>.<object> syntax or the need for public synonyms. Child's play to query the PROGRAM column in v$session at login time and add in more session control if it's "SQL Developer" or "Toad.exe".
And of course: if one absolutely needs to use PQ, one can always enable it back in the SQLDeveloper window sessions!
Thanks heaps, Jeff!
So: we wanted to make sure no one accidentally used extensive parallel query in production through SQLDeveloper. While still allowing legitimate and controlled use of the feature and not interfering in any way with the existing processes and their access.
How to actually do that turned out to be interesting!
There is no easy way to turn off parallel query at session level, even in Oracle 11gr2! This basically adds to my long running gripe with Oracle development in not providing enhancements to the CONTEXT functionality to allow full session parameter control - not just half a dozen session info hooks.
Like, I'm not paying enough maintenance fees already so I can have one of my "desirables" considered for inclusion? Hey, I'm not even an "Ace travel points" expense - and I refuse to be one!
But, that is another story and I digress...
The only avenue open was apparently to use the db resource manager and create a special profile that would turn off parallel processing. But, here was the problem: resource profiles work by becoming attached to a login. And of course both developers and our overnight processing use the same schema login! So if we turned PQ off for one group, we'd turn it off for everyone!
Of course, that was a no go!
Alternatives? Well, all the parameters that control parallel query up to 11gr2 are essentially SYSTEM-wide ones: they cannot be changed at session level!
Clearly, we needed something more subtle. And it turns out the automatic parallel query tuning in 11gr2 was just the ticket!
Three SESSION-level parameters control that.
PARALLEL_ADAPTIVE_MULTI_USER turns on the adaptive parallel query tuning.
PARALLEL_DEGREE_POLICY controls if adaptive query tuning takes place manually - essentially controlled by the "DEGREE" of the table, same as in previous releases - or automatically, with a couple of subtleties thrown in.
PARALLEL_DEGREE_LIMIT throttles the amount of parallel query processing from a number linked to the CPU cores assigned to the db, to IO-balanced, to a fixed number > 1.
One of my long-term tasks for this year was precisely to start using the adaptive parallel query tuning in 11gr2: one of the reasons we upgraded everything to that release! And of course adaptive query tuning can be set by default to "same as always", for those less adventurous.
There are heaps of posts out there on how to control auto DOP, the adaptive query parallel tuning environment and how to avoid potential problems/bug/quirks/oddities. So I won't repeat all that jazz here, now. Google it, if it's unknown to you.
What was the end result, then?
Well, I turned on the adaptive parallel query tuning option in our acceptance test db.
Then I did set PARALLEL_DEGREE_POLICY to AUTO system wide.
PARALLEL_DEGREE_LIMIT was set to CPU system wide.
And the login trigger now looks up the PROGRAM column of v$session - for the row corresponding to the login session - and set PARALLEL_DEGREE_LIMIT to 2 if it is SQLDeveloper or Toad. Parallel 2 is OK for reasonably fast emergency queries without causing any undue extensive grabbing of parallel query service processes.
And it all worked peachy! I'm going to leave it on for a month or so and follow the performance impact on overnight processing. So far, it's been exactly what the doc ordered! No more "strange" overruns!
If it all comes together as I expect, by September we'll include this in our normal production DW database. And hopefully the problem will be gone once and for all!
I guess joining Twitter paid off after all. And thanks heaps once again to Jeff Smith for his prompt follow-up on the problem and genuine willingness to help find a solution.
If only more people at Oracle development descended from the high stratosphere they imagine they live in and did what Jeff does - *listen* to customers! Not "aces"! - things would be a lot easier for Oracle sales...
Catchyalata, folks!
In these situations there is a confidentiality protocol that must be followed and simply cannot be broken. Hence the absence. We've now reached the end of the first phase. More on that front soon.
Followers of this blog would have noticed I started using Twitter a few months ago - @wizofoz2k. Mostly as a result of Paul Vallée and Yury Velikanov from Pythian shaming me to join that social site. For nearly 10 years I could not be part of the emerging "social media" phenomena, due to my military clearance mandate between 2001 and 2004.
But that is now all gone and I can participate in these sites as any normal person - and also present at conferences and such. Which I'll be doing this year at the INSYNC13 AUSOUG event here in Sydney!
But, I digress. Back to the core of this post.
It is a result of a recent interchange between yours truly and Jeff Smith, of SQLDeveloper fame.
In a nutshell:
A few years ago I asked our folks to start using SQLDeveloper as an alternative to more Toad licensing. SQLDeveloper is of course free - in fact, one of the very few pieces of excellent software unca Lazza hasn't yet managed to license to insane expensive levels! (I'd likely should not have mentioned that...)
As a result, they started to use it more and more. Now it's one of the essential pieces of software in their arsenal. Note: these are internal developers - not contractors or temps. As such, they have slightly different - and higher - status than the "duhvelopers" one hears about around the place.
They are charged with fixing problems in our inhouse apps - mostly DW/BI but also some bespoke ones. Which from time to time involves connecting to production databases as the schema owner, to fix problems that cannot be fixed any other way in a usable timeframe.
Now, the problem with using SQLDeveloper for that is: it is essentially a development tool
(Duh, name says "Developer", doesn't it?).
In that sense, it does have optimizations for development work that are not necessarily compatible with the workflow of a production environment. In particular, it does things like keeping cursors open for as long as possible, placing locks on tables and other objects to avoid them changing under the developer's feet. And so on.
All legitimate. And very, very desirable. In a development environment.
Do I need to explain what sort of an impact this type of approach might have in a production database? Imagine a long running overnight PL/SQL process being locked out of DDL or change access to a table because someone has left a SQLDeveloper window open with a SELECT FOR UPDATE cursor in it?
Yeah. That sort of thing. Dumb thing to do, yes. But, hey: s**t happens! And in the best families. A big part of my job is to ensure bad effects of those are minimized or eliminated.
So, it's not a great idea to let developers use SQLDeveloper in a production environment. Sure, sometimes they might absolutely need to - emergencies. Otherwise, no way Josay!
Now, how to get around the issue that one thing is what is desirable, the other is what folks do? Yes, of course: like any other place, our developers from time to time go into our production DW db and leave some query window open overnight in their desktops. With the result that we get some major delays in overnight processing, or even major lockouts.
6 years ago I implemented a crontab job that killed all SQLDeveloper and Toad sessions in production after 6pm. Our folks quickly learned to not do silly things like leave uncommitted windows in Toad and SQLDeveloper. And in the interest of good relations, I removed the crontab job 2.5 years ago.
But we are still getting the odd overnight processing overruns. Nothing dramatic, simply unexplained. After a long exercise in monitoring and checks by one of the app experts in our team, it became apparent the issue was caused by SQLDeveloper windows with PQ SQL "grabbing" a bunch of "pnnn" background processes Oracle uses to perform parallel queries. And of course not letting overnight processes grab as much of those as needed for smooth, fast execution.
Those "pnnn" processes don't come in infinite numbers, so we had to manage that somehow. I didn't want to go back to the 6pm "kill them all" approach: some legitimate instances of their work would be affected by that. Locking them out of access to the main schema(s) wouldn't be possible either: they are not external people and their use of those schemas *is* necessary.
Enter Twitter and Jeff!
A few days ago, I had the idea of getting in touch with him and asking what his advice would be to handle the problem. Interestingly, Vishal Gupta jumped in on the conversation as well with the same PQ problem: it seems to be more widespread than I initially thought.
Now, Jeff heads one of the few Java developer teams I trust. Mostly because I've seen how good SQLDeveloper is and how often requests for improvements get heard and acted upon - by contrast with ConFusion and the imbecile dystopia that is J2EE.
And Jeff is very open to comments and interaction in Twitter - which is indeed a breath of fresh air!
So what did we talk about, then? To cut a long story short - the whole conversation is visible in our twitter accounts - Jeff suggested I use a login trigger to control what folks can do with PQ. And that was indeed the way to handle this problem!
We already use a login trigger to set current_schema by session, thereby avoiding the need to code everything with <owner>.<object> syntax or the need for public synonyms. Child's play to query the PROGRAM column in v$session at login time and add in more session control if it's "SQL Developer" or "Toad.exe".
And of course: if one absolutely needs to use PQ, one can always enable it back in the SQLDeveloper window sessions!
Thanks heaps, Jeff!
So: we wanted to make sure no one accidentally used extensive parallel query in production through SQLDeveloper. While still allowing legitimate and controlled use of the feature and not interfering in any way with the existing processes and their access.
How to actually do that turned out to be interesting!
There is no easy way to turn off parallel query at session level, even in Oracle 11gr2! This basically adds to my long running gripe with Oracle development in not providing enhancements to the CONTEXT functionality to allow full session parameter control - not just half a dozen session info hooks.
Like, I'm not paying enough maintenance fees already so I can have one of my "desirables" considered for inclusion? Hey, I'm not even an "Ace travel points" expense - and I refuse to be one!
But, that is another story and I digress...
The only avenue open was apparently to use the db resource manager and create a special profile that would turn off parallel processing. But, here was the problem: resource profiles work by becoming attached to a login. And of course both developers and our overnight processing use the same schema login! So if we turned PQ off for one group, we'd turn it off for everyone!
Of course, that was a no go!
Alternatives? Well, all the parameters that control parallel query up to 11gr2 are essentially SYSTEM-wide ones: they cannot be changed at session level!
Clearly, we needed something more subtle. And it turns out the automatic parallel query tuning in 11gr2 was just the ticket!
Three SESSION-level parameters control that.
PARALLEL_ADAPTIVE_MULTI_USER turns on the adaptive parallel query tuning.
PARALLEL_DEGREE_POLICY controls if adaptive query tuning takes place manually - essentially controlled by the "DEGREE" of the table, same as in previous releases - or automatically, with a couple of subtleties thrown in.
PARALLEL_DEGREE_LIMIT throttles the amount of parallel query processing from a number linked to the CPU cores assigned to the db, to IO-balanced, to a fixed number > 1.
One of my long-term tasks for this year was precisely to start using the adaptive parallel query tuning in 11gr2: one of the reasons we upgraded everything to that release! And of course adaptive query tuning can be set by default to "same as always", for those less adventurous.
There are heaps of posts out there on how to control auto DOP, the adaptive query parallel tuning environment and how to avoid potential problems/bug/quirks/oddities. So I won't repeat all that jazz here, now. Google it, if it's unknown to you.
What was the end result, then?
Well, I turned on the adaptive parallel query tuning option in our acceptance test db.
Then I did set PARALLEL_DEGREE_POLICY to AUTO system wide.
PARALLEL_DEGREE_LIMIT was set to CPU system wide.
And the login trigger now looks up the PROGRAM column of v$session - for the row corresponding to the login session - and set PARALLEL_DEGREE_LIMIT to 2 if it is SQLDeveloper or Toad. Parallel 2 is OK for reasonably fast emergency queries without causing any undue extensive grabbing of parallel query service processes.
And it all worked peachy! I'm going to leave it on for a month or so and follow the performance impact on overnight processing. So far, it's been exactly what the doc ordered! No more "strange" overruns!
If it all comes together as I expect, by September we'll include this in our normal production DW database. And hopefully the problem will be gone once and for all!
I guess joining Twitter paid off after all. And thanks heaps once again to Jeff Smith for his prompt follow-up on the problem and genuine willingness to help find a solution.
If only more people at Oracle development descended from the high stratosphere they imagine they live in and did what Jeff does - *listen* to customers! Not "aces"! - things would be a lot easier for Oracle sales...
Catchyalata, folks!
posted by Noons at 7/27/2013 10:29:00 pm
2 Comments:
"Note: that these are internal developers - not contractors or temps. As such, they have slightly different - and higher - status than the "duhvelopers" one hears about around the place."
I am now deeply offended ;)
I think the real miracle of licensing came when they threw away the idea of the licence fee for the Data Modeller and added it to SQL Developer.
Yeah, Gary. But you don't use "Jawa", do you?
Therefore, you're not a "duh"veloper!
Quite a lot of useful stuff in SQLDeveloper nowadays.
I particularly like the admin of the Apex web listener. Even though the way they packed that product makes it essentially useless...
Post a Comment
<< Home