2

I work on a relatively small development team that works with an Oracle (11g) database. It's recently been requested that all the developers be given the Advisor role so that we can utilize SQL Tuning Advisor when developing complex queries. Some have raised concerns that this may have significant performance and/or security implications, but I have not been able to find concrete answers to what these implications are.

If this role were to be given to the various members of my team, what are the major pitfalls we should be watching out for? I've included the tag for Oracle 12c as well, since we'll be upgrading to that in the near future. If there's a significant difference between the two, I'd appreciate it if it was at least pointed out.

2

As long as the developers only have that role in the development database instance, I see no problem; far better to tune now than later. Now, if your development machine has limited resources, there may be a reason to limit access. But in general, usually tuning tasks run from a few minutes to the default maximum of 30 minutes; during that time, Oracle may try parallel queries (if enabled), so that could be an issue, but you can play with the instance parameters parallel_adaptive_multi_user and parallel_degree_level to minimize the impact.

There are no changes between Oracle 11g and 12c that would affect your decision.

  • How would you change your answer if we were talking about a production database here? – McGlothlin Jul 18 '17 at 16:53
  • 1
    If you trust the developers to only run Advisor tasks when it would not affect end-users if they ran a monster query, then no changes; but I've accidentally run some tuning tasks in production that competed with a crucial batch job. Fortunately I'm the only one who knew about it until now! – Mark Stewart Jul 19 '17 at 16:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.