Hi,
Trying to get the opinion of a wider audience. We have had a new remote DBA firm come into our environment, and they have made it known that they consider SQL Profiles and SQL Baselines bandaids or quick fixes for immediate prod performance issues and the permanent fix is to send the query to “Development” for any needed index creations or re-structuring the query so that it hits existing indexes (or whatever else is required to guarantee performance). They seem to think that sql profiles become out of date too quickly and if the query changes, sql profiles and baselines are no longer useful, justifies their position.
Now, a little bit about our environment. We run Oracle on-prem E-Business that has been heavily customized (~3500 tables in our custom schema, most of our users don't access the native EBS, the access custom angular/spring front-ends). Our DB is 16TB (3.5TB in the custom schema). Our shared pool on any given day has ~80k EBS related sql in it. Of those, ~30k hit custom tables. Being an ERP, most SQL are fairly complex (avg sql plan lines is around 120).
Traditionally, when we have had a performance issue, we would check for plan regressions. If a regression was found, we would baseline the sql. If no obvious regression, we would run the tuning advisor to see if any sql profiles are available. If SQL Profile available, implement it. If no SQL Profile or SQL Profile does not help, then, if it's a seeded E-Business query, we would send to functional team to log an SR, if custom, we, the DBAs, would try to do a deep dive on the query (SQL Monitor, etc…) to pinpoint sections where the query is spending it's time or where obvious cardinality estimates are way off and then finally send to Development (along with any obvious recommendations we may have come up with, if any).
The new remote DBA firm seems to follow: Are end users complaining about the system or specific query. If yes, and if a specific SQL is identified, check for baseline/sql profile, and then send to development. If no end user is complaining, send to development. After development fixes, remove baseline/sql profile. They seem to think that developers adding additional indexes or restructuring the sql can resolve most problems.
Now, obviously, I know that some companies are organized such that all performance tuning is done by the developers, or that developers have associated DBA's to help them tune during development, but I've seen a number of companies, including my current one, that aren't organized that way. With us, the developers code their queries and unit test, QA does more extensive testing, including performance, but no load testing (because load testing an EBS environment is both difficult and costly), then code goes to Production. The no load testing has bitten us a couple of times, but so far, mgmt has not been willing to spend the money and time on it.
Mostly Oracle does a pretty good job, considering the complexity of the queries, but with ~30k custom queries, we usually get 1 -2 plan regressions a month that actual become visible to either our monitoring systems or end users (this is not including the probably 100s that happen that are small enough that they aren't noticed). 90% of the time, those regressions have been solved with baselines or profiles. Very rarely do we find an actual candidate for a new index (our system has been live since 2007 and is already pretty indexed up the wazoo…). Every once in a while, we do get a case where a SQL has actually changed such that any baselines/sql profiles are no longer valid, but we consider that the price of doing business…. (though in typing this up, I'm considering that we should investigate if having the developers add comments in their code if we had to implement a baseline/profile just so that if they change the query, they know to pay special attention).
The new remote firm hasn't brought up their new philosophy to the development team yet, and I'm not looking forward to that, but I want to know what other companies do for these situations and what their opinions on SQL Profiles/Baselines are?
Thanks
Wayne