Hi.
Agreed Mark.
As for quasi-magic, you know the optimizer is full of that these days. :)
Adaptive Plans: There are limitations in terms of join order, but
being able to flip between nested loop and hash joins can mitigate bad
optimizer decisions.
SQL Plan Directives: Related to query expressions, not whole
statements, so a single directive can *benefit* multiple queries and
alter statistics collection to "fix" the issue. I guess this has the
potential to help ad-hoc statement, if the SQL fragment/expression has
been seen to be a problem in another statement.
I have of course also seen these confuse (me) as much as help...
Can someone say something nice about Oracle so I can continue to play
devil's advocate and change sides please? I'm a bit sick of this
side... :)
Cheers
Tim...
On Thu, Oct 5, 2017 at 10:56 AM, Mark W. Farnham <mwf@xxxxxxxx> wrote:
Mostly I agree with Tim. Please don't lose that sentiment when I point out--
that SQL Plan Baselines don't really apply to ad hoc queries. If a change
causes a tendency for ad hoc plans that did at least okay before to mostly
land in the dumper, that is a problem.
If there is a quasi-magical way to have SQL Plan Baselines or any variety of
profiles type technology apply to ad hoc (which includes generated queries
from applications that haven't exactly been run before and saved as
Baselines), then I missed that memo and would be joyfully corrected.
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Tim Hall
Sent: Thursday, October 05, 2017 10:38 AM
To: Hemant K Chitale
Cc: knecht.stefan@xxxxxxxxx; tim.evdbt@xxxxxxxxx; MacGregor, Ian A.;
oracle-l; verma.labs@xxxxxxxxx
Subject: Re: Future of Oracle DBA - Man vs Machine
Hi.
A collective response to several points raised over the last few emails...
RE Linux: "Larger changes require a reboot to pick up a new kernel."
No. That's what ksplice is for. You can apply kernel changes without a
reboot. If you run on any server on Oracle Cloud you have Ksplice by default.
You can stay fully compliant without reboot. If you run on-prem or on another
cloud you can pay support for Oracle Linux and get ksplice. Oracle have
servers that haven't rebooted for years but are fully up to date.
RE: "All it takes is for a single plan to flip"
SQL Plan Baselines stop this. Free in all editions in 18c. License change
will probably be backported to older versions. For the vast majority of cases
the problem of keeping consistent plans was solved a long time ago. The only
reason for not having consistent plans is lack of knowledge of the product,
or an unwillingness to pay for the feature, which is now free.
The Autonomous suite of database services, of which the DW one is the first,
will not suit everyone, which is why Oracle and other Cloud providers give
you multiple services, as well as the ability to run on-prem. You don't have
to use it, but it is worth considering.
The service is built on top of Exadata. Since 18c allows rolling patches of
everything, including OJVM, you can patch and stay online.
(their words not mine)
I share many of the same concerns that people here do, but I would love this
stuff to work as I'm sick of doing the bullshit tasks or having to automate
them myself. I would love someone to take them off my hands for Oracle, SQL
Server and MySQL, so I can focus on more interesting issues.
Cheers
Tim...
On Wed, Oct 4, 2017 at 11:28 PM, Hemant K Chitale <hemantkchitale@xxxxxxxxx>
wrote:
How confident are those in the Oracle Database Core Development team--
(not referring to the VPs/SVPs reporting to the CxOs) ? Can they
deliver automated patching that doesn't cause plan flips on critical
SQLs (and each application / implementation has a different list of
critical SQLs) or any outages ?
Hemant K Chitale
Sent from my smartphone
On 5 Oct 2017 13:47, "Stefan Knecht" <knecht.stefan@xxxxxxxxx> wrote:
On Wed, Oct 4, 2017 at 11:03 PM, MacGregor, Ian A.
<ian@xxxxxxxxxxxxxxxxx>
wrote:
It's an interesting point, but I think there is a fundamental
Minor patches to Linux operating systems are done automatically. Isn't
this bringing that same methodology to database executables. Larger
changes require a reboot to pick up a new kernel. I have to think the
database won't apply patch sets or upgrade itself without permission.
If once the DBA deigns that the system is ready, and these things
happen without outages this is a good thing.
difference in patching an operating system vs patching a software as
sensitive and sophisticated as the Oracle database. Yes, of course
system calls may behave somewhat differently under the hood , but the
odds that some specific behavior fundamentally changes is
significantly lower when you're patching the OS. The system call that
gets patched still has to adhere to the standards and documentation,
it still has to return the same data. Oracle has much more leeway
there since the bulk of the database's functionality isn't
documented. The boundaries within Oracle can change things during patches
are much more loose than those of an OS.
I am personally very strongly against anything automatic that
potentially impacts the functionality and usability. Look at Windows
10 for a good example what automatic updates can do: data loss. I've
had to adjust my personal behavior ( I can't safely let the computer
run over night with applications opened and assume that it will still
be there in the morning like I used to). Imposing, or forcing,
something along those lines on Oracle's database customers in the
cloud, oh boy I truly hope they don't go down that road. Just imagine
the headlines "Major Oracle-Cloud hosted website out for 8h due to
automatic patch".
I think overall a database is much more sensitive to software changes
than an operating system. All it takes is for a single plan to flip,
and all hell will break loose. And there's literally thousands of
things that can potentially cause a plan to flip - the
constantly-growing list of optimizer underscore parameters, the
numerous different functions involved in statistics gathering, the
optimizer itself and the hundreds of decisions it makes when
producing an execution plan. Just thinking about leaving all that at
the grace of your cloud provider to change all that, "automagically", and
without me being able to test it first? Uhhhh Nope. Nope, nope, nope.
Don't get me wrong, I'm not saying OS patches aren't equally
important and can't cause any issues. Many shops carefully plan and
test their OS patches as they do their database patches, which in my
opinion is the right way to go about it.
Automation is fine, so long as it's within clearly defined boundaries
that I can control. <shameless plug> such as my new framework zztat,
where I can control under what circumstances exactly the database
will be adding new data files automatically, or will collect detailed
latch data automatically when there is a contention so I don't have
to do this manually at 3 AM. That sort of automation I am a big fan
of. Because the odds that the outcome makes my life easier are far
greater than the odds of me loosing my job over it :) </shameless
plug>
Stefan
--
//
zztat - The Next-Gen Oracle Performance Monitoring and Reaction Framework!
Visit us at zztat.net | Support our Indiegogo campaign at
igg.me/at/zztat
| @zztat_oracle
//www.freelists.org/webpage/oracle-l
