On your “old” versus “new” list, glaringly omitted are the details of the disk
farms and connections thereto.
You’ve dropped from 512gb RAM to 224gb RAM. What was the extra being used for
on the old?
What is the operating system on old, and what OS is being hosted that runs
Oracle on VMWare?
Where do the clients live, and how are they connected on both old and new?
And all the stuff Lothar asked, but probably we should know if you were 100%
duplex low latency SSD over infiniband on the old system and you’ve got a scsi
cable to a floppy jukebox on the new system.
Also, does your part of the VMWare world you are in “own” the disk farm you are
using, or is that virtualized with the company receptionist downloading
episodes of the office?
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Lothar Flatz
Sent: Tuesday, July 23, 2019 2:18 PM
To: oracle-l@xxxxxxxxxxxxx; lyallbarbour@xxxxxxxxxxxxxxx
Subject: Re: Database upgrade/move I/O waits performance
Hi,
how about the average I/O wait time in both AWR? How is the I/O wait histogram?
Get runtime stats from your queries. Did you license diagnostic and Tuning pack?
Did you disable adaptive features? Tried OFE 11.2.0.4? Why 12.1. which is known
to have performance issues?
Regards
Lothar
Am 23.07.2019 um 18:39 schrieb Lyall Barbour:
Hello gurus,
We've upgraded and moved the company's CRM OLTP Oracle database.
Old: physical hardware, 512g RAM, 32 CPU -- Oracle 11.2.0.4 Enterprise, 16g
MEMORY_TARGET, SPM baselines used, one or two Profiles.
New: VMWare VM, 224g RAM, 24 vCPU -- Oracle 12.1.0.2 Enterprise, 72g
SGA_TARGET, HugePages, 12g PGA_TARGET, no SPM baselines, multiple Profiles.
Having said all that, there's a consistency to the slowness that's very
interesting. We are constantly having I/O waits on all (?) queries, if not
all, the main top sqls that are running in v$session_longops.
Comparing one query. Explain Plan, Cost, disk reads, buffer gets, all line up
pretty well in 11g and 12c. The 2 things that are different is the actual
time, slowest in 11g is 2.5 seconds, 12c is 14 seconds. and percentage of
work, 11g disk work is 85% and cpu work is 15%. 12c disk work is 98%, cpu 2%.
I'm not trying to get any solutions from everyone here, i'm trying to get
direction on where to look next so i know who to talk to at my company with
this issue. Do i talk to the SAN folks and see what their graphs are like? VM
team? keep digging into AWR stats and comparisons on the database?
Comparing AWR hour periods from today (oracle 12.1) to last week, tuesday,
(oracle 11.2) the amount of buffer gets and disk reads all line up with the
issue i described above. 420T of buffer cache reads last week, 10T today, in
that hour period. ALL work is waiting on disk.
Any help... helps.
Thanks,
Lyall Barbour
-- //www.freelists.org/webpage/oracle-l ;
--
