Your original note says "our application" - but now you're citing the
E-Business suite. So which is it?
If "your" application is E-Business suite why did you ask the question?
The reason why you see the figures you do is because that's what
"NOWORKLOAD" does. It deletes any existing system stats, sets the seek
time and transfer rates to their (1970's) defaults and measures the CPU
speed (of a single CPU) by running some baseline non-database CPU load.
There's likely to be a small variation in this captured CPU speed over a
series of calls because there's always likely to be some activity going on
and a little scheduling time lost. (See also this note for running with
the 'EXADATA' option, which explains something of how the arithmetic works:
https://jonathanlewis.wordpress.com/2019/08/14/gather_system_stats/
In your case, with the E-Business directive, one of two things will happen
when Oracle calculates the cost of a tablescan:
a) by default Oracle will assume that a multiblock read count will be
"_db_file_optimizer_read_count" blocks (default 8) and use the ioseektim
and iotfrspeed to calculate the size and time for a multiblock read -
though it will try to use the largest possible size for a multiblock read
at run-time.
b) if you've set the db_file_multiblock_read_count Oracle will copy that to
"_db_file_optimizer_read_count" and use it in the calculation, and limit
the run-time multiblock read size to that value.
Note - the default value for db_file_multiblock_read_count depends on
several installation details but typically equates to 1MB / db_block_size,
which usually means 128.
In terms of SSD a "seek time" means nothing. But the parameters are just
numbers that go into a calculation.
If you wanted to create a meaningful pair of values for the two I/O times
you could run up a simple C program that did thousands of 8KB direct I/O
reads from a very large file, and then did thousands 1MB reads, and checked
the average times for the two sizes of read. Then you could work out your
own seek and transfer rates based on the simultaneous equations:
seek + 1 transfer = time1
seek + 128 transfer = time2
transfer = (time2 - time1)/127 -- that's the time to transfer 8KB, from
which you need to derive bytes per millisecond
seek = time1 - transfer
Then use dbms_stats.set_system_stats to set the MBRC to 128, the ioseektim
to seek, and the iotfrspeed to the derived bytes per millisecond.
Regards
Jonathan Lewis
On Fri, 29 Jan 2021 at 02:43, Hameed, Amir <Amir.Hameed@xxxxxxxxx> wrote:
Thanks Jonathan.
System statistics were collected in the past and also recently with the
NOWROKLOAD option, which is what Oracle suggests for Oracle E-Business
Suite R12 databases. The MRBC value was not set. What I find interesting is
that the IO statistics numbers on both storage frames (VMAX250F versus
PowerMax) are identical. I would also like to understand what the value of
IOSEEKTIM represents in the world of SSDs or All Flash Array storage.
SaveTime Stat Name Value
------------------------- --------------- ------------------------------
26-JAN-2021 23:07:15 CPUSPEED
26-JAN-2021 23:07:15 CPUSPEEDNW 3,528
26-JAN-2021 23:07:15 IOSEEKTIM 10
26-JAN-2021 23:07:15 IOTFRSPEED 4,096
26-JAN-2021 23:07:15 MAXTHR
26-JAN-2021 23:07:15 MBRC
26-JAN-2021 23:07:15 MREADTIM
26-JAN-2021 23:07:15 SLAVETHR
26-JAN-2021 23:07:15 SREADTIM
Current CPUSPEED
Current CPUSPEEDNW 3,518
Current IOSEEKTIM 10
Current IOTFRSPEED 4,096
Current MAXTHR
Current MBRC
Current MREADTIM
Current SLAVETHR
Current SREADTIM
Thank you,
Amir
*From:* oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> *On
Behalf Of *Jonathan Lewis
*Sent:* Thursday, January 28, 2021 6:08 AM
*To:* oracle-l@xxxxxxxxxxxxx
*Subject:* Re: Question on gathering System Statistics
CAUTION: This email originated from outside the organization. Do not
click links or open attachments unless you recognize the sender and know
the content is safe.
A key question to ask first is whether you had system stats set/collected
on the vmax250 and whether this was done in combination with setting the
db_file_multiblock read count or leaving it to default, and had you done
anything with the calibrate_io option?
Ideally you probably want to believe that if you get the same plans with
the Powermax you'll get the same, or better, performance. But if you do
something that changes the optimizer's arithmetic you may get plan changes
that result in random variations in performance.
If you don't know what you've done about system stats in the past there's
a script at the end of this blog note that will report them for you:
https://jonathanlewis.wordpress.com/2019/08/14/gather_system_stats/
Regards
Jonathan Lewis
On Tue, 26 Jan 2021 at 19:30, Hameed, Amir <Amir.Hameed@xxxxxxxxx> wrote:
Hi,
We are in the process of moving our applications from current storage
array (VMAX250) to a new array (Powermax). From best practices standpoint,
should we collect System Stats after moving to the new storage? If we
should then is there a recommended way of gathering System Stats?
Thanks,
Amir
