Totally agreed with Mark answer, it will narrow down to some bad nastly
sqls. Once you solve this, system will be again smooth
Just check the "SQLs ordered by buffer gets" section" to see if some
better indexes can solve the problem and "sql ordered by executions"
sections to see these sqls are coming from function calls , which might be
deep embeded in sqls and thus doing the heavy context switch
Increasing hardware should be last part to solve the tuning problem. it
might push the issue back for some days, but bad sqls will come back again
to haunt the system and DBA ;)
Best regards,
Harmandeep Singh
On Wed, Mar 22, 2017 at 10:56 PM, Jack Applewhite <
jack.applewhite@xxxxxxxxxxxxx> wrote:
Jeff,
I can't really say, since you have the Flash SAN. I'm assuming you have
excellent bandwidth between the DB server and the SAN, so Flash Cache might
make no difference. I'd think it *might* IF you had an SSD locally on the
server itself and configured Flash Cache to use it. That would eliminate
any traffic latency and perhaps any SAN contention - which may be used by
other servers?.
You can turn it on and off pretty easily so it might be worth testing it
for your situation. After turning it on, give it a few days to become fully
populated to get the maximum advantage of the largest Flash Cache part of
the Buffer Cache. I'd be *very* interested in any results of that testing.
--
Jack C. Applewhite - Database Administrator
Austin I.S.D. - MIS Department
512.414.9250 <0512%20414%209250> (wk)
I can't help about the shape I'm in,
I can't sing, I ain't pretty and my legs are thin.
But ... -- "Oh Well" F.M.
------------------------------
*From:* Jeff Chirco <backseatdba@xxxxxxxxx>
*Sent:* Wednesday, March 22, 2017 11:12:49 AM
*To:* Jack Applewhite
*Cc:* oracle-l@xxxxxxxxxxxxx
*Subject:* Re: Additional CPU justification
HI Jack, yep we are moving to Oracle Linux 7. We already have a NetApp All
Flash SAN which this database runs on and yeah it is basically eliminated
most I/O wait. Would this Database Smart Flash Cache still be beneficial?
Thanks
On Tue, Mar 21, 2017 at 2:29 PM, Jack Applewhite <
jack.applewhite@xxxxxxxxxxxxx> wrote:
Jeff,
If you're going to Linux you should consider Oracle Linux. Also, get some
SSDs for that server. The huge performance improvement of implementing
Database Smart Flash Cache is available under Oracle Linux and Solaris -
only those two OS's.
We have two X5 ODAs with a lot of Prod DBs on them. Each ODA node
(server) has 256GB of RAM, 20 cores lit up, with one shared 512GB SSD
per ODA. Our main Prod DBs are running on the the two nodes of one of
the ODAs, each with about a 50GB SGA.. They were straining the ODA nodes at
times - Load Averages of up to 80 or so (which is 400%), with a lot of that
being I/O waits.
We turned on Flash Cache and have been utterly stunned at the performance
improvement for each DB. We rarely have L.A.'s over 20 (100%) anymore.
Flash Cache extends the Buffer Cache to the SSD and manages it. We've seen
waits on DB File Sequential Read go way, way down.
As you know memory and disk are much, much cheaper than Oracle CPU
licenses. SSDs have come way down. Licensing Oracle Linux looks like a
fairly inexpensive proposition, though I haven't done so standalone - OL is
just part of the ODA package, which we love.
Hope this helps.
--
Jack C. Applewhite - Database Administrator
Austin I.S.D. - MIS Department
512.414.9250 <(512)%20414-9250> (wk)
I can't help about the shape I'm in,
I can't sing, I ain't pretty and my legs are thin.
But ... -- "Oh Well" F.M.
------------------------------
*From:* oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on
behalf of Jeff Chirco <backseatdba@xxxxxxxxx>
*Sent:* Tuesday, March 21, 2017 3:07 PM
*To:* oracle-l@xxxxxxxxxxxxx
*Subject:* Additional CPU justification
Hi everyone, I am working on trying to make a case that our production
database server needs some additional CPU's but was hoping you might be
able to give me some tips/suggestion that you've used to prove your case.
We are a smaller shop and so we currently only have a 2 CPU database
license and so the cost of doubling that plus all the packs and options we
have is not an easy thing to pass.
We currently running 11.2.0.4 on Windows but plans to move to Oracle
Linux this year and possibly 12c at that time.
We occasionally spike to 80-100% during the day but average around 40%
Thanks for any help.
Jeff
Confidentiality Notice: This email message, including all attachments, is
for the sole use of the intended recipient(s) and may contain confidential
student and/or employee information. Unauthorized use of disclosure is
prohibited under the federal Family Educational Rights & Privacy Act (20
U.S.C. §1232g, 34 CFR Part 99, 19 TAC 247.2, Gov’t Code 552.023, Educ. Code
21.355, 29 CFR 1630.14(b)(c)). If you are not the intended recipient, you
may not use, disclose, copy or disseminate this information. Please call
the sender immediately or reply by email and destroy all copies of the
original message, including attachments.
Confidentiality Notice: This email message, including all attachments, is
for the sole use of the intended recipient(s) and may contain confidential
student and/or employee information. Unauthorized use of disclosure is
prohibited under the federal Family Educational Rights & Privacy Act (20
U.S.C. §1232g, 34 CFR Part 99, 19 TAC 247.2, Gov’t Code 552.023, Educ. Code
21.355, 29 CFR 1630.14(b)(c)). If you are not the intended recipient, you
may not use, disclose, copy or disseminate this information. Please call
the sender immediately or reply by email and destroy all copies of the
original message, including attachments.
